Wednesday, August 23, 2017

Unable to Complete Transaction Due to Error: APP-AR-42536: This transaction is associated with at least one invalid or end-dated GL account

Cause
AutoAccounting is set to pick 'Receivable' account from 'Transaction Type' and no receivable account is defined at the transaction type

Solution
If AutoAccounting is set to pick 'Receivable' account from 'Transaction Type' and no receivable account is defined at the transaction type. The Rounding Account cannot be updated and hence transaction cannot be completed. Hence please define receivable account at the transaction type being used.

Workaround:
First update the REC account before entering into the lines screen. When doing this see if the rounding account will populate when the REV record is written to the gl_dist table.


Note: If the user goes into the lines screen without updating the missing REC account then the rounding account will never be updated.

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;



Best Blogger TipsGet Flower Effect