DATABASICS Standard Expense Extract

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

Technical Specification

File Attribute	Description
File Format	tab delimited, or xml
What to include	All fully approved expense reports’ data that that has not previously been extracted
Level of detail	A Detailed Extract All data will be extracted as entered on the expense report. There will be an export expense record created for every distributed line entered in DATABASICS Expense.
Batch Type	DB

Extract Specification

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 – e.g. Project (Four Level Project Structure)
LEVEL2_CODE	Level 2 – e.g. Task (Four Level Project Structure)
LEVEL3_CODE	Level 3 – 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 Amount
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

Notes on Extract definition

XP_TEAIM_EXTRACT have the following amount columns naming convention

Columns first letter:

E_ - Entry values, how user entered (if more then one WBS values will be repeated on each line)

L_ - in local currency distributed by WBS percentage

R_ - in report/employee currency distributed by WBS percentage

C_ - in credit card currency distributed by WBS percentage (apply only to credit card transactions)

W_ in the Level1 or Level2 Converted Currency depending on the profile setup. (The Profile Option is WBS_CONVERT_CURRENCY)

O_ 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

(columns GRAND don't have business and personal options, because it's sum of business and personal amounts)

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)