UFN_REVENUE_GETPROPERTYDETAILSPLITS

            CREATE function dbo.UFN_REVENUE_GETPROPERTYDETAILSPLITS(@PROPERTYDETAILID 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 = sum(amount) from dbo.REVENUE                        
                        where REVENUE.ID = @PROPERTYDETAILID;

                    -- get the amount to be distributed

                    select @AMOUNT_TO_DISTRIBUTE = 
                        case  
                            when @PAYMENTMETHODCODE = 21 or @PAYMENTMETHODCODE = 23 then ABS(PD.SALEAMOUNT - @REVENUEAMOUNT) -- distribute gain/loss

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

                        end 
                    from dbo.PROPERTYDETAIL as PD where PD.ID = @PROPERTYDETAILID;

                    -- 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 = @PROPERTYDETAILID;

                    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