Oracle EBS R12/AP/iPayments: Supplier Bank Accounts

In R12 Supplier Bank Accounts can be setup at four different Levels:

·         Supplier

·         Supplier Site

·         Address

·         Address Operating Unit

Query 1: Banks Associated at Supplier Level

SELECT  HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          'Supplier' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND ASS.VENDOR_ID           = APS.VENDOR_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 IEP.PARTY_SITE_ID IS  NULL

AND IEP.ORG_ID IS  NULL 

AND IEP.ORG_TYPE IS  NULL

AND IEP.SUPPLIER_SITE_ID IS   NULL  

Query 2: Banks Associated at Supplier Site Level

SELECT HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          ASS.VENDOR_SITE_CODE "SITE_CODE",

          HOU.NAME "OU_NAME",

          'Supplier Site' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  AP_SUPPLIER_SITES_ALL ASS ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK,

  hr_operating_units hou

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 IEP.ORG_TYPE IS NOT NULL

AND ass.ORG_ID = hou.organization_id

Query 3: Banks Associated at Address Level

SELECT   HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          'Supplier Address' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND HZP.PARTY_ID            = SITE_SUPP.PARTY_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.PARTY_SITE_ID       = SITE_SUPP.PARTY_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 IEP.ORG_ID IS  NULL 

AND IEP.SUPPLIER_SITE_ID IS   NULL  

Query 4: Banks Associated at Address OU Level

SELECT HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

                                  HOU.NAME "OU_NAME"

          'Supplier Address OU' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND HZP.PARTY_ID            = SITE_SUPP.PARTY_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.PARTY_SITE_ID       = SITE_SUPP.PARTY_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 IEP.ORG_ID IS  NOT NULL 

AND IEP.SUPPLIER_SITE_ID IS   NULL  

AND IEP.ORG_ID = hou.organization_id