Monday, December 11, 2017

Query To Get Payment Method for Supplier and Supplier Sites

This information is stored in IBY_EXT_PARTY_PMT_MTHDS which joins to IBY_EXTERNAL_PAYEES_ALL by ...
IBY_EXT_PARTY_PMT_MTHDS.ext_pmt_party_id = IBY_EXTERNAL_PAYEES_ALL.ext_payee_id.

This table holds any payment method that has been the default for the site. The current default
will have IBY_EXT_PARTY_PMT_MTHDS.Primary_Flag = 'Y'.

You could use these queries to see the rows for a given supplier site:

This one will show the default payment method (if any) at the Site level:
  SELECT SUP.vendor_name SUPPLIER,
         SITE.vendor_site_code SITE,
         SITE.vendor_site_id,
         SITE.org_id,
         IBEPPM.payment_method_code
    FROM ap_suppliers SUP,
         iby_external_payees_all IBEP,
         iby_ext_party_pmt_mthds IBEPPM,
         ap_supplier_sites_all SITE
   WHERE     SUP.party_id = IBEP.payee_party_id
         AND IBEPPM.ext_pmt_party_id = IBEP.ext_payee_id
         AND SITE.vendor_id = SUP.vendor_id
         AND IBEP.supplier_site_id = SITE.vendor_site_id
         AND IBEPPM.primary_flag = 'Y'
         AND SUP.vendor_name = '<Supplier Name>'
ORDER BY SUPPLIER, SITE;


This one will show the default payment method (if any) at the Supplier level:
SELECT SUP.vendor_name SUPPLIER, IBEPPM.payment_method_code
  FROM ap_suppliers SUP,
       iby_external_payees_all IBEP,
       iby_ext_party_pmt_mthds IBEPPM
 WHERE     SUP.party_id = IBEP.payee_party_id
       AND IBEPPM.ext_pmt_party_id = IBEP.ext_payee_id
       AND IBEP.supplier_site_id IS NULL
       AND SUP.vendor_name = '<Supplier Name>';


This will show all Payees associated with a given supplier. The payee associated with a Supplier site will have a value for Supplier_Site_ID
SELECT SUP.vendor_name, PAYEE.*
  FROM iby_external_payees_all PAYEE, ap_suppliers SUP
 WHERE PAYEE.payee_party_id = SUP.party_id

       AND SUP.vendor_name = '<Supplier Name>'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect