Query to fetch Supplier Site Bank Account Details in Oracle Apps R12

SELECT aps.vendor_name supplier_name,
       aps.segment1 supplier_number,
       (SELECT NAME
          FROM apps.hr_operating_units hou
         WHERE assa.org_id = hou.organization_id)
          operating_unit,                                         
       assa.vendor_site_code,
       assa.address_line1,
       assa.address_line2,
       assa.address_line3,
       assa.address_line4,
       assa.city,
       assa.state,
       assa.zip postal_code,
       assa.LANGUAGE,
       assa.purchasing_site_flag address_purpose_purchasing,
       assa.pay_site_flag address_purpose_payment,
       assa.rfq_only_site_flag address_purpose_rfq_only,
       (SELECT meaning
          FROM apps.fnd_lookup_values flv
         WHERE     flv.lookup_type = 'PAY GROUP'
               AND lookup_code = assa.pay_group_lookup_code
               AND flv.LANGUAGE = USERENV ('LANG'))
          pay_group,
       (select gcckv.concatenated_segments
          from apps.gl_code_combinations_kfv gcckv
         where gcckv.code_combination_id = assa.accts_pay_code_combination_id)
          liability_account,
       (select gcckv.concatenated_segments
          from apps.gl_code_combinations_kfv gcckv
         where gcckv.code_combination_id = assa.prepay_code_combination_id)
          prepayment_account,
       assa.payment_currency_code payment_currency,
       (select ap.name
          from apps.ap_terms ap
         where ap.term_id = assa.terms_id)
          payment_terms,
       assa.terms_date_basis,
       assa.pay_date_basis_lookup_code pay_date_basis,
       assa.invoice_currency_code invoice_currency,
       assa.vat_code tax_classification,
       NULL bank_name,
       NULL branch_site,
       NULL branch_type,
       NULL swift_code,
       NULL account_name,
       NULL account_number,
       NULL bank_account_priority,
       NULL account_currency,
       NULL iban,
       NULL assignment_level,
       NULL pay_on_code,
       assa.country,
       ship_to.location_code ship_to_location,
       bill_to.location_code bill_to_location,
       (SELECT DISTINCT payment_method_name
          FROM apps.iby_payment_methods_vl ipmvl,
               apps.iby_ext_party_pmt_mthds iepmm,
               apps.iby_external_payees_all iepa
         WHERE     iepa.payee_party_id(+) = aps.party_id
               AND iepa.party_site_id = assa.party_site_id
               AND iepa.supplier_site_id = assa.vendor_site_id
               AND ipmvl.payment_method_code = iepmm.payment_method_code
               AND iepmm.ext_pmt_party_id = iepa.ext_payee_id
               AND iepmm.primary_flag = 'Y'
               AND NVL (ipmvl.inactive_date, SYSDATE) >= SYSDATE
               AND NVL (iepmm.inactive_date, SYSDATE) >= SYSDATE
               AND iepa.org_id = assa.org_id)
          default_payment_method,
       NULL BANK_COUNTRY,
       assa.hold_unmatched_invoices_flag hold_from_payment,
       assa.creation_date,
       assa.inactive_date inactive_site_date,
       awt.name withholding_tax,
       assa.allow_awt_flag,
       assa.vendor_site_code_alt,
       assa.vendor_site_code old_site_code,
       NULL bank_account_end_date,
       NULL bank_site_email_address,
       assa.supplier_notif_method,
       assa.fax,
       assa.phone,
       assa.email_address,
       assa.attribute14 tabs_supplier
  FROM apps.iby_external_payees_all payees,
       apps.ap_supplier_sites_all assa,
       apps.ap_suppliers aps,
       apps.fnd_lookup_values flv,
       apps.hr_locations_all bill_to,
       apps.hr_locations_all ship_to,
       apps.ap_awt_groups awt
 WHERE     1 = 1
       AND payees.payee_party_id = aps.party_id
       AND payees.supplier_site_id = assa.vendor_site_id
       AND assa.vendor_id = aps.vendor_id
       AND flv.lookup_code(+) = assa.pay_group_lookup_code
       AND flv.lookup_type(+) = 'PAY GROUP'
       AND flv.LANGUAGE(+) = USERENV ('LANG')
       AND NOT EXISTS
                  (SELECT 1
                     FROM apps.iby_pmt_instr_uses_all uses,
                          apps.iby_external_payees_all payee
                    WHERE     1 = 1
                          AND USES.instrument_type = 'BANKACCOUNT'
                          AND PAYEE.ext_payee_id = uses.ext_pmt_party_id
                          AND PAYEE.payee_party_id = aps.party_id
                          AND PAYEE.payment_function = 'PAYABLES_DISB')
       AND bill_to.location_id(+) = aps.bill_to_location_id
       AND ship_to.location_id(+) = aps.ship_to_location_id
       AND awt.GROUP_ID(+) = assa.AWT_GROUP_ID
       AND assa.org_id = :p_org_id                          /* Operations Id*/
