Versions Compared

Old Version 1

changes.mady.by.user Torbjorn Nilsen

Saved on

compared with

New Version Current

changes.mady.by.user Torbjorn Nilsen

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

DATABASICS Expense has a standard extract, available to all customers. The intent of this extract is to make all expense data collected in the Expense Reporting process available in a batched solution dependent of defined workflow.

It is understood that no customer utilize all DATABASICS feature functionality, and if a feature is not used the field output value in this specification will be blank.

Customer may take this raw data and process further to meet their internal needs. DATABASICS Consulting may also be engaged to tailor individual variations in this output to suit customer requirements specifically, e.g. remove unused fields or add customer specific fields.

DATABASICS Standard Expense Extract

...

Column ID

Note

*BATCH_ID

DATABASICS generated Batch ID, Generated on the back end

TRANS_NO

*REPORT_ID

Expense Report ID, Generated on the front end (unique across the system)

REPORT_TYPE

Report Type ID

EMPLY_ID

DB Primary Key – HR Employee ID

OTHER_EMPLY_ID

Same as Employee ID

VENDOR_ID

Employee Vendor ID

PAID_VENDOR_ID

FULL_NAME

Employee Full name

RPT_CURR

Report Currency Code

HDR_OPT_CODE

User's Home Company ID (Two Level Org Structure)

HDR_DEPT_CODE

User's Home Department ID (Two Level Org Structure)

E_SUB_LINE_NO

*E_LINE_NO

Expense Line Number, Generated on the front end  Transaction Primary Key (unique across the system)

PURCHASE_DATE

TRAVEL_DATE

EXCHANGE_RATE

MILEAGE_RATE

MILEAGE_UNIT

CC_TRANS_NO

Credit card transaction number - mapped to transaction details table

REIMBURSABLE

Y/N - depends on the payment type and or expense type definition

PAY_ID

Payment type ID

CC_FLAG

ADV_TRANS_NO

Advance transaction number mapped to advance table

CURRENCY_FROM

Expense line's currency from (original)

GRP_EXP_ID

GL Account Number (set up in Expense Type Group)

EXP_TYPE

Expense Type ID

WBS_KEY

Unique Key generated upon distributing an expense line

EXP_CODE

Expense Code (determines if direct charge or indirect)

LEVEL1_CODE

Level 1 – Company e.g. Project (Four Level Project Structure)

LEVEL2_CODE

Level 2 – Cost Centre e.g. Task (Four Level Project Structure)

LEVEL3_CODE

Level 3 – Project e.g. Sub-Task (Four Level Project Structure)

LEVEL4_CODE

Level4 code (from WBS Tree)

LEVEL_N_CODE

LevelN code (from WBS Tree)

LEVEL_INDEX

LevelN Index (from WBS Tree)

OPT_CODE

Transaction Charged Company ID (Two Level Org Structure)

DEPT_CODE

Transaction Charged Cost Centre ID (Two Level Org Structure)

PERCENTAGE

Percentage tied to the distribution

TRAN_ND

PAYMENT_ND

POST_DATE

Extract Post date

MILEAGE_TOTAL

Number of miles

CC_BILL_AMOUNT

Credit Card Bill amountAmount

CC_BILL_CURR

Credit Card Billing Currency

CC_CURR_RATE

Credit card Exchage rate

E_PERSONAL_AMOUNT

expense line entry values, how user entered (if more then e WBS values will be repeated on each line)

E_BUSINESS_AMOUNT

E_ALCOHOL_AMOUNT

E_TIP_AMOUNT

E_TAX_AMOUNT

E_TAX2_AMOUNT

L_BASE_B

in local currency (currency from) distributed by WBS percentage; Columns last letter:
_B  - business
_P  - personal. Middle part (all amounts are distributed by WBS percentage)

BASE- user entered business or personal amounts
ALC- alcohol amount
TIP - tip amount
TAX- tax 1 amount
TAX2- tax2 amount
TOTAL- sum of BASE, ALC, TIP, Tax and TAX2 amounts
GRAND - sum of business and personal TOTAL amounts
COMP_EMP- amount paid by company to employee (only in report and cc currencies)
COMP_CC- amount paid company to credit card (only in report and cc currencies)
EMP_CC- amount paid employee to credit card (only in report and cc currencies)

L_BASE_P

L_ALC_B

L_ALC_P

L_TIP_B

L_TIP_P

L_TAX_B

L_TAX_P

L_TAX2_B

L_TAX2_P

L_TOTAL_B

L_TOTAL_P

L_GRAND

