Monday, January 10, 2011
In this post, we will find a query which fetches both invoice and prepayment information of a payable invoice ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.
SELECT pv.vendor_name "Vendor Name",
ai.invoice_num "Invoice Num",
ai.invoice_id "Invoice Id",
ai.invoice_amount "Invoice amount",
ail.line_number "Invoice Line Num",
ai2.invoice_id "Prepay Invoice Id",
ai2.invoice_num "Prepay Invoice Num",
ail.prepay_line_number "Prepay Invoice Line Num",
(-1)*(ail.amount - NVL(ail.included_tax_amount,0))
"Prepay Amount Applied",
NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) +
NVL(ail.total_nrec_tax_amount, 0)), 0)
"Tax amount Applied"
FROM AP_INVOICES_ALL ai,
WHERE ai.invoice_id = ail.invoice_id
AND ai2.invoice_id = ail.prepay_invoice_id
AND ail.amount < 0
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND ail.line_type_lookup_code = 'PREPAY'
AND ai.vendor_id = pv.vendor_id
AND ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
AND ai.invoice_id = '166014';
Payable Invoice Data in other Modules
- Payables Invoice Data In Oracle Apps R12
- Payable Invoice Payment Data
- Payables Invoice Reconciliation Data
- Payable Invoice Suppliers Data
- Sub-Ledger Accounting Data of Payable Invoice
- Sub-Ledger Accounting data for Invoice Payments
- Query to Fetch Prepayment and Invoice Data
Do you think this Article is useful?
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.