View: dbo.Consolidation_Subsidiary_Client_Account_Number_View
View definition
-- drop view Consolidation_Subsidiary_Client_Account_Number_View
create view dbo.Consolidation_Subsidiary_Client_Account_Number_View
as
select
client_KEY = n.consolidating__client_KEY
, n.subsidiary__client_KEY
, n.sequence_number
, gla.description
, segment1 = cast(n.sequence_number as nvarchar(3)) collate Latin1_General_CI_AS
, segment2 =
-- Strip off any leading or trailing padding and, for multiple segment charts, replace any internal padding
-- with zeros.
replace(ltrim(--rtrim(
-- Replace the special padding character (supplied by the
-- Consolidation_Subsidiary_Client_Account_Number_Detail_View view) with spaces to make it easier to trim.
replace(
-- Build the account number by concatenating each of its segment codes in the order defined by the client's
-- Chart of Accounts mask.
(select cast(segment AS varchar(60))
from dbo.Consolidation_Subsidiary_Client_Account_Number_Detail_View
where gl_account_KEY = gla.gl_account_KEY
order by mask_order
for xml path (N''))
, N'+'
, N' '))--)
, N' '
, N'0')
, gla.is_active
, subsidiary__gl_account_KEY = gla.gl_account_KEY
from
dbo.Client_N_Subsidiary_Client as n
inner join dbo.GL_Account as gla
on n.subsidiary__client_KEY = gla.client_KEY
where
n.subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate