USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONTEMPLATE

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONTEMPLATE]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SITEID uniqueidentifier = null output, 
  @TEMPLATETYPECODE tinyint = null output,
  @OVERRIDEASKLADDERS bit = null output,
  @OVERRIDEAPPEALBUSINESSUNITS bit = null output,
  @INCLUDESEEDS bit = null output,
  @ISACTIVE bit = null output,
  @COMMUNICATIONNAMESCHEMEID uniqueidentifier = null output,
  @DESCRIPTIONDEFAULT nvarchar(255) = null output,
  @DESCRIPTIONLOCKED bit = null output,
  @APPEALINFORMATION xml = null output,
  @APPEALLOCKED bit = null output,
  @CHANNELCODEDEFAULT tinyint = null output,
  @CHANNELCODELOCKED bit = null output,
  @ALLOWRESERVINGFINDERNUMBERS bit = null output,
  @ALLOWRESERVINGFINDERNUMBERSLOCKED bit = null output,
  @ALLOWSPECIFYBUDGET bit = null output,
  @ALLOWSPECIFYBUDGETLOCKED bit = null output,
  @ALLOWEXCLUDEPREVIOUSEFFORTS bit = null output,
  @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED bit = null output,
  @SOURCECODEIDDEFAULT uniqueidentifier = null output,
  @SOURCECODELOCKED bit = null output,  
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output,
  @MAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @MAILEXPORTDEFINITIONIDLOCKED bit = null output,
  @PHONEEXPORTDEFINITIONID uniqueidentifier = null output,
  @PHONEEXPORTDEFINITIONIDLOCKED bit = null output,
  @EMAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @EMAILEXPORTDEFINITIONIDLOCKED bit = null output,
  @EXPORTDESCRIPTION nvarchar(255) = null output,
  @EXPORTDESCRIPTIONLOCKED bit = null output,
  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = null output,
  @CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED bit = null output,
  @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE tinyint = null output,
  @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED bit = null output,
  @CURRENCYDECIMALDIGITS int = null output,
  @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE tinyint = null output,
  @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED bit = null output,
  @CURRENCYGROUPSEPARATOR nvarchar(4) = null output,
  @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE tinyint = null output,
  @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED bit = null output,
  @CURRENCYDECIMALSEPARATOR nvarchar(4) = null output,
  @ORGANIZATIONCURRENCYID uniqueidentifier = null output,
  @DATEFORMAT nvarchar(50) = null output,
  @DATEFORMATLOCKED bit = null output,
  @FUZZYDATEFORMAT nvarchar(50) = null output,
  @FUZZYDATEFORMATLOCKED bit = null output,
  @MONTHDAYFORMAT nvarchar(50) = null output,
  @MONTHDAYFORMATLOCKED bit = null output,
  @HOURMINUTEFORMAT nvarchar(50) = null output,
  @HOURMINUTEFORMATLOCKED bit = null output,
  @CSVLINEBREAKCODE tinyint = null output,
  @CSVLINEBREAKCODELOCKED bit = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED bit = null output,
  @RUNMARKETINGEXCLUSIONSREPORT bit = null output,
  @RUNMARKETINGEXCLUSIONSREPORTLOCKED bit = null output,
  @CACHESOURCEANALYSISRULEDATA bit = null output,
  @CACHESOURCEANALYSISRULEDATALOCKED bit = null output,
  @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS bit = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE bit = null output,
  @RUNMARKETINGEXCLUSIONSREPORTACTIVATE bit = null output,
  @CACHESOURCEANALYSISRULEDATAACTIVATE bit = null output,
  @ACTIVATEOPTIONSLOCKED bit = null output,
  @EXPORTAFTERACTIVATE bit = null output,
  @EXPORTAFTERACTIVATELOCKED bit = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @ADDRESSPROCESSINGLOCKED bit = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = null output,
  @HOUSEHOLDINGLOCKED bit = null output,
  @DEFAULTKPIS xml = null output,
  @KPISLOCKED bit = null output,
  @INCLUDESELECTIONS xml = null output,
  @INCLUDESELECTIONSLOCKED bit = null output,
  @EXCLUDESELECTIONS xml = null output,
  @EXCLUDESELECTIONSLOCKED bit = null output,
  @EXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @EXCLUDEDECEASEDLOCKED bit = null output,
  @EXCLUDEINACTIVE bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @EXCLUDEINACTIVELOCKED bit = null output,
  @EXCLUSIONASOFDATE datetime = null output,
  @EXCLUSIONDATETYPECODE tinyint = null output,
  @EXCLUSIONASOFDATELOCKED bit = null output,
  @EXCLUSIONS xml = null output,
  @EXCLUSIONSLOCKED bit = null output,
  @ASKLADDEROVERRIDES xml = null output,
  @ASKLADDEROVERRIDESLOCKED bit = null output,
  @SEEDSLOCKED bit = null output,
  @SEEDS xml = null output,
  @BUSINESSUNITSLOCKED bit = null output,
  @BUSINESSUNITS xml = null output,
  @CURRENCY nvarchar(110) = null output,
  @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED bit = null output,
  @ALLOWEFFORTBUSINESSUNITSOVERRIDE bit = null output,
  @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE bit = null output,
  @ISBBEC bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @TSLONG = [MKTCOMMUNICATIONTEMPLATE].[TSLONG],
    @BASECURRENCYID = [MKTCOMMUNICATIONTEMPLATE].[BASECURRENCYID],
    @NAME = [MKTCOMMUNICATIONTEMPLATE].[NAME],
    @DESCRIPTION = [MKTCOMMUNICATIONTEMPLATE].[DESCRIPTION],
    @SITEID = [MKTCOMMUNICATIONTEMPLATE].[SITEID],
    @TEMPLATETYPECODE = [MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPECODE],
    @OVERRIDEASKLADDERS = [MKTCOMMUNICATIONTEMPLATE].[OVERRIDEASKLADDERS],
    @OVERRIDEAPPEALBUSINESSUNITS = [MKTCOMMUNICATIONTEMPLATE].[OVERRIDEAPPEALBUSINESSUNITS],
    @ALLOWEFFORTBUSINESSUNITSOVERRIDE = [MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTBUSINESSUNITSOVERRIDE],
    @INCLUDESEEDS = [MKTCOMMUNICATIONTEMPLATE].[INCLUDESEEDS],
    @ISACTIVE = [MKTCOMMUNICATIONTEMPLATE].[ISACTIVE],
    @COMMUNICATIONNAMESCHEMEID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONNAMESCHEMEID],
    @DESCRIPTIONDEFAULT = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTION],
    @DESCRIPTIONLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTIONLOCKED],
    @APPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED],
    @CHANNELCODEDEFAULT = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODE],
    @CHANNELCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODELOCKED],
    @ALLOWRESERVINGFINDERNUMBERS = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERS],
    @ALLOWRESERVINGFINDERNUMBERSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERSLOCKED],
    @ALLOWSPECIFYBUDGET = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGET],
    @ALLOWSPECIFYBUDGETLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGETLOCKED],
    @ALLOWEXCLUDEPREVIOUSEFFORTS = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTS],
    @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED],
    @SOURCECODEIDDEFAULT = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODEID],
    @SOURCECODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODELOCKED],
    @MAILEXPORTDEFINITIONID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONID],
    @MAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED],
    @PHONEEXPORTDEFINITIONID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONID],
    @PHONEEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED],
    @EMAILEXPORTDEFINITIONID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONID],
    @EMAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED],
    @EXPORTDESCRIPTION = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTION],
    @EXPORTDESCRIPTIONLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYSYMBOLDISPLAYSETTINGCODE],
    @CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED],
    @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE],
    @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED],
    @CURRENCYDECIMALDIGITS = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYDECIMALDIGITS],
    @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE],
    @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED],
    @CURRENCYGROUPSEPARATOR = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYGROUPSEPARATOR],
    @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE],
    @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED],
    @CURRENCYDECIMALSEPARATOR = [BUSINESSPROCESSEXPORTFORMAT].[CURRENCYDECIMALSEPARATOR],
    @DATEFORMAT = [BUSINESSPROCESSEXPORTFORMAT].[DATEFORMAT],
    @DATEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DATEFORMATLOCKED],
    @FUZZYDATEFORMAT = [BUSINESSPROCESSEXPORTFORMAT].[FUZZYDATEFORMAT],
    @FUZZYDATEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[FUZZYDATEFORMATLOCKED],
    @MONTHDAYFORMAT = [BUSINESSPROCESSEXPORTFORMAT].[MONTHDAYFORMAT],
    @MONTHDAYFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MONTHDAYFORMATLOCKED],
    @HOURMINUTEFORMAT = [BUSINESSPROCESSEXPORTFORMAT].[HOURMINUTEFORMAT],
    @HOURMINUTEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOURMINUTEFORMATLOCKED],
    @CSVLINEBREAKCODE = [BUSINESSPROCESSEXPORTFORMAT].[CSVLINEBREAKCODE],
    @CSVLINEBREAKCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CSVLINEBREAKCODELOCKED],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED],
    @RUNMARKETINGEXCLUSIONSREPORT = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORT],
    @RUNMARKETINGEXCLUSIONSREPORTLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED],
    @CACHESOURCEANALYSISRULEDATA = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATA],
    @CACHESOURCEANALYSISRULEDATALOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED],    
    @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTCALCULATEPROCESS],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE],
    @RUNMARKETINGEXCLUSIONSREPORTACTIVATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTACTIVATE],
    @CACHESOURCEANALYSISRULEDATAACTIVATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATE],
    @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED],
    @ACTIVATEOPTIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED],
    @EXPORTAFTERACTIVATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATE],
    @EXPORTAFTERACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED],    
    @USEADDRESSPROCESSING = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTIONID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGOPTIONID],
    @NAMEFORMATPARAMETERID = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[NAMEFORMATPARAMETERID],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    @ADDRESSPROCESSINGLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGLOCKED],
    @ALLOWEFFORTADDRESSPROCESSINGOVERRIDE = [MKTCOMMUNICATIONTEMPLATE].[ALLOWEFFORTADDRESSPROCESSINGOVERRIDE],
    @KPISLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[KPISLOCKED],
    @INCLUDESELECTIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[INCLUDESELECTIONSLOCKED],
    @HOUSEHOLDINGTYPECODE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGTYPECODE],
    @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
    @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
    @HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
    @HOUSEHOLDINGLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGLOCKED],
    @EXCLUDESELECTIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDESELECTIONSLOCKED],
    @EXCLUDEDECEASED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASED],
    @EXCLUDEDECEASEDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASEDLOCKED],
    @EXCLUDEINACTIVE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVE],
    @EXCLUDEINACTIVELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVELOCKED],
    @EXCLUSIONASOFDATE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATE],
    @EXCLUSIONDATETYPECODE = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONDATETYPECODE],
    @EXCLUSIONASOFDATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATELOCKED],
    @EXCLUSIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONSLOCKED],
    @ASKLADDEROVERRIDESLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ASKLADDEROVERRIDESLOCKED],
    @SEEDSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SEEDSLOCKED],
    @BUSINESSUNITSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[BUSINESSUNITSLOCKED]
  from dbo.[MKTCOMMUNICATIONTEMPLATE]
  inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] on [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
  left join dbo.[BUSINESSPROCESSEXPORTFORMAT] on [BUSINESSPROCESSEXPORTFORMAT].[ID] = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[BUSINESSPROCESSEXPORTFORMATID]
  where [MKTCOMMUNICATIONTEMPLATE].[ID] = @ID;

  if @DATALOADED = 1
    begin

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

    -- get appeal search catalog IDs with their record source names

    set @APPEALINFORMATION = (
      select 
        [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
        [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
        [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
        [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD],
        [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as [APPEALSYSTEMID],
        (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
          (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
          else
          [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALID]
          end) as [APPEALID],
        (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
          (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
          else
          [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALDESCRIPTION]
          end) as [APPEALDESCRIPTION]
      from dbo.[MKTAPPEALRECORDSOURCE]
      inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
      left join [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] on [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] 
        and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = @ID
      where (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
      for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

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

    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;

      declare @KPITABLE as table (
        [KPICATALOGID] uniqueidentifier,
        [NAME] nvarchar(255),
        [LOCKED] bit
      );

      insert into @KPITABLE
        exec dbo.[USP_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_GETFIELDS] @ID, @CURRENTAPPUSERID;

      /* select KPI items into xml attributes instead of elements */
      set @DEFAULTKPIS = (
        select 
          [KPICATALOGID],
          [LOCKED],
          @TEMPLATETYPECODE as [TEMPLATETYPECODE]
        from @KPITABLE 
        for xml raw('ITEM'), root('DEFAULTKPIS'), binary base64);

      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 @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 @INCLUDESELECTIONS = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION_GETINCLUDESELECTIONS_TOITEMLISTXML](@ID);
      set @EXCLUDESELECTIONS = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION_GETEXCLUDESELECTIONS_TOITEMLISTXML](@ID);
      set @EXCLUSIONS = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES_GETEXCLUDESOLICITCODES_TOITEMLISTXML](@ID);

      set @ASKLADDEROVERRIDES = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTASKLADDEROVERRIDE_GETITEMLIST_TOITEMLISTXML](@ID);

      set @SEEDS = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTSEED_GETITEMLIST_TOITEMLISTXML](@ID);

      set @BUSINESSUNITS = dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@ID);

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

    end

  return 0;