Query to get validated invoices in oracle apps

Query to get validated invoices in oracle apps

In this post , we will be discuss about Query to get validated invoices in oracle apps. In Oracle apps , validation is the process , which helps to validate the Invoice in oracle apps and that Invoice become eligible for payment after validation. Un validated invoices cannot be paid , once the Invoice will be validated then this Invoice will be paid so Invoice Validation is Quite Important step in the Oracle Apps. Here below I will share the complete sql query to get validated invoices in oracle apps.

 

 

 

Tables Uses in Query to get validated 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

 

Sample SQL Query to get validated 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 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