Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 5 February 2017

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
Share this article :

0 comments:

Post a Comment