USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONEFFORT

Parameters

Parameter Parameter Type Mode Description
@ID uniqueidentifier IN
@DATALOADED bit INOUT
@TSLONG bigint INOUT
@EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier INOUT
@TEMPLATENAME nvarchar(100) INOUT
@EFFORTNAME nvarchar(100) INOUT
@COMMUNICATIONNAMESCHEMEID uniqueidentifier INOUT
@EFFORTNAMESCHEMETEXT xml INOUT
@NAMEPARTS xml INOUT
@EFFORTDESCRIPTION nvarchar(255) INOUT
@DESCRIPTIONLOCKED bit INOUT
@APPEALINFORMATION xml INOUT
@APPEALLOCKED bit INOUT
@EFFORTCHANNELCODE tinyint INOUT
@CHANNELCODELOCKED bit INOUT
@EFFORTSITEID uniqueidentifier INOUT
@SITEREQUIRED bit INOUT
@EFFORTDUEDATE datetime INOUT
@EFFORTLAUNCHDATE datetime INOUT
@EFFORTALLOWRESERVINGFINDERNUMBERS bit INOUT
@ALLOWRESERVINGFINDERNUMBERSLOCKED bit INOUT
@EFFORTALLOWSPECIFYBUDGET bit INOUT
@ALLOWSPECIFYBUDGETLOCKED bit INOUT
@EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit INOUT
@ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED bit INOUT
@EFFORTBASECURRENCYID uniqueidentifier INOUT
@CURRENTAPPUSERID uniqueidentifier IN
@ACTIVE bit INOUT
@DATECREATED date INOUT
@HASSEGMENT bit INOUT
@PLANPATH nvarchar(405) INOUT

Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONEFFORT
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier = null output,
  @TEMPLATENAME nvarchar(100) = null output,
  @EFFORTNAME nvarchar(100) = null output,
  @COMMUNICATIONNAMESCHEMEID uniqueidentifier = null output,
  @EFFORTNAMESCHEMETEXT xml = null output,
  @NAMEPARTS xml = null output,
  @EFFORTDESCRIPTION nvarchar(255) = null output,
  @DESCRIPTIONLOCKED bit = null output,
  @APPEALINFORMATION xml = null output,
  @APPEALLOCKED bit = null output,
  @EFFORTCHANNELCODE tinyint = null output,
  @CHANNELCODELOCKED bit = null output,
  @EFFORTSITEID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @EFFORTDUEDATE datetime = null output,
  @EFFORTLAUNCHDATE datetime = null output,
  @EFFORTALLOWRESERVINGFINDERNUMBERS bit = null output,
  @ALLOWRESERVINGFINDERNUMBERSLOCKED bit = null output,
  @EFFORTALLOWSPECIFYBUDGET bit = null output,
  @ALLOWSPECIFYBUDGETLOCKED bit = null output,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit = null output,
  @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED bit = null output,
  @EFFORTBASECURRENCYID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @ACTIVE bit = null output,
  @DATECREATED date = null output,
  @HASSEGMENT bit = null output,
  @PLANPATH nvarchar(405) = null output
)
as

  set nocount on;

  -- be sure to set these, in case the select returns no rows
  set @DATALOADED = 0;
  set @TSLONG = 0;

  declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

  set @ACTIVE = 1;

  select
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @EFFORTNAME = [MKTSEGMENTATION].[NAME],
    @EFFORTDESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @EFFORTSITEID = [MKTSEGMENTATION].[SITEID],
    @EFFORTCHANNELCODE = [MKTSEGMENTATION].[CHANNELCODE],
    @EFFORTDUEDATE = [MKTSEGMENTATION].[DUEDATE],
    @EFFORTLAUNCHDATE = [MKTSEGMENTATION].[MAILDATE],
    @EFFORTALLOWRESERVINGFINDERNUMBERS = [MKTSEGMENTATION].[ALLOWRESERVINGFINDERNUMBERS],
    @EFFORTALLOWSPECIFYBUDGET = [MKTSEGMENTATION].[ALLOWSPECIFYBUDGET],
    @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = [MKTSEGMENTATION].[ALLOWEXCLUDEPREVIOUSEFFORTS],
    @EFFORTBASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @EFFORTCOMMUNICATIONTEMPLATEID = [MKTCOMMUNICATIONTEMPLATE].[ID],
    @TEMPLATENAME = [MKTCOMMUNICATIONTEMPLATE].[NAME],
    @COMMUNICATIONNAMESCHEMEID = [MKTCOMMUNICATIONNAMESCHEME].[ID],
    @DATECREATED = [MKTSEGMENTATION].[DATEADDED],
    @HASSEGMENT = (case when exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) then 1 else 0 end),
    @PLANPATH = dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MKTSEGMENTATION].[MARKETINGPLANITEMID], 1),
    @DATALOADED = 1,
    @TSLONG = [MKTSEGMENTATION].[TSLONG]
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
  left join dbo.[MKTCOMMUNICATIONNAMESCHEME] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONNAMESCHEME].[MKTSEGMENTATIONID]
  where
    [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
    begin
      --Name pattern may require site so set it here since the name pattern cannot change to a different one in an edit
      set @SITEREQUIRED = cast(case
                            when dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '60B12DB0-C327-4614-9E4D-A676373EE6D0', 1) = 1 or
                                 exists(select 1 from dbo.[MKTCOMMUNICATIONNAMESCHEMEPART] where [MKTCOMMUNICATIONNAMESCHEMEID] = @COMMUNICATIONNAMESCHEMEID and [NAMEPARTTYPECODE] = 4)
                            then 1
                            else 0
                          end as bit);

      -- get appeal search catalog IDs with their record source names
      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 [APPEAL].[NAME] from dbo.[APPEAL] where [APPEAL].[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 [APPEAL].[DESCRIPTION] from dbo.[APPEAL] where [APPEAL].[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);

      /* load existing communication template to get locked values and name */
      exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONTEMPLATE]
        @ID = @EFFORTCOMMUNICATIONTEMPLATEID,
        @NAME = @TEMPLATENAME output,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @DESCRIPTIONLOCKED = @DESCRIPTIONLOCKED output,
        @APPEALLOCKED = @APPEALLOCKED output,
        @CHANNELCODELOCKED = @CHANNELCODELOCKED output,
        @ALLOWRESERVINGFINDERNUMBERSLOCKED = @ALLOWRESERVINGFINDERNUMBERSLOCKED output,
        @ALLOWSPECIFYBUDGETLOCKED = @ALLOWSPECIFYBUDGETLOCKED output,
        @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED = @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED output;

      /* get the name pattern parts as it holds the values they typed in when adding the effort */
      set @NAMEPARTS = (
        select
          [MKTCOMMUNICATIONNAMESCHEMEPART].[ID] as [@ID],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] as [@NAMEPARTTYPECODE],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[OPTIONTYPECODE] as [@OPTIONTYPECODE],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[SEPARATORTYPECODE] as [@SEPARATORTYPECODE],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[SEQUENCE] as [@SEQUENCE],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[VALUE] as [@VALUE],
          [MKTCOMMUNICATIONNAMESCHEMEPART].[COMMUNICATIONEFFORTVALUE] as [@COMMUNICATIONEFFORTVALUE]
        from 
          dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
        where
          [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID] = @COMMUNICATIONNAMESCHEMEID
        order by
          [MKTCOMMUNICATIONNAMESCHEMEPART].[SEQUENCE]
       for xml path('ITEM'),type,elements,root('NAMEPARTS'),BINARY BASE64
      );
    end
  return 0;