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:
|
Approved Flag (APPRVD_FL) |
The approved flag is set by the application as follows:
|
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) |
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:
The application sets the following fields to zero:
The application sets the following fields to a single space:
The following fields are left null by the application:
|
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:
The application sets the following fields to N:
The following fields are left null by the application:
|
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:
The application sets the following fields to a single space:
The following fields are left null by the application:
|