USP_DATAFORMTEMPLATE_ADDLOAD_APPEALMAILINGSETUP_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDLOAD_APPEALMAILINGSETUP_2
(
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin
    declare @CONSTITUENTRECORDTYPEID uniqueidentifier;
    select @CONSTITUENTRECORDTYPEID = [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT';

    -- Retrieve the ID for the Default Communication Exclusions

    declare @DEFAULTEXCLUSIONEXISTS bit;
    declare @DEFAULTCOMMUNICATIONEXCLUSIONID uniqueidentifier;    
    declare @EXCLUDEBASEDONRECENTCOMMUNICATION bit;
    declare @NUMRECENTCOMMUNICATIONPERIODS int;
    declare @RECENTCOMMUNICATIONPERIODTYPECODE tinyint;
    declare @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR bit;
    declare @NUMTOTALCOMMUNICATIONSINPASTYEAR int;
    declare @EXCLUDEBASEDONRECENTGIVING bit;
    declare @NUMRECENTGIVINGPERIODS int;
    declare @RECENTGIVINGPERIODTYPECODE tinyint;
    declare @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR bit;
    declare @TOTALREVENUEAMOUNTINPASTYEAR money;
    declare @COMMUNICATIONTYPES xml;

    exec dbo.USP_DATAFORMTEMPLATE_VIEW_DEFAULTCOMMUNICATIONLETTERACTIVITYEXCLUSIONS
        @DEFAULTEXCLUSIONEXISTS output,
        @DEFAULTCOMMUNICATIONEXCLUSIONID output;

    if @DEFAULTEXCLUSIONEXISTS = 1
        select
            @EXCLUDEBASEDONRECENTCOMMUNICATION = EXCLUDEBASEDONRECENTCOMMUNICATION,
            @NUMRECENTCOMMUNICATIONPERIODS = NUMRECENTCOMMUNICATIONPERIODS,
            @RECENTCOMMUNICATIONPERIODTYPECODE = RECENTCOMMUNICATIONPERIODTYPECODE,
            @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR = EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
            @NUMTOTALCOMMUNICATIONSINPASTYEAR = NUMTOTALCOMMUNICATIONSINPASTYEAR,
            @EXCLUDEBASEDONRECENTGIVING = EXCLUDEBASEDONRECENTGIVING,
            @NUMRECENTGIVINGPERIODS = NUMRECENTGIVINGPERIODS,
            @RECENTGIVINGPERIODTYPECODE = RECENTGIVINGPERIODTYPECODE,
            @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR = EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
            @TOTALREVENUEAMOUNTINPASTYEAR = TOTALREVENUEAMOUNTINPASTYEAR,           
            @COMMUNICATIONTYPES = dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES_TOITEMLISTXML(ID)
        from dbo.COMMUNICATIONLETTERACTIVITYEXCLUSIONS
        where ID = @DEFAULTCOMMUNICATIONEXCLUSIONID;

    select
        @CONSTITUENTRECORDTYPEID as CONSTITUENTRECORDTYPEID,

        dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 0) as MAILEXPORTDEFINITIONID,
        dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 1) as EMAILEXPORTDEFINITIONID,

        dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDDEFAULTSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@CURRENTAPPUSERID) as REQUIREDANDEFAULTEXCLUSIONS,
        (
            select top(1) [ID] 
            from dbo.[ADDRESSPROCESSINGOPTION] 
            where [ISDEFAULT] = 1 
                and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1
        ) as ADDRESSPROCESSINGOPTIONID,
        (
            select top(1) [ID] 
            from dbo.[NAMEFORMATPARAMETER] 
            where [ISDEFAULT] = 1 
                and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1
        ) as NAMEFORMATPARAMETERID,        

        @EXCLUDEBASEDONRECENTCOMMUNICATION as EXCLUDEBASEDONRECENTCOMMUNICATION,
        @NUMRECENTCOMMUNICATIONPERIODS as NUMRECENTCOMMUNICATIONPERIODS,
        @RECENTCOMMUNICATIONPERIODTYPECODE as RECENTCOMMUNICATIONPERIODTYPECODE,
        @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR as EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
        @NUMTOTALCOMMUNICATIONSINPASTYEAR as NUMTOTALCOMMUNICATIONSINPASTYEAR,
        @EXCLUDEBASEDONRECENTGIVING as EXCLUDEBASEDONRECENTGIVING,
        @NUMRECENTGIVINGPERIODS as NUMRECENTGIVINGPERIODS,
        @RECENTGIVINGPERIODTYPECODE as RECENTGIVINGPERIODTYPECODE,
        @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR as EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
        @TOTALREVENUEAMOUNTINPASTYEAR as TOTALREVENUEAMOUNTINPASTYEAR,           
        @COMMUNICATIONTYPES as COMMUNICATIONTYPES;

end