View: dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_With_Leading_Zeroes_View | |||
View definition | |||
-- drop view dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_With_Leading_Zeroes_View create view dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_With_Leading_Zeroes_View as select client_KEY = n.consolidating__client_KEY , glas.mask_order , gla.gl_account_KEY , codes.gl_account_segment_code_KEY -- Pad the code to the full length of the segment including a separator if one is set up. The -- Consolidation_Subsidiary_Client_Account_Number_View will use these padded codes to build the subsidiaries -- full account number. , segment = right(replicate(N'0', len(glas.pattern)) + isnull(codes.code, N''), len(glas.pattern)) -- Append the segment's separator character to the code. + glas.separator from -- Identify subsidiary clients. dbo.Client_N_Subsidiary_Client as n -- Cross join the accounts with the segments. This view must produce a row per account number per segment, even -- if a particular account number doesn't use all the segments. Why? Cuz of how the 'for xml' logic in view -- Consolidation_Subsidiary_Client_Account_Number_View builds the final account string. inner join dbo.GL_Account as gla on n.subsidiary__client_KEY = gla.client_KEY inner join dbo.GL_Account_Segment as glas on n.subsidiary__client_KEY = glas.client_KEY -- For those account number / segment combinations that are actually in use, dig up the code information and -- add it in. left join ( select glasc.gl_account_segment_KEY, glanglasc.gl_account_KEY, glasc.gl_account_segment_code_KEY, glasc.code from dbo.GL_Account_N_GL_Account_Segment_Code as glanglasc inner join dbo.GL_Account_Segment_Code as glasc on glasc.gl_account_segment_code_KEY = glanglasc.gl_account_segment_code_KEY ) as codes on codes.gl_account_KEY = gla.gl_account_KEY and codes.gl_account_segment_KEY = glas.gl_account_segment_KEY where n.subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate and glas.mask_order > 0 | |||