ORACLE MASTERMINDS: Customer Contact Creation Email Creation and assignment API

CREATE OR REPLACE PACKAGE XX_CUST_CONT_PKG
IS
--=================
-- Global Variables
--=================
gn_request_id NUMBER := apps.fnd_global.conc_request_id;
gn_prog_appl_id NUMBER := apps.fnd_global.prog_appl_id;
gn_responsibility_id NUMBER := apps.fnd_global.resp_id;
gn_respappl_id NUMBER := apps.fnd_global.resp_appl_id;
gn_program_id NUMBER := apps.fnd_global.conc_program_id;
gn_user_id NUMBER := apps.fnd_global.user_id;
gn_login_id NUMBER := apps.fnd_global.login_id;
gn_business_group_id NUMBER := apps.fnd_global.per_business_group_id;
gd_sysdate DATE := SYSDATE;
p_api_version NUMBER := 1.0;
PROCEDURE main(x_errbuf OUT VARCHAR2, x_retcode OUT VARCHAR2);

PROCEDURE trim_data;

PROCEDURE prevalidation;

PROCEDURE load_cust_contact(p_first_name VARCHAR2,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2,
p_party_id OUT NUMBER);

PROCEDURE load_cust_cont_name;

procedure CREATE_ORG_CONTACT_PERSON (P_CUST_PARTY_ID NUMBER,P_PERSON_PARTY_ID NUMBER,p_err_flag out varchar2,
p_err_msg out varchar2,P_ORG_PARTY_ID out NUMBER);

PROCEDURE create_cust_acct_role (
p_org_party_id NUMBER,
p_cust_account_id NUMBER,
p_cust_acct_site_id NUMBER,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2,
p_party_id OUT NUMBER
);

PROCEDURE cust_role_assign(p_role_id number,p_return_status out varchar2, p_err_cnt out number,p_err_msg out varchar2);

procedure CREATE_CONTACT_POINT ( p_email_address varchar2,
p_party_id number,
p_err_flag out varchar2,
p_err_msg out varchar2
);

procedure printlog (p_text varchar2);
procedure debuglog (p_text varchar2);
procedure printout (p_text varchar2);

PROCEDURE record_status;

END XX_CUST_CONT_PKG;
/

CREATE OR REPLACE PACKAGE BODY XX_cust_cont_pkg
IS
gc_status VARCHAR2 (1);
g_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
gn_bulk_limit NUMBER;
gc_ret_status VARCHAR2 (1);
gn_bulk_err NUMBER;
gn_conc_req_id CONSTANT VARCHAR2 (10) := fnd_global.conc_request_id;

-- +====================================================================================+
-- |
-- |
-- +====================================================================================+
-- | |
-- | $Id: XX_CUST_CONT_PKG 12/06/2018 |
-- | |
-- | |
-- |Description : Program to update customers email address |
-- | |
-- | |
-- | |
-- |Change History: |
-- |--------------- |
-- |Version Date Author Remarks |
-- |------- ---------- ------------ ------------------- |
-- | 1.0 10/12/2018 Mohan G Program to update customer's email address
-- +====================================================================================+
--
PROCEDURE main (x_errbuf OUT VARCHAR2, x_retcode OUT VARCHAR2)
IS
BEGIN
fnd_file.put_line
(apps.fnd_file.LOG,
'###########################################################################################'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');
fnd_file.put_line (apps.fnd_file.LOG,
' ----- Main Program Started -----'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');
XX_cust_cont_pkg.trim_data;
XX_cust_cont_pkg.prevalidation;
XX_cust_cont_pkg.load_cust_cont_name;
-- XX_CUST_CONT_PKG.load_cust_cont;
fnd_file.put_line (apps.fnd_file.LOG, ' ');
fnd_file.put_line (apps.fnd_file.LOG,
' ----- Main Program Completed -----'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');

INSERT INTO XX_cust_cont_stg_arc
SELECT *
FROM XX_cust_cont_stg
WHERE NVL (process_flag, '~') <> 'N';

DELETE FROM XX_cust_cont_stg
WHERE NVL (process_flag, '~') <> 'N';
XX_cust_cont_pkg.record_status;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Exception Main Program ');
fnd_file.put_line (fnd_file.LOG,
'Error info for main program: ' || SQLERRM
);
END main;

PROCEDURE load_cust_cont_name
AS
l_cust_number VARCHAR2 (200) := NULL;
pe_err_flag VARCHAR2(20) := NULL;
pe_err_msg varchar2(4000) := NULL;
pe_party_id NUMBER;
l_party_id NUMBER := -1;
l_err_flag VARCHAR2 (5) := NULL;
l_err_msg VARCHAR2 (2000) := NULL;
l_org_party_id NUMBER := NULL;
l_cust_acct_id NUMBER := -1;
l_cust_acct_site_id number := -1;
l_acct_role_party_id NUMBER;
p_return_status VARCHAR(20) := NULL;
p_err_msg VARCHAR2(2000) := NULL;
p_err_cnt NUMBER := 0;
lo_err_flag VARCHAR2(5) := NULL;
lo_err_msg VARCHAR2 (2000) := NULL;
lr_err_flag VARCHAR2(5) := NULL;
lr_err_msg VARCHAR2 (2000) := NULL;
l_email_address VARCHAR2(2000) := NULL;
ln_hdr_cnt number := 0;
ln_line_cnt number := 0;
ln_email_cnt number := 0;

CURSOR cust_info
IS
SELECT customer_number, contact_name,contact_email, site_number
FROM XX_cust_cont_stg
WHERE process_flag = 'V'
GROUP BY customer_number, contact_name,contact_email, site_number
ORDER BY customer_number;

BEGIN
FOR lc_rec IN cust_info LOOP
BEGIN
ln_hdr_cnt := ln_hdr_cnt+1;
-- fnd_file.put_line (fnd_file.LOG, 'Header Count '||ln_hdr_cnt);
load_cust_contact(lc_rec.contact_name,pe_err_flag,pe_err_msg,pe_party_id);
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while creating the person : ' || lc_rec.customer_number);
END;
fnd_file.put_line(fnd_file.LOG, 'Create person OUT values '||'Error Flag ~ '||pe_err_flag||' Error Message ~'||
pe_err_msg||'Party ID ~'||pe_party_id);
IF pe_err_flag = 'S'
THEN
BEGIN
UPDATE XX_cust_cont_stg
SET person_flag = 'Y'
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the person flag: ' || lc_rec.customer_number);
END;

BEGIN
SELECT party_id
INTO l_party_id
FROM hz_cust_accounts_all
WHERE account_number = lc_rec.customer_number AND status = 'A';
EXCEPTION
WHEN OTHERS
THEN
l_party_id := NULL;
END;

fnd_file.put_line
(fnd_file.LOG,
'-----------------------***************************------------------------'
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Output information ....create_org_contact_person------------------------'
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------***************************------------------------'
);
fnd_file.put_line (fnd_file.LOG,
'Loc-01 '
|| 'l_party_id: '
|| l_party_id
|| 'customer_number '
|| lc_rec.customer_number
|| ' pe_party_id '
|| pe_party_id
);

IF l_party_id IS NULL OR pe_party_id IS NULL
THEN
BEGIN
UPDATE XX_cust_cont_stg
SET assign_flag = 'E', ERROR_MESSAGE = 'Customer Not Exist / Create person API Error'
WHERE customer_number = lc_rec.customer_number;
END;

fnd_file.put_line (fnd_file.LOG,
'Loc-02 '
|| 'l_party_id: '
|| l_party_id
|| 'customer_number '
|| lc_rec.customer_number
|| ' x_party_id '
|| pe_party_id
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Party Id Is Null----------------'
|| lc_rec.customer_number
);
ELSIF l_party_id IS NOT NULL AND pe_party_id IS NOT NULL
THEN
fnd_file.put_line (fnd_file.LOG,
'Loc-03 '
|| 'l_party_id: '
|| l_party_id
|| 'customer_number '
|| lc_rec.customer_number
|| ' x_party_id '
|| pe_party_id
);

BEGIN
create_org_contact_person (l_party_id,
pe_party_id,
l_err_flag,
l_err_msg,
l_org_party_id
);

EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Loc-04 '
|| 'l_party_id: '
|| l_party_id
|| 'customer_number '
|| lc_rec.customer_number
|| ' x_party_id '
|| pe_party_id
|| 'SQLERRM '
|| SQLERRM
);
END;

fnd_file.put_line (fnd_file.LOG,
'Loc-05 '
|| 'l_party_id: '
|| l_party_id
|| 'l_err_flag '
|| l_err_flag
|| ' l_err_msg '
|| l_err_msg
|| 'l_org_party_id '
|| l_org_party_id
);

--3 PART
IF NVL(l_err_flag,'~') <> 'S' THEN
BEGIN
UPDATE XX_cust_cont_stg
SET assign_flag = 'E', ERROR_MESSAGE = l_err_msg
WHERE customer_number = lc_rec.customer_number;
END;
ELSE
-- FOR lc_rec_lines IN cust_info_lines(lc_rec.customer_number, lc_rec.contact_name) LOOP
fnd_file.put_line (fnd_file.LOG, 'Line Loop Started ');
ln_line_cnt := ln_line_cnt+1;
-- fnd_file.put_line (fnd_file.LOG, 'Line Count '||ln_line_cnt);

BEGIN
UPDATE XX_cust_cont_stg
SET assign_flag = 'Y'
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the assign flag: ' || lc_rec.customer_number);
END;
BEGIN
SELECT CUST_ACCOUNT_ID INTO l_cust_acct_id
from hz_cust_accounts_all
where status = 'A'
and account_number = lc_rec.customer_number;
exception when others then
fnd_file.put_line (fnd_file.LOG,
'Loc-06 '|| 'invalid cust account '||lc_rec.customer_number);
END;

BEGIN

select
hca.cust_acct_site_id
INTO l_cust_acct_site_id
from
apps.hz_cust_accounts_all a,
apps.hz_parties hp,
apps.hz_cust_acct_sites_all hca,
apps.hz_cust_site_uses_all csu,
apps.hz_party_sites hps
where hp.party_id=a.party_id
and hca.CUST_ACCOUNT_ID=a.CUST_ACCOUNT_ID
AND hp.party_id = hps.party_id
AND hca.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND csu.CUST_ACCT_SITE_ID = hca.CUST_ACCT_SITE_ID
and csu.SITE_USE_CODE='BILL_TO'
and hps.status='A'
and csu.status='A'
and hca.status='A'
and a.status='A'
and hps.party_site_number= lc_rec.site_number
and a.account_number = lc_rec.customer_number;
fnd_file.put_line(fnd_file.LOG,'ACCOUNT SITE ID '||l_cust_acct_site_id||'cust no '||lc_rec.customer_number||'site number '||lc_rec.site_number);
EXCEPTION WHEN OTHERS THEN
l_cust_acct_site_id := NULL;
fnd_file.put_line(fnd_file.LOG,'Invalid customer account site id ' ||lc_rec.customer_number||'-'||lc_rec.site_number);
UPDATE XX_cust_cont_stg
SET error_message = 'Invalid Account Site Id'
WHERE customer_number = lc_rec.customer_number
and site_number = lc_rec.site_number;
END;
fnd_file.put_line
(fnd_file.LOG,'log100 ' ||l_org_party_id||'-'||l_cust_acct_id||'-'||l_cust_acct_site_id||'-'||l_err_flag||'-'||l_err_msg||'-'||l_acct_role_party_id);
BEGIN
create_cust_acct_role(l_org_party_id,l_cust_acct_id,l_cust_acct_site_id, lo_err_flag,lo_err_msg,l_acct_role_party_id);
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line
(fnd_file.LOG,'Error while calling the create_cust_acct_role'||sqlerrm);
END;
fnd_file.put_line
(fnd_file.LOG,'log110 ' ||lo_err_flag||lo_err_msg||l_acct_role_party_id);
l_email_address := NULL;
-- l_email_address := lc_rec_lines.contact_email;
-- fnd_file.put_line
-- (fnd_file.LOG,'email address and site number ' ||lc_rec_lines.site_number ||' ~ '|| lc_rec_lines.contact_email);
-- END LOOP;
-- END IF;

IF NVL(lo_err_flag,'~') <> 'S' THEN
BEGIN
UPDATE XX_cust_cont_stg
SET role_flag = 'E',ERROR_MESSAGE = lo_err_msg
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the person flag: ' || lc_rec.customer_number);
END;
ELSE
BEGIN
UPDATE XX_cust_cont_stg
SET role_flag = 'Y'
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the person flag: ' || lc_rec.customer_number);
END;
BEGIN
fnd_file.put_line
(fnd_file.LOG,'log111' ||l_acct_role_party_id);
cust_role_assign(l_acct_role_party_id ,p_return_status ,p_err_cnt, p_err_msg );
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line
(fnd_file.LOG,'Error while calling the cust_role_assign'||sqlerrm);
END;
-- END IF;
fnd_file.put_line
(fnd_file.LOG,' log125 p_return_status'|| p_return_status||'-'||'p_err_cnt'||p_err_cnt||'-'||'p_err_msg'||p_err_msg);

IF NVL(p_return_status,'~') <> 'S' THEN
BEGIN
UPDATE XX_cust_cont_stg
SET conpoint_flag = 'E',ERROR_MESSAGE = p_err_msg
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the person flag: ' || lc_rec.customer_number);
END;
ELSE

BEGIN
UPDATE XX_cust_cont_stg
SET conpoint_flag = 'Y'
WHERE customer_number = lc_rec.customer_number;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error while updating the person flag: ' || lc_rec.customer_number);
END;
fnd_file.put_line
(fnd_file.LOG,'log-1000 ' ||lc_rec.site_number||'-'||lc_rec.customer_number);
BEGIN
ln_email_cnt := ln_email_cnt+1;
fnd_file.put_line
(fnd_file.LOG,'log-1001 ' ||l_org_party_id||'-'||lc_rec.contact_email);

create_contact_point(lc_rec.contact_email ,l_org_party_id,lr_err_flag, lr_err_msg); -- l_org_party_id
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line
(fnd_file.LOG,'Error while calling the create_contact_point'||sqlerrm);
END;
-- END LOOP;

END IF;
END IF;
-- END LOOP; --- line cursor loop
END IF;
END IF;
ELSE
BEGIN
UPDATE XX_cust_cont_stg
SET process_flag = 'E'
WHERE customer_number = lc_rec.customer_number;
END;

-- END LOOP;
END IF;

END LOOP;
END load_cust_cont_name;

------------------------------------------------------------------
-- Procedure to create customer contact
----------------------------------------------------------------

PROCEDURE load_cust_contact(
p_first_name VARCHAR2,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2,
p_party_id OUT NUMBER)
AS
p_person_rec hz_party_v2pub.person_rec_type;
x_cust_account_id NUMBER;
x_account_number VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
lv_msg_data VARCHAR2(4000);
lv_msg_index_out NUMBER;

BEGIN
fnd_file.put_line (apps.fnd_file.LOG,
' ----- load_cust_cont_name Program Started -----'
);
fnd_global.apps_initialize (gn_user_id,
gn_responsibility_id,
gn_respappl_id
);

BEGIN
mo_global.set_policy_context ('S', g_org_id);
END;
p_person_rec.person_first_name := p_first_name;
-- p_person_rec.person_last_name := lc_rec.contact_name;
p_person_rec.created_by_module := 'TCA_V1_API';
hz_party_v2pub.create_person ('T',
p_person_rec,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------***************************------------------------'
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Output information ....create_person------------------------'
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------***************************------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'x_party_id: ' || x_party_id);
fnd_file.put_line (fnd_file.LOG, 'x_party_number:' || x_party_number);
fnd_file.put_line (fnd_file.LOG, 'x_profile_id: ' || x_profile_id);
fnd_file.put_line (fnd_file.LOG,'x_return_status:' || x_return_status);
fnd_file.put_line (fnd_file.LOG, 'x_msg_count: ' || x_msg_count);
fnd_file.put_line (fnd_file.LOG, 'x_msg_data: ' || x_msg_data);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------***************************------------------------'
);

IF x_return_status <> 'S' THEN

IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => lv_msg_data,
p_msg_index_out => lv_msg_index_out
);

