Hi All,
I have a peculiar requirement, wherein I have to prepare a query on iby_ext_bank_accounts.
The query i prepared is this
SELECT (SELECT hp.party_name
FROM hz_parties hp, hz_parties hz
WHERE hp.party_id = iby.bank_id
AND hz.party_id = iby.branch_id) party_name,
(SELECT user_concurrent_program_name
FROM apps.fnd_concurrent_programs_tl
WHERE concurrent_program_id = iby.program_id
and application_id = iby.program_application_id) user_concurrent_program_name,
(SELECT usr.user_name
FROM apps.fnd_user usr, apps.FND_CONCURRENT_requests cp
WHERE usr.user_id = cp.requested_by
AND cp.request_id = iby.request_id) REQUEST_ID,
(SELECT vendor_name
FROM ap_suppliers t
WHERE vendor_id IN
(SELECT ext_pmt_party_id
FROM iby.iby_pmt_instr_uses_all ipi,
iby.iby_external_payees_all iep,
iby.iby_ext_bank_accounts iby1
WHERE ipi.instrument_id = iby1.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND iep.payee_party_id = t.party_id)) supplier_name,
/* (select vendor_name
from ap_suppliers
where vendor_id IN (select vendor_id
from ap_supplier_sites_all
where party_site_id IN(select party_site_id
from iby_external_payees_all
where ext_payee_id IN (select ext_pmt_party_id
from iby_pmt_instr_uses_all ipi,
iby_ext_bank_accounts iby
where ipi.instrument_id = iby.ext_bank_account_id )))) supp,*/
(SELECT segment1
FROM ap_suppliers t
WHERE vendor_id IN
(SELECT ext_pmt_party_id
FROM iby_pmt_instr_uses_all ipi,
iby_external_payees_all iep\*,
iby_ext_bank_accounts iby*\
WHERE ipi.instrument_id = iby.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND iep.payee_party_id = t.party_id
AND SYSDATE BETWEEN NVL(iby.start_date, SYSDATE)
AND NVL(iby.end_date, SYSDATE)
AND SYSDATE BETWEEN NVL(ipi.start_date, SYSDATE)
AND NVL(ipi.end_date, SYSDATE))) Supplier_number,
(SELECT vendor_site_code
FROM ap_supplier_sites_all t
WHERE vendor_id IN
(SELECT ext_pmt_party_id
FROM iby_pmt_instr_uses_all ipi,
iby_external_payees_all iep,
iby.iby_ext_bank_accounts iby
WHERE ipi.instrument_id = iby.ext_bank_account_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND iep.party_site_id = t.party_site_id
AND iep.org_id = t.org_id
AND iep.supplier_site_id = t.vendor_site_id
AND SYSDATE BETWEEN NVL(iby.start_date, SYSDATE)
AND NVL(iby.end_date, SYSDATE)
AND SYSDATE BETWEEN NVL(ipi.start_date, SYSDATE)
AND NVL(ipi.end_date, SYSDATE))) Supplier_site,
iby.*
FROM iby.iby_ext_bank_accounts iby;
Upon running the query I get 443 rows, now for column name supplier_name,Supplier_number and supplier site I am getting the same value
'Southern Office Supplies',5034,'SOS MAIN' for all the 443 rows. Is ti possible to have different values for all the rows? If yes then hoe=w can we do it?
Since this is a SQL Query only so i am putting in SQL AND PL/SQL.
I need to do this urgent. I am using PL?SQL Developer and oracle apps r12.