Friday, October 22, 2010
API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)
Do you think this Article is useful?
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.
Subscribe to:
Post Comments (Atom)
16 Responses to “API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)”
April 15, 2011 at 6:46 AM
it's very good article.
Thnk u very much.
Regards,
Anil
November 30, 2011 at 1:52 AM
THANK U VERY MUCH FOR PROVIDING IN DETAIL..
THIS REALLY HELPED ME..
RAVI
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
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
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..??
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.
November 1, 2013 at 3:52 AM
It helped me......
Regards,
Vikrant Korde
December 30, 2013 at 1:33 AM
nice.. thank you
April 20, 2014 at 3:44 AM
good article .. thank u
May 2, 2014 at 9:41 AM
Thanks
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
June 29, 2015 at 8:12 AM
Excellent !!
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.
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;
September 15, 2016 at 11:39 AM
baaaap ray...all my search ended here.....tq u so much
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