USP_DATALIST_SEGMENTATIONASKLADDER

CREATE procedure dbo.[USP_DATALIST_SEGMENTATIONASKLADDER]
(
  @ID uniqueidentifier
)
as
  set nocount on;

  select
    coalesce([MKTASKLADDER].[ID],newid()),
    case
        when MAILLETTER.ID is not null then MAILLETTER.NAME + ' (Mail)'
        when EMAILLETTER.ID is not null then EMAILLETTER.NAME + ' (Email)'
        else ''
    end    LETTER
    ,[MKTSEGMENT].[NAME]
    ,(select [P].[NAME] from dbo.[MKTPACKAGE] [P] where [P].[ID]=[MKTSEGMENTATIONSEGMENT].[PACKAGEID]) [PACKAGE]
    ,coalesce([MKTASKLADDER].[NAME],'<none>')
    ,coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) [BASEDON]            
    ,dbo.[UFN_MKTASKLADDER_VALUESTRING]([MKTASKLADDER].[ID],null) [VALUESTRING]             
    ,0 [TYPE]
    ,[MKTSEGMENTATIONSEGMENT].[ID] [TYPEID]
  from
    dbo.[MKTSEGMENTATIONSEGMENT] 
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID]=[MKTSEGMENT].[ID]
    left join dbo.[MKTASKLADDER] on [MKTSEGMENTATIONSEGMENT].[ASKLADDERID]=[MKTASKLADDER].[ID]
    left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
        on MKTSEGMENTATIONSEGMENT.PACKAGEID = MAILLETTER.MAILPACKAGEID
    left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
        on MKTSEGMENTATIONSEGMENT.PACKAGEID = EMAILLETTER.EMAILPACKAGEID
    left join 
    (select 
    [QUERYVIEWCATALOG].[DISPLAYNAME] [PARENTTYPE] 
    ,(select isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) from [QUERYVIEWCATALOG].OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) where T.c.value('(@Name)[1]','nvarchar(255)')=[SYSCOLUMNS].[NAME]) [DISPLAYNAME]
    ,[SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME] [OBJECTKEY]
    from dbo.[QUERYVIEWCATALOG] 
    left join dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME]=[QUERYVIEWCATALOG].[OBJECTNAME] 
    left join dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID]=[SYSOBJECTS].[ID]) [FIELDINFO] on [FIELDINFO].[OBJECTKEY]=[MKTASKLADDER].[OBJECTKEY]
    left join
    (select 
    'Smart Field' [PARENTTYPE],
    [SMARTFIELD].[NAME] [DISPLAYNAME], 
    [SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME] [OBJECTKEY]
    from dbo.[SMARTFIELD]
    inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID]=[SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID]=[SMARTFIELD].[TABLECATALOGID]) [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY]=[MKTASKLADDER].[OBJECTKEY]
  where 
    dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]=@ID

  union select
    coalesce([MKTASKLADDER].[ID],newid())
    ,'' [LETTER]
    ,[MKTSEGMENT].[NAME] + ' \ ' +[MKTSEGMENTATIONTESTSEGMENT].[NAME]
    ,(select [P].[NAME] from dbo.[MKTPACKAGE] [P] where [P].[ID]=[MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]) [PACKAGE]
    ,coalesce([MKTASKLADDER].[NAME],'<none>')
    ,coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) [BASEDON]            
    ,dbo.[UFN_MKTASKLADDER_VALUESTRING]([MKTASKLADDER].[ID],null) [VALUESTRING]             
    ,1 [TYPE]
    ,[MKTSEGMENTATIONTESTSEGMENT].[ID] [TYPEID]
  from
    dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]=[MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID]=[MKTSEGMENT].[ID]
    left join dbo.[MKTASKLADDER] on [MKTSEGMENTATIONTESTSEGMENT].[ASKLADDERID]=[MKTASKLADDER].[ID]
    left join 
    (select 
    [QUERYVIEWCATALOG].[DISPLAYNAME] [PARENTTYPE] 
    ,(select isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) from [QUERYVIEWCATALOG].OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) where T.c.value('(@Name)[1]','nvarchar(255)')=[SYSCOLUMNS].[NAME]) [DISPLAYNAME]
    ,[SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME] [OBJECTKEY]
    from dbo.[QUERYVIEWCATALOG] 
    left join dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME]=[QUERYVIEWCATALOG].[OBJECTNAME] 
    left join dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID]=[SYSOBJECTS].[ID]) [FIELDINFO] on [FIELDINFO].[OBJECTKEY]=[MKTASKLADDER].[OBJECTKEY]
    left join
    (select 
    'Smart Field' [PARENTTYPE],
    [SMARTFIELD].[NAME] [DISPLAYNAME], 
    [SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME] [OBJECTKEY]
    from dbo.[SMARTFIELD]
    inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID]=[SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID]=[SMARTFIELD].[TABLECATALOGID]) [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY]=[MKTASKLADDER].[OBJECTKEY]
  where 
    dbo.[MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]=@ID;

  return 0;