create view vendor_payment_activity as
select
vp.fin_document_key as fin_document_key,
vpa.post_date as post_date,
case --the payment amount is null by definition if this is an application document
when vp.applied_fin_document_key is null then sum(vpa.payment_amount)
else 0
end as payment_amount,
case --the balance is null by definition if this is an application document or a voiding document
when vp.applied_fin_document_key is null and fd.voided_fin_document_key is null then sum(vpa.payment_amount) - sum(vpa.applied) - sum(vpa.application)
else 0
end as balance,
sum(vpa.applied) as applied,
sum(vpa.discount) as discount
from
(
-- Select the vendor payment itself - includes voiding documents
select
vp.fin_document_key as fin_document_key,
fd.post_date as post_date,
vp.payment_amount as payment_amount,
0 as applied,
0 as discount,
0 as application
from vendor_payment vp
join fin_document fd on fd.fin_document_key = vp.fin_document_key
union all
-- Select the sum of the detail lines included in the vendor payment itself
select
fd.fin_document_key as fin_document_key,
fd.post_date as post_date,
0 as payment_amount,
sum(coalesce(fdd.debit_amount, 0) - coalesce(fdd.credit_amount, 0)) as applied,
0 as discount,
0 as application
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 vendor payment itself
select
fd.fin_document_key as fin_document_key,
fd.post_date as post_date,
0 as payment_amount,
coalesce(sum(vpi.applied_amount), 0) as applied,
coalesce(sum(vpi.discount_amount), 0) as discount,
0 as application
from vendor_payment_included vpi
join fin_document fd on fd.fin_document_key = vpi.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 vendor payment
select
vp.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
from vendor_payment vp
join fin_document fd on fd.fin_document_key = vp.fin_document_key
left outer join (
-- get any detail lines
select
sum(coalesce(fdd.debit_amount, 0) - coalesce(fdd.credit_amount, 0)) as applied,
vp.fin_document_key as fin_document_key
from fin_document_detail fdd
join vendor_payment vp on vp.fin_document_key = fdd.fin_document_key
group by
vp.fin_document_key
) application_detail on application_detail.fin_document_key = vp.fin_document_key left outer join
(
-- get any included documents
select
coalesce(sum(vpi.applied_amount), 0) as applied,
vpi.fin_document_key as fin_document_key
from vendor_payment_included vpi
group by
vpi.fin_document_key
) application_included on application_included.fin_document_key = vp.fin_document_key
where vp.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
vpi.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(vpi.applied_amount), 0) as application
from vendor_payment_included vpi
join fin_document fd on fd.fin_document_key = vpi.fin_document_key
join fin_document fdi on fdi.fin_document_key = vpi.included_fin_document_key
where fd.posted_timestamp is not null and
fdi.document_type = 1 --limits to vendor payment documents only
group by
vpi.included_fin_document_key,
fd.post_date
union all
-- Select voiding document for the vendor 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,
-vp.payment_amount as application
from vendor_payment vp
join fin_document fd on fd.fin_document_key = vp.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 vendor 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.debit_amount, 0) - coalesce(fdd.credit_amount, 0)) as application
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 vendor 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(vpi.applied_amount), 0) as application
from vendor_payment_included vpi
join fin_document fd on fd.fin_document_key = vpi.fin_document_key and fd.voided_fin_document_key is not null
group by
fd.voided_fin_document_key,
fd.post_date
) vpa
join vendor_payment vp on vp.fin_document_key = vpa.fin_document_key
join fin_document fd on vp.fin_document_key = fd.fin_document_key
group by
vp.fin_document_key,
vp.applied_fin_document_key,
fd.voided_fin_document_key,
vpa.post_date
|