USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTCONTACTRULEEXCLUSIONS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@EXCLUDEDECEASED	bit	INOUT
@EXCLUDEDECEASEDLOCKED	bit	INOUT
@EXCLUDEINACTIVE	bit	INOUT
@EXCLUDEINACTIVELOCKED	bit	INOUT
@EXCLUSIONS	xml	INOUT
@EXCLUSIONSLOCKED	bit	INOUT
@EXCLUSIONDATETYPECODE	tinyint	INOUT
@EXCLUSIONASOFDATE	datetime	INOUT
@EXCLUSIONASOFDATELOCKED	bit	INOUT
@SHOWLOCKEDFIELDS	bit	INOUT
@HASCOMMUNICATIONTEMPLATE	bit	INOUT

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTCOMMUNICATIONEFFORTCONTACTRULEEXCLUSIONS]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @EXCLUDEDECEASED bit = null output,
  @EXCLUDEDECEASEDLOCKED bit = null output,
  @EXCLUDEINACTIVE bit = null output,
  @EXCLUDEINACTIVELOCKED bit = null output,
  @EXCLUSIONS xml = null output,
  @EXCLUSIONSLOCKED bit = null output,
  @EXCLUSIONDATETYPECODE tinyint = null output,
  @EXCLUSIONASOFDATE datetime = null output,
  @EXCLUSIONASOFDATELOCKED bit = null output,
  @SHOWLOCKEDFIELDS bit = null output,
  @HASCOMMUNICATIONTEMPLATE bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @HASCOMMUNICATIONTEMPLATE = 0;

  select
    @DATALOADED = 1,
    @EXCLUDEDECEASED = isnull([BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED], 0),
    @EXCLUDEINACTIVE = isnull([BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE], 0),
    @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_FROMTEMPLATE_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
    @EXCLUSIONDATETYPECODE = isnull([BUSINESSPROCESSCOMMPREF].[DATETYPECODE], 0),
    @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
    @SHOWLOCKEDFIELDS = isnull([MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SHOWLOCKEDFIELDS], 0)
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
  left join dbo.[MKTCOMMUNICATIONEFFORTUSERSETTINGS] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SEGMENTATIONID] and [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[APPUSERID] = @CURRENTAPPUSERID
  where [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
    begin

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

          select
            @EXCLUDEDECEASEDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASEDLOCKED],
            @EXCLUDEINACTIVELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVELOCKED],
            @EXCLUSIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONSLOCKED],
            @EXCLUSIONASOFDATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATELOCKED],
            @HASCOMMUNICATIONTEMPLATE = 1
          from dbo.[MKTCOMMUNICATIONTEMPLATE]
          inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
          where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID;

        end

    end

  return 0;