Part 1: Customer Interface In Oracle Apps (Validation Script)

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;