Costpoint Online Help

Input Files

The Import Purchase Order Vouchers preprocessor supports the several input file types.

Supported input file types include:

  • PO Voucher Header: This input file type is required. The information in this file populates the Voucher Header (VCHR_HDR) table.
  • PO Voucher Line: This input file type is required. The information in this file populates the Voucher Lines (VCHR_LN) table and its child table, Voucher Line Account (VCHR_LN_ACCT).
  • PO Voucher Line Account: This input file type is optional. The information in this file populates the Voucher Line Account (VCHR_LN_ACCT) table. It allows you to load multiple charge numbers for a single voucher line.
  • PO Voucher Vendor Labor: This input file type is optional. The information in this file populates the Voucher Vendor Labor (VCHR_LAB_VEND) table. This file type is optional. It allows you to load vendor labor voucher information.
  • PO Voucher Vendor Labor Details Record: This input file type is optional. The information in this file captures the timesheet information for labor that has been reported.

You can process input files simultaneously. Costpoint renames input files with an extension of .OLD after processing.

File Format Tips

Note: The input file layout may contain fields that are available only in Costpoint Advanced. If you are using Costpoint Essentials, these fields are excluded from the import process and may be left blank or null when you create the input file.
  • Deltek recommends that you use .TXT and .CSV file-naming conventions.
  • Each PO voucher header input file record must have at least one PO voucher line input file record, and a PO voucher line input file record must have a corresponding PO voucher header input file record. Similarly, each PO voucher vendor labor input file record and each PO voucher line account input file record requires a matching PO voucher line input file record.
  • PO voucher line records must physically follow their corresponding PO voucher header input file record, with no other records for another invoice in between. Invoice IDs determine which line records belong to which header record.
  • If you use a PO voucher vendor labor input file, the records for each voucher/voucher line combination must begin with a subline number (SUB_LN_NO) of '1' and be numbered sequentially.
  • If you use delimited input files, fields are separated by a comma or some other character. The delimiter must not be a character that is used for data in the input file. For example, you would not use a hyphen as a delimiter because date fields also contain hyphens. When the preprocessor encounters the declared delimiter, the delimiter is interpreted as the end of the input field.
  • If you use fixed-length input files, 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. Numeric fields should be right justified; character fields should be left justified. If an optional column in an input file is not populated, you must include the required number of spaces for that column before entering data for the next column. As an exception, if there are empty columns at the end of the last record in the file, it is not necessary to populate them.
  • Every row must end with a carriage return and a line feed. As an exception, the last record in the file does not have to be followed with a carriage return and line feed.
  • Input file fields defined as dates in Costpoint must be 10 characters and in the YYYY-MM-DD format, with a four-character year (including century), month (01-12), and day (01-31). Year, month, and day must be separated by hyphens (-).
  • Input file field types are alphanumeric or characters (VARCHAR), numeric (DECIMAL, SMALLINT, and INTEGER), and date. The maximum for the 14,4 decimal value is 9,999,999,999.9999, and the maximum for 14,2 is 999,999,999,999.99. (These commas should not be included in the input file.)
  • For international users, the data can contain non-English characters, provided they are in the ASCII character set. Before you create an ASCII text (.TXT) file, be sure that your keyboard language is set to 'EN' (United States English) to prevent the application from generating errors.

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.

PO Voucher Header Input File

Seq. No. Column Name Costpoint Table/Column Type Length Starting Position Ending Position Required or Optional
1 Record Type N/A Character (H for Header) 1 1 1 Required
2 Invoice Number VCHR_HDR.INVC_ID and VCHR_LN.INVC_ID Alphanumeric 15 2 16 Required
3 Invoice Date VCHR_HDR.INVC_DT Date 10 17 26 Required
4 Invoice Amount VCHR_HDR.TRN_INVC_AMT Decimal (14,2) 16 27 42 Required
5 PO Number VCHR_HDR and VCHR_LN.PO_ID Alphanumeric 10 43 52 Required
6 PO Release VCHR_HDR and VCHR_LN.PO_RLSE_NO Numeric 3 53 55 Required
7 Voucher Number VCHR_HDR and VCHR_LN.VCHR_NO Numeric 10 56 65 Optional
8 Fiscal Year VCHR_HDR.FY_CD Alphanumeric 6 66 71 Optional
9 Period VCHR_HDR.PD_NO Numeric 2 72 73 Optional
10 Sub Period VCHR_HDR.SUB_PD_NO Numeric 2 74 75 Optional
11 Header Notes VCHR_HDR.NOTES Alphanumeric 254 76 329 Optional
12 Retainage Percentage VCHR_HDR.RTN_RT Decimal (5,4) 5 330 334 Optional
13 Warehouse VCHR_HDR.DFLT_WHSE_ID Alphanumeric 8 335 342 Optional
14 Receipt ID VCHR_HDR.DFLT_RECPT_ID Alphanumeric 10 343 352 Optional
15 Packing Slip VCHR_HDR.DFLT_PS_ID Alphanumeric 15 353 367 Optional
16 Debit Memo VCHR_HDR.DM_FL Alphanumeric 1 368 368 Optional

PO Voucher Line Input File

