This AP Supplier Bank Listing query generates the Supplier Bank Listing Report to view bank information in regards to a supplier. This query will help in identifying supplier sites to their banks and account information.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves supplier bank account assignment details used for disbursements.
HAOU.NAME ORGANIZATION_NAME,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSV.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
PSSV.CITY,
PSSV.ZIP,
IEBAV.BANK_NAME,
IEBAV.BANK_ACCOUNT_NAME,
IEBAV.BANK_ACCOUNT_NUMBER,
IEBAV.CURRENCY_CODE,
IPIUA.ORDER_OF_PREFERENCE PRIMARY_FLAG,
IPIUA.START_DATE PAYMENT_INSTRUMENT_ACTIVE_DATE,
IPIUA.END_DATE PAYMENT_INSTRUMENT_INACTIVE_DATE,
--PSSV.VENDOR_SITE_ID BA_VENDOR_SITE,
IPIUA.CREATION_DATE PAYMENT_INSTRUMENT_CREATION_DATE,
PU.USERNAME PAYMENT_INSTRUMENT_CREATED_BY,
IPIUA.LAST_UPDATE_DATE PAYMENT_INSTRUMENT_UPDATE_DATE,
PU1.USERNAME PAYMENT_INSTRUMENT_UPDATED_BY,
PSSV.VENDOR_SITE_ID SUPPLIER_SITE_ID,
HAOU.ORGANIZATION_ID
FROM
IBY_EXT_BANK_ACCOUNTS_V IEBAV,
IBY_PMT_INSTR_USES_ALL IPIUA,
IBY_EXTERNAL_PAYEES_V IEP,
POZ_SUPPLIER_SITES_V PSSV,
POZ_SUPPLIERS_V PSV,
PER_USERS PU,
PER_USERS PU1,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND IEBAV.EXT_BANK_ACCOUNT_ID = IPIUA.INSTRUMENT_ID
AND IPIUA.PAYMENT_FLOW = 'DISBURSEMENTS'
AND IPIUA.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND IPIUA.EXT_PMT_PARTY_ID = IEP.EXT_PAYEE_ID
AND IEP.SUPPLIER_SITE_ID = PSSV.VENDOR_SITE_ID
AND PSV.VENDOR_ID = PSSV.VENDOR_ID
AND NVL (IEP.PARTY_SITE_ID, -99) = NVL (PSSV.PARTY_SITE_ID, -99)
AND PU.USERNAME = IPIUA.CREATED_BY
AND PU1.USERNAME = IPIUA.LAST_UPDATED_BY
AND PSSV.PRC_BU_ID =HAOU.ORGANIZATION_ID