END LOOP;
p_err_flag := 'IE';
p_err_msg := p_err_msg||' ~ '|| lv_msg_data;
END IF;
ELSIF x_return_status = 'S' THEN
p_party_id := x_party_id;
p_err_flag := 'S';
END IF;
END load_cust_contact;

-------------------------------------------------------------------
-- Procedure to Validate and Create Customer contact point
-------------------------------------------------------------------
PROCEDURE create_contact_point (
p_email_address VARCHAR2,
p_party_id NUMBER,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
IS
/************************************************************
*PURPOSE: To create a Contact Point for the contact person *
*AUTHOR: Mohan Ganesan *
************************************************************/
lv_return_status VARCHAR2 (500);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (500);
lv_api_message VARCHAR2 (4000);
lv_msg_index_out NUMBER;
lv_api_name VARCHAR2 (150);
lv_table_name VARCHAR (150);
lv_contact_point_id NUMBER;
lv_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
lv_email_rec hz_contact_point_v2pub.email_rec_type;
BEGIN
lv_contact_point_rec.contact_point_type := 'EMAIL';
lv_contact_point_rec.created_by_module := 'HZ_CPUI';
lv_contact_point_rec.status := 'A';
lv_email_rec.email_format := 'MAILHTML';
lv_email_rec.email_address := p_email_address;
lv_contact_point_rec.owner_table_name := 'HZ_PARTIES';
lv_contact_point_rec.owner_table_id := p_party_id;
--<< This is the lv_party_id value generated from the Step 2>>
mo_global.init ('AR');
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => fnd_api.g_true,
p_contact_point_rec => lv_contact_point_rec,
p_email_rec => lv_email_rec,
x_contact_point_id => lv_contact_point_id,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data
);

