USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORT

The load procedure used by the view dataform template "Marketing Effort Export 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	NetCommunity 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

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONEXPORT]
(
  @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
)
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([MKTSEGMENTATIONEXPORTPROCESSSTATUS].[ID], '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)
  from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
  left join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONEXPORTPROCESSSTATUS] on [MKTSEGMENTATIONEXPORTPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONEXPORTPROCESS].[ID]
  where [MKTSEGMENTATIONEXPORTPROCESS].[ID] = @ID;

  return 0;