View: customer_invoice_activity

Columns
  Column Name Data Type
1.   invoice_key decimal(15)
2.   post_date timestamp
3.   invoice_amount decimal(18,2)
4.   applied decimal(38,2)
5.   discount decimal(38,2)
6.   writeoff decimal(38,2)
Table/Column Dependencies
Table Column
customer_payment_included_inv applied_amount
  discount_amount
  fin_document_key
  invoice_key
  writeoff_amount
fin_document fin_document_key
  post_date
  posted_timestamp
invoice amount
  completed_date
  invoice_key
  post_date
  voided_invoice_key
SQL Server Create Statement
-- (8) Customer Invoice Activity View ------------------------------------------------------
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