Costpoint Online Help

Column Mappings for AP Voucher Tables

When you import TE Expenses/Advances, Costpoint stores the data from the input file into different tables.

AP Voucher Header Table (VCHR_HDR)

The Voucher Header table is filled as follows:

Field Description
Voucher Key (VCHR_KEY)

The application sets this value using the Sequence Generator during the Import step.

Anticipated Pay Date (ANTIC_PAY_DT)

The application sets the Anticipated Pay Date to the Due Date.

A/P Account (AP_ACCT_ID)

The application sets the A/P Account to the A/P Account from the Default A/P Accounts table using the AP_ACCTS_KEY from the Vendor Table.

A/P Accounts Key (AP_ACCTS_KEY)

The A/P Accounts Key is a system-generated key that correlates to the A/P Accounts Description from the DFLT_AP_ACCTS table. The key that is used comes from the AP_ACCTS_KEY of the VEND table.

The A/P Acct Key must exist in the Default A/P Accounts table.

A/P Organization (AP_ORG_ID)

The application sets the A/P Organization to the Organization from the Default A/P Accounts table using the AP_ACCTS_KEY from the Vendor Table.

A/P Reference Number 1 (AP_REF1_ID)

The application sets the A/P Reference Number 1 to the Ref No 1 from the Default A/P Accounts table using the AP_ACCTS_KEY from the Vendor Table.

A/P Reference Number 2 (AP_REF2_ID)

The application sets the A/P Reference Number 2 to the Ref No 2 from the Default A/P Accounts table using the AP_ACCTS_KEY from the Vendor Table.

Approval Date (APPRVL_DTT)

The application determines the Approval Date as follows:

  • If you selected the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen, the application uses the current system date.
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen, but the Require Approvals check box is selected on the Configure Accounts Payable Voucher Settings screen and the invoice amount is less than or equal to the Approval Required Above amount on the Configure Accounts Payable Voucher Settings screen, the application uses the current system date.
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances but the Require Approvals check box is selected on the Configure Accounts Payable Voucher Settings screen and the invoice amount is greater than the Approval Required Above amount on the Configure Accounts Payable Voucher Settings screen, the application sets the approval date to "null."
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen and the Require Approvals check box is not selected in the Configure Accounts Payable Voucher Settings screen, the application uses the current system date.
Approved Flag (APPRVD_FL)

The approved flag is set by the application as follows:

  • If you selected the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen, the approved flag is set to Y (Yes).
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen, but the Require Approvals check box is selected on the Configure Accounts Payable Voucher Settings screen and the invoice amount is less than or equal to the Approval Required Above amount on the Configure Accounts Payable Voucher Settings screen, the approved flag is set to Y (Yes).
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen, but the Require Approvals check box is selected on the Configure Accounts Payable Voucher Settings screen and the invoice amount is greater than the Approval Required Above amount on the Configure Accounts Payable Voucher Settings screen, the approved flag is set to N (No).
  • If you did not select the Mark Vouchers as Approved check box on the Import TE Expense/Advances screen and the Require Approvals check box is not selected on the Configure Accounts Payable Voucher Settings screen, the approved flag is set to Y (Yes).
Cash Account (CASH_ACCT_ID)

The application sets the Cash Account to the Cash Account from the Default Cash Accounts table using the CASH_ACCTS_KEY from the Vendor Table.

Cash Account Key (CASH_ACCTS_KEY)

The Cash Accounts Key is a system-generated key that correlates to the Cash Accounts Description from the DFLT_CASH_ACCTS table. The key comes from the CASH_ACCTS_KEY of the VEND table.

The Cash Acct Key must exist in the Default Cash Accounts table.

Cash Organization (CASH_ORG_ID)

The application sets the Cash Organization to the Organization from the Default Cash Accounts table using the CASH_ACCTS_KEY from the Vendor Table.

Cash Reference Number 1 (CASH_REF1_ID)

