Query to Display Suppliers and Sites Banks Details

SELECT asp.vendor_name

      ,assa.vendor_site_code

      ,asp.pay_group_lookup_code vendor_pay_group

      ,assa.pay_group_lookup_code site_pay_group

      , asp.vat_code vendor_vat_code

      ,assa.vat_code site_vat_code

      ,asp.vat_registration_num vendor_reg_no

      ,assa.vat_registration_num site_reg_no

      ,assa.org_id

      ,assa.inactive_date

      ,at.name vendor_terms

      ,at2.name site_terms

      ,asp.payment_method_lookup_code supplier_pay_method

      ,assa.payment_method_lookup_code site_pay_method

      ,ieba.bank_account_name vendor_bank_account_name

      ,ieba.masked_iban AS vendor_iban

      ,ieba.currency_code vendor_currency_code

      ,ipiua.order_of_preference vendor_order_of_preference

      ,ipiua.start_date vendor_start_date

      ,ipiua.end_date vendor_end_date

      ,fc.name AS currency_name

      ,hp.party_name AS vendor_bank_name

      ,ieba.masked_bank_account_num AS vendor_bank_account_number

      ,cbbv.bank_branch_name vendor_bank_branch_name

      ,cbbv.branch_number AS vendor_branch_name

      ,hop.bank_or_branch_number AS vendor_bank_number

      ,cbbv.eft_swift_code AS vendor_eft_swift_code

      ,ieba.bank_account_type as vendor_bank_account_type

  FROM ap_suppliers asp

      ,ap_supplier_sites_all assa

      ,ap_terms at

      ,ap_terms at2

      ,iby_external_payees_all iepa

      ,iby_pmt_instr_uses_all ipiua

      ,iby_ext_bank_accounts ieba

      ,fnd_currencies_vl fc

      ,hz_parties hp

      ,ce_bank_branches_v cbbv

      ,hz_organization_profiles hop

 WHERE 1=1

   AND asp.vendor_id=assa.vendor_id

   AND at.term_id=asp.terms_id

   AND at2.term_id(+)=assa.terms_id

   AND asp.vendor_name=nvl(:p_vend_name,asp.vendor_name)

   AND iepa.payee_party_id=asp.party_id

   AND iepa.party_site_id=assa.party_site_id

   AND iepa.org_id=assa.org_id

   AND iepa.payment_function = 'PAYABLES_DISB'

   AND iepa.supplier_site_id=assa.vendor_site_id

   AND iepa.ext_payee_id = ipiua.ext_pmt_party_id(+)

   AND ipiua.instrument_type (+)= 'BANKACCOUNT'

   AND ieba.ext_bank_account_id(+)=ipiua.instrument_id

   AND fc.currency_code(+) = ieba.currency_code

   AND hp.party_id(+)=ieba.bank_id

   AND ieba.branch_id=cbbv.branch_party_id(+)

   AND ieba.bank_id=hop.party_id(+)

   AND SYSDATE BETWEEN NVL (ieba.start_date, SYSDATE)

                                 AND NVL (ieba.end_date, SYSDATE)

                 AND SYSDATE BETWEEN NVL (ipiua.start_date, SYSDATE)

                                 AND NVL (ipiua.end_date, SYSDATE)

  AND SYSDATE BETWEEN TRUNC(hop.effective_start_date(+))

  AND NVL(TRUNC(hop.effective_end_date(+)),SYSDATE + 1)

;