UFN_REVENUEUPDATEBATCH_BUILDINSTALLMENTSPLITS

CREATE function dbo.UFN_REVENUEUPDATEBATCH_BUILDINSTALLMENTSPLITS
(
    @REVENUEID uniqueidentifier,
    @AMOUNT money,
    @SPLITS xml, 
    @MAXINSTALLMENTSEQUENCE int,
    @INSTALLMENTS xml, 
    @TRANSACTIONCURRENCYID uniqueidentifier, 
    @NUMBEROFINSTALLMENTS int
)
returns xml
as begin
    declare @PLEDGEAMOUNT decimal(30, 5);
    declare @INSTALLMENTAMOUNT decimal(30, 5);
    declare @INSTALLMENTID uniqueidentifier;
    declare @DESIGNATIONID uniqueidentifier;
    declare @DESIGNATIONSUM decimal(30, 5);
    declare @IDEALAMOUNT decimal(30, 5);
    declare @INSTALLMENTSEQUENCE integer;
    declare @INSTALLMENTSPLITSRETURN xml;    
    declare @INSTALLMENTSPLIT table
    (
        ID uniqueidentifier,
        INSTALLMENTID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        AMOUNT money,
        APPLIED money,
        TRANSACTIONCURRENCYID uniqueidentifier
    );

    -- load existing installment splits

    insert into @INSTALLMENTSPLIT (ID, INSTALLMENTID, DESIGNATIONID, AMOUNT, APPLIED, TRANSACTIONCURRENCYID)
        select  
            ID,
            INSTALLMENTID,
            DESIGNATIONID, 
            INSTALLMENTSPLIT.TRANSACTIONAMOUNT as AMOUNT,
            dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITAMOUNTAPPLIED(INSTALLMENTSPLIT.ID) APPLIED,
            INSTALLMENTSPLIT.TRANSACTIONCURRENCYID
         from dbo.INSTALLMENTSPLIT
        where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID;

    -- remove all installment splits that do not have money applied

    delete from @INSTALLMENTSPLIT
    where APPLIED = 0;

    declare @INSTALLMENTCOUNT integer;
    select @INSTALLMENTCOUNT  = count(*) from @INSTALLMENTSPLIT;

    -- only add installment splits if there is money left over

    if @AMOUNT > 0 and @NUMBEROFINSTALLMENTS <> @INSTALLMENTCOUNT
    begin
        --

        -- handle installment splits

        --

        set @PLEDGEAMOUNT = @AMOUNT;

        declare INSTALLMENTSPLITS cursor local STATIC FOR
            select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                REVENUESPLIT.DESIGNATIONID, 
                T.c.value('(AMOUNT)[1]','money') AS 'INSTALLMENTAMOUNT',
                REVENUESPLIT.AMOUNT as SPLITAMOUNT
            from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
           cross join dbo.UFN_REVENUEBATCH_GETSPLITS_FROMITEMLISTXML(@SPLITS) REVENUESPLIT


        open INSTALLMENTSPLITS;
        fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM;

        while @@FETCH_STATUS = 0
        begin
            if @PLEDGEAMOUNT <> 0
                --Do multiplication first to reduce rounding errors

                set @IDEALAMOUNT = round((@INSTALLMENTAMOUNT  * @DESIGNATIONSUM)/ @PLEDGEAMOUNT, 2);
            else
                set @IDEALAMOUNT = 0;

            --On the last installment balance out the designations

            if @INSTALLMENTSEQUENCE = @MAXINSTALLMENTSEQUENCE 
            begin
                set @IDEALAMOUNT = @DESIGNATIONSUM - (@IDEALAMOUNT * (@NUMBEROFINSTALLMENTS - 1));
            end

            insert into @INSTALLMENTSPLIT (INSTALLMENTID, DESIGNATIONID, AMOUNT, APPLIED, TRANSACTIONCURRENCYID)
            values (@INSTALLMENTID, @DESIGNATIONID, @IDEALAMOUNT, 0, @TRANSACTIONCURRENCYID);

            fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM;
        end

        close INSTALLMENTSPLITS;
        deallocate INSTALLMENTSPLITS;

        -- update any remaining installment splits that have applied amount

        update @INSTALLMENTSPLIT
            set AMOUNT = APPLIED
         where APPLIED <> 0;

    end; -- amount > 0


    set @INSTALLMENTSPLITSRETURN = (
        select ID, 
            INSTALLMENTID, 
            DESIGNATIONID, 
            AMOUNT, 
            APPLIED, 
            TRANSACTIONCURRENCYID
        from  @INSTALLMENTSPLIT
        for xml raw('ITEM'),type,elements,BINARY BASE64);

    return @INSTALLMENTSPLITSRETURN;
end