USP_DATAFORMTEMPLATE_MKTMARKETINGPLAN_VIEW

The load procedure used by the view dataform template "Marketing Plan 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.
@NAME	nvarchar(100)	INOUT	Name
@CODE	nvarchar(10)	INOUT	Code
@STATUS	nvarchar(25)	INOUT	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
@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	int	INOUT	Expenses
@SITE	nvarchar(1024)	INOUT	Site
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@BASECURRENCY	nvarchar(220)	INOUT	Base currency

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_MKTMARKETINGPLAN_VIEW]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME 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 int = null output,
  @FORECOLOR int = 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 int = null output,
  @LEVEL2CAPTION nvarchar(50) = null output,
  @LEVEL2NAME nvarchar(50) = null output,
  @LEVEL2ID uniqueidentifier = null output,
  @LEVEL2COUNT int = null output,
  @LEVEL3CAPTION nvarchar(50) = null output,
  @LEVEL3NAME nvarchar(50) = null output,
  @LEVEL3ID uniqueidentifier = null output,
  @LEVEL3COUNT int = null output,
  @LEVEL4CAPTION nvarchar(50) = null output,
  @LEVEL4NAME nvarchar(50) = null output,
  @LEVEL4ID uniqueidentifier = null output,
  @LEVEL4COUNT int = null output,
  @LEVEL int = null output,
  @ASSUMEDBUDGET decimal = null output,
  @ALLOCATEDBUDGET decimal = null output,
  @ASSUMEDREVENUE decimal = null output,
  @ALLOCATEDREVENUE decimal = null output,
  @ASSUMEDRESPONSE int = null output,
  @ALLOCATEDRESPONSE int = null output,
  @ASSUMEDQUANTITY int = null output,
  @ALLOCATEDQUANTITY int = null output,
  @EXPENSES int = null output,
  @SITE nvarchar(1024) = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @BASECURRENCY nvarchar(220) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAME = [MPI].[NAME],
    @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_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 0), ''),
    @LEVEL0ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 0),
    @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_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 1), ''),          
    @LEVEL1ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 1),
    @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_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 2), ''),
    @LEVEL2ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 2),
    @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_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 3), ''),
    @LEVEL3ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 3),
    @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_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 4), ''),
    @LEVEL4ID = (select top 1 [ID] from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = 4),
    @LEVEL4COUNT = (select count(*) from dbo.[UFN_MKTMARKETINGPLANITEMHIERARCHYFROMID]([MPI].[ID]) as [SUB] where [SUB].[LEVEL] = [MPITI].[LEVEL] + 4),
    @LEVEL = [MPI].[LEVEL],
    @BASECURRENCYID = [MPI].[BASECURRENCYID],
    @BASECURRENCY = dbo.[UFN_CURRENCY_GETDESCRIPTION]([MPI].[BASECURRENCYID]),
    @ASSUMEDBUDGET = [MPI].[BUDGETAMOUNT],
    @ALLOCATEDBUDGET = coalesce((select sum([SUB].[BUDGETAMOUNT]) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                       coalesce((select sum([SUB].[BUDGETAMOUNT]) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0),
    @ASSUMEDREVENUE = [MPI].[TOTALREVENUEGOAL],
    @ALLOCATEDREVENUE = coalesce((select sum([SUB].[TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [SUB] where [SUB].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + 
                        coalesce((select sum([SUB].[TOTALREVENUEGOAL]) 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([SUB].[BUDGETAMOUNT]) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] in (select [ID] from dbo.[MKTMARKETINGPLANITEM] as [SUB2] where [SUB2].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID])), 0) + 
                coalesce((select sum([SUB].[BUDGETAMOUNT]) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID]), 0),
    @SITE = dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MP].[SITEID])
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MP].[ID] = [MPI].[MARKETINGPLANID]
  inner 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] = @ID;

  return 0;