Thursday, June 30, 2016

How to get AP Invoice Status?


 How to get the Invoice status from tables in Oracle EBS:


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

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'.

 1.Match_status_flag = Null:  Invoice is Never Validated status

2.Match_status_flag=’A’ :   Invoice is Validated Status

3.Match_status_flag=’N’:  Invoice is Need Revalidation status


SQL Query to  display AP Invoice Status:

SQL1:
select
Invoice_num,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 Status"
                                from ap_invoices_all
            where invoice_num= &Invoice_num; ---- provide the invoice number in quotes i.e 'Invoice_Num';

SQL2:
select
 aia.invoice_id,aia.invoice_num,aia.invoice_date,aia.invoice_amount,
 alc.LOOKUP_TYPE,alc.LOOKUP_CODE,alc.displayed_field " Invoice Status"
 from  ap_invoices_all aia,
 ap_lookup_codes alc
 where  alc.LOOKUP_TYPE='INVOICE STATUS'
AND alc.LOOKUP_CODE IN('APPROVED','UNAPPROVED','CANCELLED')
AND AIA.invoice_num = &Invoice_num; ---- provide the invoice number in quotes i.e 'Invoice_Num' ;