USP_DATALIST_SEGMENTATIONASKLADDEROVERRIDE

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

  select
    [MKTSEGMENTATIONASKLADDEROVERRIDE].[ID],
    [MKTASKLADDER].[NAME],
    coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) as [BASEDON],
    [IDSETREGISTER].[NAME] as [SELECTION],
    dbo.[UFN_MKTASKLADDER_VALUESTRING]([MKTASKLADDER].[ID], null) as [VALUELADDER],
    [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE] as [SEQUENCE],
    case when [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE] > 1 then 1 else 0 end as [ALLOWUP],
    case when [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE] < (select max([MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE]) from dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] [MKTSEGMENTATIONASKLADDEROVERRIDE] where [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEGMENTATIONID] = @ID) then 1 else 0 end as [ALLOWDOWN],
    [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID],
    [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID],
    [MKTASKLADDERITEM].[ITEMVALUE1],
    [MKTASKLADDERITEM].[ITEMVALUE2],
    [MKTASKLADDERITEM].[ITEMVALUE3],
    [MKTASKLADDERITEM].[ITEMVALUE4],
    [MKTASKLADDERITEM].[ITEMVALUE5],
    [MKTASKLADDERITEM].[WRITEINTEXT],
    [MKTASKLADDER].[BASECURRENCYID]
  from dbo.[MKTASKLADDER]
  inner join dbo.[MKTASKLADDERITEM] on [MKTASKLADDERITEM].[ASKLADDERID] = [MKTASKLADDER].[ID]
  inner join dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE] on [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID] = [MKTASKLADDER].[ID]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID]
  left join 
    (select 
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [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]) as [DISPLAYNAME],
      [SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME] as [OBJECTKEY]
     from dbo.[QUERYVIEWCATALOG] 
     left join dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME] = [QUERYVIEWCATALOG].[OBJECTNAME] 
     left join dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID] = [SYSOBJECTS].[ID]) as [FIELDINFO] on [FIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  left join
    (select 
      'Smart Field' as [PARENTTYPE],
      [SMARTFIELD].[NAME] as [DISPLAYNAME], 
      [SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME] as [OBJECTKEY]
     from dbo.[SMARTFIELD]
     inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
     inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID] = [SMARTFIELD].[TABLECATALOGID]) as [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  where [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEGMENTATIONID] = @ID
  order by [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE];

  return 0;