R_BASE_B

 in report/employee currency  distributed by WBS percentage. Columns last letter:
_B  - business
_P  - personal. Middle part (all amounts are distributed by WBS percentage)

BASE- user entered business or personal amounts
ALC- alcohol amount
TIP - tip amount
TAX- tax 1 amount
TAX2- tax2 amount
TOTAL- sum of BASE, ALC, TIP, Tax and TAX2 amounts
GRAND - sum of business and personal TOTAL amounts
COMP_EMP- amount paid by company to employee (only in report and cc currencies)
COMP_CC- amount paid company to credit card (only in report and cc currencies)
EMP_CC- amount paid employee to credit card (only in report and cc currencies) 

R_BASE_P

R_ALC_B

R_ALC_P

R_TIP_B

R_TIP_P

R_TAX_B

R_TAX_P

R_TAX2_B

R_TAX2_P

R_TOTAL_B

R_TOTAL_P

R_GRAND

R_COMP_EMP

R_COMP_CC

R_EMP_CC

C_BASE_B

in credit card currency  distributed by WBS percentage (apply only to credit card transactions). Columns last letter:
_B  - business
_P  - personal. Middle part (all amounts are distributed by WBS percentage)

BASE- user entered business or personal amounts
ALC- alcohol amount
TIP - tip amount
TAX- tax 1 amount
TAX2- tax2 amount
TOTAL- sum of BASE, ALC, TIP, Tax and TAX2 amounts
GRAND - sum of business and personal TOTAL amounts
COMP_EMP- amount paid by company to employee (only in report and cc currencies)
COMP_CC- amount paid company to credit card (only in report and cc currencies)
EMP_CC- amount paid employee to credit card (only in report and cc currencies)

C_BASE_P

C_ALC_B

C_ALC_P

C_TIP_B

C_TIP_P

C_TAX_B

C_TAX_P

C_TAX2_B

C_TAX2_P

C_TOTAL_B

C_TOTAL_P

C_GRAND

C_COMP_CC

C_EMP_CC

L_TOTAL_B_BAL

L_TOTAL_P_BAL

L_GRAND_BAL

R_TOTAL_B_BAL

R_TOTAL_P_BAL

R_GRAND_BAL

C_GRAND_BAL

W_CURR

in the Level1 or Level2 Converted Currency depending on the profile setup. (The Profile Option is WBS_CONVERT_CURRENCY). Columns last letter:
_B  - business
_P  - personal. Middle part (all amounts are distributed by WBS percentage)

BASE- user entered business or personal amounts
ALC- alcohol amount
TIP - tip amount
TAX- tax 1 amount
TAX2- tax2 amount
TOTAL- sum of BASE, ALC, TIP, Tax and TAX2 amounts
GRAND - sum of business and personal TOTAL amounts
COMP_EMP- amount paid by company to employee (only in report and cc currencies)
COMP_CC- amount paid company to credit card (only in report and cc currencies)
EMP_CC- amount paid employee to credit card (only in report and cc currencies)

W_CURR_RATE

W_BASE_B

W_BASE_P

W_ALC_B

W_ALC_P

W_TIP_B

W_TIP_P

W_TAX_B

W_TAX_P

W_TAX2_B

W_TAX2_P

W_TOTAL_B

W_TOTAL_P

W_GRAND

W_GRAND_BAL

O_CURR

in the Distributed Operating Unit or Department Converted Currency, depending on the profile setup. (The Profile Option is OBS_CONVERT_CURRENCY). Columns last letter:
_B  - business
_P  - personal. Middle part (all amounts are distributed by WBS percentage)

BASE- user entered business or personal amounts
ALC- alcohol amount
TIP - tip amount
TAX- tax 1 amount
TAX2- tax2 amount
TOTAL- sum of BASE, ALC, TIP, Tax and TAX2 amounts
GRAND - sum of business and personal TOTAL amounts
COMP_EMP- amount paid by company to employee (only in report and cc currencies)
COMP_CC- amount paid company to credit card (only in report and cc currencies)
EMP_CC- amount paid employee to credit card (only in report and cc currencies)

O_CURR_RATE

O_BASE_B

O_BASE_P

O_ALC_B

O_ALC_P

O_TIP_B

O_TIP_P

O_TAX_B

O_TAX_P

O_TAX2_B

O_TAX2_P

O_TOTAL_B

O_TOTAL_P

O_GRAND

O_GRAND_BAL

TAX1_CODE

TAX2_CODE

C_VENDOR_ID

As entered by user

PO_NUMBER

As entered by user

TRANS_STATUS

 

...