UFN_REVENUE_GETPROPERTYDETAILSPLITSBYTRANSACTION

            CREATE function dbo.UFN_REVENUE_GETPROPERTYDETAILSPLITSBYTRANSACTION
            (
                @TRANSACTIONID uniqueidentifier, 
                @PAYMENTMETHODCODE int
            )
            returns @SPLITS TABLE
            (
                REVENUESPLITID uniqueidentifier,
                AMOUNT money,
                TRANSACTIONAMOUNT money,
                REVENUEID uniqueidentifier,
                SPLITTRANSACTIONAMOUNT money,
                ORGANIZATIONAMOUNT money,
                TRANSACTIONCURRENCYID uniqueidentifier,
                BASECURRENCYID uniqueidentifier,
                BASEEXCHANGERATEID uniqueidentifier,
                ORGANIZATIONEXCHANGERATEID uniqueidentifier
            )
            as
                begin

                    declare @REVENUETRANSACTIONAMOUNT money;
                    declare @REVENUEAMOUNT money;
                    declare @REVENUEORGANIZATIONAMOUNT money;
                    declare @TRANSACTIONCURRENCYID uniqueidentifier;
                    declare @BASECURRENCYID uniqueidentifier;
                    declare @BASEEXCHANGERATEID uniqueidentifier;
                    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                    -- get Revenue values

                    select 
                        @REVENUETRANSACTIONAMOUNT = TRANSACTIONAMOUNT,
                        @REVENUEAMOUNT = AMOUNT,
                        @REVENUEORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT,
                        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
                        @BASECURRENCYID = BASECURRENCYID,
                        @BASEEXCHANGERATEID = BASEEXCHANGERATEID,
                        @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
                    from dbo.REVENUE with (nolock) 
                    where ID = @TRANSACTIONID;

                    declare @NUMBEROFSPLITS int
                    select @NUMBEROFSPLITS = count(ID)
                    from dbo.REVENUESPLIT
                    where REVENUEID = @TRANSACTIONID;

                    declare @TRANSACTIONAMOUNT_TO_DISTRIBUTE money;
                    declare @AMOUNT_TO_DISTRIBUTE money;
                    declare @ORGANIZATIONAMOUNT_TO_DISTRIBUTE money;
                    -- get the amount to be distributed

                    if @PAYMENTMETHODCODE = 201 or @PAYMENTMETHODCODE = 203
                    begin
                        declare @TRANSACTIONSALEAMOUNT money;
                        declare @SALEDATE datetime

                        select top 1 
                            @TRANSACTIONSALEAMOUNT = PROPERTYDETAIL.TRANSACTIONSALEAMOUNT,
                            @SALEDATE = PROPERTYDETAIL.SALEDATE
                        from dbo.PROPERTYDETAIL with (nolock)
                            inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                        where 
                            REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;                            

                        set @TRANSACTIONAMOUNT_TO_DISTRIBUTE = abs(@TRANSACTIONSALEAMOUNT - @REVENUETRANSACTIONAMOUNT)
                        select
                            @AMOUNT_TO_DISTRIBUTE = BASEAMOUNT,
                            @ORGANIZATIONAMOUNT_TO_DISTRIBUTE = ORGANIZATIONAMOUNT
                        from dbo.UFN_CURRENCY_GETCURRENCYVALUES(
                            @TRANSACTIONAMOUNT_TO_DISTRIBUTE,
                            @SALEDATE,
                            @BASECURRENCYID,
                            @BASEEXCHANGERATEID,
                            @TRANSACTIONCURRENCYID
                        )

                    end
                    else if @PAYMENTMETHODCODE = 204
                    begin
                        select top 1 
                            @TRANSACTIONAMOUNT_TO_DISTRIBUTE =PROPERTYDETAIL.TRANSACTIONBROKERFEE,
                            @AMOUNT_TO_DISTRIBUTE = PROPERTYDETAIL.BROKERFEE,
                            @ORGANIZATIONAMOUNT_TO_DISTRIBUTE = PROPERTYDETAIL.ORGANIZATIONBROKERFEE
                        from dbo.PROPERTYDETAIL with (nolock)
                            inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                        where 
                            REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;                    
                    end


                    declare @TRANSACTIONCURRENCYDECIMALDIGITS int
                    select @TRANSACTIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
                    from dbo.CURRENCY
                    where ID = @TRANSACTIONCURRENCYID

                    declare @BASECURRENCYDECIMALDIGITS int
                    select @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS
                    from dbo.CURRENCY
                    where ID = @BASECURRENCYID

                    declare @ORGANIZATIONCURRENCYDECIMALDIGITS int
                    select @ORGANIZATIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
                    from dbo.CURRENCY
                    where ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()


                    declare @REVENUESPLITID uniqueidentifier;
                    declare @SPLITTRANSACTIONAMOUNT money;
                    declare @SPLITAMOUNT money;
                    declare @SPLITORGANIZATIONAMOUNT money;

                    declare @DISTRIBUTEDTRANSACTIONAMOUNT money = 0;
                    declare @DISTRIBUTEDAMOUNT money = 0;
                    declare @DISTRIBUTEDORGANIZATIONAMOUNT money = 0;

                    declare @SPLITCOUNT int = 0
                    declare @TOTALDISTRIBUTEDTRANSACTIONAMOUNT money = 0;
                    declare @TOTALDISTRIBUTEDAMOUNT money = 0;
                    declare @TOTALDISTRIBUTEDORGANIZATIONAMOUNT money = 0;

                    -- get the Revenue splits

                    declare SPLITS_CURSOR cursor local fast_forward for
                        select 
                            SPLIT.ID as REVENUESPLITID,
                            SPLIT.AMOUNT,
                            SPLIT.TRANSACTIONAMOUNT,
                            SPLIT.ORGANIZATIONAMOUNT
                        from dbo.REVENUE with (nolock)
                            inner join dbo.REVENUESPLIT as SPLIT with (nolock) on REVENUE.ID = SPLIT.REVENUEID
                        where
                            REVENUE.ID = @TRANSACTIONID;

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

                    while @@FETCH_STATUS = 0 begin
                        set @SPLITCOUNT = @SPLITCOUNT + 1                        

                        if @SPLITCOUNT < @NUMBEROFSPLITS
                        begin
                            set @DISTRIBUTEDTRANSACTIONAMOUNT = dbo.UFN_CURRENCY_CONVERTBYPROPORTION(@SPLITTRANSACTIONAMOUNT, @REVENUETRANSACTIONAMOUNT, @TRANSACTIONAMOUNT_TO_DISTRIBUTE, @TRANSACTIONCURRENCYDECIMALDIGITS);
                            set @TOTALDISTRIBUTEDTRANSACTIONAMOUNT = @TOTALDISTRIBUTEDTRANSACTIONAMOUNT + @DISTRIBUTEDTRANSACTIONAMOUNT;

                            set @DISTRIBUTEDAMOUNT = dbo.UFN_CURRENCY_CONVERTBYPROPORTION(@SPLITAMOUNT, @REVENUEAMOUNT, @AMOUNT_TO_DISTRIBUTE, @BASECURRENCYDECIMALDIGITS);
                            set @TOTALDISTRIBUTEDAMOUNT = @TOTALDISTRIBUTEDAMOUNT + @DISTRIBUTEDAMOUNT;

                            set @DISTRIBUTEDORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERTBYPROPORTION(@SPLITORGANIZATIONAMOUNT, @REVENUEORGANIZATIONAMOUNT, @ORGANIZATIONAMOUNT_TO_DISTRIBUTE, @ORGANIZATIONCURRENCYDECIMALDIGITS);
                            set @TOTALDISTRIBUTEDORGANIZATIONAMOUNT = @TOTALDISTRIBUTEDORGANIZATIONAMOUNT + @DISTRIBUTEDORGANIZATIONAMOUNT;
                        end
                        else
                        begin
                            set @DISTRIBUTEDTRANSACTIONAMOUNT = @TRANSACTIONAMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTEDTRANSACTIONAMOUNT;
                            set @DISTRIBUTEDAMOUNT = @AMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTEDAMOUNT;
                            set @DISTRIBUTEDORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTEDORGANIZATIONAMOUNT;
                        end

                        insert into @SPLITS(
                            REVENUESPLITID, 
                            AMOUNT, 
                            TRANSACTIONAMOUNT, 
                            REVENUEID, 
                            SPLITTRANSACTIONAMOUNT, 
                            ORGANIZATIONAMOUNT, 
                            TRANSACTIONCURRENCYID, 
                            BASECURRENCYID, 
                            BASEEXCHANGERATEID, 
                            ORGANIZATIONEXCHANGERATEID
                        ) 
                        values(
                            @REVENUESPLITID, 
                            @DISTRIBUTEDAMOUNT, 
                            @REVENUEAMOUNT, 
                            @TRANSACTIONID, 
                            @DISTRIBUTEDTRANSACTIONAMOUNT, 
                            @DISTRIBUTEDORGANIZATIONAMOUNT, 
                            @TRANSACTIONCURRENCYID, 
                            @BASECURRENCYID, 
                            @BASEEXCHANGERATEID, 
                            @ORGANIZATIONEXCHANGERATEID
                        );

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

                    close SPLITS_CURSOR;
                    deallocate SPLITS_CURSOR;

                    return;
                end