UNION
SELECT aps.vendor_name supplier_name,
       aps.segment1 supplier_number,
       (SELECT NAME
          FROM apps.hr_operating_units hou
         WHERE assa.org_id = hou.organization_id)
          operating_unit,
       assa.vendor_site_code,
       assa.address_line1,
       assa.address_line2,
       assa.address_line3,
       assa.address_line4,
       assa.city,
       assa.state,
       assa.zip postal_code,
       assa.LANGUAGE,
       assa.purchasing_site_flag address_purpose_purchasing,
       assa.pay_site_flag address_purpose_payment,
       assa.rfq_only_site_flag address_purpose_rfq_only,
       (SELECT meaning
          FROM apps.fnd_lookup_values flv
         WHERE     flv.lookup_type = 'PAY GROUP'
               AND lookup_code = assa.pay_group_lookup_code
               AND flv.LANGUAGE = USERENV ('LANG'))
          pay_group,
       (select gcckv.concatenated_segments
          from apps.gl_code_combinations_kfv gcckv
         where gcckv.code_combination_id = assa.accts_pay_code_combination_id)
          liability_account,
       (select gcckv.concatenated_segments
          from apps.gl_code_combinations_kfv gcckv
         where gcckv.code_combination_id = assa.prepay_code_combination_id)
          prepayment_account,
       assa.payment_currency_code payment_currency,
       (select ap.name
          from apps.ap_terms ap
         where ap.term_id = assa.terms_id)
          payment_terms,
       assa.terms_date_basis,
       assa.pay_date_basis_lookup_code pay_date_basis,
       assa.invoice_currency_code invoice_currency,
       assa.vat_code tax_classification,
       cbv.bank_name,
       cbbv.bank_branch_name branch_site,
       cbbv.bank_branch_type branch_type,
       cbbv.eft_swift_code swift_code,
       ieb.bank_account_name account_name,
       ieb.bank_account_num account_number,
       instrument.order_of_preference bank_account_priority,
       ieb.currency_code account_currency,
       ieb.iban iban,
       CASE
          WHEN (    payees.party_site_id IS NOT NULL
                AND payees.supplier_site_id IS NOT NULL
                AND payees.org_id IS NOT NULL
                AND payees.org_type IS NOT NULL)
          THEN
             'Supplier Site Level'
          WHEN (    payees.party_site_id IS NOT NULL
                AND payees.org_id IS NOT NULL
                AND payees.org_type IS NOT NULL)
          THEN
             'Address ORG Level'
          WHEN (payees.party_site_id IS NOT NULL)
          THEN
             'Address Level'
          ELSE
             'Supplier Level'
       END
          assignment_level,
       assa.pay_on_code,
       assa.country,
       ship_to.location_code ship_to_location,
       bill_to.location_code bill_to_location,
       (SELECT DISTINCT payment_method_name
          FROM apps.iby_payment_methods_vl ipmvl,
               apps.iby_ext_party_pmt_mthds iepmm,
               apps.iby_external_payees_all iepa
         WHERE     iepa.payee_party_id(+) = aps.party_id
               AND iepa.party_site_id = assa.party_site_id
               AND iepa.supplier_site_id = assa.vendor_site_id
               AND ipmvl.payment_method_code = iepmm.payment_method_code
               AND iepmm.ext_pmt_party_id = iepa.ext_payee_id
               AND iepmm.primary_flag = 'Y'
               AND NVL (ipmvl.inactive_date, SYSDATE) >= SYSDATE
               AND NVL (iepmm.inactive_date, SYSDATE) >= SYSDATE
               AND iepa.org_id = assa.org_id)
          default_payment_method,
       cbv.home_country bank_country,
       assa.hold_unmatched_invoices_flag hold_from_payment,
       assa.creation_date,
       assa.inactive_date inactive_site_date,
       awt.name withholding_tax,
       assa.allow_awt_flag,
       assa.vendor_site_code_alt,
       assa.vendor_site_code old_site_code,
       instrument.end_date bank_account_end_date,
       ieb.contact_email bank_site_email_address,
       assa.supplier_notif_method,
       assa.fax,
       assa.phone,
       assa.email_address,
       assa.attribute14 tabs_supplier
    FROM apps.iby_pmt_instr_uses_all instrument,
       apps.iby_external_payees_all payees,
       apps.iby_ext_bank_accounts ieb,
       apps.ap_supplier_sites_all assa,
       apps.ap_suppliers aps,
       apps.ce_bank_branches_v cbbv,
       apps.ce_banks_v cbv,
       apps.fnd_lookup_values flv,
       apps.hr_locations_all bill_to,
       apps.hr_locations_all ship_to,
       apps.ap_awt_groups awt
 WHERE     1 = 1         
   AND ieb.ext_bank_account_id = instrument.instrument_id
   AND payees.ext_payee_id = instrument.ext_pmt_party_id(+)
   AND payees.payee_party_id = aps.party_id
       AND assa.vendor_id = aps.vendor_id
       AND cbbv.branch_party_id(+) = ieb.branch_id
       AND cbv.bank_party_id(+) = ieb.bank_id
       AND flv.lookup_code(+) = assa.pay_group_lookup_code
       AND flv.lookup_type(+) = 'PAY GROUP'
       AND flv.LANGUAGE(+) = USERENV ('LANG')
       AND bill_to.location_id(+) = aps.bill_to_location_id
       AND ship_to.location_id(+) = aps.ship_to_location_id
       AND awt.group_id(+) = assa.awt_group_id
       AND assa.org_id = :p_org_id