ORACLE MASTERMINDS

Open Receipt Report

Description

This Open Receipt Report is used to review a list of receipts which are in open status for the date  range that you specify.

SELECT coll.coll_name "COLLECTOR", cons.group_name "PARENT",

       cons.group_acc "GROUP_ACC", hca.account_number "CUST_NUMBER",

       hp.party_name "CUSTOMER_NAME", rcpt.posted_date "POSTED_DATE",

       rcpt.receipt_date "RCPT_DATE", rcpt.currency "CURRENCY",

       rcpt.receipt_method "RCPT_METHOD", rcpt.receipt_number "RCPT_NUMBER",

       rcpt.stat "RCPT_STATUS", rcpt.receipt_amount "RCPT_AMOUNT",

       ROUND (gbp_receipt, 2) "GBP_RECEIPT",

       ROUND (CASE

                 WHEN rcpt.receipt_date >= '01-SEP-2010'

                    THEN gbp_receipt

                 ELSE 0

              END,

              2

             ) "CURRENT_MONTH_GBP",

       rcpt.gbp_remaining "GBP_REMAINING",

       ROUND

           (CASE

               WHEN rcpt.receipt_date < SYSDATE - 1

               AND rcpt.receipt_date > SYSDATE - 31

                  THEN rcpt.gbp_remaining

               ELSE 0

            END,

            2

           ) "DAYS_1_30",

       ROUND

          (CASE

              WHEN rcpt.receipt_date < SYSDATE - 31

              AND rcpt.receipt_date > SYSDATE - 61

                 THEN rcpt.gbp_remaining

              ELSE 0

           END,

           2

          ) "DAYS_31_60",

       ROUND

          (CASE

              WHEN rcpt.receipt_date < SYSDATE - 61

              AND rcpt.receipt_date > SYSDATE - 91

                 THEN rcpt.gbp_remaining

              ELSE 0

           END,

           2

          ) "DAYS_61_90",

       ROUND

          (CASE

              WHEN rcpt.receipt_date < SYSDATE - 91

              AND rcpt.receipt_date > SYSDATE - 181

                 THEN rcpt.gbp_remaining

              ELSE 0

           END,

           2

          ) "DAYS_91_180",

       ROUND

          (CASE

              WHEN rcpt.receipt_date < SYSDATE - 181

              AND rcpt.receipt_date > SYSDATE - 361

                 THEN rcpt.gbp_remaining

              ELSE 0

           END,

           2

          ) "DAYS_181_360",

       ROUND

            (CASE

                WHEN rcpt.receipt_date < SYSDATE - 361

                AND rcpt.receipt_date > SYSDATE - 999999

                   THEN rcpt.gbp_remaining

                ELSE 0

             END,

             2

            ) "DAYS_361",

       rcpt.reason_code "REASON_CODE", rcpt.narrative "NARRATIVE"

  FROM ar_payment_schedules_all aps,

       hz_parties hp,

       hz_cust_accounts hca,

      

----------------- collector ---------------------

       (SELECT ac.NAME coll_name, acp.credit_hold credit_hold,

               acp.cust_account_id cust_id

          FROM hz_cust_accounts hca2,

               hz_customer_profiles acp,

               ar_collectors ac

         WHERE acp.cust_account_id = hca2.cust_account_id

           AND acp.site_use_id IS NULL

           AND acp.collector_id = ac.collector_id(+)) coll,

      

       --------- sub select receipt data -----------

       (SELECT aps3.payment_schedule_id pid, aps3.customer_id cust_id,

               acr.status stat, acr.receipt_date receipt_date,

               acr.creation_date posted_date, acr.currency_code currency,

               acr.receipt_number receipt_number, acr.status status,

               arm.NAME receipt_method, acr.attribute4 reason_code,

               acr.attribute3 narrative, acr.amount receipt_amount,

               (CASE

                   WHEN aps3.CLASS = 'PMT' AND acr.status != 'REV'

                      THEN DECODE (aps3.invoice_currency_code,

                                   'GBP', (acr.amount),

                                   ((acr.amount * acr.exchange_rate)

                                   )

                                  )

                   ELSE 0

                END

               ) gbp_receipt,

               aps3.amount_due_remaining * -1 amt_remaining,

               (CASE

                   WHEN aps3.CLASS = 'PMT' AND aps3.amount_due_remaining <> 0

                      THEN DECODE (aps3.invoice_currency_code,

                                   'GBP', (aps3.amount_due_remaining * -1),

                                   (  (  aps3.amount_due_remaining

                                       * aps3.exchange_rate

                                      )

                                    * -1

                                   )

                                  )

                   ELSE 0

                END

               ) gbp_remaining

          FROM ar_payment_schedules_all aps3,

               ar_cash_receipts_all acr,

               ar_receipt_methods arm

         WHERE aps3.cash_receipt_id = acr.cash_receipt_id

           AND acr.receipt_method_id = arm.receipt_method_id

           AND aps3.CLASS = 'PMT'

           AND aps3.org_id = 210) rcpt,

      

--------------- sub select group ------------------

       (SELECT hcar.cust_account_id GROUP_ID, hcar.status status,

               hcar.related_cust_account_id related_id,

               hca2.account_number group_acc, hp2.party_name group_name

          FROM hz_cust_acct_relate_all hcar,

               hz_cust_accounts hca2,

               hz_parties hp2

         WHERE hp2.party_id = hca2.party_id

           AND hcar.cust_account_id = hca2.cust_account_id

           AND hcar.status = 'A'

           AND hcar.org_id = 210) cons

 WHERE aps.org_id = 210

   AND hp.party_id(+) = hca.party_id

   AND rcpt.pid = aps.payment_schedule_id

   AND coll.cust_id(+) = aps.customer_id

   AND rcpt.cust_id = hca.cust_account_id(+)

   AND cons.related_id(+) = hca.cust_account_id

   AND aps.amount_due_remaining <> 0

   By

   Deepak J