USP_DATAFORMTEMPLATE_PRELOAD_MKTCOMMUNICATIONTEMPLATE

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_PRELOAD_MKTCOMMUNICATIONTEMPLATE]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier = null output,
  @ORGANIZATIONCURRENCYID uniqueidentifier = null output,
  @APPEALINFORMATION xml = null output,
  @SITEID uniqueidentifier = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @EXCLUSIONS xml = null output,
  @CURRENCY nvarchar(110) = null output,
  @CURRENCYGROUPSEPARATOR nvarchar(4) = null output,
  @CURRENCYDECIMALSEPARATOR nvarchar(4) = null output,
  @CURRENCYDECIMALDIGITS int = null output,
  @DATEFORMAT nvarchar(50) = null output,
  @FUZZYDATEFORMAT nvarchar(50) = null output,
  @MONTHDAYFORMAT nvarchar(50) = null output,
  @HOURMINUTEFORMAT nvarchar(50) = null output,
  @CSVLINEBREAKCODE tinyint = null output,
  @SOURCECODEIDDEFAULT uniqueidentifier = null output,
  @ISBBEC bit = null output
)
as
  set nocount on;

  set @SITEID = dbo.[UFN_APPUSER_DEFAULTSITEFORUSER](@CURRENTAPPUSERID);
  set @EXPORTDEFINITIONRECORDTYPE = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEFROMMAILINGTYPECODE](0);
  set @EXPORTDEFINITIONRECORDTYPEID = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEIDFROMMAILINGTYPECODE](0);
  set @EXPORTDEFINITIONQUERYVIEWID = dbo.[UFN_MKTEXPORTDEFINITION_GETQUERYVIEWIDFROMMAILINGTYPECODE](0);
  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
  set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

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

  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;

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

  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;

  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;

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

  set @CURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION](@BASECURRENCYID);

  /* load export format options from the installationinfo table */
  select top 1
    @CURRENCYGROUPSEPARATOR = [BUSINESSPROCESSEXPORTFORMATCURRENCYGROUPSEPARATOR],
    @CURRENCYDECIMALSEPARATOR = [BUSINESSPROCESSEXPORTFORMATCURRENCYDECIMALSEPARATOR],
    @CURRENCYDECIMALDIGITS = [BUSINESSPROCESSEXPORTFORMATCURRENCYDECIMALDIGITS],
    @DATEFORMAT = [BUSINESSPROCESSEXPORTFORMATDATEFORMAT],
    @FUZZYDATEFORMAT = [BUSINESSPROCESSEXPORTFORMATFUZZYDATEFORMAT],
    @MONTHDAYFORMAT = [BUSINESSPROCESSEXPORTFORMATMONTHDAYFORMAT],
    @HOURMINUTEFORMAT = [BUSINESSPROCESSEXPORTFORMATHOURMINUTEFORMAT],
    @CSVLINEBREAKCODE = [CSVLINEBREAKCODE]
  from dbo.[INSTALLATIONINFO];

  set @SOURCECODEIDDEFAULT = dbo.[UFN_MKTSOURCECODE_GETDEFAULTSOURCECODEID2](@CURRENTAPPUSERID, 'C6B6011C-2D62-4A7C-95F3-9C0566287EE7', 1);

  return 0;