OpenReceipt Report DescriptionThis Open Receipt Re…

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
  • January 5, 2017 | 17 views