USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONPASSIVE

The load procedure used by the edit dataform template "Public Media Marketing Effort Edit Form"

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.
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type
@ACTIVE	bit	INOUT	Active
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SITEID	uniqueidentifier	INOUT	Site
@SITEREQUIRED	bit	INOUT	Site required?
@SITECANBECHANGED	bit	INOUT	Site can be changed?
@MAILDATE	datetime	INOUT	Date
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@CODE	nvarchar(10)	INOUT	Code
@ITEMLIST	xml	INOUT	Items
@ISTESTMAILING	tinyint	INOUT	Is test marketing effort
@ACTIVATIONKPIS	xml	INOUT	Activation KPIs
@USEKPISASDEFAULT	bit	INOUT	Use the chosen KPIs as the default for future marketing efforts
@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
@APPEALINFORMATION	xml	INOUT	Appeal information
@RESPONSES	int	INOUT	Responses
@CANCHANGEBASECURRENCY	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONPASSIVE]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @ACTIVE bit = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SITEID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @SITECANBECHANGED bit = null output,
  @MAILDATE datetime = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @CODE nvarchar(10) = null output,
  @ITEMLIST xml = null output,
  @ISTESTMAILING tinyint = null output,
  @ACTIVATIONKPIS xml = null output, -- obsolete

  @USEKPISASDEFAULT bit = null output, -- obsolete

  @TSLONG bigint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @APPEALINFORMATION xml = null output,
  @RESPONSES integer = null output,
  @CANCHANGEBASECURRENCY bit = null output
)
as
  set nocount on;

  declare @ISBBEC bit;
  declare @RESPONSECOUNTS table(
    [OFFERS] int, 
    [RESPONDERS] int, 
    [RESPONSES] int, 
    [TOTALGIFTAMOUNT] money, 
    [RESPONSERATE] decimal(20, 5), 
    [ORGANIZATIONTOTALGIFTAMOUNT] money,
    [FIRSTRESPONSEDATE] datetime
  );

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

  set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
  set @CANCHANGEBASECURRENCY = (case when (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @ID) > 0 then 0 else 1 end);

  select
    @DATALOADED = 1,
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @NAME = [MKTSEGMENTATION].[NAME],
    @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @SITEID = [MKTSEGMENTATION].[SITEID],
    @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, 'D76EDDB0-5BA6-4CBF-B528-25879060C564', 1),
    @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @CODEVALUEID = [MKTSEGMENTATION].[PARTDEFINITIONVALUESID],
    @CODE = [MKTSEGMENTATION].[CODE],
    @ITEMLIST = dbo.[UFN_MKTSOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
    @ISTESTMAILING = case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null then 0 else 1 end,
    @TSLONG = [MKTSEGMENTATION].[TSLONG],
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
    begin
      set @SITECANBECHANGED = 1; -- obsolete


      set @APPEALINFORMATION = (
        select 
          [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
          [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
          [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
          [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD],
          [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as [APPEALSYSTEMID],
          (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
            (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
           else
            [MKTSEGMENTATIONACTIVATE].[APPEALID]
           end) as [APPEALID],
          (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
            (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier))
           else
            [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION]
           end) as [APPEALDESCRIPTION]
        from dbo.[MKTAPPEALRECORDSOURCE]
        inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
        left join [MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @ID
        where (@ACTIVE = 0 or (@ACTIVE = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALID] <> ''))
        and (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
      for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

      declare @KPITABLE as table (
        [KPICATALOGID] uniqueidentifier,
        [SELECTED] bit,
        [NAME] nvarchar(255),
        [GOALTYPECODE] tinyint,
        [DEFAULT] bit,
        [LOCKED] bit,
        [TEMPLATETYPECODE] tinyint
      );

      insert into @KPITABLE 
        exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS] @ID, 1, @CURRENTAPPUSERID;

      set @ACTIVATIONKPIS = (
        select 
          [KPICATALOGID],
          [SELECTED],
          [NAME],
          [GOALTYPECODE],
          [DEFAULT]
        from @KPITABLE 
        for xml raw('ITEM'), type, elements, root('ACTIVATIONKPIS'), binary base64);

      insert into @RESPONSECOUNTS
        exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @ID, null;

      select top 1 @RESPONSES = [RESPONSES] from @RESPONSECOUNTS;
    end

  return 0;