Wednesday, March 30, 2016

Assign Receipt method to Customer via API in Oracle Apps R12 (hz_payment_method_pub.create_payment_method)





In this post, we have given a sample script to assign a payment method to Customer account in R12.


Test instance: R12


Script: 


SET SERVEROUTPUT ON;


DECLARE

  vl_payment_method_name          VARCHAR2(50) := 'AP/AR Netting';

  vl_cust_orig_sys_reference      VARCHAR2(50) := '17312';

  vl_site_use_orig_sys_reference  VARCHAR2(50) := '34914';


  vl_pay_method_rec         hz_payment_method_pub.payment_method_rec_type;

 

  vl_method_id_num          NUMBER;

  vl_acc_id_num             NUMBER;

  vl_party_id_num           NUMBER;

  vl_site_id_num            NUMBER;

  vg_status_txt             VARCHAR2 (1);

  vg_msg_cnt_num            NUMBER;

  vg_msg_data_txt           VARCHAR2 (2000);

  vl_cust_receipt_method_id NUMBER;

  lv_cust_rec_met_id        NUMBER;

BEGIN

 -- Apps initialize

  fnd_global.APPS_INITIALIZE (1119, 50937, 222);

  mo_global.init('AR');


  BEGIN

    SELECT receipt_method_id

      INTO vl_method_id_num

      FROM ar_receipt_methods

     WHERE NAME = vl_payment_method_name

       AND SYSDATE BETWEEN (nvl(start_date,SYSDATE - 1))

                       AND (nvl(end_date,SYSDATE + 1));

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Receipt Method ID - ' || SQLERRM);

  END;


  BEGIN

    SELECT hca.cust_account_id,

           hca.party_id

      INTO vl_acc_id_num,

           vl_party_id_num

      FROM hz_cust_accounts hca

     WHERE hca.orig_system_reference = TRIM(vl_cust_orig_sys_reference);

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Cust Account ID - ' || SQLERRM);

  END;


  BEGIN

    SELECT hcsua.site_use_id

      INTO vl_site_id_num

      FROM hz_cust_site_uses_all hcsua

     WHERE hcsua.orig_system_reference = vl_site_use_orig_sys_reference

       AND hcsua.site_use_code IN ('BILL_TO', 'SHIP_TO');

       dbms_output.put_line('vl_site_id_num :'||vl_site_id_num);

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error deriving Site Use ID - ' || SQLERRM);

  END;


  vl_pay_method_rec.cust_account_id   := vl_acc_id_num;

  vl_pay_method_rec.receipt_method_id := vl_method_id_num;

  vl_pay_method_rec.primary_flag      := 'Y';

  vl_pay_method_rec.site_use_id       := vl_site_id_num;

  vl_pay_method_rec.start_date        := SYSDATE;

  vl_pay_method_rec.end_date          := NULL;

 

  hz_payment_method_pub.create_payment_method

               (p_init_msg_list          => fnd_api.g_false,

                p_payment_method_rec     => vl_pay_method_rec,

                x_cust_receipt_method_id => vl_cust_receipt_method_id,

                x_return_status          => vg_status_txt,

                x_msg_count              => vg_msg_cnt_num,

                x_msg_data               => vg_msg_data_txt);

                                             

  dbms_output.put_line('PAYMENT METHOD ');

  dbms_output.put_line('return_status=' || substr(vg_status_txt,1,255));

  dbms_output.put_line('count=' || to_char(vg_msg_cnt_num));

  dbms_output.put_line('Msg_data = ' || substr(vg_msg_data_txt,1,255));


  IF (nvl(vg_status_txt,'X') != fnd_api.g_ret_sts_success)

  THEN

    IF vg_msg_cnt_num >= 1

    THEN

      dbms_output.put_line('Create Payment Methods : ' || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255));

    END IF;

  ELSE

    BEGIN

    SELECT cust_receipt_method_id

      INTO lv_cust_rec_met_id

      FROM ra_cust_receipt_methods

     WHERE customer_id       = vl_acc_id_num

       AND site_use_id       = vl_site_id_num

       AND receipt_method_id = vl_method_id_num;

      

    dbms_output.put_line('Successfully Created the Payment Methods (cust_receipt_method_id) :'||lv_cust_rec_met_id);

    END;

   

    COMMIT;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Unknown error during call to Create Payment Methods : ' ||SQLERRM);

END;

/

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...