UFN_REVENUE_GETSTOCKDETAILSPLITS

            create function dbo.UFN_REVENUE_GETSTOCKDETAILSPLITS(@STOCKDETAILID uniqueidentifier, @PAYMENTMETHODCODE int)
                returns @SPLITS TABLE
                (
                    REVENUESPLITID uniqueidentifier,
                    AMOUNT    money
                )
            as
                begin
                    declare @REVENUESPLITID uniqueidentifier;
                    declare @SPLITAMOUNT decimal(20, 2);                    
                    declare @AMOUNT_TO_DISTRIBUTE decimal(20, 2);
                    declare @REVENUEAMOUNT decimal(20, 2);

                    declare @AMOUNTIDEAL decimal(20, 4);
                    declare @AMOUNT decimal(20, 2);
                    declare @AMOUNTDISTRIBUTED decimal(20, 4);
                    declare @AMOUNTDISTRIBUTEDIDEAL decimal(20, 4);

                    set @AMOUNTIDEAL = 0;
                    set @AMOUNT = 0;
                    set @AMOUNTDISTRIBUTED = 0;
                    set @AMOUNTDISTRIBUTEDIDEAL = 0;        

                    -- get Revenue amount

                    select @REVENUEAMOUNT = AMOUNT from dbo.REVENUE where ID = @STOCKDETAILID;

                    -- get the amount to be distributed

                    select @AMOUNT_TO_DISTRIBUTE = 
                        case  
                            when @PAYMENTMETHODCODE = 20 or @PAYMENTMETHODCODE = 22 then ABS(SD.SALEAMOUNT - @REVENUEAMOUNT) -- distribute gain/loss

                            when @PAYMENTMETHODCODE = 24 then SD.BROKERFEE                                                     -- distribute broker fee

                        end 
                    from dbo.STOCKDETAIL as SD where SD.ID = @STOCKDETAILID;

                    -- get the Revenue splits

                    declare SPLITS_CURSOR cursor local fast_forward for
                        select 
                            RDS.ID as REVENUESPLITID,
                            RDS.AMOUNT
                        from dbo.REVENUE as RD                        
                        inner join dbo.REVENUESPLIT as RDS on RD.ID = RDS.REVENUEID
                        where
                            RD.ID = @STOCKDETAILID;

                    open SPLITS_CURSOR;
                    fetch next from SPLITS_CURSOR into @REVENUESPLITID, @SPLITAMOUNT;

                    while @@FETCH_STATUS = 0 begin
                        if @REVENUEAMOUNT = 0
                            set @AMOUNTIDEAL = 0;
                        else
                            set @AMOUNTIDEAL =     @AMOUNT_TO_DISTRIBUTE * (@SPLITAMOUNT / @REVENUEAMOUNT);

                        set @AMOUNT = ROUND(@AMOUNTIDEAL + @AMOUNTDISTRIBUTEDIDEAL - @AMOUNTDISTRIBUTED, 2);
                        set @AMOUNTDISTRIBUTEDIDEAL = @AMOUNTDISTRIBUTEDIDEAL + @AMOUNTIDEAL;
                        set @AMOUNTDISTRIBUTED = @AMOUNTDISTRIBUTED + @AMOUNT;

                        insert into @SPLITS (REVENUESPLITID, AMOUNT) values (@REVENUESPLITID, @AMOUNT);

                        fetch next from SPLITS_CURSOR into @REVENUESPLITID, @SPLITAMOUNT;
                    end

                    close SPLITS_CURSOR;
                    deallocate SPLITS_CURSOR;

                    return;
                end