set serveroutput on;
DECLARE
l_contact_point_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
l_role_id NUMBER := 0;
l_cust_acct_id NUMBER := 0;
l_obj_num NUMBER := 0;
p_party_id HZ_PARTIES.party_id % TYPE;
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000) := NULL;
x_return_status VARCHAR2(1000) := NULL;
CURSOR C1 IS SELECT ACCOUNT_NUMBER, BILL_TO_SITE, STATUS, UPD_STATUS
fROM xx_bill_to_roll_tbl WHERE NVL(UPD_STATUS,’I’) = ‘I’;
BEGIN
FOR I IN C1 LOOP
SELECT DISTINCT role_acct.CUST_ACCOUNT_ID, acct_role.CUST_ACCOUNT_ROLE_ID
INTO l_cust_acct_id, l_role_id
FROM apps.hz_contact_points cont_point,
apps.hz_cust_account_roles acct_role,
apps.hz_parties party,
apps.hz_parties rel_party,
apps.hz_relationships rel,
apps.hz_org_contacts org_cont,
apps.hz_cust_accounts role_acct,
apps.hz_contact_restrictions cont_res,
apps.hz_person_language per_lang,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcu,
apps.hz_role_responsibility resp,
apps.hz_party_sites hps
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = ‘CONTACT’
AND org_cont.party_relationship_id = rel.relationship_id
AND hcasa.party_site_id = hps.party_site_id
AND rel.subject_id = party.party_id
AND rel_party.party_id = rel.party_id
AND cont_point.owner_table_id(+) = rel_party.party_id
AND cont_point.contact_point_type = ‘EMAIL’
AND cont_point.status=’A’
AND acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.object_id
AND party.party_id = per_lang.party_id(+)
AND per_lang.native_language(+) = ‘Y’
AND party.party_id = cont_res.subject_id(+)
AND cont_res.subject_table(+) = ‘HZ_PARTIES’
AND role_acct.account_number = I.ACCOUNT_NUMBER
AND role_acct.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
AND hcasa.cust_acct_site_id = hcu.cust_acct_site_id
AND resp.cust_account_role_id = acct_role.cust_account_role_id
AND resp.responsibility_type =’BILL TRUST EMAIL’
AND hps.party_site_number = I.BILL_TO_SITE;
select object_version_number
into l_obj_num
from hz_cust_account_roles
where CUST_ACCOUNT_ROLE_ID = l_role_id
and rownum <2;
l_contact_point_rec.CUST_ACCOUNT_ROLE_ID := l_role_id; — Role id from hz_cust_account_roles
l_contact_point_rec.role_type := ‘CONTACT’;
l_contact_point_rec.CUST_ACCOUNT_ID :=l_cust_acct_id; — cust_account_id from hz_cust_accounts
l_contact_point_rec.ORIG_SYSTEM_REFERENCE := l_role_id; — Role id from hz_cust_account_roles
l_contact_point_rec.status := ‘A’;
HZ_CUST_ACCOUNT_ROLE_V2PUB.update_cust_account_role (
p_init_msg_list => FND_API.G_FALSE,
p_cust_account_role_rec => l_contact_point_rec,
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
);
dbms_output.put_line(‘Return Status :’ || x_return_status);
IF x_return_status <> ‘S’
THEN
FOR k in 1 .. x_msg_count loop
x_msg_data := fnd_msg_pub.get
( p_msg_index => k
, p_encoded => ‘F’
) ;
dbms_output.put_line(‘Error:’ || x_msg_data);
END LOOP;
ELSE
UPDATE xx_bill_to_roll_tbl SET UPD_STATUS = ‘A’
WHERE ACCOUNT_NUMBER = I.ACCOUNT_NUMBER
AND BILL_TO_SITE = I.BILL_TO_SITE
AND STATUS = ‘I’;
END IF;
END LOOP;
END;
