UFN_SPLITS_PRORATEAMOUNTS

CREATE function dbo.UFN_SPLITS_PRORATEAMOUNTS(
    @ORIGINALAMOUNT money,
    @NEWAMOUNT money,
    @DECIMALDIGITSFORCURRENCY int,
    @AMOUNTSTOPRORATE xml
) 
returns @PRORATEDAMOUNTS table
(
    ID uniqueidentifier,
    AMOUNT money
)
as 
BEGIN

    ------

    -- This function will pro-rate the splits from an original total to a new total while taking into 

    -- account the number of decimal digits in the currency. 

    -- Parameters:

    -- @ORIGINALAMOUNT : the original total.  It is the caller's responsibility to make sure this is 

    -- equal to the SUM of the AMOUNT fields in the @AMOUNTSTOPRORATE xml parameter

    -- @NEWAMOUNT : the new total among which the splits will be distributed.  This function has the responsibility

    -- of ensuring that the SUM of the AMOUNT fields in the returned table is equal to the @NEWAMOUNT parameter.

    -- @DECIMALDIGITSFORCURRENCY : the number of decimal digits in the currency.  Should be in the range 0 to 4.

    -- @AMOUNTSTOPRORATE : represents the splits for the original total amount, and should be in the following format:

    -- <AMOUNTSTOPRORATE>

    --  <ITEM>

    --   <AMOUNT>100</AMOUNT>

    --   <ID>A Guid</ID>

    --  </ITEM>

    --  <!-- other ITEMS in the split -->

    -- </AMOUNTSTOPRORATE>

    ------


    -- adjust splits for new amount

    declare @IDEALAMOUNT decimal(30, 5);
    declare @ORIGINALAMOUNTDISTRIBUTEDIDEAL decimal(30, 5); 
    declare @ORIGINALAMOUNTDISTRIBUTED decimal(30, 5);
    declare @WEIGHTAMOUNT decimal(30, 10);
    declare @ID uniqueidentifier;

    set @ORIGINALAMOUNTDISTRIBUTEDIDEAL = 0;
    set @ORIGINALAMOUNTDISTRIBUTED = 0;

    -- Loop through and calculate new split amounts

    declare AMOUNTSTOPRORATECURSOR cursor local fast_forward for 
        select
            T.c.value('ID[1]', 'uniqueidentifier'),
            T.c.value('AMOUNT[1]', 'money')
        from
            @AMOUNTSTOPRORATE.nodes('/AMOUNTSTOPRORATE/ITEM') T(c) 
            where 
            T.c.value('ID[1]', 'CHAR(36)') <> ''
            and isnumeric(T.c.value('AMOUNT[1]', 'money')) = 1;


    open AMOUNTSTOPRORATECURSOR;

    fetch next from AMOUNTSTOPRORATECURSOR into @ID, @WEIGHTAMOUNT;

    while @@FETCH_STATUS = 0
        begin
            if @ORIGINALAMOUNT <> 0
                set @IDEALAMOUNT = (@WEIGHTAMOUNT / @ORIGINALAMOUNT) * @NEWAMOUNT;
            else
                set @IDEALAMOUNT = 0;

            set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT + @ORIGINALAMOUNTDISTRIBUTEDIDEAL - @ORIGINALAMOUNTDISTRIBUTED, @DECIMALDIGITSFORCURRENCY);

            insert into @PRORATEDAMOUNTS (ID, AMOUNT)
            values (@ID, @WEIGHTAMOUNT);

            set @ORIGINALAMOUNTDISTRIBUTEDIDEAL = @ORIGINALAMOUNTDISTRIBUTEDIDEAL + @IDEALAMOUNT;
            set @ORIGINALAMOUNTDISTRIBUTED = @ORIGINALAMOUNTDISTRIBUTED + @WEIGHTAMOUNT;

            fetch next from AMOUNTSTOPRORATECURSOR into @ID, @WEIGHTAMOUNT;
        end

    close AMOUNTSTOPRORATECURSOR;
    deallocate AMOUNTSTOPRORATECURSOR;

    return

END