add

About Me

My photo
Oracle Apps - Techno Functional consultant

Sunday, August 14

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;

==========================================================



No comments: