Input File and Error File Layout

The input file can be a fixed length or delimited ASCII text file named by the user. The error file, which contains records that were not processed, has the same name as the input file but with an extension of .ERR.

After processing is complete, you can rename the input file with an extension of .OLD. The layout of the error file is the same as the layout of the input file.

In the input file, the Sales Order Header record must precede the associated Sales Order Line records, which in turn must precede the associated SO Line Notes records. A Header record cannot be inserted without at least one valid Line record, and a Line record cannot be inserted without a corresponding valid Header record. Likewise, Notes records can be inserted only when there is a corresponding valid Line record.

The fields in a delimited input file are separated by a comma or other user-defined symbol, as indicated in the File Delimiter drop-down list. You do not need to include a comma (or user-defined symbol) after the last field entry in the input file.

Each field in a fixed-length input file contains a set number of characters and must be filled with either the appropriate character or space to the specified width. ASCII text files do not have commas to separate the fields. It is not necessary to zero fill numbers; you can use spaces to maintain the proper format. Numeric fields should be right justified; character fields should be left justified. Each record must be on a separate line and end with a carriage return.

You must populate the required input file fields with valid data. If an optional column is not populated in a fixed-length input file, you must enter the maximum number of spaces for that column before entering data for the next column. Once 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, in the SO Header input file (total of 967 possible field position spaces), if the last character of data ends at position 818 (Sales Order Status Code), you do not need to add the remaining 149 spaces. However, if you enter the Sales Order Status Code in position 818 and wish to enter data in the Rate Group field (starting at position 849) but not the Ship Via field (starting at position 819), you must enter 30 spaces in place of the Ship Via and Customer Terms fields before entering Rate Group data in position 849. This also applies when you enter data in a required field, skip the following optional field, and then proceed to enter data in another required field. Similarly, if the Customer ID (starting at position 18) is only 6 characters long, you must enter the 6 characters plus six spaces before you enter the Due Date beginning in position 30.

Note: Input file fields defined as dates in Costpoint must be eight characters and formatted as MMDDYYYY, with a two-character month (01-12), a two-character day (01-31), and four-character year (including century). Input file field types are alphanumeric or characters (VARCHAR), numeric (DECIMAL, SMALLINT, and INTEGER), and date. The Starting and Ending Position columns below indicate the number of characters allowed for each field in a fixed-length input file. Decimal values that are "5,4" can be entered, for example, with a maximum of six (6) characters, including the decimal ("9.9999"). Decimal values that are "14,4" can be entered, for example, with a maximum of 15 characters, including the decimal ("9999999999.9999"). Decimal values should not include comma separators.

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.

SO Header Record Format (SO_HDR)

