USP_INSTALLMENTS_DEFAULTRECEIPTAMOUNTS

            CREATE procedure dbo.USP_INSTALLMENTS_DEFAULTRECEIPTAMOUNTS
            (
                @PLEDGEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                -----

                -- NOTE:

                -- This function will redefault the receipt amounts of an existing pledge with respect to its

                -- amount, writeoffs, and associated benefits. This stored procedure looks exclusively in the

                -- database, meaning that it must be called after all other database modification is done.

                -- Results may be inaccurate if this procedure is run on out-dated information.

                -----


                declare @TEMP_INSTALLMENTS table
                (
                    ID uniqueidentifier,
                    SEQUENCE tinyint,
                    [DATE] datetime,
                    AMOUNT money,
                    WRITEOFFAMOUNT money,
                    RECEIPTAMOUNT money,
                    BENEFITAMOUNT as case when AMOUNT - WRITEOFFAMOUNT - RECEIPTAMOUNT > 0 then AMOUNT - WRITEOFFAMOUNT - RECEIPTAMOUNT else 0 end,
                    COMMITMENTAMOUNT as AMOUNT - WRITEOFFAMOUNT
                )

                declare @REMAININGCOMMITMENTAMOUNT money
                declare @REMAININGBENEFITAMOUNT money
                declare @REMAININGRECEIPTAMOUNT money
                declare @FIRSTUNPAIDINSTALLMENTSEQUENCE tinyint
                declare @CURRENTINSTALLMENTID uniqueidentifier
                declare @CURRENTINSTALLMENTCOMMITMENTAMOUNT money

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                -- Get installment details

                insert into @TEMP_INSTALLMENTS (ID, SEQUENCE, [DATE], AMOUNT, WRITEOFFAMOUNT, RECEIPTAMOUNT)
                select
                    INSTALLMENT.ID,
                    INSTALLMENT.SEQUENCE,
                    INSTALLMENT.DATE,
                    INSTALLMENT.TRANSACTIONAMOUNT,
                    coalesce(sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT), 0),
                    INSTALLMENT.TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT
                from dbo.INSTALLMENT
                    left join dbo.INSTALLMENTWRITEOFF on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
                where REVENUEID = @PLEDGEID
                group by SEQUENCE, INSTALLMENT.ID, INSTALLMENT.DATE, INSTALLMENT.TRANSACTIONAMOUNT, INSTALLMENT.TRANSACTIONRECEIPTAMOUNT

                -- Adjust installment receipt amounts for writeoffs

                update @TEMP_INSTALLMENTS
                set RECEIPTAMOUNT = COMMITMENTAMOUNT - BENEFITAMOUNT
                where WRITEOFFAMOUNT > 0

                -- Get cut-off point

                select @FIRSTUNPAIDINSTALLMENTSEQUENCE = SEQUENCE
                from @TEMP_INSTALLMENTS
                where ID = dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(@PLEDGEID,null)

                -- Subtract value of benefits already paid

                select
                    @REMAININGBENEFITAMOUNT =
                    (
                        select coalesce(sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT), 0)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUEBENEFIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUEBENEFIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID and REVENUEBENEFIT_EXT.BENEFITTYPECODE = 1
                    ) - coalesce(sum(BENEFITAMOUNT), 0)
                from @TEMP_INSTALLMENTS
                where SEQUENCE < @FIRSTUNPAIDINSTALLMENTSEQUENCE

                -- Set amounts

                set @REMAININGCOMMITMENTAMOUNT = (select sum(COMMITMENTAMOUNT) from @TEMP_INSTALLMENTS where SEQUENCE >= @FIRSTUNPAIDINSTALLMENTSEQUENCE)
                set @REMAININGRECEIPTAMOUNT = @REMAININGCOMMITMENTAMOUNT - @REMAININGBENEFITAMOUNT

                declare cur cursor fast_forward for
                select
                    ID,
                    COMMITMENTAMOUNT
                from @TEMP_INSTALLMENTS
                where SEQUENCE >= @FIRSTUNPAIDINSTALLMENTSEQUENCE
                order by SEQUENCE desc

                -- Default receipt amounts

                open cur
                fetch next from cur into @CURRENTINSTALLMENTID, @CURRENTINSTALLMENTCOMMITMENTAMOUNT
                while @@FETCH_STATUS = 0
                begin
                    if @REMAININGRECEIPTAMOUNT < @CURRENTINSTALLMENTCOMMITMENTAMOUNT
                    begin
                        update @TEMP_INSTALLMENTS
                        set RECEIPTAMOUNT = @REMAININGRECEIPTAMOUNT
                        where ID = @CURRENTINSTALLMENTID

                        set @REMAININGRECEIPTAMOUNT = 0
                    end
                    else
                    begin
                        update @TEMP_INSTALLMENTS
                        set RECEIPTAMOUNT = @CURRENTINSTALLMENTCOMMITMENTAMOUNT
                        where ID = @CURRENTINSTALLMENTID

                        set @REMAININGRECEIPTAMOUNT = @REMAININGRECEIPTAMOUNT - @CURRENTINSTALLMENTCOMMITMENTAMOUNT
                    end

                    if @REMAININGRECEIPTAMOUNT < 0
                        set @REMAININGRECEIPTAMOUNT = 0

                    fetch next from cur into @CURRENTINSTALLMENTID, @CURRENTINSTALLMENTCOMMITMENTAMOUNT
                end

                close cur
                deallocate cur

                -- Update data

                declare @INSTALLMENTS xml
                declare @CURRENTDATE datetime = getdate()
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;

                set @INSTALLMENTS = 
                    (
                    select
                        ID,
                        SEQUENCE,
                        [DATE],
                        AMOUNT,
                        RECEIPTAMOUNT
                    from @TEMP_INSTALLMENTS
                    for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
                    )

                select
                    @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                    @ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
                from dbo.FINANCIALTRANSACTION
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where
                    FINANCIALTRANSACTION.ID = @PLEDGEID and
                    FINANCIALTRANSACTION.DELETEDON is null

                set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID)
                exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_UPDATEFROMXML @PLEDGEID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;
end