--
--Capturing error if not success
--
IF lv_return_status <> fnd_api.g_ret_sts_success
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => lv_msg_data,
p_msg_index_out => lv_msg_index_out
);
lv_api_message := lv_api_message || ' ~ ' || lv_msg_data;

fnd_file.put_line
(fnd_file.LOG,'Error:' || lv_api_message);
END LOOP;
p_err_flag := 'IE';
p_err_msg := lv_api_message;
ELSIF (lv_return_status = fnd_api.g_ret_sts_success)
THEN
fnd_file.put_line
(fnd_file.LOG,'***************************');
fnd_file.put_line
(fnd_file.LOG,'Output information ....');
fnd_file.put_line
(fnd_file.LOG,'Success');
fnd_file.put_line
(fnd_file.LOG,'lv_contact_point_id: ' || lv_contact_point_id);
fnd_file.put_line
(fnd_file.LOG,'***************************');
END IF;

END create_contact_point;

-------------------------------------------
-- Procedure to do the total Validation
-------------------------------------------
PROCEDURE prevalidation
IS
lc_verify_flag VARCHAR2 (1) := NULL;
l_error_message VARCHAR2 (4000) := NULL;
l_cnt NUMBER := 0;
ln_cust_acct_id NUMBER := 0;
ln_party_id NUMBER := 0;
ln_party_site_id NUMBER := 0;
lc_item_cnt VARCHAR2 (20) := NULL;

