USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTEXPORTFORMAT

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@MAILEXPORTDEFINITIONID	uniqueidentifier	IN
@PHONEEXPORTDEFINITIONID	uniqueidentifier	IN
@EMAILEXPORTDEFINITIONID	uniqueidentifier	IN
@EXPORTDESCRIPTION	nvarchar(255)	IN
@CURRENCYSYMBOLDISPLAYSETTINGCODE	tinyint	IN
@CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE	tinyint	IN
@CURRENCYDECIMALDIGITS	int	IN
@CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE	tinyint	IN
@CURRENCYGROUPSEPARATOR	nvarchar(4)	IN
@CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE	tinyint	IN
@CURRENCYDECIMALSEPARATOR	nvarchar(4)	IN
@DATEFORMAT	nvarchar(50)	IN
@FUZZYDATEFORMAT	nvarchar(50)	IN
@MONTHDAYFORMAT	nvarchar(50)	IN
@HOURMINUTEFORMAT	nvarchar(50)	IN
@CSVLINEBREAKCODE	tinyint	IN
Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTEXPORTFORMAT]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @MAILEXPORTDEFINITIONID uniqueidentifier,
  @PHONEEXPORTDEFINITIONID uniqueidentifier,
  @EMAILEXPORTDEFINITIONID uniqueidentifier,
  @EXPORTDESCRIPTION nvarchar(255),
  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
  @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE tinyint,
  @CURRENCYDECIMALDIGITS int,
  @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE tinyint,
  @CURRENCYGROUPSEPARATOR nvarchar(4),
  @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE tinyint,
  @CURRENCYDECIMALSEPARATOR nvarchar(4),
  @DATEFORMAT nvarchar(50),
  @FUZZYDATEFORMAT nvarchar(50),
  @MONTHDAYFORMAT nvarchar(50),
  @HOURMINUTEFORMAT nvarchar(50),
  @CSVLINEBREAKCODE tinyint
)
as

  set nocount on;

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

  declare @CURRENTDATE datetime = getdate();
  declare @BUSINESSPROCESSEXPORTFORMATID uniqueidentifier;
  declare @MAILEXPORTDEFINITIONIDLOCKED bit = 0;
  declare @PHONEEXPORTDEFINITIONIDLOCKED bit = 0;
  declare @EMAILEXPORTDEFINITIONIDLOCKED bit = 0;
  declare @EXPORTDESCRIPTIONLOCKED bit = 0;
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED bit = 0;
  declare @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED bit = 0;
  declare @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED bit = 0;
  declare @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED bit = 0;
  declare @DATEFORMATLOCKED bit = 0;
  declare @FUZZYDATEFORMATLOCKED bit = 0;
  declare @MONTHDAYFORMATLOCKED bit = 0;
  declare @HOURMINUTEFORMATLOCKED bit = 0;
  declare @CSVLINEBREAKCODELOCKED bit = 0;

  begin try

    /* Get template locked settings */  
    if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @ID)
      begin

        select
          @MAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED],
          @PHONEEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED],
          @EMAILEXPORTDEFINITIONIDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED],
          @EXPORTDESCRIPTIONLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED],
          @CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED],
          @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED],
          @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED],
          @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED],
          @DATEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DATEFORMATLOCKED],
          @FUZZYDATEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[FUZZYDATEFORMATLOCKED],
          @MONTHDAYFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MONTHDAYFORMATLOCKED],
          @HOURMINUTEFORMATLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOURMINUTEFORMATLOCKED],
          @CSVLINEBREAKCODELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CSVLINEBREAKCODELOCKED]
        from dbo.[MKTCOMMUNICATIONTEMPLATE]
        inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
        where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID;

      end

    /* Get business process export format ID */
    select 
      @BUSINESSPROCESSEXPORTFORMATID = [BUSINESSPROCESSEXPORTFORMAT].[ID]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT] on [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID]
    inner join dbo.[BUSINESSPROCESSEXPORTFORMAT] on [BUSINESSPROCESSEXPORTFORMAT].[ID] = [MKTSEGMENTATIONEXPORTPROCESSEXPORTFORMAT].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;

    /* Update export settings */
    update [MKTSEGMENTATIONEXPORTPROCESS]
    set
      [MAILEXPORTDEFINITIONID] = case when @MAILEXPORTDEFINITIONIDLOCKED = 1 then [MAILEXPORTDEFINITIONID] else @MAILEXPORTDEFINITIONID end,
      [PHONEEXPORTDEFINITIONID] = case when @PHONEEXPORTDEFINITIONIDLOCKED = 1 then [PHONEEXPORTDEFINITIONID] else @PHONEEXPORTDEFINITIONID end,
      [EMAILEXPORTDEFINITIONID] = case when @EMAILEXPORTDEFINITIONIDLOCKED = 1 then [EMAILEXPORTDEFINITIONID] else @EMAILEXPORTDEFINITIONID end,
      [DESCRIPTION] = case when @EXPORTDESCRIPTIONLOCKED = 1 then [DESCRIPTION] else @EXPORTDESCRIPTION end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [SEGMENTATIONID] = @ID;

    /* Update currency/date settings */
    update [BUSINESSPROCESSEXPORTFORMAT]
    set
      [CURRENCYSYMBOLDISPLAYSETTINGCODE] = case when @CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYSYMBOLDISPLAYSETTINGCODE] else @CURRENCYSYMBOLDISPLAYSETTINGCODE end,
      [CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE] = case when @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE] else @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODE end,
      [CURRENCYDECIMALDIGITS] = case when @CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYDECIMALDIGITS] else @CURRENCYDECIMALDIGITS end,
      [CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE] = case when @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE] else @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODE end,
      [CURRENCYGROUPSEPARATOR] = case when @CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYGROUPSEPARATOR] else @CURRENCYGROUPSEPARATOR end,
      [CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE] = case when @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE] else @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODE end,
      [CURRENCYDECIMALSEPARATOR] = case when @CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED = 1 then [CURRENCYDECIMALSEPARATOR] else @CURRENCYDECIMALSEPARATOR end,
      [DATEFORMAT] = case when @DATEFORMATLOCKED = 1 then [DATEFORMAT] else @DATEFORMAT end,
      [FUZZYDATEFORMAT] = case when @FUZZYDATEFORMATLOCKED = 1 then [FUZZYDATEFORMAT] else @FUZZYDATEFORMAT end,
      [MONTHDAYFORMAT] = case when @MONTHDAYFORMATLOCKED = 1 then [MONTHDAYFORMAT] else @MONTHDAYFORMAT end,
      [HOURMINUTEFORMAT] = case when @HOURMINUTEFORMATLOCKED = 1 then [HOURMINUTEFORMAT] else @HOURMINUTEFORMAT end,
      [CSVLINEBREAKCODE] = case when @CSVLINEBREAKCODELOCKED = 1 then [CSVLINEBREAKCODE] else @CSVLINEBREAKCODE end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @BUSINESSPROCESSEXPORTFORMATID;    

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

return 0;