Seq. No.	Column Name	Costpoint Column	Number of Characters	Starting Position	Ending Position	Required, Optional, N/A (Not Used)	Format/Notes
1	Record Type	N/A	Character 1	1	1	Required	H
2	Government Payment Office	BILL_PMT_ADDR_CD	Character 6	2	7	Optional
3	Customer Bill To	BILL_TO_ADDR_DC	Character 10	8	17	Optional
4	Customer ID	CUST_ID	Character 12	18	29	Required
5	Due Date	DUE_DT	Date	30	37	Optional	MMDDYYYY Required in the input file for either SO header or SO line. Due Date is required in the SO header if any associated line does not have a Due Date.
6	Customer Purchase Order	EXT_CUST_PO_ID	Character 30	38	67	Optional
7	Order Date	ORD_DT	Date	68	75	Required	MMDDYYYY
8	ACRN (Accounting Classification Reference Number)	ACRN_ID_S	Character 30	76	105	Optional
9	Bill Cycle	BILL_CYCLE_CD	Character 6	106	111	Optional
10	Blanket Amount	TRN_BLKT_AMT	Number 15	112	126	N/A	For fixed-length format, leave 15 spaces if proceeding from Bill Cycle to Project To Charge. For delimited format, include a delimiter.
11	Project To Charge	CHG_PROJ_ID	Character 30	127	156	Optional
12	Contact First Name	CNTACT_FIRST_  NAME	Character 25	157	181	Optional
13	Contact Last Name	CNTACT_LAST_  NAME	Character 25	182	206	Optional
14	Contractor Address Code	CONTR_ADDR_  CD	Character 6	207	212	Optional
15	Customer Terms (key)	CUST_TERMS_  KEY	Number 9	213	221	Optional	(999999999)
16	Deliver To	DEL_TO_FLD	Character 25	222	246	Optional
17	Desired Date	DESIRED_DT	Date	247	254	Optional	MMDDYYYY
18	Customer Mod Number	EXT_CUST_MOD_ID	Character 10	255	264	Optional
19	Contact Fax ID	FAX_ID	Character 25	265	289	Optional
20	FOB	FOB_FLD	Character 15	290	304	Optional
21	International Shipment	INTL_SHIP_FL	Character 1	305	305	Optional
22	Issued by Office	ISSUE_BY_ADDR_CD	Character 6	306	311	Optional
23	Mark for Address Code	MARK_FOR_  ADDR_DC	Character 10	312	321	Optional
24	Overshipment Flag	OVRSHP_ALLOW_FL	Character 1	322	322	Optional
25	Partial Shipment Flag	PARTIAL_SHIP_  FL	Character 1	323	323	Optional
26	Blanket Performance End Date	PERF_END_DT	Date	324	331	N/A	For fixed-length format, leave 16 spaces if proceeding from Partial shipment Flag to Contact Phone Number. For delimited format, include two delimiters.
27	Blanket Performance Start Date	PERF_START_DATE	Date	332	339	N/A
28	Contact Phone Number	PHONE_ID	Character 25	340	364	Optional
29	Pricing Catalog	PRICE_CATLG_  CD	Character 10	365	374	Optional
30	Pricing Project	PRICE_PROJ_ID	Character 30	375	404	Optional
31	Prime Contract ID	PRIME_CONTR_  ID	Character 20	405	424	Optional
32	Quote ID	QUOTE_ID	Character 10	425	434	Optional
33	Recurring Item End Date	RECUR_END_DT	Date	435	442	Optional	MMDDYYYY
34	Recurring Item Start Date	RECUR_START_  DT	Date	443	450	Optional	MMDDYYYY
35	Remit To Address Code	REMIT_ADDR_CD	Character 6	451	456	Optional
36	Sales Group Abbreviation Code	SALES_ABBRV_  CD	Character 6	457	462	Optional
37	Sales Representative ID	SALES_REP_ID	Character 12	463	474	Optional
38	Ship By Date	SHIP_BY_DT	Date	475	482	Optional	MMDDYYYY
39	Ship From Address Code	SHIP_FROM_  ADDR_CD	Character 6	483	488	Optional
40	Ship To Address Code	SHIP_ID (derived)	Character 20	489	508	Optional
41	Sales Order Change Number	SO_CHNG_ORD_NO	Number 4	509	512	N/A	For fixed-length format, leave four spaces if proceeding from Ship To Address Code to Sales Order ID. For delimited format, include one delimiter.
42	Sales Order ID	SO_ID	Character 10	513	522	Optional	Sales Order ID is required if it is not auto-assigned per Configure Sales Order Entry Settings screen. (See Note at end of table.)
43	Sales Order Notes	SO_NOTES_NT	Character 254	523	776	Optional
44	SO Blanket Release Number	SO_RLSE_NO	Number 4	777	780	N/A	For fixed-length format, leave 19 spaces if proceeding from Sales Order Notes to Volume Discount Percentage. For delimited form, include two delimiters.
45	Total Release Amount	TRN_TOT_RLSED_AMT	Number 15	781	795	N/A
46	Volume Discount Percentage	VOL_DISC_PCT_  RT	Number 6 (Decimal 5,4)	796	801	Optional	(9.9999)
47	Default Taxable Flag	DFLT_TAXABLE_FL	Character 1	802	802	Optional
48	GSA Flag	GSA_FL	Character 1	803	803	Optional
49	Inventory Abbreviation Code	INVT_ABBRV_CD	Character 6	804	809	Optional	For fixed length file format, limit to 6 characters. For delimited file format, increase field size to 20.
50	Sales Order Approval Process Code	OE_APPR_PROC_CD	Character 8	810	817	Optional
51	Sales Order Status Code	S_SO_STATUS_  CD	Character 1	818	818	Optional
52	Ship Via field	SHIP_VIA_FLD	Character 15	819	833	Optional
53	Customer Terms Description	Used to assign CUST_TERMS_  KEY	Character 15	834	848	Optional
54	Rate Group	RATE_GRP_ID	Character 6	849	854	Optional
55	Transaction Currency	TRN_CRNCY_CD	Character 3	855	857	Optional
56	Rate Date	TRN_CRNCY_DT	Date	858	865	Optional	MMDDYYYY
57	Tax ID	VAT_TAX_ID	Character 20	866	885	Optional
58	Destination Country	EC_COUNTRY_  DEST_CD	Character 3	886	888	Optional
59	Permit/License ID	PERMIT_ID	Character 20	889	908	Optional
60	Declaration Point	DECL_POINT_CD	Character 5	909	913	Optional
61	Traffic Direction	TRAFFIC_DIR_CD	Character 1	914	914	Optional
62	Mode of Transportation	MODE_OF_  TRANSP_NO	Number 2	915	916	Optional	(99)
63	Intrastat Reference Number	INTRASTAT_REF_NO	Character 14	917	930	Optional
64	Commission	COMM_PCT_RT	Number 6 (Decimal 5,4)	931	936	Optional	(9.9999)
65	Revenue Recognition	S_REV_RECOG_  CD	Character 1	937	937	Optional	S: Shipment Sales U: Unbilled A/R D: Deferred Revenue L: Liquidations R: Ready for Use P: Prepay
66	Enable SO Invoices for WAWF	USE_WAWF_FL	Character 1	938	938	Optional
67	Prime Contractor CAGE Code	CONTR_CAGE_ID	Character 5	939	943	Optional
68	Inspect By DoDAAC	INSPECT_BY_ADDR_CD	Character 6	944	949	Optional
69	Local Processing Office DoDAAC	LOCAL_PROC_ADDR_CD	Character 6	950	955	Optional
70	3rd Party/  Other Office DoDAAC	OTHER_OFFC_ADDR_CD	Character 6	956	961	Optional
71	Inspection Point	S_INSPECT_PT_CD	Character 1	962	962	Optional	S: Source D: Destination O: Other
72	Acceptance Point	S_ACCEPT_PT_CD	Character 1	963	963	Optional	S: Source D: Destination O: Other
73	FOB Point	S_WAWF_FOB_CD	Character 1	964	964	Optional	S: Source D: Destination O: Other
74	Alternate Release Procedure	ARP_FL	Character 1	965	965	Optional
75	Certificate of Conformance	COC_FL	Character 1	966	966	Optional
76	Construction Certificate	CONSTR_FL	Character 1	967	967	Optional

