Oracle R12 Supplier Headers, Sites and Contacts Single Insertion Scripts

The Interface table used here is:

1. AP_SUPPLIERS_INT

2. AP_SUPPLIER_SITES_INT

3. AP_SUPP_SITE_CONTACT_INT

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

Execution Step	Description
Step 1	Select organization_id from HR_OPERATING_UNITS where organization_id= 98; --For Example ChoosecORGANIZATION_ID=98
Step 2	Supplier type should be define. SELECT DISTINCT lookup_code FROM apps.po_lookup_codes poc WHERE poc.lookup_type = 'VENDOR TYPE'
Step 3	Terms type should be define. SELECT term_id.name FROM ap_terms WHERE NAME = 'NET30';
Step 4	Currency code should be define. SELECT currency_code FROM fnd_currencies_tl WHERE LANGUAGE='US'.
Step 5	Country code should be define. SELECT DISTINCT territory_code FROM fnd_territories_tl WHERE LANGUAGE = 'US'
Step 6	Freight terms should be define. SELECT lookup_code FROM apps.fnd_lookup_values WHERE lookup_type LIKE 'FREIGHT TERMS' AND LANGUAGE = 'US'
Step 7	FOB code should be define. SELECT DISTINCT lookup_code FROM apps.fnd_lookup_values WHERE lookup_type = 'FOB' AND LANGUAGE = 'US'
Step 8	Ship via should be define. SELECT DISTINCT ofv.freight_code FROM apps.org_freight_vl ofv,apps.financials_system_params_all fsp WHERE ofv.organization_id = fsp.inventory_organization_id
Step 9	Pay date basis code should be define. SELECT DISTINCT lookup_code FROM apps.fnd_lookup_values WHERE lookup_type = 'PAY DATE BASIS' AND LANGUAGE = 'US'
Step 10	Vat rate code should be define. SELECT DISTINCT lookup_code FROM apps.fnd_lookup_values WHERE lookup_type='ZX_INPUT_CLASSIFICATIONS' AND LANGUAGE ='US'
Step 11	Invoice match option R ---> Receipt P ---> Purchase I ---> Invoice
Step 12	Match Approval Level Receipt_required_flag and Inspection_required_flag respectively Y , Y ---> 4-Way Y , N ---> 3-Way N , N ---> 2-Way
Step 13	Select user_id from fnd_user where user_name='CONVERSION'; Pass the user_id which we get from the query.
	Along with the Specific Columns, we need to pass the following columns Standard WHO Columns like CREATED_BY, LAST_UPDATED_BY = USER_ID And CREATION_DATE, LAST_UPDATE_DATE = SYSDATE.

Header Level :-
VENDOR_INTERFACE_ID = sequence(AP.AP_SUPPLIERS_INT_S.NEXTVAL) should be
passed.

INSERT INTO apps.ap_suppliers_int
(
vendor_interface_id ,
last_update_date ,
last_updated_by ,
vendor_name ,
summary_flag ,
enabled_flag ,
creation_date ,
created_by ,
employee_id ,
ship_to_location_code ,
bill_to_location_code ,
vendor_type_lookup_code ,
one_time_flag ,
terms_id ,
num_1099 ,
payment_method_lookup_code ,
women_owned_flag ,
small_business_flag ,
vat_registration_num ,
create_debit_memo_flag ,
exclude_freight_from_discount,
hold_unmatched_invoices_flag ,
match_option ,
receipt_required_flag ,
inspection_required_flag ,
federal_reportable_flag ,
type_1099 ,
vendor_name_alt ,
invoice_currency_code ,
payment_currency_code ,
organization_type_lookup_code,
pay_group_lookup_code ,
pay_date_basis_lookup_code
)
VALUES
(
apps.ap_suppliers_int_s.NEXTVAL , --> vendor_interface_id
SYSDATE , --> last_update_date
1451 , --> last_updated_by
'Test Sup 001' , --> vendor_name
'N' , --> summary_flag
'Y' , --> enabled_flag
SYSDATE , --> creation_date
1451 , --> created_by
NULL , --> employee_id
'US_MA_DEVENS' , --> ship_to_location_code
'US_MA_DEVENS' , --> bill_to_location_code
'VENDOR' , --> vendor_type_lookup_code
'N' , --> one_time_flag
10004 , --> terms_id
'TaxPID 002' , --> num_1099
'CHECK' , --> payment_method_lookup_code
'Y' , --> women_owned_flag
'Y' , --> small_business_flag
'TaxREGID 002' , --> vat_registration_num
'Y' , --> create_debit_memo_flag
'Y' , --> exclude_freight_from_discount
'Y' , --> hold_unmatched_invoices_flag
'R' , --> match_option
'Y' , --> receipt_required_flag
'Y' , --> Inspection_required_flag
'Y' , --> federal_reportable_flag
'MISC1' , --> type_1099
'Single record insertion Testing Supplier' , --> vendor_name_alt
'USD' , --> invoice_currency_code
'USD' , --> payment_currency_code
'CORPORATION' , --> organization_type_lookup_code
'CA' , --> pay_group_lookup_code
'DISCOUNT' --> pay_date_basis_lookup_code
);

