USP_DATAFORMTEMPLATE_ADD_MKTTESTCOMMUNICATIONEFFORT_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTTESTCOMMUNICATIONEFFORT_PRELOAD]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @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,
  @ACTIVE bit = null output,
  @HASSEGMENT bit = null output,
  @ISBBEC bit = null output
)
as
  set nocount on;

  --Check if the mailing is currently being activated...

  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

  set @ISBBEC = (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],
    @HASSEGMENT = (case when exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) then 1 else 0 end)
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID]
  left join dbo.[MKTCOMMUNICATIONNAMESCHEME] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONNAMESCHEME].[MKTSEGMENTATIONID]
  where
    [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  --Name pattern may require site so set it here since the name pattern cannot change to a different one

  set @SITEREQUIRED = cast(case
                        when dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '20b8fa36-2ea1-4d06-a8b6-b44fa7e2f392', 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] = @SEGMENTATIONID
    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
  );

  return 0;