USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPASSIVESEGMENTGROUP

The save procedure used by the add dataform template "Public Media Marketing Effort Segment Group Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SEGMENTATIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@SEGMENTGROUPID	uniqueidentifier	IN	Segment group
@PACKAGEID	uniqueidentifier	IN	Package
@PACKAGECODEVALUEID	uniqueidentifier	IN	Package code value ID
@PACKAGECODE	nvarchar(10)	IN	Package code
@EXPOSURESTARTDATE	date	IN	Start date
@EXPOSUREENDDATE	date	IN	End date
@RESPONSERATE	decimal(5, 2)	IN	Response rate
@GIFTAMOUNT	money	IN	Gift amount

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPASSIVESEGMENTGROUP]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @SEGMENTATIONID uniqueidentifier,
  @SEGMENTGROUPID uniqueidentifier,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODEVALUEID uniqueidentifier = null,
  @PACKAGECODE nvarchar(10) = '',
  @EXPOSURESTARTDATE date = null,
  @EXPOSUREENDDATE date = null,
  @RESPONSERATE decimal(5,2) = 5,
  @GIFTAMOUNT money
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;

  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTTYPECODE tinyint;
  declare @SEGMENTATIONIDSEGMENTTYPECODE nvarchar(38);
  declare @SEGMENTCODEVALUEID uniqueidentifier;
  declare @SEGMENTCODE nvarchar(10);
  declare @CHANNELSOURCECODEVALUEID uniqueidentifier;
  declare @CHANNELSOURCECODE nvarchar(10);

  select @MAILINGTYPECODE = [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  begin try
    -- check if the mailing is currently being activated

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    select 
      @PACKAGECODEVALUEID = [PARTDEFINITIONVALUESID],
      @PACKAGECODE = case when len(@PACKAGECODE) = 0 then [CODE] else @PACKAGECODE end,
      @CHANNELSOURCECODEVALUEID = [CHANNELPARTDEFINITIONVALUESID],
      @CHANNELSOURCECODE = [CHANNELSOURCECODE]
    from dbo.[MKTPACKAGE] 
    where [ID] = @PACKAGEID;

    declare SEGMENTCURSOR cursor local fast_forward for
      select 
        [MKTSEGMENT].[ID], 
        [MKTSEGMENT].[SEGMENTTYPECODE], 
        [MKTSEGMENT].[PARTDEFINITIONVALUESID],
        [MKTSEGMENT].[CODE]
      from dbo.[MKTGROUPSEGMENTS]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
      where [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = @SEGMENTGROUPID
      and [MKTSEGMENT].[SEGMENTTYPECODE] in (6, 7, 8);

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTTYPECODE, @SEGMENTCODEVALUEID, @SEGMENTCODE;

    while (@@FETCH_STATUS = 0)
    begin
      set @ID = null;
      set @SEGMENTATIONIDSEGMENTTYPECODE = convert(nvarchar(36), @SEGMENTATIONID) + '|' + convert(nvarchar(1), @SEGMENTTYPECODE);

      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENTPASSIVE] 
        @ID = @ID output, 
        @CHANGEAGENTID = @CHANGEAGENTID, 
        @SEGMENTATIONIDSEGMENTTYPECODE = @SEGMENTATIONIDSEGMENTTYPECODE,
        @SEGMENTID = @SEGMENTID,
        @CODEVALUEID = @SEGMENTCODEVALUEID,
        @CODE = @SEGMENTCODE,
        @PACKAGEID = @PACKAGEID,
        @PACKAGECODEVALUEID = @PACKAGECODEVALUEID,
        @PACKAGECODE = @PACKAGECODE,
        @CHANNELCODEVALUEID = @CHANNELSOURCECODEVALUEID,
        @CHANNELCODE = @CHANNELSOURCECODE,
        @EXPOSURESTARTDATE = @EXPOSURESTARTDATE,
        @EXPOSUREENDDATE = @EXPOSUREENDDATE,
        @RESPONSERATE = @RESPONSERATE,
        @GIFTAMOUNT = @GIFTAMOUNT,
        @ITEMLIST = null;

      fetch next from SEGMENTCURSOR into @SEGMENTID, @SEGMENTTYPECODE, @SEGMENTCODEVALUEID, @SEGMENTCODE;
    end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;
  end try

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

  return 0;