Validated-unpaid-invoices-in-AP

SELECT

haou.name ”Entity”,

aia.invoice_num “Invoice Number”,

aia.voucher_num “Voucher Number”,

aia.description “Invoice Description”,

pv.vendor_name “Supplier”,

pvs.vendor_site_code “Supplier Site”,

aia.invoice_amount “Invoice Amount”,

aia.amount_paid “Amount Paid”,

aia.invoice_currency_code “Currency”,

aia.invoice_date “Invoice Date”,

aia.gl_date “GL Date”,

apt.name ”Payment Terms”,

DECODE(APPS.Ap_Invoices_Pkg.GET_APPROVAL_STATUS(aia.INVOICE_ID,

aia.INVOICE_AMOUNT,aia.PAYMENT_STATUS_FLAG,aia.INVOICE_TYPE_LOOKUP_CODE),’NEVER

APPROVED’ , ‘Never Validated’,'NEEDS REAPPROVAL’, ‘Needs

Revalidation’,'Validated’) INVOICE_STATUS

FROM

gl_sets_of_books sob,

hr_all_organization_units haou,

ap_invoices_all aia,

ap_invoice_distributions_all aida,

ap_invoice_payments_all pay,

ap_checks_all aca,

ap_terms apt,

po_vendors pv,

po_vendor_sites_all pvs

WHERE aia.set_of_books_id = sob.set_of_books_id

AND aia.invoice_id = aida.invoice_id

AND aia.org_id = haou.organization_id

AND aia.terms_id = apt.term_id

AND aia.vendor_id = pv.vendor_id

AND aia.vendor_site_id = pvs.vendor_site_id

AND DECODE(APPS.Ap_Invoices_Pkg.GET_APPROVAL_STATUS(aia.INVOICE_ID,

aia.INVOICE_AMOUNT,aia.PAYMENT_STATUS_FLAG,aia.INVOICE_TYPE_LOOKUP_CODE),’NEVER

APPROVED’ , ‘Never Validated’,'NEEDS REAPPROVAL’, ‘Needs

Revalidation’,'Validated’) = ‘Validated’

AND aia.payment_status_flag != ‘Y’