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.
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) Note:
This field is not used if you have a Costpoint Starter Edition license. The value defaults to 1 when imported. |
|
| 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) Note:
This field is not used if you have a Costpoint Starter Edition license. |
|
| 28 | Reference Number 2 | Char 20 | 233 - 252 | Optional
(Default provided) Note:
This field is not used if you have a Costpoint Starter Edition license. |
|
| 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 |