Friday, March 27, 2015

Oracle Apps R12 Query to Find Payment against Invoice



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')