USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_2

Adds an appeal mailing.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(255)	IN
@MAILDATE	datetime	IN
@APPEALID	uniqueidentifier	IN
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN
@NAMEFORMATPARAMETERID	uniqueidentifier	IN
@HOUSEHOLDINGTYPECODE	tinyint	IN
@MAILEXPORTDEFINITIONID	uniqueidentifier	IN
@EMAILEXPORTDEFINITIONID	uniqueidentifier	IN
@LETTERS	xml	IN
@CREATEOUTPUTIDSET	bit	IN
@OUTPUTIDSETNAME	nvarchar(100)	IN
@OVERWRITEOUTPUTIDSET	bit	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_2
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,    
    @NAME nvarchar(100),
    @DESCRIPTION nvarchar(255) = '',
    @MAILDATE datetime = null,    
    @APPEALID uniqueidentifier,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
    @NAMEFORMATPARAMETERID uniqueidentifier = null,
    @HOUSEHOLDINGTYPECODE tinyint = 0,
    @MAILEXPORTDEFINITIONID uniqueidentifier = null,
    @EMAILEXPORTDEFINITIONID uniqueidentifier = null,
    @LETTERS xml = null,
    @CREATEOUTPUTIDSET bit = 0,
    @OUTPUTIDSETNAME nvarchar(100) = '',
    @OVERWRITEOUTPUTIDSET bit = 0
)
as
begin
    declare @CURRENTDATE datetime = getDate();

    begin try
        if @ID is null
            set @ID = newid();

        if @CHANGEAGENTID is null
            exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        -- Add mailing information first b/c foreign key points from AppealMailing to MKTSegmentation        

        exec dbo.USP_COMMUNICATIONS_CREATEORUPDATEMAILING_2            
            @ID,
            @CURRENTAPPUSERID,
            @CHANGEAGENTID,
            1,                            -- CommunicationTypeCode

            1,                            -- MailingTypeCode

            @NAME,
            @DESCRIPTION,
            @MAILDATE,
            @APPEALID,
            @ADDRESSPROCESSINGOPTIONID,
            @NAMEFORMATPARAMETERID,
            @HOUSEHOLDINGTYPECODE,
            @MAILEXPORTDEFINITIONID,
            @EMAILEXPORTDEFINITIONID,
            @LETTERS,
            @CREATEOUTPUTIDSET,
            @OUTPUTIDSETNAME,
            @OVERWRITEOUTPUTIDSET,
            null;                                                -- PACKAGESITEID;    


        insert into dbo.[APPEALMAILING] 
            ([ID], [APPEALID], [CREATEOUTPUTIDSET], [OUTPUTIDSETNAME], [OVERWRITEOUTPUTIDSET], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
        values
            (@ID, @APPEALID, @CREATEOUTPUTIDSET, @OUTPUTIDSETNAME, @OVERWRITEOUTPUTIDSET, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        --Because the appeal mailing name uniqueness depends on the appeal, we need to check for name uniqueness after the appeal mailing is created.

        if dbo.[UFN_MKTSEGMENTATION_NAMEEXISTS](@NAME, 0, 1) = 1
            begin
                --Name is not unique, throw error.

                raiserror('BBERR_MKTSEGMENTATION_VALIDNAME', 13, 1);
            end

        -- If Enterprise don't install into setup table

        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 0
            begin
                insert into dbo.[APPEALMAILINGSETUP]
                    ([ID], [MAILEXPORTDEFINITIONID], [EMAILEXPORTDEFINITIONID], [HOUSEHOLDINGTYPECODE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
                values
                    (@ID, @MAILEXPORTDEFINITIONID, @EMAILEXPORTDEFINITIONID, @HOUSEHOLDINGTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                    
            end

    end try

    begin catch
        exec dbo.[USP_RAISE_ERROR];
        return 1;
    end catch

    return 0;
end