INPUT FILES

Input files must be either an ASCII fixed-length text file with the file extension .DAT, that is, filename.DAT, or a comma-separated file with the file extension .CSV, that is, filename.CSV, to upload successfully. Each record in an input file must be on a separate line. Field sizes and validations are the same for both .CSV and .DAT formats, except that fields in comma-separated formats are separated by commas, and fields in ASCII formats are padded by spaces and/or zeros. If a condition is not met for a field that is being processed in an input file record, an error will occur and an error file will be created. Refer to the Output Tables topic for additional information on error output files.

After the process completes, the Input file is renamed with the extension .OLD and saved in the same directory.

Input File Layout

Every position in an ASCII fixed-length format input file (.DAT) must be filled with the appropriate number of either characters or spaces for a given column before you enter data for the next column. 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. Once you have entered the last character of meaningful data for the input file, it is not necessary to fill the remaining column lines with spaces.

For example, if the maximum line length (i.e., the entire input file) is a possible total of 759 characters and the last character of meaningful data is in position 480 (i.e., Reason Code), you do not need to add the remaining 270 spaces. However, if the Packing Slip ID (beginning at position 23 on the input file) is only seven characters long, you must include the Packing Slip ID plus eight additional spaces before entering the PO Number in position number 38.

The following table presents the input file fields in the order required.

Input file fields that are defined as dates in Costpoint must be 10 characters and formatted as "MM/DD/YYYY," with a two-character month (01-12), a two-character day (01-31) and a four-character year. 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.

