USP_DATALIST_PLANPACKAGE

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

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

  select
    [P].[ID],
    [P].[CODE],
    [P].[NAME],
    case @CURRENCYCODE when 1 then [P].[ORGANIZATIONUNITCOST] else [P].[UNITCOST] end as [COST],
    sum([MPB].[QUANTITY]) as [TOTALQUANTITY],
    sum(case @CURRENCYCODE when 1 then [MPB].[ORGANIZATIONBUDGETAMOUNT] else [MPB].[BUDGETAMOUNT] end) as [TOTALBUDGET],
    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]
  inner join dbo.[MKTPACKAGE] as [P] on [P].[ID] = [MPB].[PACKAGEID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MP].[BASECURRENCYID] end
  where @ID is null or [MPB].[MARKETINGPLANITEMID] = @ID
  group by [P].[ID],
           [P].[CODE],
           [P].[NAME],
           case @CURRENCYCODE when 1 then [P].[ORGANIZATIONUNITCOST] else [P].[UNITCOST] end,
           case @CURRENCYCODE when 1 then @ORGANIZATIONCURRENCYID else [MPB].[BASECURRENCYID] end,
           [CURRENCY].[ISO4217],
           [CURRENCY].[DECIMALDIGITS],
           [CURRENCY].[CURRENCYSYMBOL],
           [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  order by [P].[CODE], [P].[NAME];

  return 0;