Bank Details for Suppliers

Bank at Supplier Site level
-------------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, ASS.VENDOR_SITE_CODE "SITE CODE"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.HZ_PARTY_SITES SITE_SUPP
, apps.AP_SUPPLIER_SITES_ALL ASS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID
AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID
AND ASS.VENDOR_ID = APS.VENDOR_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.PARTY_SITE_ID = SITE_SUPP.PARTY_SITE_ID
AND IEP.SUPPLIER_SITE_ID = ASS.VENDOR_SITE_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
ORDER BY 1,3


Bank at Supplier level
------------------------

SELECT HZP.PARTY_NAME "VENDOR NAME"
, APS.SEGMENT1 "VENDOR NUMBER"
, IEB.BANK_ACCOUNT_NUM "ACCOUNT NUMBER"
, IEB.BANK_ACCOUNT_NAME "ACCOUNT NAME"
, IEB.BANK_ACCOUNT_TYPE "Bank Account Type" 
, HZPBANK.PARTY_NAME "BANK NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BANK NUMBER"
, HZPBRANCH.PARTY_NAME "BRANCH NAME"
, HOPBRANCH.BANK_OR_BRANCH_NUMBER "BRANCH NUMBER" 
, HZPBANK.address1 "Bank Address"
, HZPBANK.country "Bank Country"
, HZPBANK.city "Bank City"
, HZPBANK.Postal_Code
, NVL(HZPBANK.state, HZPBANK.province) "State/Province"
FROM apps.HZ_PARTIES HZP
, apps.AP_SUPPLIERS APS
, apps.IBY_EXTERNAL_PAYEES_ALL IEP
, apps.IBY_PMT_INSTR_USES_ALL IPI
, apps.IBY_EXT_BANK_ACCOUNTS IEB
, apps.HZ_PARTIES HZPBANK
, apps.HZ_PARTIES HZPBRANCH
, apps.HZ_ORGANIZATION_PROFILES HOPBANK
, apps.HZ_ORGANIZATION_PROFILES HOPBRANCH
WHERE HZP.PARTY_ID = APS.PARTY_ID
AND IEP.PAYEE_PARTY_ID = HZP.PARTY_ID
AND IEP.EXT_PAYEE_ID = IPI.EXT_PMT_PARTY_ID
AND IPI.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
AND IEB.BANK_ID = HZPBANK.PARTY_ID
AND IEB.BANK_ID = HZPBRANCH.PARTY_ID
AND HZPBRANCH.PARTY_ID = HOPBRANCH.PARTY_ID
AND HZPBANK.PARTY_ID = HOPBANK.PARTY_ID
and IEP.SUPPLIER_SITE_ID is null
and aps.vendor_id = 2