Monday 30 September 2019

API to update customer Profile in Oracle Applications

DECLARE       
   p_customer_profile_rec_type   hz_customer_profile_v2pub.customer_profile_rec_type;
   p_cust_account_profile_id              NUMBER;
   p_object_version_number               NUMBER;
   x_return_status                             VARCHAR2 (2000);
   x_msg_count                                NUMBER;
   X_Msg_Data                                Varchar2 (2000);
   g_org_id                                      NUMBER :=Fnd_Profile.Value('ORG_ID');
   G_User_Name                             Varchar2(200):=Fnd_Profile.Value('USERNAME');
   g_resp_name                                varchar2(200):=fnd_profile.value('RESP_NAME');

CURSOR c_data_rtvl --------+=data retrival cursor
Is
  Select Hcp.Object_Version_Number Obj_Ver_No,
    Hcp.Cust_Account_Profile_Id Account_Profile
  FROM Hz_Customer_Profiles Hcp,
    Hz_Parties Hp,
    Hz_Cust_Accounts Hca
  WHERE Hcp.Attribute5   IS NULL
  AND Hca.Party_Id        = Hp.Party_Id
  AND Hca.Cust_Account_Id = Hcp.Cust_Account_Id
  And Hcp.Attribute_Category Like 'ALL_CAT'
  And Hca.Account_Number Like 'C0001%';

CURSOR c_cntx_vlu --------+=context value cursor
Is
  Select Afu.User_Id User_Id,
    Aft.Application_Id Application_Id,
    Aft.Responsibility_Id resp_id
  FROM Applsys.Fnd_User Afu,
    applsys.fnd_responsibility_tl aft
  WHERE Afu.User_Name LIKE g_user_name
  AND aft.responsibility_name LIKE g_resp_name;
Cdr_Rec C_Data_Rtvl%Rowtype;
ccv_rec c_cntx_vlu%ROWTYPE;

BEGIN
-- Setting the Context --
  Open C_Cntx_Vlu;
  Fetch C_Cntx_Vlu Into ccv_rec;
 
   Mo_Global.Init ('AR');
   Fnd_Global.Apps_Initialize (User_Id           => Ccv_Rec.User_Id,
                               Resp_Id           => Ccv_Rec.Application_Id,
                               resp_appl_id      => ccv_rec.resp_id
                              );
   mo_global.set_policy_context ('S', g_org_id);
   fnd_global.set_nls_context ('AMERICAN');
                                                                          -- Initializing the Mandatory API parameters
  For Cdr_Rec In  C_Data_Rtvl
   Loop
   p_customer_profile_rec_type.cust_account_profile_id := Cdr_Rec.Account_Profile; --first value
   P_Customer_Profile_Rec_Type.Credit_Rating :=            Null;
   P_Customer_Profile_Rec_Type.Attribute5 :=                 'S';
   p_object_version_number :=                                         Cdr_Rec.Obj_Ver_No;          --always change this also
   DBMS_OUTPUT.put_line
          ('Calling the API hz_customer_profile_v2pub.update_customer_profile');
   hz_customer_profile_v2pub.update_customer_profile
                (p_init_msg_list                         => fnd_api.g_true,
                 p_customer_profile_rec             => p_customer_profile_rec_type,
                 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 = fnd_api.g_ret_sts_success
   Then
      --COMMIT;
      DBMS_OUTPUT.put_line ('Updation of Customer Profile is Successful ');
      DBMS_OUTPUT.put_line ('Output information ....');
      DBMS_OUTPUT.put_line (   'Object Version Number = '
                            || TO_CHAR (p_object_version_number)
                           );
      DBMS_OUTPUT.put_line (   'CDE STMT DIST = '
                            || p_customer_profile_rec_type.attribute5
                           );

   ELSE
      DBMS_OUTPUT.put_line (   'Updation of Customer Profile got failed:'
                            || x_msg_data
                           );
      ROLLBACK;

      FOR i IN 1 .. x_msg_count
      LOOP
         x_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F');
         DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
      END LOOP;
   End If;
   EXIT WHEN c_data_rtvl%NOTFOUND;-----------look up
end loop;
   DBMS_OUTPUT.put_line ('Completion of API');
END;

1 comment:

  1. Such a nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete