Input Files and Database Tables
The Import Vendors preprocessor allows you to import vendor information either from an ASCII input file produced from PRO-III system or a database table.
This help topic contains information on how to prepare an input file or database table that Costpoint can use to import vendor records.
ASCII Input File
The input file used by the Import Vendors preprocessor must be a standard ASCII text file without null characters. Each record to be included must be on a separate line in the input file, separated with a combination of carriage return and line feed characters (ASCII decimal characters 13 and 10).
Each record must be in a separate line in the input file. There are separate formats for vendor and vendor address records. Vendor records are identified by the V record type and will be imported to the VEND table, while Vendor address records are identified by the A record type and will be imported to the VEND_ADDR table.
When starting a line in the input file, you must first enter the record type as the first field to identify the record as a vendor or vendor address. Costpoint then imports that record to the appropriate table.
See the Vendor Input File Format (VEND) and Vendor Address Input File Format (VEND_ADDR) tables for the input file layouts expected by Costpoint.
Database Tables
You can use database tables as the input source for importing vendor or vendor address records. You must create an AOPUTLVU_INP_VEND table for vendor records and an AOPUTLVU_INP_VENDA table for vendor address records. These tables use the same format as the input file but the first field uses status codes, instead of record type. Status codes for both input table types must be set to 'U' to indicate that a record is unprocessed or has not been imported yet into Costpoint.
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.
Vendor Input File Format (VEND)
Use this input file layout to enter a vendor record (record type 'V'). The following table lists the entries contained in each record of the vendor input file.
For Costpoint Essentials users, the following columns are processed by default, regardless of the values you entered in the input file:
- Vendor Group Code (VEND_GRP_CD): The value of this column is ignored and not entered in the database.
- NON-US EFT Active (NON_US_ACTIVE_FL): The value of this column is set to N by default.
- NON-US Bank ID (NON_US_BANK_ID): The value of this column is ignored and not entered in the database.
- Enable Supplier Portal: (SUPPLIER_PORTAL_FL): The value of this column is set to N by default.
| Seq. No. | Column Name | Costpoint Table/Column | Type | Length | Required or Optional | Format |
|---|---|---|---|---|---|---|
| 1 | Record Type/ Status Code | N/A | Character | 1 | Required | Must be V for Vendor. |
| 2 | Vendor ID | VEND_ID | Alphanumeric | 12 | Required | |
| 3 | Vendor Approval Code | VEND_APPRVL_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 4 | Vendor Name | VEND_NAME | Alphanumeric | 25 | Required | |
| 5 | Vendor Long Name | VEND_LONG_NAME | Alphanumeric | 40 | Optional | |
| 6 | Vendor Terms | TERMS_DC | Alphanumeric | 15 | Optional | |
| 7 | Location | VEND_NAME_EXT | Alphanumeric | 6 | Optional | |
| 8 | Hold Payment Flag | HOLD_PMT_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 9 | 1099 Tax ID | AP_1099_TAX_ID | Alphanumeric | 20 | Optional | |
| 10 | 1099 Type Code | S_AP_1099_TYPE_CD | Alphanumeric | 6 | Optional | |
| 11 | Print 1099 | PRNT_1099_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 12 | Allow Auto Voucher | AUTO_VCHR_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 13 | A/P Account Description | AP_ACCTS_KEY | Alphanumeric | 30 | Optional | |
| 14 | Cash Account Description | CASH_ACCTS_KEY | Alphanumeric | 30 | Optional | |
| 15 | Vendor Notes | VEND_NOTES | Alphanumeric | 254 | Optional | |
| 16 | Vendor Group Code | VEND_GRP_CD | Alphanumeic | 6 | Optional | |
| 17 | Pay Vendor | AP_CHK_VEND_ID | Alphanumeric | 12 | Optional | |
| 18 | Allow Edits to Pay Vendor | ED_VCH_PAY_VEND_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 19 | Pay When Paid | PAY_WHEN_PAID_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 20 | Separate Check | SEP_CHK_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 21 | Vendor Status for PO | S_VEND_PO_CNTL_CD | Character | 1 | Optional | |
| 22 | Business Size Class Code | S_CL_SM_BUS_CD | Character | 1 | Optional | Enter S (Small) or L (Large). |
| 23 | Woman Owned | CL_WOM_OWN_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 24 | Disadvantaged | CL_DISADV_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 25 | HUB Zone | CL_LAB_SRPL_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 26 | Historic Black College | CL_HIST_BL_CLG_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 27 | Veteran Owned | CL_VET_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 28 | Service Disabled Veteran Owned | CL_SD_VET_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 29 | Blank Laser Check Memo | CHK_MEMO_S | Alphanumeric | 25 | Optional | |
| 30 | Customer Account | CUST_ACCT_FLD | Alphanumeric | 20 | Optional | |
| 31 | Employee ID | EMPL_ID | Alphanumeric | 12 | Optional | |
| 32 | ANC and Indian Tribes | CL_ANC_IT_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 33 | Certification Date | VEND_CERT_DT | Date | 10 | Optional | |
| 34 | Certification Number | Number | 20 | Optional | ||
| 35 | eProcurement Vendor | EPROCURE_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 36 | FOB | FOB_FLD | Alphanumeric | 15 | Optional | |
| 37 | Ship Via | SHIP_VIA_FLD | Alphanumeric | 15 | Optional | |
| 38 | Payroll Vendor | PR_VEND_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 39 | Time Collection Expense Class | Alphanumeric | 20 | Optional | ||
| 40 | Vendor 1099 Name | VEND_1099_NAME | Alphanumeric | 40 | Optional | |
| 41 | SM Subcontractor Flag | SM_SUBCTR_FL | Character | 1 | Optional | Y (Yes) or
N (No)
For use if licensed for Subcontractor Management. If null, this will be set to N. |
| 42 | Vendor Employee Approval Group Code | VE_APPRVL_GRP_CD | Character | 6 | Optional | For use if:
- SM license is ON or available and - SM Subcontractor Flag = Y and - Vendor Employee Requires Approval = Y in AP_SETTINGS table and - Use Vendor Employee Approval Groups = Y in AP_SETTINGS table This is set to NULL if: - Subcontractor Management (SM) license is OFF or -SM license is ON and SM Subcontractor Flag = N. or - SM license is ON and SM Subcontractor Flag = Y and Vendor Employee Requires Approval = N in AP_SETTINGS table or - SM license is ON and SM Subcontractor Flag = Y and Vendor Employee Requires Approval = Y in AP_SETTINGS table and Use Vendor Employee Approval Groups = N in AP_SETTINGS table. |
| 43 | DUNS Number | DUNS_NO | Alphanumeric | 15 | Optional | |
| 44 | CAGE Code | CAGE_CD | Alphanumeric | 15 | Optional | |
| 45 | 8(a) Certified | CL_8A_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 46 | AbilityOne Non-Profit Agency | CL_ABIL_ONE_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 47 | GovWin IQ Company ID | GOVWIN_COMP_ID | Alphanumeric | 15 | Optional | |
| 48 | Vendor Web Site | VEND_WEB_SITE | Alphanumeric | 1024 | Optional | |
| 49 | UEI Number | UEI_NO | Alphanumeric | 12 | Optional | |
| 50 | Enable Digital Signature | DIGITAL_SIG_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 51 | Enable Supplier Portal | SUPPLIER_PORTAL_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 52 | Intracompany Vendor | IC_VEND_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 53 | Performing Company | PERF_COMPANY_ID | Alphanumeric | 10 | Required if you enter Y in the Intracompany column. Otherwise, optional. | |
| 54 | CMMC Level | CMMC_LEVEL | Alphanumeric | 10 | Optional | |
| 55 | LGBTQ+ Owned | CL_LGBTQ_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 56 | 1099 State | AP_1099_STATE_CD | Alphanumeric | 2 | Optional | The value you enter in this field must match an existing state code in Costpoint. Refer to the
States and Codes special topic to view valid state codes.
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.
|
Vendor Address Input File Format (VEND_ADDR)
Use this input file layout to enter a vendor address record (record type 'A'). The following table lists the entries contained in each record of the vendor address input file.
| Seq. No. | Column Name | Costpoint Table/Column | Type | Length | Required or Optional | Format |
|---|---|---|---|---|---|---|
| 1 | Record Type/ Status Code | N/A | Character | 1 | Required | Must be A for Vendor Address. |
| 2 | Vendor ID | VEND_ID | Alphanumeric | 12 | Required | |
| 3 | Address Code | ADDR_DC | Alphanumeric | 10 | Required | |
| 4 | US EFT Active | ACTIVE_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 5 | Order Address Code | S_ORD_ADDR_CD | Character | 1 | Optional | |
| 6 | Payment Address Code | S_PMT_ADDR_CD | Character | 1 | Required | |
| 7 | Address Line 1 | LN_1_ADR | Alphanumeric | 40 | Optional | |
| 8 | Address Line 2 | LN_2_ADR | Alphanumeric | 40 | Optional | |
| 9 | Address Line 3 | LN_3_ADR | Alphanumeric | 40 | Optional | |
| 10 | City Name | CITY_NAME | Alphanumeric | 25 | Optional | |
| 11 | Payment State | MAIL_STATE_DC | Alphanumeric | 15 | Optional | |
| 12 | Payment Country | COUNTRY_CD | Alphanumeric | 8 | Optional | |
| 13 | Postal Code | POSTAL_CD | Alphanumeric | 10 | Optional | |
| 14 | Phone ID | PHONE_ID | Number | 25 | Optional | |
| 15 | Other Phone ID | OTH_PHONE_ID | Number | 25 | Optional | |
| 16 | Fax No. | FAX_ID | Number | 25 | Required | |
| 17 | EMAIL_ID | Alphanumeric | 100 | Required | ||
| 18 | EFT Payment | EFT_PMT_CD | Character | 6 | Optional | |
| 19 | Bank ID. (ABA No.) | BANK_ABA_NO | Alphanumeric | 9 | Required if you enter Y in the US EFT Active column. Otherwise, optional. | |
| 20 | Bank Account | BANK_ACCT_ID_S | Number | 17 | Required if you enter
Y in the
US EFT Active column and the
Non-US Bank Account column is blank.
Otherwise, optional. |
|
| 21 | ACH Code | S_ACH_TRN_CD | Alphanumeric | 2 | Optional | |
| 22 | Non-US Bank Account | NON_US_BANK_ACCT_ID | Alphanumeric | 25 | Optional | |
| 23 | Print EFT Advice | PRINT_EFT_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
| 24 | Originator ID Code | EDI_N104_CD | Alphanumeric | 80 | Optional | |
| 25 | Bank Reference | BANK_ACCT_REF_CD | Alphanumeric | 18 | Optional | |
| 26 | IBAN Code | IBAN_CD | Alphanumeric | 34 | Optional | |
| 27 | Ship ID | SHIP_ID | Alphanumeric | 20 | Optional | |
| 28 | Sales Tax Code | SALES_TAX_CD | Alphanumeric | 6 | Optional | |
| 29 | Intermediary Bank ID | IB_BANK_ID | Alphanumeric | 15 | Optional | |
| 30 | SWIFT Code | SWIFT_CD | Alphanumeric | 11 | Optional | |
| 31 | NON-US EFT Active? | NON_US_ACTIVE_FL | Character | 1 | Optional | |
| 32 | NON-US Bank ID | NON_US_BANK_ID | Alphanumeric | 34 | Optional | |
| 33 | Email EFT Advice | EMAIL_EFT_FL | Character | 1 | Optional | Enter Y (Yes) or N (No) |
| 34 | UEI Number | UEI_NO | Alphanumeric | 12 | Optional |