USP_DATALIST_PLANMAILINGPROFILE

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

  select
    [MPI].[MARKETINGPLANID] as [ID],
    [MPI].[NAME],
    dbo.[UFN_DATE_EARLIESTFROMFUZZYDATE]([MPI].[STARTDATE]) as [STARTDATE],
    case [MP].[ISAPPROVED] when 0 then 'Not approved' else 'Approved' end as [STATUS],
    isnull((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANITEMEXPENSE] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) +
    isnull((select sum(case @CURRENCYCODE when 1 then [ORGANIZATIONBUDGETAMOUNT] else [BUDGETAMOUNT] end) from dbo.[MKTMARKETINGPLANBRIEF] as [SUB] where [SUB].[MARKETINGPLANITEMID] = [MPI].[ID]), 0) 
    as [EXPENSES],
    isnull((select [NAME] from dbo.[MKTSOURCECODE] where [ID] = [MPI].[SOURCECODEID]), '<none>') as [SOURCECODE],
    [MPITI].[CAPTION],
    [MPI].[MAILDATE],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MP].[SITEID]) as [SITE],
    [CURRENCY].[ISO4217] as [CURRENCYISOCURRENCYCODE],
    [CURRENCY].[DECIMALDIGITS] as [CURRENCYDECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL] as [CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE] as [CURRENCYSYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTMARKETINGPLANITEM] as [MPI]
  inner join dbo.[MKTMARKETINGPLAN] as [MP] on [MP].[ID] = [MPI].[MARKETINGPLANID]
  left join dbo.[MKTMARKETINGPLANITEMTEMPLATEITEM] as [MPITI] on [MPITI].[MARKETINGPLANID] = [MPI].[MARKETINGPLANID] and [MPITI].[LEVEL] = [MPI].[LEVEL]
  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;