Field Name	Costpoint Column	Field Type, Max Length	Starting Position	Ending Position	Required, Optional, or Not Used	Notes
Record Type	N/A	Character, 2	1	2	R	Must be "RL" or "RA." "RL" changes existing receipt information; "RA" creates new receipts.
Receipt ID	RECPT_ID	Alphanumeric, 10	3	12	R	Warehouse ID and Receipt ID must be unique if record type is "RA." Must exist for "RL" record types.
Receipt Date	RECPT_DT	Date, 10 (MM/DD/YYYY)	13	22	O	Defaults to current system date if missing or invalid. If entered, must be within an open accounting period.
Packing Slip ID	PS_ID	Alphanumeric, 15	23	37	O	From input file if entered. Packing slip number is required if Require Packing Slip checkbox is selected in the Receiving Settings screen, for the user’s company.
PO Number	PO_ID	Alphanumeric, 10	38	47	R	Must exist in PO Header (PO_HDR) and PO Line (PO_LN) tables.
PO Line Number	PO_LN_NO	Numeric, 4	48	51	R	Must exist in PO line (PO_LN) table.
PO Release Number	PO_RLSE_NO	Numeric, 3	52	54	R	Must exist in PO Header (PO_HDR) and PO Line (PO_LN) tables.
PO Change Order Number	PO_CHNG_ ORD_NO	Numeric, 3	55	57	Not used.	From PO.
Receipt Line Description	RECPT_LN_ DESC	Alphanumeric, 60	58	117	Not used.	From PO Line.
Fiscal Year Code	FY_CD	Alphanumeric, 5	118	122	Not used.	Looks up from receipt date.
Period Number	PD_NO	Numeric, 2	123	124	Not used.	Looks up from receipt date.
Subperiod	SUB_PD_NO	Numeric, 2	125	126	Not used.	Looks up from receipt date.
Received Quantity	RECV_QTY	Numeric, 16/Decimal, 14,4 (+9999999999.9999)	127	142	O	If record type is "RL," Accepted Quantity + Rejected Quantity cannot exceed Received Quantity.
Unit of Measure	UM	Alphanumeric, 3	143	145	Not used.	From PO Line U/M.
Received Amount	RECV_AMT	Numeric, 16/Decimal 14,2 (+999999999999.99)	146	161	O	Calculated from Received Quantity * Gross Unit Cost Amount on PO line.
Accepted Amount	ACCPT_AMT	Numeric, 16/Decimal 14,2 (+999999999999.99)	162	177	O	Calculated from Accepted Quantity * Gross Unit Cost Amount on PO line.
Accepted Location	ACCPT_LOC_ ID	Alphanumeric, 15	178	192	Not used.
Accepted Quantity	ACCPT_QTY	Numeric, 16/Decimal 14,4 (+9999999999.9999)	193	208	O	If not entered, will be set to zero.
Material Handler Employee ID	MATL_HNDLR_EMPL_ID	Alphanumeric, 12	209	220	R	Must be an active employee ID in the Employee » Basic Employee Info screen.
Inspection Accepted Amount	INSP_ACCPT_ AMT	Numeric, 16	221	236	Not used.
Inspection Accepted Quantity	INSP_ACCPT_ QTY	Numeric, 16	237	252	Not used.
Inventory Abbreviation Code	INVT_ABBRV_ CD	Alphanumeric, 6	253	258	Not used.
Item ID	ITEM_ID	Alphanumeric, 30	259	288	Not used.	Uses PO Line item.
Item Revision ID	ITEM_RVSN_ ID	Alphanumeric, 3	289	291	Not used.	Uses PO Line revision.
Non-inventory Receiving Location Field	NINVT_RECV_ LOC_FLD	Alphanumeric, 15	292	306	O
Receiving Location	RECV_LOC_ID	Alphanumeric, 15	307	321	Not used.
Rejected Amount	REJ_AMT	Numeric, 16/Decimal, 14,2 (+999999999999.99)	322	337	O	Calculated from Rejected Quantity * Gross Unit Cost Amount on PO line.
Rejected for Credit Amount	REJ_CR_AMT	Numeric, 16/Decimal, 14,2 (+999999999999.99)	338	353	O	Calculated from Rejected Credit Quantity * Gross Unit Cost Amount on PO line.
Rejected for Credit Quantity	REJ_CR_QTY	Numeric, 16/Decimal, 14,4 (+9999999999.9999)	354	369	O	Input file value is used only if Rejection Order/Payment Disposition = “C” or blank (in which case it defaults to “C”). Otherwise, or if not in input file, set to zero.
Rejected Location	REJ_LOC_ID	Alphanumeric, 15	370	384	Not used.
Rejected but Pay Amount	REJ_PAY_AMT	Numeric, 16/Decimal, 14,2 (+999999999999.99)	385	400	O	Calculated from Rejected but Pay Quantity * Gross Unit Cost Amount on PO line.
Rejected but Pay Quantity	REJ_PAY_QTY	Numeric, 16/Decimal, 14,4 (+9999999999.9999)	401	416	O	Input file value is used only if Rejection Order/Payment Disposition = “P”. Otherwise, or if not in input file, set to zero.
Rejected Quantity	REJ_QTY	Numeric, 16/Decimal, 14,4 (+9999999999.9999)	417	432	O	If entered, must equal the Rejected Credit Qty or Rejected Pay Qty or Rejected Replace Qty, depending upon the Rejection Order/Payment Disposition of C (or blank), P or R, respectively.
Rejected for Repair Amount	REJ_REP_AMT	Numeric, 16/Decimal, 14,2 (+999999999999.99)	433	448	O	Calculated from Rejected for Repair Quantity * Gross Unit Cost Amount on PO line.
Rejected for Repair Quantity	REJ_REP_QTY	Numeric, 16/Decimal, 14,4 (+9999999999.9999)	449	464	O	Input file value is used only if Rejection Order/Payment Disposition = “R”. Otherwise, or if not in input file, set to zero.
Returned Material Authorization No.	RMA_NO_ID	Alphanumeric, 15	465	479	O
Reason Code	RSN_CD	Character, 1	480	480	O	If entered, must be in Vendor Rejection Reasons screen. Will be set to null if there are no rejections.
Rejection Order/Payment Disposition	S_OP_REJ_ DISPN_CD	Character, 1	481	481	O	Determined by rejection quantity. Will be set to null if there are no rejections.
Rejection Disposition	S_REJ_DISPN_ CD	Character, 1	482	482	O	If not entered, will default to "R." Will be set to null if there are no rejections.
Reason When Used code	S_RSN_WH_USED_CD	Character, 1	483	483	Not used.	Always populate with an "R."
Traveler Printed Flag	TRVLR_PRNTD_FL	Character, 1	484	484	Not used.	Program sets to "N."
User Revision Field	USER_RVSN_ FLD	Alphanumeric, 3	485	487	Not used.	Populated from PO_LN
Warehouse ID	WHSE_ID	Alphanumeric, 8	488	495	O	If entered, must exist in Inventory » Warehouses screen.
Comment Field	COMMENT_NT	Alphanumeric, 254	496	749	O
Time Stamp	TIMESTAMP	Date, 10 (MM/DD/YYYY)	750	759	Not used.	Costpoint timestamps with current system date/time.

Input Costpoint Database Tables

Input tables are used for validations and/or obtaining current Costpoint information. The application accesses the following Costpoint tables:

Item	(ITEM)
Purchase Order Header	(PO_HDR)
Purchase Order Line	(PO_LN)
Receipts Header	(RECPT_HDR)
Unit of Measure	(UM)
Subperiod	(SUB_PD)
Employee Table	(EMPL)
Warehouse	(WHSE)
Warehouse Locations	(WHSE_LOC)

