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

2 comments:

  1. eToro is the ultimate forex trading platform for new and pro traders.

    ReplyDelete
  2. QUANTUM BINARY SIGNALS

    Professional trading signals delivered to your mobile phone daily.

    Start following our signals today and profit up to 270% per day.

    ReplyDelete