The blog provides the Oracle EBS R12 SQL to get AP invoices posted to  General Ledger (GL). The Account Payables (AP) Invoices should be validated and Posted for AP reconciliation during month end period closure.  Few are the validations given below which are being used to ensure that the Invoice has been accounted with approved Status and Posting Status.

Oracle EBS R12 AP invoice Status

There are 4 invoice status as  given below
‘S’ – Selected
‘P’ – Partial
‘N’ – Unposted
‘Y’ – Posted

1. Check  and ensure that the Invoice Id entry should be in the table – AP_INVOICE_DISTRIBUTIONS_ALL
2. Check  and ensure that the Invoice Id entry should be in the table – AP_SELF_ASSESSED_TAX_DIST_ALL
3. Check if DIST VAR hold is placed on this invoice. DIST VAR Hold  could be placed  when no distributions exist and the invoice status should be NEEDS REAPPROVAL
4. Check and ensure that there is NO Hold applied on the Invoice
5. Check if the Invoice is NOT Cancelled
6. Check Table – AP_INVOICE_DISTRIBUTIONS_ALL for the Invoice ID match_status_flag IS NOT NULL and count should be 0

SQL to get AP Invoices Validated but Not Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='N

SQL Query to get AP Invoices Validated & Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='P'