USP_DATALIST_MKTMARKETINGPLANITEMS

Displays a list of the active marketing plan items.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@BUDGET	tinyint	IN	Budget
@REVENUE	tinyint	IN	Revenue
@QUANTITY	tinyint	IN	Quantity
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


CREATE procedure dbo.[USP_DATALIST_MKTMARKETINGPLANITEMS]
(
  @ID uniqueidentifier,
  @BUDGET tinyint = null,
  @REVENUE tinyint = null,
  @QUANTITY tinyint = null,
  @CURRENCYCODE tinyint = 0
)
as
  set nocount on;

  select
    convert(nvarchar(36), [MPI].[ID]) + '|' + convert(nvarchar(1), @CURRENCYCODE) as [ID],
    [MPI].[NAME],
    [MPI].[STARTDATE],
    [MPI].[ENDDATE],
    [MPITI].[CAPTION],
    [MPITI].[BACKCOLOR],
    [MPITI].[FORECOLOR],
    [MPITI].[CAPTIONCOLOR],
    [MPITI].[LEVEL],
    [MKTPLANITEMCATEGORYCODE].[DESCRIPTION] as [CATEGORY]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on ([MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL])
  left join dbo.[MKTPLANITEMCATEGORYCODE] on [MKTPLANITEMCATEGORYCODE].[ID] = [MPI].[PLANITEMCATEGORYCODEID]
  where [MPI].[PARENTMARKETINGPLANITEMID] = @ID
  and (
        (@BUDGET = 0 and ((select [ALLOCATED] from dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]([MPI].[ID])) > (select [SPECIFIED] from dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]([MPI].[ID]))))
        or 
        (@BUDGET = 1 and ((select [ALLOCATED] from dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]([MPI].[ID])) < (select [SPECIFIED] from dbo.[UFN_MKTMARKETINGPLANITEMBUDGET]([MPI].[ID]))))
        or 
         @BUDGET is null
      )
  and (
        (@REVENUE = 0 and (coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)) > ([MPI].[TOTALREVENUEGOAL]))
        or 
        (@REVENUE = 1 and (coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + coalesce((select sum([TOTALREVENUEGOAL]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)) < ([MPI].[TOTALREVENUEGOAL]))
        or
         @REVENUE is null
      )
  and (
        (@QUANTITY = 0 and (coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)) > ([MPI].QUANTITY))
        or
        (@QUANTITY = 1 and (coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANITEM] as [MPI] where [MPI].[PARENTMARKETINGPLANITEMID] = [MPI].[ID]), 0) + coalesce((select sum([QUANTITY]) from dbo.[MKTMARKETINGPLANBRIEF] as [MPB] where [MPB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0)) < ([MPI].QUANTITY))
        or 
         @QUANTITY is null
      )
  order by [MPI].[STARTDATE], [MPI].[NAME];

  return 0;