select i.invoice_num ,
ps.payment_num,ps.due_date,ps.gross_amount,curr.currency_code,fnd.meaning,pov.vendor_name,
bank.bank_name,bank.bank_branch_name,acc.bank_account_num bank_account_num
from ap_invoices i, ap_payment_schedules ps, FND_CURRENCIES_VL curr, po_vendors pov,
FND_LOOKUPS fnd,ce_bank_branches_v bank, iby_payee_all_bankacct_v acc
where
(fnd.lookup_type = 'YES_NO' and fnd.lookup_code = ps.hold_flag) and
ps.invoice_id = i.invoice_id and
i.vendor_id = pov.vendor_id and
i.invoice_currency_code= curr.currency_code and
ps.payment_status_flag <> 'Y' and
ps.global_attribute11 is null and
ps.external_bank_account_id = acc.ext_bank_account_id (+) and
acc.branch_id = bank.branch_party_id (+) and
acc.bank_id = bank.bank_party_id (+) and
invoice_num between nvl(:p_inv_num_ini , invoice_num) and nvl(:p_inv_num_fin, invoice_num) and
due_date between nvl(:p_due_date_ini, due_date) and nvl( :p_due_date_fin ,due_date) and
gross_amount between nvl( :p_gross_amt_ini ,gross_amount) and nvl(:p_gross_amt_fin, gross_amount)
order by :p_inv_sort |
select i.invoice_num ,
ps.payment_num,ps.due_date,ps.gross_amount,curr.currency_code,fnd.meaning,pov.vendor_name,
bank.bank_name,bank.bank_branch_name,acc.bank_account_num bank_account_num
from ap_invoices i, ap_payment_schedules ps, FND_CURRENCIES_VL curr, po_vendors pov,
FND_LOOKUPS fnd,ce_bank_branches_v bank, iby_payee_all_bankacct_v acc
where
(fnd.lookup_type = 'YES_NO' and fnd.lookup_code = ps.hold_flag) and
ps.invoice_id = i.invoice_id and
i.vendor_id = pov.vendor_id and
i.invoice_currency_code= curr.currency_code and
ps.payment_status_flag <> 'Y' and
ps.global_attribute11 is null and
ps.external_bank_account_id = acc.ext_bank_account_id (+) and
acc.branch_id = bank.branch_party_id (+) and
acc.bank_id = bank.bank_party_id (+) and
invoice_num between nvl(:p_inv_num_ini , invoice_num) and nvl(:p_inv_num_fin, invoice_num) and
due_date between nvl(:p_due_date_ini, due_date) and nvl( :p_due_date_fin ,due_date) and
gross_amount between nvl( :p_gross_amt_ini ,gross_amount) and nvl(:p_gross_amt_fin, gross_amount)
order by :p_inv_sort |
