USP_REVENUE_UPDATESPLITAMOUNTS

Updates split amounts to match the amount of a revenue detail while maintaining the correct percentages.
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@ORIGINALAMOUNT	money	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_REVENUE_UPDATESPLITAMOUNTS
            (
                @REVENUEID uniqueidentifier,
                @ORIGINALAMOUNT money,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as 
                set nocount on;

                declare @WEIGHTSUM decimal(30, 5);
                declare @IDEALAMOUNT decimal(30, 5);
                declare @AMOUNTDISTRIBUTEDIDEAL decimal(30, 5); 
                declare @AMOUNTDISTRIBUTED decimal(30, 5);
                declare @WEIGHTAMOUNT decimal(30, 5);
                declare @SPLITID uniqueidentifier;

                set @AMOUNTDISTRIBUTEDIDEAL = 0;
                set @AMOUNTDISTRIBUTED = 0;

                select @WEIGHTSUM = AMOUNT from dbo.REVENUE where ID = @REVENUEID;

                declare SPLITSCURSOR cursor local fast_forward 
                for 
                    select ID, AMOUNT from dbo.REVENUESPLIT where REVENUEID = @REVENUEID;

                open SPLITSCURSOR;

                fetch next from SPLITSCURSOR into @SPLITID, @WEIGHTAMOUNT;

                while @@FETCH_STATUS = 0
                    begin
                        if @ORIGINALAMOUNT <> 0
                            set @IDEALAMOUNT = (@WEIGHTAMOUNT / @ORIGINALAMOUNT) * @WEIGHTSUM;
                        else
                            set @IDEALAMOUNT = 0;

                        set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT + @AMOUNTDISTRIBUTEDIDEAL - @AMOUNTDISTRIBUTED, 2);

                        update 
                            dbo.REVENUESPLIT 
                        set 
                            AMOUNT = @WEIGHTAMOUNT,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where 
                            ID = @SPLITID;

                        set @AMOUNTDISTRIBUTEDIDEAL = @AMOUNTDISTRIBUTEDIDEAL + @IDEALAMOUNT;
                        set @AMOUNTDISTRIBUTED = @AMOUNTDISTRIBUTED + @WEIGHTAMOUNT;

                        fetch next from SPLITSCURSOR into @SPLITID, @WEIGHTAMOUNT;
                    end

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

                close SPLITSCURSOR;
                deallocate SPLITSCURSOR;