USP_DATALIST_MKTSEGMENTPASSIVE_NONACTIVATEDMAILINGS

Displays a list of non-activated marketing efforts that use a specific public media segment.

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTPASSIVE_NONACTIVATEDMAILINGS]
(
  @SEGMENTID uniqueidentifier
)
as
  set nocount on;

  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  select
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.envelope_16.png' as [IMAGEKEY],
    [MKTSEGMENTATION].[NAME] as [MAILINGNAME],
    [MKTPACKAGE].[NAME] as [PACKAGENAME],
    [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE],
    datediff(dd, [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE], [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]) + 1 as  [EXPOSURE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
       when 0 then [MKTPACKAGE].[UNITCOST]
       when 4 then [MKTPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else 0 end) + 
     dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, 0) +
     -- Add any other package costs using 'Per thousand'

     dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, 0)
     as [COSTPERPIECE],
    (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTPACKAGE].[UNITCOST] else 0 end) + dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, 0) as [COSTPERRESPONSE],
    [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
    ([MKTSEGMENTATIONSEGMENT].[RESPONSERATE] / 100) as [EXPECTEDRESPONSERATE],
    [MKTPACKAGE].[BASECURRENCYID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
  left join dbo.[MKTPACKAGE] as [TESTPACKAGE] on [TESTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTATION].[ACTIVE] = 0
  order by [MKTSEGMENTATION].[NAME];

  return 0;