Query to get accounted invoices in oracle apps

Query to get accounted invoices in oracle apps

In this post , we will discuss about Query to get accounted invoices in oracle apps. There are many steps involved after creating AP invoices in oracle apps. In that steps , Invoice Accounting is also one of the Important step. Only Validated Invoices can accounted. Accounting means to drive the values from transactions to the actual GL account. Here below I will share the SQL query to get accounted invoices in oracle apps.

 

 

 

Tables uses in the Query to get accounted invoices

1.ap_invoices_all   

2.ap_suppliers

3.ap_supplier_sites_all

4.po_headers_all

5.ap_terms

6.hr_operating_units

SQL Query to get accounted invoices in oracle apps

select aps.vendor_name,
apss.vendor_site_code,
aia.invoice_num,     
aia.invoice_date,
aia.invoice_amount,

pha.segment1 po_number,

apt.name Term_name,     
hou.name operating_unit,
DECODE(aia.PAYMENT_STATUS_FLAG,'N','UN-PAID','P','Partial Paid','Y','PAID') PAYMENT_STATUS_FLAG
from apps.ap_invoices_all       aia,

ap_suppliers aps,

ap_supplier_sites_all apss,

po_headers_all pha,

ap_terms apt,

hr_operating_units hou

where 1=1

and aia.vendor_id=aps.vendor_id

and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID

 

AND aps.vendor_id=apss.VENDOR_ID

 

AND AP_INVOICES_PKG.GET_APPROVAL_STATUS( AIA.INVOICE_ID,
   AIA.INVOICE_AMOUNT, AIA.PAYMENT_STATUS_FLAG,
   AIA.INVOICE_TYPE_LOOKUP_CODE) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(AIA.INVOICE_ID)='P'

and aia.po_header_id=pha.po_header_id(+)

and apt.term_id=aia.terms_id

 and hou.organization_id=aia.org_id

and aia.ORG_ID=:P_ORG_ID