Hi, I am trying to fetch all the details of the suppliers, supplier sites and bank information. I have 2 scenarios.
1. A supplier has 2 sites and both the sites are associated with a bank. I am able to get these records properly by using below query :
SELECT *
FROM apps.iby_pmt_instr_uses_all instrument,
apps.iby_account_owners owners,
apps.iby_external_payees_all payees,
apps.iby_ext_bank_accounts ieb,
apps.ap_supplier_sites_all asa,
apps.ap_suppliers asp,
apps.ce_bank_branches_v cbbv
WHERE owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id --(+)
AND payees.ext_payee_id = instrument.ext_pmt_party_id --(+)
AND cbbv.branch_party_id = ieb.branch_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
and payees.party_site_id = asa.party_site_id
--and asa.vendor_id = 4092
2. A suppliers has 2 sites but 1 site has a bank and the other site doesn't have a bank. I need to show both the sites in this case (with bank and without bank). I am trying to put a outer join on "iby_pmt_instr_uses_all " table since this table doesn't have a record corresponding to 'iby_external_payees_all' table. But this doesn't seems to work. It returns me 4 records instead of 2.
Can you please suggest how can I achieve my requirement , so that I get all the supplier sites for a suppliers, irrespective of whether there is any bank associated with it or not.
Thanks,
Abha