UFN_SPLITS_GETPRORATEDSPLITS

CREATE function dbo.UFN_SPLITS_GETPRORATEDSPLITS
(
    @ORIGINALAMOUNT money,
    @NEWAMOUNT money,
    @AMOUNTSTOPRORATE xml
)
returns @PRORATEDAMOUNTS table
(
    ID uniqueidentifier,
    AMOUNT money,
    CREDITITEMID uniqueidentifier
)
as begin
    -- adjust splits for new amount

    declare @CURRENTAMOUNT decimal(30, 5);
    declare @WEIGHT decimal(30, 10);
    declare @ID uniqueidentifier;

    -- Load return table with current amounts to prorate

    insert into @PRORATEDAMOUNTS(AMOUNT, ID, CREDITITEMID)
    select
        T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(CREDITITEMID)[1]','uniqueidentifier') AS 'CREDITITEMID'
    from @AMOUNTSTOPRORATE.nodes('/AMOUNTSTOPRORATE/ITEM') T(c)

    -- Loop through and calculate new split amounts

    declare AMOUNTSTOPRORATECURSOR cursor local fast_forward 
    for 
        select isnull(CREDITITEMID, ID), AMOUNT from @PRORATEDAMOUNTS;

    open AMOUNTSTOPRORATECURSOR;

    fetch next from AMOUNTSTOPRORATECURSOR into @ID, @WEIGHT;

    while @@FETCH_STATUS = 0 begin
        if @ORIGINALAMOUNT <> 0
            set @CURRENTAMOUNT = round((@WEIGHT / @ORIGINALAMOUNT) * @NEWAMOUNT, 2);
        else
            set @CURRENTAMOUNT = 0;

        update
            @PRORATEDAMOUNTS 
        set
            AMOUNT = @CURRENTAMOUNT
        where
            CREDITITEMID = @ID
            or ID=@ID;

        set @NEWAMOUNT = @NEWAMOUNT - @CURRENTAMOUNT;
        set @ORIGINALAMOUNT = @ORIGINALAMOUNT - @WEIGHT;

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

    close AMOUNTSTOPRORATECURSOR;
    deallocate AMOUNTSTOPRORATECURSOR;

    return
end