SELECT cbb.bank_name, ac.bank_account_name, cep.payment_document_name,
ac.check_number, TO_CHAR(ac.check_date,'DD-MON-YY') check_date,TO_CHAR(ac.future_pay_due_date,'DD-MON-YY') future_pay_due_date,
cba.currency_code account_currency,
nvl(decode(ac.currency_code, cba.currency_code, ac.amount,
ap_utilities_pkg.ap_round_currency((ac.amount * ac.exchange_rate),
asp.base_currency_code)), 0) payment_amount,
ac.amount trans_amount, ac.currency_code trans_curr, hzp.party_name,
ac.vendor_site_code,
ac.exchange_rate
FROM ce_bank_branches_v cbb, ce_bank_accounts cba, ce_bank_acct_uses_all cbau,
ce_payment_documents cep, ap_checks ac, ap_system_parameters asp, hz_parties hzp
WHERE cbb.branch_party_id = cba.bank_branch_id
AND cba.bank_account_id = cbau.bank_account_id
AND cbau.bank_acct_use_id = ac.ce_bank_acct_use_id
AND cep.payment_document_id (+) = ac.payment_document_id
AND ac.status_lookup_code = 'ISSUED'
AND ac.future_pay_due_date <= nvl(:P_maturity_date, ac.future_pay_due_date)
AND ac.ce_bank_acct_use_id = nvl(:P_ce_bank_acct_use_id, ac.ce_bank_acct_use_id)
AND ac.party_id = nvl(:P_party_id, ac.party_id)
AND ac.party_id = hzp.party_id
AND (:P_vendor_site_id is NULL OR
(:P_vendor_site_id IS NOT NULL and ac.vendor_site_id = :P_vendor_site_id)
)
ORDER BY cbb.bank_name, cba.bank_account_name, ac.future_pay_due_date,
cba.currency_code, cep.payment_document_name, ac.check_number |