Oracle Interface to Upload Customer Communication Details

 

In this post i will share you PLSQL script with Oracle Interface to upload Customer Communication Details in Oracle apps.

 

 

PROCEDURE xx_commu_detail

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_party_id NUMBER;

CURSOR cur_customer_detail

IS

SELECT FROM xx_customer_stag

WHERE flag_process = 'P'

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

AND NVL(UPLOAD_NO,'NULL') <>'NULL'

AND NVL(CATE_REQUEST_ID_FLAG,'NULL') = 'NULL';

CURSOR cur_get_party_id (v_ref_no VARCHAR2)

IS

SELECT party_id

FROM hz_parties

WHERE orig_system_reference = v_ref_no;

CURSOR cur_get_org_id (v_org_code VARCHAR2)

IS

SELECT organization_id

FROM hr_operating_units

WHERE UPPER (NAME) = UPPER (v_org_code);

BEGIN

FOR cur_det IN cur_customer_detail

LOOP

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 := 'C' || 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;

----------Script for Updating HZ_PARTIES----------------------------

UPDATE hz_parties

SET person_pre_name_adjunct = cur_det.title

WHERE orig_system_reference = cur_det.upload_no;

IF cur_det.date_of_birth IS NOT NULL

THEN

OPEN cur_get_party_id (cur_det.upload_no);

FETCH cur_get_party_id

INTO v_party_id;

IF cur_get_party_id%NOTFOUND

THEN

v_party_id := NULL;

CLOSE cur_get_party_id;

END IF;

CLOSE cur_get_party_id;

IF v_party_id IS NOT NULL

THEN

UPDATE hz_person_profiles

SET date_of_birth = cur_det.date_of_birth

WHERE party_id = v_party_id;

END IF;

END IF;

--------Script for FAX No.------------------------------------------

IF cur_det.office_telephone_no IS NOT NULL

THEN

INSERT INTO ar.ra_contact_phones_int_all

(orig_system_customer_ref,

orig_system_address_ref,

orig_system_contact_ref,

orig_system_telephone_ref,

insert_update_flag,

contact_first_name,

contact_last_name,

telephone,

telephone_area_code,

telephone_type,

last_update_date,

last_updated_by, creation_date,

created_by, org_id,

contact_point_type
 

)

VALUES (cur_det.upload_no --ORIG_SYSTEM_CUSTOMER_REF

,

cur_det.upload_no --ORIG_SYSTEM_ADDRESS_REF

,

contact_ref --ORIG_SYSTEM_CONTACT_REF

,

f_ref_number --ORIG_SYSTEM_TELEPHONE_REF

,

'I' --INSERT_UPDATE_FLAG

,

cur_det.contact_first_name --CONTACT_FIRST_NAME

,

cur_det.contact_last_name --CONTACT_LAST_NAME

,

cur_det.office_telephone_no --TELEPHONE

,

cur_det.o_telephone_area_code

--TELEPHONE_AREA_CODE

,

'FAX' --TELEPHONE_TYPE

,

SYSDATE --LAST_UPDATE_DATE

,

4670 --LAST_UPDATED_BY

, SYSDATE --CREATION_DATE

,

4670 --CREATED_BY

, v_org_id --ORG_ID

,

'PHONE' --CONTACT_POINT_TYPE

);

END IF;

----------Script for Residence No.----------------------------------

IF cur_det.residence_telephone_no IS NOT NULL

THEN

INSERT INTO ar.ra_contact_phones_int_all

(orig_system_customer_ref,

orig_system_address_ref,

orig_system_contact_ref,

orig_system_telephone_ref,

insert_update_flag,

contact_first_name,

contact_last_name,

telephone,

telephone_area_code,

telephone_type,

last_update_date,

last_updated_by, creation_date,

created_by, org_id,

contact_point_type
 

)

VALUES (cur_det.upload_no --ORIG_SYSTEM_CUSTOMER_REF

,

cur_det.upload_no --ORIG_SYSTEM_ADDRESS_REF

,

contact_ref --ORIG_SYSTEM_CONTACT_REF

,

r_ref_number --ORIG_SYSTEM_TELEPHONE_REF

,

'I' --INSERT_UPDATE_FLAG

,

cur_det.contact_first_name --CONTACT_FIRST_NAME

,

cur_det.contact_last_name --CONTACT_LAST_NAME

,

cur_det.residence_telephone_no --TELEPHONE

,

cur_det.r_telephone_area_code

--TELEPHONE_AREA_CODE

,

'GEN' --TELEPHONE_TYPE

,

SYSDATE --LAST_UPDATE_DATE

,

4670 --LAST_UPDATED_BY

, SYSDATE --CREATION_DATE

,

4670 --CREATED_BY

, v_org_id --ORG_ID

,

'PHONE' --CONTACT_POINT_TYPE

);

END IF;

----------For Mobile Number-----------------------------------------

IF cur_det.mobile_no IS NOT NULL

THEN

INSERT INTO ra_contact_phones_int_all

(orig_system_customer_ref,

orig_system_address_ref,

orig_system_contact_ref,

orig_system_telephone_ref, insert_update_flag,

contact_first_name,

contact_last_name, telephone,

telephone_type, last_update_date,

last_updated_by, creation_date, created_by,

org_id, contact_point_type

)

VALUES (cur_det.upload_no --ORIG_SYSTEM_CUSTOMER_REF

,

cur_det.upload_no --ORIG_SYSTEM_ADDRESS_REF

,

contact_ref --ORIG_SYSTEM_CONTACT_REF

,

m_ref_number --ORIG_SYSTEM_TELEPHONE_REF

, 'I' --

,

cur_det.contact_first_name --CONTACT_FIRST_NAME

,

cur_det.contact_last_name, cur_det.mobile_no,

'MOBILE', SYSDATE,

0, SYSDATE, 0,

v_org_id, 'PHONE'

);

END IF;

-----------Script for Email Address--------------------------------

IF cur_det.email_address IS NOT NULL

THEN

INSERT INTO ar.ra_contact_phones_int_all

(orig_system_customer_ref,

orig_system_address_ref,

orig_system_contact_ref,

orig_system_telephone_ref,

insert_update_flag,

contact_first_name,

contact_last_name,

email_address,

last_update_date,

last_updated_by, creation_date,

created_by, org_id,

contact_point_type
 

)

VALUES (cur_det.upload_no -- ORIG_SYSTEM_CUSTOMER_REF

,

cur_det.upload_no -- ORIG_SYSTEM_ADDRESS_REF

,

contact_ref -- ORIG_SYSTEM_CONTACT_REF

,

e_ref_number -- ORIG_SYSTEM_TELEPHONE_REF

,

'I' -- INSERT_UPDATE_FLAG

,

cur_det.contact_first_name -- CONTACT_FIRST_NAME

,

cur_det.contact_last_name -- CONTACT_LAST_NAME

,

cur_det.email_address -- EMAIL_ADDRESS

,

SYSDATE -- LAST_UPDATE_DATE

,

4670 -- LAST_UPDATED_BY

, SYSDATE -- CREATION_DATE

,

4670 -- CREATED_BY

, v_org_id,

'EMAIL' -- CONTACT_POINT_TYPE

);

END IF;

UPDATE xx_customer_stag

SET cate_request_id_flag = 'P'

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;

END xx_commu_detail;