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