Single Insert Scripts for Suppliers in 11i Applications

This Document provides the guidance to the user with the necessary information for creating Suppliers,Supplier Sites and Supplier Contacts through Single Insert in an accurate way.

The Interface tables used here are :

1. AP_SUPPLIERS_INT

1. AP_SUPPLIER_SITES_INT

1. AP_SUP_SITE_CONTACT_INT

Insertion steps

Create Suppliers :

Following are the steps that must be followed, in order, to perform the Single insertions for Creating Suppliers :

<><><><>

Execution Step	Description
Step 1	select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2	The value of vendor_interface_id can be passed through sequence ap_suppliers_int_s.NEXTVAL.
Step 3	select * from po_vendors where vendor_name = 'TEST_VENDOR' If no rows returned from the above query,take this vendor name and pass it to the interface table ap_suppliers_int.
Step 4	Vendor Type Look Up Code must be passed as Vendor.
Step 5	select term_id,name from ap_terms Any term_id and name can be taken from the above query and pass it to the interface table ap_suppliers_int.Here,we passed terms_id as 10112 and name as GR_NET15.
Step 6	Pass ‘Y’ for all the flags such as always_take_disc_flag,inspection_required_flag, receipt_required_flag, auto_tax_calc_flag, federal_reportable_flag and create_debit_memo_flag in the interface table ap_suppliers_int.
Step 7	Pass set_of_books_id as 1 and invoice_currency_code and payment_currency_code as ‘USD’ to the interface table ap_suppliers_int.
Step 8	The values payment_method_lookup_code and terms_date_basis must be passed as per the requirement.Here,we are passing payment_method_lookup_code as ‘CHECK’ and terms_date_basis as ‘Invoice’ to the interface table ap_suppliers_int.
Step 9	Pass qty_rcv_tolerance as 0, qty_rcv_exception_code as ‘WARNING’ enforce_ship_to_location_code as ‘NONE’ and receipt_days_exception_code as ‘WARNING’ to the interface table ap_suppliers_int.
Step 10	The values pay_date_basis_lookup_code and payment_priority must be passed as per the requirement.Here,we are passing pay_date_basis_lookup_code as ‘DISCOUNT ’and payment_priority as 99 to the interface table ap_suppliers_int.
Step 11	Pass the Who columns created_by, last_updated_by as user_id. and creation_date,last_update_date and the value for start_date_active can be passed as SYSDATE. The remaining leftout values in the interface table ap_suppliers_int can be passed as NULL.
Step 12	To insert the data into the suppliers interface table INSERT INTO ap_suppliers_int (vendor_interface_id, last_update_date, last_updated_by, vendor_name, creation_date, created_by, vendor_type_lookup_code, terms_id, terms_name, set_of_books_id, always_take_disc_flag, pay_date_basis_lookup_code, payment_priority, invoice_currency_code, payment_currency_code, num_1099, type_1099, start_date_active, payment_method_lookup_code, terms_date_basis, inspection_required_flag, receipt_required_flag, qty_rcv_tolerance, qty_rcv_exception_code, enforce_ship_to_location_code, receipt_days_exception_code, auto_tax_calc_flag, federal_reportable_flag, match_option, create_debit_memo_flag ) VALUES (ap_suppliers_int_s.NEXTVAL, --vendor_interface_id SYSDATE, --last_update_date 1007910, --last_updated_by 'TEST_VENDOR',--vendor_name SYSDATE, --creation_date 1007910,--created_by 'VENDOR',--vendor_type_lookup_code 10112,--terms_id 'GR_NET15',--terms_name 1, --set_of_books_id 'Y', --always_take_disc_flag 'DISCOUNT',--pay_date_basis_lookup_code 99,--payment_priority 'USD',--invoice_currency_code 'USD',--payment_currency_code NULL,--num_1099 NULL, --type_1099 SYSDATE,--start_date_active 'CHECK', --payment_method_lookup_code 'Invoice',--terms_date_basis 'Y',--inspection_required_flag 'Y', --receipt_required_flag 0, --qty_rcv_tolerance 'WARNING',--qty_rcv_exception_code 'NONE', --enforce_ship_to_location_code 'WARNING',--receipt_days_exception_code 'Y',--auto_tax_calc_flag 'Y',--federal_reportable_flag NULL,--match_option 'Y'--create_debit_memo_flag );
Step 13	Run Import Program for Suppliers through the following navigation : Responsibility : Payables Manager Navigation : Other -> Requests -> Run Program Name : Supplier Open Interface Import
Step 15	Once the import program is successful, please check the following query in the interface table : select * from ap_suppliers_int Where trunc(creation_date) = trunc(sysdate) On successful import,the record remains in the interface table ap_suppliers_int itself with the status as ‘PROCESSED’. Then, once we observe that the status is PROCESSED in the interface table ap_suppliers_int, query the following base table as follows : select * from po_vendors Where trunc(creation_date) = trunc(sysdate)

Create Supplier Sites :

