USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORTFROMPLAN_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORTFROMPLAN_PRELOAD]
(
  @MARKETINGPLANITEMID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @TEMPLATES xml = null output,
  @EFFORTNAME nvarchar(100) = null output,
  @EFFORTBASECURRENCYID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @APPEALINFORMATION xml = null output,
  @PLANPATH nvarchar(405) = null output,
  @EFFORTSITEID uniqueidentifier = null output,
  @EFFORTLAUNCHDATE datetime = null output,
  @ISBBEC bit = null output
)
as
  set nocount on;

  declare @PLANSOURCECODEID uniqueidentifier;

  select top 1
    @PLANSOURCECODEID = [MKTMARKETINGPLANITEM].[SOURCECODEID],
    @PLANPATH = dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MKTMARKETINGPLANITEM].[ID], 1),
    @EFFORTLAUNCHDATE = [MKTMARKETINGPLANITEM].[MAILDATE],
    @EFFORTSITEID = [MKTMARKETINGPLAN].[SITEID],
    @EFFORTBASECURRENCYID = [MKTMARKETINGPLANITEM].[BASECURRENCYID],
    @EFFORTNAME = [MKTMARKETINGPLANITEM].[NAME]
  from dbo.[MKTMARKETINGPLANITEM]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
  where dbo.[MKTMARKETINGPLANITEM].[ID] = @MARKETINGPLANITEMID;

  set @TEMPLATES = (
  select
    [MKTCOMMUNICATIONTEMPLATE].[ID] as '@ID',
    [MKTCOMMUNICATIONTEMPLATE].[NAME] as '@NAME'
  from
    dbo.[MKTCOMMUNICATIONTEMPLATE]
    inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATE].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID]
    where [MKTCOMMUNICATIONTEMPLATE].[ISACTIVE] = 1 and
    [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] is null and
    [MKTCOMMUNICATIONTEMPLATE].[PARENTCOMMUNICATIONTEMPLATEID] is null
    and [MKTCOMMUNICATIONTEMPLATE].[BASECURRENCYID] = @EFFORTBASECURRENCYID
    and [MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPECODE] = 0
    and (
          --Planner does not specify source code, so return all templates

          (@PLANSOURCECODEID is null)
          or
          --Template does not lock source code so return this template

          ([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODELOCKED] = 0)
          or
          --Template locks source code so only return template if the source code matches the planner source code

          ([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODELOCKED] = 1 and ([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODEID] = @PLANSOURCECODEID or ([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODEID] is null and @PLANSOURCECODEID is null)))
        )
    and (
        dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        or exists (
                  select
                    1
                  from
                    dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'be893a33-88f1-49e4-9404-1b5d4df90ebd',1) as [SITESFORUSERONFEATURE]
                  where
                    [SITESFORUSERONFEATURE].[SITEID]=[MKTCOMMUNICATIONTEMPLATE].[SITEID]
                    or ([SITESFORUSERONFEATURE].[SITEID] is null and [MKTCOMMUNICATIONTEMPLATE].[SITEID] is null)
                  )
        )
    order by [MKTCOMMUNICATIONTEMPLATE].[NAME]
    for xml path('ITEM'),root('TEMPLATES'),elements,binary base64
  )
  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, 'be893a33-88f1-49e4-9404-1b5d4df90ebd', 1);

  --Get appeal search catalog IDs with the record source name

  set @APPEALINFORMATION = (
    select 
      [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
      [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
      [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD]
    from dbo.[MKTAPPEALRECORDSOURCE]
    inner join QUERYVIEWCATALOG on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
    for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

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

  return 0;