AP Invoice Validation Status

(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

1 thought on “AP Invoice Validation Status

  1. 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_status
    FROM ap_invoices_all aia
    , ap_payment_schedules_all apsa
    WHERE apsa.invoice_id = aia.invoice_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s