USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORT_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORT_PRELOAD]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @TEMPLATES xml = null output,
  @EFFORTBASECURRENCYID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @APPEALINFORMATION xml = null output,
  @ISBBEC bit = null output,
  @EFFORTSITEID uniqueidentifier = null output
)
as
  set nocount on;

  set @EFFORTSITEID = dbo.UFN_APPUSER_DEFAULTSITEFORUSER(@CURRENTAPPUSERID)

  if @EFFORTBASECURRENCYID is null
    set @EFFORTBASECURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);

  set @TEMPLATES = (
  select
    [MKTCOMMUNICATIONTEMPLATE].[ID] as '@ID',
    [MKTCOMMUNICATIONTEMPLATE].[NAME] as '@NAME'
  from
    dbo.[MKTCOMMUNICATIONTEMPLATE]
    where [MKTCOMMUNICATIONTEMPLATE].[ISACTIVE] = 1 and
    [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] is null and
    [MKTCOMMUNICATIONTEMPLATE].[PARENTCOMMUNICATIONTEMPLATEID] is null and
    dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTCOMMUNICATIONTEMPLATE].[SITEID]) = 1
    order by [MKTCOMMUNICATIONTEMPLATE].[NAME]
    for xml path('ITEM'),root('TEMPLATES'),elements,binary base64
  );

  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, 'C6B6011C-2D62-4A7C-95F3-9C0566287EE7', 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;