plsql - Query for Active Supplier and Supplier Sites with bank and bank branch details TCA
SELECT sup.segment1 vendor_num
,sup.vendor_name
,supsite.vendor_site_code
,(SELECT hur.name
FROM hr_operating_units hur
WHERE hur.organization_id = supsite.org_id) ou_name
,b.bank_account_name
,b.ext_bank_account_id
,b.bank_account_number
,b.bank_account_num_electronic
,b.currency_code
,b.iban_number
,b.bank_name
,b.bank_number
,b.bank_branch_name
,b.branch_number
,b.country_code
,b.alternate_account_name
,b.bank_account_type
,b.account_suffix
,b.description
,b.foreign_payment_use_flag
,b.payment_factor_flag
,b.eft_swift_code
FROM apps.iby_pmt_instr_uses_all ibyu
,apps.iby_ext_bank_accounts_v b
,apps.iby_external_payees_all ibypayee
,apps.hz_parties prty
,apps.ap_suppliers sup
,apps.ap_supplier_sites_all supsite
WHERE 1 = 1
AND prty.party_id(+) = ibypayee.payee_party_id
AND prty.party_id = sup.party_id(+)
AND ibypayee.supplier_site_id = supsite.vendor_site_id(+)
AND ibyu.instrument_id = b.ext_bank_account_id
AND ibyu.instrument_type = 'BANKACCOUNT'
/* AND (b.currency_code = p_payment_currency OR b.currency_code IS NULL OR
nvl(b.foreign_payment_use_flag, 'N') = 'Y')*/
-- AND ibyu.payment_function = p_payment_function
AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
-- AND ibypayee.payee_party_id = p_payee_party_id
AND trunc(SYSDATE) BETWEEN nvl(ibyu.start_date, trunc(SYSDATE)) AND
nvl(ibyu.end_date - 1, trunc(SYSDATE))
AND trunc(SYSDATE) BETWEEN nvl(b.start_date, trunc(SYSDATE)) AND
nvl(b.end_date - 1, trunc(SYSDATE))
and prty.status='A' -- Active Parties
AND nvl(sup.end_date_active,sysdate) >= sysdate -- Active supplier records
AND nvl(supsite.inactive_date,sysdate) >= sysdate -- Active Supplier Sites Records
and supsite.org_id in (215,126) -- Opearting unit hardcoded
/* AND (ibypayee.party_site_id IS NULL OR ibypayee.party_site_id = p_payee_party_site_id)
AND (ibypayee.org_id IS NULL OR
(ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type))*/
ORDER BY ibypayee.party_site_id
,ibypayee.org_id
,ibyu.order_of_preference;
