UFN_MKTMARKETINGPLAN_CUMULATIVERESPONSE

CREATE function dbo.[UFN_MKTMARKETINGPLAN_CUMULATIVERESPONSE]
(
  @MARKETINGPLANITEMID uniqueidentifier=null
)
/*
Returns a table of template items that have been assigned to the given MARKETINGPLANID
*/
returns @CUMRESPONSE table
(
  [ID] uniqueidentifier null, --not null primary key,

  [NAME] nvarchar(50) NULL,
  [PARENTBUDGETAMOUNT] decimal null,
  [BUDGETAMOUNT] decimal null,
  [CUMBUDGETAMOUNT] decimal null,
  [PARENTTOTALREVENUEGOAL] decimal null,
  [TOTALREVENUEGOAL] decimal null,
  [CUMTOTALREVENUEGOAL] decimal null,
  [PARENTTOTALRESPONSEGOAL] int null,
  [TOTALRESPONSEGOAL] int null,
  [CUMTOTALRESPONSEGOAL] int null,
  [SEQUENCE] int identity (1,1)
)
as
begin
  declare @lvl as int;
  declare @mpid as uniqueidentifier;

  select
    @lvl = [LEVEL],
    @mpid=[MARKETINGPLANID]
  from
    dbo.[MKTMARKETINGPLANITEM]
  where
    ID=@MARKETINGPLANITEMID;

  set @lvl=coalesce(@lvl,0);

  insert into @CUMRESPONSE
  select
    M.[ID],
    M.[NAME],
    P.[BUDGETAMOUNT],
    M.[BUDGETAMOUNT],
    0,
    P.[TOTALREVENUEGOAL],
    M.[TOTALREVENUEGOAL],
    0,
    P.[TOTALTRANSACTIONSGOAL],
    M.[TOTALTRANSACTIONSGOAL],
    0
  from
    dbo.[MKTMARKETINGPLANITEM] M
  inner join
    dbo.[MKTMARKETINGPLANITEM] P on M.[PARENTMARKETINGPLANITEMID]=P.[ID]
  where
    M.[LEVEL]=@lvl
  and
    M.[MARKETINGPLANID]=@mpid
  order by
    M.[STARTDATE], M.[NAME]

  declare @totbud decimal;
  declare @totrev decimal;
  declare @totrsp int;
  declare @seq int;
  set @seq = 0;

  while @@rowcount > 0
  begin
    set @seq = @seq + 1;
    set @totbud = (select sum(BUDGETAMOUNT) from @CUMRESPONSE where [SEQUENCE]<=@seq);
    set @totrev = (select sum(TOTALREVENUEGOAL) from @CUMRESPONSE where [SEQUENCE]<=@seq);
    set @totrsp = (select sum(TOTALRESPONSEGOAL) from @CUMRESPONSE where [SEQUENCE]<=@seq);
    update @CUMRESPONSE set [CUMBUDGETAMOUNT]=@totbud, [CUMTOTALREVENUEGOAL]=@totrev, [CUMTOTALRESPONSEGOAL]=@totrsp where [SEQUENCE]=@seq;
  end;
  return;
end