Note: The application first looks for the Sales Order ID in the input file. If the ID is unavailable, it checks to see whether the SO ID is auto-assigned—this occurs when the Overall System check box is selected and the Last System SO Number field is populated on the Configure Sales Order Entry Settings screen. Refer to the Configure Sales Order Entry Settings screen documentation for additional details.

SO Line Record (SO_LN)

Seq. No.	Column Name	Costpoint Column	Number of Characters	Starting Position	Ending Position	Required, Optional, N/A (Not Used)	Format/Notes
1	Record Type	N/A	Character 1	1	1	Required	(L)
2	Gross Unit Price	TRN_GR_UNIT_PR_AMT	Number 15 (Decimal 14,4)	2	16	Required	(9999999999.9999)
3	Item	ITEM_ID	Character 30	17	46	Optional	For fixed length file format, limit to 30 characters. For delimited file format, increase field size to 50.
4	Item Key	ITEM_KEY	Number 9	47	55	Optional	(999999999)
5	Item Revision	ITEM_RVSN_ID	Character 3	56	58	Optional	For fixed length file format, limit to 3 characters. For delimited file format, increase field size to 10.
6	Quantity Ordered	ORD_QTY	Number 15 (Decimal 14,4)	59	73	Required	(9999999999.9999)
7	Sales Order Line Description	SO_LN_DESC	Character 60	74	133	Optional
8	Taxable Flag	TAXABLE_FL	Character 1	134	134	Required
9	ACRN (Accounting Classification Reference Number)	ACRN_ID	Character 30	135	164	Optional
10	Bill Cycle	BILL_CYCLE_CD	Character 6	165	170	Optional
11	BOM Configuration ID	BOM_CONFIG_ID	Character 10	171	180	Optional
12	CLIN	CLIN_ID	Character 10	181	190	Optional
13	Deliver To	DEL_TO_FLD	Character 25	191	215	Optional
14	Desired Date	DESIRED_DT	Date	216	223	Optional	MMDDYYYY
15	Discount Percentage	DISC_PCT_RT	Number 6 (Decimal 5,4)	224	229	Optional	(9.9999)
16	Due Date	DUE_DT	Date	230	237	Optional	MMDDYYYY Required in the input file for either SO header or SO line. Due Date in the SO line is required if SO header does not have a Due Date.
17	Customer Item ID	EXT_CUST_ITEM_ID	Character 30	238	267	Optional
18	Milstrip ID	MILSTRIP_ID	Character 30	268	297	Optional
19	Miscellaneous Line Charge Type	MISC_LN_CHG_ TYPE	Character 6	298	303	Optional
20	Model ID	MODEL_ID	Character 30	304	333	Optional
21	National Stock Number	NSN_ID	Character 20	334	353	Optional
22	Overshipment Flag	OVRSHP_  ALLOW_FL	Character 1	354	354	Optional
23	Quote ID	QUOTE_ID	Character 10	355	364	Optional
24	Recurring Item End Date	RECUR_END_DT	Date	365	372	Optional	MMDDYYYY
25	Recurring Item Start Date	RECUR_START_DT	Date	373	380	Optional	MMDDYYYY
26	Sales Group Abbrev Code	SALES_ABBRV_CD	Character 6	381	386	Optional
27	Sales Tax/VAT Code	SALES_TAX_CD	Character 6	387	392	Optional
28	Ship By Date	SHIP_BY_DT	Date	393	400	Optional	MMDDYYYY
29	Ship to Address Code	SHIP_ID	Character 20	401	420	Optional
30	Ship Via Field	SHIP_VIA_FLD	Character 15	421	435	Optional
31	Sales Order ID	SO_ID	Character 10	436	445	Optional	Must be valid in the Sales Order Header table. SO ID defaults based on SO Header Record Format.
32	Additional Misc Line Charge	N/A	15	446	460	N/A	For fixed-length format, leave 15 spaces if proceeding from Sales Order ID to Sales Order Line Number. For delimited format, include a delimiter.
33	Sales Order Line Number	SO_LN_NO	Number 9	461	469	Required	(999999999)
34	Sales Order Blanket Release Number	SO_RLSE_NO	Number 4	470	473	N/A	For fixed-length format, leave four spaces if proceeding from Sales Order Line Number to Sales Order Line Key. For delimited format, include a delimiter.
35	Sales Order Line Key	SO_LN_KEY	Number 9	474	482	Optional	(999999999)
36	Unit of Measure	UM_CD	Character 3	483	485	Optional
37	Inventory Abbreviation Code	INVT_ABBRV_CD	Character 6	486	491	Optional	For fixed length file format, limit to 6 characters. For delimited file format, increase field size to 20.
38	Extended Amount	TRN_SO_LN_EXT_AMT	Number 15 (Decimal 14,2)	492	506	Optional	(999999999999.99)
39	Warehouse ID	WHSE_ID	Character 8	507	514	Optional
40	Sales Order Line Type	S_SO_LN_TYPE	Character 3	515	517	Optional
41	Charge Project ID	CHG_PROJ_ID	Character 30	518	547	Optional
42	Sales Representative ID	SALES_REP_ID	Character 12	548	559	Optional
43	Mass	MASS_QTY	Number 13 (Decimal 12,1)	560	572	Optional	(99999999999.9)
44	Mass U/M	MASS_UM_CD	Character 3	573	575	Optional
45	NOTC	NOTC_NO	Number 2	576	577	Optional	(99)
46	Correction	CORRECTION_CD	Character 1	578	578	Optional
47	Commodity	COMM_CD	Character 15	579	593	Optional
48	Supplementary Units	SUPPL_UNITS_NO	Number 4	594	597	Optional	(9999)
49	Country of Origin	EC_COUNTRY_ORIG_CD	Character 3	598	600	Optional
50	Commission	COMM_PCT_RT	Decimal 6	601	606	Optional	(9.9999)
51	License Type	LICENSE_TYPE_ID	Character 6	607	612	Optional
52	Platform Type	PLATFORM_TYPE_ID	Character 12	613	624	Optional
53	Number of Users	USERS_NO	Number 5	625	629	Optional	(99999)
54	Version	VERSION_ID	Character 6	630	635	Optional
55	Amortization Schedule Code	AMORT_CD	Character 6	636	641	Optional
56	PO Ship ID	PO_SHIP_ID	Character 20	642	661	Optional
57	Consume Forecast	CONSUME_FORECST_FL	Character 1	662	662	Optional
58	GFE	GFE_FL	Character 1	663	663	Optional
59	SDN	SDN_ID	Character 30	664	693	Optional
60	Product Service Type	S_PROD_SVC_TYPE_CD	Character 2	694	695	Optional
61	UID Required	UID_REQD_FL	Character 1	696	696	Optional

