Oracle Apps: SQL Query to get Invoice approval Status and Posting status
If you want to extract AP invoices with their approval status and posted status, can use below query to get that.
To get the AP Invoices approval status, we use AP_INVOICES_utility_PKG.GET_APPROVAL_STATUS and for POSTING status, we will use ap_invoices_pkg.get_posting_status
SELECT AI.INVOICE_ID ,INVOICE_NUM "INVOICE NUMBER" ,AP_INVOICES_utility_PKG.GET_APPROVAL_STATUS ( AI.INVOICE_ID ,AI.INVOICE_AMOUNT ,AI.PAYMENT_STATUS_FLAG ,AI.INVOICE_TYPE_LOOKUP_CODE ) "Validation Status" ,AI.INVOICE_AMOUNT ,AI.PAYMENT_STATUS_FLAG ,AI.INVOICE_TYPE_LOOKUP_CODE ,AID.accounting_date ,AI.ORG_ID ,HOU.NAME OU_NAME ,ap_invoices_pkg.get_posting_status(AI.INVOICE_ID) Accounting_Status -- ,HOU.SHORT_CODE from apps.ap_invoice_distributions_all AID ,apps.ap_invoices_all AI ,APPS.HR_ALL_ORGANIZATION_UNITS HOU where aiD.accounting_date between :1 and :2 AND AID.invoice_id = AI.invoice_id AND HOU.organization_id = AI.ORG_ID
Hope this query helps you, if you have any further query, put it in comment box.