USP_DATALIST_DISTINCTMKTMARKETINGPLANITEMTEMPLATEITEMS

CREATE procedure dbo.[USP_DATALIST_DISTINCTMKTMARKETINGPLANITEMTEMPLATEITEMS]
(
  @MARKETINGPLANITEMID uniqueidentifier = null
)
as
  set nocount on;

  declare @LEVEL tinyint;
  declare @MARKETINGPLANID uniqueidentifier;

  select
    @LEVEL = [LEVEL],
    @MARKETINGPLANID = [MARKETINGPLANID]
  from dbo.[MKTMARKETINGPLANITEM]
  where [ID] = @MARKETINGPLANITEMID;

  set @LEVEL = coalesce(@LEVEL, 0);

  select distinct
    [MPITI].[CAPTION],
    [MPITI].[BACKCOLOR],
    [MPITI].[LEVEL],
    coalesce((select [T0].[CAPTION] + cast([T0].[BACKCOLOR] as nvarchar(25)) + cast([T0].[LEVEL] as nvarchar(1)) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T0] where [T0].[LEVEL] = 0 and [T0].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]), '') +
    coalesce((select [T1].[CAPTION] + cast([T1].[BACKCOLOR] as nvarchar(25)) + cast([T1].[LEVEL] as nvarchar(1)) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T1] where [T1].[LEVEL] = 1 and [T1].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]), '') +
    coalesce((select [T2].[CAPTION] + cast([T2].[BACKCOLOR] as nvarchar(25)) + cast([T2].[LEVEL] as nvarchar(1)) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T2] where [T2].[LEVEL] = 2 and [T2].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]), '') +
    coalesce((select [T3].[CAPTION] + cast([T3].[BACKCOLOR] as nvarchar(25)) + cast([T3].[LEVEL] as nvarchar(1)) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T3] where [T3].[LEVEL] = 3 and [T3].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]), '') +
    coalesce((select [T4].[CAPTION] + cast([T4].[BACKCOLOR] as nvarchar(25)) + cast([T4].[LEVEL] as nvarchar(1)) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T4] where [T4].[LEVEL] = 4 and [T4].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]), '')
    as [KEY]
  from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] 
  left join dbo.[MKTMARKETINGPLANITEM] [MPI] on ([MPI].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID] and [MPI].[LEVEL] = [MPITI].[LEVEL])
  where (([MPITI].[MARKETINGPLANID] = @MARKETINGPLANID and [MPI].[LEVEL] >= @LEVEL) or @MARKETINGPLANITEMID is null)
  and (select count(*) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where ([SUB].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID] and [SUB].[LEVEL] = [MPITI].[LEVEL])) > 0
  and (select [ISACTIVE] from dbo.[MKTMARKETINGPLAN] as [SUB] where [SUB].[ID] = [MPITI].[MARKETINGPLANID]) <> 0
  order by 4, 3;

  return 0;