USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORT

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTCOMMUNICATIONEFFORT]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier = null,
  @EFFORTNAME nvarchar(100) = '',
  @EFFORTNAMESCHEMETEXT xml = null,
  @EFFORTDESCRIPTION nvarchar(255) = '',
  @APPEALINFORMATION xml = null,
  @EFFORTCHANNELCODE tinyint = 255,
  @EFFORTSITEID uniqueidentifier = null,
  @EFFORTDUEDATE datetime = null,
  @EFFORTLAUNCHDATE datetime = null,
  @EFFORTALLOWRESERVINGFINDERNUMBERS bit = 0,
  @EFFORTALLOWSPECIFYBUDGET bit = 0,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit = 0,
  @EFFORTBASECURRENCYID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MARKETINGPLANITEMID uniqueidentifier = null
)
as
  set nocount on;

  if @ID is null
    set @ID = newid();

  if @CHANGEAGENTID is null  
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();

  begin try

    --load template values


      declare @OVERRIDEAPPEALBUSINESSUNITS bit;
      declare @DESCRIPTIONLOCKED bit;
      declare @APPEALLOCKED bit;
      declare @CHANNELCODELOCKED bit;
      declare @ALLOWRESERVINGFINDERNUMBERSLOCKED bit;
      declare @ALLOWSPECIFYBUDGETLOCKED bit;
      declare @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED bit;
      declare @SOURCECODEIDDEFAULT uniqueidentifier;
      declare @SOURCECODELOCKED bit;  
      declare @EXPORTDEFINITIONRECORDTYPE nvarchar(50);
      declare @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier;
      declare @MAILEXPORTDEFINITIONID uniqueidentifier;
      declare @PHONEEXPORTDEFINITIONID uniqueidentifier;
      declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
      declare @EXPORTDESCRIPTION nvarchar(255);
      declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
      declare @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE tinyint;
      declare @CURRENCYDECIMALDIGITS int;
      declare @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE tinyint;
      declare @CURRENCYGROUPSEPARATOR nvarchar(4);
      declare @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE tinyint;
      declare @CURRENCYDECIMALSEPARATOR nvarchar(4);
      declare @ORGANIZATIONCURRENCYID uniqueidentifier;
      declare @DATEFORMAT nvarchar(50);
      declare @FUZZYDATEFORMAT nvarchar(50);
      declare @MONTHDAYFORMAT nvarchar(50);
      declare @HOURMINUTEFORMAT nvarchar(50);
      declare @CSVLINEBREAKCODE tinyint;
      declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit;
      declare @RUNMARKETINGEXCLUSIONSREPORT bit;
      declare @CACHESOURCEANALYSISRULEDATA bit;
      declare @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS bit;
      declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE bit;
      declare @RUNMARKETINGEXCLUSIONSREPORTACTIVATE bit;
      declare @CACHESOURCEANALYSISRULEDATAACTIVATE bit;
      declare @EXPORTAFTERACTIVATE bit;
      declare @USEADDRESSPROCESSING bit;
      declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
      declare @NAMEFORMATPARAMETERID uniqueidentifier;
      declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
      declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
      declare @ENABLEHOUSEHOLDING bit;
      declare @HOUSEHOLDINGTYPECODE tinyint;
      declare @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
      declare @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
      declare @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
      declare @DEFAULTKPIS xml;
      declare @INCLUDESELECTIONS xml;
      declare @EXCLUDESELECTIONS xml;
      declare @EXCLUDEDECEASED bit;
      declare @EXCLUDEINACTIVE bit;
      declare @EXCLUSIONASOFDATE datetime;
      declare @EXCLUSIONDATETYPECODE tinyint;
      declare @EXCLUSIONS xml;
      declare @ASKLADDEROVERRIDES xml;
      declare @SEEDS xml;
      declare @BUSINESSUNITS xml;
      declare @TEMPLATENAME nvarchar(100);
      declare @COMMUNICATIONNAMESCHEMEID uniqueidentifier;
      declare @NAMESCHEMENAME nvarchar(100);
      declare @NAMESCHEMEHASCOUNTER bit = 0;
      declare @COUNTERVALUE nvarchar(10);
      declare @COUNTER int = 0;
      declare @COUNTERFORMAT nvarchar(10);
      declare @TMPEFFORTNAME nvarchar(max) = '';
      declare @CODEVALUEID uniqueidentifier;
      declare @CODE nvarchar(10) = '';
      declare @PLANSOURCECODEID uniqueidentifier;
      declare @PLANCODE nvarchar(10);

      /* load values from plan */
      select top 1
        @PLANSOURCECODEID = [MKTMARKETINGPLANITEM].[SOURCECODEID],
        @PLANCODE = [MKTMARKETINGPLANITEM].[CODE]
      from dbo.[MKTMARKETINGPLANITEM]
      inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
      where dbo.[MKTMARKETINGPLANITEM].[ID] = @MARKETINGPLANITEMID;

      /* load existing communication template */
      exec dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTCOMMUNICATIONTEMPLATE]
        @ID = @EFFORTCOMMUNICATIONTEMPLATEID,
        @NAME = @TEMPLATENAME output,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @BASECURRENCYID = @EFFORTBASECURRENCYID output,
        @OVERRIDEAPPEALBUSINESSUNITS = @OVERRIDEAPPEALBUSINESSUNITS output,
        @COMMUNICATIONNAMESCHEMEID = @COMMUNICATIONNAMESCHEMEID output,
        @DESCRIPTIONLOCKED = @DESCRIPTIONLOCKED output,
        @APPEALLOCKED = @APPEALLOCKED output,
        @CHANNELCODELOCKED = @CHANNELCODELOCKED output,
        @ALLOWRESERVINGFINDERNUMBERSLOCKED = @ALLOWRESERVINGFINDERNUMBERSLOCKED output,
        @ALLOWSPECIFYBUDGETLOCKED = @ALLOWSPECIFYBUDGETLOCKED output,
        @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED = @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED output,
        @SOURCECODEIDDEFAULT = @SOURCECODEIDDEFAULT output,
        @SOURCECODELOCKED = @SOURCECODELOCKED output,  
        @EXPORTDEFINITIONRECORDTYPE = @EXPORTDEFINITIONRECORDTYPE output,
        @EXPORTDEFINITIONRECORDTYPEID = @EXPORTDEFINITIONRECORDTYPEID output,
        @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID output,
        @PHONEEXPORTDEFINITIONID = @PHONEEXPORTDEFINITIONID output,
        @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID output,
        @EXPORTDESCRIPTION = @EXPORTDESCRIPTION output,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = @CURRENCYSYMBOLDISPLAYSETTINGCODE output,
        @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE = @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE output,
        @CURRENCYDECIMALDIGITS = @CURRENCYDECIMALDIGITS output,
        @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE = @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE output,
        @CURRENCYGROUPSEPARATOR = @CURRENCYGROUPSEPARATOR output,
        @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE = @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE output,
        @CURRENCYDECIMALSEPARATOR = @CURRENCYDECIMALSEPARATOR output,
        @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID output,
        @DATEFORMAT = @DATEFORMAT output,
        @FUZZYDATEFORMAT = @FUZZYDATEFORMAT output,
        @MONTHDAYFORMAT = @MONTHDAYFORMAT output,
        @HOURMINUTEFORMAT = @HOURMINUTEFORMAT output,
        @CSVLINEBREAKCODE = @CSVLINEBREAKCODE output,
        @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS output,
        @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT output,
        @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA output,
        @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS = @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS output,
        @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE = @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE output,
        @RUNMARKETINGEXCLUSIONSREPORTACTIVATE = @RUNMARKETINGEXCLUSIONSREPORTACTIVATE output,
        @CACHESOURCEANALYSISRULEDATAACTIVATE = @CACHESOURCEANALYSISRULEDATAACTIVATE output,
        @EXPORTAFTERACTIVATE = @EXPORTAFTERACTIVATE output,
        @USEADDRESSPROCESSING = @USEADDRESSPROCESSING output,
        @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID output,
        @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID output,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE output,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE output,
        @ENABLEHOUSEHOLDING = @ENABLEHOUSEHOLDING output,
        @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE output,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS output,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD output,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD output,
        @DEFAULTKPIS = @DEFAULTKPIS output,
        @INCLUDESELECTIONS = @INCLUDESELECTIONS output,
        @EXCLUDESELECTIONS = @EXCLUDESELECTIONS output,
        @EXCLUDEDECEASED = @EXCLUDEDECEASED output,
        @EXCLUDEINACTIVE = @EXCLUDEINACTIVE output,
        @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE output,
        @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE output,
        @EXCLUSIONS = @EXCLUSIONS output,
        @ASKLADDEROVERRIDES = @ASKLADDEROVERRIDES output,
        @SEEDS = @SEEDS output,
        @BUSINESSUNITS = @BUSINESSUNITS output;

      --load default values for those that were locked and editable on the effort Add form

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

      select
        @SOURCECODEIDDEFAULT = (case when @SOURCECODELOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODEID] else case when @PLANSOURCECODEID is null then @SOURCECODEIDDEFAULT else @PLANSOURCECODEID end end),
        @EFFORTDESCRIPTION = (case when @DESCRIPTIONLOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTION] else @EFFORTDESCRIPTION end),
        @EFFORTCHANNELCODE = (case when @CHANNELCODELOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODE] else @EFFORTCHANNELCODE end),
        @EFFORTALLOWRESERVINGFINDERNUMBERS = (case when @ALLOWRESERVINGFINDERNUMBERSLOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERS] else @EFFORTALLOWRESERVINGFINDERNUMBERS end),
        @EFFORTALLOWSPECIFYBUDGET = (case when @ALLOWSPECIFYBUDGETLOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGET] else @EFFORTALLOWSPECIFYBUDGET end),
        @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = (case when @ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTS] else @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS end),
        @APPEALINFORMATION = (case when @APPEALLOCKED = 1 then (
            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] = @EFFORTCOMMUNICATIONTEMPLATEID
            where (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
            for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64)
            else @APPEALINFORMATION end)
      from
        dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT]
      where
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID;

      --convert attribute serializations to element based

      set @EXCLUDESELECTIONS = (
        select
          newid() as [ID],
          [SELECTIONID],
          [FILTERTYPECODE] 
        from dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION_GETEXCLUDESELECTIONS_FROMITEMLISTXML](@EXCLUDESELECTIONS)
        for xml path('ITEM'),type,elements,root('EXCLUDESELECTIONS'),BINARY BASE64);

      set @INCLUDESELECTIONS = (
        select 
          newid() as [ID],
          [SELECTIONID],
          [FILTERTYPECODE]
        from dbo.[UFN_MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION_GETINCLUDESELECTIONS_FROMITEMLISTXML](@INCLUDESELECTIONS) 
        for xml path('ITEM'),type,elements,root('INCLUDESELECTIONS'),BINARY BASE64);

      set @EXCLUSIONS = (
          select
            newid() as [ID],
            [SOLICITCODEID] 
          from
            (
              select
                T.c.value('(SOLICITCODEID)[1]', 'uniqueidentifier') as [SOLICITCODEID]
              from
                @EXCLUSIONS.nodes('/EXCLUSIONS/ITEM') T(c)
            ) [KPIS] 
          for xml path('ITEM'),type,elements,root('EXCLUSIONS'),BINARY BASE64
      );

    -- KPIs

      set @DEFAULTKPIS = 
        (
          select 
            [KPICATALOGID] 
          from
            (
              select
                T.c.value('(@KPICATALOGID)[1]', 'uniqueidentifier') as [KPICATALOGID]
              from
                @DEFAULTKPIS.nodes('/DEFAULTKPIS/ITEM') T(c)
            ) [KPIS] 
          for xml path('ITEM'),type,elements,root('ACTIVATIONKPIS'),BINARY BASE64
        );

    -- business units

      set @BUSINESSUNITS = 
        (
          select 
            [BUSINESSUNITCODEID],
            [PERCENTVALUE] 
          from 
            (
              select
                T.c.value('(@BUSINESSUNITCODEID)[1]', 'uniqueidentifier') as [BUSINESSUNITCODEID],
                T.c.value('(@PERCENTVALUE)[1]', 'decimal(20, 4)') as [PERCENTVALUE]
              from @BUSINESSUNITS.nodes('/BUSINESSUNITS/ITEM') T(c)
            ) [BUSINESSUNITS]
          for xml path('ITEM'),type,elements,root('BUSINESSUNITS'),BINARY BASE64
        );

      --place the namescheme data into a table variable

      if @COMMUNICATIONNAMESCHEMEID is not null
      begin
        --build the effort name to check if it is longer than 100

        set @TMPEFFORTNAME = dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](@EFFORTNAMESCHEMETEXT, NULL);

        if len(@TMPEFFORTNAME) > 100
          raiserror('ERR_COMMUNICATIONEFFORT_NAMESCHEMETEXTTOOLONG', 13, 1);
        else
          set @EFFORTNAME = @TMPEFFORTNAME;
      end

      --build the name based on name pattern or supplied text

      exec dbo.[USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME] 
        @COMMUNICATIONNAMESCHEMEID = @COMMUNICATIONNAMESCHEMEID,
        @EFFORTNAMESCHEMETEXT = @EFFORTNAMESCHEMETEXT,
        @EFFORTNAME = @EFFORTNAME output,
        @COUNTERVALUE = @COUNTERVALUE output,
        @COUNTER = @COUNTER output,
        @SEGMENTATIONID =  null;

      --update the name pattern counter if needed

      if @COMMUNICATIONNAMESCHEMEID is not null
        update
          dbo.[MKTCOMMUNICATIONNAMESCHEME]
        set
          [MAXCOUNTERVALUE] = @COUNTER
        where
          [ID] = @COMMUNICATIONNAMESCHEMEID;

      --check if the source code, if any, has an auto increment marketing effort part

      if @SOURCECODEIDDEFAULT is not null
      begin
        select
          @CODEVALUEID = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]
        from
          dbo.[MKTSOURCECODEITEM]
          inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 0
          inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
          inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
        where
          [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEIDDEFAULT;

        /* if this has a PLANCODE use it */
        if @PLANCODE is not null
          set @CODE = @PLANCODE;
        else
        begin
          /*Auto-Increment effort source code if auto-increment is enabled */
          if dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUE_GETAUTOINCREMENTSETTING](@CODEVALUEID) = 1
            set @CODE = isnull(dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE](@CODEVALUEID, 0), '');
        end
      end

      --some default templates have BBEC only features turned on.  If this is not BBEC, make sure those are cleared

      if @ISBBEC = 0
      begin
        set @USEADDRESSPROCESSING = 0;
        set @ADDRESSPROCESSINGOPTIONID = null;
        set @NAMEFORMATPARAMETERID = null;
        set @EXCLUSIONS = null;
        set @BUSINESSUNITS = null;
        set @OVERRIDEAPPEALBUSINESSUNITS = 0;
        set @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = 0;
      end
      -- if the Exclusion is set to 'TODAY' (for specific date the EXCLUSIONDATETYPECODE is 1) then we need to set EXCLUSIONASOFDATE to null

      if @EXCLUSIONDATETYPECODE = 0 
         set @EXCLUSIONASOFDATE = null;  

      --create the marketing effort

      exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATION]
          @ID = @ID output,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CODE = @CODE,
          @NAME = @EFFORTNAME,
          @DESCRIPTION = @EFFORTDESCRIPTION,
          @INCLUDESELECTIONS = @INCLUDESELECTIONS,
          @EXCLUDESELECTIONS = @EXCLUDESELECTIONS,
          @MARKETINGPLANITEMID = @MARKETINGPLANITEMID,
          @SOURCECODEID = @SOURCECODEIDDEFAULT,
          @MAILDATE = @EFFORTLAUNCHDATE,
          @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
          @SITEID = @EFFORTSITEID,
          @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
          @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
          @EXCLUDEDECEASED = @EXCLUDEDECEASED,
          @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
          @EXCLUSIONS = @EXCLUSIONS,
          @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
          @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
          @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
          @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
          @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
          @ACTIVATIONKPIS = @DEFAULTKPIS,
          @USEKPISASDEFAULT = 0,
          @APPEALINFORMATION = @APPEALINFORMATION,
          @RUNACTIVATEANDEXPORT = @EXPORTAFTERACTIVATE,
          @EXPORTDESCRIPTION = @EXPORTDESCRIPTION,
          @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
          @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
          @PHONEEXPORTDEFINITIONID = @PHONEEXPORTDEFINITIONID,
          @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
          @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
          @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
          @CODEVALUEID = @CODEVALUEID,
          @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS,
          @CACHESOURCEANALYSISRULEDATA = @CACHESOURCEANALYSISRULEDATA,
          @BUSINESSUNITS = @BUSINESSUNITS,
          @OVERRIDEBUSINESSUNITS = @OVERRIDEAPPEALBUSINESSUNITS,
          @BASECURRENCYID = @EFFORTBASECURRENCYID,
          @RUNMARKETINGEXCLUSIONSREPORT = @RUNMARKETINGEXCLUSIONSREPORT,
          @DUEDATE = @EFFORTDUEDATE,
          @CHANNELCODE = @EFFORTCHANNELCODE,
          @ALLOWRESERVINGFINDERNUMBERS = @EFFORTALLOWRESERVINGFINDERNUMBERS,
          @ALLOWSPECIFYBUDGET = @EFFORTALLOWSPECIFYBUDGET,
          @ALLOWEXCLUDEPREVIOUSEFFORTS = @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS;

      --save ask ladder overrides

      set @ASKLADDEROVERRIDES = (
        select 
          newid() as [ID], 
          [ASKLADDERID],
          [IDSETREGISTERID],
          [SEQUENCE] 
        from 
          (
            select
              T.c.value('(@ASKLADDERID)[1]', 'uniqueidentifier') as [ASKLADDERID],
              T.c.value('(@IDSETREGISTERID)[1]', 'uniqueidentifier') as [IDSETREGISTERID],
              T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE]
            from @ASKLADDEROVERRIDES.nodes('/ASKLADDEROVERRIDES/ITEM') T(c)  
          ) [ASKLADDEROVERRIDES]
          for xml path('ITEM'),type,elements,root('ITEMLIST'),BINARY BASE64);

      exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONASKLADDEROVERRIDES] @ID, @CHANGEAGENTID, @ASKLADDEROVERRIDES;

      --save seeds

      set @SEEDS = 
        (select 
          [ID],
          1 [SELECTED]
        from 
          (
            select
              T.c.value('(@SEEDID)[1]', 'uniqueidentifier') as [ID]
            from @SEEDS.nodes('/SEEDS/ITEM') T(c)
          ) [SEEDS]
        for xml path('ITEM'),type,elements,root('SEEDS'),BINARY BASE64);

      exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEED_2] @ID, @CHANGEAGENTID, @SEEDS;

      --save format options for currency/date

      update
        dbo.[BUSINESSPROCESSEXPORTFORMAT]
      set
        [CURRENCYDECIMALDIGITS] = @CURRENCYDECIMALDIGITS,
        [CURRENCYSYMBOLDISPLAYSETTINGCODE] = @CURRENCYSYMBOLDISPLAYSETTINGCODE,
        [CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE] = @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE,
        [CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE] = @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE,
        [CURRENCYGROUPSEPARATOR] = @CURRENCYGROUPSEPARATOR,
        [CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE] = @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE,
        [CURRENCYDECIMALSEPARATOR] = @CURRENCYDECIMALSEPARATOR,
        [DATEFORMAT] = @DATEFORMAT,
        [FUZZYDATEFORMAT] = @FUZZYDATEFORMAT,
        [MONTHDAYFORMAT] = @MONTHDAYFORMAT,
        [HOURMINUTEFORMAT] = @HOURMINUTEFORMAT,
        [CSVLINEBREAKCODE] = @CSVLINEBREAKCODE
      from
        dbo.[BUSINESSPROCESSEXPORTFORMAT]
        inner join [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT] on [BUSINESSPROCESSEXPORTFORMAT].[ID] = [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[ID]
        inner join [MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID]
      where
        [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = @ID;

      --copy template

      exec dbo.[USP_MKTCOMMUNICATIONTEMPLATE_COPY] @EFFORTCOMMUNICATIONTEMPLATEID output, @CURRENTAPPUSERID, @CHANGEAGENTID, @TEMPLATENAME, @ID;

      --copy name pattern

      if @COMMUNICATIONNAMESCHEMEID is not null
        begin
          select
            @NAMESCHEMENAME = [MKTCOMMUNICATIONNAMESCHEME].[NAME] 
          from
            dbo.[MKTCOMMUNICATIONNAMESCHEME]
          where 
            [MKTCOMMUNICATIONNAMESCHEME].[ID] = @COMMUNICATIONNAMESCHEMEID;

          exec dbo.[USP_MKTCOMMUNICATIONNAMESCHEME_COPY] @COMMUNICATIONNAMESCHEMEID output, @CURRENTAPPUSERID, @CHANGEAGENTID, @NAMESCHEMENAME, @ID;

          --update part values

          update
            dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
          set
            [MKTCOMMUNICATIONNAMESCHEMEPART].[COMMUNICATIONEFFORTVALUE] = case when [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8 then @COUNTERVALUE else [NSP].[FREEFORMPART] end
          from
            dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
            inner join 
            (select
              T.c.value('(@FREEFORMPART)[1]', 'nvarchar(100)') as [FREEFORMPART],
              T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE],
              @COMMUNICATIONNAMESCHEMEID [ID],
              T.c.value('(@NAMEPARTTYPECODE)[1]', 'tinyint') as [NAMEPARTTYPECODE]
            from @EFFORTNAMESCHEMETEXT.nodes('/EFFORTNAMESCHEMETEXT/ITEM') T(c)
            ) [NSP] on [NSP].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID]
          where [MKTCOMMUNICATIONNAMESCHEMEPART].[SEQUENCE] = [NSP].[SEQUENCE] and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] in (8, 10);

        end

      --save calculation options

      update dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
      set
        [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] = @RUNSEGMENTATIONSEGMENTREFRESHPROCESS,
        [CACHESOURCEANALYSISRULEDATA] = @CACHESOURCEANALYSISRULEDATA,
        [RUNMARKETINGEXCLUSIONSREPORT] = @RUNMARKETINGEXCLUSIONSREPORT,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate()
      from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
      where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @ID;

      --save activation options

      update dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
      set
        [RUNSEGMENTATIONSEGMENTCALCULATEPROCESS] = @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS,
        [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] = @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE,
        [CACHESOURCEANALYSISRULEDATA] = @CACHESOURCEANALYSISRULEDATAACTIVATE,
        [RUNMARKETINGEXCLUSIONSREPORT] = @RUNMARKETINGEXCLUSIONSREPORTACTIVATE,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate()
      from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
      where [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = @ID;

      --update the name on the plan item the mailings is from

      if @MARKETINGPLANITEMID is not null
        update dbo.[MKTMARKETINGPLANITEM] set 
          [NAME] = @EFFORTNAME,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @MARKETINGPLANITEMID;

      if @SOURCECODEIDDEFAULT is not null
      begin

        if @MARKETINGPLANITEMID is null
        begin
          --save user defined source code parts

            with [SOURCECODEPARTDEFINITIONVALUES_CTE] ([MKTSOURCECODEITEMID], [MKTSOURCECODEPARTDEFINITIONVALUESID]) as
            (
              select
              [MKTSOURCECODEITEMID],
              [MKTSOURCECODEPARTDEFINITIONVALUESID]
              from
              dbo.[MKTSOURCECODEVALIDPARTVALUES]
            )
            insert into dbo.[MKTSOURCECODEPART]
              ([SEGMENTATIONID], [SOURCECODEITEMID], [CODE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [PARTDEFINITIONVALUESID])
              select
              @ID,
              [MKTSOURCECODEITEM].[ID],                    
              case
                when dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUE_GETAUTOINCREMENTSETTING]([SOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]) = 1
                then isnull(dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE]([SOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID], 0), '')
                else ''
              end,                    
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE,
              [SOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
              from
              dbo.[MKTSOURCECODEITEM]
              inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
              outer apply (              
                select top 1 
                [MKTSOURCECODEPARTDEFINITIONVALUESID]
                from [SOURCECODEPARTDEFINITIONVALUES_CTE]
                where [SOURCECODEPARTDEFINITIONVALUES_CTE].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] 
              ) as [SOURCECODEPARTDEFINITIONVALUES]
              where
              ([MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEIDDEFAULT)
              and ([MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5);
            end
            else
          begin
            --save user defined source code parts

            with [SOURCECODEPARTDEFINITIONVALUES_CTE] ([MKTSOURCECODEITEMID], [MKTSOURCECODEPARTDEFINITIONVALUESID]) as
            (
              select
              [MKTSOURCECODEITEMID],
              [MKTSOURCECODEPARTDEFINITIONVALUESID]
              from
              dbo.[MKTSOURCECODEVALIDPARTVALUES]
            )
            insert into dbo.[MKTSOURCECODEPART]
              ([SEGMENTATIONID], [SOURCECODEITEMID], [CODE], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [PARTDEFINITIONVALUESID])
              select
              @ID,
              [MKTSOURCECODEITEM].[ID],                    
              isnull((select [CODE] from dbo.[MKTSOURCECODEPART] where [MARKETINGPLANITEMID] = @MARKETINGPLANITEMID and [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEPART].[SOURCECODEITEMID]), ''),                    
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE,
              @CURRENTDATE,
              [SOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
              from
              dbo.[MKTSOURCECODEITEM]
              inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
              outer apply (              
                select top 1 
                [MKTSOURCECODEPARTDEFINITIONVALUESID]
                from [SOURCECODEPARTDEFINITIONVALUES_CTE]
                inner join MKTSOURCECODEPARTDEFINITIONVALUES on MKTSOURCECODEPARTDEFINITIONVALUES.ID = SOURCECODEPARTDEFINITIONVALUES_CTE.[MKTSOURCECODEPARTDEFINITIONVALUESID]
                inner join MKTSOURCECODEPART on MKTSOURCECODEPART.PARTDEFINITIONVALUESID = MKTSOURCECODEPARTDEFINITIONVALUES.ID
                where [SOURCECODEPARTDEFINITIONVALUES_CTE].[MKTSOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID] and MKTSOURCECODEPART.MARKETINGPLANITEMID = @MARKETINGPLANITEMID
              ) as [SOURCECODEPARTDEFINITIONVALUES]
              where
              ([MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEIDDEFAULT)
              and ([MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5);
            end
        end

  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;