Allows you to create a report based on the details at the expense line level. These details include amounts payable to the employee, amounts payable to the credit card company, personal amounts (non-reimbursable) and total amounts. Examples:
- Expenses by Payment Type
- Mileage by user
Supported Version | Domain Name | Domain Folder |
|---|---|---|
5.1.x to 5.4.4 | Expense Detail Charges at Line Item 5.1.x | Domains |
5.5 and higher | Expense Detail Charges by Line | DomainsV55 |
The domains consists of the following columns (NOTE: Column names could be renamed so you might see different names based on your side configuration, so the following names are the base names of the system)
NOTE: Make sure you select the CONVERT_CURR for one specific currency, Default is USD
Column ID | Column Name (From DBLabels.properties) | Data Type | Description |
|---|
EMPLY_TYPE_ |
NAME | Employee Type | String |
Convert all the data to one currency - Currency code
CONVERT_RATE
Converted Rate
Decimal
Converted currency rate
CURRENCY_FROM
Local Currency
String
Expense line charged currency
EXCHANGE_RATE
Exchange Rate
Decimal
Employee Type Name | |||
EXP_TYPE_NAME | Expense Type | String | Expense Type Name Charge that contains attendees |
GRP_EXP_ID | Expense Type Group ID | String | Expense Type Group ID for the charged Expense Type |
GRP_EXP_NAME | Expense Type Group | String | Expense Type Group Name for the charge Expense Type |
HDR_TITLE | Report Title | String | Report Tile |
HOME_DEPT_ |
NAME |
REPORT_TYPE
Repor Type ID
String
Report Type ID
REPORT_TYPE_NAME
Report Type
String
Home Department |
Integer
Report ID
String | Home Department Name | ||
HOME_OPT_NAME | Home Operating Unit | String | Home Operating Unit Name |
LAST_FIRST | Full Name (Last, First) | String | Employee/User Last Name, First Name |
LOCK_BY | Locked By | String | Locked by if the expense report is in Status: Open or Closed |
PAY_ |
Locked Datetime Stamp
Datetime
Locked Datetime if the expense report is in Status: Open or Closed
NOTE
Note
String
Attendee Line Level Note
LAST_FIRST
Full Name (Last, First)
String
Employee/User Last Name, First Name
VENDOR_ID
Vendor ID
String
Vendor ID that is linked to the report that got requested
VENDOR_NAME
Vendor Name
String
Vendor Name
START_DATE
Report Start Date
Date
Expense Report Start Date
END_DATE
Report End Date
Date
Expense Report End Date
RPT_CURR
Report Currency
String
Expense Report Currency Code
COMP_BALANCE_EMP
Due by Company to Employee
Decimal
Expense Report - Company Total Amount due to Employee for that specific expense line charge
EMP_BALANCE_CC
Due by Employee to CC
Decimal
Expense Report - Employee Total Amount due to Credit Card for that specific expense line charge
TOTAL_AMOUNT
(R_TOTAL)
Total Amount in Report Currency
Decimal
Expense Report- Total Amount per line item that contains the attendees on the specific report.
ALCOHOL_AMOUNT
Entry Alcohol Amount
Decimal
Alcohol Entry Amount
REIMBURSE_AMOUNT
(R_TOTAL_B)
Total Business Amount in Report Currency
Decimal
Expense Line - Reimbursable/Business Amount
REIMBURSE_TAX
(R_TAX1_B)
Business Tax1 Amount in Report Currency
Decimal
Expense Line - Reimbursable/Business Tax 1 Amount
REIMBURSE_TAX2
(R_TAX2_B)
Business Tax2Amount in Report Currency
Decimal
Expense Line - Reimbursable/Business Tax 2 Amount
REIMBURSE_TIP
(R_TIP_B)
Business Tip Amount in Report Currency
Decimal
Expense Line - Reimbursable/Business Tip Amount
XP_CERT_STATUS
Certify Status
String
Certify Status by the user for the expense report.
Certify Status:
Require Certification
Certified Already
No Action (default)
REF_NUM
Reference Number
String
Expense Line Reference Number
MAIN_GROUP_TITLE
Credit Card Charge Description
String
Contains the itemized main group if there are itemized charges.
GROUP_TITLE
Line Description
String
Contains the default Expense Type name or the Credit Card Description charge.
EXP_TYPE
Expense Type ID
String
Expense Type ID Charge that contains attendees
EXP_TYPE_NAME
Expense Type
String
Expense Type Name Charge that contains attendees
GRP_EXP_ID
Expense Type Group ID
String
Expense Type Group ID for the charged Expense Type
GRP_EXP_NAME
Expense Type Group
String
Expense Type Group Name for the charge Expense Type
TRAVEL_DATE
Travel Date
Date
Expense Charge charge or travel date.
PAY_ID
Payment Type ID
String
Payment Type ID per the expense line
PAY_ID_NAME
Payment Type
String
Payment Type Name per the expense line
SSN
SSN
String
Employee Other id that is mapped to SSN field.
PLACE_NAME
Place Name
String
Expense Report Place Name if existing on the Report Cover Page.
OTHER_EMPLY_ID1
Other Employee ID1
String
Employee Other id1 that is mapped to OTHER_EMPLY_ID1 in the employee table
OTHER_EMPLY_ID2
Other Employee ID2
String
Employee Other id2
EMP_ACTIVE
Active Employee
String
Employee Active Yes or No
EMPLY_ID
Employee ID
String
Employee Id
EMPLY_TYPE
Employee Type ID
String
Employee Type ID
EMPLY_TYPE_NAME
Employee Type
String
Employee Type Name
DEPT_CODE
(HOME_DEPT_CODE)
Home Department Code
String
Home Department Code
HOME_DEPT_NAME
Home Department
String
Home Department Name
HOME_OPT_NAME
Home Operating Unit
String
Home Operating Unit Name
ID | Payment Type ID | String | Payment Type ID per the expense line |
PLACE_NAME | Place Name | String | Expense Report Place Name if existing on the Report Cover Page. |
REF_NUM | Reference No | String | Expense Line Reference Number |
REPORT_ID | Report ID | Integer | Report ID |
REPORT_TYPE_NAME | Report Type | String | Report Type Name |
RPT_CURR | Report Currency | String | Expense Report Currency Code |
CURRENCY_FROM | Local Currency | String | Expense line charged currency |
EXCHANGE_RATE | Exchange Rate | Decimal | Currency Rate if the Currency From is different than Report Currency |
CONVERT_CURR | Convert to Currency | String | Convert all the data to one currency - Currency code |
CONVERT_RATE | Converted Rate | Decimal | Converted currency rate |
ALCOHOL_AMOUNT | Entry Alcohol Amount | Decimal | Alcohol Entry Amount |
BUSINESS_AMOUNT | Entry Business Amount | Decimal | Personal Entry Amount |
PERSONAL_AMOUNT | Entry Personal Amount | Decimal | Personal Entry Amount |
TAX_AMOUNT | Entry Tax1 Amount | Decimal | Business Amount entered in the system |
TAX2_AMOUNT | Entry Tax2 Amount | Decimal | Tax 2 Amount entered in the system |
TIP_AMOUNT | Entry Tip Amount | Decimal | Tip Amount entered in the system |
L_ALC_B | Business Alcohol Amount in Local Currency | Decimal | Business Alcohol Amount in Local Currency |
L_ALC_P | Personal Alcohol Amount in Local Currency | Decimal | Personal Alcohol Amount in Local Currency |
L_TAX1_B | Business Tax1 Amount in Local Currency | Decimal | Business Tax1 Amount in Local Currency |
L_TAX1_P | Personal Tax1 Amount in Local Currency | Decimal | Personal Tax1 Amount in Local Currency |
L_TAX2_B | Business Tax2 Amount in Local Currency | Decimal | Business Tax2 Amount in Local Currency |
L_TAX2_P | Personal Tax2 Amount in Local Currency | Decimal | Personal Tax2 Amount in Local Currency |
L_TIP_B | Business Tip Amount in Local Currency | Decimal | Business Tip Amount in Local Currency |
L_TIP_P | Personal Tip Amount in Local Currency | Decimal | Personal Tip Amount in Local Currency |
L_TOTAL_B | Total Business Amount in Local Currency | Decimal | Total Business Amount in Local Currency |
L_TOTAL_P | Personal Business Amount in Local Currency | Decimal | Personal Business Amount in Local Currency |
ORIGINAL_AMOUNT | Total Amount in Local Currency | Decimal | Total Amount in Local Currency |
REIMBURSE_ |
ALCOHOL |
R_ALC_ |
B) |
Business Alcohol Amount in Report Currency | Decimal |
Expense Line - Reimbursable/ Business Amount for Alcohol to Employee | |||
R_ALC_P | Personal Alcohol Amount in Report Currency | Decimal | Personal Alcohol Amount in Report Currency |
REIMBURSE_TAX | Business Tax1 Amount in Report Currency | Decimal | Expense Line - Reimbursable/Business Tax 1 Amount |
R_TAX1_P | Personal Tax1 Amount in Report Currency | Decimal | Personal Tax1 Amount in Report Currency |
REIMBURSE_TAX2 | Business Tax2Amount in Report Currency | Decimal | Expense Line - Reimbursable/Business Tax 2 Amount |
R_TAX2_P | Personal Tax2 Amount in Report Currency | Decimal | Personal Tax2 Amount in Report Currency |
REIMBURSE_TIP | Business Tip Amount in Report Currency | Decimal | Expense Line - Reimbursable/Business Tip Amount |
R_TIP_P | Personal Tip Amount in Report Currency | Decimal | Personal Tip Amount in Report Currency |
REIMBURSE_AMOUNT | Total Business Amount in Report Currency | Decimal | Expense Line - Reimbursable/Business Amount |
R_TOTAL_P | Personal Amount in Report Currency | Decimal | Personal Amount in Report Currency |
TOTAL_AMOUNT |
R_ |
TOTAL) |
TIP_AMOUNT
(E_TIP)
Entry Tip Amount
Decimal
Total Amount in Report Currency | Decimal |
Expense Report- Total Amount per line item that contains the attendees on the specific report. | |||
COMP_BALANCE_CC | Due by Company to CC | Decimal | Expense Report - Company Total Amount due to Credit Card for that specific expense line charge |
COMP_BALANCE_EMP | Due by Company to Employee | Decimal | Expense Report - Company Total Amount due to Employee for that specific expense line charge |
EMP_BALANCE_CC | Due by Employee to CC | Decimal | Expense Report - Employee Total Amount due to Credit Card for that specific expense line charge |
EXPENSE_DEDUCTION | Expense Deduction | Decimal | Expense Deduction |
MILEAGE_BUSINESS | Business Mileage | Decimal | Business Mileage |
MILEAGE_PERSONAL | Personal Mileage | Decimal | Personal Mileage |
MILEAGE_RATE | Mileage Rate | Decimal | Mileage Rate |
MILEAGE_TOTAL | Total Mileage |
Decimal | Mileage Total |
PARTIAL_DAY_DEDUCTION
Partial Day Deduction
Decimal
Partial Day Deduction
PERDIEM_RATE | Per Diem Rate | Decimal | Per Diem Rate |
BUSINESS_AMOUNT
(E_BUSINESS)
Entry Business Amount
Decimal
Personal Entry Amount
TAX_AMOUNT
(E_TAX1)
Entry Tax1 Amount
Decimal
Business Amount entered in the system
REIMBURSE_ALCOHOL
(R_ALC_B)
Business Alcohol Amount in Report Currency
Decimal
Expense Line - Reimbursable/ Business Amount for Alcohol to Employee
EXPENSE_DEDUCTION
PARTIAL_DAY_DEDUCTION | Partial Day Deduction | Decimal |
Partial Day Deduction | |||
V_ALC_B | Converted Business Alcohol Amount | Decimal | Converted Business Alcohol Amount for output report |
V_ALC_P | Converted Personal Alcohol Amount | Decimal | Converted Personal Alcohol Amount for output report |
V_TAX1_B | Converted Business Tax1 Amount | Decimal | Converted Business Tax1 Amount for output report |
V_TAX1_P | Converted Personal Tax1 Amount | Decimal | Converted Personal Tax1 Amount for output report |
V_TAX2_B | Converted Business Tax2 Amount | Decimal | Converted Business Tax2 Amount for output report |
V_TAX2_P | Converted Personal Tax2 Amount | Decimal | Converted Personal Tax2 Amount for output report |
V_TIP_B | Converted Business Tip Amount | Decimal | Converted Business Tip Amount for output report |
V_TIP_P | Converted Personal Tip Amount | Decimal | Converted Personal Tip for output report |
V_TOTAL | Converted Total Amount | Decimal | Converted Total Amount for output report |
V_TOTAL_B | Converted Total Business Amount | Decimal | Converted Total Business Amount for output report |
V_TOTAL_P | Converted Personal Business Amount | Decimal | Converted Personal Business Amount for output report |
VENDOR_ID | Vendor ID | String | Vendor ID that is linked to the report that got requested |
MAIN_GROUP_TITLE | Credit Card Charge Description | String | Contains the itemized main group if there are itemized charges. |
GROUP_TITLE | Line Description | String | Contains the default Expense Type name or the Credit Card Description charge. |
VENDOR_NAME | Vendor | String | Vendor Name |
XP_CERT_STATUS | Certify Status | String | Certify Status by the user for the expense report. |
PROCESS_STATUS | Process Status | String | Process Status could be one of the following: |
PREV_STATUS | Report Previous Status | String | Previous Process Status could be one of the following: |
XP_REPORT_STATUS | Report Status | String | Expense report status: |
VALID_STATUS | Report Validation Status | String | Report Validation Status could be one of the following: |
ODOMETER_END | Odometer End | Decimal | Odometer End |
ODOMETER_START | Odometer Start | Decimal | Odometer Start |
NOTES | Note | String | Expense Line Notes |
OTHER_EMPLY_ID1 | Other Employee ID1 | String | Employee Other id1 that is mapped to OTHER_EMPLY_ID1 in the employee table |
OTHER_EMPLY_ID2 | Other Employee ID2 | String | Employee Other id2 |
SSN | SSN | String | Employee Other id that is mapped to SSN field. |
HDR_PLACE_NAME | Cover Page Place Name | String | Expense Report Place Name if existing on the Report Cover Page. |
TODAY_DATE |
TODAY_DATE | Date | Today's Date | |
PAY_ID_NAME | Payment Type | String | Payment Type Name per the expense line |
EMP_ACTIVE | Active Employee | String | Employee Active Yes or No |
EMPLY_ID | Employee ID | String | Employee Id |
EMPLY_TYPE | Employee Type Code | String | Employee Type ID |
EXP_TYPE | Expense Type Code | String | Expense Type ID Charge that contains attendees |
DEPT_CODE | Home Department Code | String | Home Department Code |
OPT_CODE | Home Operating Unit Code | String | Home Operating Unit Code |
REPORT_TYPE | Report Type Code | String | Report Type ID |
FIRST_RELEASED_DATE | First Released date | String | Submitted Date |
LAST_APPROVED_DATE | Last Approved Date | String | Last Approval Date |
PAID_VENDOR_ID | Paid Vendor ID | String | Paid to Vendor ID by default it is always the employee vendor for expense reporting. |
PAID_VENDOR_NAME | Paid Vendor Name | String | Paid to Vendor Name by default it is always the employee vendor for expense reporting. |
COMMUTE_MILEAGE | Commute Mileage | String | Commute Mileage |
BATCH_TYPE_1 | Databasics Extract Batch Type | String | Databasics Extract Batch Type |
BATCH_TYPE_2 | DATABASICS ACH | String | DATABASICS ACH |
BATCH_TYPE_3 | Batch type 3 | String | Used for additional batch types |
BATCH_TYPE_4 | Batch type 4 | String | Used for additional batch types |
BATCH_TYPE_5 | Batch type 5 | String | Used for additional batch types |
BATCH_TYPE_6 | Batch type 6 | String | Used for additional batch types |
END_DATE | Report End Date | Date | Expense Report End Date |
TRAVEL_DATE | Travel Date | Date | Expense Charge charge or travel date. |
HDR_DT | Report DateTime Stamp | Datetime | Report Header Date Time Stamp |
COMP_BALANCE_CC
Due by Company to CC
Decimal
LOCK_DT | Locked Datetime Stamp | Datetime | Locked Datetime if the expense report is in Status: Open or Closed |
START_DATE | Report Start Date | Date | Expense Report Start Date |