Query to get accounted ar invoices in oracle apps

Query to get accounted AR invoices in oracle apps

In this post , We will be discuss about Query to get accounted AR invoices in oracle apps. This sql query will help to extract the accounted AR invoices in oracle apps. We will be able to find out the details of accounted AR invoices in oracle apps using this sql query. We can also find out the other important information's related to AR invoices using this sql Query. Here below is the detail about Query to get accounted ar invoices in oracle apps.

Query to get accounted ar invoices in oracle apps

 

6 Tables used by Query to get accounted AR invoices in oracle apps

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 ar 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

Query to get accounted ar invoices in oracle apps