Input File Layout

The input file is named by the user.

The error file has the same name with an ".ERR" extension.

The input file can be either a fixed-format file or a comma-separated file. If the file is a fixed-format file, you must fill every position with either an appropriate character or a space. It is not necessary to zero-fill numbers; you can use spaces to maintain the proper format. If the file is comma-separated, it is not necessary to fill every position, but the file must have the correct number of commas. Each record must be on a separate line and end with a carriage return and line feed.

In fixed-format files, you must populate the required input file fields with valid data. If you do not populate an optional column in a fixed-length input file, you must enter the maximum number of spaces for that column before entering data for the next column. After you have entered the last character of meaningful data for the input file, you do not need to fill the remaining column lines with spaces. For example, if the Sequence Number field in position 217 is the last required value (out of a total of 487 possible field position spaces), you do not need to add the remaining 269 spaces. However, if you enter the Sequence Number in position 217 and wish to enter data in the Project-Account Abbrev field (starting at position 228) but not the Effective Billing Date field (starting at position 218), you must enter 10 spaces in place of the Effective Billing Date field before entering Project-Account Abbrev data in position 228. This also applies when you enter data in a required field, skip the following optional field, and then enter data in another required field.

Most users of earlier versions of Costpoint can continue to use the previous format, which did not include the Organization Abbreviation and the Project Abbreviation. If you are using the Comma-Separated Values format and 21 commas are present in the input file, the earlier format is presumed. There is one condition in which you cannot use the previous format. You must include the new field positions if:

  • You are using the Fixed-Length format,
  • You entered Notes, and
  • You did not pad the record to the previous full record length.

The current full record length for fixed-length records is 487. 

Note:

You cannot use commas in the Notes field.

Neither format requires that the Notes field be filled with spaces, but both require that every record end with a carriage return and line feed.

The Input File Layout

# Field Name Costpoint Table/Column Data Type and Size Column Required/Optional Defaulting Information Format/Contents
1 Timesheet Date TS_HDR and TS_LN.TS_DT Char 10 1–10 Required If a value is not supplied in the input file, the To date specified on the screen defaults. YYYY-MM-DD
2 Employee ID TS_HDR and TS_LN.EMPL_ ID Char 12 11-22 Required
3 Timesheet Type Code TS_HDR and TS_LN.S_TS_ TYPE_CD Char 2 23-24 Required If a value is not supplied in the input file, type R defaults. R, B, L, D, N, or C
4 Working State TS_HDR. WORK_STATE_CD Char 2 25-26 Required If a value is not supplied in the input file, the Default Overtime State from the Manage Employee Salary Information screen defaults.
5 Fiscal Year TS_HDR.FY_ CD Char 6 27-32 Required If not supplied in the input file, the Fiscal Year specified on the Import Timesheets screen defaults.
6 Period TS_HDR.PD_ NO Numeric 2 33-34 Required If not supplied in the input file, the Period specified on the Import Timesheets screen defaults. (99)
7 Subperiod TS_HDR.SUB_PD_NO Numeric 2 35-36 Required If not supplied in the input file, the Subperiod specified on the Import Timesheets screen defaults. (99)
8 Correcting Ref Date TS_HDR.CORRECTING_REF_DT Char 10 37-46 Optional YYYY-MM-DD
9 Pay Type TS_LN.PAY_TYPE Char 3 47-49 Required For default information, please see "Pay Type" in the Tables section of Processing Details.
10 General Labor Category TS_LN.GENL_LAB_CAT_CD Char 6 50-55 Required For default information, please see "General Labor Category" in the Tables section of Processing Details.
11 Timesheet Line Type Code TS_LN.S_TS_ LN_TYPE_CD Char 1 56-56 Required If a value is not supplied in the input file, type A defaults. A, B, L, M, S
12 Labor Cost Amount TS_LN.LAB_CST_AMT Numeric 15 57-71 Required For default information, please the "Labor Cost Amount" in the Tables section of Processing Details. (-99999999999.99)
13 Hours TS_LN.ENTERED_HRS and CHG_HRS Numeric 10 72-81 Required (-999999.99)
14 Workers' Comp Code TS_LN.WORK_COMP_CD Char 6 82-87 Required For default information, please see "Workers' Comp Code" in the Tables section of Processing Details.
15 Labor Location Code TS_LN.LAB_LOC_CD Char 6 88-93 Required if Require Labor Location is selected on the Configure Labor Settings screen. For default information, please see "Labor Location Code" in the Tables section of Processing Details.
16 Organization ID TS_LN.ORG_ID Char 20 94-113 Required For default information, please see "Organization" in the Tables section of Processing Details.
17 Account ID TS_LN.ACCT_ ID Char 15 114-128 Required For default information, please see "Account" in the Tables section of Processing Details.
18 Project ID TS_LN.PROJ_ ID Char 30 129-158 Required if Project Required is selected on the Manage Accounts screen for the specified account. For default information, please see "Project" in the Tables section of Processing Details.
19 Project Labor Category TS_LN.BILL_LAB_CAT_CD Char 6 159-164 Required/ Optional status dependent upon project setup. For default information, please see "Project Labor Category" in the Tables section of Processing Details.
20 Reference Number 1 TS_LN.REF_STRUC_1_ID Char 20 165-184 Optional For default information, please see "Reference Number 1" in the Tables section of Processing Details.
21 Reference Number 2 TS_LN.REF_STRUC_2_ID Char 20 185-204 Optional For default information, please see "Reference Number 2" in the Tables section of Processing Details.
22 Organization Abbreviation TS_LN.ORG_ABBRV_CD Char 6 205-210 Optional If not supplied in input file, the organization abbreviation defaults from the Manage Organization Elements screen.
23 Project Abbreviation TS_LN.PROJ_ABBRV_CD Char 6 211-216 Optional If not supplied in input file, the project abbreviation defaults from the Basic Info tab of the Manage Project User Flow.
24 Sequence Number TS_HDR and TS_LN.TS_HDR_SEQ_NO Numeric 1 217-217 Required If a value is not supplied in the input file, 1 defaults. 1 - 9
25 Effective Billing Date TS_LN.EFFECT_BILL_D Char 10 218-227 Optional If a value is not supplied in the input file, the Default Method selected in the Effective Bill Date Options group box on the Configure Labor Settings screen is used to determine the default value. YYYY-MM-DD
26 Project-Account Abbrev. TS_LN.PROJ_ACCT_ABBRV_CD Char 6 228-233 Optional If a value is not specified in the input file, the Proj-Acct Abbrev defaults from the Manage Project Account Groups screen.
27 Multi-State Code TS_LN.WH_ST_CD Char 2 234-235 Optional Required if the Multi State Withholding Taxes check box is selected on the Manage Pay Periods screen.
28 Reference Sequence Num TS_HDR.REFERENCE_

