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*/ | |||