The application sets the Cash Reference Number 1 to the Ref No 1 from the Default Cash Accounts table using the CASH_ACCTS_KEY from the Vendor Table.

Cash Reference Number 2 (CASH_REF2_ID)

The application sets the Cash Reference Number 2 to the Ref No 2 from the Default Cash Accounts table using the CASH_ACCTS_KEY from the Vendor Table.

Company ID (COMPANY_ID)

The application sets this value to the login company during the Import step.

Discount Date (DISC_DT)

The application sets the Discount Date to the invoice date.

Due Amount (DUE_AMT, TRN_DUE_AMT)

The application sets the Due Amount to the Invoice Amount.

Due Date (DUE_DT)

The application derives the Due Date using the Vendor terms in the VEND table and the Invoice Date.

Entry Date (ENTR_DTT)

The application sets the Entry Date to the current system date during the Import step.

Entry User (ENTR_USER_ID)

The application sets the Entry User to the current user during the Import step.

Euro to Functional Currency Rate (EUR_TO_FUNC_RT)

The application sets this to 1 if the transactional currency and functional currency are not Euro-based. If the currency is Euro-based, the application retrieves a value from the rate tables using the rate group, currency codes, and rate date.

Euro to Pay Currency Rate (EUR_TO_PAY_RT)

The application sets this to 1 if the pay currency is the same as the transactional currency or the pay currency and functional currency are not Euro-based. If the currencies are Euro-based, the application retrieves a value from the rate tables using the rate group, currency codes, and rate date.

Fiscal Year (FY_CD), Period Number (PD_NO) and Subperiod Number (SUB_PD_NO)

The Fiscal Year, Period Number, and Subperiod Number are taken from the screen. 

Functional to Euro Currency Rate (FUNC_TO_EUR_RT)

The application sets this to 1 if the transactional currency is the same as the pay currency. If the currencies are different, the application retrieves a value from the rate tables using the rate group, currency codes, and rate date.

Functional to Euro Rate Flag (FUNC_TO_EUR_RT_FL)

The application sets this to Y if the functional currency is Euro-based. Otherwise, this is set to N.

Hold Voucher Flag (HOLD_VCHR_FL)

The application retrieves this value from the Place Expense Voucher(s) on Hold Flag in the input file. If no value is provided, the application sets this field to N.

Invoice Amount (INVC_AMT, TRN_INVC_AMT)

The application computes the transactional invoice amount by adding the TRN_TOT_BEF_DC_AMT column for all the VCHR_LN_ACCT rows for a voucher. It computes the invoice amount as the sum of the TOT_BEF_DC_AMT column for all the VCHR_LN_ACCT rows for a voucher.

Invoice Date (INVC_DT)

The Invoice Date is taken from the Expense Report Date in the Input File unless you enter a value on the Import TE Expense/Advances screen.

Invoice ID (INVC_ID)

The application concatenates the Expense Report ID and the External Expense ID.

Journal Code (S_JNL_CD)

The application sets the Journal Code to APV.

Notes (NOTES)

The application uses the Notes in the Input File header. It sets this field to a space if Notes are not in the Input File.

Pay Currency Code (PAY_CRNCY_CD)

If a value is not in the input file, the application sets this to the TRN_CRNCY_CD. 

Pay Currency Date (PAY_CRNCY_DT)

The application uses the value from the input file. If a value is not in the input file and the TRN_CRNCY_CD equals the PAY_CRNCY_CD, the application sets this to TRN_CRNCY_DT.

Pay Freeze Rate Flag (PAY_FREEZE_RT_FL)

The application sets this to Y if an Input File Pay Amount exists. It is also set to Y if the Freeze Rates check box is selected on the screen and the TRN_CRNCY_CD is not equal to the PAY_CRNCY_CD. Otherwise, this is set to N.

Pay Vendor ID (PAY_VEND_ID)

Can be assigned in the Input file; if not, the application uses the VEND table to retrieve the pay vendor. Validations must be applied to the pay vendor coming from either source.

Payment Address (PAY_ADDR_DC)

