-- (7) 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
|