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
,AP_INVOICES_PKG.GET_POSTING_STATUS( D.INVOICE_ID) "Accounting 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