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

#	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

If the timesheet line type is M (Manufacturing 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	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-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

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