USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATERULE_MULTIPLE

The load procedure used by the edit dataform template "Sponsorship Effort Template Multiple Rule Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SPONSORSHIPMAILINGTEMPLATEID	uniqueidentifier	INOUT	Sponsorship Effort Template
@ASKLADDER	xml	INOUT	Ask ladder
@ASKLADDERLOADED	bit	INOUT	Ask ladder loaded
@ASKLADDERLOCKED	bit	INOUT	Ask ladder locked
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@PACKAGELOADED	bit	INOUT	Package loaded
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@RESPONSERATELOADED	bit	INOUT	Response rate loaded
@GIFTAMOUNT	money	INOUT	Gift amount
@GIFTAMOUNTLOADED	bit	INOUT	Gift amount loaded
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@SPONSORSHIPMAILINGTEMPLATESITEID	uniqueidentifier	INOUT	Sponsorship Effort Template site ID
@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.
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@PACKAGEADDDATAFORMCONTEXT	nvarchar(128)	INOUT	Package add data form context
@PACKAGECODEVALUEID	uniqueidentifier	INOUT	Package code value ID
@EXCLUDE	bit	INOUT	Exclude from effort but show counts
@EXCLUDELOADED	bit	INOUT	Exclude loaded
@EXCLUDELOCKED	bit	INOUT	Exclude locked
@PACKAGEPARTDEFINITIONID	uniqueidentifier	INOUT
@PACKAGECHANNELCODE	tinyint	INOUT
@CHANNELPARTDEFINITIONID	uniqueidentifier	INOUT
@CHANNELCODEVALUEID	uniqueidentifier	INOUT
@CHANNELCODE	nvarchar(10)	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATERULE_MULTIPLE]
( 
  @ID nvarchar(max),
  @DATALOADED bit = 0 output,
  @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier = null output,
  @ASKLADDER xml = null output,
  @ASKLADDERLOADED bit = null output,
  @ASKLADDERLOCKED bit = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @PACKAGELOADED bit = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @RESPONSERATELOADED bit = null output,
  @GIFTAMOUNT money = null output,
  @GIFTAMOUNTLOADED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @SPONSORSHIPMAILINGTEMPLATESITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output,
  @PACKAGECODEVALUEID uniqueidentifier = null output,
  @EXCLUDE bit = null output,
  @EXCLUDELOADED bit = null output,
  @EXCLUDELOCKED bit = null output,
  @PACKAGEPARTDEFINITIONID uniqueidentifier = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @CHANNELPARTDEFINITIONID uniqueidentifier = null output,
  @CHANNELCODEVALUEID uniqueidentifier = null output,
  @CHANNELCODE nvarchar(10) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  declare @RULESTABLE table([ID] uniqueidentifier primary key);
  declare @START int;
  declare @POS int;

  /* parse out the multiple rule IDs */
  set @START = 0;
  select @POS = charindex(',', @ID, @START);

  while (@POS <> 0)
  begin
    insert into @RULESTABLE
      select substring(@ID, @START, @POS - @START);

    set @START = @POS + 1;
    select @POS = charindex(',', @ID, @START);
  end;

  if len(@ID) > 0
    insert into @RULESTABLE
      select substring(@ID, @START, 37);

  select top 1
    @DATALOADED = 1,
    @SPONSORSHIPMAILINGTEMPLATEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID],
    @SOURCECODEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID],
    @SPONSORSHIPMAILINGTEMPLATESITEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SITEID],
    @BASECURRENCYID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[BASECURRENCYID],
    @PACKAGEADDDATAFORMCONTEXT = '3|' + convert(nvarchar(36), [MKTSPONSORSHIPMAILINGTEMPLATERULE].[BASECURRENCYID])
  from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
  inner join @RULESTABLE as [RULESTABLE] on [RULESTABLE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]
  inner join dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID];

  if @DATALOADED = 1
    begin
      /* common exclude */
      if (select count(distinct([MKTSPONSORSHIPMAILINGTEMPLATERULE].[EXCLUDE])) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [R] on [R].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]) = 1
        select top 1
          @EXCLUDE = [EXCLUDE],
          @EXCLUDELOADED = 1,
          @EXCLUDELOCKED = 0
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [R] on [R].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID];
      else
        begin
          set @EXCLUDE = 0;
          set @EXCLUDELOADED = 0;
        end

      /* common record source and common ask ladder */
      if (select count(distinct([MKTSEGMENT].[QUERYVIEWCATALOGID])) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID]) = 1
        begin
          if (select count(distinct(isnull([MKTSPONSORSHIPMAILINGTEMPLATERULE].[ASKLADDERID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]) = 1
          select top 1
              @ASKLADDER = (select 
                              [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                              [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
                              (select top 1 [T1].[ASKLADDERID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] as [T1] inner join @RULESTABLE as [T2] on [T2].[ID] = [T1].[ID]) as [ASKLADDERID]
                            from dbo.[MKTRECORDSOURCE]
                            inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                            where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
                            for xml raw('ITEM'), type, elements, root('ASKLADDER'), binary base64),
              @ASKLADDERLOADED = 1,
              @ASKLADDERLOCKED = 0
            from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
            inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID];
          else
            select top 1
              @ASKLADDER = (select 
                              [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                              [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME]
                            from dbo.[MKTRECORDSOURCE]
                            inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                            where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
                            for xml raw('ITEM'), type, elements, root('ASKLADDER'), BINARY BASE64),
              @ASKLADDERLOADED = 0,
              @ASKLADDERLOCKED = 0
            from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
            inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID];
        end
      else
        begin
          set @ASKLADDERLOADED = 0;
          set @ASKLADDERLOCKED = 1;
        end

      /* common package */
      if (select count(distinct([PACKAGEID])) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]) = 1
        select top 1
          @PACKAGEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID],
          @PACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
          @PACKAGECODEVALUEID = [MKTPACKAGE].[PARTDEFINITIONVALUESID],
          @PACKAGECODE = [MKTPACKAGE].[CODE],
          @CHANNELCODEVALUEID = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID],
          @CHANNELCODE = [MKTPACKAGE].[CHANNELSOURCECODE],
          @PACKAGELOADED = 1
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]
        inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID];
      else
        begin
          set @PACKAGELOADED = 0;
          set @PACKAGECHANNELCODE = 255;
        end

      /* common response rate */
      if (select count(distinct([RESPONSERATE])) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]) = 1
        select top 1
          @RESPONSERATE = [RESPONSERATE],
          @RESPONSERATELOADED = 1
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID];
      else
        begin
          set @RESPONSERATE = 5;
          set @RESPONSERATELOADED = 0;
        end

      /* common gift amount */
      if (select count(distinct([GIFTAMOUNT])) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID]) = 1
        select top 1
          @GIFTAMOUNT = [GIFTAMOUNT],
          @GIFTAMOUNTLOADED = 1
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID];
      else
        set @GIFTAMOUNTLOADED = 0;

      select @TSLONG = max([TSLONG])
      from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
      inner join @RULESTABLE as [S] on [S].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID];

      select @PACKAGEPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 2;
      select @CHANNELPARTDEFINITIONID = [ID] from dbo.[MKTSOURCECODEPARTDEFINITION] where [ITEMTYPECODE] = 3;
    end

  return 0;