...
Employee ID
Login ID
Department Code
First Name
Last Name
Start Date
End Date
Work Breakdown Structure Mapping
...
DATABASICS usually maps Level1 and Level 2 to Deltek project structure. Here is an example of most of the configuration with Deltek
Main WBS → Customer
WBS Level1 → Project
WBS Level2 → Labor Category
WBS assignment → Project Assignment at the employee level.
Timesheet Extract Definition
Specification
Attribute | Description |
Format | CostPoint extract to timesheet module |
What to include | All fully approved, not already extracted timesheets. Extracted by reporting timesheet period for each user. |
Level of detail | Summarized Extract. Extract by expense report and unique account string |
Process | Timesheet extracted from DATABASICS to CostPoint; it will be executed by Administrators once they are ready to close the reporting period in DATABASICS. |
File Name | DBTIME<datetimestamp>.txt |
Time Specification
For Adjustments: apply the original logic for the adjusted line(s), and reverse the original posting (post as a negative value).
Seq | Column Name | Type | Lgth | DB DESC |
10 | Timesheet Date | Char | 10 | Last Day of the reporting period YYYY-MM-DD |
20 | Employee ID | Char | 12 | EMPLY_ID from profile (also the Vendor_ID) |
30 | Timesheet Type Code | Char | 2 | Initial posting hard Code: "R"; if adjustment: C (correction). From a process point net should be zero but there is no way to validate adjustments made to ensure this is the case. |
40 | Working State | Char | 2 | Blank |
50 | Fiscal Year | Char | 6 | Blank |
60 | Period | Numeric | 2 | Blank |
70 | Subperiod | Numeric | 2 | Blank |
80 | Correcting Ref Date | Char | 10 | Last Day of the reporting period, only supplied on Adjustments: YYYY-MM-DD |
90 | Pay Type | Char | 3 | R - OT - OS1, mapped to time code DESC (note: Leave codes are all mapped to R) |
100 | General Labor Category | Char | 6 | Blank |
110 | Timesheet Line Type Code | Char | 1 | Hard Code: "A" |
120 | Labor Cost Amount | Numeric | 15 | Hard Code: “000000000000000” |
130 | Hours | Numeric | 10 | hrs reported, will be negative for the originally posted hours in an adjustment |
140 | Workers' Comp Code | Char | 6 | Blank |
150 | Labor Location Code | Char | 6 | Blank (cannot be removed) |
160 | Organization ID | Char | 20 | DEPT_CODE - from employee profile |
170 | Account ID | Char | 15 | Blank |
180 | Project ID | Char | 30 | WBS Level 1 code as selected by the user (only lowest level will form part of the load), for Indirect Time codes: as associated with Time Code selected by user |
190 | Project Labor Category | Char | 6 | WBSL2 as selected by user - assignment from Workforce file |
200 | Reference Number 1 | Char | 20 | Blank |
210 | Reference Number 2 | Char | 20 | Blank |
220 | Organization Abbreviation | Char | 6 | Blank |
230 | Project Abbreviation | Char | 6 | Blank |
240 | Sequence Number | Numeric | 1 | Blank |
250 | Effective Billing Date | Char | 10 | Blank |
260 | Project-Account Abbrev. | Char | 6 | Blank |
270 | Multi-State Code | Char | 2 | Blank |
280 | Reference Sequence Num | Char | 1 | Blank |
290 | Timesheet Line Date | Char | 10 | Blank |
290 | Notes | Char | 254 | Blank |
Expense Extract Definition
Technical Specification
Attribute | Description |
Format | Fixed Length .txt file |
What to include | All fully approved, not already extracted expense reports. |
Level of detail | Separate Header per payment type Summarized Extract. Extract by Expense Reprot and unique account string |
Process | Expenses extracted from DATABASICS to CostPoint will be executed by Administrators. File extract will be posted to customer SFTP site, customer will import file into CostPoint according to CostPoint process. |
File Name | DBEXPENSE<datetimestamp>.txt |
PO Voucher Header
Seq No. | Column Name | Type | Lgth | DB DESC |
10 | Record Type | Character | 1 | Always H |
20 | Input Voucher Number | Number | 9 | Sequential counter for Vouchers. Sequential number in the extract per header, starting from ‘1” per CostPoint Voucher– Note: NOT DB ER number - one ER may be two CostPoint Vouchers, as we create a separate CostPoint Voucher per payment type in DB. |
30 | Fiscal Year | Character | 6 | Blank |
40 | Posting Period Number | Character | 2 | Blank |
50 | Subperiod Number | Character | 2 | Blank |
60 | Vendor ID | Character | 12 | By Payment Type If Reimbursable: EMPLY_ID from profile, If Non-reimbursable: ###### (from CC payment type DESC) |
70 | Terms | Character | 15 | Blank |
80 | Invoice Number | Character | 15 | DB-ER# |
90 | Invoice Date | Character | 10 | DB Post date – Date of Extract from DATABASICS |
100 | Invoice Amt | Number | 15 | Report Business Amount total By Payment Type with decimals implied (last two digits) |
110 | Discount Date | Character | 10 | Blank |
120 | Discount Percentage | Number | 6 | Blank |
130 | Total Disc Amt | Number | 15 | Blank |
140 | Due Date | Character | 10 | Blank |
150 | Hold Voucher | Character | 1 | Hard Code: N |
160 | Pay When Paid | Character | 1 | Hard Code: N |
170 | Pay Vendor ID | Character | 12 | By Payment Type Reimbursable: EMPLY_ID from profile, if Non-reimbursable: ##### (from CC payment type DESC) |
180 | Payment Address Code | Character | 10 | Blank |
190 | PO Number | Character | 10 | Blank |
200 | PO Release Number | Number | 3 | Blank |
210 | Retainage Rate | Number | 6 | Blank |
220 | Invoice Type | Character | 1 | Hard Code: N |
230 | Delivery Value (Ship Amt) | Number | 15 | Blank |
240 | Check Fiscal Year | Character | 6 | Blank |
250 | Check Period Number | Number | 2 | Blank |
260 | Check Subperiod Number | Number | 2 | Blank |
270 | Check Number | Number | 9 | Blank |
280 | Check Date | Character | 10 | Blank |
290 | Check Amount | Number | 15 | Blank |
300 | Discount Taken Amount | Number | 15 | Blank |
310 | Invoice Period of Performance Date | Character | 10 | Blank |
320 | Print Note-Blank Laser Check Flag | Character | 1 | Blank |
330 | Separate Check Flag | Character | 1 | Blank |
340 | Joint Payee Name | Character | 40 | Blank |
350 | Notes | Character | 254 | Report title as entered by user |
PO Voucher Line
Seq No. | Column Name | Type | Lgth | DB DESC |
10 | Record Type | Character | 1 | Hard Code: "L" |
20 | Input Voucher Number | Number | 9 | Tie in with Level H, above |
30 | Fiscal Year | Character | 6 | Blank |
40 | Voucher Line Num | Number | 6 | Sequential counter for lines on the Invoice - NOT DB Line number. Sequential number in the extract, starting from ‘1” per Invoice Number (above) |
50 | Account ID | Character | 15 | If Project has value in the DESC field post this value, else As associated with the Expense type / Project Type selected, |
60 | Org ID | Character | 20 | DEPT_CODE - from employee profile |
70 | Project ID | Character | 30 | As selected by user on WBSL Level 1 |
80 | Reference Num 1 | Character | 20 | Blank |
90 | Reference Num 2 | Character | 20 | Blank |
100 | Line Amt | Number | 15 | Transaction Amount - with decimals implied (last two digits) |
110 | Taxable Code | Character | 1 | Hard Code N |
120 | Sales Tax Code | Character | 6 | Blank |
130 | Sales Tax Amount | Number | 15 | Blank |
140 | Discount Amount | Number | 15 | Blank |
150 | Use Tax Amt | Number | 15 | Blank |
160 | AP 1099 Flag | Character | 1 | Blank |
170 | 1099-MISC Type Code | Character | 6 | Blank |
180 | Voucher Line Description | Character | 30 | Expense Type ID, plus remaining characters worth of user's line description |
190 | Org Abbreviation | Character | 6 | Blank |
200 | Project Abbreviation | Character | 6 | Blank |
210 | Project Account Abbreviation | Character | 6 | Blank |
220 | Notes | Character | 254 | Blank |