SELECT b.segment1 vendor_number,
b.vendor_name
vendor_name,
c.vendor_site_code,
c.pay_group_lookup_code,
a.invoice_num
invoice_number,
a.invoice_date,
a.gl_date,
d.due_date,
a.invoice_currency_code,
a.invoice_amount,
a.amount_paid,
a.pay_group_lookup_code,
d.payment_priority,
(SELECT MAX (check_date)
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)
"Last Payment Made on",
a.cancelled_date
FROM apps.ap_invoices_all a,
apps.ap_suppliers
b,
apps.ap_supplier_sites_all
c,
apps.ap_payment_schedules_all
d,
apps.ap_invoice_payments_all
ap,
ap_checks_all ac
WHERE a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND b.vendor_id = c.vendor_id
AND a.invoice_id = d.invoice_id
AND ap.invoice_id = a.invoice_id
AND ac.CHECK_ID = ap.CHECK_ID
and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
AND a.org_id = 89
and a.invoice_id= 1234
AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')