Seq. No. Column Name Costpoint Table/Column Type Length Starting Position Ending Position Required or Optional
1 Record Type N/A Character (L for Line) 1 1 1 Required
2 Invoice Number N/A. Used to link to header. Alphanumeric 15 2 16 Required
3 Invoice Line Number VCHR_LN.VCHR_LN_NO Numeric 4 17 20 Required
4 PO Line Number VCHR_LN.PO_LN_NO (to link to PO_LN) Numeric 4 21 24 Optional
5 Line Charge Code VCHR_LN.MISC_LN_CHG_TYPE Alphanumeric 6 25 30 Optional
6 Invoice Quantity VCHR_LN.QTY Decimal (14,4) 14 31 44 Required if Extended Cost is blank.
7 Unit Cost VCHR_LN.TRN_UNIT_CST_AMT Decimal (14,4) 16 45 60 Required if Extended Cost is blank.
8 Extended Cost VCHR_LN.TRN_EXT_CST_AMT Decimal (14,2) 16 61 76 Required if Invoice Quantity and Unit Cost are blank.
9 Line Notes VCHR_LN.NOTES Alphanumeric 254 77 330 Optional
10 Account VCHR_LN_ACCT.ACCT_ID Alphanumeric 15 331 345 Optional
11 Organization VCHR_LN_ACCT.ORG_ID Alphanumeric 20 346 365 Optional
12 Project VCHR_LN_ACCT.PROJ_ID Alphanumeric 30 366 395 Optional
13 Reference 1 VCHR_LN_ACCT.REF1_ID Alphanumeric 20 396 415 Optional
14 Reference 2 VCHR_LN_ACCT.REF2_ID Alphanumeric 20 416 435 Optional
15 Taxable Code VCHR_LN.S_TAXABLE_CD, VCHR_LN_ACCT.S_TAXABLE_CD Alphanumeric 1 436 436 Optional
16 Tax Amount VCHR_LN.TRN_SALES_TAX_AMT (for sales tax/VAT); VCHR_LN.TRN_USE_TAX_AMT (for use tax) Decimal (14,2) 16 437 452 Optional

PO Voucher Line Account Record Input File

Seq. No. Column Name Costpoint Table/Column Type Length Starting Position Ending Position Required or Optional
1 Record Type N/A Character (A for Account) 1 1 1 Required
2 Invoice Number N/A. Used to link to header. Alphanumeric 15 2 16 Required
3 Invoice Line Number N/A. Used to link to line. Numeric 4 17 20 Required
4 Account Line Number N/A. Used to link to account line. Numeric 4 21 24 Required
5 Allocation VCHR_LN_ACCT.CST_AMT_PCT_RT Decimal (5,4) 6 25 30 Optional
6 Amount VCHR_LN_ACCT.CST_AMT_PCT_RT Decimal (14,2) 15 31 45 Optional
7 Account VCHR_LN_ACCT.ACCT_ID Alphanumeric 15 46 60 Required
8 Organization VCHR_LN_ACCT.ORG_ID Alphanumeric 20 61 80 Optional
9 Project VCHR_LN_ACCT.PROJ_ID Alphanumeric 30 81 110 Optional
10 Reference 1 VCHR_LN_ACCT.REF1_ID Alphanumeric 20 111 130 Optional
11 Reference 2 VCHR_LN_ACCT.REF2_ID Alphanumeric 20 131 150 Optional

PO Voucher Vendor Labor Input File

Seq. No. Column Name Costpoint Table/Column Type Length Starting Position Ending Position Required or Optional
1 Record Type N/A Character (V for Vendor) 1 1 1 Required
2 Invoice Number N/A. Used to link to header. Alphanumeric 15 2 16 Required
3 Invoice Line Number N/A. Used to link to line. Numeric 4 17 20 Required
4 Vendor Labor Subline Number VCHR_LAB_VEND.SUB_LN_NO Numeric 4 21 24 Required
5 Vendor Empl ID VCHR_LAB_VEND.VEND_EMPL_ID Alphanumeric 12 25 36 Optional
6 General Labor Category VCHR_LAB_VEND.GENL_LAB_CAT_CD Alphanumeric 6 37 42 Optional
7 Project Labor Category VCHR_LAB_VEND.BILL_LAB_CAT_CD Alphanumeric 6 43 48 Optional
8 Vendor Hours VCHR_LAB_VEND.VEND_HRS Decimal (14,2) 16 49 64 Required
9 Vendor Amount VCHR_LAB_VEND.VEND_AMT Decimal (14,2) 16 65 80 Required
10 Effective Bill Date VCHR_LAB_VEND.EFFECT_BILL_DT Date 10 81 90 Optional
11 Timesheet Date VCHR_LAB_VEND.TS-DT Date 10 91 100 Optional
12 Comments VCHR_LAB_VEND.NOTES Alphanumeric 254 101 354 Optional

PO Voucher Vendor Labor Details Input File

Seq. No. Column Name Costpoint Table/Column Type Length Starting Position Ending Position Required or Optional
1 Record Type N/A Character (T for Timesheet) 1 1 1 Required
2 Invoice Number N/A Character 15 2 16 Required
3 Invoice Line Number N/A Numeric 4 17 20 Required
4 Vendor Labor Subline Number VCHR_LAB_VEND_TS.LAB_SUB_LN_NO Numeric 4 21 24 Required
5 Vendor Detail Subline Number N/A Numeric 4 25 28 Required
6 Timesheet Hours Date VCHR_LAB_VEND_TS.TS_HRS_DT Date 10 29 38 Optional
7 Pay Type VCHR_LAB_VEND_TS.PAY_TYPE Character 3 39 41 Optional
8 Hours VCHR_LAB_VEND_TS.VEND_HRS Decimal 16 42 57 Optional
9 Rate VCHR_LAB_VEND_TS.TRN_LAB_RT_AMT Decimal 16 58 73 Optional
10 Comments VCHR_LAB_VEND_TS.NOTES Alphanumeric 25 74 327 Optional