USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORTPROCESS

The load procedure used by the view dataform template "Marketing Effort Export Process 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.
@MAILINGID	uniqueidentifier	INOUT	Effort ID
@MAILINGNAME	nvarchar(100)	INOUT	Marketing effort
@MAILINGDESCRIPTION	nvarchar(255)	INOUT	Marketing effort description
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@ACKNOWLEDGEDATE	datetime	INOUT	Acknowledged/receipted date
@MAILINGEXPORTPROCESSSTATUSID	uniqueidentifier	INOUT	Marketing effort export process status ID
@NETCOMMUNITYLINKESTABLISHED	bit	INOUT	Blackbaud Internet Solutions link established?
@HASPOSTALTEMPLATES	bit	INOUT	Has postal templates?
@EXPORTPROCESSDESCRIPTION	nvarchar(255)	INOUT	Description
@ACTIVE	bit	INOUT	Active
@NUMSEGMENTS	int	INOUT	Segments
@ONLYHASVENDORMANAGEDLISTSEGMENTS	bit	INOUT	Marketing effort only contains vendor managed list segments?
@ISCALCULATING	bit	INOUT	Marketing effort segment counts currently being calculated?
@ISACTIVATING	bit	INOUT	Marketing effort currently being activated?
@RECORDCOUNTCACHEISCURRENT	bit	INOUT	Record count cache is current
@NETCOMMUNITYEMAILJOBSTOSTART	bit	INOUT	Blackbaud Internet Solutions email jobs to start
@NETCOMMUNITYEMAILJOBSPENDING	bit	INOUT	Blackbaud Internet Solutions email jobs pending
@HASCOMMUNICATIONTEMPLATE	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORTPROCESS]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGID uniqueidentifier = null output,
  @MAILINGNAME nvarchar(100) = null output,
  @MAILINGDESCRIPTION nvarchar(255) = null output,
  @MAILINGTYPECODE tinyint = null output,
  @ACKNOWLEDGEDATE datetime = null output,
  @MAILINGEXPORTPROCESSSTATUSID uniqueidentifier = null output,
  @NETCOMMUNITYLINKESTABLISHED bit = null output,
  @HASPOSTALTEMPLATES bit = null output,
  @EXPORTPROCESSDESCRIPTION nvarchar(255) = null output,
  @ACTIVE bit = null output,
  @NUMSEGMENTS int = null output,
  @ONLYHASVENDORMANAGEDLISTSEGMENTS bit = null output,
  @ISCALCULATING bit = null output,
  @ISACTIVATING bit = null output,
  @RECORDCOUNTCACHEISCURRENT bit = null output,
  @NETCOMMUNITYEMAILJOBSTOSTART bit = null output,
  @NETCOMMUNITYEMAILJOBSPENDING bit = null output,
  @HASCOMMUNICATIONTEMPLATE bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @MAILINGID = [MKTSEGMENTATION].[ID],
    @MAILINGNAME = [MKTSEGMENTATION].[NAME],
    @MAILINGDESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @ACKNOWLEDGEDATE = (case when [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ID] is not null then
                         [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEDATE]
                        else
                         (select min([ACKNOWLEDGEDATE]) from dbo.[V_MKTREVENUELETTER] where [MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID])
                        end),
    @MAILINGEXPORTPROCESSSTATUSID = isnull((select top 1 [ID] from dbo.[MKTSEGMENTATIONEXPORTPROCESSSTATUS] where [MKTSEGMENTATIONEXPORTPROCESSSTATUS].[PARAMETERSETID] = @ID order by [DATEADDED] desc), '00000000-0000-0000-0000-000000000000'),
    @NETCOMMUNITYLINKESTABLISHED = dbo.[UFN_MKTNETCOMMUNITYINTEGRATION_LINKESTABLISHED](),
    @HASPOSTALTEMPLATES = convert(bit, case when exists (select top 1 1 from dbo.[MKTPOSTALTEMPLATE]) then 1 else 0 end),
    @EXPORTPROCESSDESCRIPTION = [MKTSEGMENTATIONEXPORTPROCESS].[DESCRIPTION],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @NUMSEGMENTS = (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]),
    @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] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
                                                          and [MKTSEGMENTLIST].[TYPECODE] = 1)
                                           and not exists(select 1
                                                          from dbo.[MKTSEGMENTATIONSEGMENT]
                                                          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                                                          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
                                                          and [MKTSEGMENT].[SEGMENTTYPECODE] <> 2)
                                         then 1 else 0 end),
    @ISCALCULATING = dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]),
    @ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]),
    @RECORDCOUNTCACHEISCURRENT = dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID], 0),
    @HASCOMMUNICATIONTEMPLATE = (case when [MKTCOMMUNICATIONTEMPLATE].[ID] is not null then 1 else 0 end)
  from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATIONEXPORTPROCESS].[ID] = @ID;

  if @DATALOADED = 1 and @MAILINGEXPORTPROCESSSTATUSID <> '00000000-0000-0000-0000-000000000000'
    exec dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETSUMMARYEMAILJOBSTATUS] @MAILINGEXPORTPROCESSSTATUSID, @NETCOMMUNITYEMAILJOBSTOSTART output, @NETCOMMUNITYEMAILJOBSPENDING output;

  return 0;