View: dbo.Transaction_Template_List_View
View definition
create view dbo.Transaction_Template_List_View
as
select
1 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
description,
null as vendor_id,
null as vendor_description,
client_KEY
from dbo.Transaction_Template tt
join dbo.Transaction_Template_N_Journal_Entry ttnje
on tt.transaction_template_KEY = ttnje.transaction_template_KEY
where tt.transaction_template_frequency_KEY = 1
union all
select
2 as transaction_template_type,
vendor_amortization_schedule_KEY as template_KEY,
vendor_amortization_schedule_id as id,
vas.description,
vendor_id,
v.description as vendor_description,
client_KEY
from dbo.Vendor_Amortization_Schedule vas
join dbo.Vendor v on v.vendor_KEY = vas.vendor_KEY
where vas.is_active = 1
union all
select
3 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
null as vendor_id,
null as vendor_description,
client_KEY
from dbo.Transaction_Template tt
join dbo.Bank_Transaction_Template btt
on tt.transaction_template_KEY = btt.transaction_template_KEY
where btt.bank_transaction_type_KEY = 2 and tt.transaction_template_frequency_KEY = 1
union all
select
4 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
ven.vendor_id as vendor_id,
ven.description as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.Bank_Transaction_Template btt
on tt.transaction_template_KEY = btt.transaction_template_KEY
left outer join dbo.Vendor as ven
on btt.vendor_KEY = ven.vendor_KEY
where btt.bank_transaction_type_KEY = 3 /*payment*/ and tt.transaction_template_frequency_KEY = 1 /*on demand*/
union all
select
5 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
ven.vendor_id as vendor_id,
ven.description as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.Payable_Transaction_Template ptt
on tt.transaction_template_KEY = ptt.transaction_template_KEY
left outer join dbo.Vendor as ven
on ptt.vendor_KEY = ven.vendor_KEY
where tt.transaction_template_frequency_KEY = 1 /* on demand only */
union all
select
6 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
null as vendor_id,
null as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.AR_Transaction_Template att
on tt.transaction_template_KEY = att.transaction_template_KEY
where tt.transaction_template_frequency_KEY = 1 /* on demand only */
union all
select
7 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
ven.vendor_id as vendor_id,
ven.description as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.Bank_Transaction_Template btt
on tt.transaction_template_KEY = btt.transaction_template_KEY
left outer join dbo.Vendor as ven
on btt.vendor_KEY = ven.vendor_KEY
where btt.bank_transaction_type_KEY = 11 /*Ap Payment*/ and tt.transaction_template_frequency_KEY = 1 /*on demand*/
union all
select
8 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
ven.vendor_id as vendor_id,
ven.description as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.Bank_Transaction_Template btt
on tt.transaction_template_KEY = btt.transaction_template_KEY
left outer join dbo.Vendor as ven
on btt.vendor_KEY = ven.vendor_KEY
where btt.bank_transaction_type_KEY = 1 /*Check*/ and tt.transaction_template_frequency_KEY = 1 /*on demand*/
union all
select
9 as transaction_template_type,
tt.transaction_template_KEY as template_KEY,
transaction_template_id as id,
tt.description,
ven.vendor_id as vendor_id,
ven.description as vendor_description,
tt.client_KEY as client_KEY
from dbo.Transaction_Template tt
join dbo.Bank_Transaction_Template btt
on tt.transaction_template_KEY = btt.transaction_template_KEY
left outer join dbo.Vendor as ven
on btt.vendor_KEY = ven.vendor_KEY
where btt.bank_transaction_type_KEY = 10 /*AP Check*/ and tt.transaction_template_frequency_KEY = 1 /*on demand*/