Wednesday, 10 June 2015

Supplier's Payment Methods query in oralce apps

Payment_method_lookup_code column of AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL is obsolete in Rel12.

These column values reflect values prior to upgrade. Any updates and new addition go to IBY_EXT_PARTY_PMT_MTHDS table.

            USE_NL (spu ss epa ppm)
     , sup.segment1 vendor_number
     , sup.party_id
     , epa.payee_party_id
     , epa.supplier_site_id
     , ss.vendor_site_code
     , ppm.payment_method_code
     , ppm.primary_flag
  FROM ap_suppliers                sup
     , ap_supplier_sites_all       ss
     , iby.iby_external_payees_all epa
     , iby_ext_party_pmt_mthds     ppm
 WHERE sup.vendor_id = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id --epa.payee_party_id
   AND epa.ext_payee_id  = ppm.ext_pmt_party_id
   AND sup.creation_date BETWEEN SYSDATE - 300
                             AND SYSDATE - 290
   -- Modify above condition or add new conditions to get desired supplier's info
   AND ss.inactive_date    IS NULL
   AND sup.end_date_active IS NULL
   AND ppm.inactive_date   IS NULL
   AND epa.inactive_date   IS NULL

No comments:

Post a Comment