USP_DATALIST_MKTMARKETINGPLANEXPENSES

Displays a list of the active marketing plan expenses for a given marketing plan item.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENCYCODE	tinyint	IN	Currency

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTMARKETINGPLANEXPENSES]
(
  @ID uniqueidentifier,
  @CURRENCYCODE tinyint = 0
)
as
  set nocount on;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  if @CURRENCYCODE = 1 set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  select
    [MPIE].[ID],
    [MPIE].[DESCRIPTION],
    [CODE].[DESCRIPTION] as [EXPENSECATEGORYCODE],
    [MPIE].[EXPENSETYPE],
    case @CURRENCYCODE when 1 then [MPIE].[ORGANIZATIONAMOUNT] else [MPIE].[AMOUNT] end as [AMOUNT],
    [MPIE].[COUNT],
    case @CURRENCYCODE when 1 then [MPIE].[ORGANIZATIONBUDGETAMOUNT] else [MPIE].[BUDGETAMOUNT] end as [BUDGETAMOUNT],
    case @CURRENCYCODE when 1 then @ORGANIZATIONCURRENCYID else [MPIE].[BASECURRENCYID] end as [BASECURRENCYID],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [MPIE]
  inner join dbo.[MKTMARKETINGPLANITEM] as [MPI] on [MPI].[ID] = [MPIE].[MARKETINGPLANITEMID]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MP].[ID] = [MPI].[MARKETINGPLANID]
  left join dbo.[MKTEXPENSECATEGORYCODE] as [CODE] on [CODE].[ID] = [MPIE].[EXPENSECATEGORYCODEID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MP].[BASECURRENCYID] end
  where [MPI].[ID] = @ID;

  return 0;