USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFROMPLAN_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFROMPLAN_PRELOAD]
(
  @MARKETINGPLANITEMID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CODE nvarchar(10) = null output,
  @NAME nvarchar(100) = null output,
  @PLANPATH nvarchar(405) = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @MAILDATE datetime = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @SITEID uniqueidentifier = null output,
  @SITEREQUIRED bit = null output,
  @MAILINGTYPECODE tinyint = null output,
  @ISBBEC bit = null output,
  @EXCLUSIONS xml = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @ACTIVATIONKPIS xml = null output,
  @APPEALINFORMATION xml = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output,
  @SMARTQUERIESEXIST bit = null output,
  @ITEMLIST xml = null output
)
as
  set nocount on;

  select TOP 1 
    @NAME = [MKTMARKETINGPLANITEM].[NAME],
    @CODE = [MKTMARKETINGPLANITEM].[CODE],
    @CODEVALUEID = [MKTMARKETINGPLANITEM].[PARTDEFINITIONVALUESID],
    @SOURCECODEID = [MKTMARKETINGPLANITEM].[SOURCECODEID],
    @PLANPATH = dbo.[UFN_MKTMARKETINGPLANITEM_PATH]([MKTMARKETINGPLANITEM].[ID], 1),
    @MAILDATE = [MKTMARKETINGPLANITEM].[MAILDATE],
    @SITEID = [MKTMARKETINGPLAN].[SITEID],
    @BASECURRENCYID = [MKTMARKETINGPLANITEM].[BASECURRENCYID],
    @ITEMLIST = dbo.[UFN_MKTPLANSOURCECODEPART_GETITEMLIST_TOITEMLISTXML]([MKTMARKETINGPLANITEM].[ID])
  from dbo.[MKTMARKETINGPLANITEM]
  inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
  where dbo.[MKTMARKETINGPLANITEM].[ID] = @MARKETINGPLANITEMID;

  select
    @ENABLEHOUSEHOLDING = (case when sum(case when isnull([MKTRECORDSOURCEFIELDMAPPINGS].[HOUSEHOLDIDFIELD],'') = '' then 0 else 1 end) = 0 then 0 else 1 end)
  from dbo.[MKTRECORDSOURCE]
  left join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTRECORDSOURCE].[ID]
  where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

  --If householding is enabled, then default to only mail one person per household...

  set @HOUSEHOLDINGTYPECODE = (case when @ENABLEHOUSEHOLDING = 1 then 1 else 0 end);
  set @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @ENABLEHOUSEHOLDING;

  set @MAILINGTYPECODE = 0;

  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '44411772-B68C-4281-9C34-B5B936A0A2BC', 1);

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

  set @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDDEFAULTSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@CURRENTAPPUSERID);

  set @SMARTQUERIESEXIST = dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST](1);

  set @CANUPDATEEXCLUDEDECEASED = 1;
  set @CANUPDATEEXCLUDEINACTIVE = 1;

  if dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 0
    begin
      set @CANUPDATEEXCLUDEDECEASED = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '2EEC593D-06B1-49E0-9031-A4076B07081C');
      set @CANUPDATEEXCLUDEINACTIVE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'C0E02A6F-DF46-460C-ACA2-CC31C9C11BFC');
    end;

  select top 1 
    @ADDRESSPROCESSINGOPTIONID = [ID]
  from
    dbo.[ADDRESSPROCESSINGOPTION]
  where
    [ISDEFAULT] = 1 and
    dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

  select top 1
    @NAMEFORMATPARAMETERID = [ID]
  from
    dbo.[NAMEFORMATPARAMETER]
  where
    [ISDEFAULT] = 1 and
    dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

  -- Load KPI values

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

  insert into @KPITABLE
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETDEFAULTS] @MAILINGTYPECODE, @CURRENTAPPUSERID;

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

  --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 @EXPORTDEFINITIONRECORDTYPE = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEFROMMAILINGTYPECODE](@MAILINGTYPECODE);
  set @EXPORTDEFINITIONRECORDTYPEID = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEIDFROMMAILINGTYPECODE](@MAILINGTYPECODE);
  set @EXPORTDEFINITIONQUERYVIEWID = dbo.[UFN_MKTEXPORTDEFINITION_GETQUERYVIEWIDFROMMAILINGTYPECODE](@MAILINGTYPECODE);

  return 0;