Friday, October 22, 2010

API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)


When I tried to find the AP invoice status from backend, I found the column name APPROVAL_STATUS_LOOKUP_CODE under the view name AP_INVOICE_V. Since it is org based view, setting the context is mandatory. Please find my script to set the context here.

Below query will give you the status,

SELECT invoice_num
      ,approval_status_lookup_code
FROM   ap_invoices_v
WHERE  invoice_num = 'DM080310';

When I further traced to understand the source of the view column, I found that there is no column in the AP_INVOICES_ALL table that stores the validation status. An API named AP_INVOICES_PKG.GET_APPROVAL_STATUS is used by the view to finding the status.

Below query will give you the usage of the mentioned api,


SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
            (
             I.INVOICE_ID
            ,I.INVOICE_AMOUNT
            ,I.PAYMENT_STATUS_FLAG
            ,I.INVOICE_TYPE_LOOKUP_CODE
            ) Approval_Status
      ,invoice_num
FROM   AP_INVOICES I
WHERE  invoice_num = 'DM080310';
    


When I went one more step deeper, I got the below,

Invoice distributions are validated individually and the status is stored at the invoice distribution level.  This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.
Valid values for the column are:

A - Validated (it used to be called Approved)
N or NULL - Never validated
T - Tested but not validated

The invoice header form derives the invoice validation status based on the following:
'Validated'
-          If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
'Never Validated'
-          If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
'Needs Revalidation'
-          If there are any rows in AP_HOLDS that do not have a release code.
-          If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.
-          If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).
I hope this information helps you.

16 Responses to “API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)”

Anil said...
April 15, 2011 at 6:46 AM

it's very good article.
Thnk u very much.

Regards,
Anil


Anonymous said...
November 30, 2011 at 1:52 AM

THANK U VERY MUCH FOR PROVIDING IN DETAIL..

THIS REALLY HELPED ME..

RAVI


Anonymous said...
May 23, 2012 at 11:38 AM

Thanks a lot for this article. I was searching here and there to get table information on this field. This cleared all my doubts.

thanks
Fareed


Anonymous said...
May 23, 2012 at 12:06 PM

The Invoice Status field at the Header also show "Selected for Payment". Do you know how to get this information?

thanks
Fareed


Anonymous said...
December 3, 2012 at 10:32 AM

Invoice_number,invoice_date,invoice_amount,transaction_type,currency

In which base table does this data stores and how to retrieve data from these columns for AR invoice API..

Am a beginne can any body guide me..??


Anonymous said...
January 27, 2013 at 9:22 PM

I tried to include this in my query in Oracle SQL Developer but it retun null. Is their any setup needed to make this work?? Thank you.


Anonymous said...
November 1, 2013 at 3:52 AM

It helped me......

Regards,
Vikrant Korde


Anonymous said...
December 30, 2013 at 1:33 AM

nice.. thank you


Anonymous said...
April 20, 2014 at 3:44 AM

good article .. thank u


Anonymous said...
May 2, 2014 at 9:41 AM

Thanks


Muhammad Burhan Shafaat said...
May 7, 2014 at 11:01 PM

Here is an accurate answer for this question

http://m-burhan.blogspot.com/2012/06/function-which-provide-ap-validation.html


Unknown said...
June 29, 2015 at 8:12 AM

Excellent !!


Jeff said...
July 9, 2015 at 1:21 PM

Good SQL. Needs to also join on Supplier Sites table (ap_supplier_sites_all) otherwise vendors/suppliers that have multiple payment sites would end up having duplicated rows.


Unknown said...
September 7, 2015 at 6:05 AM

To fetch data from above query we need to run below context script by replacing 124 with respective OU id

begin
mo_global.set_policy_context('S',124);
END;


yogesh said...
September 15, 2016 at 11:39 AM

baaaap ray...all my search ended here.....tq u so much


Unknown said...
October 20, 2017 at 1:49 AM

Anyone've tried to use this function for indexing AP_INVOICES_ALL ? For some of the queries it is way to slow to compute the status online.


Post a Comment

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.