UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGIFTINCURRENCY

create function dbo.UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGIFTINCURRENCY
(
  @OPPORTUNITYID uniqueidentifier,
  @TYPECODE tinyint,
  @APPLICATIONCODE tinyint,
  @CURRENCYID uniqueidentifier
)
returns xml as
begin

  declare @OPP_TRANSACTIONCURRENCYID uniqueidentifier;
  select top 1 @OPP_TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID from dbo.OPPORTUNITYDESIGNATION where OPPORTUNITYDESIGNATION.OPPORTUNITYID = @OPPORTUNITYID;

  declare @OUTSTANDINGDESIGNATIONS table
  (
    DESIGNATIONID uniqueidentifier,
    AMOUNT money
  );

  insert into @OUTSTANDINGDESIGNATIONS
  select
    OD.DESIGNATIONID,
    OD.TRANSACTIONAMOUNT - coalesce(
      (
        select
          sum
          (
            -- When the base currency of the designation does not match the transaction currency of the split,

            -- we need to convert the split amount to the designation currency in order to perform the subtraction

            -- on like currencies.

            case
              when RS.TRANSACTIONCURRENCYID = @OPP_TRANSACTIONCURRENCYID
                then RS.TRANSACTIONAMOUNT
              else
                dbo.UFN_CURRENCY_CONVERT(RS.TRANSACTIONAMOUNT,
                                         dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(RS.TRANSACTIONCURRENCYID, @OPP_TRANSACTIONCURRENCYID, REVENUE.DATE, 1, null))
            end
          )
        from
          dbo.REVENUEOPPORTUNITY RO
        inner join
          dbo.REVENUESPLIT RS on RS.ID = RO.ID
        inner join
          dbo.REVENUE on RS.REVENUEID = REVENUE.ID
        where
          RO.OPPORTUNITYID = OD.OPPORTUNITYID and
          RS.DESIGNATIONID = OD.DESIGNATIONID
      ), 0)
  from
    dbo.OPPORTUNITYDESIGNATION OD
  where
    OD.OPPORTUNITYID = @OPPORTUNITYID;

  delete from @OUTSTANDINGDESIGNATIONS where AMOUNT <= 0;

  declare @SUMMEDAMOUNT money;
  select @SUMMEDAMOUNT = sum(OD.TRANSACTIONAMOUNT) from dbo.OPPORTUNITYDESIGNATION OD where OD.OPPORTUNITYID = @OPPORTUNITYID;

  declare @TOTALOUTSTANDINGAMOUNT money;
  select @TOTALOUTSTANDINGAMOUNT = sum(AMOUNT) from @OUTSTANDINGDESIGNATIONS;

  declare @OPP_TRANSACTIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@OPP_TRANSACTIONCURRENCYID, @CURRENCYID, getdate(), 1, null);
  declare @SPLITS xml;

  if @SUMMEDAMOUNT >= @TOTALOUTSTANDINGAMOUNT
  begin
    set @SPLITS = 
    (
      select
        null [ID],
        DESIGNATIONID,
        case
          when @CURRENCYID = @OPP_TRANSACTIONCURRENCYID
            then AMOUNT
          else
            dbo.UFN_CURRENCY_CONVERT(AMOUNT, @OPP_TRANSACTIONEXCHANGERATEID)
        end as AMOUNT,
        @TYPECODE as TYPECODE,
        @APPLICATIONCODE as APPLICATIONCODE,
        @CURRENCYID TRANSACTIONCURRENCYID
      from 
        @OUTSTANDINGDESIGNATIONS
      group by 
        DESIGNATIONID, AMOUNT
      for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
    );
  end
  else
  begin
    set @SPLITS = 
    (
      select
        null [ID],
        DESIGNATIONID,
        case
          when @CURRENCYID = @OPP_TRANSACTIONCURRENCYID
            then ((AMOUNT / @TOTALOUTSTANDINGAMOUNT) * @SUMMEDAMOUNT)
          else
            dbo.UFN_CURRENCY_CONVERT(((AMOUNT / @TOTALOUTSTANDINGAMOUNT) * @SUMMEDAMOUNT), @OPP_TRANSACTIONEXCHANGERATEID)
        end as AMOUNT,
        @TYPECODE as TYPECODE,
        @APPLICATIONCODE as APPLICATIONCODE,
        @CURRENCYID TRANSACTIONCURRENCYID
      from
        @OUTSTANDINGDESIGNATIONS
      group by
        DESIGNATIONID, AMOUNT
      for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
    );
  end

  return @SPLITS;
end