USP_DATAFORMTEMPLATE_MKTMARKETINGPLANITEM_VIEW

The load procedure used by the view dataform template "Marketing Plan Item View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(38)	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.
@MARKETINGPLANID	uniqueidentifier	INOUT	Marketing plan ID
@PARENTMARKETINGPLANITEMID	uniqueidentifier	INOUT	Parent marketing plan item ID
@CAPTION	nvarchar(50)	INOUT	Caption
@NAME	nvarchar(100)	INOUT	Name
@SOURCECODE	nvarchar(100)	INOUT	Source code layout
@CODE	nvarchar(10)	INOUT	Code
@STATUS	nvarchar(25)	INOUT	Approval status
@CATEGORY	nvarchar(25)	INOUT	Category
@GOALS	nvarchar(max)	INOUT	Goals
@STARTDATE	UDT_FUZZYDATE	INOUT	Start date
@ENDDATE	UDT_FUZZYDATE	INOUT	End date
@BACKCOLOR	int	INOUT	Back color
@FORECOLOR	int	INOUT	Fore color
@LEVEL0NAME	nvarchar(50)	INOUT	Level 0 name
@LEVEL0ID	uniqueidentifier	INOUT	Level 0 ID
@LEVEL1CAPTION	nvarchar(50)	INOUT	Level 1 caption
@LEVEL1NAME	nvarchar(50)	INOUT	Level 1 name
@LEVEL1ID	uniqueidentifier	INOUT	Level 1 ID
@LEVEL1COUNT	int	INOUT	Level 1 count
@LEVEL2CAPTION	nvarchar(50)	INOUT	Level 2 caption
@LEVEL2NAME	nvarchar(50)	INOUT	Level 2 name
@LEVEL2ID	uniqueidentifier	INOUT	Level 2 ID
@LEVEL2COUNT	int	INOUT	Level 2 count
@LEVEL3CAPTION	nvarchar(50)	INOUT	Level 3 caption
@LEVEL3NAME	nvarchar(50)	INOUT	Level 3 name
@LEVEL3ID	uniqueidentifier	INOUT	Level 3 ID
@LEVEL3COUNT	int	INOUT	Level 3 count
@LEVEL4CAPTION	nvarchar(50)	INOUT	Level 4 caption
@LEVEL4NAME	nvarchar(50)	INOUT	Level 4 name
@LEVEL4ID	uniqueidentifier	INOUT	Level 4 ID
@LEVEL4COUNT	int	INOUT	Level 4 count
@LEVEL	int	INOUT	Level
@APPEALLEVEL	int	INOUT	Appeal level
@APPEALCAPTION	nvarchar(100)	INOUT	Appeal caption
@ASSUMEDBUDGET	decimal(18, 0)	INOUT	Assumed budget
@ALLOCATEDBUDGET	decimal(18, 0)	INOUT	Allocated budget
@ASSUMEDREVENUE	decimal(18, 0)	INOUT	Assumed total revenue
@ALLOCATEDREVENUE	decimal(18, 0)	INOUT	Allocated total revenue
@ASSUMEDRESPONSE	int	INOUT	Assumed total responses
@ALLOCATEDRESPONSE	int	INOUT	Allocated total responses
@ASSUMEDQUANTITY	int	INOUT	Assumed quantity
@ALLOCATEDQUANTITY	int	INOUT	Allocated quantity
@EXPENSES	money	INOUT	Expenses
@AVGGIFT	money	INOUT	Average gift
@COSTPERPIECE	money	INOUT	Cost per piece
@SEGMENTATIONNAME	nvarchar(100)	INOUT	Marketing effort name
@SEGMENTATIONID	uniqueidentifier	INOUT	Effort ID
@ISLINKED	bit	INOUT	Linked to marketing effort?
@MAILINGLEVEL	int	INOUT	Marketing effort level
@MAILDATE	datetime	INOUT	Date
@HASACTIVATEDMAILING	bit	INOUT	Has activated marketing effort?
@ALERTSENABLED	bit	INOUT	Alerts enabled?
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@BASECURRENCYCANBECHANGED	bit	INOUT	Base currency can be changed?

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_MKTMARKETINGPLANITEM_VIEW]
(
  @ID nvarchar(38),
  @DATALOADED bit = 0 output,
  @MARKETINGPLANID uniqueidentifier = null output,
  @PARENTMARKETINGPLANITEMID uniqueidentifier = null output,
  @CAPTION nvarchar(50) = null output,
  @NAME nvarchar(100) = null output,
  @SOURCECODE nvarchar(100) = null output,
  @CODE nvarchar(10) = null output,
  @STATUS nvarchar(25) = null output,
  @CATEGORY nvarchar(25) = null output,
  @GOALS nvarchar(max) = null output,
  @STARTDATE dbo.UDT_FUZZYDATE = null output,
  @ENDDATE dbo.UDT_FUZZYDATE = null output,
  @BACKCOLOR integer = null output,
  @FORECOLOR integer = null output,
  @LEVEL0NAME nvarchar(50) = null output,
  @LEVEL0ID uniqueidentifier = null output,
  @LEVEL1CAPTION nvarchar(50) = null output,
  @LEVEL1NAME nvarchar(50) = null output,
  @LEVEL1ID uniqueidentifier = null output,
  @LEVEL1COUNT integer = null output,
  @LEVEL2CAPTION nvarchar(50) = null output,
  @LEVEL2NAME nvarchar(50) = null output,
  @LEVEL2ID uniqueidentifier = null output,
  @LEVEL2COUNT integer = null output,
  @LEVEL3CAPTION nvarchar(50) = null output,
  @LEVEL3NAME nvarchar(50) = null output,
  @LEVEL3ID uniqueidentifier = null output,
  @LEVEL3COUNT integer = null output,
  @LEVEL4CAPTION nvarchar(50) = null output,
  @LEVEL4NAME nvarchar(50) = null output,
  @LEVEL4ID uniqueidentifier = null output,
  @LEVEL4COUNT integer = null output,
  @LEVEL integer = null output,
  @APPEALLEVEL integer = null output,
  @APPEALCAPTION nvarchar(100) = null output,
  @ASSUMEDBUDGET decimal = null output,
  @ALLOCATEDBUDGET decimal = null output,
  @ASSUMEDREVENUE decimal = null output,
  @ALLOCATEDREVENUE decimal = null output,
  @ASSUMEDRESPONSE integer = null output,
  @ALLOCATEDRESPONSE integer = null output,
  @ASSUMEDQUANTITY integer = null output,
  @ALLOCATEDQUANTITY integer = null output,
  @EXPENSES money = null output,
  @AVGGIFT money = null output,
  @COSTPERPIECE money = null output,
  @SEGMENTATIONNAME nvarchar(100) = null output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @ISLINKED bit = null output,
  @MAILINGLEVEL integer = null output,
  @MAILDATE datetime = null output,
  @HASACTIVATEDMAILING bit = null output,
  @ALERTSENABLED bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BASECURRENCYCANBECHANGED bit = null output
)
as
  set nocount on;

  declare @MARKETINGPLANITEMID uniqueidentifier;
  declare @CURRENCYCODE tinyint;

  set @DATALOADED = 0;

  if charindex('|', @ID, 1) > 0
    begin
      set @MARKETINGPLANITEMID = convert(uniqueidentifier, substring(@ID, 1, 36));
      set @CURRENCYCODE = convert(tinyint, substring(@ID, 38, 1));
    end
  else
    begin
      set @MARKETINGPLANITEMID = convert(uniqueidentifier, @ID);
      set @CURRENCYCODE = 0;
    end

  select
    @DATALOADED = 1,
    @MARKETINGPLANID = [MPI].[MARKETINGPLANID],
    @PARENTMARKETINGPLANITEMID = [MPI].[PARENTMARKETINGPLANITEMID],
    @CAPTION = [MPITI].[CAPTION],
    @NAME = [MPI].[NAME],
    @SOURCECODE = (coalesce((select [NAME] from dbo.[MKTSOURCECODE] where [ID] = [MPI].[SOURCECODEID]), '<none>')),
    @CODE = [MPI].[CODE],
    @STATUS = (case [MP].[ISAPPROVED] when 0 then 'Not approved' else 'Approved' end),
    @CATEGORY = [CODE].[DESCRIPTION],
    @GOALS = [MPI].[GOALS],
    @STARTDATE = [MPI].[STARTDATE],
    @ENDDATE = [MPI].[ENDDATE], 
    @BACKCOLOR = [MPITI].[BACKCOLOR],
    @FORECOLOR = [MPITI].[FORECOLOR],
    @LEVEL0NAME = coalesce((select top 1 [NAME] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 5), ''),
    @LEVEL0ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 5),
    @LEVEL1CAPTION = (select [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [SUB].[LEVEL] = [MPITI].[LEVEL] + 1)),
    @LEVEL1NAME = coalesce((select top 1 [NAME] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 4), ''),          
    @LEVEL1ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 4),
    @LEVEL1COUNT = (select count(*) from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPITI].[LEVEL] + 1),
    @LEVEL2CAPTION = (select [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [SUB].[LEVEL] = [MPITI].[LEVEL] + 2)),
    @LEVEL2NAME = coalesce((select top 1 [NAME] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 3), ''),
    @LEVEL2ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 3),
    @LEVEL2COUNT = (select count(*) from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPITI].[LEVEL] + 2),
    @LEVEL3CAPTION = (select [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [SUB].[LEVEL] = [MPITI].[LEVEL] + 3)),
    @LEVEL3NAME = coalesce((select top 1 [NAME] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 2), ''),
    @LEVEL3ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 2),
    @LEVEL3COUNT = (select count(*) from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPITI].[LEVEL] + 3),
    @LEVEL4CAPTION = (select [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [SUB].[LEVEL] = [MPITI].[LEVEL] + 4)),
    @LEVEL4NAME = coalesce((select top 1 [NAME] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 1), ''),
    @LEVEL4ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHY]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPI].[LEVEL] - 1),
    @LEVEL4COUNT = (select count(*) from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPITI].[LEVEL] + 4),
    @LEVEL = [MPI].[LEVEL],
    @APPEALLEVEL = (select max([LEVEL]) - 1 from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]),
    @APPEALCAPTION = (select [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [SUB].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]))),
    @BASECURRENCYID = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MPI].[BASECURRENCYID] end,
    @ASSUMEDBUDGET = case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONBUDGETAMOUNT] else [MPI].[BUDGETAMOUNT] end,
    @ALLOCATEDBUDGET = coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONBUDGETAMOUNT] else [SUB].[BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                       coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONBUDGETAMOUNT] else [SUB].[BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0),
    @ASSUMEDREVENUE = case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end,
    @ALLOCATEDREVENUE = coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONTOTALREVENUEGOAL] else [SUB].[TOTALREVENUEGOAL] end) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                        coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONTOTALREVENUEGOAL] else [SUB].[TOTALREVENUEGOAL] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0),
    @ASSUMEDRESPONSE = [MPI].[TOTALTRANSACTIONSGOAL],
    @ALLOCATEDRESPONSE = coalesce((select sum([SUB].[TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                         coalesce((select sum([SUB].[TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0), 
    @ASSUMEDQUANTITY = [MPI].[QUANTITY],
    @ALLOCATEDQUANTITY = coalesce((select sum([SUB].[QUANTITY]) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                         coalesce((select sum([SUB].[QUANTITY]) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0),
    @EXPENSES = coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONBUDGETAMOUNT] else [SUB].[BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                coalesce((select sum(case @CURRENCYCODE when 1 then [SUB].[ORGANIZATIONBUDGETAMOUNT] else [SUB].[BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0),
    @AVGGIFT = case when [MPI].[TOTALTRANSACTIONSGOAL] > 0
                    then case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end / [MPI].[TOTALTRANSACTIONSGOAL]
                    else 0 end,
    @COSTPERPIECE = case when [MPI].[QUANTITY] > 0
                    then case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONBUDGETAMOUNT] else [MPI].[BUDGETAMOUNT] end / [MPI].[QUANTITY]
                    else 0 end,
    @SEGMENTATIONNAME = (select top 1 [MKTSEGMENTATION].[NAME] from dbo.[MKTSEGMENTATION] where ([MPI].[ID] = [MKTSEGMENTATION].[MARKETINGPLANITEMID])),
    @SEGMENTATIONID = (select top 1 [MKTSEGMENTATION].[ID] from dbo.[MKTSEGMENTATION] where ([MPI].[ID] = [MKTSEGMENTATION].[MARKETINGPLANITEMID])),
    @ISLINKED = (case when exists ((select [MKTSEGMENTATION].[ID] from dbo.[MKTSEGMENTATION] where ([MPI].[ID] = [MKTSEGMENTATION].[MARKETINGPLANITEMID]))) then 1 else 0 end),
    @MAILINGLEVEL = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where [MPI].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID]),
    @MAILDATE = [MPI].[MAILDATE]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MP].[ID] = [MPI].[MARKETINGPLANID]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on ([MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL])
  left outer join dbo.[MKTPLANITEMCATEGORYCODE] as [CODE] on [CODE].[ID] = [MPI].[PLANITEMCATEGORYCODEID]
  where [MPI].[ID] = @MARKETINGPLANITEMID;

  with [PLANS] ([ID], [IDTREE]) as
  (
    select [MPI].[ID], convert(nvarchar(max), [MPI].[ID]) as [IDTREE]
    from dbo.[MKTMARKETINGPLANITEM] as [MPI]
    where [MPI].[PARENTMARKETINGPLANITEMID] is null
    union all
    select [MPI].[ID], convert(nvarchar(max), [PLANS].[IDTREE] + '\' + convert(nvarchar(36), [MPI].[ID]))
    from dbo.[MKTMARKETINGPLANITEM] as [MPI]
    inner join [PLANS] on [MPI].[PARENTMARKETINGPLANITEMID] = [PLANS].[ID]
  )
  select @HASACTIVATEDMAILING = 1 
  from [PLANS]
  where 
    [ID] in (select [MARKETINGPLANITEMID] from dbo.[MKTSEGMENTATION] where [MARKETINGPLANITEMID] is not null and [ACTIVE] = 1) 
    and charindex(convert(varchar(36), @MARKETINGPLANITEMID), [IDTREE]) > 0;

  if @HASACTIVATEDMAILING is null set @HASACTIVATEDMAILING = 0;
  if @AVGGIFT is null set @AVGGIFT = 0;
  if @COSTPERPIECE is null set @COSTPERPIECE = 0;

  if exists(select object_id from sys.objects where type = 'U' and name = 'DATABASEMAILSETTINGS')
    select @ALERTSENABLED = [ENABLED] from dbo.[DATABASEMAILSETTINGS];

  declare @PLANLINKEDTOMAILING bit;
  set @PLANLINKEDTOMAILING = case when exists (select * 
                                               from dbo.[MKTMARKETINGPLAN]
                                               inner join [MKTMARKETINGPLANITEM] on [MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLAN].[ID]
                                               left join [MKTSEGMENTATION] on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]
                                               where [MKTMARKETINGPLAN].[ID] = @MARKETINGPLANID and [MKTSEGMENTATION].[ID] is not null) then 1 else 0 end;

  set @BASECURRENCYCANBECHANGED = case when exists (select top 1 1 
                                                    from dbo.[MKTMARKETINGPLANBRIEF]
                                                    where 
                                                      [MKTMARKETINGPLANBRIEF].[MARKETINGPLANID] = @MARKETINGPLANID and 
                                                      [MKTMARKETINGPLANBRIEF].[PACKAGEID] is not null) or @PLANLINKEDTOMAILING = 1 then 0 else 1 end;
  return 0;