USP_MKTSEGMENTATIONREFRESH_MULTICURRENCY

create procedure dbo.[USP_MKTSEGMENTATIONREFRESH_MULTICURRENCY]
(
  @ORGANIZATIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @DATATABLENAME nvarchar(128)
)
with execute as owner
as
begin
  set nocount on;
  declare @SQL nvarchar(max) = '';

  if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
  begin
    --1. If the segment's currency is the organization currency, use the organization amount.

    set @SQL = 'set nocount on;
    update dbo.[' + @DATATABLENAME + '] set
      [AMOUNT] = [SUMS].[ORGAMOUNT],
      [ORGANIZATIONAMOUNT] = [SUMS].[ORGAMOUNT]
    from dbo.[' + @DATATABLENAME + '] as [T]
    inner join (
    select
      sum([FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT]) [ORGAMOUNT],
      [DATATABLE].[ID]
    from dbo.[' + @DATATABLENAME + '] [DATATABLE]
      inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [DATATABLE].[ID]
      inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
    where [FINANCIALTRANSACTION].[DELETEDON] is null
      and [FINANCIALTRANSACTIONLINEITEM].[DELETEDON] is null
      and [FINANCIALTRANSACTION].[CONSTITUENTID] is not null
      and (
      ([FINANCIALTRANSACTION].[TYPECODE] in (0, 5) and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5))
      or
      ([FINANCIALTRANSACTION].[TYPECODE] in (1, 3) and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0)
      or
      ([FINANCIALTRANSACTION].[TYPECODE] = 6 and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)
      )
    group by
      [DATATABLE].[ID]
    ) [SUMS] on [SUMS].[ID] = [T].[ID];'
    exec sp_executesql @SQL;
  end
  else
  begin
    --2. If the segment's currency is the base currency of the gift, use the base currency.

    --using AMOUNT = 0 to signify that a conversion needs to be done: UFN_CURRENCYEXCHANGERATE_GETLATEST returns null 

    --if no applicable rate is found, UFN_CURRENCY_CONVERT returns 0 if passed a null rate ID, so 0 means that

    --UFN_CURRENCYEXCHANGERATE_GETLATEST returned null -- otherwise, I'd need to put an additional call to

    --UFN_CURRENCYEXCHANGERATE_GETLATEST in the where clause to tell which amounts still needed to be converted


    set @SQL = 'set nocount on;
    update dbo.[' + @DATATABLENAME + '] set
      [AMOUNT] = (case when isnull([REVENUE_EXT].[NONPOSTABLEBASECURRENCYID], [V].[BASECURRENCYID]) = @BASECURRENCYID then [T].[AMOUNT] else 0 end),
      [ORGANIZATIONAMOUNT] = [SUMS].[ORGAMOUNT]
    from dbo.[' + @DATATABLENAME + '] as [T]
    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [T].[ID]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
    inner join dbo.[V_BASECURRENCYFORFINANCIALTRANSACTION_I] as [V] with (noexpand) on [FINANCIALTRANSACTION].[ID] = [V].[FINANCIALTRANSACTIONID]
    inner join (
    select
      sum([FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT]) [ORGAMOUNT],
      [DATATABLE].[ID]
    from dbo.[' + @DATATABLENAME + '] [DATATABLE]
      inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [DATATABLE].[ID]
      inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
    where [FINANCIALTRANSACTION].[DELETEDON] is null
      and [FINANCIALTRANSACTIONLINEITEM].[DELETEDON] is null
      and [FINANCIALTRANSACTION].[CONSTITUENTID] is not null
      and (
      ([FINANCIALTRANSACTION].[TYPECODE] in (0, 5) and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5))
     or
      ([FINANCIALTRANSACTION].[TYPECODE] in (1, 3) and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0)
      or
      ([FINANCIALTRANSACTION].[TYPECODE] = 6 and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)
      )
    group by
      [DATATABLE].[ID]
    ) [SUMS] on [SUMS].[ID] = [V].[FINANCIALTRANSACTIONID];';
    exec sp_executesql @SQL, N'@ORGANIZATIONCURRENCYID uniqueidentifier, @BASECURRENCYID uniqueidentifier', @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID, @BASECURRENCYID = @BASECURRENCYID;

  --3. Otherwise, look for a corporate exchange rate from the organization currency to the segment's currency and apply this rate to the organization amount.

    set @SQL = 'set nocount on;
    update dbo.[' + @DATATABLENAME + '] set
      [AMOUNT] = dbo.[UFN_CURRENCY_CONVERT]([SUMS].[ORGAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@ORGANIZATIONCURRENCYID, @BASECURRENCYID, [FINANCIALTRANSACTION].[DATEADDED], 0, null))
    from dbo.[' + @DATATABLENAME + '] as [T]
    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [T].[ID]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
    inner join dbo.[V_BASECURRENCYFORFINANCIALTRANSACTION_I] as [V] with (noexpand) on [FINANCIALTRANSACTION].[ID] = [V].[FINANCIALTRANSACTIONID]
    inner join (
    select
      sum([FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT]) [ORGAMOUNT],
      [DATATABLE].[ID]
    from dbo.[' + @DATATABLENAME + '] [DATATABLE]
      inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [DATATABLE].[ID]
      inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
    where [FINANCIALTRANSACTION].[DELETEDON] is null
      and [FINANCIALTRANSACTIONLINEITEM].[DELETEDON] is null
      and [FINANCIALTRANSACTION].[CONSTITUENTID] is not null
      and (
      ([FINANCIALTRANSACTION].[TYPECODE] in (0, 5) and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5))
      or
      ([FINANCIALTRANSACTION].[TYPECODE] in (1, 3) and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0)
      or
      ([FINANCIALTRANSACTION].[TYPECODE] = 6 and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)
      )
    group by
      [DATATABLE].[ID]
    ) [SUMS] on [SUMS].[ID] = [V].[FINANCIALTRANSACTIONID]
    where isnull([REVENUE_EXT].[NONPOSTABLEBASECURRENCYID], [V].[BASECURRENCYID]) <> @BASECURRENCYID
    and [T].[AMOUNT] = 0;';
    exec sp_executesql @SQL, N'@ORGANIZATIONCURRENCYID uniqueidentifier, @BASECURRENCYID uniqueidentifier', @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID, @BASECURRENCYID = @BASECURRENCYID;

    --4. If this rate does not exist, look for a corporate exchange rate from the segment's currency to the organization currency and apply the _inverse_ of this rate to the organization amount.

    set @SQL = 'set nocount on;
    update dbo.[' + @DATATABLENAME + '] set
      [AMOUNT] = dbo.[UFN_CURRENCY_CONVERTINVERSE]([SUMS].[ORGAMOUNT], dbo.[UFN_CURRENCYEXCHANGERATE_GETLATEST](@BASECURRENCYID, @ORGANIZATIONCURRENCYID, [FINANCIALTRANSACTION].[DATEADDED], 0, null))
    from dbo.[' + @DATATABLENAME + '] as [T]
    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [T].[ID]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
    inner join dbo.[V_BASECURRENCYFORFINANCIALTRANSACTION_I] as [V] with (noexpand) on [FINANCIALTRANSACTION].[ID] = [V].[FINANCIALTRANSACTIONID]
    inner join (
    select
      sum([FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT]) [ORGAMOUNT],
      [DATATABLE].[ID]
    from dbo.[' + @DATATABLENAME + '] [DATATABLE]
      inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [DATATABLE].[ID]
      inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
      inner join dbo.[REVENUESPLIT_EXT] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [REVENUESPLIT_EXT].[ID]
    where [FINANCIALTRANSACTION].[DELETEDON] is null
      and [FINANCIALTRANSACTIONLINEITEM].[DELETEDON] is null
      and [FINANCIALTRANSACTION].[CONSTITUENTID] is not null
      and (
      ([FINANCIALTRANSACTION].[TYPECODE] in (0, 5) and [REVENUESPLIT_EXT].[APPLICATIONCODE] in (0, 1, 3, 5))
      or
      ([FINANCIALTRANSACTION].[TYPECODE] in (1, 3) and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 0)
      or
      ([FINANCIALTRANSACTION].[TYPECODE] = 6 and [REVENUESPLIT_EXT].[APPLICATIONCODE] = 8)
      )
    group by
      [DATATABLE].[ID]
    ) [SUMS] on [SUMS].[ID] = [V].[FINANCIALTRANSACTIONID]
    where isnull([REVENUE_EXT].[NONPOSTABLEBASECURRENCYID], [V].[BASECURRENCYID]) <> @BASECURRENCYID
    and [T].[AMOUNT] = 0;';
    exec sp_executesql @SQL, N'@ORGANIZATIONCURRENCYID uniqueidentifier, @BASECURRENCYID uniqueidentifier', @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID, @BASECURRENCYID = @BASECURRENCYID;

  end
end
return 0;