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.
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.
| 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 Supplier Portal | SUPPLIER_PORTAL_FL | Character | 1 | Optional | Enter Y (Yes) or N (No). |
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 Active column. Otherwise, optional. | |
| 20 | Bank Account | BANK_ACCT_ID_S | Number | 17 | Required if you enter
Y in the
Active column and the
Non-US Bank Account column is blank.
Otherwise, optional. |
|
| 21 | ACH Code | S_ACH_TRN_CD | Alphanumeric | 2 | Required if you enter Y in the Active column. Otherwise, optional. | |
| 22 | Non-US Bank Account | NON_US_BANK_ACCT_ID | Alphanumeric | 25 | Required if you enter
Y in the
Active column and the
Bank Account column is blank.
Otherwise, 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) |