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
|