In this post , I will share you the PLSQL code for
Customer Interface.
I am using xx_customer_stag staging table
where I have uploaded Customer 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 Customer is available in Oracle apps
or not.
In the Second Procedure I will share your the
Execution script to upload validated date in Customer Interface Table.
First Procedure (Validation Script)
PROCEDURE xx_customer_vailidate
IS
----------------------Cursor for Customer
Detail--------------------------------
CURSOR cur_customer_detail
IS
SELECT /*+
USE_HASH_AGGREGATION INDEX_RS_ASC (xx_customer_stag
xx_customer_stag_3)*/ *
FROM xx_customer_stag
WHERE
NVL(VAILIDATE_FLAG_PROCESS,'NULL')='NULL'
AND NVL(FLAG_PROCESS,'E') <>'P';
----------------------Cursor For Vailid
Organization----------------------------
CURSOR cur_vailid_org (v_org_code VARCHAR2)
IS
SELECT COUNT (*)
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_org_code);
CURSOR cur_get_org_id (v_org_code VARCHAR2)
IS
SELECT organization_id
FROM hr_operating_units
WHERE UPPER (NAME) = UPPER (v_org_code);
---------------------Check if the customer_number
already exists----------------
CURSOR cur_vailid_cust_number (p_customer_number VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM hz_cust_accounts cust
WHERE cust.account_number = p_customer_number);
-------------------Cursor for
SITE_USE_CODE-------------------------------------
CURSOR cur_vailid_site_code (p_site_code VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND lookup_code = p_site_code);
------------------Cursor for Customer Category Code
-----------
CURSOR cur_vailid_category_code (p_site_code VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER_CATEGORY'
AND UPPER (lookup_code) = UPPER (p_site_code));
------------------Cursor For Vailid Country
Code--------------------------------
CURSOR cur_vailid_country_code (p_country_code VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (
SELECT NULL
FROM fnd_territories_tl
WHERE UPPER (territory_code) =
UPPER (p_country_code));
-----------------Cursor For Vailid
Party_ref_number-----------------------------
CURSOR cur_vailid_party_ref (p_party_ref VARCHAR2)
IS
SELECT 1 dummy
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM hz_parties
WHERE orig_system_reference = p_party_ref);
----------------Cursor For vailid
Order_type_id--------
CURSOR cur_order_type_id (v_order_type_name VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT transaction_type_id, NAME
FROM oe_transaction_types_tl
WHERE NAME = v_order_type_name);
---------------territory------------------
CURSOR cur_territories_id (v_territory_id NUMBER)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT *
FROM ra_territories
WHERE territory_id = v_territory_id);
--------------term-----------------
CURSOR cur_term_id (v_name VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (SELECT term_id
FROM ra_terms
WHERE NAME = v_name);
--------------CATEGORY
CURSOR cur_category_list (v_operating_unit NUMBER, v_description VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT tax_category_list
FROM jai_inv_itm_taxctg_hdrs a,
org_organization_definitions b
WHERE a.organization_id = b.organization_id
AND a.tax_category_list = v_description
AND DECODE (b.operating_unit,
NULL, NVL (v_operating_unit, 0),
b.operating_unit
) = v_operating_unit);
----------------------For PRICE_LIST------------------
CURSOR cur_price_list (v_currency_code VARCHAR2, price_list_name VARCHAR2)
IS
SELECT 1
FROM DUAL
WHERE NOT EXISTS (
SELECT pl.list_header_id, pl.NAME, pl.description
FROM qp_list_headers pl
WHERE SYSDATE BETWEEN NVL (pl.start_date_active, SYSDATE)
AND NVL (pl.end_date_active, SYSDATE)
AND currency_code = v_currency_code
AND pl.NAME = price_list_name);
-----------------
-----------------Cursor For Book Id
-------------------------
CURSOR cur_get_book_id (v1_operating_unit NUMBER)
IS
SELECT TO_NUMBER (o2.org_information3) book_id
FROM hr_organization_information o1,
hr_organization_information o2,
gl_ledgers_public_v gl
WHERE o1.organization_id = o2.organization_id
AND o1.organization_id = v1_operating_unit
AND o1.org_information_context = 'CLASS'
AND o2.org_information_context = 'Operating
Unit Information'
AND o1.org_information1 = 'OPERATING_UNIT'
AND o1.org_information2 = 'Y'
AND o2.org_information3 = gl.ledger_id;
org_null VARCHAR2 (2000);
org_count_num NUMBER;
v_cust_null VARCHAR2 (2000);
v_site_code_null VARCHAR2 (2000);
v_country_null VARCHAR2 (2000);
v_party_ref_null VARCHAR2 (2000);
error_msg VARCHAR2 (2000);
v_sqlerrm VARCHAR2 (2000);
v_operating_org_id NUMBER;
v_book_id NUMBER;
auto_flag_error VARCHAR2 (2000);
auto_number_flag VARCHAR2 (20);
v_category_code_null VARCHAR2 (2000);
v_excise_price_list_null VARCHAR2 (2000);
v_territory_null VARCHAR2 (2000);
v_order_type_null VARCHAR2 (2000);
v_payment_terms_null VARCHAR2 (2000);
v_tax_category_list_null VARCHAR2 (2000);
v_payment_terms_id NUMBER;
BEGIN
FOR cur_det IN cur_customer_detail
LOOP
BEGIN
-----------------------------Refresh
Vairiable-----------
BEGIN
org_null := NULL;
org_count_num := NULL;
v_cust_null := NULL;
v_site_code_null := NULL;
v_country_null := NULL;
v_party_ref_null := NULL;
error_msg := NULL;
v_sqlerrm := NULL;
v_operating_org_id := NULL;
v_book_id := NULL;
auto_flag_error := NULL;
auto_number_flag := NULL;
v_category_code_null := NULL;
v_excise_price_list_null := NULL;
v_territory_null := NULL;
v_order_type_null := NULL;
v_payment_terms_null := NULL;
v_tax_category_list_null := NULL;
v_payment_terms_id := NULL;
END;
-------------------For Vailid Org Code-----------
BEGIN
IF cur_det.org_code IS NOT NULL
THEN
OPEN cur_vailid_org (cur_det.org_code);
FETCH cur_vailid_org
INTO org_count_num;
IF cur_vailid_org%NOTFOUND
THEN
org_count_num := 0;
CLOSE cur_vailid_org;
END IF;
CLOSE cur_vailid_org;
IF org_count_num = 0
THEN
org_null :=
cur_det.org_code || ' is
invalid Operating Unit ';
ELSE
OPEN cur_get_org_id (cur_det.org_code);
FETCH cur_get_org_id
INTO v_operating_org_id;
IF cur_get_org_id%NOTFOUND
THEN
v_operating_org_id := NULL;
CLOSE cur_get_org_id;
END IF;
CLOSE cur_get_org_id;
END IF;
ELSE
org_null := 'Operating Unit can not be null';
END IF;
END;
-------------------For Auto Numbering Flag
----------------
IF v_operating_org_id IS NOT NULL
THEN
OPEN cur_get_book_id (v_operating_org_id);
FETCH cur_get_book_id
INTO v_book_id;
IF cur_get_book_id%NOTFOUND
THEN
v_book_id := NULL;
CLOSE cur_get_book_id;
END IF;
CLOSE cur_get_book_id;
IF v_book_id IS NOT NULL
THEN
BEGIN
SELECT generate_customer_number
INTO auto_number_flag
FROM ar_system_parameters_all
WHERE set_of_books_id = v_book_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
auto_number_flag := NULL;
WHEN OTHERS
THEN
auto_number_flag := NULL;
END;
IF auto_number_flag = 'Y'
AND cur_det.customer_number IS NOT NULL
THEN
auto_flag_error :=
'CUSTOMER_NUMBER must be null when auto-numbering is
set to ¿Yes¿';
ELSIF auto_number_flag = 'N'
AND ( cur_det.customer_number IS NULL
OR cur_det.LOCATION IS NULL
)
THEN
auto_flag_error :=
'CUSTOMER_NUMBER and Location is mandatory when
auto-numbering is set to ¿No¿';
END IF;
END IF;
END IF;
-------------------For Vailid Customer
Number----
IF cur_det.customer_number IS NOT NULL AND auto_number_flag = 'N'
THEN
FOR cur_num IN cur_vailid_cust_number (cur_det.customer_number)
LOOP
v_cust_null :=
cur_det.customer_number || ' is
already defined.......';
END LOOP;
END IF;
------------------For Vailid site code-----------
IF cur_det.site_use_code IS NOT NULL
THEN
FOR cur_site IN cur_vailid_site_code (cur_det.site_use_code)
LOOP
v_site_code_null :=
cur_det.site_use_code || ' is not
defined.......';
END LOOP;
ELSE
v_site_code_null :=
cur_det.site_use_code || ' can not
be null .......';
END IF;
------------------For Vailid site code-----------
IF cur_det.customer_category_code IS NOT NULL
THEN
FOR cur_category IN
cur_vailid_category_code (cur_det.customer_category_code)
LOOP
v_category_code_null :=
cur_det.customer_category_code
|| ' is not defined.......';
END LOOP;
END IF;
------------------For Vailid Country code--------
IF cur_det.country IS NOT NULL
THEN
FOR cur_country IN cur_vailid_country_code (cur_det.country)
LOOP
v_country_null :=
cur_det.country || ' country
code is invailid.......';
END LOOP;
END IF;
--------------------For Vailid Party-------------
IF cur_det.party_reference IS NOT NULL
THEN
FOR cur_party IN cur_vailid_party_ref (cur_det.party_reference)
LOOP
v_party_ref_null :=
cur_det.party_reference || ' is
invailid........';
END LOOP;
END IF;
IF cur_det.excise_price_list IS NOT NULL
THEN
FOR cur_row IN cur_price_list (cur_det.currency_code,
cur_det.excise_price_list
)
LOOP
v_excise_price_list_null :=
cur_det.excise_price_list || ' is invalid.......';
END LOOP;
END IF;
IF cur_det.territory IS NOT NULL
THEN
FOR cur_row IN cur_territories_id (cur_det.territory)
LOOP
v_territory_null :=
cur_det.territory || ' is
invalid.......';
END LOOP;
END IF;
IF cur_det.order_type IS NOT NULL
THEN
FOR cur_row IN cur_order_type_id (cur_det.order_type)
LOOP
v_order_type_null :=
cur_det.order_type || ' is
invalid.......';
END LOOP;
END IF;
IF cur_det.payment_terms IS NOT NULL
THEN
FOR cur_row IN cur_term_id (cur_det.payment_terms)
LOOP
v_payment_terms_null :=
cur_det.payment_terms || ' is
invalid.......';
END LOOP;
END IF;
IF cur_det.tax_category_list IS NOT NULL
THEN
FOR cur_row IN cur_category_list (v_operating_org_id,
cur_det.tax_category_list
)
LOOP
v_tax_category_list_null :=
cur_det.tax_category_list || ' is invalid.......';
END LOOP;
END IF;
IF org_null IS NOT NULL
OR v_cust_null IS NOT NULL
OR v_site_code_null IS NOT NULL
OR v_country_null IS NOT NULL
OR v_party_ref_null IS NOT NULL
OR auto_flag_error IS NOT NULL
OR v_category_code_null IS NOT NULL
OR v_excise_price_list_null IS NOT NULL
OR v_territory_null IS NOT NULL
OR v_order_type_null IS NOT NULL
OR v_payment_terms_null IS NOT NULL
OR v_tax_category_list_null IS NOT NULL
THEN
error_msg :=org_null || ' ;'||
v_cust_null || ' ;'
|| v_site_code_null || ' ;'||
v_country_null || ' ;'
|| v_party_ref_null
|| auto_flag_error
|| v_category_code_null
|| v_excise_price_list_null
|| v_territory_null
|| v_order_type_null
|| v_payment_terms_null
|| v_tax_category_list_null;
UPDATE xx_customer_stag
SET vailidate_flag_process = 'E',
vailidate_error_message = error_msg
WHERE entry_id = cur_det.entry_id;
COMMIT;
ELSE
UPDATE xx_customer_stag
SET vailidate_flag_process = 'P'
WHERE entry_id = cur_det.entry_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_sqlerrm := SQLERRM;
UPDATE xx_customer_stag
SET vailidate_flag_process = 'E',
vailidate_error_message = v_sqlerrm
WHERE entry_id = cur_det.entry_id;
COMMIT;
END;
END LOOP;
END xx_customer_vailidate;
No comments:
Post a Comment