Query: Cancelled invoices

 

Query: Cancelled invoices

SELECT
distinct AIA.INVOICE_ID, aia.invoice_num, AIA.INVOICE_AMOUNT,
 AIA.INVOICE_TYPE_LOOKUP_CODE,
DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
              AIA.INVOICE_AMOUNT,
     AIA.PAYMENT_STATUS_FLAG,              
    AIA.INVOICE_TYPE_LOOKUP_CODE),
                       'NEVER APPROVED', 'Never Validated',
                       'NEEDS REAPPROVAL', 'Needs Revalidation',
                       'APPROVED',  'Validated',
                       'CANCELLED',      'Cancelled',
                       'AVAILABLE PREPAYMENT', 'Available Prepayment',
                       'AVAILABLE', 'Available') INVOICE_STATUS
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_TYPE_LOOKUP_CODE = 'AWT'
 AND   AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,
                AIA.INVOICE_AMOUNT,
                AIA.PAYMENT_STATUS_FLAG,
                AIA.INVOICE_TYPE_LOOKUP_CODE) = 'NEVER APPROVED'