USP_DATALIST_MARKETINGMEMBERSHIPASKLADDER

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

  with [FIELDINFO] ([PARENTTYPE], [DISPLAYNAME], [OBJECTKEY]) as
  (
    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 outer join 
      dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME] = [QUERYVIEWCATALOG].[OBJECTNAME]
    left outer join 
      dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID] = [SYSOBJECTS].[ID]
  ),
  [SMARTFIELDINFO] ([PARENTTYPE], [DISPLAYNAME], [OBJECTKEY]) as
  (
    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]
  )
  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] = [MKTMEMBERSHIPMAILINGTEMPLATERULE].[PACKAGEID]) as [PACKAGE],
    coalesce([MKTASKLADDER].[NAME], '<none>'),
    coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME], [SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME], [MKTASKLADDER].[OBJECTKEY]) as [BASEDON],
    dbo.[UFN_MKTASKLADDER_VALUESTRING]([MKTASKLADDER].[ID], null) as [VALUESTRING]
  from 
    dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] 
  inner join 
    dbo.[MKTSEGMENT] on [MKTMEMBERSHIPMAILINGTEMPLATERULE].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join 
    dbo.[MKTASKLADDER] on [MKTMEMBERSHIPMAILINGTEMPLATERULE].[ASKLADDERID] = [MKTASKLADDER].[ID]
  left outer join 
    [FIELDINFO] on [FIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  left outer join 
    [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
    on MKTMEMBERSHIPMAILINGTEMPLATERULE.PACKAGEID = MAILLETTER.MAILPACKAGEID
  left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
    on MKTMEMBERSHIPMAILINGTEMPLATERULE.PACKAGEID = EMAILLETTER.EMAILPACKAGEID
  where 
    dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE].[MEMBERSHIPMAILINGTEMPLATEID] = @ID

  return 0;