USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUP
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin
    set nocount on;

    declare @CONSTITUENTRECORDTYPEID uniqueidentifier;
    select @CONSTITUENTRECORDTYPEID = [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT';

    select top(1)
        [MKTSEGMENTATION].[NAME],
        [MKTSEGMENTATION].[DESCRIPTION],
        [MKTSEGMENTATION].[MAILDATE],
        [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
        [APPEALMAILING].[APPEALID],
        (select [NAME] as GROUPNAME from dbo.[UFN_SELECTION_GETGROUPNAMES](@CONSTITUENTRECORDTYPEID) for xml raw('ITEM'),type,elements,root('SELECTIONLISTS'),binary base64) as SELECTIONLISTS,
        --coalesce([APPEALMAILINGSETUP].[SELECTEDSELECTIONS], dbo.[UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[SEGMENTID], @CURRENTAPPUSERID)) as SELECTEDSELECTIONS,

    isnull(dbo.UFN_APPEALMAILINGSETUP_GETSELECTIONS_TOITEMLISTXML(APPEALMAILINGSETUP.ID), dbo.[UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[SEGMENTID], @CURRENTAPPUSERID)) as SELECTEDSELECTIONS,
        dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]) as SELECTEDEXCLUSIONS,
        [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
        [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
        [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
        [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
        [BUSINESSPROCESSCOMMPREF].[DATETYPECODE] as [EXCLUSIONDATETYPECODE],
        [BUSINESSPROCESSCOMMPREF].[ASOFDATE] as [EXCLUSIONASOFDATE],
        (select [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT')as CONSTITUENTRECORDTYPEID,
        dbo.[UFN_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_TOITEMLISTXML]([MKTSEGMENTATION].[ID]) as EXCLUDESELECTIONS,
        coalesce
        (
            [APPEALMAILINGSETUP].[CHANNELCODE], 
            case
                when [MAILPACKAGE].[CHANNELCODE] = 0 then 2
                when [MAILPACKAGE].[CHANNELCODE] = 1 then 1
                else 0
            end
        ) as CHANNELCODE,
        coalesce
        (
            [APPEALMAILINGSETUP].[CHANNELPREFERENCECODE],
            case
                when (select top(1) [MKTPACKAGE].[CHANNELCODE] from dbo.[MKTPACKAGE] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) = 0 then 1
                else 0
            end
        )as CHANNELPREFERENCECODE,
        coalesce
        (
            [APPEALMAILINGSETUP].[MAILPACKAGEID],
            case
                when [MAILPACKAGE].[ID] is not null and [MAILPACKAGE].[CHANNELCODE] = 0 then [MAILPACKAGE].[ID]
                else null
            end
        ) as MAILPACKAGEID,
        coalesce
        (
            [APPEALMAILINGSETUP].[EMAILPACKAGEID],
            case
                when [MAILPACKAGE].[ID] is not null and [MAILPACKAGE].[CHANNELCODE] = 1 then [MAILPACKAGE].[ID]
                else null
            end
        ) as EMAILPACKAGEID,
        coalesce
        (
            [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
            (select top(1) [ID] from dbo.[ADDRESSPROCESSINGOPTION] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1)
        ) as ADDRESSPROCESSINGOPTIONID,
        coalesce
        (
            [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
            (select top(1) [ID] from dbo.[NAMEFORMATPARAMETER] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1)
        ) as NAMEFORMATPARAMETERID,
        [APPEALMAILING].[CREATEOUTPUTIDSET],
        [APPEALMAILING].[OUTPUTIDSETNAME],
        [APPEALMAILING].[OVERWRITEOUTPUTIDSET],
        [APPEALMAILING].[TSLONG],
        [MKTSEGMENTATION].[ACTIVE],
        [APPEALMAILINGSETUP].[MKTASKLADDERID] as [MKTASKLADDERID],
        (select IDSETREGISTERID from dbo.MKTSEGMENT where ID = APPEALMAILINGSETUP.SEGMENTID) as IDSETREGISTERID,
        [APPEALMAILINGSETUP].[ESTIMATEDRESPONSERATE],
        [APPEALMAILINGSETUP].[ESTIMATEDAVERAGEGIFTAMOUNT]
    from
        dbo.[APPEALMAILING]
    left join
        dbo.[APPEALMAILINGSETUP] on [APPEALMAILINGSETUP].[ID] = [APPEALMAILING].[ID]
    inner join
        dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
    inner join
        dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
    inner join
        dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join
        dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    left join
        dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join
        dbo.[MKTPACKAGE] as [MAILPACKAGE] on [MAILPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    where
        [APPEALMAILING].[ID] = @ID;

    return 0;
end