USP_DATAFORMTEMPLATE_ADD_APPEALMAILING_PRELOAD

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_APPEALMAILING_PRELOAD]
(
  @APPEALID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CONSTITUENTRECORDTYPEID uniqueidentifier = null output,
  @EXCLUSIONS xml = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @SITEID uniqueidentifier = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @ACTIVATIONKPIS xml = null output,
  @RECENCYEXCLUSIONDESIGNATIONS xml = null output,
  @RECENCYEXCLUSIONREVENUETYPECODES xml = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @PACKAGEADDDATAFORMCONTEXT nvarchar(128) = null output
)
as
  set nocount on;

  select
    @CONSTITUENTRECORDTYPEID = [ID]
  from dbo.[RECORDTYPE]
  where upper([NAME]) = 'CONSTITUENT';

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

  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
    @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 [MKTRECORDSOURCE].[ID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0';

  --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;

  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] 0, @CURRENTAPPUSERID;

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

  if @APPEALID is null or @APPEALID = '00000000-0000-0000-0000-000000000000'
    begin
      set @BASECURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);
      set @SITEID = null;
    end
  else
    select
      @BASECURRENCYID = [BASECURRENCYID],
      @SITEID = [SITEID]
    from dbo.[APPEAL]
    where [ID] = @APPEALID;

  set @PACKAGEADDDATAFORMCONTEXT = '0|' + convert(nvarchar(36), @BASECURRENCYID);

  return 0;