USP_DATALIST_MKTPLANINCOMEFORECAST

Returns a list of appeals for a marketing plan with their expected revenues for the first four months.
Parameters

Parameter	Parameter Type	Mode	Description
@PLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MONTH1RETURN	nvarchar(6)	IN	Month 1 Return Rate
@MONTH2RETURN	nvarchar(6)	IN	Month 2 Return Rate
@MONTH3RETURN	nvarchar(6)	IN	Month 3 Return Rate
@MONTH4RETURN	nvarchar(6)	IN	Month 4 Return Rate
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


CREATE procedure dbo.[USP_DATALIST_MKTPLANINCOMEFORECAST]
(
  @PLANID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @MONTH1RETURN nvarchar(6) = null,
  @MONTH2RETURN nvarchar(6) = null,
  @MONTH3RETURN nvarchar(6) = null,
  @MONTH4RETURN nvarchar(6) = null,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @ROWCOUNT int;
  declare @CURRENCYISOCURRENCYCODE nvarchar(6);
  declare @CURRENCYDECIMALDIGITS tinyint;
  declare @CURRENCYSYMBOL nvarchar(10);
  declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

  if @MONTH1RETURN is null
    set @MONTH1RETURN = '50.00';
  if @MONTH2RETURN is null
    set @MONTH2RETURN = '35.00';
  if @MONTH3RETURN is null
    set @MONTH3RETURN = '10.00';
  if @MONTH4RETURN is null
    set @MONTH4RETURN = '5.00';

  set @ROWCOUNT = 0;

  select
    @ROWCOUNT = count([MPI].[ID])
  from
    dbo.[MKTMARKETINGPLANITEM] as [MPI]
  where [MPI].[MARKETINGPLANID] = @PLANID
  and [MPI].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID]);

  if @ROWCOUNT > 0
    begin
      select 
        @CURRENCYISOCURRENCYCODE = [ISO4217],
        @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
        @CURRENCYSYMBOL = [CURRENCYSYMBOL],
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
      from dbo.[CURRENCY]
      where [ID] = case @CURRENCYCODE when 1 
                     then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() 
                     else (select [BASECURRENCYID] from dbo.[MKTMARKETINGPLAN] where [ID] = @PLANID)
                   end;

      select
        [MPI].[ID],
        [MPI].[NAME],
        dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME](dbo.[UFN_SITEID_MAPFROM_MARKETINGPLANID](@PLANID)) as [SITE],
        @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
        @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
        @CURRENCYSYMBOL as [CURRENCYSYMBOL],
        @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI].[STARTDATE]) as [STARTDATE],
        (case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end * cast(@MONTH1RETURN as float) / 100) as [MONTH1REVENUE],
        (case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end * cast(@MONTH2RETURN as float) / 100) as [MONTH2REVENUE],
        (case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end * cast(@MONTH3RETURN as float) / 100) as [MONTH3REVENUE],
        (case @CURRENCYCODE when 1 then [MPI].[ORGANIZATIONTOTALREVENUEGOAL] else [MPI].[TOTALREVENUEGOAL] end * cast(@MONTH4RETURN as float) / 100) as [MONTH4REVENUE],
        [MPI2].[NAME] as [PLAN],
        @MONTH1RETURN as [MONTH1RETURN],
        @MONTH2RETURN as [MONTH2RETURN],
        @MONTH3RETURN as [MONTH3RETURN],
        @MONTH4RETURN as [MONTH4RETURN],
        [MPI].[MAILDATE]
      from dbo.[MKTMARKETINGPLANITEM] as [MPI]
      inner join dbo.[MKTMARKETINGPLANITEM] as [MPI2] on [MPI2].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPI2].[LEVEL] = 0
      where [MPI].[MARKETINGPLANID] = @PLANID
      and [MPI].[LEVEL] = (select max([LEVEL]) from dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] where [MPI].[MARKETINGPLANID] = [MPITI].[MARKETINGPLANID])
      order by dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI].[STARTDATE]) asc;
    end
  else
    begin
      select 
        @CURRENCYISOCURRENCYCODE = [ISO4217],
        @CURRENCYDECIMALDIGITS = [DECIMALDIGITS],
        @CURRENCYSYMBOL = [CURRENCYSYMBOL],
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = [SYMBOLDISPLAYSETTINGCODE]
      from dbo.[CURRENCY]
      where [ID] = case @CURRENCYCODE when 1 
                     then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() 
                     else dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID)
                   end;

      select top 1
        '00000000-0000-0000-0000-000000000000' as [ID],
        '' as [NAME],
        '' as [SITE],
        @CURRENCYISOCURRENCYCODE as [CURRENCYISOCURRENCYCODE],
        @CURRENCYDECIMALDIGITS as [CURRENCYDECIMALDIGITS],
        @CURRENCYSYMBOL as [CURRENCYSYMBOL],
        @CURRENCYSYMBOLDISPLAYSETTINGCODE as [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        getdate() as [STARTDATE],
        0 as [MONTH1REVENUE],
        0 as [MONTH2REVENUE],
        0 as [MONTH3REVENUE],
        0 as [MONTH4REVENUE],
        [MPI].[NAME] as [PLAN],
        @MONTH1RETURN as [MONTH1RETURN],
        @MONTH2RETURN as [MONTH2RETURN],
        @MONTH3RETURN as [MONTH3RETURN],
        @MONTH4RETURN as [MONTH4RETURN],
        getdate()
      from dbo.[MKTMARKETINGPLANITEM] as [MPI]
      where [MPI].[MARKETINGPLANID] = @PLANID
      and [MPI].[LEVEL] = 0;
    end

  return 0;