CURSOR lcu_cont_stg
IS
SELECT customer_number, site_number, contact_name, contact_email,
record_id
FROM XX_cust_cont_stg
WHERE process_flag = 'N'
ORDER BY customer_number;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
fnd_file.put_line
(fnd_file.LOG,
'--------------- Entering into Prevalidation -----------'
);

---------------------------
--Validating the Customer details
---------------------------
FOR lc_rec IN lcu_cont_stg
LOOP
lc_verify_flag := 'Y';
l_error_message := NULL;

BEGIN
SELECT cust_account_id, party_id
INTO ln_cust_acct_id, ln_party_id
FROM hz_cust_accounts
WHERE account_number = lc_rec.customer_number AND status = 'A';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_cust_acct_id := 0;
l_error_message :=
l_error_message
|| ' ~ Customer is not exist '
;
lc_verify_flag := 'N';
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Customer error' || '~' || SQLERRM;
END;

----------------------------------------
-- Validating the Site details
---------------------------------------
BEGIN
IF ln_party_id IS NOT NULL
THEN
SELECT party_site_id
INTO ln_party_site_id
FROM hz_party_sites
WHERE party_id = ln_party_id
AND party_site_number = lc_rec.site_number
AND STATUS = 'A';
END IF;

ln_party_site_id := NULL;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Multiple party site id for same customer '
|| '~'
|| lc_rec.customer_number;

WHEN NO_DATA_FOUND THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ party site validation error '
|| '~'
|| lc_rec.customer_number
|| ' ~ '
||lc_rec.site_number
||' ~ '
|| 'Site is Not Available. ';
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ party site validation error '
|| '~'
|| lc_rec.customer_number
|| ' ~ '
|| SQLERRM;
END;
fnd_file.put_line (fnd_file.LOG,'verify flag '||lc_verify_flag);
IF lc_verify_flag = 'N'
THEN
UPDATE XX_cust_cont_stg
SET error_message = l_error_message,
process_flag = 'VE' -- Validation Error
WHERE record_id = lc_rec.record_id
AND process_flag = 'N'
AND request_id = gn_conc_req_id
AND customer_number = lc_rec.customer_number;

fnd_file.put_line (fnd_file.LOG,
' Validation error records --> '
|| '~'
|| lc_rec.customer_number
);
ELSE
UPDATE XX_cust_cont_stg
SET error_message = l_error_message,
process_flag = 'V' -- Validated
WHERE record_id = lc_rec.record_id
AND process_flag = 'N'
AND request_id = gn_conc_req_id
AND customer_number = lc_rec.customer_number;

fnd_file.put_line
(fnd_file.LOG,
'Validation Success records customer number --> '
|| lc_rec.customer_number
);
END IF;
END LOOP;

fnd_file.put_line (fnd_file.LOG,
'--------------- Prevalidation ended -----------'
);
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);

-------------------------------------------------
-- Updating the Dependent customer Records
-- if any 1 record has Validation error Mark all
-- depedent records as Error
-------------------------------------------------
UPDATE XX_cust_cont_stg stg
SET error_message = 'DEPENDENCY ERROR: Customer Validations Failed ',
process_flag = 'VE' -- Validation Error
WHERE NVL (process_flag, 'X') <> 'VE'
AND request_id = gn_conc_req_id
AND EXISTS (
SELECT '1'
FROM XX_cust_cont_stg stg1
WHERE 1 = 1
AND stg1.request_id = stg.request_id
AND NVL (stg1.process_flag, 'X') = 'VE'
AND stg.customer_number = stg1.customer_number);
END prevalidation;

PROCEDURE trim_data
IS
BEGIN
--------------------------------------
-- Update to remove unwanted spaces
-------------------------------------
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
fnd_file.put_line (fnd_file.LOG,
'--------------- Entering Trim Data-----------'
);

UPDATE XX_cust_cont_stg
SET customer_number = LTRIM (RTRIM (customer_number)),
site_number = LTRIM (RTRIM (site_number)),
contact_name = LTRIM (RTRIM (contact_name)),
contact_email = LTRIM (RTRIM (contact_email)),
-- cust_account_id = (SELECT cust_account_id
-- FROM hz_cust_accounts
-- WHERE ACCOUNT_NUMBER = LTRIM (RTRIM (customer_number))),
-- corr_cust_acct_site_id = (SELECT hcasa.cust_acct_site_id
-- FROM hz_cust_acct_sites_all hcasa,
-- hz_cust_site_uses_all hcsua
-- WHERE 1 = 1
-- AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
-- AND hcasa.cust_account_id= (SELECT cust_account_id
-- FROM hz_cust_accounts
-- WHERE ACCOUNT_NUMBER = LTRIM (RTRIM (customer_number))
-- AND hcsua.site_use_code = 'CORR')),
request_id = gn_conc_req_id,
created_by = gn_user_id,
last_updated_by = gn_user_id,
record_id = ROWNUM,
CREATION_DATE = sysdate,
LAST_UPDATE_DATE = SYSDATE
WHERE NVL (process_flag, 'N') = 'N';

