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;
No comments:
Post a Comment