USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONPASSIVE

The load procedure used by the view dataform template "Public Media Marketing Effort 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	int	INOUT	Effort ID
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SITE	nvarchar(100)	INOUT	Site
@CODE	nvarchar(10)	INOUT	Code
@APPEAL	nvarchar(max)	INOUT	Appeal
@ACTIVE	bit	INOUT	Active
@MAILDATE	date	INOUT	Date
@ACTIVATEDATE	datetime	INOUT	Activate date
@DATEREFRESHED	datetime	INOUT	Current as of
@SEGMENTS	int	INOUT	Segments
@PACKAGES	int	INOUT	Packages
@OFFERS	int	INOUT	Quantity
@BUDGET	money	INOUT	Budget
@FIXEDCOST	money	INOUT	Fixed cost
@TOTALEXPENSES	money	INOUT	Total expenses
@EXPECTEDREVENUE	money	INOUT	Expected revenue
@GIFTSOURCESDEFINED	int	INOUT	Gift record sources defined
@SEGMENTATIONACTIVATEPROCESSID	uniqueidentifier	INOUT	Marketing effort activate process ID
@SEGMENTATIONREFRESHPROCESSID	uniqueidentifier	INOUT	Marketing effort refresh process ID
@ISACTIVATING	bit	INOUT	Is activating?
@ADDMEDIAOUTLETCONTEXTID	nvarchar(38)	INOUT	Add media outlet context ID
@ADDTIMESLOTCONTEXTID	nvarchar(38)	INOUT	Add time slot context ID
@ADDMARKETINGLOCATIONCONTEXTID	nvarchar(38)	INOUT	Add marketing location context ID
@EVENTINSTANCEID	uniqueidentifier	INOUT	Event instance ID
@HASPACKAGE	bit	INOUT	Has a package
@HASAPPEAL	bit	INOUT	Has an appeal
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@CURRENCY	nvarchar(110)	INOUT	Currency
@RESPONSES	int	INOUT	Responses
@HASACTIVATIONSTATUS	bit	INOUT	Has activation status
@HASREFRESHSTATUS	bit	INOUT	Has refresh status
@BBECAPPEALID	nvarchar(36)	INOUT
@BBECAPPEALDESCRIPTION	nvarchar(100)	INOUT

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONPASSIVE]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGID integer = null output,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SITE nvarchar(100) = null output,
  @CODE nvarchar(10) = null output,
  @APPEAL nvarchar(max) = null output,
  @ACTIVE bit = null output,
  @MAILDATE date = null output,
  @ACTIVATEDATE datetime = null output,
  @DATEREFRESHED datetime = null output,
  @SEGMENTS integer = null output,
  @PACKAGES integer = null output,
  @OFFERS integer = null output,
  @BUDGET money = null output,
  @FIXEDCOST money = null output,
  @TOTALEXPENSES money = null output,
  @EXPECTEDREVENUE money = null output,
  @GIFTSOURCESDEFINED integer = null output,
  @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier = null output,
  @SEGMENTATIONREFRESHPROCESSID uniqueidentifier = null output,
  @ISACTIVATING bit = null output,
  @ADDMEDIAOUTLETCONTEXTID nvarchar(38) = null output,
  @ADDTIMESLOTCONTEXTID nvarchar(38) = null output,
  @ADDMARKETINGLOCATIONCONTEXTID nvarchar(38) = null output,
  @EVENTINSTANCEID uniqueidentifier = null output,
  @HASPACKAGE bit = null output,
  @HASAPPEAL bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @CURRENCY nvarchar(110) = null output,
  @RESPONSES integer = null output,
  @HASACTIVATIONSTATUS bit = null output,
  @HASREFRESHSTATUS bit = null output,
  @BBECAPPEALID nvarchar(36) = null output,
  @BBECAPPEALDESCRIPTION nvarchar(100) = null output
)
as
  set nocount on;

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

  select 
    @BBECAPPEALID = [APPEALSYSTEMID]
  from dbo.[MKTSEGMENTATIONACTIVATE] 
  where [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @ID
  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID]) = 1;

  begin try
    set @BBECAPPEALGUID = convert(uniqueidentifier, @BBECAPPEALID);
  end try
  begin catch
    set @BBECAPPEALGUID = null;
  end catch

  if not @BBECAPPEALGUID is null
    select
      @BBECAPPEALDESCRIPTION = [NAME]
    from dbo.[APPEAL] 
    where [ID] = @BBECAPPEALGUID;

  declare @RESPONSETABLE table (
    [OFFERS] integer,
    [RESPONDERS] integer,
    [RESPONSES] integer,
    [TOTALGIFTAMOUNT] money,
    [RESPONSERATE] decimal,
    [TOTALORGANIZATIONGIFTAMOUNT] money,
    [FIRSTRESPONSEDATE] datetime
  );

  set @DATALOADED = 0;

  select @ACTIVE = [MKTSEGMENTATION].[ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @ID;

  select
    @DATALOADED = 1,
    @MAILINGID = [MKTSEGMENTATION].[IDINTEGER],
    @NAME = [MKTSEGMENTATION].[NAME],
    @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @SITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]),
    @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
    @CODE = [MKTSEGMENTATION].[CODE],
    @ACTIVATEDATE = [MKTSEGMENTATION].[ACTIVATEDATE],
    @APPEAL = isnull(stuff(
                            (
                              select ', ' + case when [APPEALSYSTEMID] = @BBECAPPEALID then @BBECAPPEALDESCRIPTION else [APPEALDESCRIPTION] end 
                              from dbo.[MKTSEGMENTATIONACTIVATE] 
                              where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID] 
                              for xml path(''), type
                            ).value('.', 'varchar(max)')
                            , 1, 2, ''
                          ), ''),
    @DATEREFRESHED = isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]),
    @SEGMENTS = (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @PACKAGES = (select count([ID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @OFFERS = (
      select 
        isnull(sum(dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY](
                         [MKTSEGMENTATIONSEGMENT].[ID], 
                      @ACTIVE, 
                         case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end, 
                         case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end, 
                         [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE], 
                         [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE])), 0)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID),
    @BUDGET = [MKTSEGMENTATIONBUDGET].[BUDGETAMOUNT],
    @FIXEDCOST = [MKTSEGMENTATIONBUDGET].[FIXEDCOST],
    @TOTALEXPENSES = [MKTSEGMENTATIONBUDGET].[FIXEDCOST] + (
      select
        isnull(
          sum(
            -- cost per piece
            (case @ACTIVE
               when 1 then
                 case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
                   when 0 then [MKTSEGMENTATIONPACKAGE].[UNITCOST]
                   when 4 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] / @PACKAGEPERTHOUSANDAMOUNT else 0 end + 
                 [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE]
               else
                 case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
                   when 0 then [MKTPACKAGE].[UNITCOST]
                   when 4 then [MKTPACKAGE].[UNITCOST] / @PACKAGEPERTHOUSANDAMOUNT else 0 end +
                 dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 0, 0) +
                 -- Add any other package costs using 'Per thousand'
                 dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 4, 0)
             end 
             * dbo.[UFN_MKTSEGMENTATIONPASSIVEPACKAGE_GETQUANTITY](@ID, [MKTSEGMENTATIONPACKAGE].[PACKAGEID], @ACTIVE)) +
            -- cost per response
            (case @ACTIVE
               when 1 then
                 (case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end + 
                  [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE])
                 * dbo.[UFN_MKTSEGMENTATIONPACKAGEACTIVE_GETRESPONSECOUNT](@ID, [MKTSEGMENTATIONPACKAGE].[PACKAGEID])
               else
                 (case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 1 then [MKTPACKAGE].[UNITCOST] else 0 end + 
                  dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 1, 0))
                 * dbo.[UFN_MKTSEGMENTATIONPASSIVEPACKAGE_GETEXPECTEDRESPONSECOUNT](@ID, [MKTSEGMENTATIONPACKAGE].[PACKAGEID], @ACTIVE)
             end) +
            -- cost per effort
            (case @ACTIVE
               when 1 then
                 case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTSEGMENTATIONPACKAGE].[UNITCOST] else 0 end +
                 [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT]
               else
                 case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] when 2 then [MKTPACKAGE].[UNITCOST] else 0 end +
                 dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTSEGMENTATIONPACKAGE].[PACKAGEID], 2, 0)
             end)
          )
        , 0)
      from dbo.[MKTSEGMENTATIONPACKAGE]
      inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
      where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @ID),
    @EXPECTEDREVENUE = (
      select 
        isnull(sum(floor(dbo.[UFN_MKTSEGMENTATIONPASSIVESEGMENT_GETQUANTITY](
                               [MKTSEGMENTATIONSEGMENT].[ID], 
                               @ACTIVE,
                               case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONCALCULATIONMETHODCODE] else [MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHODCODE] end, 
                               case @ACTIVE when 1 then [MKTSEGMENTATIONSEGMENTACTIVE].[IMPRESSIONS] else [MKTSEGMENTPASSIVE].[IMPRESSIONS] end, 
                               [MKTSEGMENTATIONSEGMENT].[EXPOSURESTARTDATE], 
                               [MKTSEGMENTATIONSEGMENT].[EXPOSUREENDDATE]) 
                   * ([MKTSEGMENTATIONSEGMENT].[RESPONSERATE] / 100)) * [MKTSEGMENTATIONSEGMENT].[GIFTAMOUNT]), 0)
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left outer join dbo.[MKTSEGMENTATIONSEGMENTACTIVE] on [MKTSEGMENTATIONSEGMENTACTIVE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID),
    @GIFTSOURCESDEFINED = 
      (select (case when count(*) = 0 then (select case when (select count([ID]) from dbo.[MKTGIFTRECORDSOURCE]) > 0 then 1 else 0 end) else 0 end)
       from dbo.[MKTSEGMENTATIONSEGMENT]
       inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
       left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
       where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
       and [MKTGIFTRECORDSOURCE].[ID] is null),
    @SEGMENTATIONACTIVATEPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONACTIVATEPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @ISACTIVATING = dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]),
    @SEGMENTATIONREFRESHPROCESSID = (select [ID] from dbo.[MKTSEGMENTATIONREFRESHPROCESS] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]),
    @HASPACKAGE = (select case when exists (select 1 from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
    @HASAPPEAL = (select case when exists(select 1 from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID) then 1 else 0 end),
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
    @CURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTSEGMENTATION].[BASECURRENCYID])
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATION].[ID] = @ID;

  set @ADDMEDIAOUTLETCONTEXTID = convert(nvarchar(36), @ID) + '|6';
  set @ADDTIMESLOTCONTEXTID = convert(nvarchar(36), @ID) + '|7';
  set @ADDMARKETINGLOCATIONCONTEXTID = convert(nvarchar(36), @ID) + '|8';
  set @EVENTINSTANCEID = newid();

  insert into @RESPONSETABLE
    exec dbo.[USP_MKTSEGMENTATION_GETRESPONSECOUNTS] @SEGMENTATIONID = @ID;

  select
    @RESPONSES = [RESPONSES]
  from @RESPONSETABLE;

  if @SEGMENTATIONACTIVATEPROCESSID is not null
    set @HASACTIVATIONSTATUS = case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEPROCESSSTATUS] where [PARAMETERSETID] = @SEGMENTATIONACTIVATEPROCESSID) then 1 else 0 end
  else
    set @HASACTIVATIONSTATUS = 0;

  if @SEGMENTATIONREFRESHPROCESSID is not null
    set @HASREFRESHSTATUS = case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONREFRESHPROCESSSTATUS] where [PARAMETERSETID] = @SEGMENTATIONREFRESHPROCESSID) then 1 else 0 end
  else
    set @HASREFRESHSTATUS = 0;

  return 0;