SEQ_NO

Char 1 236-236 Optional If not supplied and timesheet type is Labor Only, default 1.
29 Timesheet Line Date TS_LN.TS_LN_DT Char 10 237-246 Optional Required only when prorating with a Calculation Method of Days per Cycle. YYYY-MM-DD
30 Notes TS_LN.NOTES Char 254 247-500 Optional

Manufacturing Order Timesheets with 3-Digit Operation Step Numbers

If the timesheet line type is M (Manufacturing Order) with 3-digit operation step numbers, replace fields 22 through 30 with the following fields:

Note:

If you have 3-digit operation step numbers, you must select the Use File layout with a three-digit MO Operation Step Number check box on the Configure Labor Settings screen.

# Field Name Costpoint Table/Column Data Type and Size Column Required/ Optional Defaulting Information Format/Contents
22 Manufacturing Order ID TS_LN_MO.MO_ID Char 10 205-214 Required if the Timesheet Line Type Code is M.
23 Operation Sequence No TS_LN_MO.MO_ OPER_SEQ_NO Numeric 4 215-218 Required if the Operation Step No is specified.
24 Operation Step No TS_LN_MO.MO_OPER_STEP_NO Numeric 3 219-221 Required if the Operation Sequence No is specified.
25 Activity Type TS_LN_MO.S_ ACTIVITY_ TYPE Char 1 222 Required if the Timesheet Line Type Code is M. R or S
26 Work Center ID TS_LN_MO.WC_ID Char 12 223-234 Optional
27 Organization Abbreviation TS_LN.ORG_ ABBRV_CD Char 6 235-240 Optional If not supplied in input file, the organization abbreviation defaults from the Manage Organization Elements screen.
28 Project Abbreviation TS_LN.PROJ_ ABBRV_CD Char 6 241-246 Optional If not supplied in the input file, the project abbreviation defaults from the Basic Info tab of the Manage Project User Flow.
29 Sequence Number TS_HDR and TS_LN.TS_HDR_SEQ_NO Numeric 1 247 Required If a value is not supplied in the input file, 1 defaults. 1 - 9
30 Effective Billing Date TS_LN.EFFECT_BILL_DT Char 10 248-257 Optional If a value is not supplied in the input file, the Default Method selected in the Effective Bill Date Options group box on the Configure Labor Settings screen is used to determine the default value. YYYY-MM-DD
31 Project-Account Abbrev TS_LN.PROJ_ACCT_ABBRV_CD Char 6 258-263 Optional If not specified in the input file, the project account abbreviation defaults from the Manage Project Account Groups screen.
32 Multi-State Code TS_LN.WH_ST_CD Char 2 264-265 Optional Required if the Multi State Withholding Taxes check box is selected on the Manage Pay Periods screen.
33 Reference Seq Num TS_HDR.REFERENCE_SEQ_NO Char 1 266 Optional If not supplied and timesheet type is Labor Only, default 1.
34 Timesheet Line Date TS_LN.TS_LN_DT Char 10 267-276 Optional Required only when prorating with a Calculation Method of Days per Cycle. YYYY-MM-DD
35 Notes TS_LN.NOTES Char 254 277-530 Optional
36 Transaction ID TS_LN.TRANS_ID Numeric 8 N/A Optional

Manufacturing Order Timesheets with 2-Digit Operation Step Numbers

