USP_DATALIST_MKTMARKETPLANHIERARCHY

CREATE procedure dbo.[USP_DATALIST_MKTMARKETPLANHIERARCHY]
(
  @MARKETINGPLANID uniqueidentifier
)
as
  set nocount on;

  select
    [MPI0].[NAME] as [CAPTION],
    (case
      when (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI0].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI0].[LEVEL]
        then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png' 
      when (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI0].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI0].[LEVEL]
        then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.appeal.png'
      else 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.marketing_plan.png'
    end) as [IMAGEKEY],
    '2A4B9226-9755-4CB2-8332-EF73CC8202DB' as [PAGEID],
    [MPI0].[ID] as [CONTEXTID],
    [T0].[BACKCOLOR] as [FORECOLOR],
    (
      select
        [MPI1].[NAME] as [CAPTION],
        (case
          when (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI1].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI1].[LEVEL]
            then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png'
          when (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI1].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI1].[LEVEL]
            then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.appeal.png'
          else 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.marketing_plan.png'
        end) as [IMAGEKEY],
        '2A4B9226-9755-4CB2-8332-EF73CC8202DB' as [PAGEID],
        [MPI1].[ID] as [CONTEXTID],
        [T0].[BACKCOLOR] as [FORECOLOR],
        (
          select
            [MPI2].[NAME] as [CAPTION],
            (case
              when (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI2].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI2].[LEVEL]
                then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png'
              when (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI2].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI2].[LEVEL]
                then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.appeal.png'
              else 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.marketing_plan.png'  
            end) as [IMAGEKEY],
            '2A4B9226-9755-4CB2-8332-EF73CC8202DB' as [PAGEID],
            [MPI2].[ID] as [CONTEXTID],
            [T0].[BACKCOLOR] as [FORECOLOR],
            (
              select
                [MPI3].[NAME] as CAPTION,
                (case
                  when (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI3].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI3].[LEVEL]
                    then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png'
                  when (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI3].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI3].[LEVEL]
                    then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.appeal.png'
                  else 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.marketing_plan.png'
                end) as [IMAGEKEY],
                '2A4B9226-9755-4CB2-8332-EF73CC8202DB' as [PAGEID],
                [MPI3].[ID] as [CONTEXTID],
                [T0].[BACKCOLOR] as [FORECOLOR],
                (
                  select
 [MPI4].[NAME] as [CAPTION],
                    (case
                      when (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI4].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI4].[LEVEL]
                        then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png'
                      when (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI4].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]) = [MPI4].[LEVEL]
                        then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.appeal.png'
                      else 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.marketing_plan.png'
                    end) as [IMAGEKEY], 
                    '2A4B9226-9755-4CB2-8332-EF73CC8202DB' as [PAGEID], 
                    [MPI4].[ID] as [CONTEXTID],
                    [T0].[BACKCOLOR] as [FORECOLOR]
                  from dbo.[MKTMARKETINGPLANITEM] as [MPI4]
                  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T4] on ([T4].[MARKETINGPLANID] = [MPI4].[MARKETINGPLANID] and [T4].[LEVEL] = [MPI4].[LEVEL])
                  where [MPI4].[PARENTMARKETINGPLANITEMID] = [MPI3].[ID]
                  order by [MPI4].[NAME]
                  for xml raw('NODE'), root('NODES'), elements, type
                )
              from dbo.[MKTMARKETINGPLANITEM] as [MPI3]
              left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T3] on ([T3].[MARKETINGPLANID] = [MPI3].[MARKETINGPLANID] and [T3].[LEVEL] = [MPI3].[LEVEL])
              where [MPI3].[PARENTMARKETINGPLANITEMID] = [MPI2].[ID]
              order by [MPI3].[NAME]
              for xml raw('NODE'), root('NODES'), elements, type
            )
          from dbo.[MKTMARKETINGPLANITEM] as [MPI2]
          left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T2] on ([T2].[MARKETINGPLANID] = [MPI2].[MARKETINGPLANID] and [T2].[LEVEL] = [MPI2].[LEVEL])
          where [MPI2].[PARENTMARKETINGPLANITEMID] = [MPI1].[ID]
          order by [MPI2].[NAME]
          for xml raw('NODE'), root('NODES'), elements, type
        )
      from dbo.[MKTMARKETINGPLANITEM] as [MPI1]
      left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T1] on ([T1].[MARKETINGPLANID] = [MPI1].[MARKETINGPLANID] and [T1].[LEVEL] = [MPI1].[LEVEL])
      where [MPI1].[PARENTMARKETINGPLANITEMID] = [MPI0].[ID]
      order by [MPI1].[NAME]
      for xml raw('NODE'), root('NODES'), elements, type
    ) as [NODES]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI0]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MPI0].[MARKETINGPLANID] = [MP].[ID]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [T0] on ([T0].[MARKETINGPLANID] = [MPI0].[MARKETINGPLANID] and [T0].[LEVEL] = [MPI0].[LEVEL])
  where [MPI0].[LEVEL] = 0;

  return 0;