fnd_file.put_line (fnd_file.LOG,
'--------------- Trim Data Completed -----------'
);
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error Updating the NEW RECORDS - TRIM DATA : '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END trim_data;

PROCEDURE record_status
AS
l_total_cnt NUMBER;
l_bsa_success_cnt NUMBER;
l_bsa_error_cnt NUMBER;
l_order_num VARCHAR2 (20);
lc_hdr_message VARCHAR2 (255) := NULL;
lc_err_cnt NUMBER := 0;

CURSOR lcu_err_rec
IS
SELECT DISTINCT customer_number, site_number, error_message,
request_id
FROM XX_cust_cont_stg_arc
WHERE request_id = gn_conc_req_id
AND (nvl(PERSON_FLAG,'~') <> 'Y'
or NVL(ASSIGN_FLAG,'~') <> 'Y'
OR NVL(ROLE_FLAG,'~') <> 'Y'
OR NVL(CONPOINT_FLAG,'~') <> 'Y');

CURSOR lcu_succ_rec
IS
SELECT DISTINCT request_id,
RPAD (SUBSTR (customer_number, 1, 38),
40
)
|| RPAD (SUBSTR (site_number, 1, 38), 40)
|| RPAD (SUBSTR (contact_name, 1, 38), 40)
|| RPAD (DECODE (NVL (process_flag, 'X'),
'S', 'Success ',
'NA', 'NA',
'No'
),
8
) succ_message
FROM XX_cust_cont_stg_arc
WHERE request_id = gn_conc_req_id
AND PERSON_FLAG = 'Y'
AND ASSIGN_FLAG = 'Y'
AND ROLE_FLAG = 'Y'
AND CONPOINT_FLAG = 'Y';
BEGIN
-------------------------------------------------------------
-- UPdating All the Success Records with Process Flag = 'S'
-------------------------------------------------------------
UPDATE XX_cust_cont_stg_arc
SET process_flag = 'S'
WHERE request_id = gn_conc_req_id AND PERSON_FLAG = 'Y'
AND ASSIGN_FLAG = 'Y'
AND ROLE_FLAG = 'Y'
AND CONPOINT_FLAG = 'Y';

SELECT COUNT ( customer_number)
INTO l_total_cnt
FROM XX_cust_cont_stg_arc
WHERE request_id = gn_conc_req_id;

SELECT COUNT ( customer_number)
INTO l_bsa_success_cnt
FROM XX_cust_cont_stg_arc
WHERE request_id = gn_conc_req_id AND PERSON_FLAG = 'Y'
AND ASSIGN_FLAG = 'Y'
AND ROLE_FLAG = 'Y'
AND CONPOINT_FLAG = 'Y';

SELECT COUNT ( customer_number)
INTO l_bsa_error_cnt
FROM XX_cust_cont_stg_arc
WHERE request_id = gn_conc_req_id and (nvl(PERSON_FLAG,'~') <> 'Y'
or NVL(ASSIGN_FLAG,'~') <> 'Y'
OR NVL(ROLE_FLAG,'~') <> 'Y'
OR NVL(CONPOINT_FLAG,'~') <> 'Y');

