Part 2: Customer Interface In Oracle Apps ( Script to Load data In Customer Interface Tables)

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.


Second Procedure (Main Execution Script to Load Data in Oracle Apps)

PROCEDURE xx_cust_interface

IS

------------------Cursor For Customer Detail

v_org_id NUMBER;

v_erro_msg VARCHAR2 (32765);

v_ref_number VARCHAR2 (2000);

f_ref_number VARCHAR2 (2000);

o_ref_number VARCHAR2 (2000);

r_ref_number VARCHAR2 (2000);

m_ref_number VARCHAR2 (2000);

e_ref_number VARCHAR2 (2000);

contact_ref VARCHAR2 (2000);

v_ref_number_ship VARCHAR2 (2000);

v_ship_count NUMBER;

v_primary_site_use_flag VARCHAR2 (20);

v_payment_terms_id NUMBER;

CURSOR cur_customer_detail

IS

SELECT

FROM xx_customer_stag

WHERE

NVL (vailidate_flag_process, 'E') = 'P'

AND NVL(flag_process ,'NULL') = 'NULL'

AND UPPER (site_use_code) = 'BILL_TO'

GROUP BY org_code,

site_use_code,

CUSTOMER_NUMBER,

CUSTOMER_NAME;

CURSOR cur_customer_detail_ship_to (

v_customer_name VARCHAR2,

v_customer_number VARCHAR2,

v_org_code VARCHAR2

)

IS

SELECT /*+ USE_HASH_AGGREGATION INDEX_RS_ASC (xx_customer_stag

xx_customer_stag_5)*/ *

FROM xx_customer_stag

WHERE

NVL (vailidate_flag_process, 'E') = 'P'

AND NVL (flag_process, 'NULL') = 'NULL'

AND UPPER (site_use_code) = 'SHIP_TO'

AND customer_name = v_customer_name

AND NVL (customer_number, 'E') = NVL (v_customer_number, 'E')

AND org_code = v_org_code

GROUP BY org_code,

site_use_code,

customer_number,

customer_name,

address1,

address2,

ADDRESS3,

ADDRESS4;

CURSOR cur_get_org_id (v_org_code VARCHAR2)

IS

SELECT organization_id

FROM hr_operating_units

WHERE UPPER (NAME) = UPPER (v_org_code);

CURSOR cur_term_id (v_name VARCHAR2)

IS

SELECT term_id

FROM ra_terms

WHERE NAME = v_name;

BEGIN

FOR cur_det IN cur_customer_detail

LOOP

v_ref_number := 'AA' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

f_ref_number := 'FF' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

o_ref_number := 'OO' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

r_ref_number := 'RR' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

m_ref_number := 'MM' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

e_ref_number := 'EE' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

IF cur_det.contact_last_name IS NULL

THEN

contact_ref := NULL;

ELSE

contact_ref := 'CC' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

END IF;

BEGIN

OPEN cur_get_org_id (cur_det.org_code);

FETCH cur_get_org_id

INTO v_org_id;

IF cur_get_org_id%NOTFOUND

THEN

v_org_id := NULL;

CLOSE cur_get_org_id;

END IF;

CLOSE cur_get_org_id;

INSERT INTO ra_customers_interface_all

(org_id, orig_system_customer_ref, site_use_code,

orig_system_address_ref, insert_update_flag,

customer_name, customer_number, customer_status,

primary_site_use_flag, LOCATION, address1,

address2, address3,

address4, city, state,

postal_code, country, cust_tax_reference,

last_updated_by, last_update_date, created_by,

creation_date, person_flag, person_first_name,

person_last_name, orig_system_party_ref,

customer_name_phonetic,



customer_category_code
  )

VALUES (v_org_id, v_ref_number, cur_det.site_use_code,

v_ref_number, 'I',

cur_det.customer_name, cur_det.customer_number, 'A',

'Y', cur_det.LOCATION, cur_det.address1,

cur_det.address2, cur_det.address3,

cur_det.address4, cur_det.city, cur_det.state,

cur_det.postal_code, cur_det.country, NULL,

0, SYSDATE, 0,

SYSDATE, cur_det.person_flag, cur_det.first_name,

cur_det.last_name, cur_det.party_reference,

cur_det.alternate_name,

UPPER (cur_det.customer_category_code)

);

