UFN_MKTCOMUNICATIONEFFORTASKLADDEROVERRIDES_GETITEMLIST

create function dbo.UFN_MKTCOMUNICATIONEFFORTASKLADDEROVERRIDES_GETITEMLIST(@SEGMENTATIONID uniqueidentifier)
/*
Returns a table of all of the ask ladder overrides associated with a given effort
*/
returns table
as
return 
(
      select 
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[ID],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID],
        coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME], [MKTASKLADDER].[OBJECTKEY]) as [ENTRYAMOUNT],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE]
      from
        dbo.[MKTSEGMENTATIONASKLADDEROVERRIDE]
        inner join dbo.[MKTASKLADDER] on [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID] = [MKTASKLADDER].[ID]
        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] = @SEGMENTATIONID
)