Costpoint Online Help

Using the Help Search
Using the Help Search
  • Phrase:Returns all topics that contain the exact phrase in the search criteria. Use quotation marks around the phrase.
    Ex."system administration"
  • And:Returns all topics that contain all the words in the search criteria, in any combination. Insert a plus sign between all words in the search criteria.
    Ex.system + administration
  • Or:Returns all topics with any combination of words in the search criteria. This is the default.
    Ex.system administration
For more information:Searching Online Help

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.

Note: The input file layout may contain fields that are available only in Costpoint Advanced. If you are using Costpoint Essentials, these fields are excluded from the import process and may be left blank or null when you create the input file.

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.

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 Supplier Portal SUPPLIER_PORTAL_FL Character 1 Optional Enter Y (Yes) or N (No).
51 CMMC Level CMMC_LEVEL Alphanumeric 10 Optional Enter:
  • N: None
  • 1: Performed processes, basic cyber hygiene practices
  • 2: Documented processes, intermediate cyber hygiene practices
  • 3: Managed processes, good cyber hygiene practices
  • 4: Reviewed processes, proactive cyber hygiene practices
  • 5: Optimizing processes, advanced/progressive cyber hygiene practices
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.

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 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