Query to Find the Validated Invoices in AP

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 Distributions level.  This status is stored in  the match_status_flag column of the ap_invoice_distributions_all table (AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG).

Valid values for this column are: 

   A - Validated (it used to be called Approved)

   N or null - Never validated

   T - Tested but not validated

   S - Stopped

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' 

·         If MATCH_STATUS_FLAG is 'T' on ALL the distributions and org has no encumbrance enabled then Invoice would show Validated (provided there is no Unreleased Hold)

·          

Never Validated:

·         If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'

·          

Needs Revalidation:

·         If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T' and the org has Encumbrance enabled

·         If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed)

·         If the invoice distributions have MATCH_STATUS_FLAG value = 'S' (stopped)

·         If there are any rows in AP_HOLDS that do not have a release code MATCH_STATUS_FLAG would remain 'T' if invoice has hold which does not allow Accounting. In such scenario you can not account the invoice because event status will be 'I'.
As soon as Hold is released from Holds Tab/Invoice Workbench event status is set to 'U'. Invoice is shown as Validated and accounting is allowed. Match_Status_Flag still remains 'T'.

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 = 'INV_TEST_001';

SELECT

  v.vendor_id,

  segment1 vendor_num,

  s.vendor_name,

  ss.vendor_site_code site_name,

  CAST (invoice_num AS VARCHAR (20)) doc_number,

  invoice_type_lookup_code,

  DECODE (apps.ap_invoices_pkg.get_approval_status (invoice_id, invoice_amount,payment_status_flag, invoice_type_lookup_code ),

          'FULL', 'Fully Applied'

          ,'UNAPPROVED', 'Unvalidated'

          ,'NEEDS REAPPROVAL', 'Needs Revalidation'

          ,'APPROVED', 'Validated'

          ,'NEVER APPROVED', 'Never Validated'

          ,'CANCELLED','Cancelled'

          ,'UNPAID', 'Unpaid'

          ,'AVAILABLE', 'Available'

         ) invoice_check_status,

  invoice_date doc_date,

  v.invoice_currency_code doc_currency,

  exchange_rate,

  exchange_date,

  gl_date,

  payment_status_flag,

  ap_invoices_pkg.get_amount_withheld (invoice_id) awt_amount

FROM

  ap_invoices_all v,

  ap_suppliers s,

  ap_supplier_sites_all ss

WHERE 1=1

  --AND s.segment1 = :supplier_number

  --AND v.invoice_currency_code = :mycurrency

AND v.vendor_id = ss.vendor_id

AND v.vendor_id = s.vendor_id

  --AND v.org_id = :org_id

AND v.vendor_site_id = ss.vendor_site_id