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
- 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.
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
|
Optional
|
7
|
Unit Cost
|
VCHR_LN.TRN_UNIT_CST_AMT
|
Decimal (14,4)
|
16
|
45
|
60
|
Optional
|
8
|
Extended Cost
|
VCHR_LN.TRN_EXT_CST_AMT
|
Decimal (14,2)
|
16
|
61
|
76
|
Optional
|
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
|