SO Line Notes Record (SO_LN_NOTES)

Seq. No.	Column Name	Costpoint Column	Number of Characters	Starting Position	Ending Position	Required, Optional, N/A (Not Used)	Format/Notes
1	Record Type	N/A	Character 1	1	1	Required	(N)
2	Sales Order ID	SO_ID	Character 10	2	11	Optional	Must be valid in the Sales Order Header table. SO ID defaults depending on SO Header Record Format.
3	SO Blanket Release Number	SO_RLSE_NO	Number 4	12	15	N/A	For fixed-length format, leave four spaces if proceeding from Sales Order ID to Sales Order Line Number. For delimited format, include a delimiter.
4	SO Line Number	SO_LN_NO	Number 9	16	24	Required	(999999999)
5	Line Notes	SO_LN_TX	Character 1,000	25	1,024	Required

Line Notes must be sequenced after the associated SO Line row, and before another SO Line row in the input file.

SO Line Text Record (SO_LN_TEXT)

Seq. No.	Column Name	Costpoint Column	Number of Characters	Starting Position	Ending Position	Required, Optional, N/A (Not Used)	Format/Notes
1	Record Type	N/A	Character 1	1	1	Required	(T)
2	Sales Order ID	SO_ID	Character 10	2	11	Optional	Must be valid in the Sales Order Header table. SO ID defaults based on SO Header Record Format.
3	SO Blanket Release Number	SO_RLSE_NO	Number 4	12	15	N/A	Leave four spaces if proceeding from Sales Order ID to Sales Order Line Number.
4	SO Line Number	SO_LN_NO	Number 9	16	24	Required
5	Text Code	TEXT_CD	Character 10	25	34	Required	Text Code/Revision combination: - must not be a duplicate of another new/existing line text row - must exist for login user’s company - must be active
6	Sequence Number	SEQ_NO	Number 4	35	38	Required
7	SO Text Source Code	S_TEXT_SRCE_CD	Character 1	39	39	Required
8	Text Revision	TXT_CD_RVSN_NO	Number 5			Optional	If not provided, Costpoint defaults the latest active revision for the company. Text Code/Revision combination must exist in the Standard Text table and in the Text Where Used table with a Where Used Code of S (Sales Order Acknowledgment). Text Code/Revision combination: - must not be a duplicate of another new/existing line text row - must exist for login user’s company - must be active