The PO Header, Receipt Header, Employee, Warehouse, and Warehouse Location table information is validated for the user's company ID.

ITEM table information is also validated for the user’s company ID, based on the setting of the Separate Items by Company flag at Product Definition » Product Definition Settings » Corporate Settings. If the checkbox is selected, Costpoint validates ITEM information for the user’s company. If the checkbox is cleared, Costpoint does not perform this validation.

Input Files/Output Costpoint Database Tables

The following tables (Purchase Order Line, Receipt Header, and Receipt Line) indicate which columns are modified when information is added or updated during the upload process. Only rows associated with the user's company ID are affected. If the record type is "RA," input file quantities and amounts are used to update the original PO line amounts.

If the record type is "RL," input file quantities and amounts are compared to original receipt line quantities and amounts. The difference is applied to the PO line. RL type records can also be used to add to an existing receipt transaction, receipt lines that didn’t exist previously.

Purchase Order Line (PO_LN)

Costpoint Column Description	Costpoint Column ID	Column Type	ASCII Format	Max Length	Notes
Received Quantity	RECVD_ QTY	DECIMAL	NUMERIC	14.4	Updated based on input file, if entered. Otherwise, no change. If Receiving Settings has Overshipments = Prevent Entry of Receipts, and PO line does not allow overshipments, PO line Received Qty – Reject/Replace qty cannot exceed the PO line Order Qty. If Receiving Settings has Overshipments = Prevent Entry of Receipts, and PO line allow overshipments within certain tolerance limits, PO line Received Qty – Reject/Replace qty can exceed the PO line Order Qty, but only up to the tolerance limit allowed.
Accepted Quantity	ACCPTD_ QTY	DECIMAL	NUMERIC	14.4	Updated based on input file, if entered. Otherwise, no change.
Received Amount	RECVD_ AMT	DECIMAL	NUMERIC	14.2	Calculated from Received Quantity * Gross Unit Cost Amount on PO line.
Accepted Amount	ACCPTD_ AMT	DECIMAL	NUMERIC	14.2	Calculated from Accepted Quantity * Gross Unit Cost Amount on PO line.
Rejected for Repair Quantity	REJ_REP_ QTY	DECIMAL	NUMERIC	14.4	Updated based on input file, if entered. Otherwise, no change.
Rejected but Pay Quantity	REJ_PAY_ QTY	DECIMAL	NUMERIC	14.4	Updated based on input file, if entered. Otherwise, no change.
Rejected for Credit Quantity	REJ_CR_ QTY	DECIMAL	NUMERIC	14.4	Updated based on input file, if entered. Otherwise, no change.
Rejected for Repair Amount	REJ_REP_ AMT	DECIMAL	NUMERIC	14.2	Calculated from Rejected for Repair Quantity * Gross Unit Cost Amount on PO line.
Rejected but Pay Amount	REJ_PAY_ AMT	DECIMAL	NUMERIC	14.2	Calculated from Rejected but Pay Quantity * Gross Unit Cost Amount on PO line.
Rejected for Credit Amount	REJ_CR_ AMT	DECIMAL	NUMERIC	14.2	Calculated from Rejected Credit Quantity * Gross Unit Cost Amount on PO line.

Receipt Header (RECPT_HDR)

Costpoint Column Description	Costpoint Column ID	Column Type	ASCII Format	Max Length	Notes
Receipt ID	RECPT_ID	VARCHAR	ALPHANUMERIC	10	From Input file.
PO ID	PO_ID	VARCHAR	ALPHANUMERIC	10	From Input File. PO/PO Release combination must be in PO Header table (PO_HDR). The user's company ID should match the company ID assigned to the PO Header row for the corresponding PO ID.
PO Change Order Number	PO_CHNG_ ORD_NO	SMALLINT	NUMERIC	3	Loaded from PO Header table (PO_HDR. PO_CHNG_ORD_NO).
PO Release Number	PO_RLSE_ NO	SMALLINT	NUMERIC	3	From input file. PO/PO Release combination must be in PO Header table (PO_HDR). The user's company ID should match the company ID assigned to the PO Header row.
Packing Slip ID	PS_ID	VARCHAR	ALPHANUMERIC	15	Packing slip number is required if Require Packing Slip checkbox is selected in the Receiving Settings screen. Receiving Settings filters user's company ID.
Receipt Date	RECPT_DT	DATE	NUMERIC (MM-DD-YYYY)	10	From input file if entered. Will default to current system date if missing or invalid. Must be within an open accounting period.
Material Handler Employee ID	MATL_HNDLR_EMPL_ID	VARCHAR	ALPHANUMERIC	12	From input file. Must be an active employee ID in Employee » Basic Employee Info. The user's company ID should match the company ID assigned to the Employee row.
Offsite Receiver ID	OFFSITE_ RECVR_ID	VARCHAR	ALPHANUMERIC	12	Defaults to “RECPTS UPLD”.
Receipt Comment	RECPT_ COMMENT_NT	VARCHAR	ALPHANUMERIC	254	From input file, if entered. Otherwise, no change.
Total Receipt Amount	TOT_ RECPT_ AMT	DECIMAL	NUMERIC	14.2	Cumulative total of Received Amounts for each Receipt Line.
Time Stamp	TIME_ STAMP	DATE	NUMERIC (MM/DD/YYYY)	10	Program timestamps with current system date.
Company ID	COMPANY_ID	VARCHAR	ALPHANUMERIC	10	Load user's company ID.

