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)
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