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 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
January 16, 2017 at 9:46 PM
It's about time that Oracle be used in finding the AP invoice status. Something that Paper masters review are looking forward in doing with their own invoice related work.
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