View: dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_View | |||
View definition | |||
-- drop view dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_View create view dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_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 then will strip off any leading or trailing padding and, for multiple segment charts, -- will replace any internal padding with zeros. , segment = right(replicate(N'+', len(glas.pattern)) + isnull(codes.code, N''), len(glas.pattern)) -- Append the segment's separator character to the code. However, if the account number either doesn't have -- a code selected for this segment or doesn't have a code selected for the next segment then don't use -- the separator character; append a padding character instead since there aren't two codes that need to be -- separated. + case when glas.separator <> N'' and (codes.code is null or next_segment_code.code is null) then N'+' -- Special padding character else glas.separator end 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 -- For each segment code, look up the account number's next segment to see if a code was provided for that as well. -- If not, then this segment will not use it's separator character if one was provided since there won't be a -- following code that it needs to be separated from. left join ( select glanglasc1.gl_account_KEY, glas1.mask_order, glasc1.code from dbo.GL_Account_N_GL_Account_Segment_Code as glanglasc1 inner join dbo.GL_Account_Segment_Code as glasc1 on glasc1.gl_account_segment_code_KEY = glanglasc1.gl_account_segment_code_KEY inner join dbo.GL_Account_Segment as glas1 on glasc1.gl_account_segment_KEY = glas1.gl_account_segment_KEY ) as next_segment_code on gla.gl_account_KEY = next_segment_code.gl_account_KEY and glas.mask_order + 1 = next_segment_code.mask_order where n.subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate and glas.mask_order > 0 | |||