Processing Details

Costpoint follows a series of steps when importing accounts payable vouchers.

Processing Details for Import Accounts Payable Vouchers

  1. Costpoint checks the voucher temporary tables to ensure that all rows are imported. If any rows have not been imported, Costpoint displays a message and you can continue or cancel the process. If you continue, the temporary tables are cleared.
  2. Costpoint reads records from the Input File and inserts them into the temporary tables.
  3. Costpoint applies defaults to fields that are blank in the Input File and that have a "default provided" status.
  4. Validations are performed.
  5. After the validations are completed, vouchers with errors are written to the error file. If there is an error on one line of a voucher (header, detail, or vendor labor), Costpoint rejects all lines of the voucher and writes them to the error file. It then deletes these rows from the temporary tables. The temporary tables now have only valid rows in them.
  6. Costpoint performs field processing and calculations.
  7. The Error Report prints. If there are errors, Costpoint displays a message on the screen. If there are no errors, the Error Report indicates that no records were found.

Import Details

  1. When Costpoint uses the voucher numbers from the Input File, the Voucher Header and Voucher Header History are checked again to ensure that the vouchers being imported do not already exist. If duplicates do exist, Costpoint displays a message and will not perform the import.
  2. When Costpoint assigns voucher numbers, all vouchers in the temporary tables are assigned new and unique voucher numbers.
  3. Costpoint sets the Voucher Key, Voucher Line Key, Voucher Line Account Key, and Voucher Line Vendor Key.
  4. Costpoint inserts rows from the temporary tables into the Voucher Header, Voucher Line, Voucher Line Account, and Voucher Labor Vendor tables.

Input File and Error File Layout

You name the Input File. The Error File has the same name with an extension of .ERR and will have the same layout as the Input File.

The Input File must be a fixed format file. You must fill every position with either an appropriate character or space. It is not necessary to zero-fill numbers; you can use spaces to maintain the proper format. Numeric fields must be right-justified and character fields must be left-justified. You do not have to fill the Notes fields with spaces, but every record must end with a carriage return and line feed.

A record in the Input File must be in one of three formats: Header, Detail, or Vendor Labor. Each voucher Header record must have at least one matching voucher Detail record in the Input File. Vendor Labor records are optional. Each Vendor Labor record must match with a Detail record in the Input File.

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.

Header Format (VCHR_HDR Table)

Type CP Column/Table Characters Required? Format
Record Type Character 1 Required (H)
Input Voucher Number VCHR_NO Number 9 Required (999999999)
Fiscal Year FY_CD Character 6 Default provided
Period Number PD_NO Number 2 Default provided (99)
Subperiod Number SUB_PD_NO Number 2 Default provided (99)
Vendor ID VEND_ID Character 12 Required
Terms TERMS_DC Character 15 Default provided
Invoice Num INVC_ID Character 15 Optional
Invoice Date INVC_DT Character 10 Required (YYYY-MM-DD)
Invoice Amt INVC_AMT Number 15 Required (-99999999999.99)

(including Sales Tax)

Discount Date DISC_DT Character 10 Optional (YYYY-MM-DD)
Discount Pct DISC_PCT_RT Number 6 Optional (999.99)         

(10.00 = 10%)

Total Disc Amt DISC_AMT Number 15 Optional (-99999999999.99)
Due Date DUE_DT Character 10 Optional (YYYY-MM-DD)
Hold Voucher HOLD_VCHR_FL Character 1 Required (Y or N)
Pay When Paid Fl PAY_WHEN_ PAID_FL Character 1 Default provided (Y, N, or blank)
Pay Vendor ID PAY_VEND_ID Character 12 Default provided
Payment Add Cd PAY_ADDR_DC Character 10 Default provided
PO Num EXT_PO_ID Character 10 Optional (For reference only)
PO Release Num EXT_PO_RLSE_NO Number 3 Optional (999)   

(for reference only)

Retainage Rate RTN_RT Number 6 Optional (999.99)            

(10.00 = 10%)

A/P Acct Desc AP_ACCTS_KEY

(used to derive AP_ACCT_ID, AP_ORG_ID, AP_REF1_ID, and AP_REF2_ID)

Character 30 Default provided
Cash Acct Desc CASH_ACCTS_KEY

