USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUP_MINIMAL

The load 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 used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@MAILDATE	datetime	INOUT	Mail date
@MAILINGBUDGET	money	INOUT	Budget
@APPEALID	uniqueidentifier	INOUT	Appeal
@SELECTIONLISTS	xml	INOUT
@SELECTEDSELECTIONS	xml	INOUT
@SELECTEDEXCLUSIONS	xml	INOUT	Exclude constituents with these
@EXCLUDESELECTIONS	xml	INOUT	Exclude records in these selections
@EXCLUSIONLISTS	xml	INOUT
@EXCLUSIONDATETYPECODE	tinyint	INOUT	Consider exclusions as of
@EXCLUSIONASOFDATE	datetime	INOUT	Consider exclusions as of
@CONSTITUENTRECORDTYPEID	uniqueidentifier	INOUT	Constituent record type
@CHANNELCODE	tinyint	INOUT
@CHANNELPREFERENCECODE	tinyint	INOUT	If the constituent does not have a preference, attempt to send...
@MAILPACKAGEID	uniqueidentifier	INOUT	Mail package
@EMAILPACKAGEID	uniqueidentifier	INOUT	Email package
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format
@HOUSEHOLDINGTYPECODE	tinyint	INOUT	Include
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	INOUT	Also include qualifying individuals who are not members of any household
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	INOUT	Also include qualifying households which do not have any members
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	INOUT	Send to one person per household
@CREATEOUTPUTIDSET	bit	INOUT	Create selection from results
@OUTPUTIDSETNAME	nvarchar(100)	INOUT	Selection name
@OVERWRITEOUTPUTIDSET	bit	INOUT	Overwrite existing selection
@FINALSAVECODE	tinyint	INOUT
@OUTPUTPATH	nvarchar(max)	INOUT
@TASKS	xml	INOUT
@SELECTIONS	xml	INOUT	Selection
@MKTASKLADDERID	uniqueidentifier	INOUT	Ask ladder
@EXCLUSIONS	xml	INOUT	Exclusions

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_APPEALMAILINGSETUP_MINIMAL(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @NAME nvarchar(100) = null output,
    @DESCRIPTION nvarchar(255) = null output,
    @MAILDATE datetime = null output,
    @MAILINGBUDGET money = null output,
    @APPEALID uniqueidentifier = null output,
    @SELECTIONLISTS xml = null output,
    @SELECTEDSELECTIONS xml = null output,
      @SELECTEDEXCLUSIONS xml = null output,
      @EXCLUDESELECTIONS xml = null output,
    @EXCLUSIONLISTS xml = null output,
    @EXCLUSIONDATETYPECODE tinyint = null output,
    @EXCLUSIONASOFDATE datetime = null output,
    @CONSTITUENTRECORDTYPEID uniqueidentifier = null output,
    @CHANNELCODE tinyint = null output,
    @CHANNELPREFERENCECODE tinyint = null output,
    @MAILPACKAGEID uniqueidentifier = null output,
    @EMAILPACKAGEID uniqueidentifier = null output,
    @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
    @NAMEFORMATPARAMETERID uniqueidentifier = null output,
    @HOUSEHOLDINGTYPECODE tinyint = null output,
    @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = null output,
    @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = null output,
    @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
    @CREATEOUTPUTIDSET bit = null output,
    @OUTPUTIDSETNAME nvarchar(100) = null output,
    @OVERWRITEOUTPUTIDSET bit = null output,
    @FINALSAVECODE tinyint = null output,
    @OUTPUTPATH nvarchar(max) = null output,
    @TASKS xml = null output,
    @SELECTIONS xml = null output,
    @MKTASKLADDERID uniqueidentifier = null output,
    @EXCLUSIONS xml = null output
)
as
  begin
    set nocount on;
  declare @TempTbl table ([ID] uniqueidentifier);

    set @DATALOADED = 0
    set @TSLONG = 0

    select @CONSTITUENTRECORDTYPEID = [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT';

    select top(1)
    @DATALOADED = 1,
    @TSLONG = APPEALMAILING.TSLONG,
        @NAME = [MKTSEGMENTATION].[NAME],
        @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
        @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
        @MAILINGBUDGET = [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
        @APPEALID = [APPEALMAILING].[APPEALID],
        @SELECTIONLISTS = (select [NAME] as GROUPNAME from dbo.[UFN_SELECTION_GETGROUPNAMES](@CONSTITUENTRECORDTYPEID) for xml raw('ITEM'),type,elements,root('SELECTIONLISTS'),binary base64),
        --@SELECTEDSELECTIONS = coalesce([APPEALMAILINGSETUP].[SELECTEDSELECTIONS], dbo.[UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[SEGMENTID], @CURRENTAPPUSERID)),
    @SELECTEDSELECTIONS = coalesce(dbo.UFN_APPEALMAILINGSETUP_GETSELECTIONS_TOITEMLISTXML(APPEALMAILINGSETUP.ID), dbo.[UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[SEGMENTID], @CURRENTAPPUSERID)),    
        @SELECTEDEXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
        @HOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
        @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
        @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
        @CONSTITUENTRECORDTYPEID = (select [ID] from dbo.[RECORDTYPE] where upper([NAME]) = 'CONSTITUENT'),
        @EXCLUDESELECTIONS = dbo.[UFN_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
        @CHANNELCODE = coalesce
        (
            [APPEALMAILINGSETUP].[CHANNELCODE], 
            case
                when [MAILPACKAGE].[CHANNELCODE] = 0 then 2
                when [MAILPACKAGE].[CHANNELCODE] = 1 then 1
                else 0
            end
        ),
        @CHANNELPREFERENCECODE = coalesce
        (
            [APPEALMAILINGSETUP].[CHANNELPREFERENCECODE],
            case
                when (select top(1) [MKTPACKAGE].[CHANNELCODE] from dbo.[MKTPACKAGE] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) = 0 then 1
                else 0
            end
        ),
        @MAILPACKAGEID = coalesce
        (
            [APPEALMAILINGSETUP].[MAILPACKAGEID],
            case
                when [MAILPACKAGE].[ID] is not null and [MAILPACKAGE].[CHANNELCODE] = 0 then [MAILPACKAGE].[ID]
                else null
            end
        ),
        @EMAILPACKAGEID = coalesce
        (
            [APPEALMAILINGSETUP].[EMAILPACKAGEID],
            case
                when [MAILPACKAGE].[ID] is not null and [MAILPACKAGE].[CHANNELCODE] = 1 then [MAILPACKAGE].[ID]
                else null
            end
        ),
        @ADDRESSPROCESSINGOPTIONID = coalesce
        (
            [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
            (select top(1) [ID] from dbo.[ADDRESSPROCESSINGOPTION] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1)
        ),
        @NAMEFORMATPARAMETERID = coalesce
        (
            [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
            (select top(1) [ID] from dbo.[NAMEFORMATPARAMETER] where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1)
        ),
        @CREATEOUTPUTIDSET = [APPEALMAILING].[CREATEOUTPUTIDSET],
        @OUTPUTIDSETNAME = [APPEALMAILING].[OUTPUTIDSETNAME],
        @OVERWRITEOUTPUTIDSET = [APPEALMAILING].[OVERWRITEOUTPUTIDSET],
    @MKTASKLADDERID = [APPEALMAILINGSETUP].[MKTASKLADDERID]
  from
        dbo.[APPEALMAILING]
    left join
        dbo.[APPEALMAILINGSETUP] on [APPEALMAILINGSETUP].[ID] = [APPEALMAILING].[ID]
    inner join
        dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
    inner join
        dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
    inner join
        dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join
        dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    left join
        dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join
        dbo.[MKTPACKAGE] as [MAILPACKAGE] on [MAILPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    where
        [APPEALMAILING].[ID] = @ID;

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

  set @SELECTIONS = (
    select TEMP.ID as SELECTIONID
      from @TempTbl as TEMP
    for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64);

  set @TASKS = (
    SELECT
      ID as TASKID,
      SUBJECT,
      NOTES,
      STATUSCODE,
      DATEDUE,
      DATECOMPLETED,
      OWNERID,      
      (
            SELECT
                  [APPEALMAILINGTASKID] as TASKID,
                  [NAME],
                  [SENDDATE]
            FROM
                  dbo.APPEALMAILINGTASKREMINDER
            WHERE
                  APPEALMAILINGTASKREMINDER.APPEALMAILINGTASKID = APPEALMAILINGTASK.ID
            for xml raw('ITEM'),type,elements,root('REMINDERS'),BINARY BASE64
      )
        FROM
          dbo.APPEALMAILINGTASK
        where
            APPEALMAILINGTASK.SEGMENTATIONID = @ID

  for xml raw('ITEM'),type,elements,root('TASKS'),BINARY BASE64)

    return 0;
  end