USP_DATAFORMTEMPLATE_VIEW_APPEALMAILING_EXPRESSION

Retrieves data used by the appeal mailing page.

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.
@APPEALID	uniqueidentifier	INOUT	Appeal ID
@APPEALNAME	nvarchar(100)	INOUT	Appeal name
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@MAILDATE	datetime	INOUT	Date
@ACTIVE	bit	INOUT	Active
@MKTSEGMENTATIONACTIVATEPROCESSID	uniqueidentifier	INOUT	Mailing activation process ID
@MKTSEGMENTATIONREFRESHPROCESSID	uniqueidentifier	INOUT	Mailing refresh process ID
@ISCALCULATING	bit	INOUT	Is calculating segment counts
@ISACTIVATING	bit	INOUT	Is activating
@SEGMENTATIONSEGMENTCALCULATEPROCESSID	uniqueidentifier	INOUT	Segmentation segment calculate process ID
@SEGMENTATIONEXPORTPROCESSID	uniqueidentifier	INOUT	Mailing export process ID
@ALERTSENABLED	bit	INOUT	ALERTSENABLED
@NUMSEGMENTS	int	INOUT	NUMSEGMENTS
@PACKAGEDEFINED	bit	INOUT	PACKAGEDEFINED
@DATEREFRESHED	datetime	INOUT	DATEREFRESHED
@HASLETTERS	bit	INOUT	HASLETTERS
@HASEXCLUSIONS	bit	INOUT	HASEXCLUSIONS
@ISHYBRID	bit	INOUT
@HASHISTORY	bit	INOUT
@HASARCHIVEDCONTENT	bit	INOUT
@ARCHIVEDCONTENTFILENAME	nvarchar(255)	INOUT
@ISREFRESHING	bit	INOUT
@LETTERCOUNT	int	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_APPEALMAILING_EXPRESSION]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @APPEALID uniqueidentifier = null output,
  @APPEALNAME nvarchar(100) = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @MAILDATE datetime = null output,
  @ACTIVE bit = null output,
  @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier = null output,
  @MKTSEGMENTATIONREFRESHPROCESSID uniqueidentifier = null output,
  @ISCALCULATING bit = null output,
  @ISACTIVATING bit = null output,
  @SEGMENTATIONSEGMENTCALCULATEPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONEXPORTPROCESSID uniqueidentifier = null output,
  @ALERTSENABLED bit = null output,
  @NUMSEGMENTS int = null output,
  @PACKAGEDEFINED bit = null output,
  @DATEREFRESHED datetime = null output,
  @HASLETTERS bit = null output,
  @HASEXCLUSIONS bit = null output,
  @ISHYBRID bit = null output,
  @HASHISTORY bit = null output,
  @HASARCHIVEDCONTENT bit = null output,
  @ARCHIVEDCONTENTFILENAME nvarchar(255) = null output,
  @ISREFRESHING bit = null output,
  @LETTERCOUNT int = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @APPEALID = [APPEAL].[ID],
    @APPEALNAME = [APPEAL].[NAME],
    @NAME = [MKTSEGMENTATION].[NAME],
    @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
    @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
    @MKTSEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID],
    @MKTSEGMENTATIONREFRESHPROCESSID = [MKTSEGMENTATIONREFRESHPROCESS].[ID],
    @SEGMENTATIONSEGMENTCALCULATEPROCESSID = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID],
    @ISCALCULATING = dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATION].[ID]),
    @ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]),
    @SEGMENTATIONEXPORTPROCESSID = [MKTSEGMENTATIONEXPORTPROCESS].[ID],
    @NUMSEGMENTS = (select count([MKTSEGMENTATIONSEGMENT].[ID]) from dbo.[MKTSEGMENTATIONSEGMENT] inner join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENTSELECTION].[SEGMENTID]  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @PACKAGEDEFINED = case when APPEALMAILINGSETUP.ID is null or APPEALMAILINGSETUP.MAILPACKAGEID is not null or APPEALMAILINGSETUP.EMAILPACKAGEID is not null then 1 else 0 end,
    @DATEREFRESHED = isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]),
    @HASLETTERS = case when exists(select id from dbo.COMMUNICATIONLETTER where SEGMENTATIONID = @ID) then 1 else 0 end,
    @HASEXCLUSIONS = case when exists(select [BUSINESSPROCESSOUTPUT].ID from dbo.[BUSINESSPROCESSOUTPUT] inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID] where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] and [BUSINESSPROCESSOUTPUT].[TABLEKEY] = 'EFFORTEXCLUSIONS') then 1 else 0 end,
    @ISHYBRID = convert(bit, case when APPEALMAILINGSETUP.ID is not null then 1 else 0 end),
    @HASHISTORY = case when exists(select ID from dbo.MKTSEGMENTATIONACTIVATEPROCESSSTATUS where PARAMETERSETID = MKTSEGMENTATIONACTIVATEPROCESS.ID) then 1 else 0 end,
    @HASARCHIVEDCONTENT = case when ARCHIVEDAPPEALMAILINGCONTENT.ID is not null then 1 else 0 end,
    @ARCHIVEDCONTENTFILENAME = coalesce(replace(ARCHIVEDAPPEALMAILINGCONTENT.DOCUMENTFILENAME, ' ', '_'), ''),
    @ISREFRESHING = dbo.UFN_MKTSEGMENTATION_ISREFRESHING([MKTSEGMENTATION].[ID]),
    @LETTERCOUNT = (select count(ID) from dbo.COMMUNICATIONLETTER where SEGMENTATIONID = @ID)
  from dbo.[APPEALMAILING]
  left outer join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
  left outer join dbo.[APPEAL] on [APPEAL].[ID] = [APPEALMAILING].[APPEALID]
  left outer join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.APPEALMAILINGSETUP on APPEALMAILINGSETUP.ID = APPEALMAILING.ID
  left outer join dbo.ARCHIVEDAPPEALMAILINGCONTENT on APPEALMAILINGSETUP.ID = ARCHIVEDAPPEALMAILINGCONTENT.ID
  where [MKTSEGMENTATION].[ID] = @ID
  and [APPEALMAILING].[ID] is not null;

  select 
    @ALERTSENABLED = [ENABLED]
  from dbo.[DATABASEMAILSETTINGS];

  return 0;