View: dbo.Client_Tax_Code_View
View definition
-- DESCRIPTION
-- This view flattens the contents of the Tax Code table, the Tax Code N Ultra Tax Tax
-- Code table and the Ultra Tax Tax Code table into a simple list of tax codes per client
-- per code year. Global tax codes are not incuded in the result set. Only tax codes
-- associated with a client's current entity type (1120, 1065, etc) are included in the
-- results. A few things to keep in mind when using this view is:
-- 1) Columns client_KEY, tax_code_KEY and code_year make up a unique key on the result
-- set.
-- 2) Likewise, columns client_KEY, tax_code, tax_subcode and code_year make up a unique
-- key on the result set.
-- 3) When using this view, it is usually necessary to limit the result set by:
-- a) Client (filter by client_KEY)
-- b) Code year (filter by code_year; usually the current period's year if tax codes
-- exist for it otherwise the latest year that tax codes do exist (see
-- business rule method GLAccountRules.GetCurrentTaxCodeYear))
--drop view dbo.Client_Tax_Code_View
create view dbo.Client_Tax_Code_View
as
select
-- This isnull call is necessary to force the view to recognize the client key as a NOT
-- NULL column. It will *never* be set to zero because tc.client_KEY can never be NULL
-- in this view.
isnull(tc.client_KEY,0) as client_KEY
, tc.tax_code_KEY
, uttc.ultra_tax_tax_code_KEY
, uttc.ultra_tax_entity_type_KEY
, uttc.code_year
, tc.tax_code
, tc.tax_subcode
, uttc.description as tax_code_description
, tcnuttc.tax_subcode_description
, uttc.ultra_tax_category_type_KEY
, uttc.gl_account_type_KEY
from
dbo.Tax_Code as tc
inner join dbo.Tax_Code_N_Ultra_Tax_Tax_Code as tcnuttc
on tc.tax_code_KEY = tcnuttc.tax_code_KEY
inner join dbo.Ultra_Tax_Tax_Code as uttc
on tcnuttc.ultra_tax_tax_code_KEY = uttc.ultra_tax_tax_code_KEY
inner join dbo.Client_GL_Setup as cgls
on tc.client_KEY = cgls.client_KEY
and uttc.ultra_tax_entity_type_KEY = cgls.ultra_tax_entity_type_KEY
where
tc.client_KEY is not null