Monday, January 10, 2011
Query fetching both Prepayment Data and Invoice Data of a Payable Invoice
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,
AP_INVOICES_ALL ai2,
AP_INVOICE_LINES_ALL ail,
AP_SUPPLIERS pv
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?
Subscribe to:
Post Comments (Atom)
Disclaimer
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.
5 Responses to “Query fetching both Prepayment Data and Invoice Data of a Payable Invoice”
January 27, 2011 at 11:19 PM
This two tables: AP_INVOICE_LINES_ALL ail,
AP_SUPPLIERS pv are not in EBS..so how did you derive the query?
January 28, 2011 at 6:58 AM
Hi Friend,
The tables mentioned ( AP_INVOICE_LINES_ALL & AP_SUPPLIERS) are available in Oracle Applications Release R12.1.1 Instance. I think so, you are trying to test the query in lower releases.
June 9, 2011 at 2:54 AM
:P
May 8, 2012 at 8:13 AM
Very usefull. Thanks a lot
December 28, 2016 at 7:54 AM
Very good.
Post a Comment