Line Text must be sequenced after the associated SO Line row, and before another SO Line row in the input file.

SO Text Record (SO_TEXT)

Seq. No.	Column Name	Costpoint Column	Number of Characters	Starting Position	Ending Position	Required, Optional, N/A (Not Used)	Format/Notes
1	Record Type	N/A	Character 1	1	1	Required	(X)
2	Sales Order ID	SO_ID	Character 10	2	11	Optional	Must be valid in the Sales Order Header table. SO ID defaults based on SO Header Record Format.
3	SO Blanket Release Number	SO_RLSE_NO	Number 4	12	15	N/A	Leave four spaces if proceeding from the Sales Order ID to the Text Code.
4	Text Code	TEXT_CD	Character 10	16	25	Required	Text code/revision must exist in the Standard Text table. Text code/revision must exist in the Standard Text - Where Used table - with a code of S (Sales Order Acknowledgment), C (Sales Order Packing Slip), D (Sales Order DD250 Invoice), I (Sales Order Invoice), K (Sales Order DD250 Packing Slip), or U (Sales Order Quotation).
5	Sequence Number	SEQ_NO	Number 4	26	29	Required
6	Text Revision	TXT_CD_RVSN_NO	Number 5			Optional	If not provided, Costpoint defaults the latest active revision for the company. Text Code/Revision combination must exist in the Standard Text table. Text code/revision must exist in the Standard Text - Where Used table - with a code of S (Sales Order Acknowledgment), C (Sales Order Packing Slip), D (Sales Order DD250 Invoice), I (Sales Order Invoice), K (Sales Order DD250 Packing Slip), or U (Sales Order Quotation).

Costpoint Online Help