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.
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:
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:
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 |