USP_PLEDGE_GENERATEINSTALLMENTSPLITS

            CREATE procedure dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS
            (
                @PLEDGEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as 
            begin
                set nocount on;                

                declare @PLEDGEAMOUNT decimal(30, 5);
                declare @INSTALLMENTAMOUNT decimal(30, 5);
                declare @INSTALLMENTID uniqueidentifier;
                declare @DESIGNATIONID uniqueidentifier;
                declare @DESIGNATIONSUM decimal(30, 5);
                declare @REVENUESPLITID uniqueidentifier;

                declare @WEIGHTAMOUNT decimal(30, 5);
                declare @IDEALAMOUNT decimal(30, 5);
                declare @DESIGNATIONAMOUNTDISTRIBUTEDIDEAL decimal(30, 5); 
                declare @DESIGNATIONAMOUNTDISTRIBUTED decimal(30, 5);
                declare @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL decimal(30, 5); 
                declare @INSTALLMENTAMOUNTDISTRIBUTED decimal(30, 5);
                declare @DESIGNATIONBASEAMOUNTDISTRIBUTED decimal(30, 5);
                declare @INSTALLMENTBASEAMOUNTDISTRIBUTED decimal(30, 5);
                declare @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED decimal(30, 5);
                declare @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED decimal(30, 5);


                declare @INSTALLMENTMAX integer;
                declare @INSTALLMENTSEQUENCE integer;


                set @DESIGNATIONAMOUNTDISTRIBUTEDIDEAL = 0;
                set @DESIGNATIONAMOUNTDISTRIBUTED = 0;
                set @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL = 0;
                set @INSTALLMENTAMOUNTDISTRIBUTED = 0;
                set @DESIGNATIONBASEAMOUNTDISTRIBUTED = 0;
                set @INSTALLMENTBASEAMOUNTDISTRIBUTED = 0;
                set @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED = 0;
                set @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED = 0;

                --Multicurrency - AdamBu 3/30/10 - Retrieve the info needed to convert values later.

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;

                declare @DESIGNATIONSUMBASEAMOUNT money;
                declare @INSTALLMENTBASEAMOUNT money;
                declare @DESIGNATIONSUMORGANIZATIONAMOUNT money;
                declare @INSTALLMENTORGANIZATIONAMOUNT money;

                declare @BASEWEIGHTAMOUNT money;
                declare @ORGANIZATIONWEIGHTAMOUNT money;

                declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint;
                declare @BASECURRENCYDECIMALDIGITS tinyint;
                declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;

                select
                    @BASECURRENCYID = REVENUE.BASECURRENCYID,
                    @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID,
                    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @PLEDGEAMOUNT = REVENUE.TRANSACTIONAMOUNT,
                    @TRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTIONCURRENCY.DECIMALDIGITS,
                    @BASECURRENCYDECIMALDIGITS = BASECURRENCY.DECIMALDIGITS
                from 
                    dbo.REVENUE
                    inner join dbo.CURRENCY as TRANSACTIONCURRENCY on REVENUE.TRANSACTIONCURRENCYID = TRANSACTIONCURRENCY.ID
                    inner join dbo.CURRENCY as BASECURRENCY on REVENUE.BASECURRENCYID = BASECURRENCY.ID
                where 
                    REVENUE.ID = @PLEDGEID

                select @ORGANIZATIONCURRENCYDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

                declare @ORGANIZATIONAMOUNTORIGINCODE tinyint = 0;
                select @ORGANIZATIONAMOUNTORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE,0) from dbo.MULTICURRENCYCONFIGURATION;


                declare @DESIGNATIONIDEALAMOUNTLIST table
                (
                    DESIGNATIONID uniqueidentifier,
                    IDEALAMOUNT money,
                    DISTRIBUTEDAMOUNT money,
                    DISTRIBUTEDBASEAMOUNT money,
                    DISTRIBUTEDORGANIZATIONAMOUNT money
                )                            

                declare @INSTALLMENTIDEALAMOUNTLIST table
                (
                    INSTALLMENTID uniqueidentifier,
                    IDEALAMOUNT money,
                    DISTRIBUTEDAMOUNT money,
                    DISTRIBUTEDBASEAMOUNT money,
                    DISTRIBUTEDORGANIZATIONAMOUNT money
                )                            

                insert into @DESIGNATIONIDEALAMOUNTLIST(DESIGNATIONID) 
                select DESIGNATIONID from REVENUESPLIT where REVENUEID = @PLEDGEID;

                insert into @INSTALLMENTIDEALAMOUNTLIST(INSTALLMENTID) 
                select ID from INSTALLMENT where REVENUEID = @PLEDGEID;

                select @INSTALLMENTMAX = max(SEQUENCE)
                from INSTALLMENT where INSTALLMENT.REVENUEID = @PLEDGEID;

                declare INSTALLMENTSPLITS cursor local fast_forward for 
                    select 
                        INSTALLMENT.ID, 
                        INSTALLMENT.SEQUENCE, 
                        REVENUESPLIT_EXT.DESIGNATIONID, 
                        INSTALLMENT.TRANSACTIONAMOUNT, 
                        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT TRANSACTIONAMOUNT,
                        INSTALLMENT.AMOUNT,
                        FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT AMOUNT,
                        INSTALLMENT.ORGANIZATIONAMOUNT,
                        FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT ORGANIZATIONAMOUNT,
                        FINANCIALTRANSACTIONLINEITEM.ID REVENUESPLITID
                    from 
                        dbo.INSTALLMENT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    where 
                        INSTALLMENT.REVENUEID =  @PLEDGEID
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
                    order by 
                        INSTALLMENT.SEQUENCE, FINANCIALTRANSACTIONLINEITEM.SEQUENCE, FINANCIALTRANSACTIONLINEITEM.TS

                open INSTALLMENTSPLITS;
                    fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM, @INSTALLMENTBASEAMOUNT, @DESIGNATIONSUMBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @DESIGNATIONSUMORGANIZATIONAMOUNT, @REVENUESPLITID;

                    while @@FETCH_STATUS = 0
                    begin

                        select @DESIGNATIONAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT, 0.00),
                                @DESIGNATIONAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDAMOUNT, 0.00),
                                @DESIGNATIONBASEAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDBASEAMOUNT, 0.00),
                                @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDORGANIZATIONAMOUNT, 0.00)
                        from @DESIGNATIONIDEALAMOUNTLIST
                        where DESIGNATIONID = @DESIGNATIONID;

                        select @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT, 0.00),
                                @INSTALLMENTAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDAMOUNT, 0.00),
                                @INSTALLMENTBASEAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDBASEAMOUNT, 0.00),
                                @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDORGANIZATIONAMOUNT, 0.00)
                        from @INSTALLMENTIDEALAMOUNTLIST
                        where INSTALLMENTID = @INSTALLMENTID;

                        if @PLEDGEAMOUNT <> 0
                            --Do multiplication first to reduce rounding errors

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

                        --On the last installment balance out the designations

                        if @INSTALLMENTSEQUENCE = @INSTALLMENTMAX 
                        begin
                            set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT 
                                                    + @DESIGNATIONAMOUNTDISTRIBUTEDIDEAL - @DESIGNATIONAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);

                            --Calculate the weighted base amount

                            if @TRANSACTIONCURRENCYID = @BASECURRENCYID 
                                set @BASEWEIGHTAMOUNT = @WEIGHTAMOUNT;
                            else
                                set @BASEWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @DESIGNATIONAMOUNTDISTRIBUTED), @DESIGNATIONSUM, @DESIGNATIONSUMBASEAMOUNT, @BASECURRENCYDECIMALDIGITS) - @DESIGNATIONBASEAMOUNTDISTRIBUTED);

                            --Calculate the weighted organization amount

                            if @ORGANIZATIONAMOUNTORIGINCODE = 0
                            begin
                                if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                    set @ORGANIZATIONWEIGHTAMOUNT = @BASEWEIGHTAMOUNT;
                                else
                                    set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@BASEWEIGHTAMOUNT + @DESIGNATIONBASEAMOUNTDISTRIBUTED), @DESIGNATIONSUMBASEAMOUNT, @DESIGNATIONSUMORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED);
                            end
                            else
                            begin
                                if @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
                                    set @ORGANIZATIONWEIGHTAMOUNT = @WEIGHTAMOUNT;
                                else
                                    set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @DESIGNATIONAMOUNTDISTRIBUTED), @DESIGNATIONSUM, @DESIGNATIONSUMORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED);
                            end    
                        end
                        else
                        begin
                            set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT 
                                                    + @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL - @INSTALLMENTAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);    

                            --Calculate the weighted base amount

                            if @TRANSACTIONCURRENCYID = @BASECURRENCYID 
                                set @BASEWEIGHTAMOUNT = @WEIGHTAMOUNT;
                            else
                                set @BASEWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @INSTALLMENTAMOUNTDISTRIBUTED), @INSTALLMENTAMOUNT, @INSTALLMENTBASEAMOUNT, @BASECURRENCYDECIMALDIGITS) - @INSTALLMENTBASEAMOUNTDISTRIBUTED);

                            --Calculate the weighted organization amount

                            if @ORGANIZATIONAMOUNTORIGINCODE = 0
                            begin
                                if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                    set @ORGANIZATIONWEIGHTAMOUNT = @BASEWEIGHTAMOUNT;
                                else
                                    set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@BASEWEIGHTAMOUNT + @INSTALLMENTBASEAMOUNTDISTRIBUTED), @INSTALLMENTBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED);
                            end
                            else
                            begin
                                if @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
                                    set @ORGANIZATIONWEIGHTAMOUNT = @WEIGHTAMOUNT;
                                else
                                    set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @INSTALLMENTAMOUNTDISTRIBUTED), @INSTALLMENTAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED);
                            end
                        end


                        insert into dbo.INSTALLMENTSPLIT(
                            INSTALLMENTID,
                            PLEDGEID,
                            DESIGNATIONID,
                            AMOUNT,
                            ADDEDBYID, 
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED,
                            BASECURRENCYID,
                            ORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID,
                            TRANSACTIONAMOUNT,
                            TRANSACTIONCURRENCYID,
                            BASEEXCHANGERATEID,
                            REVENUESPLITID)
                        values(
                            @INSTALLMENTID,
                            @PLEDGEID,
                            @DESIGNATIONID,
                            @BASEWEIGHTAMOUNT,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            @BASECURRENCYID,
                            @ORGANIZATIONWEIGHTAMOUNT,
                            @ORGANIZATIONEXCHANGERATEID,
                            @WEIGHTAMOUNT,
                            @TRANSACTIONCURRENCYID,
                            @BASEEXCHANGERATEID,
                            @REVENUESPLITID)

                        update @DESIGNATIONIDEALAMOUNTLIST
                        set IDEALAMOUNT = @DESIGNATIONAMOUNTDISTRIBUTEDIDEAL + @IDEALAMOUNT,
                            DISTRIBUTEDAMOUNT = @DESIGNATIONAMOUNTDISTRIBUTED + @WEIGHTAMOUNT,
                            DISTRIBUTEDBASEAMOUNT = @DESIGNATIONBASEAMOUNTDISTRIBUTED + @BASEWEIGHTAMOUNT,
                            DISTRIBUTEDORGANIZATIONAMOUNT = @DESIGNATIONORGANIZATIONAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTAMOUNT
                        where DESIGNATIONID = @DESIGNATIONID;

                        update @INSTALLMENTIDEALAMOUNTLIST
                        set IDEALAMOUNT = @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL + @IDEALAMOUNT,
                            DISTRIBUTEDAMOUNT = @INSTALLMENTAMOUNTDISTRIBUTED + @WEIGHTAMOUNT,
                            DISTRIBUTEDBASEAMOUNT = @INSTALLMENTBASEAMOUNTDISTRIBUTED + @BASEWEIGHTAMOUNT,
                            DISTRIBUTEDORGANIZATIONAMOUNT = @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTAMOUNT
                        where INSTALLMENTID = @INSTALLMENTID;

                        fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM, @INSTALLMENTBASEAMOUNT, @DESIGNATIONSUMBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @DESIGNATIONSUMORGANIZATIONAMOUNT, @REVENUESPLITID;
                    end

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

                    close INSTALLMENTSPLITS;
                    deallocate INSTALLMENTSPLITS;
            end