Monday 1 April 2013

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'

No comments:

Post a Comment