API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)

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.