Query to get the All Supplier, Site and Bank Details at Site Level with or without bank setup at site level

Below query is used to retrieve the supplier, site and bank details of all the suppliers  belonging to particular org_id, also to retrieve the supplier and site information if no bank account is setup for a particular site. 

If a site does not have any bank account setup then only supplier and site details are displayed.

SELECT * FROM (select  distinct 

    asp.vendor_name "VENDOR NAME",

    asp.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE" ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    NULL "ACCOUNT NUMBER",

    NULL "ACCOUNT NAME",

    NULL "Bank Account Type",

    NULL ext_bank_account_id,

    NULL bank_account_num,

    NULL  ext_bank_account_iban_number,

    NULL bank_account_name,

    NULL bank_account_name_alt,

    NULL bank_account_num_electronic,

    NULL  bank_account_end_date,

   NULL bank_branch_name,

   NULL bank_branch_name_alt,

   NULL branch_number,

   NULL branch_party_id,

   NULL bank_name_alt,

   NULL bank_name,

   NULL bank_number

FROM ap_supplier_sites_all assa,

  apps.ap_suppliers asp

 WHERE assa.vendor_id              = asp.vendor_id

and asp.segment1 = NVL(:P_VENDOR_NUMBER,aps.segment1)

and assa.org_id=:P_ORG_ID

MINUS

SELECT

/*Supplier Information*/  

distinct 

    aps.vendor_name "VENDOR NAME",

    aps.segment1 "VENDOR NUMBER",

      /* Supplier Site Information */

    assa.vendor_site_code "SITE CODE"   ,

     assa.pay_site_flag   "PAY SITE FLAG",

    assa.purchasing_site_flag "PURCHASE SITE FLAG",

    assa.rfq_only_site_flag   "RFQ SITE FLAG",

    assa.ADDRESS_LINE1 ,

    assa.ADDRESS_LINE2,

    assa.ADDRESS_LINE3,

    assa.city,

    assa.state,

    assa.zip,

    assa.country,

     /* Bank Information*/

    ieb.bank_account_num "ACCOUNT NUMBER",

    ieb.bank_account_name "ACCOUNT NAME",

    ieb.bank_account_type "Bank Account Type",

    ieb.ext_bank_account_id,

    ieb.bank_account_num,

    ieb.iban ext_bank_account_iban_number,

    ieb.bank_account_name,

    ieb.bank_account_name_alt,

    ieb.bank_account_num_electronic,

    ieb.end_date bank_account_end_date,

    ext_branch.bank_branch_name,

    ext_branch.bank_branch_name_alt,

    ext_branch.branch_number,

    ext_branch.branch_party_id,

    ext_bank.bank_name_alt,

    ext_bank.bank_name,

    ext_bank.bank_number

FROM

    apps.hz_parties hzp,

    apps.ap_suppliers aps,

    apps.hz_party_sites site_supp,

    apps.ap_supplier_sites_all assa,

    apps.iby_external_payees_all iep,

    apps.iby_pmt_instr_uses_all ipi,

    apps.iby_ext_bank_accounts ieb,

    iby_ext_banks_v ext_bank,

    iby_ext_bank_branches_v ext_branch

WHERE

        hzp.party_id = aps.party_id

    AND        hzp.party_id = site_supp.party_id

    AND        site_supp.party_site_id = assa.party_site_id

    AND        assa.vendor_id = aps.vendor_id

    AND        aps.enabled_flag ='Y'

    AND      ( aps.end_date_active IS NULL OR aps.end_date_active > SYSDATE)

    AND        iep.payee_party_id = hzp.party_id

    AND        iep.party_site_id = site_supp.party_site_id

    AND        iep.supplier_site_id = assa.vendor_site_id

    AND        ieb.bank_id = ext_bank.bank_party_id

    AND        ieb.branch_id = ext_branch.branch_party_id

   AND        ipi.instrument_id = ieb.ext_bank_account_id

   AND        iep.ext_payee_id = ipi.ext_pmt_party_id  

   and       (assa.inactive_date is null OR assa.inactive_date >SYSDATE  )                             

    AND        assa.org_id = :P_ORG_ID

   AND    aps.segment1= NVL(:P_VENDOR_NUMBER,aps.segment1)

  ) order by 1