(See Metalink doc ID 301806.1)
There is no column in the AP_INVOICES_ALL table that stores the validation status. 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).
See Comment 1 for example SQL
SELECT aia.invoice_num , aia.invoice_date, apsa.due_date, aia.invoice_amount, aia.amount_paid, aia.invoice_type_lookup_code, ap_invoices_pkg.get_approval_status (aia.invoice_id ,aia.invoice_amount ,aia.payment_status_flag ,aia.invoice_type_lookup_code ) approval_status, aia.wfapproval_status, aia.payment_status_flag, CASE WHEN NOT EXISTS ( SELECT match_status_flag FROM ap_invoice_distributions_all aida WHERE aida.invoice_id = aia.invoice_id AND NVL(match_status_flag,'N') != 'A' ) THEN 'Validated' WHEN NOT EXISTS ( SELECT match_status_flag FROM ap_invoice_distributions_all aida WHERE aida.invoice_id = aia.invoice_id AND NVL(match_status_flag,'N') != 'N' ) THEN 'Never Validated' ELSE 'Needs Revalidation' END validation_statusFROM ap_invoices_all aia, ap_payment_schedules_all apsaWHERE apsa.invoice_id = aia.invoice_id
LikeLike