USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENTREFRESHPROCESS

The load procedure used by the edit dataform template "Marketing Effort Segment Record Count Calculation Edit 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	Marketing effort
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS	bit	INOUT	Refresh segment selections and filters
@CACHESOURCEANALYSISRULEDATA	bit	INOUT	Capture source analysis rule data
@SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN	datetime	INOUT	Segment selections and filters last refreshed
@SOURCEANALYSISRULEDATALASTCACHED	datetime	INOUT	Source analysis rule data last captured
@ISBBEC	bit	INOUT	Is BBEC?
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@RUNMARKETINGEXCLUSIONSREPORT	bit	INOUT
@MARKETINGEXCLUSIONSREPORTLASTRUN	datetime	INOUT
@ADVANCEDNAMEFORMATOPTIONUSED	bit	INOUT
@RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED	bit	INOUT
@CACHESOURCEANALYSISRULEDATALOCKED	bit	INOUT
@RUNMARKETINGEXCLUSIONSREPORTLOCKED	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENTREFRESHPROCESS]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit = null output,
  @CACHESOURCEANALYSISRULEDATA bit = null output,
  @SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN datetime = null output,
  @SOURCEANALYSISRULEDATALASTCACHED datetime = null output,
  @ISBBEC bit = null output,
  @TSLONG bigint = 0 output,
  @RUNMARKETINGEXCLUSIONSREPORT bit = null output,
  @MARKETINGEXCLUSIONSREPORTLASTRUN datetime = null output,
  @ADVANCEDNAMEFORMATOPTIONUSED bit = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED bit = null output,
  @CACHESOURCEANALYSISRULEDATALOCKED bit = null output,
  @RUNMARKETINGEXCLUSIONSREPORTLOCKED bit = null output
)
as
  set nocount on;

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

  select 
    @DATALOADED = 1,
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    @CACHESOURCEANALYSISRULEDATA = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[CACHESOURCEANALYSISRULEDATA],
    @SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN = [LASTRUN].[SEGMENTATIONSEGMENTREFRESHPROCESSLASTRUN],
    @SOURCEANALYSISRULEDATALASTCACHED = [LASTRUN].[CACHESOURCEANALYSISRULEDATALASTRUN],
    @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end),
    @RUNMARKETINGEXCLUSIONSREPORT = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[RUNMARKETINGEXCLUSIONSREPORT],
    @MARKETINGEXCLUSIONSREPORTLASTRUN = [LASTRUN].[MARKETINGEXCLUSIONSREPORTLASTRUN],
    @TSLONG = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[TSLONG]
  from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
  left join dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETLASTRUNDATE_BULK]() as [LASTRUN] on [LASTRUN].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID]
  where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = @ID;

  /* If using a communication template, load the lock settings from the template */
  if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID)
    begin

      select
        @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED],
        @CACHESOURCEANALYSISRULEDATALOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED],
        @RUNMARKETINGEXCLUSIONSREPORTLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED]
      from dbo.[MKTCOMMUNICATIONTEMPLATE]
      inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
      where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @SEGMENTATIONID;

    end

  if @DATALOADED = 1
    begin

      -- See if any segments use a name format containing an advanced spouse option.

      if exists (select top 1 1
                  from dbo.[MKTSEGMENTATIONSEGMENT]
                  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
                  left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETEREFFORT] on [NAMEFORMATPARAMETEREFFORT].[ID] = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID]
                  left join dbo.[NAMEFORMATPARAMETER] as [NAMEFORMATPARAMETERSEGMENT] on [NAMEFORMATPARAMETERSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[NAMEFORMATPARAMETERID]
                  where 
                    [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                    -- advanced name format on the segment level

                    and (([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 1 
                          and [MKTSEGMENTATIONSEGMENT].[USEADDRESSPROCESSING] = 1 
                          and ([NAMEFORMATPARAMETERSEGMENT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETERSEGMENT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)) 
                    -- advanced name format on the mailing level

                    or ([MKTSEGMENTATIONSEGMENT].[OVERRIDEADDRESSPROCESSING] = 0 
                        and [MKTSEGMENTATION].[USEADDRESSPROCESSING] = 1
                        and ([NAMEFORMATPARAMETEREFFORT].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETEREFFORT].[JOINTSPOUSESMAILEDSEPARATELY] = 1)))
                )

        set @ADVANCEDNAMEFORMATOPTIONUSED = 1;
      else
        set @ADVANCEDNAMEFORMATOPTIONUSED = 0;

    end

  return 0;