File Layout

The file layout provides information on the fields included in the input file created by the Import Timesheets from Deltek Time and Expense application.

You name the input file when exporting timesheets from Time & Expense. After you upload a file, it is renamed with an extension of ".ZZZ". If a file with the same name and extension already exists, it is overwritten. This helps prevent uploading the same file twice.

When the import process for Time and Expense completes, the application creates a confirmation file with an extension of ".CFM". This confirmation file contains error information if any error occurred during the import process.

Note: If you are using Deltek Time & Expense 7.x to 8.1.1, the file layout does not include the Withholding State field.

Input File Excel Template

Go to the Developer Resources page of the Costpoint Information Center to download the Excel template file specifically designed for this preprocessor. Templates for other Costpoint preprocessors are also available at the same location.

File Layout Information

# Field Name Length Column # Required/Optional Format/Source
1 ET Batch ID Char 10 1 - 10
2 Employee ID Char 12 11 - 22
3 Year Char 4 23 - 26
4 Period Number Char 3 27 - 29
5 Timesheet Schedule Char 10 30 - 39
6 Line Number Num 3 40 - 42
7 Timesheet Line Date Char 6 43 - 48 Required if the prorate Calculation Method is Days per Cycle (LAB_SETTINGS.prorate_calc_mthd = D). If Summary, fill with spaces.

Otherwise, TS Date (YYMMDD).

8 Timesheet Date Char 10 49 - 58 Required

(Default provided)

YYYY-MM-DD
9 Employee ID Char 12 59 - 70 Required
10 Timesheet Type Code Char 2 71 - 72 Required

(Default provided)

R, L, B, C, D, or N
11 Working State Char 2 73 - 74 Required

(Default provided)

12 Fiscal Year Char 6 75 - 80 Required

(Default provided)

13 Period Num 2 81 - 82 Required

(Default provided)

14 Sub Period Num 2 83 - 84 Required

(Default provided)

15 Correcting Ref Date Char 10 85 - 94 Optional YYYY-MM-DD
16 Pay Type Char 3 95 - 97 Required

(Default provided)

17 General Labor Category Char 6 98 - 103 Required

(Default provided)

18 Timesheet Line Type Code Char 1 104 Required

(Default provided)

A, B, L, M, S
19 Labor Cost Amount Num 15 105 - 119 Required

(Default provided)

99999999999.99
20 Hours Num 10 120 - 129 Required -999999.99
21 Worker’s Comp Code Char 6 130 - 135 Required

(Default provided)

22 Labor Location Code Char 6 136 - 141 Required if the Require Labor Location check box is selected on the Configure Labor Settings screen.

(Default provided)

23 Organization ID Char 20 142 - 161 Required

(Default provided)

24 Account ID Char 15 162 - 176 Required

(Default provided)

25 Project ID Char 30 177 - 206 Required if a Project Required account is specified.

(Default provided)

26 Project Labor Category Char 6 207 - 212 Required /Optional status dependent upon project setup

(Default provided)

27 Reference Number 1 Char 20 213 - 232 Optional

(Default provided)

28 Reference Number 2 Char 20 233 - 252 Optional

(Default provided)

29 Manufacturing Order ID Char 10 253 - 262 Required if the Timesheet Line Type Code is M.
30 Operation Sequence No Num 4 263 - 266 Required if Operation Step No is specified and Timesheet Line Type Code is M.
31 Operation Step No Num 3 267 - 269 Required if Operation Sequence No is specified and Timesheet Line Type Code is M.
32 Activity Type Char 1 270 Required if the Timesheet Line Type Code is M. R or S

If blank, Costpoint populates the timesheet with an R.

33 Work Center ID Char 12 271 - 282 If the Timesheet Line Type Code is M and no work center is provided in the input file, default the MO_ROUTING.wc_id where:

1. The MO_ROUTING.mo_id matches the timesheet line Manufacturing Order ID (field 29).

2. The MO_ROUTING.mo_oper_seq_no matches the timesheet line Operation Sequence No (field 30).

3. The MO_ROUTING.mo_oper_step_no matches the timesheet line Operation Step No (field 31).

34 Sales Order ID Char 10 283 - 292 Required if Timesheet Line Type Code is S.

(Not currently populated by Deltek Time & Expense)

35 SO Release No Num 2 293 - 294 Required if Timesheet Line Type Code is S.

(Not currently populated by Deltek Time & Expense)

36 SO Line No Num 2 295 - 296 Optional.

(Not currently populated by Deltek Time & Expense)

37 Organization Abbreviation Char 6 297 - 302 Optional
38 Project Abbreviation Char 6 303 - 308 Optional
39 Sequence Number Num 1 309

Required

If the timesheet type is Correcting, the application automatically increments the sequence number if a timesheet already exists for the employee/TS date/TS type/seq no (whether or not a sequence number is provided in the input file).

If this field is blank, the timesheet type is Regular, and no timesheet already exists for the Empl/TS Date/TS Type, 1 defaults. If this field is blank and a sequence 1 timesheet already exists for the Empl/TS Date/TS Type, an error message prints.

1 - 9
40 Effective Billing Date Char 10 310 - 319 Optional.

(Default provided if a PLC exists on the timesheet line.)

YYYY-MM-DD
41 Project-Account Abbreviation Char 6 320 - 325 Optional
42 Withholding State Char 2 326 - 327 Optional/Required. This value is required if you are using Deltek Time & Expense 8.1.2 and above and the Enable Multi-State Tax Withholding check box is selected on the Manage Pay Periods screen.
43 Future Use Char 46 329 - 375 Optional
44 Notes Char 254 376 - 629 Optional