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"
       AP_INVOICES_ALL ai2,
       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';

5 Responses to “Query fetching both Prepayment Data and Invoice Data of a Payable Invoice”

Anonymous said...
January 27, 2011 at 11:19 PM

This two tables: AP_INVOICE_LINES_ALL ail,
AP_SUPPLIERS pv are not in how did you derive the query?

Team search said...
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.

Anonymous said...
June 9, 2011 at 2:54 AM


Anonymous said...
May 8, 2012 at 8:13 AM

Very usefull. Thanks a lot

Amir said...
December 28, 2016 at 7:54 AM

Very good.

Post a Comment


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.