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,
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,
WHEREinvoice_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:
-If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
-If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
-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).
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.