View: dbo.Consolidation_Subsidiary_Client_Tax_Code_View
View definition
-- drop view dbo.Consolidation_Subsidiary_Client_Tax_Code_View
create view dbo.Consolidation_Subsidiary_Client_Tax_Code_View
as
select
pickme.master__client_KEY
, pickme.subsidiary__sequence_number
, detail.subsidiary__client_KEY -- key (1 of 4)
, pickme.ultra_tax_entity_type_KEY -- key (2 of 4)
, pickme.code_year -- key (3 of 4)
, pickme.tax_code -- alternate key (4.1 of 4)
, detail.description
, pickme.tax_subcode -- alternate key (4.2 of 4)
, detail.tax_subcode_description
, subsidiary__tax_code_n_ultra_tax_tax_code_KEY = detail.tax_code_n_ultra_tax_tax_code_KEY
, subsidiary__tax_code_KEY = detail.tax_code_KEY -- key (4 of 4)
, detail.ultra_tax_tax_code_KEY
, detail.ultra_tax_category_type_KEY
, detail.gl_account_type_KEY
from
(
select
master__client_KEY = cnsc.consolidating__client_KEY
, subsidiary__sequence_number = min(cnsc.sequence_number)
, uttc.ultra_tax_entity_type_KEY -- key (2 of 4)
, uttc.code_year -- key (3 of 4)
, tc.tax_code -- alternate key (4.1 of 4)
, tc.tax_subcode -- alternate key (4.2 of 4)
from
dbo.Client_N_Subsidiary_Client as cnsc
inner join dbo.Tax_Code as tc
on tc.client_KEY = cnsc.subsidiary__client_KEY
inner join dbo.Tax_Code_N_Ultra_Tax_Tax_Code as n
on tc.tax_code_KEY = n.tax_code_KEY
inner join dbo.Ultra_Tax_Tax_Code as uttc
on n.ultra_tax_tax_code_KEY = uttc.ultra_tax_tax_code_KEY
where
cnsc.subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate (1 to consolidate, 2 in master)
group by
cnsc.consolidating__client_KEY
, uttc.ultra_tax_entity_type_KEY
, uttc.code_year
, tc.tax_code
, tc.tax_subcode
--order by
-- uttc.ultra_tax_entity_type_KEY
-- , uttc.code_year
-- , tc.tax_code
-- , tc.tax_subcode
) as pickme
inner join
(
select
master__client_KEY = cnsc.consolidating__client_KEY
, subsidiary__sequence_number = cnsc.sequence_number
, cnsc.subsidiary__client_KEY -- key (1 of 4)
, uttc.ultra_tax_entity_type_KEY -- key (2 of 4)
, uttc.code_year -- key (3 of 4)
, tc.tax_code -- alternate key (4.1 of 4)
, uttc.description
, tc.tax_subcode -- alternate key (4.2 of 4)
, n.tax_subcode_description
, n.tax_code_n_ultra_tax_tax_code_KEY
, tc.tax_code_KEY -- key (4 of 4)
, uttc.ultra_tax_tax_code_KEY
, uttc.ultra_tax_category_type_KEY
, uttc.gl_account_type_KEY
from
dbo.Client_N_Subsidiary_Client as cnsc
inner join dbo.Tax_Code as tc
on tc.client_KEY = cnsc.subsidiary__client_KEY
inner join dbo.Tax_Code_N_Ultra_Tax_Tax_Code as n
on tc.tax_code_KEY = n.tax_code_KEY
inner join dbo.Ultra_Tax_Tax_Code as uttc
on n.ultra_tax_tax_code_KEY = uttc.ultra_tax_tax_code_KEY
where
cnsc.subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate (1 to consolidate, 2 in master)
--order by
-- uttc.ultra_tax_entity_type_KEY
-- , uttc.code_year
-- , tc.tax_code
-- , tc.tax_subcode
) as detail
on pickme.master__client_KEY = detail.master__client_KEY
and pickme.subsidiary__sequence_number = detail.subsidiary__sequence_number
and pickme.ultra_tax_entity_type_KEY = detail.ultra_tax_entity_type_KEY
and pickme.code_year = detail.code_year
and pickme.tax_code = detail.tax_code
and pickme.tax_subcode = detail.tax_subcode
--order by
-- detail.master__client_KEY
-- , pickme.ultra_tax_entity_type_KEY
-- , pickme.code_year
-- , pickme.tax_code
-- , pickme.tax_subcode
;