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