[Fusion] Checking AP Invoice Validation Status in Oracle Fusion via SQL in Oracle Fusion BIP

 

Objective

In Oracle Fusion Payables, verifying whether an invoice is "Validated" isn’t as simple as checking a single status column. The system determines this status dynamically based on distribution lines and active holds. This post provides a reliable SQL approach to identifying this status.

Key Logic for Validation

• Validated: All invoice distributions must have MATCH_STATUS_FLAG = 'A' and there must be no unreleased holds.
• Never Validated: All distributions have a MATCH_STATUS_FLAG of NULL or 'N'.
• Needs Revalidation: Any distribution has a flag of 'T' (Tested), 'S' (Stopped), or if there is an unreleased row in the AP_HOLDS_ALL table. 

Quick Table Reference for MATCH_STATUS_FLAG:

SQL Query

We can use the below query to get validation status:

SELECT
    aia.invoice_num,

    aia.invoice_date,
    aia.vendor_id,

    CASE
        -- Case 1: No distributions have been processed (All match_status_flag are NULL)

        WHEN NOT EXISTS (SELECT 1 FROM ap_invoice_distributions_all aid
                         WHERE aid.invoice_id = aia.invoice_id

                         AND aid.match_status_flag IS NOT NULL) THEN 'Never Validated'
       

        -- Case 2: There are active (unreleased) holds
        WHEN EXISTS (SELECT 1 FROM ap_holds_all ah

                     WHERE ah.invoice_id = aia.invoice_id
                     AND ah.release_lookup_code IS NULL) THEN 'Needs Revalidation (Hold)'

       
        -- Case 3: All distributions are marked as 'A' (Approved/Validated) and there is no match_status_flag as NULL

        WHEN NOT EXISTS (SELECT 1 FROM ap_invoice_distributions_all aid
                         WHERE aid.invoice_id = aia.invoice_id

                         AND (aid.match_status_flag <> 'A' OR aid.match_status_flag IS NULL)) THEN 'Validated'
       

        -- Case 4: Any other state
        ELSE 'Needs Revalidation'

    END AS validation_status
FROM

    ap_invoices_all aia
WHERE

    aia.invoice_num = :P_INVOICE_NUM;

Description

The validation status is a calculated value. In the backend, it relies on two primary data points:

1. MATCH_STATUS_FLAG: Located in the AP_INVOICE_DISTRIBUTIONS_ALL table. A value of 'A' indicates the line is approved/validated.
2. AP_HOLDS_ALL: If a record exists in this table without a RELEASE_LOOKUP_CODE, the invoice is on hold, even if the distributions look correct.

Now using this we can now extract all validated invoices using below query:

SELECT
    aia.invoice_num,

    aia.invoice_amount,
    aia.invoice_date,

    aia.vendor_id,
    'Validated' as validation_status

FROM
    ap_invoices_all aia

WHERE
    -- 1. Ensure all distributions are validated ('A')

    NOT EXISTS (
        SELECT 1

        FROM ap_invoice_distributions_all aid
        WHERE aid.invoice_id = aia.invoice_id

        AND (aid.match_status_flag <> 'A' OR aid.match_status_flag IS NULL)
    )

    -- 2. Ensure at least one distribution exists (prevents 'Never Validated' appearing)
    AND EXISTS (

        SELECT 1
        FROM ap_invoice_distributions_all aid

        WHERE aid.invoice_id = aia.invoice_id
    )

    -- 3. Ensure there are no active holds
    AND NOT EXISTS (

        SELECT 1
        FROM ap_holds_all ah

        WHERE ah.invoice_id = aia.invoice_id
        AND ah.release_lookup_code IS NULL

    );

Conclusion

By joining the headers, distributions, and holds tables, you can accurately replicate the "Validation Status" seen in the Oracle Fusion UI. Understanding the MATCH_STATUS_FLAG is the key to mastering AP reporting. Always ensure that both the distribution flags are set to 'A' and no unreleased holds exist before expecting an invoice to be eligible for payment.