SELECT * FROM hz_cust_account_roles;
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.party_id := 18392156;
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;
Nice Informative blog and Navya Enterprises is one of the leading companies with over a decade of experience in Duct Area Safety Net in Hyderabad. Duct area nets are best taken in buildings of two blocks apartments.
ReplyDeleteWhat has your government done to help save you from your financial instability? you strive to survive and yet you hear stories of how your leaders have become terror in your entities... is time to make a different. for will have made money, and we have also come to help you out from your long time of financial suffering. clearing of credit card is made available, software for hacking ATM machines, bank to bank hacking and transfer, change your school grade and become something useful in the society. we also have other form of services such as Facebook hack, whats-app hack, twitter hack, i cloud hack, tracking of smart phones, hacking CCTV, installation of software on desktop and PC, snap-chat hack, Skype hack, wire wire, bitcoin account hack, erase your criminal record and be free for ever. database hack and many more. e-mail: cyberhackingcompany@gmail.com for your genuine hacking services and we shock we your findings.
DeleteYour blog is a beacon of guidance for students seeking Assignment Help in UK.
ReplyDeleteSuperb blog! Both the material and your analytic point of view are fascinating. For Australian students having trouble working on their papers, Best 7 Online Assignment Help in Australia is a fantastic resource! Outstanding assistance with academic accomplishment
ReplyDeleteTrying my luck on Lemon Book has been an exhilarating ride! The platform's diverse games and exciting bets make every gamble thrilling.
ReplyDelete