Thursday, August 20, 2015

AP Payables Unpaid Invoices

Below is Simple Query to get Query to get UNPAID Invoices (Oracle Payables). I have alos build condition to exclude ZERO Dollar Invoices.

select i.invoice_num,v.vendor_name,i.invoice_date,ps.due_date,
i.invoice_amount,i.amount_paid,ps.amount_remaining, SUM(i.invoice_amount),sum(ps.amount_remaining)
FROM    ap_payment_schedules_all ps,
        ap_invoices_all i,
        po_vendors v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.vendor_id = v.vendor_id
AND     i.vendor_site_id = vs.vendor_site_id
AND     i.payment_status_flag ='N'
AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
AND    i.cancelled_date is not null
group by v.vendor_name,i.invoice_num,i.invoice_date,ps.due_date,i.invoice_amount,i.amount_paid,
Order by v.vendor_name,i.invoice_num

No comments:

Post a Comment