COMMIT;

Once you loaded records into Interface table now we must run the Import
Program.

Payables Manager Responsibility->Other->Requests->Run->submit new
concurrent request

Supplier Open Interface Import

Pass the value to Parameters which is assigned to the Program
1)Import Options → New
2)Import Options → 1000
3)Print Exceptions Only → No
4)Debug Switch → No
5)Trace Switch → No

Once you submit we can cross check error table AP_SUPPLIER_INT_REJECTIONS if
any records got rejected.

If there are no rejections Check the base tables AP_SUPPLIERS whether the records are populated .

Site Level :-
1. VENDOR_SITE_INTERFACE_ID = sequence(AP.AP_SUPPLIER_SITES_INT_S.NEXTVAL) should be passed .
2. get the Vendor ID from Header level and insert into sites level

INSERT INTO apps.ap_supplier_sites_int
(
vendor_site_interface_id ,
last_update_date ,
last_updated_by ,
vendor_id ,--> Get the VENDOR ID from headers
vendor_site_code ,
creation_date ,
created_by ,
purchasing_site_flag ,
pay_site_flag ,
address_line1 ,
address_line2 ,
address_line3 ,
city ,
state ,
zip ,
country ,
phone ,
fax ,
ship_to_location_code ,
bill_to_location_code ,
payment_method_lookup_code ,
terms_id ,
create_debit_memo_flag ,
hold_unmatched_invoices_flag ,
org_id ,
email_address ,
match_option ,
exclude_freight_from_discount,
invoice_currency_code ,
payment_currency_code ,
country_of_origin_code ,
primary_pay_site_flag ,
freight_terms_lookup_code ,
fob_lookup_code ,
ship_via_lookup_code ,
customer_num ,
pay_group_lookup_code ,
pay_date_basis_lookup_code ,
vat_code ,
always_take_disc_flag
)
VALUES
(
apps.ap_supplier_sites_int_s.NEXTVAL , --> vendor_site_interface_id
SYSDATE , --> last_update_date
1451 , --> last_updated_by
36007 , --> vendor_id , --> Get the VENDOR ID from headers
'Test MA Site_1' , --> vendor_site_code
SYSDATE , --> creation_date
1451 , --> created_by
'Y' , --> purchasing_site_flag
'Y' , --> pay_site_flag
'Street no - 01' , --> address_line1
'HMT Nagar' , --> address_line2
'Nacharam' , --> address_line3
'Hyderabad' , --> city
'Andra Pradesh' , --> state
'5000078' , --> zip
'IN' , --> country
'+91 4444 333333' , --> phone
'040 40404040' , --> fax
'US_MA_DEVENS' , --> ship_to_location_code
'US_MA_DEVENS' , --> bill_to_location_code
'CHECK' , --> payment_method_lookup_code
10006 , --> terms_id
'Y' , --> create_debit_memo_flag
'N' , --> hold_unmatched_invoices_flag
98 , --> org_id
apps123@oracle.com , --> email_address
'R' , --> match_option
'Y' , --> exclude_freight_from_discount
'USD' , --> invoice_currency_code
'USD' , --> payment_currency_code
'IN' , --> country_of_origin_code
'Y' , --> primary_pay_site_flag
'TBD' , --> freight_terms_lookup_code
'FOB' , --> fob_lookup_code
'TBD' , --> ship_via_lookup_code
'Test Cust 001' , --> customer_num
'CA' , --> pay_group_lookup_code
'DISCOUNT' , --> pay_date_basis_lookup_code
'17% VAT' , --> vat_code
'Y' --> always_take_disc_flag
);

COMMIT;

Once you loaded records into Interface table now we must run the Import
Program.