v_ship_count := 1;





------------------------------- SHIP----------------------------
    FOR cur_det_ship IN

cur_customer_detail_ship_to (cur_det.customer_name,

cur_det.customer_number,

cur_det.org_code

)

LOOP

IF UPPER ( cur_det_ship.address1

|| cur_det_ship.address2

|| cur_det_ship.address3

|| cur_det_ship.address4

) <>

UPPER ( cur_det.address1

|| cur_det.address2

|| cur_det.address3

|| cur_det.address4

)

THEN

v_ref_number_ship :=

'A' || TO_CHAR (data_loading_import_row_seq_s.NEXTVAL);

v_primary_site_use_flag := 'N';

ELSIF UPPER ( cur_det_ship.address1

|| cur_det_ship.address2

|| cur_det_ship.address3

|| cur_det_ship.address4

) =

UPPER ( cur_det.address1

|| cur_det.address2

|| cur_det.address3

|| cur_det.address4

)

THEN

v_ref_number_ship := v_ref_number;

v_primary_site_use_flag := 'Y';

END IF;

INSERT INTO ra_customers_interface_all

(org_id, orig_system_customer_ref,

site_use_code, orig_system_address_ref,

insert_update_flag, customer_name,

customer_number, customer_status,

primary_site_use_flag, LOCATION,

address1, address2,

address3, address4,

city, state,

postal_code, country,

cust_tax_reference, last_updated_by,

last_update_date, created_by, creation_date,

person_flag,

person_first_name, person_last_name,

orig_system_party_ref,



customer_name_phonetic
  )

VALUES (v_org_id, v_ref_number,

cur_det_ship.site_use_code, v_ref_number_ship,

'I', cur_det_ship.customer_name,

cur_det_ship.customer_number, 'A',

v_primary_site_use_flag, cur_det_ship.LOCATION,

cur_det_ship.address1, cur_det_ship.address2,

cur_det_ship.address3, cur_det_ship.address4,

cur_det_ship.city, cur_det_ship.state,

cur_det_ship.postal_code, cur_det_ship.country,

NULL, 0,

SYSDATE, 0, SYSDATE,

cur_det_ship.person_flag,

cur_det_ship.first_name, cur_det_ship.last_name,

cur_det_ship.party_reference,

cur_det_ship.alternate_name

);

v_ship_count := 1 + 1;

UPDATE xx_customer_stag

SET flag_process = 'P',

upload_no = v_ref_number_ship

WHERE entry_id = cur_det_ship.entry_id;

END LOOP;

INSERT INTO ra_customer_profiles_int_all

(org_id, insert_update_flag,

orig_system_customer_ref,

customer_profile_class_name, credit_checking,

credit_hold,

currency_code,

overall_credit_limit,

trx_credit_limit,

-- STANDARD_TERMS ,

last_updated_by, last_update_date,

created_by, creation_date

)

VALUES (v_org_id, 'I',

v_ref_number,

'DEFAULT', NVL (cur_det.credit_checking_flag, 'N'),

NVL (cur_det.credit_hold_flag, 'N'),

NVL (cur_det.currency_code, 'INR'),

cur_det.over_all_credit_limit,

cur_det.order_credit_limit,

-- V_PAYMENT_TERMS_ID ,

-1, SYSDATE,

-1, SYSDATE

);

UPDATE xx_customer_stag

SET flag_process = 'P',

upload_no = v_ref_number

WHERE entry_id = cur_det.entry_id;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

v_erro_msg := SQLERRM;

UPDATE xx_customer_stag

SET flag_process = 'E',

error_message = v_erro_msg

WHERE entry_id = cur_det.entry_id;

COMMIT;

END;

END LOOP;

BEGIN

SELECT COUNT (*)

INTO vailidate_num

FROM xx_customer_stag

WHERE flag_process = 'E';

EXCEPTION

WHEN OTHERS

THEN

vailidate_num := 0;

END;

END xx_cust_interface;