USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUPLETTERS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUPLETTERS
(
    @SEGMENTATIONID uniqueidentifier,
    @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
            MKTSEGMENTATION.ACTIVE,
            @CONSTITUENTRECORDTYPEID as CONSTITUENTRECORDTYPEID,    
            coalesce(APPEALMAILINGSETUP.MAILEXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 0)) as MAILEXPORTDEFINITIONID,
            coalesce(APPEALMAILINGSETUP.EMAILEXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 1)) as EMAILEXPORTDEFINITIONID,
            dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 0) as DEFAULTMAILEXPORTDEFINITIONID,
            dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(1, 1) as DEFAULTEMAILEXPORTDEFINITIONID,
            dbo.[UFN_COMMUNICATION_GETLETTERS_TOITEMLISTXML](@SEGMENTATIONID, 1) as LETTERS,
            dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDDEFAULTSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@CURRENTAPPUSERID) as REQUIREDANDEFAULTEXCLUSIONS,                            
            @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
        from dbo.APPEALMAILINGSETUP
        inner join dbo.MKTSEGMENTATION
            on APPEALMAILINGSETUP.ID = MKTSEGMENTATION.ID
        where APPEALMAILINGSETUP.ID = @SEGMENTATIONID;
end