Archive for the ‘Purchasing / Payables’ Category

Payables: Payments by Method by Supplier (& Site)

Tuesday, April 4th, 2017

You can use this SQL statement to review the setup of the supplier payment options by site, and the remittance preference and if specified email address.

This SQL statement could be useful if you ware looking to validate or extract lists of contacts and look for opportunities to streamline processes.

  SELECT COUNT(aca.check_number)            “Number of Payments”,
asa.segment1                       “Supplier Number”,
asa.vendor_name                    “Supplier Name”,
st.vendor_site_code                “Supplier Site Name”,
aca.PAYMENT_METHOD_CODE            “Payment Method (At Payment)”,
ieppm.payment_method_code          “Site Payment Method (Current)”,
iepa.remit_advice_delivery_method  “Site Remit Delivery Method”,
iepa.remit_advice_email            “Site Remit Advice Email”
FROM ap_checks_all aca,
ap_suppliers asa,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds      ieppm,
ap.ap_supplier_sites_all     st
WHERE     1=1
AND aca.vendor_id = asa.vendor_id
and iepa.payee_party_id = asa.party_id
and iepa.supplier_site_id = aca.vendor_site_id
and iepa.ext_payee_id = ieppm.ext_pmt_party_id
AND iepa.supplier_site_id = st.vendor_site_id
–and iepa.supplier_site_id = aca.vendor_site_id
group by asa.segment1, asa.vendor_name, aca.PAYMENT_METHOD_CODE, remit_advice_delivery_method, remit_advice_email, ieppm.payment_method_code, st.vendor_site_code
ORDER BY COUNT(aca.check_number) desc;