USP_DATAFORMTEMPLATE_EDITLOAD_MKTMARKETINGPLANMAILINGASSUMPTIONS

The load procedure used by the edit dataform template "Marketing Plan Marketing Effort 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 response
@ASSUMPTIONSLIST	xml	INOUT	Segment 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_MKTMARKETINGPLANMAILINGASSUMPTIONS]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MARKETINGPLANID uniqueidentifier = null output,
  @CAPTION nvarchar(50) = null output,
  @CHILDCAPTION nvarchar(50) = null output,
  @LEVEL integer = null output,
  @BUDGETAMOUNT money = null output,
  @TOTALREVENUEGOAL money = null output,
  @QUANTITY integer = null output,
  @RESPONSERATEGOAL decimal(20,2) = null output,
  @TOTALTRANSACTIONSGOAL integer = null output,
  @ALLOCATEDBUDGET money = null output,
  @ALLOCATEDTOTALREVENUE money = null output,
  @ALLOCATEDQUANTITY integer = null output,
  @ALLOCATEDTOTALTRANSACTIONS integer = 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 [MPITI].[CAPTION] from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] + 1 = [MPITI].[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([MPI].[TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([MPB].[TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0),
    @ALLOCATEDQUANTITY = coalesce((select sum([MPI].[QUANTITY]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([MPB].[QUANTITY]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0), 
    @ALLOCATEDTOTALTRANSACTIONS = coalesce((select sum([MPI].[TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0) + coalesce((select sum([MPB].[TOTALTRANSACTIONSGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANITEM].[ID]), 0), 
    @ASSUMPTIONSLIST = dbo.[UFN_MKTMARKETINGPLANITEM_GETBRIEFASSUMPTIONSLIST2_TOITEMLISTXML]([MKTMARKETINGPLANITEM].[ID]),
    @TSLONG = [MKTMARKETINGPLANITEM].[TSLONG]
  from dbo.[MKTMARKETINGPLANITEM]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] [MKTMARKETINGPLANITEMTEMPLATEITEM] on ([MKTMARKETINGPLANITEM].[MARKETINGPLANID] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[MARKETINGPLANID] and [MKTMARKETINGPLANITEM].[LEVEL] = [MKTMARKETINGPLANITEMTEMPLATEITEM].[LEVEL])
  where [MKTMARKETINGPLANITEM].[ID] = @ID;

  return 0;