USP_PLEDGE_PAYSINGLEINSTALLMENT

        CREATE procedure dbo.USP_PLEDGE_PAYSINGLEINSTALLMENT
        (
            @INSTALLMENTID uniqueidentifier, 
            @REVENUEID uniqueidentifier, 
            @AMOUNT money output,
            @CAID uniqueidentifier,
            @CHANGEDATE datetime
        )
        as
        declare @PAYAMOUNT money;
        declare @AMOUNTPAID money;
        declare @AMOUNTLEFT money;
        declare @BALANCE money;
        declare @PLEDGEID uniqueidentifier
        declare @INSTALLMENTSPLITID uniqueidentifier;
        declare @INSTALLMENTPAYMENTID uniqueidentifier;
        declare @PLEDGETYPECODE tinyint;
        declare @DESIGNATIONID uniqueidentifier;
        declare @REVENUESPLITID uniqueidentifier;

        set @AMOUNTPAID = 0;
        set @AMOUNTLEFT = @AMOUNT;

        declare INSTALLMENTCURSOR cursor local fast_forward for 
            select INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.ID, INSTALLMENTSPLIT.DESIGNATIONID, dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID), REVENUESPLIT.TYPECODE
            from dbo.INSTALLMENTSPLIT
            inner join dbo.INSTALLMENT
                on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
            inner join dbo.REVENUESPLIT
                on REVENUESPLIT.REVENUEID = INSTALLMENTSPLIT.PLEDGEID and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
            where dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) > 0
                  and INSTALLMENT.ID = @INSTALLMENTID;

        open INSTALLMENTCURSOR;
        fetch next from INSTALLMENTCURSOR into @PLEDGEID, @INSTALLMENTSPLITID, @DESIGNATIONID, @BALANCE, @PLEDGETYPECODE;

        while @@FETCH_STATUS = 0
        begin
            set @INSTALLMENTPAYMENTID = null;
            set @REVENUESPLITID = null;

            if @AMOUNTPAID = @AMOUNT
                break;

            /* determine payment amount */
            if @AMOUNTLEFT <= @BALANCE
                set @PAYAMOUNT = @AMOUNTLEFT;
            else
                set @PAYAMOUNT = @BALANCE;

            set @AMOUNTPAID = @AMOUNTPAID + @PAYAMOUNT;
            set @AMOUNTLEFT = @AMOUNTLEFT - @PAYAMOUNT;

            /* Create Split    */
            select @REVENUESPLITID = ID
            from REVENUESPLIT
            where REVENUEID = @REVENUEID and DESIGNATIONID = @DESIGNATIONID

            if @REVENUESPLITID is null 
            begin
                set @REVENUESPLITID = newid();
                insert into dbo.REVENUESPLIT(ID, REVENUEID, DESIGNATIONID, AMOUNT, APPLICATIONCODE, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values(@REVENUESPLITID, @REVENUEID, @DESIGNATIONID, @PAYAMOUNT, 2,@PLEDGETYPECODE, @CAID, @CAID, @CHANGEDATE, @CHANGEDATE);
            end
            else
                update dbo.INSTALLMENTSPLITPAYMENT
                set AMOUNT = AMOUNT + @PAYAMOUNT,
                    CHANGEDBYID = @CAID,
                    DATECHANGED = @CHANGEDATE
                where ID = @INSTALLMENTPAYMENTID;

            /* see if this payment is already on this installment and just update the balance */
            select @INSTALLMENTPAYMENTID = ID
            from INSTALLMENTSPLITPAYMENT
            where INSTALLMENTSPLITID = @INSTALLMENTSPLITID and PAYMENTID = @REVENUESPLITID

            if @INSTALLMENTPAYMENTID is null
            begin
                insert into dbo.INSTALLMENTSPLITPAYMENT(ID, PAYMENTID, PLEDGEID, INSTALLMENTSPLITID, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values(newid(), @REVENUESPLITID, @PLEDGEID, @INSTALLMENTSPLITID, @PAYAMOUNT, @CAID, @CAID, @CHANGEDATE, @CHANGEDATE);
            end
            else
                update dbo.INSTALLMENTSPLITPAYMENT
                set AMOUNT = AMOUNT + @PAYAMOUNT,
                    CHANGEDBYID = @CAID,
                    DATECHANGED = @CHANGEDATE
                where ID = @INSTALLMENTPAYMENTID;

            fetch next from INSTALLMENTCURSOR into @PLEDGEID, @INSTALLMENTSPLITID, @DESIGNATIONID, @BALANCE, @PLEDGETYPECODE;
        end

        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

        close INSTALLMENTCURSOR;
        deallocate INSTALLMENTCURSOR;

        set @AMOUNT = @AMOUNTPAID;