USP_APPEALMAILING_ADD_PRELOAD

CREATE procedure dbo.[USP_APPEALMAILING_ADD_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
)
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 dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([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;

  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;

  select 
    @SITEID = [SITEID]
  from 
    dbo.[APPEAL]
  where
    [ID] = @APPEALID;

  -- 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_GETFIELDS null, 0;

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

  return 0;