The value is the default payment address for the pay vendor (where the Address Code has a Pmt Addr pay code of type D, Default, as set on the Manage Vendor screen).

Pay When Paid Amount (PAYWPD_AMT)

If the Pay When Paid Flag is set to Y, the application computes this as the sum of the billable project lines in VCHR_LN_ACCT. It uses the TOT_BEF_DISC_AMT if the discount posting flag in voucher settings is set to G. It uses the NET_AMT if the discount posting flag in voucher settings is set to N. (See the Post to G/L Discount Method group box on the Manage Accounts Payable Vouchers screen for more information on the calculation.)

Pay When Paid Flag (PAY_WHEN_PAID_FL)

The application uses the value from the input file.

Rate Group ID (RATE_GRP_ID)

The Rate Group ID is taken from the screen.

Sales Tax Amount (SALES_TAX_AMT, TRN_SALES_TAX_AMT)

The application computes the transactional sales tax amount by summing the TRN_SALES_TAX_AMT column for all the VCHR_LN_ACCT rows for a voucher. It computes the sales tax amount by summing the SALES_TAX_AMT column for all the VCHR_LN_ACCT rows for a voucher.

Terms Description (TERMS_DC)

The Terms description is taken from the Vendor table.

Transaction Currency Code (TRN_CRNCY_CD)

The application uses the value from the input file.

Transaction Currency Date (TRN_CRNCY_DT)

The application uses the value from the input file expense line date.

Transactional to Euro Currency Rate (TRN_TO_EUR_RT)

The application sets this to 1 if the transactional currency is the same as the functional currency. If the currencies are different, it retrieves a value from the rate tables, using the rate group, currency codes and rate date.

Transaction to Euro Rate Flag (TRN_TO_EUR_RT_FL)

The application sets this to Y if the transactional currency is Euro-based. Otherwise, this is set to N.

Transaction Freeze Rate Flag (TRN_FREEZE_RT_FL)

The application sets this to Y if you selected the Freeze Rates check box on the screen.

Use Tax Amount (USE_TAX_AMT, TRN_USE_TAX_AMT)

The application computes the transactional use tax amount by summing the TRN_USE_TAX_AMT column for all the VCHR_LN_ACCT rows for a voucher. It computes the use tax amount by summing the USE_TAX_AMT column for all the VCHR_LN_ACCT rows for a voucher.

VAT Tax Date (VAT_TAX_DT)

The application uses the value from the Input File header. If no value is provided, it sets this field to null.

VAT Tax ID (VAT_TAX_ID)

The application uses the value from the Input File header. If no value is provided, it sets this field to null.

Vendor (VEND_ID)

The Vendor is taken from the Input File.

Voucher Number (VCHR_NO)
  • The application assigns the voucher number using the voucher numbering method selected on the Import TE Expense/Advances screen.
  • The voucher number/FY combination must not exist on VCHR_HDR, VCHR_HDR_HS, TVL_EXP_HDR, TVL_ADV_HDR, or CO_EXP_HDR.

Both assignments are performed when you select the Import Vouchers option from .

Voucher Type (S_VCHR_TYPE)

The application sets the Voucher Type to TE or TA. The application sets the following fields to N:

  • Auto Creation Flag (AUTO_CREATE_FL)
  • Debit Memo Flag (DM_FL)
  • Debit Memo Printed Flag (DM_PRNTD_FL)
  • Over Budget Flag (OVR_BUD_FL)
  • Print Note on Blank Laser Check Flag (PRNT_NOTE_FL)
  • Posted Accounts Payable Flag (POSTED_AP_FL)
  • Purchase Order Discrepancy (S_PO_DISCR_CD)
  • Receipt Discrepancy (S_RECPT_DISCR_CD)
  • Recurring Flag (RECUR_FL)
  • Recurring Template Flag (RECUR_TMPLT_FL)
  • Separate Check Flag (SEP_CHK_FL)
  • Invoice Type (S_INVC_TYPE)
  • Subcontractor Pay Code (S_SUBCTR_PAY_CD)
  • Taxable Code (S_TAXABLE_CD)
  • Taxable Flag (TAXABLE_FL)
  • Reverse Voucher Flag (RVRS_VCHR_FL)

The application sets the following fields to zero:

  • Cost Amount (CST_AMT, TRN_CST_AMT)
  • Check Amount (CHK_AMT)
  • Check Number (CHK_NO)
  • Discount Amount (DISC_AMT, TRN_DISC_AMT)
  • Discount Percentage Rate (DISC_PCT_RT)
  • External PO Release (EXT_PO_RLSE_NO)
  • Discount Taken Amount (DISC_TAKEN_AMT)
  • Delivery Amount (SHIP_AMT)
  • Transaction Delivery Amount (TRN_SHIP_AMT)
  • Recurred from Voucher No (RECUR_PAR_VCHR_NO)
  • Retainage Rate (RTN_RT)

The application sets the following fields to a single space:

  • Default Packing Slip (DFLT_PS_ID)
  • Document Location (DOC_LOCATION)
  • External PO ID (EXT_PO_ID)
  • Retainage Notes (RTN_NT)
  • Sales Tax Source Code (S_SALES_TAX_SRC_CD)
  • Joint Pay Vendor Name (JNT_PAY_VEND_NAME)

The following fields are left null by the application:

  • Approval User ID (APPRVR_USER_ID)
  • Batch Number (BATCH_ID)
  • Check Date (CHK_DT)
  • Check Fiscal Year (CHK_FY_CD)
  • Check Period Number (CHK_PD_NO)
  • Check Sub Period Number (CHK_SUB_PD_NO)
  • CIS Code (CIS_CD)
  • Default Warehouse (DFLT_WHSE_ID)
  • Discrepancy Calculation Date (DISCR_CALC_DTT)
  • Invoice Period of Performance Date (INVC_POP_DT)
  • Posting Sequence Number (POST_SEQ_NO)
  • Purchase Order (PO_ID)
  • Purchase Order Release Number (PO_RLSE_NO)
  • Receipt Number (DFLT_RECPT_ID)
  • Recurred from Voucher Key (RECUR_PAR_VCHR_KEY)
  • Recurring Voucher Desc (RECUR_VCHR_DC)
  • Sales Tax Code (SALES_TAX_CD)
  • Status Code (S_STATUS_CD)
  • Last Voucher Fiscal Year (LST_VCHR_FY_CD)
  • Last Voucher Period Number (LST_VCHR_PD_NO)
  • Last Voucher Subperiod Number (LST_VCHR_SUB_PD_NO)
  • Start Fiscal Year (START_FY_CD)
  • Start Period Number (START_PD_NO)
  • Start Subperiod Number (START_SUB_PD_NO)
  • End Fiscal Year (END_FY_CD)
  • End Period Number (END_PD_NO)
  • End Subperiod Number (END_SUB_PD_NO)

AP/Voucher Line Table

The Voucher Line table is filled as follows:

Field Description
Voucher Key (VCHR_KEY)

The application retrieves a value from the VCHR_HDR table during import.

Voucher Line Key (VCHR_LN_KEY)

The application sets this value using the Sequence Generator during import.

Line Change Cost Amount (LN_CHG_CST_AMT, TRN_LN_CHG_CST_AMT)

The application sets the Line Change Cost Amount to the Cost Amount from the Voucher Line Account table.

Line Change Tax Amount (LN_CHG_TAX_AMT, TRN_LN_CHG_TAX_AMT)

The application sets the Line Change Tax Amount to the Sales Tax Amount from the Voucher Line Account table.

Line Change Use Tax Amount (LN_CHG_USE_TAX_AMT, TRN_LN_CHG_USE_AMT)

The application sets the Line Change Use Tax Amount to the Use Tax Amount from the Voucher Line Account table.

Notes (NOTES)

