Customer Bill To Role update API

 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;