Deltek

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