USP_DATAFORMTEMPLATE_VIEW_APPEALMAILING

Returns appeal mailing information.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@DESCRIPTION	nvarchar(255)	INOUT	Description
@MAILDATE	datetime	INOUT	Mail date
@FIXEDCOST	money	INOUT	Fixed cost
@PACKAGENAME	nvarchar(100)	INOUT	Package
@SELECTIONNAME	nvarchar(300)	INOUT	Selections
@SELECTIONRECORDCOUNT	nvarchar(100)	INOUT	Records
@TOTALCOST	nvarchar(100)	INOUT	Total cost
@ACTIVE	bit	INOUT	Active
@DATEREFRESHED	datetime	INOUT	Current as of
@SEGMENTATIONMODELNAME	nvarchar(100)	INOUT	Segmentation Model Name
@NUMPACKAGES	int	INOUT	Packages
@NUMSEGMENTS	int	INOUT	Segments
@STATUS	nvarchar(13)	INOUT	Status
@MAILINGBUDGET	money	INOUT	Budget
@ISHYBRID	bit	INOUT	ISHYBRID
@SELECTIONS	xml	INOUT	Lists
@CHANNELCODE	tinyint	INOUT	CHANNELCODE
@EMAILPACKAGE	nvarchar(150)	INOUT	Email package
@MAILPACKAGE	nvarchar(150)	INOUT	Mail package
@MKTASKLADDER	nvarchar(100)	INOUT	Ask ladder
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@CURRENCY	nvarchar(110)	INOUT	Currency
@ISOCURRENCYCODE	nvarchar(3)	INOUT	ISO currency code
@DECIMALDIGITS	tinyint	INOUT	Decimal digits
@CURRENCYSYMBOL	nvarchar(5)	INOUT	Currency symbol
@CURRENCYSYMBOLDISPLAYSETTINGCODE	tinyint	INOUT	Currency symbol display setting code
@RECORDCOUNTNEEDSUPDATED	bit	INOUT
@HASEMAIL	bit	INOUT
@UPDATECOUNTPARAMETERSETID	uniqueidentifier	INOUT
@HASLETTERSELECTIONS	bit	INOUT
@HASINCOMPLETEEMAILLETTER	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_APPEALMAILING]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @DESCRIPTION nvarchar(255) = null output,
  @MAILDATE datetime = null output,
  @FIXEDCOST money = null output,
  @PACKAGENAME nvarchar(100) = null output,
  @SELECTIONNAME nvarchar(300) = null output,
  @SELECTIONRECORDCOUNT nvarchar(100) = null output,
  @TOTALCOST nvarchar(100) = null output,
  @ACTIVE bit = null output,
  @DATEREFRESHED datetime = null output,
  @SEGMENTATIONMODELNAME nvarchar(100) = null output,
  @NUMPACKAGES integer = null output,
  @NUMSEGMENTS integer = null output,
  @STATUS nvarchar(13) = null output,
  @MAILINGBUDGET money = null output,
  @ISHYBRID bit = null output,
  @SELECTIONS xml = null output,
  @CHANNELCODE tinyint = null output,
  @EMAILPACKAGE nvarchar(150) = null output,
  @MAILPACKAGE nvarchar(150) = null output,
  @MKTASKLADDER nvarchar (100) = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CURRENCY nvarchar(110) = null output,
  @ISOCURRENCYCODE nvarchar(3) = null output,
  @DECIMALDIGITS tinyint = null output,
  @CURRENCYSYMBOL nvarchar(5) = null output,
  @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = null output,
  @RECORDCOUNTNEEDSUPDATED bit = null output,
  @HASEMAIL bit = null output,
  @UPDATECOUNTPARAMETERSETID uniqueidentifier = null output,
  @HASLETTERSELECTIONS bit = null output,
  @HASINCOMPLETEEMAILLETTER bit = null output
)
as
  set nocount on;

  declare @PACKAGEUNITCOST money;
  declare @MKTSEGMENTATIONSEGMENTID uniqueidentifier;

  set @DATALOADED = 0;
  set @RECORDCOUNTNEEDSUPDATED = 0;

  declare @MKTSEGMENTATIONSUMMARYINFO table
  (
    [NAME] nvarchar(100),
    [DESCRIPTION] nvarchar(255),
    [CODE] nvarchar(10),
    [IDINTEGER] int,
    [ACTIVE] bit,
    [ACTIVATEDATE] datetime,
    [APPEAL] nvarchar(100),
    [DATEREFRESHED] datetime,
    [NUMSEGMENTS] int,
    [NUMPACKAGES] int,
    [BUDGETAMOUNT] money,
    [FIXEDCOST] money,
    [MARKETINGPLANITEMID] uniqueidentifier,
    [GIFTSOURCESDEFINED] bit,
    [NUMSEEDS] int,
    [NUMTESTMAILINGS] int,
    [MAILDATE] datetime,
    [SITE] nvarchar(1024),
    [MAILINGTYPECODE] tinyint,
    [MAILINGFIXEDCOST] money,
    [BBECAPPEALID] uniqueidentifier,
    [BBECAPPEALDESCRIPTION] nvarchar(100),
    [BASECURRENCYID] uniqueidentifier,
    [CURRENCY] nvarchar(110),
    [ISHISTORICAL] bit,
    [DATEADDED] datetime,
    [LASTCALCULATEDATE] datetime,
    [LASTEXPORTDATE] datetime,
    [REMOVEMEMBERSDATE] datetime,
    [CALCULATEPROCESSID] uniqueidentifier,
    [EXPORTPROCESSID] uniqueidentifier,
    [ACTIVATEPROCESSID] uniqueidentifier,
    [REFRESHPROCESSID] uniqueidentifier,
    [REMOVEMEMBERSPROCESSID] uniqueidentifier,
    [DUEDATE] datetime,
    [CHANNEL] nvarchar(13),
    [TEMPLATENAME] nvarchar(100),
    [OWNER] nvarchar(100),
    [COLLAPSESUMMARYSECTION] bit,
    [SHOWLOCKEDFIELDS] bit,
    [CALCULATESTATUSCODE] tinyint,
    [EXPORTSTATUSCODE] tinyint,
    [ACTIVATESTATUSCODE] tinyint,
    [REFRESHSTATUSCODE] tinyint,
    [REMOVEMEMBERSSTATUSCODE] tinyint,
    [INCLUDESEEDS] bit,
    [ISBBEC] bit
  );

  insert into @MKTSEGMENTATIONSUMMARYINFO
    exec dbo.[USP_MKTSEGMENTATION_GETSUMMARYINFO] @ID, @CURRENTAPPUSERID;

  select
    @DATALOADED = 1,
    @DESCRIPTION = [SUMMARYINFO].[DESCRIPTION],
    @MAILDATE = [SUMMARYINFO].[MAILDATE],
    @ACTIVE = [SUMMARYINFO].[ACTIVE],
    @NUMPACKAGES = [SUMMARYINFO].[NUMPACKAGES],
    @NUMSEGMENTS = [SUMMARYINFO].[NUMSEGMENTS],
    @FIXEDCOST = [SUMMARYINFO].[FIXEDCOST],
    @DATEREFRESHED = [SUMMARYINFO].[DATEREFRESHED],
    @PACKAGENAME = [MKTPACKAGE].[NAME],
    @PACKAGEUNITCOST = (case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else [MKTPACKAGE].[UNITCOST] end),
    @SELECTIONNAME = [APPEALMAILINGSELECTION].[NAME],
    @STATUS = (case when [SUMMARYINFO].[ACTIVE] = 0 then N'Not activated' else N'Activated' end),
    @MKTSEGMENTATIONSEGMENTID = [MKTSEGMENTATIONSEGMENT].[ID],
    @MAILINGBUDGET = [SUMMARYINFO].[BUDGETAMOUNT],
    @BASECURRENCYID = [SUMMARYINFO].[BASECURRENCYID],
    @CURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION]([SUMMARYINFO].[BASECURRENCYID]),
    @ISOCURRENCYCODE = [CURRENCYPROPERTIES].[ISO4217],
    @DECIMALDIGITS = [CURRENCYPROPERTIES].[DECIMALDIGITS],
    @CURRENCYSYMBOL = [CURRENCYPROPERTIES].[CURRENCYSYMBOL],
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = [CURRENCYPROPERTIES].[SYMBOLDISPLAYSETTINGCODE],
    @UPDATECOUNTPARAMETERSETID = MKTSEGMENTATIONSEGMENTCALCULATEPROCESS.ID
  from @MKTSEGMENTATIONSUMMARYINFO as [SUMMARYINFO]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = @ID
  left outer join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left outer join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID]
  left outer join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  left outer join dbo.[IDSETREGISTER] as [APPEALMAILINGSELECTION] on [APPEALMAILINGSELECTION].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
  left outer join dbo.MKTSEGMENTATIONSEGMENTCALCULATEPROCESS on MKTSEGMENTATION.ID = MKTSEGMENTATIONSEGMENTCALCULATEPROCESS.SEGMENTATIONID
  outer apply dbo.[UFN_CURRENCY_GETPROPERTIES]([SUMMARYINFO].[BASECURRENCYID]) [CURRENCYPROPERTIES]

  if @DATALOADED = 1
    begin
      if @ACTIVE = 1
        select
          @SELECTIONRECORDCOUNT = [QUANTITY]
        from dbo.[MKTSEGMENTATIONACTIVE]
        where [ID] = @ID;
      else
        begin
          if not exists(select ID from dbo.APPEALMAILINGSETUP where ID = @ID)
            begin
              --If something changed with the segment, then re-cache the segment donor IDs and grab the new record count...

              if dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISRECORDCOUNTCACHECURRENT](@MKTSEGMENTATIONSEGMENTID, 1) = 0
                set @RECORDCOUNTNEEDSUPDATED = 1;
              else
                select
                  @SELECTIONRECORDCOUNT = sum([MKTSEGMENTATIONSEGMENTCACHEINFO].[OFFERCOUNT]) 
                from dbo.[MKTSEGMENTATIONSEGMENT] 
                inner join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID]
                where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID;
            end
        end

      if @SELECTIONRECORDCOUNT is not null
        set @TOTALCOST = (@SELECTIONRECORDCOUNT * @PACKAGEUNITCOST) + @FIXEDCOST;
      else
        begin
          set @SELECTIONRECORDCOUNT = N'--';

          set @TOTALCOST = N'--';

          set @RECORDCOUNTNEEDSUPDATED = 1;
        end

      select top(1)
        @ISHYBRID = 1,
        @SELECTIONS = dbo.UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML(MKTSEGMENTATIONSEGMENT.SEGMENTID, null),
        @CHANNELCODE = APPEALMAILINGSETUP.CHANNELCODE,
        @MAILPACKAGE = dbo.UFN_MKTPACKAGE_GETNAME(APPEALMAILINGSETUP.MAILPACKAGEID),
        @EMAILPACKAGE = dbo.UFN_MKTPACKAGE_GETNAME(APPEALMAILINGSETUP.EMAILPACKAGEID),
        @MKTASKLADDER = dbo.UFN_MKTASKLADDER_GETNAME(APPEALMAILINGSETUP.MKTASKLADDERID),
        @HASEMAIL = case when exists(select ID from dbo.COMMUNICATIONLETTER where COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID and COMMUNICATIONLETTER.CHANNELCODE > 0) then 1 else 0 end,
        @HASLETTERSELECTIONS = case when exists(select COMMUNICATIONLETTER.ID from dbo.COMMUNICATIONLETTER left outer join dbo.COMMUNICATIONLETTERSELECTION on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERSELECTION.COMMUNICATIONLETTERID and COMMUNICATIONLETTERSELECTION.FILTERTYPECODE = 0 where COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID and COMMUNICATIONLETTERSELECTION.ID is null) then 0 else 1 end,
        @HASINCOMPLETEEMAILLETTER = case when exists(select COMMUNICATIONLETTER.ID from dbo.COMMUNICATIONLETTER where COMMUNICATIONLETTER.SEGMENTATIONID = @ID and COMMUNICATIONLETTER.CHANNELCODE > 0 and (len(COMMUNICATIONLETTER.EMAILSUBJECT) = 0 or len(COMMUNICATIONLETTER.EMAILFROMADDRESS) = 0 or len(COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME) = 0 or len(COMMUNICATIONLETTER.EMAILCONTENTHTML) = 0)) then 1 else 0 end
      from dbo.APPEALMAILING
      inner join dbo.APPEALMAILINGSETUP on APPEALMAILINGSETUP.ID = APPEALMAILING.ID
      left join dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = APPEALMAILING.ID
      where APPEALMAILINGSETUP.ID = @ID;
    end

  return 0;