DATABASICS exports timesheets/expenses to Lawson in two ways:
Direct link to ACDBTRANS: Databasics exports the data to the Lawson interface table directly either using ODBC or Native drivers.
File Export: Databasics exports the data to an ASCII file to be later imported into ACDBTRANS. This method is used when there is no direct connectivity to the Lawson database server.
ACDBTRANS 8.x.x
Field Name | Field Description | Field Type and Length | Explanation | Databasics Field | Databasics Description |
Run-Group | Run Group | Alpha 15 | Key field. Required. Run Group. | Date time stamp for both TE and EX.
| TM & XP |
Seq-Number | Sequence Number | Numeric 6 | Key field. Required. Sequence number. Uniquely identifies each record in the run group. | Sequentially number each record in the batch | TM & XP |
Activity | Activity | Alpha 15 | Activity. Not required for records with a source code TE (TimeSite time records) for sick and vacation. | Lowest level in the WBS. LEVEL_FLAG = A (Activity) | TM & XP |
Acct-Category | Account Category | Alpha 5 | Account Category. Not required for source code TE records, if the activity group is flagged to default the account category from the EMPLOYEE file. Not required for EX. | Lowest level in the WBS. LEVEL_FLAG = C (Account category) for TE.
Blank for Expense. | TM Only |
Posting-Date | Posting Date | Date 8 | Posting Date. Defines the period the transaction will be posted in. | HDR_DATE from the TM_HDR record. (Defined on run parameters in Databasics.) | TM & XP |
Trans-Date | Transaction Date | Date 8 | Transaction Date. Date of the time record or expense was incurred. | DATE from the TM_DTL_ROW record. | TM & XP : In expense is the XP_REPORT_DTL.END_DATE and in Time is TM_DTL_COL.EVENT_DATE |
Dist-Company | Distribution Company | Numeric 4 | Distribution Company. For time entries this represents the distribution company on the employee file. Blank for expense. | OPT_CODE from the TM_DTL_ROW record.
Blank for expense. | TM Only |
Ds-Acct-Unit | Distribution Accounting Unit | Alpha 15 | Distribution Accounting Unit. For time entries this represents the distribution accounting unit on the employee file. Blank for expense. | DEPT_CODE from the TM_DTL_ROW record.
Blank for expense. | TE Only |
Description | Transaction Description | Alpha 30 | Transaction Description. Employee Last Name, First Name | Employee Last Name, First Name for both time and expense. | TM & XP |
Tran-Currency | Transaction Currency | Alpha 5 | Transaction Currency Code. Defines the currency the time record or expense record was entered in. | CURR_ID from EMPLOYEE record. CURR_ID from the expense record. | TM & XP: For Time is the currency id from the employee table. YES For expense is the currency id from the XP_REPORT_DTL.CURRENCY_FROM |
Tran-Amount | Transaction Amount | Signed 15,2 | Transaction Cost Amount. | Blank for TE records.
Approved expense amount in transaction or expense currency. | XP Only
XP_REPORT_DTL.REIMBURSEM before con before conversion. |
Tran-ND | Transaction Number Decimals | Numeric 1 | Transaction Number of Decimals. | Transaction currency number of decimals from the currency table. | XP Only Get this information from CURRENCY.NBR_DECIMAL where CURRENCY_FROM |
Payment-Amt | Reimbursement Amount | Signed 15,2 | Distribution Amount. For transactions with source code EX. Amount to interface to AP distribution amount. Reimbursement amount for AP. | Blank for TE records.
Payment or reimburse amount. | XP Only
Reimbursement field in XP_REPORT_DTL.REIMBURSE_AMOUNT after conversion. |
Payment-Curr | Reimbursement Currency Code | Alpha 5 | Reimbursement currency code. | Blank for TE records.
Payment or reimburse currency. | XP Only
XP_REPORT_DTL.CURRENCY_TO. |
Payment-ND | Reimbursement Number Decimals | Numeric 1 | Reimbursement Number of Decimals. | Reimburse currency number of decimals from the currency table. | XP Only
Get this information from CURRENCY.NBR_DECIMAL where CURRENCY_TO |
Payment -Rate | Reimbursement Currency Exchange Rate | Signed 12,6 | Reimbursement exchange rate from trans currency to reimbursement currency. | Reimburse currency rate from the currency table. | XP Only
XP_REPORT_DTL.EXCHANGE_RATE |
Invoice | Invoice Number | Alpha 22 | Employee Expense Invoice Number. Required for transactions with source code EX. | Blank for TE.
Unique expense number from Databasics. Unique by AP Company, Vendor, and Post date. | XP Only
Transaction unique number by Vendor this is located in
XP_TRANS_PROCESS.TRANS_NO. |
Expense-Code | Expense Code | Alpha 20 | Expense Code. Required for transactions with source code EX. | Blank for TE.
Expense Type from Databasics. | XP Only
EXPENSE_TYPE from XP_REPORT_DTL |
AP-Company | AP Company | Numeric 4 | AP Company Number. Required for source code EX. |
| XP Only
From TE_VENDOR in column AP_COMPANY
|
Vendor | Vendor | AlphaRight 9 | Vendor Number. Required for transactions with source code EX. |
| XP Only
VENDOR_ID from the EMPLOYEE Table by employee Only. |
Source-Code | Source Code | Alpha 2 | Source Code. Defines the source of the transactions. For transactions created from Databasics TimeSite source code = TE. Databasics ExpenseSite entry source code = EX. | TE EX | TM & XP |
Units-Amount | Units | Signed 15,2 | Units. Number of hours for time records.
Number of miles or units from expense. | HOURS from the TM_DTL_COL record.
Mileage or per diem from expense. | TM & XP
Hours for Time VALUE from TM_DTL_COL Mileage for expense: XP_REPORT_DTL column MILAGE_TOTAL.
Per diem we need to send the number of days. (For now we will send 0) |
Payment-Type | Payment type | Alpha 1 | Employee paid Corp Credit Card Advance Company paid | See table in the ACDBTRANS Mapping for ExpenseSite. Valid values: C, P, E | E: Pay to the employee when it reimbursable P: No credit card and No Reimbursement (Applied to Advanced amount ) C: Yes credit No Reimbursement company pay expenses |
HR-Company | HR Company | Numeric 4 | HR Company. Employee company. | EMPLOYEE_OP_UNIT | TM Only |
Employee | Employee Number | Numeric 9 | Employee Number. Required for transactions with source code TE. It represents the HR employee number. | EMPLOYEE_ID from the TM_HDR record. | TM Only |
Pay-code | Pay Code | Alpha 4 | Pay Code. Required on transactions with source code TE. | PAY_CODE from the TM_DTL_ROW record.
Blank for EX. | TM Only |
Locat-Code | Location | Alpha 10 | Location Code. Optional on transaction with source code TE. Update with the employee location. | LOCATION_CODE from the TM_DTL_ROW record | TM Only |
Job-code | Job Code | Alpha 9 | Job Code. Optional on transactions with source code TE. | Global Level 4 | TM Only |
Shift | Shift | Numeric 1 | Shift. Valid values 1-4. | Optional for TE records only.
Blank for EX. | TM Only |
Matrix-Cat-1 | Attribute Category | Alpha 12 | Attribute Category. Labor Category | Function | TM Only |
Matrix-Cat-2 | Attribute Category | Alpha 12 | Attribute Category | Blank |
|
Matrix-Cat-3 | Attribute Category | Alpha 12 | Attribute Category | Blank |
|
Mx-Value-1 | Attribute Value | Alpha 32 | Attribute Value | Function
| TM Only |
Mx-Value-2 | Attribute Value | Alpha 32 | Attribute Value | Blank |
|
Mx-Value-3 | Attribute Value | Alpha 32 | Attribute Value | Blank |
|
Trans-Type | Transaction Type | Alpha 1 | This field maintains the transaction type. This field determines if the transaction is posted to AC only, PR only, or both. Valid values: A - AC only, P – PR only, L – Labor Distribution both PR and AC, E – Expense Distribution | P – PR only (sick and vacation) L – PR and AC. E – Expense | TM & XP |
Prev-Run-Group | Previous Run Group | Alpha 15 | If this time record has been previously updated to Lawson, this field will contain the previous run group number. This will allow us to identify that this record already exists in the ACTRANSREL file and possibly in the ACCOMMIT records and needs to be deleted. | Only required for TE. Blank for EX.
| TM Only |
Flag1 | Flag 1 | Alpha 1 | Identifies time record status from Databasics. Specifically, PR adjustment records for closed or prior PR cycles. | TE adjustments only.
F - Final, A - Adjustment, D - Daily, M - Manual
| TM Only |
Orig-Obj-ID | Original Object Id | Numeric 12 | Sequence number which links record back to Databasics. Databasics defines this number. Required for both TE and EX transactions. | TRANSACTION_ID
| Transaction Number unique by expense line item and time line item. This information is located in XP_POST_DTL and TM_POST_DTL |
Atn-Obj-ID | Activity Object Id | Numeric 12 | Object Id, which links record to the commitment record. AC541 updates this with the commitment object identifier. | Blank
|
|
Pay-Per-End-Dt | Pay Period End Date | Date 8 | Pay Period End Date. | Pay Period End Date for Time records | TM Only |
Pay-Vendor | Pay Vendor | Alpharight 9 | Pay Vendor. Used to maintain credit card vendor for corporate credit card reimbursements |
| XP Only Vendor ID of the credit card vendor Only |
Record-Type | Record Type | Alpha 1 | Record Type. | Valid Values: 0,1 2 and 3 Adjustments only.
|
|
Adj-Orig-Objid | Adjustment Originating Object Id | Numeric 12 | Adjustment Originating Object Id | Adjustments only |
|