USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_MINIMAL_2

Add an appeal mailing with minimal data.

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
@TASKS	xml	IN

Definition

Copy



create procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_MINIMAL_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,
    @TASKS xml = null
)
as
begin
    declare @TaskTempTbl table (
        [ID] uniqueidentifier, 
        [SUBJECT] nvarchar(100), 
        [NOTES] nvarchar(256), 
        [STATUSCODE] tinyint,
        [DATEDUE] UDT_FUZZYDATE, 
        [DATECOMPLETED] datetime, 
        [OWNERID] uniqueidentifier,
        [REMINDERS] xml
    );

    begin try

        exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_2 
            @ID, @CURRENTAPPUSERID, 
            @CHANGEAGENTID, 
            @NAME, 
            @DESCRIPTION, 
            @MAILDATE, 
            @APPEALID, 
            @ADDRESSPROCESSINGOPTIONID, 
            @NAMEFORMATPARAMETERID, 
            @HOUSEHOLDINGTYPECODE, 
            @MAILEXPORTDEFINITIONID, 
            @EMAILEXPORTDEFINITIONID, 
            @LETTERS;

        -- Add tasks

        insert into @TaskTempTbl
            select
                newID() as 'ID',
                T.c.value('(SUBJECT)[1]', 'nvarchar(100)') as 'SUBJECT',
                T.c.value('(NOTES)[1]', 'nvarchar(256)') as 'NOTES',
                T.c.value('(STATUSCODE)[1]', 'tinyint') as 'STATUSCODE',
                T.c.value('(DATEDUE)[1]', 'UDT_FUZZYDATE') as 'DATEDUE',
                T.c.value('(DATECOMPLETED)[1]', 'datetime') as 'DATECOMPLETED',
                T.c.value('(OWNERID)[1]', 'uniqueidentifier') as 'OWNERID',
                T.c.value('(REMINDERS)[1]', 'nvarchar(max)') as 'REMINDERS'
            from @TASKS.nodes('/TASKS/ITEM') T(c);

        declare @CURSORID uniqueidentifier;
        declare @SUBJECT nvarchar(100);
        declare @NOTES nvarchar(256) = '';
        declare @DATEDUE dbo.UDT_FUZZYDATE;
        declare @DATECOMPLETED datetime;
        declare @STATUSCODE tinyint;
        declare @OWNERID uniqueidentifier;
        declare @TASKID uniqueidentifier;

        declare @REMINDERS xml;

        declare TASKCURSOR cursor local fast_forward for
            select 
                ID,
                SUBJECT, 
                NOTES, 
                STATUSCODE,
                DATEDUE, 
                DATECOMPLETED, 
                OWNERID,
                REMINDERS
            from @TaskTempTbl

        open TASKCURSOR;
        fetch next from TASKCURSOR into @CURSORID, @SUBJECT, @NOTES, @STATUSCODE, @DATEDUE, @DATECOMPLETED, @OWNERID, @REMINDERS;

        while (@@FETCH_STATUS = 0)
            begin
                exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGTASK @TASKID output,@ID,@SUBJECT,@NOTES,@DATEDUE,null,@STATUSCODE,@OWNERID,null,@CURRENTAPPUSERID,null,@REMINDERS;
                set @TASKID = null;

                fetch next from TASKCURSOR into @CURSORID, @SUBJECT, @NOTES, @STATUSCODE, @DATEDUE, @DATECOMPLETED, @OWNERID, @REMINDERS;
            end

        close TASKCURSOR;
        deallocate TASKCURSOR;
    end try

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

    return 0;
end