Supplier Interface in Oracle Apps r12

Supplier Interface in Oracle Apps r12

Hi friends, we are going to discuss about Supplier Interface in Oracle Apps r12. We will share the complete supplier interface which helps to upload the supplier informations in oracle apps r12 from external system or source. We will try to share each and every component of the supplier interface which will help to understand the supplier interface thoroughly. In oracle apps r12 , we have the interface tables and the standard API's about suppliers. These interface and API's helps to upload and update the supplier details in oracle apps. Supplier interface is one of the very important interface in oracle apps. In this post , We will explain the supplier interface from scratch. We will share the Supplier Interface tables too and the Supplier Interface script also. Please find below the complete detail about Supplier Interface in Oracle Apps r12.

Supplier Interface in Oracle Apps r12

Types of Supplier Interfaces scripts 

Here below , we will share these below types of supplier scripts which help you to upload and update the supplier details in oracle apps.

1. Supplier Upload Interface

2.Supplier Site Address Update API Interface.

3.Supplier Site Tax Information's Update API Interface.

Script 1: Supplier Upload Interface

Supplier Interface Tables in oracle apps r12

1.ap_suppliers_int

2.ap_supplier_sites_int

Detail Script For Supplier Interface in Oracle Apps r12

In this below script , we have created two procedures for Supplier Conversion in oracle apps r12. 

In First Procedure (xx_supplier_vailidate), We will first Validate the Supplier Data in the staging table which will be inserted into Supplier Interface tables. 

In Second Procedure(xx_supplier_detl) , the validated data from staging table will be inserted into the Supplier Interface Tables.

PROCEDURE xx_supplier_vailidate

IS

-----Main Cursor ---------------

CURSOR cur_supplier_detl

IS

SELECT

FROM xx_supp_stag_all

WHERE NVL(VAILIDATE_FLAG_PROCESS,'NULL')='NULL'

AND NVL(FLAG_PROCESS,'E') <>'P';

-------Cursor for Operating Unit------------

CURSOR cur_operating_unit (v_org_code VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT NULL

FROM hr_operating_units

WHERE UPPER (NAME) = UPPER (v_org_code));

-------Cursor for Vailid Vendor-------------

CURSOR cur_vailid_vendor (v_vendor_name VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE EXISTS (

SELECT vendor_id

FROM po_vendors

WHERE TRIM (UPPER (vendor_name)) =

TRIM (UPPER (v_vendor_name)));

------Cursor for Vendor Site ------------

CURSOR cur_vailid_site (

v_vendor_name VARCHAR2,

v_site_name VARCHAR2,

v_org_id NUMBER

)

IS

SELECT 1

FROM DUAL

WHERE EXISTS (

SELECT vendor_site_code

FROM po_vendor_sites_all a, po_vendors b

WHERE org_id = v_org_id

AND UPPER (vendor_site_code) = UPPER (v_site_name)

AND a.vendor_id = b.vendor_id

AND UPPER (b.vendor_name) = UPPER (v_vendor_name));

-------------CURSOR FOR VAILID COUNTRY ----------

CURSOR cur_vailid_country (v_country_name VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (

SELECT territory_code

FROM fnd_territories_tl

WHERE UPPER (territory_short_name) =

UPPER (v_country_name));

-------------CURSOR FOR VAILID VENDOR TYPE ----------

CURSOR cur_vailid_vendor_type (v_type VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (

SELECT lookup_code

FROM po_lookup_codes

WHERE lookup_type(+) = 'VENDOR TYPE'

AND UPPER (lookup_code) = UPPER (v_type));

------------Cursor for vailid payment term------------

CURSOR cur_vailid_payment_term (v_payment VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT term_id

FROM ap_terms

WHERE UPPER (NAME) = UPPER (v_payment));

-----------Cursor for vailid Location_code --------

CURSOR cur_vailid_location (v_location VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT location_id

FROM hr_locations

WHERE location_code = v_location);

----------Cursor for vailid Supplier Currency ------

CURSOR cur_currency_code (v_currency_code VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (

SELECT currency_code

FROM fnd_currencies

WHERE UPPER (currency_code) =

UPPER (v_currency_code));

-----------Cursor for vailid GL combination id --------

CURSOR cur_combination_id (v_combination_id NUMBER)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (SELECT code_combination_id

FROM gl_code_combinations

WHERE code_combination_id = v_combination_id);

-----------Cursor for vailid TERMS DATE BASIS --------

CURSOR cur_vailid_look_code (v_look_code VARCHAR2)

IS

SELECT 1

FROM DUAL

WHERE NOT EXISTS (

SELECT lookup_code

FROM fnd_lookup_values

WHERE fnd_lookup_values.lookup_type = 'TERMS DATE BASIS'

AND UPPER (lookup_code) = UPPER (v_look_code));

v_operating_unit_null VARCHAR2 (200);

v_vendor_null VARCHAR2 (200);

v_country_null VARCHAR2 (200);

v_vendor_type_null VARCHAR2 (200);

payment_term_null VARCHAR2 (200);

currency_null VARCHAR2 (200);

gl_liability_ac_null VARCHAR2 (200);

gl_advance_supplier_ac_null VARCHAR2 (200);

term_date_basis_null VARCHAR2 (200);

location_null VARCHAR2 (200);

v_error_message VARCHAR2 (32767);

BEGIN

<<supp_detl>>

FOR i IN cur_supplier_detl

LOOP

-----------------Refresh vairiable--------------

v_operating_unit_null := NULL;

v_vendor_null := NULL;

v_country_null := NULL;

v_vendor_type_null := NULL;

payment_term_null := NULL;

currency_null := NULL;

gl_liability_ac_null := NULL;

gl_advance_supplier_ac_null := NULL;

term_date_basis_null := NULL;

location_null := NULL;

v_error_message := NULL;

----------------For Operating Unit---------------

IF i.org_code IS NOT NULL

THEN

FOR cur_j IN cur_operating_unit (i.org_code)

LOOP

v_operating_unit_null := 'invailid Operating Unit.... ';

END LOOP;

ELSE

v_operating_unit_null := 'Operating Unit Can not Be null.... ';

END IF;

----------------CUR_VAILID_VENDOR --------

IF i.supplier_name IS NOT NULL

THEN

FOR cur_j IN cur_vailid_vendor (i.supplier_name)

LOOP

v_vendor_null := 'Invailid Supplier Name.... ';

END LOOP;

ELSE

v_vendor_null := 'Supplier Name can not be null.. ';

END IF;

-----------------Country-----------------

IF i.country IS NOT NULL

THEN

FOR cur_j IN cur_vailid_country (i.country)

LOOP

v_country_null := 'Invailid country.... ';

END LOOP;

END IF;

-----------------vendor type-----------------

IF i.supplier_classification IS NOT NULL

THEN

FOR cur_j IN cur_vailid_vendor_type (i.supplier_classification)

LOOP

v_vendor_type_null := 'Invailid vendor type.... ';

END LOOP;

END IF;

-------------------vailid_payment_term --------------

IF i.payment_term IS NOT NULL

THEN

FOR cur_j IN cur_vailid_payment_term (i.payment_term)

LOOP

payment_term_null := 'Invailid PAYMENT type.... ';

END LOOP;

END IF;

-------------vailid Location_code---------------------

IF i.location_code IS NOT NULL

THEN

FOR cur_j IN cur_vailid_location (i.location_code)

LOOP

location_null := 'Invailid location code.... ';

END LOOP;

END IF;

--------------currency code--------------------

IF i.invoice_ccy_code IS NOT NULL

THEN

FOR cur_j IN cur_currency_code (i.invoice_ccy_code)

LOOP

currency_null := 'Invailid currency code.... ';

END LOOP;

END IF;

---------------vailid GL combination id ---------

IF i.gl_liability_ac IS NOT NULL

THEN

FOR cur_j IN cur_combination_id (i.gl_liability_ac)

LOOP

gl_liability_ac_null := 'Invailid combination id.... ';

END LOOP;

END IF;

IF i.gl_advance_supplier_ac IS NOT NULL

THEN

FOR cur_j IN cur_combination_id (i.gl_advance_supplier_ac)

LOOP

gl_advance_supplier_ac_null := 'Invailid combination id.... ';

END LOOP;

END IF;

-------------------TERM_DATE_BASIS-----------

IF i.term_date_basis IS NOT NULL

THEN

FOR cur_j IN cur_vailid_look_code (i.term_date_basis)

LOOP

term_date_basis_null := 'Invailid TERM_DATE_BASIS.... ';

END LOOP;

END IF;

IF v_operating_unit_null IS NOT NULL

OR v_vendor_null IS NOT NULL

OR v_country_null IS NOT NULL

OR v_vendor_type_null IS NOT NULL

OR payment_term_null IS NOT NULL

OR currency_null IS NOT NULL

OR gl_liability_ac_null IS NOT NULL

OR gl_advance_supplier_ac_null IS NOT NULL

OR term_date_basis_null IS NOT NULL

OR location_null IS NOT NULL

THEN

v_error_message :=

v_operating_unit_null

|| ' ;'

|| v_vendor_null

|| ' ;'

|| v_country_null

|| ' ;'

|| v_vendor_type_null

|| ' ;'

|| payment_term_null

|| ' ;'

  

|| currency_null

|| ' ;'

|| gl_liability_ac_null

|| ' ;'

|| gl_advance_supplier_ac_null

|| ' ;'

|| term_date_basis_null

|| ' ;'

|| location_null;

UPDATE xx_supp_stag_all

SET vailidate_flag_process = 'E',

vailidate_error_message = v_error_message

WHERE entry_id = i.entry_id;

GOTO next_rec;

ELSE

UPDATE xx_supp_stag_all

SET vailidate_flag_process = 'P'

WHERE entry_id = i.entry_id;

GOTO next_rec;

END IF;

COMMIT;

END LOOP supp_detl;

END xx_supplier_vailidate;

Supplier Interface in Oracle Apps r12

------------------------------------------Second Procedure ------------------------------------------------

Supplier Conversion in oracle apps r12

Supplier Conversion in oracle apps r12

PROCEDURE xx_supplier_detl

IS

CURSOR cur_supplier

IS

SELECT org_code, supplier_name, invoice_ccy_code,

supplier_classification, location_code

FROM xx_supp_stag_all

WHERE NVL (vailidate_flag_process, 'E') = 'P'

AND NVL(FLAG_PROCESS,'NULL') ='NULL'

GROUP BY org_code, supplier_name, invoice_ccy_code,

supplier_classification, location_code;

CURSOR cur_supplier_site (v_org_code VARCHAR2, v_supp_name VARCHAR2)

IS

SELECT * FROM xx_supp_stag_all

WHERE NVL (vailidate_flag_process, 'E') = 'P'

AND NVL (flag_process ,'NULL') = 'NULL'

AND SUPPLIER_NAME = V_SUPP_NAME

AND ORG_CODE = V_ORG_CODE ;

-------------Operating Unit id ------

CURSOR operating_org_id (v_operating_unit VARCHAR2)

IS

SELECT organization_id

FROM hr_operating_units

WHERE UPPER (NAME) = UPPER (v_operating_unit);

------------Location Id---------------

CURSOR cur_loaction_id (v_location_code VARCHAR2)

IS

SELECT location_id

FROM hr_locations

WHERE location_code = v_location_code;

----------------Combination Code------

CURSOR cur_combination_id (v_combination_code VARCHAR2)

IS

SELECT code_combination_id

FROM gl_code_combinations

WHERE code_combination_id = TO_NUMBER (v_combination_code);

--WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=V_COMBINATION_CODE;

--------------Payment Term Id ---------

CURSOR cur_term_id (v_term_code VARCHAR2)

IS

SELECT term_id

FROM ap_terms

WHERE UPPER (NAME) = UPPER (TRIM (v_term_code));

vendor_id_num NUMBER;

insert_success_flag VARCHAR2 (2);

v_sql_errm VARCHAR2 (2000);

site_vendor_id_num NUMBER;

p_organization_id NUMBER;

l_pay_code_combination_id NUMBER (10);

l_prepay_code_combination_id NUMBER (10);

l_location_id NUMBER (10);

l_term_id NUMBER (10);

v_country VARCHAR2 (200);

v_terms_date_basis VARCHAR2 (200);

BEGIN

<<head_supp_info>>

FOR i IN cur_supplier

LOOP

----------------Refresh vairiable ------------

vendor_id_num := NULL;

insert_success_flag := NULL;

v_sql_errm := NULL;

site_vendor_id_num := NULL;

p_organization_id := NULL;

l_pay_code_combination_id := NULL;

l_prepay_code_combination_id := NULL;

l_location_id := NULL;

l_term_id := NULL;

v_country := NULL;

v_terms_date_basis := NULL;

vendor_id_num := ap_suppliers_int_s.NEXTVAL;

BEGIN

SAVEPOINT header_insert;

-----------------Location Id------------

IF i.location_code IS NOT NULL

THEN

OPEN cur_loaction_id (i.location_code);

FETCH cur_loaction_id

INTO l_location_id;

IF cur_loaction_id%NOTFOUND

THEN

l_location_id := NULL;

CLOSE cur_loaction_id;

END IF;

CLOSE cur_loaction_id;

END IF;

INSERT INTO ap_suppliers_int

(vendor_interface_id, vendor_name, vendor_name_alt,

enabled_flag, attribute10,

vendor_type_lookup_code,

invoice_currency_code,

payment_currency_code, create_debit_memo_flag,

payment_method_lookup_code, bill_to_location_id,

ship_to_location_id

  )

VALUES (vendor_id_num, i.supplier_name, i.supplier_name,

'Y', 'TEST DATA LOADING',

UPPER (i.supplier_classification),

NVL (i.invoice_ccy_code, 'INR'),

NVL (i.invoice_ccy_code, 'INR'), 'Y',

'CHECK', l_location_id,

l_location_id

  );

insert_success_flag := 'Y';

EXCEPTION

WHEN OTHERS

THEN

insert_success_flag := 'N';

v_sql_errm := SQLERRM;

UPDATE xx_supp_stag_all

SET flag_process = 'E',

error_message = v_sql_errm

WHERE supplier_name = i.supplier_name

AND org_code = i.org_code

AND vailidate_flag_process = 'P';

GOTO next_supp;

END;

IF insert_success_flag = 'Y'

THEN

<<site_supp_info>>

FOR supp_site IN cur_supplier_site (i.org_code, i.supplier_name)

LOOP

---------------Operating Org Id ---------------

BEGIN

OPEN operating_org_id (i.org_code);

FETCH operating_org_id

INTO p_organization_id;

IF operating_org_id%NOTFOUND

THEN

p_organization_id := NULL;

CLOSE operating_org_id;

END IF;

CLOSE operating_org_id;

BEGIN

SELECT territory_code

INTO v_country

FROM fnd_territories

WHERE UPPER (nls_territory) = UPPER (supp_site.country);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_country := NULL;

WHEN OTHERS

THEN

v_country := NULL;

END;

------------Combination Code-----------

IF supp_site.gl_liability_ac IS NOT NULL

THEN

OPEN cur_combination_id (supp_site.gl_liability_ac);

FETCH cur_combination_id

INTO l_pay_code_combination_id;

IF cur_combination_id%NOTFOUND

THEN

l_pay_code_combination_id := NULL;

CLOSE cur_combination_id;

END IF;

CLOSE cur_combination_id;

END IF;

-------------

    IF supp_site.gl_advance_supplier_ac IS NOT NULL

THEN

OPEN cur_combination_id

(supp_site.gl_advance_supplier_ac);

FETCH cur_combination_id

INTO l_prepay_code_combination_id;

IF cur_combination_id%NOTFOUND

THEN

l_pay_code_combination_id := NULL;

CLOSE cur_combination_id;

END IF;

CLOSE cur_combination_id;

END IF;

---------------Term_id -----------------

IF supp_site.payment_term IS NOT NULL

THEN

OPEN cur_term_id (supp_site.payment_term);

FETCH cur_term_id

INTO l_term_id;

IF cur_term_id%NOTFOUND

THEN

l_term_id := NULL;

CLOSE cur_term_id;

END IF;

CLOSE cur_term_id;

END IF;

---------------Term data basis-----------

BEGIN

SELECT lookup_code

INTO v_terms_date_basis

FROM fnd_lookup_values

WHERE fnd_lookup_values.lookup_type = 'TERMS DATE BASIS'

AND UPPER (lookup_code) =

UPPER (supp_site.term_date_basis);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_terms_date_basis := NULL;

WHEN OTHERS

THEN

v_terms_date_basis := NULL;

END;

INSERT INTO ap_supplier_sites_int

(vendor_interface_id, vendor_site_interface_id,

vendor_site_code,

vendor_site_code_alt,

payment_method_lookup_code, terms_id,

address_line1,

address_line2,

address_line3, org_id,

city, province,

area_code, state,

country,

phone,

fax_area_code,

fax,

zip,

email_address, bill_to_location_id,

ship_to_location_id, match_option,

terms_date_basis, purchasing_site_flag,

pay_site_flag, create_debit_memo_flag,

accts_pay_code_combination_id,

prepay_code_combination_id,

attribute10,

attribute9,

attribute6, attribute7

)

VALUES (vendor_id_num, ap_suppliers_int_s.NEXTVAL,

NVL (supp_site.supplier_site_name, 'OFFICE'),

NVL (supp_site.supplier_site_name, 'OFFICE'),

'CHECK', l_term_id,

NVL (supp_site.address_line1, 'Not Specified'),

supp_site.address_line2,

supp_site.address_line3, p_organization_id,

supp_site.city, supp_site.city,

NVL (supp_site.std_code, 00), supp_site.state,

v_country,

SUBSTR (supp_site.phone_number, 1, 15),

NVL (supp_site.std_code, 00),

SUBSTR (supp_site.fax_number, 1, 15),

supp_site.postal_code,

supp_site.email_address, l_location_id,

l_location_id, 'R',

-- upper(NVL(V_TERMS_DATE_BASIS,'Invoice')),

NVL (v_terms_date_basis, 'Invoice'), 'Y',

'Y', 'Y',

l_pay_code_combination_id,

l_prepay_code_combination_id,

'TEST DATA LOADING',,

supp_site.term_date_basis,

supp_site.phone_number, supp_site.fax_number

);

UPDATE xx_supp_stag_all

SET flag_process = 'P'

WHERE entry_id = supp_site.entry_id;

EXCEPTION

WHEN OTHERS

THEN

ROLLBACK TO SAVEPOINT header_insert;

insert_success_flag := 'N';

v_sql_errm := SQLERRM;

UPDATE xx_supp_stag_all

SET flag_process = 'E',

error_message = v_sql_errm

WHERE supplier_name = i.supplier_name

AND org_code = i.org_code

AND vailidate_flag_process = 'P';

GOTO next_supp;

END;

END LOOP site_supp_info;

END IF;

<<next_supp>>

COMMIT;

END LOOP head_supp_info;

END xx_supplier_detl;

Supplier Interface in Oracle Apps r12

Script 2: Supplier Site Address Update API Interface

Here below is the script to update the Supplier Site Addresses in Oracle Apps.

Declare 

v_api_version   NUMBER;

v_init_msg_list VARCHAR2(200);

v_commit VARCHAR2(200);

v_validation_level NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

v_vendor  NUMBER;

x_msg_data VARCHAR2(200);

l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

v_vendor_site_id NUMBER;

v_calling_prog VARCHAR2(200);

v_error_flag  VARCHAR2(200):='N';

v_error_message  VARCHAR2(4000):='N';

cursor c is

select VENDOR_NAME,address_line1,address_line2,address_line2,city,country from XX_VENDOR_SITES_STG;

CURSOR C1(P_VENDOR VARCHAR) IS SELECT VENDOR_SITE_ID

FROM ap_supplier_sites_all assa

WHERE assa.vendor_id =P_VENDOR;

BEGIN

for i in c loop

BEGIN

v_error_flag:='N';

v_error_message:=null;

select vendor_id into v_vendor from ap_suppliers

where upper(vendor_name)=upper(i.VENDOR_NAME);

EXCEPTION

WHEN OTHERS THEN

v_error_flag:='Y';

v_error_message:='Unable to find the supplier site information for site id';

DBMS_OUTPUT.put_line('Unable to find the supplier site information for site id' ||v_vendor_site_id);

END;

IF v_error_flag='N' THEN

FOR J IN C1(v_vendor) LOOP

fnd_global.apps_initialize(610970,50111,200);

mo_global.init('SQLAP');

fnd_client_info.set_org_context(4556);

v_api_version := 1.0;

v_init_msg_list := fnd_api.g_true;

v_commit := fnd_api.g_true;

v_validation_level := fnd_api.g_valid_level_full;

v_vendor_site_id := J.VENDOR_SITE_ID; -- to be end dated

v_calling_prog := 'Supplier Address Update';

l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;

l_vendor_site_rec.last_update_date := SYSDATE;

l_vendor_site_rec.last_updated_by := 6147023;

l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;

l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;

l_vendor_site_rec.address_line1 :=I.address_line1;

l_vendor_site_rec.address_line2 :=I.address_line2;

l_vendor_site_rec.address_line3 :=I.address_line3;

l_vendor_site_rec.city :=I.city;

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version => v_api_version,

p_init_msg_list => v_init_msg_list,

p_commit => v_commit,

p_validation_level => v_validation_level,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_vendor_site_rec => l_vendor_site_rec,

p_vendor_site_id => v_vendor_site_id,

p_calling_prog => v_calling_prog);

DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);

END LOOP;

UPDATE XX_VENDOR_SITES_STG

SET STATUS='SUCCESS'

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

IF v_error_flag='Y' THEN

UPDATE XX_VENDOR_SITES_STG

SET STATUS='ERROR',

ATTRIBUTE1=v_error_message

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

COMMIT;

END LOOP;

END;

Supplier Interface in Oracle Apps r12

Script 3: Supplier Site Tax Information's Update API Interface

Here below is the Supplier Site Tax Information's update API to assign and update the tax informations in oracle apps.

API to Update Supplier Sites and assign Tax code in Oracle apps

DECLARE

v_api_version   NUMBER;

v_init_msg_list VARCHAR2(200);

v_commit VARCHAR2(200);

v_validation_level NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

v_vendor  NUMBER;

x_msg_data VARCHAR2(200);

l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

v_vendor_site_id NUMBER;

v_calling_prog VARCHAR2(200);

v_error_flag  VARCHAR2(200):='N';

v_error_message  VARCHAR2(4000):='N';

cursor c is

select VENDOR_NAME,TAX_REGISTRATION_NO,TAX_CODE from XX_VENDOR_SITES_STG

GROUP BY VENDOR_NAME,REMITTANCE_EMAIL,TAX_CODE;

CURSOR C1(P_VENDOR VARCHAR) IS SELECT VENDOR_SITE_ID

FROM ap_supplier_sites_all assa

WHERE assa.vendor_id =P_VENDOR;

BEGIN

for i in c loop

BEGIN

v_error_flag:='N';

v_error_message:=null;

select vendor_id into v_vendor from ap_suppliers

 where upper(vendor_name)=upper(i.VENDOR_NAME);

EXCEPTION

WHEN OTHERS THEN

v_error_flag:='Y';

v_error_message:='Unable to find the supplier site information for site id';

DBMS_OUTPUT.put_line('Unable to find the supplier site information for site id' ||v_vendor_site_id);

END;

IF v_error_flag='N' THEN

FOR J IN C1(v_vendor) LOOP

fnd_global.apps_initialize(610970,50111,200);

mo_global.init('SQLAP');

fnd_client_info.set_org_context(4556);

v_api_version := 1.0;

v_init_msg_list := fnd_api.g_true;

v_commit := fnd_api.g_true;

v_validation_level := fnd_api.g_valid_level_full;

v_vendor_site_id := J.VENDOR_SITE_ID; -- to be end dated

v_calling_prog := 'Supplier Tax code Update';

l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;

l_vendor_site_rec.last_update_date := SYSDATE;

l_vendor_site_rec.last_updated_by := 6147023;

l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;

l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;

l_vendor_site_rec.vat_code :=I.TAX_CODE;

l_vendor_site_rec.AUTO_TAX_CALC_FLAG:='Y';

l_vendor_site_rec.VAT_REGISTRATION_NUM:=I.TAX_REGISTRATION_NO;

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version => v_api_version,

p_init_msg_list => v_init_msg_list,

p_commit => v_commit,

p_validation_level => v_validation_level,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_vendor_site_rec => l_vendor_site_rec,

p_vendor_site_id => v_vendor_site_id,

p_calling_prog => v_calling_prog);

DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);

END LOOP;

UPDATE XX_VENDOR_SITES_STG

SET STATUS='SUCCESS'

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

IF v_error_flag='Y' THEN

UPDATE XX_VENDOR_SITES_STG

SET STATUS='ERROR',

ATTRIBUTE1=v_error_message

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

COMMIT;

END LOOP;

END;