USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATION_EXPRESSION

The load procedure used by the view dataform template "Marketing Effort Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SEGMENTATIONID	uniqueidentifier	INOUT	SegmentationID
@PARENTID	uniqueidentifier	INOUT	ParentID
@PARENTNAME	nvarchar(100)	INOUT	Marketing effort Name
@PARENTACTIVE	bit	INOUT	Parent Active
@NAME	nvarchar(100)	INOUT	Name
@ACTIVE	bit	INOUT	Active
@NUMSEGMENTS	int	INOUT	Segments
@MARKETINGPLANITEMID	uniqueidentifier	INOUT	Marketing plan item ID
@GIFTSOURCESDEFINED	bit	INOUT	Gift sources defined
@SMARTQUERIESEXIST	bit	INOUT	Smart queries exist?
@GALILEOINSTALLED	bit	INOUT	Fundraising installed?
@UPDATEMAILINGCOUNTSPROCESSID	uniqueidentifier	INOUT	Update marketing effort counts process ID
@SEGMENTATIONEXPORTPROCESSID	uniqueidentifier	INOUT	Marketing effort export process ID
@SEGMENTATIONREFRESHPROCESSID	uniqueidentifier	INOUT	Marketing effort refresh process ID
@EVENTINSTANCEID	uniqueidentifier	INOUT	Event instance ID
@ONLYHASVENDORMANAGEDLISTSEGMENTS	bit	INOUT	Marketing effort only contains vendor managed list segments?
@HASVENDORMANAGEDLISTSEGMENTS	bit	INOUT	Marketing effort contains vendor managed list reservations?
@ISCALCULATING	bit	INOUT	Marketing effort segment counts currently being calculated?
@ISACTIVATING	bit	INOUT	Marketing effort currently being activated?
@SEGMENTATIONSEGMENTCALCULATEPROCESSID	uniqueidentifier	INOUT	Marketing effort segment record count process ID
@SEGMENTATIONACTIVATEPROCESSID	uniqueidentifier	INOUT	Marketing effort activate process ID
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing option ID
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format parameter ID
@ISBBEC	bit	INOUT	Is BBEC?
@HASTESTMAILINGS	bit	INOUT	Has test marketing efforts
@RECORDCOUNTCACHEISCURRENT	bit	INOUT	Record count cache is current
@SEGMENTATIONSEGMENTREFRESHPROCESSID	uniqueidentifier	INOUT	Effort segment refresh process ID
@SEGMENTATIONEXPORTPROCESSRUN	bit	INOUT	Marketing effort export process run?
@SEGMENTATIONEXCLUSIONSPROCESSID	uniqueidentifier	INOUT	Marketing effort exclusions process ID
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@WHITEMAILSEGMENTCOUNT	int	INOUT	White mail segment count
@HASPACKAGE	bit	INOUT	Has a package
@HASAPPEAL	bit	INOUT	Has an appeal
@HASASKLADDEROVERRIDES	bit	INOUT	Has an ask ladder override
@CREATEDFROMPLAN	bit	INOUT	Has an ask ladder override
@SEGMENTEDHOUSEFILEEFFORT	bit	INOUT
@ISHISTORICAL	bit	INOUT
@EFFORTEXCLUSIONSPROCESSRUN	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN
@HASFINDERFILE	bit	INOUT
@SHOWLOCKEDFIELDS	bit	INOUT
@HOUSEHOLDINGLOCKED	bit	INOUT
@INCLUDESELECTIONSLOCKED	bit	INOUT
@EXCLUDESELECTIONSLOCKED	bit	INOUT
@SOURCECODELOCKED	bit	INOUT
@HASSOURCECODEID	bit	INOUT
@ALLOWEXCLUDEPREVIOUSEFFORTS	bit	INOUT
@ALLCONTACTRULEEXCLUSIONSLOCKED	bit	INOUT
@ALLACTIVATIONOPTIONSLOCKED	bit	INOUT
@ALLEXPORTOPTIONSLOCKED	bit	INOUT
@ALLOWRESERVINGFINDERNUMBERS	bit	INOUT
@HASVENDORMANAGEDFINDERNUMBERRANGE	bit	INOUT
@ADDRESSPROCESSINGLOCKED	bit	INOUT
@OVERRIDEAPPEALBUSINESSUNITS	bit	INOUT
@BUSINESSUNITSLOCKED	bit	INOUT
@KPISLOCKED	bit	INOUT
@OVERRIDEASKLADDERS	bit	INOUT
@ASKLADDEROVERRIDESLOCKED	bit	INOUT
@SEEDSLOCKED	bit	INOUT
@INCLUDESEEDS	bit	INOUT
@ALLOWSPECIFYBUDGET	bit	INOUT
@APPEALLOCKED	bit	INOUT
@ALLSEGMENTATIONACTIVATIONOPTIONSLOCKED	bit	INOUT
@BASICINFORMATIONLOCKIMAGE	nvarchar(100)	INOUT
@UNIVERSELOCKIMAGE	nvarchar(100)	INOUT
@EXCLUSIONSLOCKIMAGE	nvarchar(100)	INOUT
@EFFORTSETTINGSLOCKIMAGE	nvarchar(100)	INOUT
@SOURCECODELOCKIMAGE	nvarchar(100)	INOUT
@EXPORTLOCKIMAGE	nvarchar(100)	INOUT
@ACTIVATIONLOCKIMAGE	nvarchar(100)	INOUT
@ADDRESSPROCESSINGLOCKIMAGE	nvarchar(100)	INOUT
@ASKLADDEROVERRIDESLOCKIMAGE	nvarchar(100)	INOUT
@SEEDSLOCKIMAGE	nvarchar(100)	INOUT
@BUSINESSUNITSLOCKIMAGE	nvarchar(100)	INOUT
@KPISLOCKIMAGE	nvarchar(100)	INOUT
@HASCOMMUNICATIONTEMPLATE	bit	INOUT
@ALLCALCULATIONOPTIONSLOCKED	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATION_EXPRESSION]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @PARENTID uniqueidentifier = null output,
  @PARENTNAME nvarchar(100) = null output,
  @PARENTACTIVE bit = null output,
  @NAME nvarchar(100) = null output,
  @ACTIVE bit = null output,
  @NUMSEGMENTS int = null output,
  @MARKETINGPLANITEMID uniqueidentifier = null output,
  @GIFTSOURCESDEFINED bit = null output,
  @SMARTQUERIESEXIST bit = null output,
  @GALILEOINSTALLED bit = null output,
  @UPDATEMAILINGCOUNTSPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONEXPORTPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONREFRESHPROCESSID uniqueidentifier = null output,
  @EVENTINSTANCEID uniqueidentifier = null output,
  @ONLYHASVENDORMANAGEDLISTSEGMENTS bit = null output,
  @HASVENDORMANAGEDLISTSEGMENTS bit = null output,
  @ISCALCULATING bit = null output,
  @ISACTIVATING bit = null output,
  @SEGMENTATIONSEGMENTCALCULATEPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier = null output,
  @MAILINGTYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @ISBBEC bit = null output,
  @HASTESTMAILINGS bit = null output,
  @RECORDCOUNTCACHEISCURRENT bit = null output,
  @SEGMENTATIONSEGMENTREFRESHPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONEXPORTPROCESSRUN bit = null output,
  @SEGMENTATIONEXCLUSIONSPROCESSID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @WHITEMAILSEGMENTCOUNT integer = null output,
  @HASPACKAGE bit = null output,
  @HASAPPEAL bit = null output,
  @HASASKLADDEROVERRIDES bit = null output,
  @CREATEDFROMPLAN bit = null output,
  @SEGMENTEDHOUSEFILEEFFORT bit = null output,
  @ISHISTORICAL bit = null output,
  @EFFORTEXCLUSIONSPROCESSRUN bit = null output,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @HASFINDERFILE bit = null output,
  @SHOWLOCKEDFIELDS bit = null output,
  @HOUSEHOLDINGLOCKED bit = null output,
  @INCLUDESELECTIONSLOCKED bit = null output,
  @EXCLUDESELECTIONSLOCKED bit = null output,
  @SOURCECODELOCKED bit = null output,
  @HASSOURCECODEID bit = null output,
  @ALLOWEXCLUDEPREVIOUSEFFORTS bit = null output,
  @ALLCONTACTRULEEXCLUSIONSLOCKED bit = null output,
  @ALLACTIVATIONOPTIONSLOCKED bit = null output,
  @ALLEXPORTOPTIONSLOCKED bit = null output,
  @ALLOWRESERVINGFINDERNUMBERS bit = null output,
  @HASVENDORMANAGEDFINDERNUMBERRANGE bit = null output,
  @ADDRESSPROCESSINGLOCKED bit = null output,
  @OVERRIDEAPPEALBUSINESSUNITS bit = null output,
  @BUSINESSUNITSLOCKED bit = null output,
  @KPISLOCKED bit = null output,
  @OVERRIDEASKLADDERS bit = null output,
  @ASKLADDEROVERRIDESLOCKED bit = null output,
  @SEEDSLOCKED bit = null output,
  @INCLUDESEEDS bit = null output,
  @ALLOWSPECIFYBUDGET bit = null output,
  @APPEALLOCKED bit = null output,
  @ALLSEGMENTATIONACTIVATIONOPTIONSLOCKED bit = null output,
  @BASICINFORMATIONLOCKIMAGE nvarchar(100) = null output,
  @UNIVERSELOCKIMAGE nvarchar(100) = null output,
  @EXCLUSIONSLOCKIMAGE nvarchar(100) = null output,
  @EFFORTSETTINGSLOCKIMAGE nvarchar(100) = null output,
  @SOURCECODELOCKIMAGE nvarchar(100) = null output,
  @EXPORTLOCKIMAGE nvarchar(100) = null output,
  @ACTIVATIONLOCKIMAGE nvarchar(100) = null output,
  @ADDRESSPROCESSINGLOCKIMAGE nvarchar(100) = null output,
  @ASKLADDEROVERRIDESLOCKIMAGE nvarchar(100) = null output,
  @SEEDSLOCKIMAGE nvarchar(100) = null output,
  @BUSINESSUNITSLOCKIMAGE nvarchar(100) = null output,
  @KPISLOCKIMAGE nvarchar(100) = null output,
  @HASCOMMUNICATIONTEMPLATE bit = null output,
  @ALLCALCULATIONOPTIONSLOCKED bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  declare @LOCKIMAGEKEY nvarchar(100) = 'CATALOG:Blackbaud.AppFx.Marketing.Catalog,Blackbaud.AppFx.Marketing.Catalog.padlock_locked_16.png';
  declare @EXPORTOPTIONLOCKED bit = 0;
  declare @ACTIVATIONOPTIONLOCKED bit = 0;
  declare @SEEDSOPTIONLOCKED bit = 0;
  declare @KPISOPTIONLOCKED bit = 0;

  select
    @SHOWLOCKEDFIELDS = isnull([MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SHOWLOCKEDFIELDS], 0)
  from
    dbo.[MKTSEGMENTATION]
    left join dbo.[MKTCOMMUNICATIONEFFORTUSERSETTINGS] on [MKTSEGMENTATION].[ID] = [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[SEGMENTATIONID] and [MKTCOMMUNICATIONEFFORTUSERSETTINGS].[APPUSERID] = @CURRENTAPPUSERID
  where
    [MKTSEGMENTATION].[ID] = @ID;

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

  select
    @DATALOADED = 1,
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @PARENTID = [PARENTEFFORT].[ID],
    @PARENTNAME = [PARENTEFFORT].[NAME],
    @PARENTACTIVE = [PARENTEFFORT].[ACTIVE],
    @NAME = [MKTSEGMENTATION].[NAME],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @NUMSEGMENTS = (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @MARKETINGPLANITEMID = [MKTSEGMENTATION].[MARKETINGPLANITEMID],
    @GIFTSOURCESDEFINED = (case when exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENT].[QUERYVIEWCATALOGID] not in ('DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0', '09A6D736-2ACB-42FF-87DB-F4F92A2025E1')) then 
                               dbo.[UFN_MKTSEGMENTATION_GETREQUIREDFIELDSSET]([MKTSEGMENTATION].[ID])
                             else
                               1
                             end),
    @SMARTQUERIESEXIST = dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST](1),
    @GALILEOINSTALLED = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end),
    @UPDATEMAILINGCOUNTSPROCESSID = (select [ID] from dbo.[MKTUPDATEMAILINGCOUNTSPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @SEGMENTATIONEXPORTPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @SEGMENTATIONREFRESHPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONREFRESHPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @SEGMENTATIONEXCLUSIONSPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONEXCLUSIONSPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @EVENTINSTANCEID = NewID(),
    @ONLYHASVENDORMANAGEDLISTSEGMENTS = (case when exists(select 1
                                                          from dbo.[MKTSEGMENTATIONSEGMENT]
                                                          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                                                          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                                                          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID
                                                          and [MKTSEGMENTLIST].[TYPECODE] = 1)  --vendor managed only

                                           and not exists(select 1
                                                          from dbo.[MKTSEGMENTATIONSEGMENT]
                                                          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                                                          left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                                                          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID
                                       and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0))  --all other segments including imported lists

                                         then 1 else 0 end),
    @HASVENDORMANAGEDLISTSEGMENTS = (case 
                                      when exists(select 1 from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MKTSEGMENTATIONFINDERNUMBER].[SEGMENTATIONID] = @ID and [MKTSEGMENTATIONFINDERNUMBER].[VENDORID] is not null)
                                        then 1 
                                      else 0 
                                     end),
    @ISCALCULATING = dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATION].[ID]),
    @ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]),
    @SEGMENTATIONSEGMENTREFRESHPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @SEGMENTATIONSEGMENTCALCULATEPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @SEGMENTATIONACTIVATEPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONACTIVATEPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @ADDRESSPROCESSINGOPTIONID = (case when [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1 then [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID] else null end),
    @NAMEFORMATPARAMETERID = (case when [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1 then [MKTSEGMENTATION].[NAMEFORMATPARAMETERID] else null end),
    @HASTESTMAILINGS = cast((case when exists(select top 1 1 from dbo.[MKTSEGMENTATION] where [PARENTSEGMENTATIONID] = @ID) then 1 else 0 end) as bit),
    @RECORDCOUNTCACHEISCURRENT = dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT](@ID, 0),
    @WHITEMAILSEGMENTCOUNT = (select count([ID]) from dbo.[MKTSEGMENTWHITEMAIL] where [SEGMENTATIONID] = @ID),
    @HASPACKAGE = (case when exists (select 1 from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
    @HASAPPEAL = (case when exists(select 1 from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
    @HASASKLADDEROVERRIDES = (case when (select dbo.[UFN_MKTSEGMENTATIONASKLADDEROVERRIDE_GETITEMLIST_TOITEMLISTXML](@ID)) is not null then 1 else 0 end),
    @CREATEDFROMPLAN = (case when [MKTSEGMENTATION].[MARKETINGPLANITEMID] is not null then 1 else 0 end),
    @SEGMENTEDHOUSEFILEEFFORT = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS]([MKTSEGMENTATION].[ID]),
    @ISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
    @HASFINDERFILE = (case when exists(select * from dbo.[MKTFINDERFILEIMPORTPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) then 1 else 0 end),
    @HOUSEHOLDINGLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGLOCKED], 0),
    @INCLUDESELECTIONSLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[INCLUDESELECTIONSLOCKED], 0),
    @EXCLUDESELECTIONSLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDESELECTIONSLOCKED], 0),
    @SOURCECODELOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODELOCKED], 0),
    @HASSOURCECODEID = case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SOURCECODEID] is not null then 1 else 0 end,
    @ALLOWEXCLUDEPREVIOUSEFFORTS = [MKTSEGMENTATION].[ALLOWEXCLUDEPREVIOUSEFFORTS],
    @ALLCONTACTRULEEXCLUSIONSLOCKED = 
      case when 
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASEDLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONSLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATELOCKED] = 1
      then 1 else 0 end,
    @ALLACTIVATIONOPTIONSLOCKED = 
      case when 
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED] = 1
      then 1 else 0 end,
    @ALLEXPORTOPTIONSLOCKED = 
      case when 
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DATEFORMATLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[FUZZYDATEFORMATLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MONTHDAYFORMATLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOURMINUTEFORMATLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CSVLINEBREAKCODELOCKED] = 1
      then 1 else 0 end,
    @ALLOWRESERVINGFINDERNUMBERS = [MKTSEGMENTATION].[ALLOWRESERVINGFINDERNUMBERS],
    @HASVENDORMANAGEDFINDERNUMBERRANGE =
      case when
        exists(select 1 from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [MKTSEGMENTATIONFINDERNUMBER].[SEGMENTATIONID] = @ID and [MKTSEGMENTATIONFINDERNUMBER].[VENDORID] is not null)
      then 1 else 0 end,
    @ADDRESSPROCESSINGLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ADDRESSPROCESSINGLOCKED], 0),
    @OVERRIDEAPPEALBUSINESSUNITS = isnull([MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS], 1),
    @BUSINESSUNITSLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[BUSINESSUNITSLOCKED], 0),
    @KPISLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[KPISLOCKED], 0),
    @OVERRIDEASKLADDERS = isnull([MKTCOMMUNICATIONTEMPLATE].[OVERRIDEASKLADDERS], 1),
    @ASKLADDEROVERRIDESLOCKED = isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ASKLADDEROVERRIDESLOCKED], 0),
    @SEEDSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[SEEDSLOCKED],
    @INCLUDESEEDS = isnull([MKTCOMMUNICATIONTEMPLATE].[INCLUDESEEDS], 1),
    @ALLOWSPECIFYBUDGET = [MKTSEGMENTATION].[ALLOWSPECIFYBUDGET],
    @APPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED],
    @ALLSEGMENTATIONACTIVATIONOPTIONSLOCKED = 
      case when 
        [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED] = 1
        and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED] = 1
      then 1 else 0 end,
    @BASICINFORMATIONLOCKIMAGE = 
      case when 
        (isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTIONLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGETLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERSLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED], 0) = 1)
        and @SHOWLOCKEDFIELDS = 1
      then @LOCKIMAGEKEY
      else '' end,
    @UNIVERSELOCKIMAGE =
      case when
        (isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOUSEHOLDINGLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[INCLUDESELECTIONSLOCKED], 0) = 1
        or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION]
                  where [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[FILTERTYPECODE] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
        )
        and @SHOWLOCKEDFIELDS = 1
      then @LOCKIMAGEKEY
      else '' end,
    @EXCLUSIONSLOCKIMAGE = 
      case when @ISBBEC = 1 then
        case when
          (isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASEDLOCKED], 0) = 1
          or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDESELECTIONSLOCKED], 0) = 1
          or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVELOCKED], 0) = 1
          or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATELOCKED], 0) = 1
          or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONSLOCKED], 0) = 1
          or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION]
                    where [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[FILTERTYPECODE] = 2 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
          or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES]
                    where [MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTSOLICITCODES].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
          )
          and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end
      else
        case when
          (isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDESELECTIONSLOCKED], 0) = 1
          or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION]
                    where [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[FILTERTYPECODE] = 2 and [MKTCOMMUNICATIONTEMPLATEDEFAULTFILTERSELECTION].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
          )          
          and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end
      end,
    @EXPORTOPTIONLOCKED = 
      case when
        isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYSYMBOLDISPLAYSETTINGCODELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALDIGITSDISPLAYSETTINGCODELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYGROUPSEPARATORDISPLAYSETTINGCODELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CURRENCYDECIMALSEPARATORDISPLAYSETTINGCODELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[DATEFORMATLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[FUZZYDATEFORMATLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[MONTHDAYFORMATLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[HOURMINUTEFORMATLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CSVLINEBREAKCODELOCKED], 0) = 1
      then 1 else 0 end,
    @ACTIVATIONOPTIONLOCKED = 
      case when 
        isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED], 0) = 1
        or isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED], 0) = 1
      then 1 else 0 end,
    @SEEDSOPTIONLOCKED = 
      case when 
        isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[SEEDSLOCKED], 0) = 1
        or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTSEED] where [MKTCOMMUNICATIONTEMPLATEDEFAULTSEED].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTSEED].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
      then 1 else 0 end,
    @KPISOPTIONLOCKED = 
      case when 
        isnull([MKTCOMMUNICATIONTEMPLATEDEFAULT].[KPISLOCKED], 0) = 1
        or exists(select 1 from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI] where [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED] = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID])
      then 1 else 0 end,
    @HASCOMMUNICATIONTEMPLATE = cast(
      case when
        [MKTCOMMUNICATIONTEMPLATE].[ID] is not null
      then 1 else 0 end as bit),
    @ALLCALCULATIONOPTIONSLOCKED = cast((case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED] = 1
          then 1 else 0 end) as bit)
  from
    dbo.[MKTSEGMENTATION]
      left join dbo.[MKTSEGMENTATION] as [PARENTEFFORT] on [MKTSEGMENTATION].[PARENTSEGMENTATIONID] = [PARENTEFFORT].[ID]
      left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
      left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
  where
    [MKTSEGMENTATION].[ID] = @ID;

  if @DATALOADED = 1
  begin

    if exists (select top 1 1 
               from dbo.[BUSINESSPROCESSSTATUS] 
               where [BUSINESSPROCESSCATALOGID] = 'C5ECEDD7-2510-4C02-BB7E-998928E3038C'
               and [BUSINESSPROCESSPARAMETERSETID] = @SEGMENTATIONEXPORTPROCESSID)
      set @SEGMENTATIONEXPORTPROCESSRUN = 1
    else
      set @SEGMENTATIONEXPORTPROCESSRUN = 0

    if exists (select top 1 1
               from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
               inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
               inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
               where 
                 [SEGMENTATIONID] = @ID and 
                 exists (select top 1 1 from dbo.[BUSINESSPROCESSOUTPUT] 
                         where [BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID] and [TABLEKEY] = 'EFFORTEXCLUSIONS'))
      set @EFFORTEXCLUSIONSPROCESSRUN = 1;
    else
      set @EFFORTEXCLUSIONSPROCESSRUN = 0;

    select
      @EFFORTSETTINGSLOCKIMAGE = 
        case when
          (@SOURCECODELOCKED = 1
          or @EXPORTOPTIONLOCKED = 1
          or @ACTIVATIONOPTIONLOCKED = 1
          or @ADDRESSPROCESSINGLOCKED = 1
          or @ASKLADDEROVERRIDESLOCKED = 1
          or @SEEDSOPTIONLOCKED = 1
          or @BUSINESSUNITSLOCKED = 1
          or @KPISOPTIONLOCKED = 1)
          and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @SOURCECODELOCKIMAGE =
        case when
          @SOURCECODELOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @EXPORTLOCKIMAGE =
        case when
          @EXPORTOPTIONLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @ACTIVATIONLOCKIMAGE =
        case when
          @ACTIVATIONOPTIONLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @ADDRESSPROCESSINGLOCKIMAGE =
        case when
          @ADDRESSPROCESSINGLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @ASKLADDEROVERRIDESLOCKIMAGE =
        case when
          @ASKLADDEROVERRIDESLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @SEEDSLOCKIMAGE =
        case when
          @SEEDSOPTIONLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @BUSINESSUNITSLOCKIMAGE =
        case when
          @BUSINESSUNITSLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end,
      @KPISLOCKIMAGE =
        case when
          @KPISOPTIONLOCKED = 1 and @SHOWLOCKEDFIELDS = 1
        then @LOCKIMAGEKEY
        else '' end;
  end

  return 0;