Thursday, December 16, 2010

Oracle R12 Banks Accounts Query

Oracle R12 Bank Accounts Query

SELECT owners.account_owner_party_id, asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM hr_all_organization_units
WHERE TYPE = 'OU' AND organization_id = asa.org_id) ou_name,
asa.vendor_site_code, ieb.country_code
--, (select bank_name from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_name
--, (select bank_number from ce_banks_v cbv where bank_party_id =ieb.bank_id) bank_number
--, ieb.branch_id
, cbbv.bank_name, cbbv.bank_number, cbbv.bank_branch_name,
cbbv.branch_number, cbbv.bank_branch_type, cbbv.eft_swift_code,
ieb.bank_account_num, ieb.currency_code, ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt bankacct_fromdata
FROM iby_pmt_instr_uses_all instrument,
iby_account_owners owners,
iby_external_payees_all payees,
iby_ext_bank_accounts ieb,
ap_supplier_sites_all asa,
ap_suppliers asp,
ce_bank_branches_v cbbv
WHERE owners.primary_flag = 'Y'
AND 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 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 cbbv.branch_party_id(+) = ieb.branch_id
--and asa.org_id in (125)
AND asp.created_by = 1451
ORDER BY 4, 1, 3

No comments: