View: customer_payment_activity

Columns
  Column Name Data Type
1.   fin_document_key decimal(15)
2.   post_date timestamp
3.   payment_amount decimal(38,2)
4.   balance decimal(38,2)
5.   applied decimal(38,2)
6.   discount decimal(38,2)
7.   writeoff decimal(38,2)
Table/Column Dependencies
Table Column
customer_payment applied_fin_document_key
  fin_document_key
  payment_amount
customer_payment_included_inv applied_amount
  discount_amount
  fin_document_key
  writeoff_amount
customer_payment_included_pmt applied_amount
  fin_document_key
  included_fin_document_key
fin_document fin_document_key
  post_date
  posted_timestamp
  voided_fin_document_key
fin_document_detail credit_amount
  debit_amount
  fin_document_key
SQL Server Create Statement
-- (6) Customer Payment Activity View ------------------------------------------
--The customer_payment_activity view serves as the go-to source for customer payment balance information.  The values retrieved by the view include the following:
--
--fin_document_key  - unique key of the customer payment.  All customer payments (posted and unposted) are retrieved by the view.
--post_date         - post date of the customer payment. NOTE that you will still need to restrict documents based on whether fin_document.posted_timestamp IS NULL
--                 to determine whether a customer payment has actaully been posted
--payment_amount    - payment amount of the customer payment.  This value is NULL for an application document.
--balance           - For UNPOSTED payments, this is the payment amount less the sum of the details and the sum of the applied amounts included in the document.
--                 For POSTED payments, this represents
--                  * the balance of the document
--                  * less the sum of the details and the sum of the applied amounts included in any related POSTED application documents
--                  * less the sum of any applications of the document in other payments
--                  * less the net effect of the voiding of the payment (e.g. voiding has the effect of bringing the balance to zero unless
--                    any there are any applications of the document in other payments - this will need to be handled separately
--                 This value is always NULL for an application or voiding document
--applied           - the sum of the applied amounts included in the document
--discount       - the sum of the discount amounts included in the document
--writeoff       - the sum of the writeoff amounts included in the document
create view customer_payment_activity as
select
   cp.fin_document_key as fin_document_key,
   cpa.post_date as post_date,
   case when cp.applied_fin_document_key is null
      then sum(cpa.payment_amount)
      else 0
   end as payment_amount,
   case when cp.applied_fin_document_key is null and fd.voided_fin_document_key is null
      then sum(cpa.payment_amount) - sum(cpa.applied) - sum(cpa.application)
      else 0
   end as balance,
   sum(cpa.applied) as applied,
   sum(cpa.discount) as discount,
   sum(cpa.writeoff) as writeoff
from (
   -- Select the customer payment itself - includes voiding documents
   select
      cp.fin_document_key as fin_document_key,
      fd.post_date as post_date,
      cp.payment_amount as payment_amount,
      0 as applied,
      0 as discount,
      0 as application,
      0 as writeoff
   from customer_payment cp
   join fin_document fd on fd.fin_document_key = cp.fin_document_key
   union all

   -- Select the sum of the detail lines included in the customer payment itself
   select
      fd.fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      sum(coalesce(fdd.credit_amount, 0) - coalesce(fdd.debit_amount, 0)) as applied,
      0 as discount,
      0 as application,
      0 as writeoff
   from fin_document_detail fdd
   join fin_document fd on fd.fin_document_key = fdd.fin_document_key
   group by
      fd.fin_document_key,
      fd.post_date
   union all

   -- Select the included payment applications from the customer payment itself
   select
      fd.fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      coalesce(sum(cpip.applied_amount), 0) as applied,
      0 as discount,
      0 as application,
      0 as writeoff
   from customer_payment_included_pmt cpip
   join fin_document fd on fd.fin_document_key = cpip.fin_document_key
   group by
      fd.fin_document_key,
      fd.post_date
   union all

   -- Select the included invoice applications from the customer payment itself
   select
      fd.fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      coalesce(sum(cpii.applied_amount), 0) as applied,
      coalesce(sum(cpii.discount_amount), 0) as discount,
      0 as application,
      coalesce(sum(cpii.writeoff_amount), 0) as writeoff
   from customer_payment_included_inv cpii
   join fin_document fd on fd.fin_document_key = cpii.fin_document_key
   group by
      fd.fin_document_key,
      fd.post_date
   union all

   -- Select the posted sum of the detail lines and the included payment applications included in POSTED application documents related to the original customer payment
   select
      cp.applied_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      coalesce(application_detail.applied, 0) + coalesce(application_included.applied, 0) as application,
      0 as writeoff
   from customer_payment cp
   join fin_document fd on fd.fin_document_key = cp.fin_document_key
   left outer join (
      -- get any detail lines
      select
         sum(coalesce(fdd.credit_amount, 0) - coalesce(fdd.debit_amount, 0)) as applied,
         cp.fin_document_key as fin_document_key
      from fin_document_detail fdd
      join customer_payment cp on cp.fin_document_key = fdd.fin_document_key
      group by
         cp.fin_document_key
   ) application_detail on application_detail.fin_document_key = cp.fin_document_key
   left outer join (
      -- get any included documents
      select
         coalesce(sum(cpi.applied_amount), 0) as applied,
         cpi.fin_document_key as fin_document_key
      from (
         select
            cpip.fin_document_key,
            cpip.applied_amount
         from customer_payment_included_pmt cpip
         union all
         select
            cpii.fin_document_key,
            cpii.applied_amount
         from customer_payment_included_inv cpii
      ) cpi
      group by cpi.fin_document_key
   ) application_included on application_included.fin_document_key = cp.fin_document_key
   where
      cp.applied_fin_document_key is not null
      and fd.posted_timestamp is not null
   union all

   -- Select the sum of payment applications of the current payment included in other POSTED vendor payments
   select
      cpip.included_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      coalesce(-sum(cpip.applied_amount), 0) as application,
      0 as writeoff
   from customer_payment_included_pmt cpip
   join fin_document fd on fd.fin_document_key = cpip.fin_document_key
   where fd.posted_timestamp is not null
   group by
      cpip.included_fin_document_key,
      fd.post_date
   union all

   -- Select voiding document for the customer payment itself - treated as a reduction in the balance
   select
      fd.voided_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      -cp.payment_amount as application,
      0 as writeoff
   from
      customer_payment cp
      join fin_document fd on fd.fin_document_key = cp.fin_document_key and fd.voided_fin_document_key is not null
   union all

   -- Select the sum of the detail lines included in the voiding document for the customer payment itself - treated as an offset to the reduction in the balance
   select
      fd.voided_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      sum(coalesce(fdd.credit_amount, 0) - coalesce(fdd.debit_amount, 0)) as application,
      0 as writeoff
   from fin_document_detail fdd
   join fin_document fd on fd.fin_document_key = fdd.fin_document_key and fd.voided_fin_document_key is not null
   group by fd.voided_fin_document_key, fd.post_date
   union all

   -- Select the included payment applications included in the voiding document for the customer payment itself - treated as an offset to the reduction in the balance
   select
      fd.voided_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      coalesce(sum(cpip.applied_amount), 0) as application,
      0 as writeoff
   from customer_payment_included_pmt cpip
   join fin_document fd on fd.fin_document_key = cpip.fin_document_key and fd.voided_fin_document_key is not null
   group by
      fd.voided_fin_document_key,
      fd.post_date
   union all

   -- Select the included invoice applications included in the voiding document for the customer payment itself - treated as an offset to the reduction in the balance
   select
      fd.voided_fin_document_key as fin_document_key,
      fd.post_date as post_date,
      0 as payment_amount,
      0 as applied,
      0 as discount,
      coalesce(sum(cpii.applied_amount), 0) as application,
      0 as writeoff
   from customer_payment_included_inv cpii
   join fin_document fd on fd.fin_document_key = cpii.fin_document_key and fd.voided_fin_document_key is not null
   group by
      fd.voided_fin_document_key,
      fd.post_date
) cpa
join customer_payment cp on cp.fin_document_key = cpa.fin_document_key
join fin_document fd on cp.fin_document_key = fd.fin_document_key
group by
   cp.applied_fin_document_key,
   cp.fin_document_key,
   fd.voided_fin_document_key,
   cpa.post_date