File Layout
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.
If you are using Deltek Time & Expense 7.x to 8.1.1, the file layout does not include the Withholding State field.
2-Digit Manufacturing Order (MO) Operation Step Number
The import process uses the 2-Digit MO operation step number file layout if you apply either of the following settings on the Corporate Labor Settings subtask of the Configure Labor Settings screen:
- The Deltek Time & Expense Version is set to Time & Expense 8.3; or
- The Deltek Time & Expense Version is set to
Time & Expense 9.x and the
Use File Layout with a three-Digit MO Operation Step Number check box is not selected.
# 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 2 267 - 268 Required if Operation Sequence No is specified and Timesheet Line Type Code is M. 32 Activity Type Char 1 269 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 270 - 281 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 282 - 291 Required if Timesheet Line Type Code is S. (Not currently populated by Deltek Time & Expense)
35 SO Release No Num 2 292 - 293 Required if Timesheet Line Type Code is S. (Not currently populated by Deltek Time & Expense)
36 SO Line No Num 2 294 - 295 Optional. (Not currently populated by Deltek Time & Expense)
37 Organization Abbreviation Char 6 296 - 301 Optional 38 Project Abbreviation Char 6 302 - 307 Optional 39 Sequence Number Num 1 308 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 309 - 318 Optional. (Default provided if a PLC exists on the timesheet line.)
YYYY-MM-DD 41 Project-Account Abbreviation Char 6 319 - 324 Optional 42 Withholding State Char 2 325 - 326 Optional/Required. This value is required if you are using Deltek Time & Expense 8.1.2 and above and the EnableMulti-State Tax Withholding check box is selected on the Manage Pay Periods screen. 43 Future Use Char 46 327 - 374 Optional 44 Notes Char 254 375 - 628 Optional
3-Digit MO Operation Step Number
The import process uses the 3-digit MO operation step number file layout if you apply one of the following settings on the Corporate Labor Settings subtask of the Configure Labor Settings screen:
- The Deltek Time & Expense Version is set to Time & Expense 9.x and the Use File Layout with a three-digit MO Operation Step Number check box is selected; or
- The Deltek Time & Expense Version is set to
Time & Expense 10.x.
# 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