Oracle apps technical tutorial

Query to Extract invoices which needs Re-validation in Oracle Apps

Query to Extract invoices which needs Re-validation in Oracle Apps

Validation status of an Invoice is not available in base table, It has to be picked after calling a standard API.

Below query will give you the list invoices that has to be re-validated.


select aps.INVOICE_NUM, 
    TO_CHAR(aps.invoice_date, 'DD-MON-YYYY') INVOICE_DATE,
    TO_CHAR(aps.creation_date, 'DD-MON-YYYY') CREATION_DATE,
    ASS.VENDOR_NAME,
    DECODE (aps.payment_status_flag,
                       'P', 'Partially Paid',
                       'Y', 'Paid',
                       'N', 'Not Paid'
                      ) payment_status
from ap_invoices_all aps
, ap_suppliers ass
where ass.vendor_id = aps.vendor_id
and APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(aps.invoice_id, aps.invoice_amount, aps.payment_status_flag, aps.invoice_type_lookup_code)= 'NEEDS REAPPROVAL'
AND APS.pay_group_lookup_code = 'VENDOR'
and aps.creation_date between :1 and :2

Query to Extract invoices which needs Re-validation in Oracle Apps

Leave a Reply