create view vendor_payment_1099_view as
-- vendor payment details for 1099-able vendor payments and applications
select
coalesce(vp.applied_fin_document_key, vp.fin_document_key) fin_document_key,
null including_fin_document_key,
null included_fin_document_key,
fdd.fin_document_detail_key detail_key,
null eda_key,
a.account_key,
'D' type,
a.category_1099,
null included_detail_amount,
fdd.debit_amount - fdd.credit_amount detail_1099_amount
from vendor_payment vp
join customer v on v.customer_key = vp.vendor_org_key
and v.vendor_1099 = 'Y'
join payment_method pm on pm.payment_method_key = vp.payment_method_key
and pm.include_in_1099 = 'Y'
join fin_document fd on fd.fin_document_key = vp.fin_document_key
and fd.posted_timestamp is not null
join fin_document_detail fdd on fdd.fin_document_key = fd.fin_document_key
join account a on a.account_key = fdd.account_key
union all
-- details for vendor invoices (not generated from eda) included in vendor payments and applications
select
coalesce(vp.applied_fin_document_key, vp.fin_document_key) fin_document_key,
vpi.fin_document_key including_fin_document_key,
vpi.included_fin_document_key included_fin_document_key,
fdd.fin_document_detail_key detail_key,
null eda_key,
a.account_key,
'VI' type,
a.category_1099,
fdd.debit_amount - fdd.credit_amount included_detail_amount,
round((vpi.applied_amount / vi.invoice_amount) * (fdd.debit_amount - fdd.credit_amount), 2) detail_1099_amount
from vendor_payment vp
join customer v on v.customer_key = vp.vendor_org_key
and v.vendor_1099 = 'Y'
join payment_method pm on pm.payment_method_key = vp.payment_method_key
and pm.include_in_1099 = 'Y'
join fin_document fd on fd.fin_document_key = vp.fin_document_key
and fd.posted_timestamp is not null
join vendor_payment_included vpi on vpi.fin_document_key = vp.fin_document_key
join vendor_invoice vi on vi.fin_document_key = vpi.included_fin_document_key
and not exists (select 1 from expense_data_allocation where vendor_invoice_key = vi.fin_document_key)
join fin_document_detail fdd on fdd.fin_document_key = vpi.included_fin_document_key
join account a on a.account_key = fdd.account_key
union all
-- eda for generated vendor invoices included in vendor payments and applications
select
coalesce(vp.applied_fin_document_key, vp.fin_document_key) fin_document_key,
vpi.fin_document_key including_fin_document_key,
vpi.included_fin_document_key included_fin_document_key,
fdd.fin_document_detail_key detail_key,
eda.expense_data_allocation_key eda_key,
a.account_key,
'E' type,
a.category_1099,
eda.amount included_detail_amount,
round((vpi.applied_amount / vi.invoice_amount) * eda.amount, 2) detail_1099_amount
from vendor_payment vp
join customer v on v.customer_key = vp.vendor_org_key
and v.vendor_1099 = 'Y'
join payment_method pm on pm.payment_method_key = vp.payment_method_key
and pm.include_in_1099 = 'Y'
join fin_document fd on fd.fin_document_key = vp.fin_document_key
and fd.posted_timestamp is not null
join vendor_payment_included vpi on vpi.fin_document_key = vp.fin_document_key
join vendor_invoice vi on vi.fin_document_key = vpi.included_fin_document_key
join expense_data_allocation eda on eda.vendor_invoice_key = vi.fin_document_key
join fin_document_detail fdd on fdd.fin_document_key = vi.fin_document_key
join account a on a.account_key = eda.cost_account_key
union all
--vendor payments that have neither details nor paid documents
select
vp.fin_document_key,
null including_fin_document_key,
null included_fin_document_key,
null detail_key,
null eda_key,
null account_key,
null type,
null category_1099,
null included_detail_amount,
null detail_1099_amount
from vendor_payment vp
join customer v on v.customer_key = vp.vendor_org_key
and v.vendor_1099 = 'Y'
join payment_method pm on pm.payment_method_key = vp.payment_method_key
and pm.include_in_1099 = 'Y'
join fin_document fd on fd.fin_document_key = vp.fin_document_key
and fd.posted_timestamp is not null
and fd.voided_fin_document_key is null
and fd.voiding_fin_document_key is null
where vp.applied_fin_document_key is null
and not exists (select 1 from fin_document_detail where fin_document_key = fd.fin_document_key)
and not exists (select 1 from vendor_payment_included where fin_document_key = fd.fin_document_key)
|