...
With DATABASICS and Deltek, federal contractors can solve their most difficult time and expense challenges, while providing the simple user experience, including mobile apps, that today’s organizations demand. DATABASICS has integrated with Deltek for more than a decade. The integration is highly efficient, can run automatically, and is totally reliable.
General Load Guidelines
DATABASICS allows data to be entered into the application two main ways, through load according to a specification given by DATABASICS or entered manually in to the screen.
The customer will be maintaining data from files according to specification in this document. Most data originates from CostPoint, but some may be maintained external to CostPoint. Data loads can be run manually, or on a scheduled basis, a “profile” will execute the load, from a predefined SFTP Location dedicated to the Customer.
Load files should not contain a header line
All load files should be presented as tab delimited .txt files
No double quotes [“] are allowed in any field
ID fields must be upper case and alpha numeric [0-9] [A-Z] [-_.], with no special characters or spaces (“&” cannot form part of the ID field in the loads).
File loads will be full. This means that all active records will be included in the load, and if a record is not included in the load this record will be set to inactive.
This load approach is configurable and has an alternative in a partial setting, where only new or changed records will be included in the load file, but the load itself includes an active y/n flag.
Organization Structure Mapping
...
Operating Unit will be mapped to Deltek Company Id
Department will be mapped to Org ID or PLC Code in Deltek
Employee data will be loaded from Deltek for the following fields:
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 |