API For Customer Limit Update

 /*Query to find Customer Credit Limit*/

SELECT Z.account_number CUSTOMER_NUMBER,
       Z1.party_name    CUSTOMER_NAME,
       X.credit_checking,
       --X.CUST_ACCOUNT_PROFILE_ID,
       -- Y.CUST_ACCT_PROFILE_AMT_ID,
       Y.overall_credit_limit
FROM   hz_customer_profiles X,
       hz_cust_profile_amts Y,
       hz_cust_accounts Z,
       hz_parties Z1
WHERE  X.cust_account_id = Y.cust_account_id(+)
       AND X.cust_account_id = Z.cust_account_id
       AND Z.party_id = Z1.party_id
       AND X.status = 'A'
       AND X.site_use_id IS NULL
       AND X.cust_account_profile_id = Y.cust_account_profile_id(+); 

/*API For Customer Limit Update in Apps R12*/

CREATE TABLE xx_cust_credit_limit_upld_temp
             (
                          account_number       VARCHAR2(30 byte),
                          party_name           VARCHAR2(360 byte),
                          overall_credit_limit NUMBER
             );

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);
    l_user_id                 NUMBER;
    l_resp_id                 NUMBER;
    l_respid_id               NUMBER;
    in_out_version_no         NUMBER;
    in_out_version_no_hcpa    NUMBER;
    v_customer_profile_amt
    hz_customer_profile_v2pub.cust_profile_amt_rec_type;
    v_cust_act_prof_amt_id    NUMBER;
    v_cust_account_profile_id NUMBER;
    v_return_status           VARCHAR2(2000);
    v_msg_count               NUMBER;
    v_msg_data                VARCHAR2(2000);
    p_create_profile_amt      VARCHAR2(2000);
    v_msg_dummy               VARCHAR2(5000);
    t_output                  VARCHAR2(5000);
    mtrx                      NUMBER;
    moverall                  NUMBER;
BEGIN
    FOR i IN (SELECT hcp.cust_account_profile_id profile_id,
                     hca.cust_account_id,
                     hca.account_number          customer_number,
                     hp.party_name               customer_name,
                     xx.party_name,
                     hcp.credit_checking,
                     hcpa.overall_credit_limit,
                     hcpa.trx_credit_limit       order_credit_limit,
                     hcpa.cust_acct_profile_amt_id,
                     xx.overall_credit_limit     crdt_lmt2upld
              FROM   hz_customer_profiles hcp,
                     hz_cust_profile_amts hcpa,
                     hz_cust_accounts hca,
                     hz_parties hp,
                     xx_cust_credit_limit_upld_temp xx
              WHERE  hcp.cust_account_id = hcpa.cust_account_id (+)
                     AND hcp.cust_account_id = hca.cust_account_id
                     AND hca.party_id = hp.party_id
                     AND hcp.status = 'A'
                     AND hcp.site_use_id IS NULL
                     AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id (+)
                     AND hca.account_number = xx.account_number
                     --and hp.party_name = xx.PARTY_NAME
             ) LOOP
        p_customer_profile_rec_type.cust_account_profile_id := i.profile_id;

        dbms_output.Put_line('profile_id = ' || i.profile_id);
        mo_global.Init('AR');
        mo_global.Set_policy_context('S', '103');
        l_user_id := 2739;
        l_resp_id := 53313;
        l_respid_id := 660;
        fnd_global.Apps_initialize(l_user_id, l_resp_id, l_respid_id);

        --dbms_output.put_line('Credit Limit = '|| mtrx|| ' '|| moverall);
        dbms_output.Put_line('CUST_ACCT_PROFILE_AMT_ID = '|| i.cust_acct_profile_amt_id);

        v_customer_profile_amt.cust_account_profile_id := i.profile_id;

        v_customer_profile_amt.cust_acct_profile_amt_id :=
        i.cust_acct_profile_amt_id;

        v_customer_profile_amt.cust_account_id := i.cust_account_id;

        --v_customer_profile_amt.site_use_id := i.site_use_id;
        --v_customer_profile_amt.currency_code := 'BHD';
        --v_customer_profile_amt.trx_credit_limit := mtrx;
        v_customer_profile_amt.overall_credit_limit := i.crdt_lmt2upld;

        SELECT hcpa.object_version_number
        INTO   in_out_version_no_hcpa
        FROM   hz_customer_profiles hcp,
               hz_cust_profile_amts hcpa
        WHERE  hcp.cust_account_profile_id = i.profile_id
               AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id;

        dbms_output.Put_line('Profile amt version = '|| in_out_version_no_hcpa);

        p_object_version_number := in_out_version_no_hcpa;

        hz_customer_profile_v2pub.Update_cust_profile_amt (
        p_init_msg_list => 'T',
        p_cust_profile_amt_rec => v_customer_profile_amt,
        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);

        dbms_output.Put_line('x_return_status = '
                             || Substr(x_return_status, 1, 255));

        dbms_output.Put_line('Object Version Number = '
                             || To_char(p_object_version_number));

        dbms_output.Put_line('Credit Rating = '
                             || p_customer_profile_rec_type.credit_rating);

        dbms_output.Put_line('x_msg_count = '
                             || To_char(x_msg_count));

        dbms_output.Put_line('x_msg_data = '
                             || Substr(x_msg_data, 1, 255));

        IF x_msg_count > 1 THEN
          FOR i IN 1..x_msg_count LOOP
              dbms_output.Put_line(i
                                   || '.'
                                   || Substr(fnd_msg_pub.Get(p_encoded =>
                                      fnd_api.g_false), 1,
                                      255));
          END LOOP;
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Error: '
                           || SQLERRM);
END;