The Notes are taken from the Input File detail record. A space is used if there are no Notes in the Input File.

Recovery Amount (RECOVERY_AMT, TRN_RECOVERY_AMT)

Recovery is taken from the Input File detail record, if a value is present in the file. If no value is present and a recovery percentage exists in the Input File, the application calculates the amount using the percentage. Otherwise, the recovery amount will be zero.  

Recovery Rate (RECOVERY_RT)

The Rate is taken from the Input File detail record, if a value is present. The application calculates the rate if the recovery amount is in the Input File. If neither the amount nor rate is provided, it sets the field to zero.  

Sales Tax Amount (SALES_TAX_AMT, TRN_SALES_TAX_AMT)

The application sets the Sales Tax Amount to the Sales Tax Amount in the Voucher Line Account table.

Sales Tax Code (SALES_TAX_CD)

The value comes from the Input File if the Taxable Code is S or U. Otherwise, the application sets it to N. 

Taxable Code (S_TAXABLE_CD)

The application sets the Taxable Code to the same value as the Taxable Code from the Voucher Line Account table.

Taxable Flag (TAXABLE_FL)

The application sets the Taxable Flag to the same value as the Taxable Flag in the Voucher Line Account table.

Net Amount (NET_AMT, TRN_NET_AMT)

The application sets the Net Amount to the same value as the Net Amount from the Voucher Line Account table.

Total Before Discount Amount (TOT_BEF_DISC_AMT, TRN_TOT_BEF_DC_AMT)

The application sets the Total Before Discount Amount to the same value as the Total Before Discount Amount from the Voucher Line Account table.

Use Tax Amount (USE_TAX_AMT, TRN_USE_TAX_AMT)

The application sets the Use Tax Amount to the same value as the Use Tax Amount from the Voucher Line Account table.

Voucher Line Description (VCHR_LN_DESC)

The Voucher Line Description comes from the Expense Line Description in the Input File. If the Expense Line Description is blank, the default value is the Account Description.

Voucher Line Number (VCHR_LN_NO)

The Voucher Line Number is a sequential number starting with 1 and increments by 1 for as many lines as are on the voucher.

The application sets the following fields to zero:

  • Extended Cost Amount (EXT_CST_AMT, TRN_EXT_CST_AMT)
  • Quantity (QTY)
  • Quantity Discrepancy Percentage (DISCR_QTY_RT)
  • Total Discrepancy Amount (DISCR_TOT_AMT, TRN_DISCR_TOT_AMT)
  • Unit Cost (UNIT_CST_AMT, TRN_UNIT_CST_AMT)
  • Unit Price Discrepancy (DISCR_UNIT_PRC_AMT, TRN_DISCR_UNIT_AMT)
  • Unit Price Discrepancy Percentage (DISCR_UNIT_PRC_RT)
  • Discount Amount (DISC_AMT, TRN_DISC_AMT)

The application sets the following fields to N:

  • CIS Report Flag (CIS_RPT_FL)
  • CIS Withholding Flag (CIS_WH_FL)
  • The application sets the following fields to a single space:
  • Purchase Order Line Type (S_PO_LN_TYPE)
  • Return Material Authorization Number (RMA_NO_ID)
  • Sales Tax Notes (SALES_TAX_NT)
  • VAT Supply Code (VAT_SUPPLY_DC)

The following fields are left null by the application:

  • Miscellaneous Line Charge Type (MISC_LN_CHG_TYPE)
  • Purchase Order Number (PO_ID)
  • Purchase Order Line Key (PO_LN_KEY)
  • Purchase Order Line Number (PO_LN_NO)
  • PO Release Number (PO_RLSE_NO)
  • Units of Measure (UM_CD)
  • VAT Supply Date (VAT_SUPPLY_DT)

AP/Voucher Line Account Table

The Voucher Line Account table is filled as follows:

Field Description
Voucher Key (VCHR_KEY)

The application gets this value from the VCHR_HDR table during the Import step.

Voucher Line Key (VCHR_LN_KEY)

The application gets this value from the VCHR_LN table during the Import step.

Voucher Line Account Key (VCHR_LN_ACCT_KEY)

The application sets this value using the Sequence Generator during the Import step.

1099 Flag (AP_1099_FL)

The 1099 Flag comes from the Input File. If no value is provided, the application sets this field to N. 

1099 Type Code (S_AP_1099_TYPE_CD)

The 1099 Type Code comes from the Input File. The application sets the field to NULL if the code is not provided or if the 1099 Flag is set to N.

Note: For Costpoint Cloud, you can choose to opt in to this feature for the Costpoint 8.2.15 release. The ability to opt in is temporary and will be removed when the feature becomes automatically enabled for all users as part of a future Costpoint release. See the Manage Opt-In Features topic for more information.
1099 State (AP_1099_STATE_CD)

The 1099 State code comes from the vendor's default 1099 state in Manage Vendors.

Account (ACCT_ID)

The Account is taken from the Input File.  

Cost Amount (CST_AMT, TRN_CST_AMT)

Costpoint computes the cost amount by subtracting the sales tax from the total before discount amount.

Cost Amount Percentage Rate (CST_AMT_PCT_RT)

The application computes the Cost Amount Percentage Rate by dividing the Total Before Discount Amount by the Invoice Amount from the Voucher Header record.

Line Charge Cost Amount (LN_CHG_CST_AMT, TRN_LN_CHG_CST_AMT)

The application sets the Line Charge Cost Amount to the same value as the Cost Amount fields.

Organization (ORG_ID)

The Organization is taken from the Input File.  

Organization Abbreviation (ORG_ABBRV_CD)

The Org Abbrev is taken from the Organization Table.

Project (PROJ_ID)

The Project is taken from the Input File. If a project is not provided, the application sets the field to NULL.  

Project Abbreviation (PROJ_ABBRV_CD)

If a Project is provided, the Proj Abbrev comes from the Project table. Otherwise, the application sets the field to NULL.

Recovery Amount (RECOVERY_AMT, TRN_RECOVERY_AMT)

The application sets the Recovery Amount to the same value as the Recovery Amount on the Voucher Line table.

Reference 1 (REF_1_ID)

The Reference 1 is taken from the Input File. If a Reference 1 is not provided, the application sets the field to NULL.   

Reference 2 (REF_2_ID)

The Reference 2 is taken from the Input File. If a Reference 2 is not provided, the application sets the field to NULL.  

Sales Tax Amount (SALES_TAX_AMT, TRN_SALES_TAX_AMT)

The Sales Tax Amount is taken from the Input File. If a Sales Tax Amount is not provided, the application sets the field to zero.

Use Tax Amount (USE_TAX_AMT, TRN_USE_TAX_AMT)

The Use Tax Amount is taken from the Input File. If a Use Tax Amount is not provided, the application sets the field to zero.  

Taxable Code (S_TAXABLE_CD)

The Taxable Code is taken from the input file. If no value is provided, the application sets this field to N. 

Taxable Flag (TAXABLE_FL)

The application sets the Taxable Flag to Y if the Taxable Code is S or U.  Otherwise, it uses N.

Total Before Discount Amount (TOT_BEF_DISC_AMT, TRN_TOT_BEF_DC_AMT)

The transaction Total Before Discount Amount is taken from Expense Amount in the input file. The application calculates the functional amount using the transaction amount and the exchange rates.

Net Amount (NET_AMT, TRN_NET_AMT)

The Net Amount is the same as the Total Before Discount Amount.

The application sets the following field to zero:

  • Discount Amount (DISC_AMT, TRN_DISC_AMT)

The application sets the following fields to a single space:

  • Template ID (FA_TMPLT_ID)
  • Template Revision ID (FA_TMPLT_RVSN_ID)

The following fields are left null by the application:

  • Template Key (FA_TMPLT_KEY)
  • Project Account Abbreviation (PROJ_ACCT_ABBRV_CD)