(used to derive CASH_ACCT_ID, CASH_ORG_ID, CASH_REF1_ID, and CASH_REF2_ID)

Character 30 Default provided
Invoice Type S_INVC_TYPE Character 1 Default provided (D, P, or N)
Delivery Value (Ship Amt) SHIP_AMT Number 15 Default provided (-99999999999.99)
Check Fiscal Year CHK_FY_CD Character 6 Default provided
Check Period Number CHK_PD_NO Number 2 Default provided (99)
Check Subperiod Number CHK_SUB_PD_NO Number 2 Default provided (99)
Check Number CHK_NO Number 9 Optional (999999999)
Check Date CHK_DT Character 10 Default provided (YYYY-MM-DD)
Check Amount CHK_AMT Number 15 Optional (only needed with Check Number) (-99999999999.99)
Discount Taken Amount DISC_TAKEN_AMT Number 15 Default provided (-99999999999.99)
Invoice Period of Performance Date INVC_POP_DT Character 10 Optional (YYYY-MM-DD)
Print Note-Blank Laser Check Flag PRINT_NOTE_FL Character 1 Default provided (Y, N, or blank)
Separate Check Flag SEP_CK_FL Character 1 Default Provided (Y or N)
Joint Payee Name JNT_PAY_VEND_NAME Character 40 Optional
Notes NOTES Character 254 Optional

Detail Format (VCHR_LN and VCHR_LN_ACCT tables)

Type CP Table/Column Characters Required? Format
Record Type Character 1 Required (D)
Input Voucher Number Number 9 Required (999999999)
Fiscal Year Character 6 Default provided
Voucher Line Num VCHR_LN.VCHR_LN_NO Number 6 Required (999999)
Account VCHR_LN_ACCT.ACCT_ID Character 15 Required
Org VCHR_LN_ACCT.ORG_ID Character 20 Required
Project VCHR_LN_ACCT.PROJ_ID Character 30 Optional
Reference Num 1 VCHR_LN_ACCT.REF1_ID Character 20 Optional
Reference Num 2 VCHR_LN_ACCT.REF2_ID Character 20 Optional
Line Amt VCHR_LN_ACCT.CST_AMT Number 15 Required (-99999999999.99) (without Tax)
Taxable Code VCHR_LN.S_TAXABLE_CD Character 1 Required S, U, or N
Tax Code VCHR_LN.SALES_TAX_CD Character 6 Optional (Required if Taxable Code is S or U)
Sales Tax Amt VCHR_LN.SALES_TAX_AMT Number 15 Required (-99999999999.99)
Discount Amt VCHR_LN.DISC_AMT Number 15 Required (-99999999999.99)
Use Tax Amt VCHR_LN.USE_TAX_AMT Number 15 Required (-99999999999.99)
AP 1099 Flag VCHR_LN_ACCT.AP_1099_FL Character 1 Default provided Y, N, or blank
1099-MISC Type Code VCHR_LN_ACCT.S_AP_1099_TYPE_CD Character 6 Default provided
Voucher Line Desc VCHR_LN.VCHR_LN_DESC Character 30 Default provided
Org Abbreviation VCHR_LN_ACCT.ORG_AB BRV_CD Character 6 Optional
Project Abbreviation VCHR_LN_ACCT.PROJ_ABBRV_CD Character 6 Optional
Project Account Abbrev VCHR_LN_ACCT.PROJ_ACCT_ABBRV_CD Character 6 Optional
Notes VCHR_LN.NOTES Character 254 Optional

Vendor Labor Format (VCHR_LAB_VEND Table)

Type CP Table/Column Character Required? Format
Record Type Character 1 Required (V)
Input Voucher Number Numeric 9 Required (999999999)
Fiscal Year Character 6 Default provided
Voucher Line Number Numeric 6 Required (999999)
Vendor Subline Number SUB_LN_NO Numeric 5 Required (99999)
Vendor Empl ID VEND_EMPL_ID Character 12 Optional
General Labor Category GENL_LAB_CAT_CD Character 6 Required
Project Labor Category BILL_LAB_CAT_CD Character 6 Optional
Vendor Hours VEND_HRS Number 15 Optional (-99999999999.99)
Vendor Amount VEND_AMT Number 15 Optional (-99999999999.99)
Effective Billing Date EFFECT_BILL_DT Character 10 Default provided (YYYY-MM-DD)