create view customer_invoice_activity as
select
coalesce(i.voided_invoice_key, i.invoice_key) invoice_key,
i.post_date post_date,
i.amount invoice_amount,
0 applied,
0 discount,
0 writeoff
from invoice i
where i.completed_date is not null
union all
select
cpii.invoice_key invoice_key,
fd.post_date post_date,
0 invoice_amount,
sum(cpii.applied_amount) applied,
sum(cpii.discount_amount) discount,
sum(cpii.writeoff_amount) writeoff
from customer_payment_included_inv cpii
join fin_document fd on fd.fin_document_key = cpii.fin_document_key
where fd.posted_timestamp is not null
group by cpii.invoice_key, fd.post_date
|