fnd_file.put_line (fnd_file.output,
'Total Number of records ' || l_total_cnt
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
fnd_file.put_line
(fnd_file.output,
'================ Success Records ==================================='
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
fnd_file.put_line (fnd_file.output,
'Total Number of success records '
|| l_bsa_success_cnt
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
fnd_file.put_line
(fnd_file.output,
'================ Error Records ==================================='
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
fnd_file.put_line (fnd_file.output,
'Total Number of error records '
|| l_bsa_error_cnt
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'================ Error output ======================================='
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
-----------------------------
-- Printing Error Records
-----------------------------
lc_err_cnt := 0;

FOR lcu_rec IN lcu_err_rec
LOOP
lc_err_cnt := lc_err_cnt + 1;

IF lc_err_cnt = 1
THEN
fnd_file.put_line (fnd_file.output,
' customer number '
|| ' site number '
|| ' Error Message '
);
fnd_file.put_line
(fnd_file.output,
'===================================================================='
);
END IF;

fnd_file.put_line (fnd_file.output,
SUBSTR (lcu_rec.customer_number, 1, 18)
|| ' '
||SUBSTR (lcu_rec.site_number, 1, 18)
|| ' '
|| SUBSTR (lcu_rec.error_message, 1, 200)
);
END LOOP;

lc_hdr_message :=
RPAD ('Customer Number ', 40)
|| RPAD (' Site Number ', 25)
|| RPAD (' Contact Name', 25);
-----------------------------
-- Printing Success Records
-----------------------------
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'.........Printing Success / Partial Success Records........'
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, lc_hdr_message);
fnd_file.put_line (fnd_file.output, RPAD (' ', 200, '='));

-- fnd_file.put_line(fnd_file.output,'========================================================================================================');
FOR lcu_succ IN lcu_succ_rec
LOOP
fnd_file.put_line (fnd_file.output, lcu_succ.succ_message);
END LOOP;

fnd_file.put_line (fnd_file.output, RPAD (' ', 200, '='));
-- fnd_file.put_line(fnd_file.output,'========================================================================================================');
END;

PROCEDURE create_org_contact_person (
p_cust_party_id NUMBER,
p_person_party_id NUMBER,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2,
p_org_party_id OUT NUMBER
)
AS
/*****************************************************************************
*PURPOSE: To Establish a relation between the Person Party and the Customer *
*AUTHOR: Mohan Ganesan *
*****************************************************************************/
lv_return_status VARCHAR2 (500);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (500);
lv_api_message VARCHAR2 (4000);
lv_msg_index_out NUMBER;
lv_api_name VARCHAR2 (150);
lv_table_name VARCHAR2 (150);
lv_oc_c_status VARCHAR2 (1);
lv_org_contact_id NUMBER;
lv_party_rel_id NUMBER;
lv_party_id NUMBER;
lv_party_number VARCHAR2 (150);
lv_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
BEGIN
debuglog ('CREATE_ORG_CONTACT_PERSON Proceudre Started');
p_err_flag := 'S';
lv_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
lv_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
lv_org_contact_rec.party_rel_rec.subject_id := p_person_party_id;
--<< this is party id of the contact created in Step 1>>
lv_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
lv_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
lv_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
lv_org_contact_rec.party_rel_rec.object_id := p_cust_party_id;
--<< this is hz_parties.party_id of the Customer (main organization/party)>>
lv_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
lv_org_contact_rec.party_rel_rec.start_date := SYSDATE;
lv_org_contact_rec.created_by_module := 'TCA_V1_API';
hz_party_contact_v2pub.create_org_contact
(p_init_msg_list => fnd_api.g_true,
p_org_contact_rec => lv_org_contact_rec,
x_org_contact_id => lv_org_contact_id,
x_party_rel_id => lv_party_rel_id,
x_party_id => lv_party_id,
x_party_number => lv_party_number,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data
);

--
--Capturing error if not success
--
IF lv_return_status <> fnd_api.g_ret_sts_success
THEN
IF lv_msg_count > 1
THEN
FOR i IN 1 .. lv_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => lv_msg_data,
p_msg_index_out => lv_msg_index_out
);
lv_api_message := lv_api_message || ' ~ ' || lv_msg_data;
printlog
( 'Error creation of Party/Person as Organization contact - CREATE_ORG_CONTACT API:'
|| lv_api_message
);
END LOOP;
ELSE
lv_api_message := lv_msg_data;
printlog
( 'Error creation of Party/Person as Organization contact - CREATE_ORG_CONTACT API:'
|| lv_api_message
);
END IF;

p_err_msg := lv_api_message;
p_err_flag := 'IE';
ELSIF (lv_return_status = fnd_api.g_ret_sts_success)
THEN
p_org_party_id := lv_party_id;
--printlog ( 'Party/Person created as Organization contact was successfully: lv_org_contact_id: ' || lv_org_contact_id);
END IF;

debuglog ('CREATE_ORG_CONTACT_PERSON Proceudre Ended');
EXCEPTION
WHEN OTHERS
THEN
p_err_msg :=
'Error in CREATE_ORG_CONTACT_PERSON Procedure' || SQLERRM;
p_err_flag := 'IE';
printlog (p_err_msg);
END create_org_contact_person;

PROCEDURE create_cust_acct_role (
p_org_party_id NUMBER,
p_cust_account_id NUMBER,
p_cust_acct_site_id NUMBER,
p_err_flag OUT VARCHAR2,
p_err_msg OUT VARCHAR2,
p_party_id OUT NUMBER
)
AS
/***********************************************************************
*PURPOSE: To Create the Contact at Account/Site level of the Customer *
*AUTHOR: Mohan Ganesan *
***********************************************************************/
p_cr_cust_acc_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
lv_api_message VARCHAR2 (4000);
lv_msg_index_out NUMBER;
lv_cust_account_role_id NUMBER;
lv_return_status VARCHAR2 (2000);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (2000);
BEGIN
p_err_flag := 'S';
-- NOTE:
-- must be unique CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID, PARTY_ID,ROLE_TYPE
--
p_cr_cust_acc_role_rec.party_id := p_org_party_id;
--<<this is the value of lv_party_id which gets generated from the Step 2>>
p_cr_cust_acc_role_rec.cust_account_id := p_cust_account_id;
--<<value for hz_cust_accounts_all.cust_account_id of the Organization party>>
p_cr_cust_acc_role_rec.cust_acct_site_id := p_cust_acct_site_id;
--<<To create contact at site level, if not to create contact at customer levl, we need to comment this line>>
-- p_cr_cust_acc_role_rec.primary_flag := 'Y';
p_cr_cust_acc_role_rec.role_type := 'CONTACT';
p_cr_cust_acc_role_rec.created_by_module := 'TCA_V1_API';
--mo_global.init ('AR');
debuglog
('HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE API Started...'
);
hz_cust_account_role_v2pub.create_cust_account_role
('T',
p_cr_cust_acc_role_rec,
lv_cust_account_role_id,
lv_return_status,
lv_msg_count,
lv_msg_data
);
commit;
fnd_file.put_line(fnd_file.LOG,'Inside create_cust_account_role '||lv_cust_account_role_id||'-'||lv_return_status||'-'||lv_msg_count||'-'||lv_msg_data);
IF lv_return_status <> fnd_api.g_ret_sts_success
THEN
IF lv_msg_count > 1
THEN
FOR i IN 1 .. lv_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => lv_msg_data,
p_msg_index_out => lv_msg_index_out
);
lv_api_message := lv_api_message || ' ~ ' || lv_msg_data;
fnd_file.put_line (fnd_file.LOG, 'Error while Assigning the role for a person at Site level:'
|| lv_api_message
);
END LOOP;
ELSE
lv_api_message := lv_msg_data;
fnd_file.put_line (fnd_file.LOG, 'Error while Assigning the role for a person at Site level:'
|| lv_api_message
);
END IF;

p_err_msg := lv_api_message;
p_err_flag := 'IE';
ELSIF (lv_return_status = fnd_api.g_ret_sts_success)
THEN
p_party_id := lv_cust_account_role_id;
-- printlog( 'Successfully Assigned role for a person at Site level ,LV_cust_account_role_id: '|| lv_cust_account_role_id);
END IF;

fnd_file.put_line (fnd_file.LOG,'HZ_CUST_ACCOUNT_ROLE_V2PUB.CREATE_CUST_ACCOUNT_ROLE API Ended...');
EXCEPTION
WHEN OTHERS
THEN
p_err_msg := 'Error in CREATE_CUST_ACCT_ROLE Procedure' || SQLERRM;
p_err_flag := 'IE';
fnd_file.put_line (fnd_file.LOG,p_err_msg);
END create_cust_acct_role;

PROCEDURE cust_role_assign(p_role_id number,p_return_status out varchar2, p_err_cnt out number,p_err_msg out varchar2) AS
p_role_responsibility_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.ROLE_RESPONSIBILITY_REC_TYPE;
x_responsibility_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);

BEGIN

p_role_responsibility_rec.cust_account_role_id := p_role_id; --value for x_cust_account_role_id from step 9>
p_role_responsibility_rec.responsibility_type := 'BILL TRUST EMAIL';
p_role_responsibility_rec.created_by_module := 'TCA_V1_API';

HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility (
'T',
p_role_responsibility_rec,
x_responsibility_id,
x_return_status,
x_msg_count,
x_msg_data
);

p_return_status := x_return_status; -- fnd_api.g_ret_sts_success
p_err_cnt := x_msg_count;
p_err_msg := x_msg_data;

fnd_file.put_line (fnd_file.LOG,'***************************');
fnd_file.put_line (fnd_file.LOG,'x_responsibility_id: '||x_responsibility_id);
fnd_file.put_line (fnd_file.LOG,'x_return_status: '||p_return_status||'fnd_api.g_ret_sts_success'||fnd_api.g_ret_sts_success);
fnd_file.put_line (fnd_file.LOG,'x_msg_count: '||p_err_cnt);
fnd_file.put_line (fnd_file.LOG,'x_msg_data: '||p_err_msg);

IF p_err_cnt >1 THEN
FOR I IN 1..p_err_cnt
LOOP
fnd_file.put_line
(fnd_file.LOG,I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;

END cust_role_assign;

PROCEDURE printlog (p_text VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_text);
--dbms_output.put_line(p_text);
END;

PROCEDURE debuglog (p_text VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_text);
--dbms_output.put_line(p_text);
END;

PROCEDURE printout (p_text VARCHAR2)
AS
BEGIN
fnd_file.put_line (fnd_file.output, p_text);
--dbms_output.put_line(p_text);
END;
END XX_cust_cont_pkg;
/

Wednesday, January 2, 2019

Customer Contact Creation Email Creation and assignment API

1 comment: