USP_DATAFORMTEMPLATE_EDITLOAD_MKTMARKETINGPLANITEMASSUMPTIONS

The load procedure used by the edit dataform template "Marketing Plan Item Assumptions Edit 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.
@MARKETINGPLANID	uniqueidentifier	INOUT	Marketing plan ID
@CAPTION	nvarchar(50)	INOUT	Caption
@CHILDCAPTION	nvarchar(50)	INOUT	Child caption
@LEVEL	int	INOUT	Level
@BUDGETAMOUNT	money	INOUT	Budget amount
@TOTALREVENUEGOAL	money	INOUT	Revenue
@QUANTITY	int	INOUT	Quantity
@RESPONSERATEGOAL	decimal(20, 2)	INOUT	Response rate
@TOTALTRANSACTIONSGOAL	int	INOUT	Responses
@ALLOCATEDBUDGET	money	INOUT	Allocated budget
@ALLOCATEDTOTALREVENUE	money	INOUT	Allocated revenue
@ALLOCATEDQUANTITY	int	INOUT	Allocated quantity
@ALLOCATEDTOTALTRANSACTIONS	int	INOUT	Allocated responses
@ASSUMPTIONSLIST	xml	INOUT	Child assumptions
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTMARKETINGPLANITEMASSUMPTIONS]
(
  @ID uniqueidentifier,
  @DATALOADED bit= 0 output,
  @MARKETINGPLANID uniqueidentifier = null output,
  @CAPTION nvarchar(50) = null  output,
  @CHILDCAPTION nvarchar(50) = null output,
  @LEVEL int = null output,
  @BUDGETAMOUNT money = null output,
  @TOTALREVENUEGOAL money = null output,
  @QUANTITY int = null output,
  @RESPONSERATEGOAL decimal(20,2) = null output,    
  @TOTALTRANSACTIONSGOAL int = null output,
  @ALLOCATEDBUDGET money = null output,
  @ALLOCATEDTOTALREVENUE money = null output,
  @ALLOCATEDQUANTITY int = null output,
  @ALLOCATEDTOTALTRANSACTIONS int = null output,
  @ASSUMPTIONSLIST xml = null output,
  @TSLONG bigint = 0 output,
  @BASECURRENCYID uniqueidentifier = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @MARKETINGPLANID = [MKTMARKETINGPLANITEM].[MARKETINGPLANID],
    @CAPTION = [MKTMARKETINGPLANITEMTEMPLATEITEM].[CAPTION],
    @CHILDCAPTION = (select top 1 [CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [SUB] where ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [SUB].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] + 1 = [SUB].[LEVEL])),
    @LEVEL = [MKTMARKETINGPLANITEM].[LEVEL],
    @BASECURRENCYID = [MKTMARKETINGPLANITEM].[BASECURRENCYID],
    @BUDGETAMOUNT = [MKTMARKETINGPLANITEM].[BUDGETAMOUNT],
    @TOTALREVENUEGOAL = [MKTMARKETINGPLANITEM].[TOTALREVENUEGOAL],
    @QUANTITY = [MKTMARKETINGPLANITEM].[QUANTITY],
    @RESPONSERATEGOAL = [MKTMARKETINGPLANITEM].[RESPONSERATEGOAL], 
    @TOTALTRANSACTIONSGOAL = [MKTMARKETINGPLANITEM].[TOTALTRANSACTIONSGOAL], 
    @ALLOCATEDBUDGET = (select [ALLOCATED] from dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]([MKTMARKETINGPLANITEM].[ID])),
    @ALLOCATEDTOTALREVENUE = coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0),
    @ALLOCATEDQUANTITY = coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0),
    @ALLOCATEDTOTALTRANSACTIONS = coalesce((select sum([TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0),
    @ASSUMPTIONSLIST = dbo.[UFN_MKTMARKETINGPLANITEM_GETCHILDASSUMPTIONSLIST_TOITEMLISTXML]([MKTMARKETINGPLANITEM].[ID]),
    @TSLONG = [MKTMARKETINGPLANITEM].[TSLONG]
  from dbo.[MKTMARKETINGPLANITEM]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] on ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[LEVEL])
  where [MKTMARKETINGPLANITEM].[ID] = @ID;

  return 0;