UFN_MKTSEGMENTATION_GETAMOUNTSINCURRENCY_BULK

CREATE function dbo.UFN_MKTSEGMENTATION_GETAMOUNTSINCURRENCY_BULK
  (
    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint
  )
  returns table
  as
  return
  (
    --CTE to calculate MKTSegmentationActive in currency

    with [MKTSEGMENTATION_CTE] 
    (
      [ID], 
      [ACTIVE], 
      [ACTIVATEDATE], 
      [MKTSEGMENTATIONACTIVEID], 
      [TOTALGIFTAMOUNTINCURRENCY], 
      [TOTALCOSTINCURRENCY],
      [QUANTITY],
      [RESPONSERATE],
      [RESPONDERS],
      [RESPONSES]
    )
      as
    (
      select 
        [MKTSEGMENTATION].[ID],
        [MKTSEGMENTATION].[ACTIVE],
        [MKTSEGMENTATION].[ACTIVATEDATE],
        [MKTSEGMENTATIONACTIVE].[ID] as [MKTSEGMENTATIONACTIVEID],
        case 
          when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
            then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT]
          else [MKTSEGMENTATIONACTIVE].[TOTALGIFTAMOUNT]
        end [TOTALGIFTAMOUNTINCURRENCY],
        case 
          when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
            then [MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST]
          else [MKTSEGMENTATIONACTIVE].[TOTALCOST]
        end [TOTALCOSTINCURRENCY],
        [MKTSEGMENTATIONACTIVE].[QUANTITY],
        [MKTSEGMENTATIONACTIVE].[RESPONSERATE],
        [MKTSEGMENTATIONACTIVE].[RESPONDERS],
        [MKTSEGMENTATIONACTIVE].[RESPONSES]
      from dbo.[MKTSEGMENTATION]
      left join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
      where 
        (@CURRENCYID is null)
        or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
        or @CURRENCYID = [MKTSEGMENTATIONACTIVE].[BASECURRENCYID]

      union all

      select
        [MKTSEGMENTATION].[ID],
        [MKTSEGMENTATION].[ACTIVE],
        [MKTSEGMENTATION].[ACTIVATEDATE],
        [MKTSEGMENTATIONACTIVE].[ID] as [MKTSEGMENTATIONACTIVEID],
        case
          when [LATESTORGANIZATIONEXCHANGERATE].[RATE] is not null
            then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT], [LATESTORGANIZATIONEXCHANGERATE].[RATE]), @DECIMALDIGITS, @ROUNDINGTYPECODE)
          when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
            then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALGIFTAMOUNT], cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE]) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
          else 0
        end [TOTALGIFTAMOUNTINCURRENCY],
        case
          when [LATESTORGANIZATIONEXCHANGERATE].[RATE] is not null
            then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST], [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
          when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
            then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONACTIVE].[ORGANIZATIONTOTALCOST], cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE]) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
          else 0
        end [TOTALCOSTINCURRENCY],
        [MKTSEGMENTATIONACTIVE].[QUANTITY],
        [MKTSEGMENTATIONACTIVE].[RESPONSERATE],
        [MKTSEGMENTATIONACTIVE].[RESPONDERS],
        [MKTSEGMENTATIONACTIVE].[RESPONSES]
      from dbo.[MKTSEGMENTATION]
      left join dbo.[MKTSEGMENTATIONACTIVE] on [MKTSEGMENTATIONACTIVE].[ID] = [MKTSEGMENTATION].[ID]
      outer apply
      (
        select [RATE]
        from dbo.[CURRENCYEXCHANGERATE]
        where
          @ORGANIZATIONCURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
          and @CURRENCYID = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
          and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
     and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
          and [MKTSEGMENTATION].[ACTIVATEDATE] >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
          and [MKTSEGMENTATION].[ACTIVATEDATE] <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
      ) [LATESTORGANIZATIONEXCHANGERATE]
      outer apply
      (
        select RATE
        from dbo.[CURRENCYEXCHANGERATE]
        where
          @CURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
          and @ORGANIZATIONCURRENCYID = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
          and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
          and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
          and [MKTSEGMENTATION].[ACTIVATEDATE] >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
          and [MKTSEGMENTATION].[ACTIVATEDATE] <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
      ) [LATESTINVERSEORGANIZATIONEXCHANGERATE]
      where 
        (@CURRENCYID is not null) 
        and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
        and @CURRENCYID <> [MKTSEGMENTATIONACTIVE].[BASECURRENCYID]
    )

    --Calculating MKTSegmentBuget fields in currency along with exposing CTE fields.

    select
      [MKTSEGMENTATION_CTE].[ID],
      [MKTSEGMENTATION_CTE].[ACTIVE],
      [MKTSEGMENTATION_CTE].[MKTSEGMENTATIONACTIVEID],
      [MKTSEGMENTATION_CTE].[TOTALGIFTAMOUNTINCURRENCY],
      [MKTSEGMENTATION_CTE].[TOTALCOSTINCURRENCY],
      [MKTSEGMENTATION_CTE].[QUANTITY],
      [MKTSEGMENTATION_CTE].[RESPONSERATE],
      [MKTSEGMENTATION_CTE].[RESPONDERS],
      [MKTSEGMENTATION_CTE].[RESPONSES],
      [MKTSEGMENTATIONBUDGET].[ID] as [MKTSEGMENTATIONBUDGETID],
      case 
        when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
          then [MKTSEGMENTATIONBUDGET].[ORGANIZATIONFIXEDCOST]
        else [MKTSEGMENTATIONBUDGET].[FIXEDCOST]
      end [FIXEDCOSTINCURRENCY]
    from [MKTSEGMENTATION_CTE]
    left join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATION_CTE].[ID] = [MKTSEGMENTATIONBUDGET].[ID]
    where 
      (@CURRENCYID is null)
      or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
      or @CURRENCYID = [MKTSEGMENTATIONBUDGET].[BASECURRENCYID]

    union all

    select
      [MKTSEGMENTATION_CTE].[ID],
      [MKTSEGMENTATION_CTE].[ACTIVE],
      [MKTSEGMENTATION_CTE].[MKTSEGMENTATIONACTIVEID],
      [MKTSEGMENTATION_CTE].[TOTALGIFTAMOUNTINCURRENCY],
      [MKTSEGMENTATION_CTE].[TOTALCOSTINCURRENCY],
      [MKTSEGMENTATION_CTE].[QUANTITY],
      [MKTSEGMENTATION_CTE].[RESPONSERATE],
      [MKTSEGMENTATION_CTE].[RESPONDERS],
      [MKTSEGMENTATION_CTE].[RESPONSES],
      [MKTSEGMENTATIONBUDGET].[ID] as [MKTSEGMENTATIONBUDGETID],
      case
        when [LATESTORGANIZATIONEXCHANGERATE].[RATE] is not null
          then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONBUDGET].[ORGANIZATIONFIXEDCOST], [LATESTORGANIZATIONEXCHANGERATE].[RATE]), @DECIMALDIGITS, @ROUNDINGTYPECODE)
        when [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE] is not null
          then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([MKTSEGMENTATIONBUDGET].[ORGANIZATIONFIXEDCOST], cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE]) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
        else 0
      end [FIXEDCOSTINCURRENCY]
    from [MKTSEGMENTATION_CTE]
    left join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATION_CTE].[ID] = [MKTSEGMENTATIONBUDGET].[ID]
    outer apply
      (
        select [RATE]
        from dbo.[CURRENCYEXCHANGERATE]
        where
          @ORGANIZATIONCURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
          and @CURRENCYID = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
          and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
          and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
          and [MKTSEGMENTATION_CTE].[ACTIVATEDATE] >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
          and [MKTSEGMENTATION_CTE].[ACTIVATEDATE] <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
      ) [LATESTORGANIZATIONEXCHANGERATE]
      outer apply
      (
          select [RATE]
          from dbo.[CURRENCYEXCHANGERATE]
          where
            @CURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID]
            and @ORGANIZATIONCURRENCYID = [CURRENCYEXCHANGERATE].[TOCURRENCYID]
            and [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1)
            and [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1
            and [MKTSEGMENTATION_CTE].[ACTIVATEDATE] >= [CURRENCYEXCHANGERATE].[ASOFDATESDTZ]
            and [MKTSEGMENTATION_CTE].[ACTIVATEDATE] <= [CURRENCYEXCHANGERATE].[NEXTRATEASOFDATEORMAXSDTZ]
      ) [LATESTINVERSEORGANIZATIONEXCHANGERATE]
      where (@CURRENCYID is not null) 
        and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
        and @CURRENCYID <> [MKTSEGMENTATIONBUDGET].[BASECURRENCYID]
  )