USP_DATALIST_ASKLADDEROVERRIDESUMMARY

CREATE procedure dbo.USP_DATALIST_ASKLADDEROVERRIDESUMMARY
(
    @MAILINGID uniqueidentifier,
    @MAILINGTYPE tinyint
)
as

set nocount on;

if @MAILINGTYPE = 0
    select
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[ID],
        [MKTASKLADDER].[NAME],
        [IDSETREGISTER].[NAME],
        coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) as [ENTRYAMOUNT],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[ASKLADDERID],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[IDSETREGISTERID],
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE] as [SEQUENCE]
    from 
        dbo.[MKTASKLADDER]
    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] = @MAILINGID
    order by 
        [MKTSEGMENTATIONASKLADDEROVERRIDE].[SEQUENCE];
else if @MAILINGTYPE = 1
    select
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[ID],
        [MKTASKLADDER].[NAME],
        [IDSETREGISTER].[NAME],
        coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) as [ENTRYAMOUNT],
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID],
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[IDSETREGISTERID],
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE] as [SEQUENCE]
    from 
        dbo.[MKTASKLADDER]
    inner join 
        dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID] = [MKTASKLADDER].[ID]
    inner join 
        dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[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 
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @MAILINGID
    order by 
        [MKTACKNOWLEDGEMENTMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE];
else if @MAILINGTYPE = 2
    select
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ID],
        [MKTASKLADDER].[NAME],
        [IDSETREGISTER].[NAME],
        coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) as [ENTRYAMOUNT],
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID],
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[IDSETREGISTERID],
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE] as [SEQUENCE]
    from 
        dbo.[MKTASKLADDER]
    inner join 
        dbo.[MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE] on [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID] = [MKTASKLADDER].[ID]
    inner join 
        dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[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 
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[MEMBERSHIPMAILINGTEMPLATEID] = @MAILINGID
    order by 
        [MKTMEMBERSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE];
else if @MAILINGTYPE = 3
    select
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ID],
        [MKTASKLADDER].[NAME],
        [IDSETREGISTER].[NAME],
        coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME],[MKTASKLADDER].[OBJECTKEY]) as [ENTRYAMOUNT],
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID],
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[IDSETREGISTERID],
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE] as [SEQUENCE]
    from 
        dbo.[MKTASKLADDER]
    inner join 
        dbo.[MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE] on [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[ASKLADDERID] = [MKTASKLADDER].[ID]
    inner join 
        dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[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 
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[SPONSORSHIPMAILINGTEMPLATEID] = @MAILINGID
    order by 
        [MKTSPONSORSHIPMAILINGTEMPLATEASKLADDEROVERRIDE].[SEQUENCE];

return 0;