Payables Manager Responsibility->Other->Requests->Run->submit new
concurrent request

Supplier Sites Open Interface Import

Pass the value to Parameters which is assigned to the Program
1)Import Options → New
2)Import Options → 1000
3)Print Exceptions Only → No
4)Debug Switch → No
5)Trace Switch → No

Once you submit we can cross check error table AP_SUPPLIER_INT_REJECTIONS if
any records got rejected.

If there are no rejections
Check the base tables AP_SUPPLIER_SITES_ALL whether the records are populated .

Contact Level :-
1. VENDOR_CONTACT_INTERFACE_ID =
sequence(AP.AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) should be passed.
2. Get the Vendor ID , Vendor Site ID from Site level and insert into contact level.

INSERT INTO ap.ap_sup_site_contact_int
(
vendor_contact_interface_id,
first_name ,
last_name ,
email_address ,
fax ,
phone ,
vendor_id ,
vendor_site_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
operating_unit_name ,
org_id ,
department
)
VALUES
(
ap.ap_sup_site_contact_int_s.NEXTVAL , --> vendor_contact_interface_id
'First Name' , --> first_name
'Last Name' , --> last_name
'Email_Address@oracle.com' , --> email_address
' 444444' , --> fax
' 77777777' , --> phone
36007 , --> vendor_id
5467 , --> vendor_site_id
SYSDATE , --> last_update_date
1451 , --> last_updated_by
SYSDATE , --> creation_date
1451 , --> created_by
'OU USA MA' , --> operating_unit_name
98 , --> org_id
'Oracle' --> department
);

COMMIT;

Once you loaded records into Interface table now we must run the Import
Program.

Payables Manager Responsibility->Other->Requests->Run->submit new
concurrent request

Supplier Sites Contacts Open Interface Import

Pass the value to Parameters which is assigned to the Program
1)Import Options → New
2)Import Options → 1000
3)Print Exceptions Only → No
4)Debug Switch → No
5)Trace Switch → No

Once you submit we can cross check error table AP_SUPPLIER_INT_REJECTIONS if
any records got rejected.

If there are no rejections Check the base tables AP_SUPPLIER_CONTACTS whether the records are populated .

Pre-Requisitions

Operating unit,Supplier type, Terms, Country code and currency code should already exist.
Payment method code, Pay group, Ship via, FOB, Freight terms, Vat rate , Pay date basis and
bill to ,Ship to locations should already exist.

==========================================================
CREATE OR REPLACE PROCEDURE Single_Insert_Supplier
IS
BEGIN
INSERT INTO apps.ap_suppliers_int
(
vendor_interface_id ,
last_update_date ,
last_updated_by ,
vendor_name ,
summary_flag ,
enabled_flag ,
creation_date ,
created_by ,
employee_id ,
ship_to_location_code ,
bill_to_location_code ,
vendor_type_lookup_code ,
one_time_flag ,
terms_id ,
num_1099 ,
payment_method_lookup_code ,
women_owned_flag ,
small_business_flag ,
vat_registration_num ,
create_debit_memo_flag ,
exclude_freight_from_discount,
hold_unmatched_invoices_flag ,
match_option ,
receipt_required_flag ,
inspection_required_flag ,
federal_reportable_flag ,
type_1099 ,
vendor_name_alt ,
invoice_currency_code ,
payment_currency_code ,
organization_type_lookup_code,
pay_group_lookup_code ,
pay_date_basis_lookup_code
)
VALUES
(
apps.ap_suppliers_int_s.NEXTVAL , --> vendor_interface_id
SYSDATE , --> last_update_date
1451 , --> last_updated_by
'Test Sup 001' , --> vendor_name
'N' , --> summary_flag
'Y' , --> enabled_flag
SYSDATE , --> creation_date
1451 , --> created_by
NULL , --> employee_id
'US_MA_DEVENS' , --> ship_to_location_code
'US_MA_DEVENS' , --> bill_to_location_code
'VENDOR' , --> vendor_type_lookup_code
'N' , --> one_time_flag
10004 , --> terms_id
'TaxPID 002' , --> num_1099
'CHECK' , --> payment_method_lookup_code
'Y' , --> women_owned_flag
'Y' , --> small_business_flag
'TaxREGID 002' , --> vat_registration_num
'Y' , --> create_debit_memo_flag
'Y' , --> exclude_freight_from_discount
'Y' , --> hold_unmatched_invoices_flag
'R' , --> match_option
'Y' , --> receipt_required_flag
'Y' , --> Inspection_required_flag
'Y' , --> federal_reportable_flag
'MISC1' , --> type_1099
'Single record insertion Testing Supplier' , --> vendor_name_alt
'USD' , --> invoice_currency_code
'USD' , --> payment_currency_code
'CORPORATION' , --> organization_type_lookup_code
'CA' , --> pay_group_lookup_code
'DISCOUNT' --> pay_date_basis_lookup_code
);
INSERT INTO apps.ap_supplier_sites_int
(
vendor_site_interface_id ,
last_update_date ,
last_updated_by ,
vendor_id ,→ Get the VENDOR ID from Header level.
vendor_site_code ,
creation_date ,
created_by ,
purchasing_site_flag ,
pay_site_flag ,
address_line1 ,
address_line2 ,
address_line3 ,
city ,
state ,
zip ,
country ,
phone ,
fax ,
ship_to_location_code ,
bill_to_location_code ,
payment_method_lookup_code ,
terms_id ,
create_debit_memo_flag ,
hold_unmatched_invoices_flag ,
org_id ,
email_address ,
match_option ,
exclude_freight_from_discount,
invoice_currency_code ,
payment_currency_code ,
country_of_origin_code ,
primary_pay_site_flag ,
freight_terms_lookup_code ,
fob_lookup_code ,
ship_via_lookup_code ,
customer_num ,
pay_group_lookup_code ,
pay_date_basis_lookup_code ,
vat_code ,
always_take_disc_flag
)
VALUES
(
apps.ap_supplier_sites_int_s.NEXTVAL , --> vendor_site_interface_id
SYSDATE , --> last_update_date
1451 , --> last_updated_by
36007 , --> vendor_id → Get the VENDOR ID from Headers level.
'Test MA Site_1' , --> vendor_site_code
SYSDATE , --> creation_date
1451 , --> created_by
'Y' , --> purchasing_site_flag
'Y' , --> pay_site_flag
'Street no - 01' , --> address_line1
'HMT Nagar' , --> address_line2
'Nacharam' , --> address_line3
'Hyderabad' , --> city
'Andra Pradesh' , --> state
'5000078' , --> zip
'IN' , --> country
'+91 4444 333333' , --> phone
'040 40404040' , --> fax
'US_MA_DEVENS' , --> ship_to_location_code
'US_MA_DEVENS' , --> bill_to_location_code
'CHECK' , --> payment_method_lookup_code
10006 , --> terms_id
'Y' , --> create_debit_memo_flag
'N' , --> hold_unmatched_invoices_flag
98 , --> org_id
apps123@apps.com , --> email_address
'R' , --> match_option
'Y' , --> exclude_freight_from_discount
'USD' , --> invoice_currency_code
'USD' , --> payment_currency_code
'IN' , --> country_of_origin_code
'Y' , --> primary_pay_site_flag
'TBD' , --> freight_terms_lookup_code
'FOB' , --> fob_lookup_code
'TBD' , --> ship_via_lookup_code
'Test Cust 001' , --> customer_num
'CA' , --> pay_group_lookup_code
'DISCOUNT' , --> pay_date_basis_lookup_code
'17% VAT' , --> vat_code
'Y' --> always_take_disc_flag
);
INSERT INTO ap.ap_sup_site_contact_int
(
vendor_contact_interface_id,
first_name ,
last_name ,
email_address ,
fax ,
phone ,
vendor_id ,→ Get the VENDOR ID from Headers and Sites level.
vendor_site_id ,→ Get the VENDOR SITE ID from Site level.
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
operating_unit_name ,
org_id ,
department
)
VALUES
(
ap.ap_sup_site_contact_int_s.NEXTVAL , --> vendor_contact_interface_id
'First Name' , --> first_name
'Last Name' , --> last_name
'Email_Address@.com' , --> email_address
' 444444' , --> fax
' 77777777' , --> phone
36007 , --> vendor_id → Get the VENDOR ID from Headers and Sites level.
5467 , --> vendor_site_id → Get the VENDOR SITE ID from Site level.
SYSDATE , --> last_update_date
1451 , --> last_updated_by
SYSDATE , --> creation_date
1451 , --> created_by
'OU USA MA' , --> operating_unit_name
98 , --> org_id
'Oracle' --> department
);
COMMIT;
END Single_Insert_Supplier;
==========================================================