Receipt Line (RECPT_LN)

Costpoint Column Description	Costpoint Column ID	Column Type	ASCII Format	Max Length	Notes
PO ID	PO_ID	VARCHAR	ALPHANUMERIC	10	From input file. PO/PO Release combination must be in the PO Header table (PO_HDR). The user's company ID should match the company ID assigned to the PO Header row for the corresponding PO ID.
PO Release Number	PO_RLSE_NO	SMALLINT	NUMERIC	2	From input file. PO/PO Release combination must be in the PO Header table (PO_HDR). The user's company ID should match the company ID assigned to the PO Header row.
Receipt Line Description	RECPT_LN_ DESC	VARCHAR	ALPHANUMERIC	60	From corresponding PO Line (PO_LN.PO_LN_DESC)
Fiscal Year Code	FY_CD	VARCHAR	ALPHANUMERIC	5	By default, the FY associated with the Receipt Date in the receipt header, whether entered or derived.
Period Number	PD_NO	SMALLINT	NUMERIC	2	By default, the Period associated with the Receipt Date in the receipt header, whether entered or derived.
Subperiod Number	SUB_PD_NO	SMALLINT	NUMERIC	2	By default, the Sub Period associated with the Receipt Date in the receipt header, whether entered or derived.
Received Quantity	RECV_QTY	DECIMAL	NUMERIC	14,4	From input file, if entered. If Receiving Settings has Overshipments set to Prevent Entry of Receipts, and the PO line does not allow overshipments, then PO line Received Qty – Reject/Replace qty cannot exceed the PO line Order Qty. If Receiving Settings has Overshipments set to Prevent Entry of Receipts, and the PO line allows overshipments within certain tolerance limits, PO line Received Qty – Reject/Replace qty can exceed the PO line Order Qty, but only up to the tolerance limit allowed.
Receiving Unit of Measure	RECV_UM_CD	VARCHAR	ALPHANUMERIC	3	From corresponding PO line (PO_LN.PO_LN_UM_CD)
Received Amount	RECV_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Received Quantity * Gross Unit Cost Amount on PO line.
Accepted Amount	ACCPT_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Accepted Quantity * Gross Unit Cost Amount on PO line.
Accept Location ID	ACCPT_LOC_ ID	VARCHAR	ALPHANUMERIC	15	Defaults to Null.
Accepted Quantity	ACCPT_QTY	DECIMAL	NUMERIC	14,4	If not entered, this value is set to zero.
Inspected Accepted Amount	INSP_ACCPT_ AMT	DECIMAL	NUMERIC	14,2	Defaults to Zero.
Inspected Accepted Quantity	INSP_ACCPT_ QTY	DECIMAL	NUMERIC	14,4	Defaults to Zero.
Inventory Abbreviation Code	INVT_ABBRV_ CD	VARCHAR	ALPHANUMERIC	6	Defaults to Null.
Item ID	ITEM_ID	VARCHAR	ALPHANUMERIC	30	From PO Line (PO_LN.ITEM_ID)
Item Revision ID	ITEM_RVSN_ ID	VARCHAR	ALPHANUMERIC	3	From PO Line (PO_LN.ITEM_RVSN_ID)
Non-inventory Receiving Location Field	NINVT_RECV_ LOC_FLD	VARCHAR	ALPHANUMERIC	15	From input file, if entered. Otherwise, space.
Receiving Location ID	RECV_LOC_ID	VARCHAR	ALPHANUMERIC	15	Defaults to Null.
Rejected Amount	REJ_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Rejected Quantity * Gross Unit Cost Amount on PO line.
Rejected for Credit Amount	REJ_CR_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Rejected Credit Quantity * Gross Unit Cost Amount on PO line.
Rejected for Credit Quantity	REJ_CR_QTY	DECIMAL	NUMERIC	14,4	From input file only if Rejection Order/Payment Disposition = “C” or blank (in which case it defaults to “C”). Otherwise, or if not in input file, set to zero.
Rejection Location ID	REJ_LOC_ID	VARCHAR	ALPHANUMERIC	15	Defaults to Null.
Rejected but Pay Amount	REJ_PAY_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Rejected but Pay Quantity * Gross Unit Cost Amount on PO line.
Rejected but Pay Quantity	REJ_PAY_QTY	DECIMAL	NUMERIC	14,4	From input file only if Rejection Order/Payment Disposition = “P”. Otherwise, or if not in input file, set to zero.
Rejected Quantity	REJ_QTY	DECIMAL	NUMERIC	14,4	From input file. Otherwise, if not entered, will be set to zero.
Rejected for Repair Amount	REJ_REP_AMT	DECIMAL	NUMERIC	14,2	From input file. Otherwise, calculated from Rejected for Repair Quantity * Gross Unit Cost Amount on PO line.
Rejected for Repair Quantity	REJ_REP_QTY	DECIMAL	NUMERIC	14,4	From input file only if Rejection Order/Payment Disposition = “R”. Otherwise, or if not in input file, set to zero.
Returned Material Authorization No.	RMA_NO_ID	VARCHAR	ALPHANUMERIC	15	From input file, if entered. Else, space.
Reason Code	RSN_CD	VARCHAR	ALPHANUMERIC	1	From input file, if entered. Otherwise, null. Must be in the Vendor Rejection Reasons screen. Set to null if there are no rejections. The user's company ID should match the company ID assigned to the Reason Code.
Rejection Order/Payment Disposition	S_OP_REJ_ DISPN_CD	VARCHAR	ALPHANUMERIC	1	From input file, if entered. Else defaults to “C” (Credit Due) if Rejected Qty is not zero. Will be set to null if there are no rejections.
Rejection Disposition	S_REJ_DISPN_ CD	VARCHAR	ALPHANUMERIC	1	From input file. If not entered, defaults to "R" (Repair/Replacement Required) if Rejected Qty is not zero. Set to null if there are no rejections.
Reason Where Used Code	S_RSN_WH_ USED_CD	VARCHAR	ALPHANUMERIC	1	Defaults to “R” (Vendor Rejection Reasons)
Traveler Printed Flag	TRVLR_PRNTD_FL	VARCHAR	ALPHANUMERIC	1	Default is "N" for new receipts.
User Revision Field	USER_RVSN_ FLD	VARCHAR	ALPHANUMERIC	3	Defaults from PO Line (PO_LN. USER_RVSN_FLD).
Warehouse ID	WHSE_ID	VARCHAR	ALPHANUMERIC	8	Defaults to Null.
Time Stamp	TIME_STAMP	DATE	NUMERIC (MM/DD/YYYY)	10	Defaults to current system date.
Asset Auto-Created from this Line	FA_AUTOCR_ FL	VARCHAR	CHARACTER	1	Default is "N" for new receipts.
Modified By (User ID)	MODIFIED_BY	VARCHAR	ALPHANUMERIC	18	Receipt Upload (RECPTS UPLD).
Material Review Board Amount	MRB_AMT	DECIMAL	NUMERIC	14,2	0
Material Review Board Quantity	MRB_QTY	DECIMAL	NUMERIC	14,4	0
PO Desired Date	PO_DESIRED_DT	DATE	NUMERIC (MM/DD/YYYY)	10	From PO Line (PO_LN.DESIRED_DT).
PO Due Date	PO_DUE_DT	DATE	NUMERIC (MM/DD/YYYY)	10	From PO Line (PO_LN.DUE_DT).
PO Line Key	PO_LN_KEY	INTEGER		4	Retrieve using PO Number/PO Line Number/PO Release Number/PO Change Order Number.
Posted Amount	PSTD_AMT	DECIMAL	NUMERIC	14,2	0
Posted Quantity	PSTD_QTY	DECIMAL	NUMERIC	14,4	0
Receipt Key	RECPT_KEY	INTEGER		4	From Receipt Header.
Rowversion	ROWVERSION	SMALLINT		2	0
Vouchered Amount	VCHR_AMT	DECIMAL	NUMERIC	14,2	0
Vouchered Quantity	VCHR_QTY	DECIMAL	NUMERIC	14,4	0