Query to Get Unapplied Receipts By Customer

SELECT   c.NAME batch_number, c.batch_date batch_date, a.receipt_number,

         a.receipt_date, a.deposit_date, e.account_number oracle_num,

         i.party_name customer_name, e.orig_system_reference minc_num,

         g.NAME collector, a.amount orig_receipt_amt,

         SUM (h.amount_applied) unapp_amt, b.user_name collector

    FROM ar.ar_cash_receipts_all a,

         applsys.fnd_user b,

         ar.ar_batches_all c,

         ar.ar_cash_receipt_history_all d,

         ar.hz_cust_accounts e,

         ar.hz_customer_profiles f,

         ar.ar_collectors g,

         ar.ar_receivable_applications_all h,

         ar.hz_parties i

   WHERE a.status = 'UNAPP'

     AND h.status = 'UNAPP'

     AND f.cust_account_id = a.pay_from_customer

     AND f.collector_id = g.collector_id

     AND f.site_use_id IS NOT NULL

     AND a.created_by = b.user_id

     AND d.batch_id = c.batch_id

     AND i.party_id = e.party_id

     AND a.cash_receipt_id = d.cash_receipt_id

     AND a.cash_receipt_id = h.cash_receipt_id

     AND a.pay_from_customer = e.cust_account_id

GROUP BY c.NAME,

         c.batch_date,

         a.receipt_number,

         a.receipt_date,

         a.deposit_date,

         a.amount,

         e.account_number,

         i.party_name,

         e.orig_system_reference,

         g.NAME,

         b.user_name