USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_MINIMAL

The save procedure used by the edit dataform template "Appeal Mailing Edit Data Form For Minimal Data Ext".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@MAILDATE	datetime	IN	Mail date
@MAILINGBUDGET	money	IN	Budget
@APPEALID	uniqueidentifier	IN	Appeal
@SELECTEDSELECTIONS	xml	IN
@SELECTEDEXCLUSIONS	xml	IN	Exclude constituents with these
@EXCLUDESELECTIONS	xml	IN	Exclude records in these selections
@EXCLUSIONDATETYPECODE	tinyint	IN	Consider exclusions as of
@EXCLUSIONASOFDATE	datetime	IN	Consider exclusions as of
@CHANNELCODE	tinyint	IN
@CHANNELPREFERENCECODE	tinyint	IN	If the constituent does not have a preference, attempt to send...
@MAILPACKAGEID	uniqueidentifier	IN	Mail package
@EMAILPACKAGEID	uniqueidentifier	IN	Email package
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	IN	Address processing
@NAMEFORMATPARAMETERID	uniqueidentifier	IN	Name format
@HOUSEHOLDINGTYPECODE	tinyint	IN	Include
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	IN	Also include qualifying individuals who are not members of any household
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	IN	Also include qualifying households which do not have any members
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	IN	Send to one person per household
@CREATEOUTPUTIDSET	bit	IN	Create selection from results
@OUTPUTIDSETNAME	nvarchar(100)	IN	Selection name
@OVERWRITEOUTPUTIDSET	bit	IN	Overwrite existing selection
@FINALSAVECODE	tinyint	IN
@OUTPUTPATH	nvarchar(256)	IN
@MKTASKLADDERID	uniqueidentifier	IN	Ask ladder
@TASKS	xml	IN
@SELECTIONS	xml	IN	Selection
@EXCLUSIONS	xml	IN	Exclusions

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP_MINIMAL (
        @ID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @NAME nvarchar(100),
        @DESCRIPTION nvarchar(255),
        @MAILDATE datetime,
        @MAILINGBUDGET money,
        @APPEALID uniqueidentifier,
        @SELECTEDSELECTIONS xml,
      @SELECTEDEXCLUSIONS xml,
        @EXCLUDESELECTIONS xml,
        @EXCLUSIONDATETYPECODE tinyint,
        @EXCLUSIONASOFDATE datetime,
        @CHANNELCODE tinyint,
        @CHANNELPREFERENCECODE tinyint,
        @MAILPACKAGEID uniqueidentifier,
        @EMAILPACKAGEID uniqueidentifier,
        @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
        @NAMEFORMATPARAMETERID uniqueidentifier,
        @HOUSEHOLDINGTYPECODE tinyint,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit,
        @CREATEOUTPUTIDSET bit,
        @OUTPUTIDSETNAME nvarchar(100),
        @OVERWRITEOUTPUTIDSET bit,
        @FINALSAVECODE tinyint,
        @OUTPUTPATH nvarchar(256),
      @MKTASKLADDERID uniqueidentifier,
      @TASKS xml,
      @SELECTIONS xml,
      @EXCLUSIONS xml
)
as
  begin
      set nocount on;
    declare @TempTbl table ([ID] uniqueidentifier);
    declare @TaskTempTbl table ([TASKID] uniqueidentifier, [SUBJECT] nvarchar(100), [NOTES] nvarchar(256), [STATUSCODE] tinyint,
              [DATEDUE] UDT_FUZZYDATE, [DATECOMPLETED] datetime, [OWNERID] uniqueidentifier);
    declare @RemindersTempTbl table ([TASKID] uniqueidentifier, [NAME] nvarchar(100),[SENDDATE] datetime);
    declare @REMINDERS xml;
    declare @count int;

      if @CHANGEAGENTID is null  
          exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

      declare @CURRENTDATE datetime
      set @CURRENTDATE = getdate()

      begin try

      insert into @TempTbl
        select
                T.c.value('(SELECTIONID)[1]', 'uniqueidentifier') as ID
            from    
                @SELECTIONS.nodes('/SELECTIONS/ITEM') T(c);

          set @SELECTEDSELECTIONS = (
              select newID() as ID,
          temp.ID as SELECTIONID,
          IDSETREGISTER.NAME as SELECTIONLABEL
          from @TempTbl as temp
          inner join dbo.IDSETREGISTER on IDSETREGISTER.ID = temp.ID 
              for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64);

          exec dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP @ID, @CURRENTAPPUSERID, @CHANGEAGENTID, @NAME, @DESCRIPTION, @MAILDATE, @MAILINGBUDGET,
              @APPEALID, @SELECTEDSELECTIONS, @SELECTEDEXCLUSIONS, @EXCLUDESELECTIONS, @EXCLUSIONDATETYPECODE, @EXCLUSIONASOFDATE, @CHANNELCODE,
              @CHANNELPREFERENCECODE, @MAILPACKAGEID, @EMAILPACKAGEID, @ADDRESSPROCESSINGOPTIONID, @NAMEFORMATPARAMETERID, @HOUSEHOLDINGTYPECODE,
              @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD, @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS, @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
              @CREATEOUTPUTIDSET, @OUTPUTIDSETNAME, @OVERWRITEOUTPUTIDSET, 0, '', @MKTASKLADDERID;

      delete from dbo.APPEALMAILINGTASK where SEGMENTATIONID = @ID;

      insert into @TaskTempTbl
        select
          T.c.value('(TASKID)[1]', 'uniqueidentifier') as 'TASKID',
          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'
          from @TASKS.nodes('/TASKS/ITEM') T(c);

        update @TaskTempTbl
          set TASKID = newID()
          where TASKID is null;

      select @count = count(*) from @TaskTempTbl

      if @count > 0
        begin

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

          declare TASKCURSOR cursor local fast_forward for
            select TASKID from @TaskTempTbl;

          insert into @RemindersTempTbl
            select
              t.c.value('(TASKID)[1]', 'uniqueidentifier') as 'TASKID',
              t.c.value('(NAME)[1]', 'nvarchar(max)') as 'NAME',
              t.c.value('(SENDDATE)[1]', 'datetime') as 'SENDDATE'
              from @TASKS.nodes('/TASKS/ITEM/REMINDERS/ITEM') t(c);

          open TASKCURSOR;
          fetch next from TASKCURSOR into @CURSORID

          while (@@FETCH_STATUS = 0)
            begin
              set @REMINDERS = (
                select REMINDERS.NAME as NAME,
                  REMINDERS.SENDDATE as SENDDATE,
                  coalesce(SENT,0) as SENT
                  from @RemindersTempTbl as REMINDERS
                  left join @TaskTempTbl as TASKS
                    on REMINDERS.TASKID = TASKS.TASKID
                  left join APPEALMAILINGTASKREMINDER on APPEALMAILINGTASKREMINDER.APPEALMAILINGTASKID = TASKS.TASKID 
                  where REMINDERS.TASKID = @CURSORID
                  for xml raw('ITEM'),type,elements,root('REMINDERS'),BINARY BASE64);

              select     @SUBJECT = SUBJECT,
                @NOTES = coalesce(NOTES,''),
                @DATEDUE = coalesce(DATEDUE,''),
                @DATECOMPLETED = coalesce(DATECOMPLETED,null),
                @STATUSCODE = coalesce(STATUSCODE,0),
                @OWNERID = coalesce(OWNERID,null),
                @TASKID = @CURSORID
                from @TaskTempTbl as TASKS
                  where (TASKS.TASKID = @CURSORID)

              if exists(select top 1 * from dbo.APPEALMAILINGTASK where ID = @CURSORID)              
                begin
                  exec dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGTASK_2 @CURSORID, @SUBJECT, @NOTES, @DATEDUE,
                    @DATECOMPLETED, @STATUSCODE, @OWNERID, @CHANGEAGENTID, @CURRENTAPPUSERID, @REMINDERS;
                end
              else
                begin
                  exec USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGTASK @TASKID output, @ID, @SUBJECT, @NOTES, 
                    @DATEDUE, @DATECOMPLETED,@STATUSCODE,@OWNERID,null,@CURRENTAPPUSERID,null,@REMINDERS;
                end
              set @TASKID = null;

              fetch next from TASKCURSOR into @CURSORID
            end

          close TASKCURSOR;
          deallocate TASKCURSOR;
        end
      end try

    begin catch
          exec dbo.USP_RAISE_ERROR
          return 1
      end catch

    return 0;
  end