Oracle apps technical tutorial

Oracle Apps: SQL Query to get Invoice approval Status and Posting status

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.

One Reply to “Oracle Apps: SQL Query to get Invoice approval Status and Posting status”

Leave a Reply