CREATE OR REPLACE PACKAGE BODY APPS.L3ar_Api_Cust_Cont_Upd
AS
/* *********************************************************************************************************************
** Filename: L3ar_Api_Cust_Cont_Upd.pkb
******************************************************************************************************************** */
p_create_person_rec Hz_Party_V2pub.person_rec_type;
p_party_number_rec Hz_Party_V2pub.party_rec_type;
x_per_party_id NUMBER;
x_per_party_number VARCHAR2 (2000);
x_per_profile_id NUMBER;
x_per_return_status VARCHAR2 (2000);
x_per_msg_count NUMBER;
x_per_msg_data VARCHAR2 (2000);
p_org_contact_rec Hz_Party_Contact_V2pub.org_contact_rec_type;
x_rel_org_contact_id NUMBER;
x_rel_party_rel_id NUMBER;
x_rel_party_id NUMBER;
x_rel_party_number VARCHAR2 (2000);
x_rel_return_status VARCHAR2 (2000);
x_rel_msg_count NUMBER;
x_rel_msg_data VARCHAR2 (2000);
p_cr_cust_acc_role_rec Hz_Cust_Account_Role_V2pub.cust_account_role_rec_type;
x_role_cust_account_role_id NUMBER;
x_role_return_status VARCHAR2 (2000);
x_role_msg_count NUMBER;
x_role_msg_data VARCHAR2 (2000);
p_contact_point_rec Hz_Contact_Point_V2pub.contact_point_rec_type;
p_phone_rec Hz_Contact_Point_V2pub.phone_rec_type;
p_edi_rec_type Hz_Contact_Point_V2pub.edi_rec_type;
p_email_rec_type Hz_Contact_Point_V2pub.email_rec_type;
p_telex_rec_type Hz_Contact_Point_V2pub.telex_rec_type;
p_web_rec_type Hz_Contact_Point_V2pub.web_rec_type;
x_con_contact_point_id_p NUMBER;
x_con_return_status_p VARCHAR2 (2000);
x_con_msg_count_p NUMBER;
x_con_msg_data_p VARCHAR2 (2000);
x_con_contact_point_id_e NUMBER;
x_con_return_status_e VARCHAR2 (50);
x_con_msg_count_e NUMBER;
x_con_msg_data_e VARCHAR2 (2000);
l_commit_flag VARCHAR2 (2);
l_contact_id NUMBER := 0;
l_object_version_number NUMBER := 0;
l_con_party_id NUMBER := 0;
l_contact_exists VARCHAR2 (10) := 'Y';
l_email_exists VARCHAR2 (2) := 'Y';
l_create_counter NUMBER := 0;
l_update_counter NUMBER := 0;
l_cust_acct_input VARCHAR2 (1000);
l_cont_phone_exists VARCHAR2 (2);
l_contact_point_id NUMBER := 0;
l_object_version_number_p NUMBER := 0;
l_email_contact_point_id NUMBER := 0;
l_object_version_number_e NUMBER := 0;
l_update_contact_point_id NUMBER := 0;
l_contact_point_type VARCHAR2 (50);
l_object_version_number_upd NUMBER := 0;
l_person_title VARCHAR2(40);
l_person_name_suffix VARCHAR2(40);
l_object_version_number_cont NUMBER := 0;
l_object_version_number_party NUMBER := 0;
l_object_version_number_rel NUMBER := 0;
l_org_contact_id NUMBER := 0;
PROCEDURE l3ar_import_cust_contacts (
P_SOURCE IN VARCHAR2 DEFAULT NULL,
P_CUST_NUM_MASTER_ACCT IN VARCHAR2,
P_BILLING_ACCOUNT_NUM IN VARCHAR2 DEFAULT NULL,
P_CONTACT_FIRST_NAME IN VARCHAR2,
P_CONTACT_LAST_NAME IN VARCHAR2,
P_SUFFIX IN VARCHAR2 DEFAULT NULL,
P_PERSON_TITLE IN VARCHAR2,
P_JOB_TITLE IN VARCHAR2,
P_PHONE_LINE_TYPE IN VARCHAR2,
P_PHONE_COUNTRY_CODE IN VARCHAR2,
P_PHONE_AREA_CODE IN VARCHAR2,
P_PHONE_NUMBER IN VARCHAR2,
P_PHONE_EXTENSION IN VARCHAR2 DEFAULT NULL,
P_EMAIL_ADDRESS IN VARCHAR2,
P_COMMENTS IN VARCHAR2 DEFAULT NULL,
P_REFERENCE IN VARCHAR2 DEFAULT NULL,
P_STATUS OUT VARCHAR2,
P_MESSAGE OUT VARCHAR2)
AS
CURSOR l_cust_contact_import
IS -- fetching details from customer table
SELECT lcd.SOURCE,
lcd.cust_num_master_acct,
lcd.billing_account_num,
lcd.contact_first_name,
lcd.contact_last_name,
lcd.suffix,
lcd.person_title,
lcd.job_title,
lcd.phone_line_type,
lcd.phone_country_code,
lcd.phone_area_code,
lcd.phone_number,
lcd.phone_extension,
lcd.email_address,
lcd.comments,
lcd.REFERENCE,
hca.cust_account_id,
hca.party_id
FROM (SELECT P_SOURCE SOURCE,
P_CUST_NUM_MASTER_ACCT CUST_NUM_MASTER_ACCT,
P_BILLING_ACCOUNT_NUM BILLING_ACCOUNT_NUM,
P_CONTACT_FIRST_NAME CONTACT_FIRST_NAME,
P_CONTACT_LAST_NAME CONTACT_LAST_NAME,
P_SUFFIX SUFFIX,
P_PERSON_TITLE PERSON_TITLE,
P_JOB_TITLE JOB_TITLE,
P_PHONE_LINE_TYPE PHONE_LINE_TYPE,
P_PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE,
P_PHONE_AREA_CODE PHONE_AREA_CODE,
P_PHONE_NUMBER PHONE_NUMBER,
P_PHONE_EXTENSION PHONE_EXTENSION,
P_EMAIL_ADDRESS EMAIL_ADDRESS,
P_COMMENTS COMMENTS,
P_REFERENCE REFERENCE
FROM DUAL) lcd,
hz_cust_accounts hca
WHERE hca.account_number = lcd.cust_num_master_acct;
BEGIN
/* IF (P_CUST_ACCT_NUM is not null)
THEN
l_cust_acct_input := 'select p_cust_acct_num from dual';
ELSE
l_cust_acct_input := 'select cust_num_master_acct from l3ar_cust_contact_details';
END IF;*/
FOR c_contact_import IN l_cust_contact_import
LOOP
l_contact_id := 0;
l_object_version_number := 0;
l_con_party_id := 0;
BEGIN
SELECT hp.party_id, hp.object_version_number, hr.party_id
INTO l_contact_id, l_object_version_number, l_con_party_id
FROM hz_parties hp, hz_cust_accounts hca, hz_relationships hr
WHERE account_number = c_contact_import.cust_num_master_acct
AND hr.object_id = hca.party_id
AND hp.person_first_name =
c_contact_import.contact_first_name
AND hp.person_last_name =
c_contact_import.contact_last_name
AND hp.party_id = hr.subject_id;
--Checking whether contact exists for this account
IF (l_contact_id != 0)
THEN
l_contact_exists := 'Y';
ELSE
l_contact_exists := 'N';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_contact_exists := 'N';
l_contact_id := 0;
WHEN OTHERS
THEN
l_contact_exists := ' ';
DBMS_OUTPUT.PUT_LINE ('creation of person record');
P_MESSAGE :=
'Excpetion When Fecthing PArty Deatils :'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'Error MEssage'
|| SQLERRM;
L3_Write_Line.to_mgr (P_MESSAGE);
END;
IF (l_contact_exists = 'N')
THEN
BEGIN
BEGIN
-- Fnd_Global.apps_initialize (1012260, 50559, 222);
/* Fnd_Global.apps_initialize(Fnd_Global.user_id, Fnd_Global.resp_id, Fnd_Global.resp_appl_id); --Vish
p_party_number_rec.party_number := c_contact_import.cust_num_master_acct||'-CONT-'||HZ_PARTY_NUMBER_S.NEXTVAL;*/
p_create_person_rec.person_pre_name_adjunct :=
UPPER (c_contact_import.person_title);
p_create_person_rec.person_first_name :=
c_contact_import.contact_first_name;
p_create_person_rec.person_last_name :=
c_contact_import.contact_last_name;
p_create_person_rec.person_name_suffix :=
c_contact_import.suffix;
p_create_person_rec.created_by_module := 'TCA_V2_API';
DBMS_OUTPUT.PUT_LINE ('creation of person record');
p_create_person_rec.party_rec := p_party_number_rec;
Hz_Party_V2pub.create_person ('T',
p_create_person_rec,
x_per_party_id,
x_per_party_number,
x_per_profile_id,
x_per_return_status,
x_per_msg_count,
x_per_msg_data);
IF (x_per_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR While Creating Contact Party '
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'Error MEssage'
|| x_per_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE (x_per_return_status);
END IF;
END;
--Creation of realtionship
BEGIN
--Fnd_Global.apps_initialize(1012260,50559,222);
DBMS_OUTPUT.PUT_LINE ('creation of relationship');
p_org_contact_rec.created_by_module := 'TCA_V1_API';
p_org_contact_rec.party_rel_rec.subject_id := x_per_party_id;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id :=
c_contact_import.party_id;
p_org_contact_rec.party_rel_rec.object_type :=
'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code :=
'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type :=
'CONTACT';
p_org_contact_rec.job_title := c_contact_import.job_title;
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
Hz_Party_Contact_V2pub.create_org_contact (
'T',
p_org_contact_rec,
x_rel_org_contact_id,
x_rel_party_rel_id,
x_rel_party_id,
x_rel_party_number,
x_rel_return_status,
x_rel_msg_count,
x_rel_msg_data);
IF (x_rel_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR While Creating Contact Relation '
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'Error MEssage'
|| x_rel_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
END IF;
END;
-- CUSTOMER ROLE CREATION
BEGIN
-- NOTE:
-- must be unique CUST_ACCOUNT_ID, PARTY_ID,ROLE_TYPE
-- must be unique CUST_ACCT_SITE_ID, PARTY_ID,ROLE_TYPE
--Fnd_Global.apps_initialize(1012260,50559,222);
p_cr_cust_acc_role_rec.party_id := x_rel_party_id;
p_cr_cust_acc_role_rec.cust_account_id :=
c_contact_import.cust_account_id;
p_cr_cust_acc_role_rec.primary_flag := 'N';
p_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_cr_cust_acc_role_rec.created_by_module := 'TCA_V1_API';
-- DBMS_OUTPUT.put_line ('Inside Craetion2');
Hz_Cust_Account_Role_V2pub.create_cust_account_role (
'T',
p_cr_cust_acc_role_rec,
x_role_cust_account_role_id,
x_role_return_status,
x_role_msg_count,
x_role_msg_data);
IF (x_role_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR While Creating Contact Role '
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'Error MEssage'
|| x_role_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('contact role');
END IF;
END;
BEGIN
--Fnd_Global.apps_initialize(1012260,50559,222);
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := x_rel_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_phone_rec.phone_number := c_contact_import.phone_number;
p_phone_rec.phone_line_type := --'GEN';
UPPER (c_contact_import.phone_line_type);
p_phone_rec.phone_country_code :=
c_contact_import.phone_country_code;
p_phone_rec.phone_area_code :=
c_contact_import.phone_area_code;
p_phone_rec.phone_extension :=
c_contact_import.phone_extension;
p_contact_point_rec.orig_system_reference :=
c_contact_import.reference;
Hz_Contact_Point_V2pub.create_contact_point (
'T',
p_contact_point_rec,
p_edi_rec_type,
p_email_rec_type,
p_phone_rec,
p_telex_rec_type,
p_web_rec_type,
x_con_contact_point_id_p,
x_con_return_status_p,
x_con_msg_count_p,
x_con_msg_data_p);
IF (x_con_return_status_p = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR While Creating Phone Contact'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'Error MEssage'
|| x_con_msg_data_p;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE (x_con_return_status_p);
DBMS_OUTPUT.PUT_LINE ('phn contact');
END IF;
IF (c_contact_import.email_address IS NOT NULL)
THEN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := x_rel_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.orig_system_reference :=
c_contact_import.reference;
p_email_rec_type.email_format := 'MAILHTML';
p_email_rec_type.email_address :=
c_contact_import.email_address;
Hz_Contact_Point_V2pub.create_contact_point (
'T',
p_contact_point_rec,
p_edi_rec_type,
p_email_rec_type,
p_phone_rec,
p_telex_rec_type,
p_web_rec_type,
x_con_contact_point_id_e,
x_con_return_status_e,
x_con_msg_count_e,
x_con_msg_data_e);
IF (x_con_return_status_e = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE Creation of EMAIL details FOR CUSTOMER'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'ERROR Message: '
|| x_con_msg_data_e;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('email details');
DBMS_OUTPUT.PUT_LINE (x_con_return_status_e);
END IF;
END IF;
IF (l_commit_flag = 'Y')
THEN
COMMIT;
P_STATUS := 'S';
l_create_counter := l_create_counter + 1;
ELSE
ROLLBACK;
P_STATUS := 'E';
END IF;
END;
END;
--update Contact
ELSIF (l_contact_exists = 'Y')
THEN
BEGIN
--Fnd_Global.apps_initialize(1012260,50559,222);
p_create_person_rec.person_pre_name_adjunct :=
UPPER (c_contact_import.person_title);
p_create_person_rec.person_first_name :=
c_contact_import.contact_first_name;
p_create_person_rec.person_last_name :=
c_contact_import.contact_last_name;
p_create_person_rec.person_name_suffix :=
c_contact_import.suffix;
p_create_person_rec.party_rec.party_id := l_contact_id;
p_create_person_rec.created_by_module := 'TCA_V2_API';
Hz_Party_V2pub.update_person ('T',
p_create_person_rec,
l_object_version_number,
x_per_profile_id,
x_per_return_status,
x_per_msg_count,
x_per_msg_data);
IF (x_per_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
P_MESSAGE :=
'ERROR WHILE updating personal details FOR CUSTOMER'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'ERROR Message: '
|| x_per_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('personal details updating');
DBMS_OUTPUT.PUT_LINE (x_per_return_status);
l_commit_flag := 'N';
END IF;
END;
BEGIN
BEGIN
SELECT 'Y', contact_point_id, object_version_number
INTO l_cont_phone_exists,
l_contact_point_id,
l_object_version_number_p
FROM hz_contact_points
WHERE owner_table_id = l_con_party_id
AND contact_point_type = 'PHONE'
AND PHONE_NUMBER = c_contact_import.phone_number;
--L3_Write_Line.to_mgr('PHONE FLAG'||l_cont_phone_exists);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_cont_phone_exists := 'N';
l_contact_point_id := 0;
l_object_version_number_p := 0;
WHEN OTHERS
THEN
l_cont_phone_exists := ' ';
l_contact_point_id := 0;
l_object_version_number_p := 0;
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE Validating Phone Number FOR CUSTOMER'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'ERROR Message: '
|| SQLERRM;
L3_Write_Line.to_mgr (P_MESSAGE);
END;
IF (l_cont_phone_exists = 'N')
THEN
--Fnd_Global.apps_initialize(1012260,50559,222);
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.orig_system_reference :=
c_contact_import.reference;
p_phone_rec.phone_number := c_contact_import.phone_number;
p_phone_rec.phone_line_type :=
UPPER (c_contact_import.phone_line_type);
p_phone_rec.phone_country_code :=
c_contact_import.phone_country_code;
p_phone_rec.phone_area_code :=
c_contact_import.phone_area_code;
p_phone_rec.phone_extension :=
c_contact_import.phone_extension;
DBMS_OUTPUT.PUT_LINE ('Inside Craetion3');
Hz_Contact_Point_V2pub.create_contact_point (
'T',
p_contact_point_rec,
p_edi_rec_type,
p_email_rec_type,
p_phone_rec,
p_telex_rec_type,
p_web_rec_type,
x_con_contact_point_id_p,
x_con_return_status_p,
x_con_msg_count_p,
x_con_msg_data_p);
IF (x_con_return_status_p = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE Creation of Phone Number FOR CUSTOMER'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'ERROR Message: '
|| x_con_msg_data_p;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('phn no creation');
END IF;
--Update Phone Contact
/* ELSIF(l_cont_phone_exists = 'Y')
THEN
BEGIN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.contact_point_id := l_contact_point_id;
p_phone_rec.phone_number := c_contact_import.phone_number;
p_phone_rec.phone_line_type :=
c_contact_import.phone_line_type;
p_phone_rec.phone_country_code :=
c_contact_import.phone_country_code;
p_phone_rec.phone_area_code :=
c_contact_import.phone_area_code;
p_phone_rec.phone_extension :=
c_contact_import.phone_extension;
Hz_Contact_Point_V2pub.update_phone_contact_point (
'T',
p_contact_point_rec,
p_phone_rec,
l_object_version_number_p,
x_con_return_status_p,
x_con_msg_count_p,
x_con_msg_data_p);
IF (x_con_return_status_p = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE := 'ERROR WHILE UPDATING PHONE NUMBER FOR CUSTOMER'||c_contact_import.contact_first_name
||'.'||c_contact_import.contact_last_name
||'ERROR Message: '||x_con_msg_data_p;
L3_Write_Line.to_mgr(P_MESSAGE);
dbms_output.put_line (P_MESSAGE);
END IF;
END;*/
END IF;
IF (c_contact_import.email_address IS NOT NULL)
THEN
BEGIN
SELECT 'Y', contact_point_id, object_version_number
INTO l_email_exists,
l_email_contact_point_id,
l_object_version_number_e
FROM hz_contact_points
WHERE owner_table_id = l_con_party_id
AND contact_point_type = 'EMAIL'
AND email_address =
c_contact_import.email_address;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_email_exists := 'N';
l_email_contact_point_id := 0;
l_object_version_number_e := 0;
WHEN TOO_MANY_ROWS
THEN
l_email_exists := ' ';
l_commit_flag := 'N';
L3_Write_Line.to_mgr (
'Excpetion When Fecthing PArty email Details :'
|| SQLERRM);
END;
IF (l_email_exists = 'N')
THEN
BEGIN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.orig_system_reference :=
c_contact_import.reference;
p_email_rec_type.email_format := 'MAILHTML';
p_email_rec_type.email_address :=
c_contact_import.email_address;
Hz_Contact_Point_V2pub.create_contact_point (
'T',
p_contact_point_rec,
p_edi_rec_type,
p_email_rec_type,
p_phone_rec,
p_telex_rec_type,
p_web_rec_type,
x_con_contact_point_id_e,
x_con_return_status_e,
x_con_msg_count_e,
x_con_msg_data_e);
IF (x_con_return_status_e = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE Creation of EMAIL FOR CUSTOMER'
|| c_contact_import.contact_first_name
|| '.'
|| c_contact_import.contact_last_name
|| 'ERROR Message: '
|| x_con_msg_data_e;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('email creation');
END IF;
END;
-- Update Email Address
/* ELSIF (l_email_exists = 'Y')
THEN
BEGIN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.contact_point_id := l_email_contact_point_id;
p_email_rec_type.email_format := 'MAILHTML';
p_email_rec_type.email_address :=
c_contact_import.email_address;
Hz_Contact_Point_V2pub.update_email_contact_point (
'T',
p_contact_point_rec,
p_email_rec_type,
l_object_version_number_e,
x_con_return_status_e,
x_con_msg_count_e,
x_con_msg_data_e);
IF (x_con_return_status_e = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE := 'ERROR WHILE UPDATING PHONE NUMBER FOR CUSTOMER'||c_contact_import.contact_first_name
||'.'||c_contact_import.contact_last_name
||'ERROR Message: '||x_con_msg_data_e;
L3_Write_Line.to_mgr(P_MESSAGE);
dbms_output.put_line (P_MESSAGE);
END IF;
END;*/
END IF;
END IF;
IF (l_commit_flag = 'Y')
THEN
COMMIT;
P_STATUS := 'S';
l_update_counter := l_update_counter + 1;
ELSE
ROLLBACK;
P_STATUS := 'E';
END IF;
END;
/*BEGIN
HZ_CONTACT_POINT_V2PUB.update_contact_point ( p_init_msg_list => fnd_api.g_false ,
p_contact_point_rec => p_contact_point_rec ,
p_email_rec => p_email_rec_type ,
p_phone_rec => p_phone_rec_type ,
p_object_version_number => l_obj_num ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
END;*/
END IF;
END LOOP;
L3_Write_Line.to_mgr (
'Total Number of Customer Contacts Created:' || l_create_counter);
L3_Write_Line.to_mgr (
'Total Number of Customer Contacts Updated:' || l_update_counter);
DBMS_OUTPUT.PUT_LINE (p_status);
DBMS_OUTPUT.PUT_LINE (p_message);
END;
PROCEDURE l3ar_update_cust_contacts (
P_SOURCE IN VARCHAR2 DEFAULT NULL,
P_CUST_NUM_MASTER_ACCT IN VARCHAR2,
P_BILLING_ACCOUNT_NUM IN VARCHAR2 DEFAULT NULL,
P_CONTACT_FIRST_NAME IN VARCHAR2,
P_CONTACT_LAST_NAME IN VARCHAR2,
P_SUFFIX IN VARCHAR2 DEFAULT NULL,
P_PERSON_TITLE IN VARCHAR2,
P_JOB_TITLE IN VARCHAR2,
P_PHONE_LINE_TYPE IN VARCHAR2,
P_PHONE_COUNTRY_CODE IN VARCHAR2,
P_PHONE_AREA_CODE IN VARCHAR2,
P_PHONE_NUMBER IN VARCHAR2,
P_PHONE_EXTENSION IN VARCHAR2 DEFAULT NULL,
P_EMAIL_ADDRESS IN VARCHAR2,
P_COMMENTS IN VARCHAR2 DEFAULT NULL,
P_REFERENCE IN VARCHAR2 DEFAULT NULL,
P_PARTY_ID IN VARCHAR2 DEFAULT NULL,
P_CONTACT_POINT_ID IN VARCHAR2,
P_STATUS OUT VARCHAR2,
P_MESSAGE OUT VARCHAR2)
IS
CURSOR l_cust_cont_import
IS -- fetching details from customer table
SELECT lcd.SOURCE,
lcd.cust_num_master_acct,
lcd.billing_account_num,
lcd.contact_first_name,
lcd.contact_last_name,
lcd.suffix,
lcd.person_title,
lcd.job_title,
lcd.phone_line_type,
lcd.phone_country_code,
lcd.phone_area_code,
lcd.phone_number,
lcd.phone_extension,
lcd.email_address,
lcd.comments,
lcd.contact_person_party_id,
lcd.REFERENCE,
lcd.contact_point_id,
hca.cust_account_id,
hca.party_id
FROM (SELECT P_SOURCE SOURCE,
P_CUST_NUM_MASTER_ACCT CUST_NUM_MASTER_ACCT,
P_BILLING_ACCOUNT_NUM BILLING_ACCOUNT_NUM,
P_CONTACT_FIRST_NAME CONTACT_FIRST_NAME,
P_CONTACT_LAST_NAME CONTACT_LAST_NAME,
P_SUFFIX SUFFIX,
P_PERSON_TITLE PERSON_TITLE,
P_JOB_TITLE JOB_TITLE,
P_PHONE_LINE_TYPE PHONE_LINE_TYPE,
P_PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE,
P_PHONE_AREA_CODE PHONE_AREA_CODE,
P_PHONE_NUMBER PHONE_NUMBER,
P_PHONE_EXTENSION PHONE_EXTENSION,
P_EMAIL_ADDRESS EMAIL_ADDRESS,
P_COMMENTS COMMENTS,
P_CONTACT_POINT_ID CONTACT_POINT_ID,
P_PARTY_ID CONTACT_PERSON_PARTY_ID,
P_REFERENCE REFERENCE
FROM DUAL) lcd,
hz_cust_accounts hca
WHERE hca.account_number = lcd.cust_num_master_acct;
BEGIN
FOR c_cust_cont_import IN l_cust_cont_import
LOOP
BEGIN
SELECT hp.party_id, hp.object_version_number, hr.party_id,hp.person_title,hp.person_name_suffix,hp2.object_version_number,hr.object_version_number,
hoc.object_version_number,hoc.org_contact_id
INTO l_contact_id, l_object_version_number_p, l_con_party_id,l_person_title,l_person_name_suffix,l_object_version_number_party,
l_object_version_number_rel,
l_object_version_number_cont,l_org_contact_id
FROM hz_parties hp, hz_cust_accounts hca, hz_relationships hr,hz_org_contacts hoc,hz_parties hp2
WHERE account_number =
c_cust_cont_import.cust_num_master_acct
AND hr.object_id = hca.party_id
AND hp.person_first_name =
c_cust_cont_import.contact_first_name
AND hp.person_last_name =
c_cust_cont_import.contact_last_name
AND hoc.party_relationship_id = hr.relationship_id
AND hp2.party_id = hr.party_id
AND hp.party_id = hr.subject_id;
--Checking whether contact exists for this account
IF (l_contact_id != 0)
THEN
l_contact_exists := 'Y';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_contact_exists := 'N';
l_contact_id := 0;
BEGIN
SELECT hp.party_id, hp.object_version_number, hr.party_id,hp.person_title,hp.person_name_suffix,hp2.object_version_number,hr.object_version_number,
hoc.object_version_number,hoc.org_contact_id
INTO l_contact_id, l_object_version_number_p, l_con_party_id,l_person_title,l_person_name_suffix,l_object_version_number_party,
l_object_version_number_rel,
l_object_version_number_cont,l_org_contact_id
FROM hz_parties hp, hz_cust_accounts hca, hz_relationships hr,hz_org_contacts hoc,hz_parties hp2
WHERE account_number =
c_cust_cont_import.cust_num_master_acct
AND hr.object_id = hca.party_id
AND hp.party_id = NVL(c_cust_cont_import.contact_person_party_id,9999999999999999)
AND hp2.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hp.party_id = hr.subject_id;
IF (l_contact_id != 0)
THEN
l_contact_exists := 'Y';
ELSE
l_contact_exists := 'N';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_contact_exists := 'N';
l_contact_id := 0;
WHEN OTHERS
THEN
l_contact_exists := ' ';
DBMS_OUTPUT.PUT_LINE ('Updation of person record');
P_MESSAGE :=
'Excpetion When Fecthing PArty Deatils :'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'Error MEssage'
|| SQLERRM;
L3_Write_Line.to_mgr (P_MESSAGE);
END;
WHEN OTHERS
THEN
l_contact_exists := ' ';
DBMS_OUTPUT.PUT_LINE ('Updation of person record');
P_MESSAGE :=
'Excpetion When Fecthing PArty Deatils :'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'Error MEssage'
|| SQLERRM;
L3_Write_Line.to_mgr (P_MESSAGE);
END;
IF (l_contact_exists = 'Y')--Update the records if party exists
THEN
IF(c_cust_cont_import.contact_person_party_id is not null) --Check if needed to update party_details
THEN
--Update of Party Details
BEGIN
--Fnd_Global.apps_initialize(1012260,50559,222);
p_create_person_rec.person_pre_name_adjunct :=
UPPER (c_cust_cont_import.person_title);
p_create_person_rec.person_first_name :=
c_cust_cont_import.contact_first_name;
p_create_person_rec.person_last_name :=
c_cust_cont_import.contact_last_name;
p_create_person_rec.person_name_suffix :=
c_cust_cont_import.suffix;
p_create_person_rec.party_rec.party_id := l_contact_id;
--p_create_person_rec.created_by_module := 'TCA_V2_API';
Hz_Party_V2pub.update_person ('T',
p_create_person_rec,
l_object_version_number_p,
x_per_profile_id,
x_per_return_status,
x_per_msg_count,
x_per_msg_data);
IF (x_per_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
P_MESSAGE :=
'ERROR WHILE updating personal details FOR CUSTOMER'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'ERROR Message: '
|| x_per_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.PUT_LINE ('personal details updating');
DBMS_OUTPUT.PUT_LINE (x_per_return_status);
l_commit_flag := 'N';
END IF;
END;
--Updat JOB_TITLE
BEGIN --Fnd_Global.apps_initialize(1012260,50559,222);
DBMS_OUTPUT.PUT_LINE ('Updation of Job Title');
-- p_org_contact_rec.created_by_module := 'TCA_V1_API';
p_org_contact_rec.party_rel_rec.subject_id := l_contact_id;
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id :=
c_cust_cont_import.party_id;
p_org_contact_rec.party_rel_rec.object_type :=
'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name :=
'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code :=
'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type :=
'CONTACT';
p_org_contact_rec.org_contact_id := l_org_contact_id;
p_org_contact_rec.job_title := c_cust_cont_import.job_title;
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
Hz_Party_Contact_V2pub.update_org_contact (
'T',
p_org_contact_rec,
l_object_version_number_cont,
l_object_version_number_rel,
l_object_version_number_party,
x_rel_return_status,
x_rel_msg_count,
x_rel_msg_data);
IF (x_rel_return_status = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR While Updating Contact Relation '
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'Error MEssage'
|| x_rel_msg_data;
L3_Write_Line.to_mgr (P_MESSAGE);
END IF;
END;
END IF;
--Update of Party Details Complete
IF(c_cust_cont_import.contact_point_id is not null) THEN
BEGIN
--Query to Fetch the Contact Point Type
SELECT contact_point_type,
object_version_number,
owner_table_id
INTO l_contact_point_type,
l_object_version_number_upd,
l_con_party_id
FROM hz_contact_points
WHERE contact_point_id = c_cust_cont_import.contact_point_id;
DBMS_OUTPUT.put_line('Record Type'||l_contact_point_type);
EXCEPTION
WHEN OTHERS
THEN
P_MESSAGE :=
'ERROR WHILE fetching Contact Point Details for CUSTOMER'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| SQLERRM;
L3_Write_Line.to_mgr (P_MESSAGE);
l_contact_point_type := 'ERROR';
l_commit_flag := 'N';
END;
ELSE
P_MESSAGE :=
' Contact Point Details for CUSTOMER'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name || 'are not passed so updating Personal Details Alone';
l_commit_flag := 'Y';
END IF;
IF (l_contact_point_type = 'PHONE')
THEN
BEGIN
p_contact_point_rec.contact_point_type := 'PHONE';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
-- p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.contact_point_id :=
c_cust_cont_import.contact_point_id;
p_phone_rec.phone_number := c_cust_cont_import.phone_number;
p_phone_rec.phone_line_type :=
c_cust_cont_import.phone_line_type;
p_phone_rec.phone_country_code :=
c_cust_cont_import.phone_country_code;
p_phone_rec.phone_area_code :=
c_cust_cont_import.phone_area_code;
p_phone_rec.phone_extension :=
c_cust_cont_import.phone_extension;
Hz_Contact_Point_V2pub.update_phone_contact_point (
'T',
p_contact_point_rec,
p_phone_rec,
l_object_version_number_upd,
x_con_return_status_p,
x_con_msg_count_p,
x_con_msg_data_p);
IF (x_con_return_status_p = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE UPDATING PHONE NUMBER FOR CUSTOMER'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'ERROR Message: '
|| x_con_msg_data_p;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.put_line (P_MESSAGE);
END IF;
END;
ELSIF (l_contact_point_type = 'EMAIL')
THEN
BEGIN
p_contact_point_rec.contact_point_type := 'EMAIL';
p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
p_contact_point_rec.owner_table_id := l_con_party_id;
-- p_contact_point_rec.created_by_module := 'TCA_V1_API';
p_contact_point_rec.contact_point_id :=
c_cust_cont_import.contact_point_id;
p_email_rec_type.email_format := 'MAILHTML';
p_email_rec_type.email_address :=
c_cust_cont_import.email_address;
Hz_Contact_Point_V2pub.update_email_contact_point (
'T',
p_contact_point_rec,
p_email_rec_type,
l_object_version_number_upd,
x_con_return_status_e,
x_con_msg_count_e,
x_con_msg_data_e);
IF (x_con_return_status_e = 'S')
THEN
l_commit_flag := 'Y';
ELSE
l_commit_flag := 'N';
P_MESSAGE :=
'ERROR WHILE UPDATING EMAIL FOR CUSTOMER'
|| c_cust_cont_import.contact_first_name
|| '.'
|| c_cust_cont_import.contact_last_name
|| 'ERROR Message: '
|| x_con_msg_data_e;
L3_Write_Line.to_mgr (P_MESSAGE);
DBMS_OUTPUT.put_line (P_MESSAGE);
END IF;
END;
END IF;
END IF;
IF (l_commit_flag = 'Y')
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
PROCEDURE L3AR_GET_CUSTCONTACT_PARTYID (
P_PARTYID IN VARCHAR2,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
SELECT HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HP2.PARTY_NAME CONTACT_NAME,
HOC.JOB_TITLE,
HCA.CUST_ACCOUNT_ID,
HP2.PERSON_FIRST_NAME,
HP2.PERSON_LAST_NAME,
HP2.PERSON_NAME_SUFFIX,
HP2.PERSON_TITLE,
HOC.JOB_TITLE,
HCP.PHONE_LINE_TYPE,
HCP.PHONE_COUNTRY_CODE,
HCP.PHONE_AREA_CODE,
HCP.PHONE_NUMBER,
HCP.PHONE_EXTENSION,
HCP.EMAIL_ADDRESS,
HCP.ORIG_SYSTEM_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA,
HZ_RELATIONSHIPS HRE,
HZ_CONTACT_POINTS HCP,
HZ_PARTIES HP,
HZ_PARTIES HP2,
HZ_ORG_CONTACTS HOC
WHERE HCA.CUST_ACCOUNT_ID = P_PARTYID
AND HRE.OBJECT_ID = HCA.PARTY_ID
AND HRE.OBJECT_ID = HP.PARTY_ID
AND HRE.PARTY_ID = HCP.OWNER_TABLE_ID
AND HRE.SUBJECT_ID = HP2.PARTY_ID
AND HRE.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID;
END L3AR_GET_CUSTCONTACT_PARTYID;
PROCEDURE L3AR_GET_CUSTCONTACT_CUSTID (
P_CUST_ID IN VARCHAR2,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
SELECT HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HP2.PARTY_NAME CONTACT_NAME,
HOC.JOB_TITLE,
HCA.CUST_ACCOUNT_ID,
HP2.PERSON_FIRST_NAME,
HP2.PERSON_LAST_NAME,
HP2.PERSON_NAME_SUFFIX,
HP2.PERSON_TITLE,
HOC.JOB_TITLE,
HCP.PHONE_LINE_TYPE,
HCP.PHONE_COUNTRY_CODE,
HCP.PHONE_AREA_CODE,
HCP.PHONE_NUMBER,
HCP.PHONE_EXTENSION,
HCP.EMAIL_ADDRESS,
HCP.ORIG_SYSTEM_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA,
HZ_RELATIONSHIPS HRE,
HZ_CONTACT_POINTS HCP,
HZ_PARTIES HP,
HZ_PARTIES HP2,
HZ_ORG_CONTACTS HOC
WHERE HCA.CUST_ACCOUNT_ID = P_CUST_ID
AND HRE.OBJECT_ID = HCA.PARTY_ID
AND HRE.OBJECT_ID = HP.PARTY_ID
AND HRE.PARTY_ID = HCP.OWNER_TABLE_ID
AND HRE.SUBJECT_ID = HP2.PARTY_ID
AND HRE.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID;
END L3AR_GET_CUSTCONTACT_CUSTID;
PROCEDURE L3AR_GET_CUSTCONTACT_ACCNUM (
P_ACCOUNT_NUM IN VARCHAR2,
p_return_cur OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_return_cur FOR
SELECT HCA.ACCOUNT_NUMBER,
HP.PARTY_NAME,
HP2.PARTY_NAME CONTACT_NAME,
HOC.JOB_TITLE,
HCA.CUST_ACCOUNT_ID,
HP2.PERSON_FIRST_NAME,
HP2.PERSON_LAST_NAME,
HP2.PERSON_NAME_SUFFIX,
HP2.PERSON_TITLE,
HOC.JOB_TITLE,
HCP.PHONE_LINE_TYPE,
HCP.PHONE_COUNTRY_CODE,
HCP.PHONE_AREA_CODE,
HCP.PHONE_NUMBER,
HCP.PHONE_EXTENSION,
HCP.EMAIL_ADDRESS,
HCP.ORIG_SYSTEM_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA,
HZ_RELATIONSHIPS HRE,
HZ_CONTACT_POINTS HCP,
HZ_PARTIES HP,
HZ_PARTIES HP2,
HZ_ORG_CONTACTS HOC
WHERE HCA.account_number = P_ACCOUNT_NUM
AND HRE.OBJECT_ID = HCA.PARTY_ID
AND HRE.OBJECT_ID = HP.PARTY_ID
AND HRE.PARTY_ID = HCP.OWNER_TABLE_ID
AND HRE.SUBJECT_ID = HP2.PARTY_ID
AND HRE.RELATIONSHIP_ID = HOC.PARTY_RELATIONSHIP_ID;
END L3AR_GET_CUSTCONTACT_ACCNUM;
PROCEDURE L3AR_GET_CUSTOMER (p_where IN VARCHAR2,
p_page_index IN NUMBER DEFAULT NULL,
p_page_size IN NUMBER DEFAULT NULL,
p_return_cur IN OUT SYS_REFCURSOR)
IS
l_sql VARCHAR2 (32767) := '[[BASE]]';
l_row_start NUMBER;
l_row_end NUMBER;
BEGIN
IF p_page_index IS NOT NULL AND p_page_size IS NOT NULL
THEN
l_row_start := p_page_index * p_page_size;
l_row_end := (p_page_index + 1) * p_page_size;
l_sql :=
'select t2.* from (select t1.*, rownum rnum from ([[BASE]]) t1 where rownum <= '
|| l_row_end
|| ') t2 where rnum > '
|| l_row_start;
END IF;
l_sql :=
REPLACE (
l_sql,
'[[BASE]]',
REPLACE (g_get_customer_sql, g_get_customer_where, p_where));
OPEN p_return_cur FOR l_sql;
END L3AR_GET_CUSTOMER;
END L3ar_Api_Cust_Cont_Upd;
/
No comments :
Post a Comment