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