Tuesday 1 September 2015

Supplier's Payment Methods query in oracle 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.

/* Formatted on 9/1/2015 11:11:36 AM (QP5 v5.240.12305.39446) */
SELECT /*+  ORDERED
            USE_NL (spu ss epa ppm)
       */
       sup.creation_date,
       sup.vendor_name,
       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
       --    Supplier created by last 100 days
       AND sup.creation_date BETWEEN SYSDATE - 100 AND SYSDATE - 1
       -- 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