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 ; | |||