Tuesday, 15 May 2012

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.

No comments:

Post a Comment