Task:

How to find a AP invoice is validated or not through a SQL query

Script:
SELECT i.invoice_id,
       i.invoice_amount,
       DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(i.invoice_id, i.invoice_amount,i.payment_status_flag,i.invoice_type_lookup_code),
               'NEVER APPROVED', 'Never Validated',
               'NEEDS REAPPROVAL', 'Needs Revalidation',
               'CANCELLED', 'Cancelled',
               'Validated') INVOICE_STATUS
  FROM ap_invoices_all i
 WHERE i.invoice_num = '&Invoice_Number';

Notes:
Invoice Distributions are validated individually and the status is stored at AP_INVOICE_DISTRIBUTIONS_ALL.match_status_flag

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

AP_INV_APRVL_HIST_ALL table will give you all the details related to approval of particular invoice.

Query to get the latest transaction on particular invoice :

select created_by from AP_INV_APRVL_HIST_ALL

where invoice_id = &inv_id

and approval_history_id =

(select max(approval_history_id)   from AP_INV_APRVL_HIST_ALL 

   where invoice_id = &inv_id 

)

Query to get the Initiator of particular invoice :

select created_by from AP_INV_APRVL_HIST_ALL

where invoice_id = &inv_id

and approval_history_id =

(select min(approval_history_id) from AP_INV_APRVL_HIST_ALL 

 where invoice_id = &inv_id 

)