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.transaction_date dtl_date,
fdd.reference dtl_reference,
null eda_key,
a.account_key,
'D' type,
a.category_1099,
null included_detail_amount,
fdd.debit_amount - fdd.credit_amount detail_1099_amount,
null ptd_key,
null fca_key
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.transaction_date dtl_date,
fdd.reference dtl_reference,
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,
null ptd_key,
null fca_key
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 eda where eda.vendor_invoice_key = vi.fin_document_key and eda.po_key is null)
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
-- funding cap adjustment 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,
fd.document_date dtl_date,
fd.document_number dtl_reference,
null eda_key,
a.account_key,
'VI' type,
a.category_1099,
fca.amount included_detail_amount,
round((vpi.applied_amount / vi.invoice_amount) * (fca.amount), 2) detail_1099_amount,
null ptd_key,
fca.vi_funding_cap_adjustment_key fca_key
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 vi_funding_cap_adjustment fca on fca.fin_document_key = vpi.included_fin_document_key
join account a on a.account_key = fca.account_key
union all
-- matched labor 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,
ptd.work_date dtl_date,
p.username dtl_reference,
null eda_key,
a.account_key,
'TS' type,
a.category_1099,
(round(ptd.quantity * ptd.eff_cost_rate, 2)) included_detail_amount,
round((vpi.applied_amount / vi.invoice_amount) * (round(ptd.quantity * ptd.eff_cost_rate, 2)), 2) detail_1099_amount,
ptd.time_data_key ptd_key,
null fca_key
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 vi_included_time vii on vii.vendor_invoice_key = vpi.included_fin_document_key
join person_time_data ptd on ptd.time_data_key = vii.time_data_key and ptd.po_key is not null
join person_time pt on pt.person_time_key = ptd.person_time_key
join person p on p.person_key = pt.person_key
join journal_time jt on jt.post_history_key = ptd.cost_post_history_key and ptd.time_data_key = jt.time_data_key and jt.category = 6
join account a on a.account_key = jt.account_key
union all
-- matched expense 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,
ed.expense_date dtl_date,
cast(ed.expense_report_key as varchar(15)) dtl_reference,
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,
null ptd_key,
null fca_key
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 vi_included_expense vii on vii.vendor_invoice_key = vpi.included_fin_document_key
join expense_data_allocation eda on eda.expense_data_allocation_key = vii.expense_data_allocation_key and eda.po_key is not null
join expense_data ed on ed.expense_data_key = eda.expense_data_key
join account a on a.account_key = eda.cost_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,
null dtl_date,
null dtl_reference,
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,
null ptd_key,
null fca_key
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 and eda.po_key is null
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 dtl_date,
null dtl_reference,
null eda_key,
null account_key,
null type,
null category_1099,
null included_detail_amount,
null detail_1099_amount,
null ptd_key,
null fca_key
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)
|