Following are the steps that must be followed, in order, to perform the Single insertions for Creating Supplier Sites for the above supplier :

<><><><>

Execution Step	Description
Step 1	select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2	Take vendor_id from the po_vendors table which we get once the supplier is created.This can be taken from the following query and can be passed to the interface table ap_supplier_sites_int. select vendor_id from po_vendors Where trunc(creation_date) = trunc(sysdate)
Step 3	Vendor_Site_Code must be passed in such a way that the value passed is unique.Here,we are passing it as ‘MAR-SANJOSE27’. Note : Before passing the value for vendor_site_code,please check the values in po_vendor_sites_all whether the value being passed already exists.
Step 4	Address Values such as AddressLine1,AddressLine2 and AddressLine3 and Phone Number,FaxNumber etc..can be passed any.But see that atleast one of them is passed to the interface table ap_supplier_sites_int. The values for city,state and postal code must be relevant to each other and must be valid.Please take the appropriate values from Vertex tables and pass them to the interface table ap_supplier_sites_int.
Step 5	The values for ship_to_location_id,ship_to_location_code and bill_to_location_id,bill_to_location_code can be passed on taking the location_id and location_code combination from hr_locations table. Here,we have taken (ship_to_location_id,ship_to_location_code) -> (20494, CSP10121) and (bill_to_location_id,bill_to_location_code) -> (20549, CSP10140) to the interface table ap_supplier_sites_int.
Step 6	The value for freight_terms_lookup_code must be passed as per the requirement.Here,we are passing it as ‘Due’. The value payment_method_lookup_code must be passed as per the requirement.Here,we are passing payment_method_lookup_code as ‘CHECK’ to the interface table ap_supplier_sites_int.
Step 7	The value for pay_group_lookup_code must be passed from any of the values extracted from the following query : select lookup_code from po_lookup_codes where lookup_type = 'PAY GROUP' Here,we are considering DOMESTIC.
Step 8	The values pay_date_basis_lookup_code and payment_priority must be passed as per the requirement.Here,we are passing pay_date_basis_lookup_code as ‘DISCOUNT ’and payment_priority as 99 to the interface table ap_supplier_sites_int.
Step 9	select term_id,name from ap_terms Any term_id and name can be taken from the above query and pass it to the interface table ap_supplier_sites_int.Here,we passed terms_id as 10112 and name as GR_NET15.
Step 10	The flags primary_pay_site_flag and auto_tax_calc_flag must be passed as ‘N’ and the flags always_take_disc_flag and create_debit_memo_flag must be passed as ‘Y’ to the interface table ap_supplier_sites_int.
Step 11	The value for supplier_notif_method must be passed as per the requirement.Here,we are passing it as EMAIL.
Step 12	The values for invoice_currency_code and payment_currency_code as ‘USD’ to the interface table ap_supplier_sites_int. The values for fax and fax_area_code can be any.
Step 13	The value being passed for vendor_site_code_alt must be the vendor name which we give during create suppliers stage.
Step 14	The values being passed for org_id and operating_unit_name must be any of the values from the following query : select organization_id,name from hr_operating_units Here,we are considering org_id as 204 and operating_unit_name as Vision Operations.
Step 15	The who column creation_date must be passed as SYSDATE. The remaining leftout values in the interface table ap_supplier_sites_int can be passed as NULL.
Step 16	-insert into the supplier sites table INSERT INTO ap_supplier_sites_int (vendor_id, vendor_site_code, purchasing_site_flag, pay_site_flag, address_line1, address_line2, address_line3, city, state, zip, country, area_code, phone, ship_to_location_id, ship_to_location_code, bill_to_location_id, bill_to_location_code, --ship_via_lookup_code, freight_terms_lookup_code, fob_lookup_code, fax, fax_area_code, payment_method_lookup_code, accts_pay_code_combination_id, prepay_code_combination_id, pay_group_lookup_code, payment_priority, terms_name, terms_id, pay_date_basis_lookup_code, always_take_disc_flag, invoice_currency_code, payment_currency_code, tax_reporting_site_flag, org_id, operating_unit_name, address_line4, vendor_site_code_alt, auto_tax_calc_flag, match_option, create_debit_memo_flag, supplier_notif_method, primary_pay_site_flag, attribute13, creation_date ) VALUES (34925,--vendor_id LTRIM (RTRIM ('MAR-SANJOSE27')),--vendor_site_code LTRIM (RTRIM ('Y') ), --purchasing_site_flag LTRIM (RTRIM ('Y')),--pay_site_flag LTRIM (RTRIM ('ABCDE NEW ADDR')),--address_line1 NULL,--address_line2 NULL,--address_line3 LTRIM (RTRIM ('SAN JOSE')),--city LTRIM (RTRIM ('CA')),--state LTRIM (RTRIM ('95126')),--zip LTRIM (RTRIM ('US')),--country LTRIM (RTRIM ('800')),--area_code LTRIM (RTRIM (8002238565)),--phone LTRIM (RTRIM (20494)),--ship_to_location_id LTRIM (RTRIM ('CSP10121')),--ship_to_location_code LTRIM (RTRIM (20549)),--bill_to_location_id LTRIM (RTRIM ('CSP10140')),--bill_to_location_code --LTRIM (RTRIM ('WAT')), LTRIM (RTRIM ('Due') ),--freight_terms_lookup_code NULL,--fob_lookup_code LTRIM (RTRIM (7708185548)),--fax LTRIM (RTRIM ('770')),--fax_area_code 'CHECK',--payment_method_lookup_code NULL,--accts_pay_code_combination_id NULL,--prepay_code_combination_id 'DOMESTIC',--pay_group_lookup_code 99,--payment_priority 'GR_NET15',--terms_name 10112,--terms_id 'DISCOUNT',--pay_date_basis_lookup_code 'Y',--always_take_disc_flag 'USD',--invoice_currency_code 'USD',--payment_currency_code NULL,--tax_reporting_site_flag 204,--org_id 'Vision Operations',--operating_unit_name NULL,--address_line4 LTRIM (RTRIM ('TEST_VENDOR')),--vendor_site_code_alt 'N',--auto_tax_calc_flag NULL,--match_option 'Y',--create_debit_memo_flag 'EMAIL',--supplier_notif_method 'N',--primary_pay_site_flag NULL,--attribute13 SYSDATE --creation_date );
Step 17	Run Import Program for Supplier Sites through the following navigation : Responsibility : Payables Manager Navigation : Other -> Requests -> Run Program Name : Supplier Sites Open Interface Import
Step 18	Once the import program is successful, please check the following query in the interface table : select * from ap_supplier_sites_int Where trunc(creation_date) = trunc(sysdate) On successful import,the record remains in the interface table ap_supplier_sites_int itself with the status as ‘PROCESSED’. Then, once we observe that the status is PROCESSED in the interface table ap_supplier_sites_int, query the following base table as follows : select * from po_vendor_sites_all Where trunc(creation_date) = trunc(sysdate)