If the timesheet line type is M (Manufacturing Order) with 2-digit operation step numbers, replace fields 22 through 30 with the following fields:

Note:

If you have 2-digit operation step numbers, you must clear the Use File layout with a three-digit MO Operation Step Number check box on the Configure Labor Settings screen. However, if you are using Time & Expense version 10.x or higher, the check box will be unavailable.

# Field Name Costpoint Table/Column Data Type and Size Column Required/ Optional Defaulting Information Format/Contents
22 Manufacturing Order ID TS_LN_MO.MO_ID Char 10 205-214 Required if the Timesheet Line Type Code is M.
23 Operation Sequence No TS_LN_MO.MO_ OPER_SEQ_NO Numeric 4 215-218 Required if the Operation Step No is specified.
24 Operation Step No TS_LN_MO.MO_OPER_STEP_NO Numeric 2 219-220 Required if the Operation Sequence No is specified.
25 Activity Type TS_LN_MO.S_ ACTIVITY_ TYPE Char 1 221 Required if the Timesheet Line Type Code is M. R or S
26 Work Center ID TS_LN_MO.WC_ID Char 12 222-233 Optional
27 Organization Abbreviation TS_LN.ORG_ ABBRV_CD Char 6 234-239 Optional If not supplied in input file, the organization abbreviation defaults from the Manage Organization Elements screen.
28 Project Abbreviation TS_LN.PROJ_ ABBRV_CD Char 6 240-245 Optional If not supplied in the input file, the project abbreviation defaults from the Basic Info tab of the Manage Project User Flow.
29 Sequence Number TS_HDR and TS_LN.TS_HDR_SEQ_NO Numeric 1 246 Required If a value is not supplied in the input file, 1 defaults. 1 - 9
30 Effective Billing Date TS_LN.EFFECT_BILL_DT Char 10 247-256 Optional If a value is not supplied in the input file, the Default Method selected in the Effective Bill Date Options group box on the Configure Labor Settings screen is used to determine the default value. YYYY-MM-DD
31 Project-Account Abbrev TS_LN.PROJ_ACCT_ABBRV_CD Char 6 257-262 Optional If not specified in the input file, the project account abbreviation defaults from the Manage Project Account Groups screen.
32 Multi-State Code TS_LN.WH_ST_CD Char 2 263-264 Optional Required if the Multi State Withholding Taxes check box is selected on the Manage Pay Periods screen.
33 Reference Seq Num TS_HDR.REFERENCE_SEQ_NO Char 1 265 Optional If not supplied and timesheet type is Labor Only, default 1.
34 Timesheet Line Date TS_LN.TS_LN_DT Char 10 266-275 Optional Required only when prorating with a Calculation Method of Days per Cycle. YYYY-MM-DD
35 Notes TS_LN.NOTES Char 254 276-529 Optional
36 Transaction ID TS_LN.TRANS_ID Numeric 8 N/A Optional

Sales Order Timesheets

If the Timesheet Line Type is S (Sales Order), replace fields 22 through 30 with the following fields:

# Field Name Costpoint Table/Column Data Type and Size Column Required/ Optional Defaulting Information Format/Contents
22 Sales Order ID TS_LN_SO.SO_ ID Char 10 205-214 Required if the Timesheet Line Type Code is S.
23 SO Release No TS_LN_SO.SO_RLSE_NO Numeric 2 215-216 Required if the Timesheet Line Type Code is S.
24 SO Line No TS_LN_SO.SO_LN_KEY Numeric 2 217-218 Optional
25 Organization Abbreviation TS_LN.ORG_ABBRV_CD Char 6 219-224 Optional If not supplied in input file, the organization abbreviation defaults from the Manage Organization Elements screen.
26 Project Abbreviation TS_LN.PROJ_ ABBRV_CD Char 6 225-230 Optional If not supplied in input file, the project abbreviation defaults from the Basic Info tab of the Manage Project User Flow.
27 Sequence Number TS_HDR and TS_LN.TS_HDR_SEQ_NO Numeric 1 231 Required If not supplied in the input file, 1 defaults. 1 - 9
28 Effective Billing Date TS_LN.EFFECT_BILL_DT Char 10 232-241 Optional If a value is not supplied in the input file, the Default Method selected in the Effective Bill Date Options group box on the Configure Labor Settings screen is used to determine the default value. YYYY-MM-DD
29 Project-Account Abbrev. TS_LN.PROJ_ ACCT_ABBRV_CD Char 6 242-247 Optional If not specified in the input file, the project account abbreviation defaults from Manage Project Account Groups screen.
30 Multi-State Code TS_LN.WH_ST_CD Char 2 248-249 Optional Required if the Multi State Withholding Taxes check box is selected on the Manage Pay Periods screen.
31 Reference Sequence Num TS_HDR.REFERENCE_SEQ_NO Char 1 250-250 Optional If not supplied and timesheet type is Labor Only, default 1.
32 Timesheet Line Date TS_LN.TS_LN_DT Char 10 251-260 Optional Required only when prorating with a Calculation Method of Days per Cycle. YYYY-MM-DD
33 Notes TS_LN.NOTES Char 254 261-514 Optional