Thursday 5 January 2017

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

1 comment:

  1. I really like your blog and the given information is very useful for me thanks for sharing.

    Gold And Silver Dealers In San Francisco

    ReplyDelete