Creating Supplier Contacts :

Following are the steps that must be followed, in order, to perform the Single insertions for Creating Supplier Contacts :

<><><><>

Execution Step	Description
Step 1	select user_id created_by from fnd_user where user_name = ‘SAYYADEVARA’
Step 2	The values being passed for org_id and operating_unit_name must be any of the values from the following query : select organization_id,name from hr_operating_units Here,we are considering org_id as 204 and operating_unit_name as Vision Operations.
Step 3	The value for vendor_site_id must be passed on extracting the vendor_site_id for the supplier created during create supplier stage.It can be taken and passed to the interface table ap_sup_site_contact_int using the following query : select vendor_site_id from po_vendor_sites_all Where trunc(creation_date) = trunc(sysdate)
Step 4	The values first_name and last_name must be passed to the interface table ap_sup_site_contact_int such that they are unique for that supplier.Check in the base table po_vendor_contacts with the value being passed for the first name and last name.
Step 5	The values for area_code,fax,phone,fax_area_code,email_address and url can be passed any.The who column creation_date must be passed as SYSDATE.
Step 6	insert into the supplier contacts table INSERT INTO ap_sup_site_contact_int ( vendor_site_id, vendor_site_code, first_name, last_name, area_code, phone, org_id, fax, fax_area_code, email_address, url, operating_unit_name, creation_date ) VALUES ( 5217,--vendor_site_id 'MAR-SANJOSE27',--vendor_site_code 'First',--first_name 'Second',--last_name '800', --area_code 8002238565,--phone 204, --org_id 7708185548,--fax '770', --fax_area_code mailto:'n_jayakrishna@yahoo.com',--email_address,--email_address 'www.yahoo.com',--url 'Vision Operations', --operating_unit_name SYSDATE --creation_date );
Step 7	Run Import Program for Supplier Contacts through the following navigation : Responsibility : Payables Manager Navigation : Other -> Requests -> Run Program Name : Supplier Site Contacts Open Interface Import
Step 8	Once the import program is successful, please check the following query in the interface table : select * from ap_sup_site_contact_int Where trunc(creation_date) = trunc(sysdate) On successful import,the record remains in the interface table ap_sup_site_contact_int itself with the status as ‘PROCESSED’. Then, once we observe that the status is PROCESSED in the interface table ap_sup_site_contact_int, query the following base table as follows : select * from po_vendor_contacts Where trunc(creation_date) = trunc(sysdate) Supplier information can be checked from front-end through the following navigation : Responsibility : Payables Manager Navigation : Suppliers -> Entry Query with the Supplier or Vendor Created. Here,We can query with TEST_VENDOR.

Pre-Requisites

<><><><>

Execution Step	Description
Step 1	There must not be any existing supplier or supplier site with the same supplier name or site which we are creating.Supplier and Supplier Site must be unique.
Step 2	The contact details such as first name and last name must be unique for the supplier created.