Query For Supplier Banks

Query For Supplier Banks

SELECT
--Bank related Info
                  ieba.ext_bank_account_id
                  ,hp.party_name                      Bank_party_name
                  ,ieba.bank_account_num              bank_account_num
                  ,ieba.bank_account_name             bank_account_name
                  ,ieba.country_code                  bank_acct_country_code
                  ,ieba.currency_code                 bank_acct_currency_code
--Bank Address realted info
                 ,hp.address1                        bank_address_line1
                 ,hp.address2                        bank_address_line2
                 ,hp.address3                        bank_address_line3
                 ,hp.city                            bank_address_city
                 ,hp.state                           bank_address_state
                 ,hp.postal_code                     bank_address_zip
                 ,hp.country                         bank_address_country
--Bank Branch Address
                 ,hp1.address1                       branch_address_line1
                 ,hp1.address2                       branch_address_line2
                 ,hp1.address3                       branch_address_line3
                 ,hp1.city                           branch_address_city
                 ,hp1.state                          branch_address_state
                 ,hp1.postal_code                    branch_address_zip
                 ,hp1.country                        branch_address_country
--Supplier Site Info
                 ,assa.vendor_site_id
                 ,assa.party_site_id                 supplier_party_site_id
                 ,assa.vendor_site_code              vendor_site_code
                 ,assa.pay_site_flag                 pay_site_flag
                 ,assa.purchasing_site_flag          purchasing_site_flag
                 ,assa.rfq_only_site_flag            rfq_only_site_flag
--Supplier Info         
                 ,aps.segment1                       oracle_supplier_number
                ,aps.vendor_id
                ,aps.vendor_name                    supplier_name
                ,aps.party_id                       supplier_party_id
                ,iepa.remit_advice_fax              remit_advice_fax
                ,iepa.remit_advice_email            remit_advice_email
FROM        
          ap_supplier_sites_all              assa
          ,hz_parties                         hp          
          ,iby_ext_bank_accounts              ieba
          ,iby_external_payees_all            iepa
          ,iby_pmt_instr_uses_all             ipiua           
          ,ap_suppliers                       aps
          ,hz_parties                         hp1
WHERE        assa.vendor_site_id         =      iepa.supplier_site_id
AND          hp.party_id                 =      ieba.bank_id
AND          ipiua.instrument_id         =      ieba.ext_bank_account_id
AND          ipiua.ext_pmt_party_id      =      iepa.ext_payee_id
AND          assa.vendor_id              =      aps.vendor_id
AND          ieba.branch_id              =      hp1.party_id