View: vendor_invoice_activity

Columns
  Column Name Data Type
1.   fin_document_key decimal(15)
2.   post_date timestamp
3.   invoice_amount decimal(18,2)
4.   applied decimal(38,2)
5.   discount decimal(38,2)
Table/Column Dependencies
Table Column
fin_document fin_document_key
  post_date
  posted_timestamp
  voided_fin_document_key
vendor_invoice fin_document_key
  invoice_amount
vendor_payment_included applied_amount
  discount_amount
  fin_document_key
  included_fin_document_key
SQL Server Create Statement
-- (7) Vendor Invoice Activity View -----------------------------------------------------------
create view vendor_invoice_activity as
select
   coalesce(fd.voided_fin_document_key, vi.fin_document_key) fin_document_key,
   fd.post_date post_date,
   vi.invoice_amount invoice_amount,
   0 applied,
   0 discount
from vendor_invoice vi
join fin_document fd on fd.fin_document_key = vi.fin_document_key
where fd.posted_timestamp is not null
union all
select
   vpi.included_fin_document_key,
   fd.post_date post_date,
   0 invoice_amount,
   sum(vpi.applied_amount) applied,
   sum(vpi.discount_amount) discount
from vendor_payment_included vpi
join vendor_invoice vi on vi.fin_document_key = vpi.included_fin_document_key
join fin_document fd on fd.fin_document_key = vpi.fin_document_key
where fd.posted_timestamp is not null
group by vpi.included_fin_document_key, fd.post_date