Friday, August 18, 2017

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;



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect