USP_MKTSEGMENTATIONPACKAGE_GETEXPENSESDATALIST

CREATE procedure dbo.[USP_MKTSEGMENTATIONPACKAGE_GETEXPENSESDATALIST]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENCYCODE tinyint = 0 /* 0 = base, 1 = organization */
)
as
  set nocount on;

  if (select [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) = 0
    begin
      --Check if the mailing is currently being activated...

      declare @R int;
      exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
      if @R <> 0
        return 1;
    end

  declare @PACKAGEPERTHOUSANDAMOUNT decimal(15,5) = 1000.0;

  select
    [MKTSEGMENTATIONPACKAGE].[ID],
    [MKTPACKAGE].[ID] as [PACKAGEID],
    [MKTPACKAGE].[NAME],
    (case when [MKTSEGMENTATION].[ACTIVE] = 1 then [MKTSEGMENTATIONPACKAGE].[CODE] else [MKTPACKAGE].[CODE] end) as [CODE],
    cast((case [MKTSEGMENTATION].[ACTIVE] 
            when 1 then 
              case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
                when 0 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end
                when 4 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else [MKTSEGMENTATIONPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT end else 0 end +
              case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERPIECE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERPIECE] end
            else 
              case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE]
                when 0 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end
                when 4 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST]/@PACKAGEPERTHOUSANDAMOUNT else [MKTPACKAGE].[UNITCOST]/@PACKAGEPERTHOUSANDAMOUNT end else 0 end +
              dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 0, @CURRENCYCODE) +
              -- Add any other package costs using 'Per thousand'

              dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 4, @CURRENCYCODE)
     end) as decimal(30,8)) -- WI 370811 cast as decimal to be explicit about the precision

    as [COSTPERPIECE],
    (case [MKTSEGMENTATION].[ACTIVE] 
       when 1 then 
         case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 1 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end else 0 end +
         case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPERRESPONSE] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPERRESPONSE] end
       else 
         case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 1 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
         dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 1, @CURRENCYCODE)
     end)
    as [COSTPERRESPONSE],
    (case [MKTSEGMENTATION].[ACTIVE] 
       when 1 then 
         case [MKTSEGMENTATIONPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 2 then case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONUNITCOST] else [MKTSEGMENTATIONPACKAGE].[UNITCOST] end else 0 end +
         case when @CURRENCYCODE = 1 then [MKTSEGMENTATIONPACKAGE].[ORGANIZATIONINSERTCOSTPEREFFORT] else [MKTSEGMENTATIONPACKAGE].[INSERTCOSTPEREFFORT] end
       else 
         case [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE] 
           when 2 then case when @CURRENCYCODE = 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST] else [MKTPACKAGE].[UNITCOST] end else 0 end +
         dbo.[UFN_MKTPACKAGE_GETINSERTCOSTFORDISTRIBUTION2]([MKTPACKAGE].[ID], 2, @CURRENCYCODE)
     end)
    as [COSTPEREFFORT],
    [MKTSEGMENTATION].[ACTIVE] as [SEGMENTATIONACTIVE],
    [MKTPACKAGE].[CHANNELCODE],
    [MKTPACKAGE].[CHANNEL],
    [MKTPACKAGE].[BASECURRENCYID],
    [CURRENCY].[ISO4217],
    [CURRENCY].[DECIMALDIGITS],
    [CURRENCY].[CURRENCYSYMBOL],
    [CURRENCY].[SYMBOLDISPLAYSETTINGCODE]
  from dbo.[MKTSEGMENTATIONPACKAGE]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONPACKAGE].[PACKAGEID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID]
  inner join dbo.[CURRENCY] on [CURRENCY].[ID] = case when @CURRENCYCODE = 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENTATION].[BASECURRENCYID] end
  where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @SEGMENTATIONID
  order by [MKTPACKAGE].[NAME];

  return 0;