USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_APPEALMAILINGSETUP_MINIMAL

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@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
@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
@TASKS	xml	IN
@SELECTIONS	xml	IN	Selection

Definition

Copy


    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_APPEALMAILINGSETUP_MINIMAL
    (
        @ID uniqueidentifier = null output,
        @CURRENTAPPUSERID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @NAME nvarchar(100),
        @DESCRIPTION nvarchar(255) = '',
        @MAILDATE datetime = null,
        @MAILINGBUDGET money = 0,
        @APPEALID uniqueidentifier,
        @SELECTEDSELECTIONS xml = null,
        @EXCLUDESELECTIONS xml = null,
        @EXCLUSIONDATETYPECODE tinyint = 0,
        @EXCLUSIONASOFDATE datetime = null,
        @CHANNELCODE tinyint = 0,
        @CHANNELPREFERENCECODE tinyint = 0,
        @MAILPACKAGEID uniqueidentifier = null,
        @EMAILPACKAGEID uniqueidentifier = null,
        @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
        @NAMEFORMATPARAMETERID uniqueidentifier = null,
        @HOUSEHOLDINGTYPECODE tinyint = 1,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0 ,
        @CREATEOUTPUTIDSET bit = 0,
        @OUTPUTIDSETNAME nvarchar(100) = '',
        @OVERWRITEOUTPUTIDSET bit = 0,
        @FINALSAVECODE tinyint = 0,
        @OUTPUTPATH nvarchar(256) = '',
      @TASKS xml = null,
      @SELECTIONS xml = null
    )

    as
    begin
    declare @EXCLUSIONS xml = null;
    declare @MKTASKLADDERID uniqueidentifier = null;
    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;

    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 USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP     @ID output, @CURRENTAPPUSERID, @CHANGEAGENTID, @NAME, @DESCRIPTION,
        @MAILDATE,    @MAILINGBUDGET,    @APPEALID,    @SELECTEDSELECTIONS,    @EXCLUSIONS, @EXCLUDESELECTIONS, @EXCLUSIONDATETYPECODE,
        @EXCLUSIONASOFDATE, @CHANNELCODE,    @CHANNELPREFERENCECODE,    @MAILPACKAGEID,    @EMAILPACKAGEID, @ADDRESSPROCESSINGOPTIONID,
        @NAMEFORMATPARAMETERID,    @HOUSEHOLDINGTYPECODE, @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD, @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD,    @CREATEOUTPUTIDSET,    @OUTPUTIDSETNAME,    @OVERWRITEOUTPUTIDSET, @FINALSAVECODE,
        @OUTPUTPATH, @MKTASKLADDERID;

      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 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 NAME as NAME,
                  SENDDATE as SENDDATE
                  from @RemindersTempTbl as REMINDERS
                  left join @TaskTempTbl as TASKS
                    on REMINDERS.TASKID = 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,''),
                @STATUSCODE = coalesce(STATUSCODE,0),
                @OWNERID = coalesce(OWNERID,null)
                from @TaskTempTbl as TASKS
                  where (TASKS.TASKID = @CURSORID)

              exec 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
            end

          close TASKCURSOR;
          deallocate TASKCURSOR;
        end

    end try

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

      return 0;
  end