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/ Tatus 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	Women 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

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/ Tatus 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	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	1	Optional
19	Bank ID. (ABA No.)	BANK_ABA_NO	Alphanumeric	9	Optional
20	Bank Account	BANK_ACCT_ID_S	Number	17	Optional
21	ACH Code	S_ACH_TRN_CD	Alphanumeric	2	Optional
22	Non-US Bank Account		Number	17	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