USP_DATALIST_MKTMARKETINGPLANBRIEF

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

  declare @MAXSEQ int;
  declare @MINSEQ int;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  select
    @MINSEQ = isnull(min([SEQUENCE]), 0),
    @MAXSEQ = isnull(max([SEQUENCE]), 0)
  from dbo.[MKTMARKETINGPLANBRIEF]
  where [MARKETINGPLANITEMID] = @ID;

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

  select
    [MPB].[ID],
    [MPB].[NAME],
    [MPB].[DESCRIPTION],
    [P].[NAME] as [PACKAGE],
    case @CURRENCYCODE when 1 then [MPB].[ORGANIZATIONUNITCOST] else [MPB].[UNITCOST] end as [COST],
    [MPB].[QUANTITY],
    case @CURRENCYCODE when 1 then [MPB].[ORGANIZATIONBUDGETAMOUNT] else [MPB].[BUDGETAMOUNT] end as [BUDGET],
    case @CURRENCYCODE when 1 then [MPB].[ORGANIZATIONTOTALREVENUEGOAL] else [MPB].[TOTALREVENUEGOAL] end as [REVENUE],
    [MPB].[RESPONSERATEGOAL] as [RESPONSERATE],
    [MPB].[TOTALTRANSACTIONSGOAL] as [RESPONSES],
    case @CURRENCYCODE when 1 then [MPB].[ORGANIZATIONAVERAGEREVENUEGOAL] else [MPB].[AVERAGEREVENUEGOAL] end as [AVERAGEAMOUNT],
    [MPB].[SEQUENCE],
    @MINSEQ as [MINSEQUENCE],
    @MAXSEQ as [MAXSEQUENCE],
    case @CURRENCYCODE when 1 then @ORGANIZATIONCURRENCYID else [MPB].[BASECURRENCYID] end as [BASECURRENCYID],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTMARKETINGPLANBRIEF] as [MPB]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MP].[ID] = [MPB].[MARKETINGPLANID]
  left join dbo.[MKTPACKAGE] as [P] on [MPB].[PACKAGEID] = [P].[ID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MP].[BASECURRENCYID] end
  where [MPB].[MARKETINGPLANITEMID] = @ID
  order by [MPB].SEQUENCE;

  return 0;