Tuesday 17 September 2019

API to update the customer attributes

DECLARE

p_cust_account_rec        HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_object_version_number   NUMBER;
x_return_status           VARCHAR2(2000);
x_msg_count               NUMBER;
x_msg_data                VARCHAR2(2000);
gn_responsibility_id      NUMBER := apps.fnd_global.resp_id;
gn_respappl_id            NUMBER := apps.fnd_global.resp_appl_id;
gn_user_id                NUMBER := apps.fnd_global.user_id;
g_org_id                  NUMBER := fnd_profile.VALUE ('ORG_ID'); 
l_success_count           NUMBER := 0;
l_failure_count           NUMBER := 0;
l_all_count               NUMBER := 0;
l_cust_acct_id            NUMBER;


CURSOR CUST_ATTR_UPD IS
SELECT     hca.cust_account_id,cau.attribute3,cau.attribute4,hca.object_version_number,hca.account_number
                        FROM HZ_CUST_ACCOUNTS_ALL hca,IRON.CUST_ATTRIBUTE_UPD cau
WHERE hca.account_number=cau.customer_number
AND cau.Status_flag='N';


BEGIN
-- Setting the Context --
INSERT INTO IRON.CUST_ATTRIBUTE_UPD_ADUDIT SELECT * FROM IRON.CUST_ATTRIBUTE_UPD WHERE STATUS_FLAG NOT IN('N');

DELETE FROM IRON.CUST_ATTRIBUTE_UPD WHERE STATUS_FLAG NOT IN('N');
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => gn_user_id
                                            ,resp_id      => gn_responsibility_id
                                            ,resp_appl_id => gn_respappl_id);
mo_global.set_policy_context('S',g_org_id);

 FOR j IN CUST_ATTR_UPD
   LOOP
 
   BEGIN
   SELECT cust_account_id into l_cust_acct_id from HZ_CUST_ACCOUNTS_ALL WHERE       Account_number=j.account_number;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Invalid customer Number'||'-'||j.account_number);
   UPDATE IRON.CUST_ATTRIBUTE_UPD SET STATUS_FLAG='E',ERROR_MESSAGE='Invalid customer number' WHERE CUSTOMER_NUMBER=j.account_number;
   COMMIT;
   END;
-- Initializing the Mandatory API parameters
p_cust_account_rec.cust_account_id                  := j.cust_account_id;
p_object_version_number                             := j.object_version_number;
p_cust_account_rec.attribute3                       := j.attribute3 ;
p_cust_account_rec.attribute4                       := j.attribute4;

--DBMS_OUTPUT.PUT_LINE('Calling the API hz_cust_account_v2pub.update_cust_account');


HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT
                  (
                    p_init_msg_list         => FND_API.G_TRUE,
                    p_cust_account_rec      => p_cust_account_rec,
                    p_object_version_number => p_object_version_number,
                    x_return_status         => x_return_status,
                    x_msg_count             => x_msg_count,
                    x_msg_data              => x_msg_data
                          );
 
IF (x_return_status <> 'S')
     THEN

--DBMS_OUTPUT.PUT_LINE('Object Version: '||l_party_obj_version );

       l_failure_count := l_failure_count + 1;
       fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                                 p_count   => x_msg_count,
                                 p_data    => x_msg_data);

      /*IF x_msg_count = 1
      THEN

        fnd_file.put_line (fnd_file.LOG,'ERROR Updating customer attributes = '||j.customer_number ||';'||j.attribute3||';'||j.attribute4||';'||x_msg_data );
   
      ELSE*/
      DBMS_OUTPUT.PUT_LINE('ERRORS Updating customer attributes');
        FOR i IN 1 .. x_msg_count
    LOOP
       
      x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
          DBMS_OUTPUT.PUT_LINE(j.account_number ||';'||j.attribute3||';'||j.attribute4||';'||x_msg_data );
 
  UPDATE IRON.CUST_ATTRIBUTE_UPD SET STATUS_FLAG='E',ERROR_MESSAGE='x_msg_data' WHERE CUSTOMER_NUMBER=j.account_number;

    END LOOP;
    --  END IF;
    DBMS_OUTPUT.PUT_LINE('Customer attributes has been updated successfully');
     ELSE
        l_success_count := l_success_count + 1;
    DBMS_OUTPUT.PUT_LINE(j.account_number ||';'||j.attribute3||';'||j.attribute4||';'||x_msg_data );
UPDATE IRON.CUST_ATTRIBUTE_UPD SET STATUS_FLAG='S',ERROR_MESSAGE='Succuss' WHERE CUSTOMER_NUMBER=j.account_number;
   IF MOD(l_success_count,100)=0
   THEN
  COMMIT;
   END IF;

     END IF;

  END LOOP;

 DBMS_OUTPUT.PUT_LINE('+--------------------------------------+');
  DBMS_OUTPUT.PUT_LINE('Total Record count : '||L_ALL_COUNT);
  DBMS_OUTPUT.PUT_LINE('Total Success Record Count : '||L_SUCCESS_COUNT);
 DBMS_OUTPUT.PUT_LINE('Total Fail Record Count :'||L_FAILURE_COUNT);
 DBMS_OUTPUT.PUT_LINE('+--------------------------------------+'); 

  COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.PUT_LINE('Error::::'||SQLERRM);
       ROLLBACK;
END ;
/

4 comments:

  1. Very interesting blog Thank you for sharing such a nice and interesting blog and really very helpful article

    Oracle Fusion HCM Online Training

    ReplyDelete
  2. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion HCM Online Training

    ReplyDelete
  3. We are tied directly into the sate’s renewal database which allows us to process your request almost instantly. buy essays tellthebell.com

    ReplyDelete