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