Part 1 Supplier Interface In Oracle Apps (Validation Script)

In this post , I will share you the PLSQL code for Supplier Interface.

I am using xx_supp_stag_all staging table where I have uploaded Supplier data through SQL loader. I am sharing you two procedures.

In the First procedure , I will share you the validation script so that you can validate the Data from your Staging table like the values you are uploading for Supplier is available in Oracle apps or not.

In the Second Procedure I will share your the Execution script to upload validated date in Supplier Interface Table.

First Procedure (Validation Script)

 

 

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;

<<next_rec>>

COMMIT;

END LOOP supp_detl;

END xx_supplier_vailidate;