Oracle Application's Blog: Supplier Conversion in oracle apps r12

Supplier Conversion in oracle apps r12

In this post , We are going to discuss about Supplier Conversion in oracle apps r12. Supplier conversion in Oracle apps , is the process to migrate the supplier Data from external system into the Oracle apps r12 application. In Supplier conversion , We do import the Suppliers created in external system in to the oracle application. Supplier Conversion is also the process to change the Supplier Data in the format of Oracle Apps supplier data so that we can upload the suppliers from External application in to the Oracle Application. In Oracle Apps r12 , for Supplier conversion , we have the supplier interface which helps to migrate/import from Supplier data from external application in to the oracle application. we need to convert the supplier data in the from of Supplier interface and then insert the data into the Supplier interfaces to create the supplier in oracle apps from external system. Here below , we will provide the complete PLSQL script using Supplier interface to import the supplier data in oracle apps. Please find below the detail about Supplier Conversion in oracle apps r12.

Supplier Conversion in oracle apps r12

Interface Tables for Supplier Conversion in oracle apps r12

1.ap_suppliers_int

2.ap_supplier_sites_int

PLSQL Script for Supplier Conversion in oracle apps r12

Here below , 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

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

CURSOR cur_supplier_detl

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)

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)

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

)

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)

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)

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)

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)

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)

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)

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)

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 Conversion in oracle apps r12

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

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;