Script to Update Supplier Site Payment Method, Remittance Advice Delivery Method and Remittance Advice Email Values from Backend

select

   ass.org_id

   ,epa.ext_payee_id

   ,epa.payee_party_id

   ,epa.supplier_site_id

   ,epa.party_site_id payee_party_site_id

   -- ,epa.remit_advice_delivery_method

   -- ,epa.remit_advice_email

   -- ,aps.vendor_id

   -- ,aps.segment1

   -- ,aps.vendor_name

   -- ,ass.vendor_site_code

   -- ,epa.default_payment_method_code

   -- ,epa.*

from

  ap_suppliers aps

  ,ap_supplier_sites_all ass

  ,iby_external_payees_all epa

where 1=1

  and aps.vendor_id = ass.vendor_id

  and ass.vendor_site_id = epa.supplier_site_id

  and aps.segment1 = 11411

order by 1,2,3 desc;

DECLARE

   x_return_status             VARCHAR2 (200) := NULL;

   x_msg_count                 NUMBER := 0;

   x_msg_data                  VARCHAR2 (200) := NULL;

   t_output                    VARCHAR2 (200) := NULL;

   t_msg_dummy                 VARCHAR2 (200) := NULL;

   l_payee_upd_status          iby_disbursement_setup_pub.ext_payee_update_tab_type;

   p_external_payee_tab_type   iby_disbursement_setup_pub.external_payee_tab_type;

   p_ext_payee_id_tab_type     iby_disbursement_setup_pub.ext_payee_id_tab_type;

   i                           NUMBER := 0;

  

BEGIN

   fnd_msg_pub.delete_msg (NULL);

   fnd_msg_pub.initialize;

 FOR rec IN

 (

  select

    ass.org_id

    ,epa.ext_payee_id

    ,epa.payee_party_id

    ,epa.supplier_site_id

    ,epa.party_site_id payee_party_site_id

    ,epa.default_payment_method_code default_pmt_method

  from

    ap_suppliers aps

    ,ap_supplier_sites_all ass

    ,iby_external_payees_all epa

  where 1=1

    and aps.vendor_id = ass.vendor_id

    and ass.vendor_site_id = epa.supplier_site_id

    and aps.segment1 = 11411

  order by 1,2,3 desc

 )

 LOOP

   apps.fnd_global.apps_initialize (1291,50977,200);

   mo_global.set_policy_context ('S', rec.org_id);

  

   i := i + 1;

   p_external_payee_tab_type (i).Remit_advice_delivery_method := 'EMAIL';

   p_external_payee_tab_type (i).Remit_advice_email := 'OracleApps88@Yahoo.com';

   p_external_payee_tab_type (i).default_pmt_method := rec.default_pmt_method;

   p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';

   p_external_payee_tab_type (i).exclusive_pay_flag := 'N';

   p_external_payee_tab_type (i).payer_org_type := 'OPERATING_UNIT';

   p_external_payee_tab_type (i).payer_org_id := rec.org_id;

   p_ext_payee_id_tab_type (i).ext_payee_id := rec.ext_payee_id;

   p_external_payee_tab_type (i).payee_party_id := rec.payee_party_id;

   p_external_payee_tab_type (i).supplier_site_id := rec.supplier_site_id;

   p_external_payee_tab_type (i).Payee_Party_Site_Id := rec.Payee_Party_Site_Id;

     

   IBY_DISBURSEMENT_SETUP_PUB.UPDATE_EXTERNAL_PAYEE

                          (p_api_version            => 1.0,

                           p_init_msg_list          => 'T',

                           p_ext_payee_tab          => p_external_payee_tab_type,

                           p_ext_payee_id_tab       => p_ext_payee_id_tab_type,

                           x_return_status          => x_return_status,

                           x_msg_count              => x_msg_count,

                           x_msg_data               => x_msg_data,

                           x_ext_payee_status_tab   => l_payee_upd_status);

                          

   DBMS_OUTPUT.put_line ('Return Status : ' || x_return_status);

   DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

   IF x_return_status <> 'S'

   THEN

      IF x_msg_count > 0

      THEN

         FOR i IN 1 .. x_msg_count

         LOOP

            fnd_msg_pub.get (i,fnd_api.g_false,x_msg_data,t_msg_dummy);

            DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

            t_output := (TO_CHAR (i) || ': ' || x_msg_data);

         END LOOP;

      END IF;

      DBMS_OUTPUT.put_line ( 'Error occured while updating the Payment Details' || t_output);

   ELSE

      DBMS_OUTPUT.put_line ( 'Upade Suceccfully');

      COMMIT;

   END IF;

   FOR j IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST

   LOOP

      DBMS_OUTPUT.put_line ('Error Message from table type : '|| l_payee_upd_status (j).payee_update_msg);

   END LOOP;

 END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error ' || SQLERRM);

END;