UFN_REVENUE_GETSPLITBYROWNUMBER

            CREATE function dbo.UFN_REVENUE_GETSPLITBYROWNUMBER
            (
                @REVENUEID uniqueidentifier,
                @ROWTORETURN int
            )
            returns table
            as
            return
                select
                    DESIGNATION.ID as DESIGNATIONID,
                    DESIGNATIONLEVEL.NAME as DESIGNATIONNAME,
                    case len(DESIGNATION.VANITYNAME)
                        when 0 then DESIGNATIONLEVEL.NAME
                        else DESIGNATION.VANITYNAME 
                    end as DESIGNATIONPUBLICNAME,
                    DESIGNATION.USERID as DESIGNATIONLOOKUPID,
                    SPLITS.TRANSACTIONAMOUNT as AMOUNT,
                    SPLITS.APPLICATION,
                    DESIGNATIONADMINISTRATOR.NAME as DESIGNATIONADMINISTRATOR,
                    SPLITS.ID,
                    MEMBERSHIPPROGRAM.NAME as MEMBERSHIPPROGRAM,
                    MEMBERSHIPLEVEL.NAME as MEMBERSHIPLEVEL,
                    MEMBERSHIPTRANSACTION.EXPIRATIONDATE as MEMBERSHIPEXPIRATIONDATE,
                    PLEDGECONSTITUENT.NAME as PLEDGECONSTITUENT,
                    PLEDGE.TRANSACTIONAMOUNT as PLEDGEAMOUNT,
                    cast(PLEDGE.DATE as datetime) as PLEDGEDATE,
                    case 
                        when PLEDGE.ID is not null then dbo.UFN_PLEDGE_GETBALANCE(PLEDGE.ID) 
                        else null
                    end as PLEDGEBALANCE,
                    PLEDGE.TRANSACTIONCURRENCYID as PLEDGETRANSACTIONCURRENCYID
                from
                (
                    select 
                        row_number() over (order by SPLIT.BASEAMOUNT desc) as ROWNUMBER,
                        SPLIT.BASEAMOUNT as AMOUNT,
                        SPLIT_EXT.APPLICATION,
                        SPLIT.ID,
                        SPLIT_EXT.DESIGNATIONID,
                        SPLIT.TRANSACTIONAMOUNT
                    from dbo.FINANCIALTRANSACTIONLINEITEM SPLIT
                    inner join dbo.REVENUESPLIT_EXT SPLIT_EXT on SPLIT.ID = SPLIT_EXT.ID
                    where
                        SPLIT.FINANCIALTRANSACTIONID = @REVENUEID
                        and SPLIT.TYPECODE <> 1
                        and SPLIT.DELETEDON is null
                ) as SPLITS

                left join dbo.DESIGNATION on SPLITS.DESIGNATIONID = DESIGNATION.ID
                left join dbo.DESIGNATIONLEVEL on coalesce(DESIGNATION.DESIGNATIONLEVEL5ID,DESIGNATION.DESIGNATIONLEVEL4ID,DESIGNATION.DESIGNATIONLEVEL3ID,DESIGNATION.DESIGNATIONLEVEL2ID,DESIGNATION.DESIGNATIONLEVEL1ID) = DESIGNATIONLEVEL.ID
                left join dbo.CONSTITUENT DESIGNATIONADMINISTRATOR on DESIGNATIONLEVEL.ADMINISTRATORID = DESIGNATIONADMINISTRATOR.ID

                left join dbo.MEMBERSHIPTRANSACTION on SPLITS.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                left join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                left join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
                left join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID

                left join dbo.INSTALLMENTSPLITPAYMENT on SPLITS.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                left join dbo.FINANCIALTRANSACTION PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID
                left join dbo.CONSTITUENT PLEDGECONSTITUENT on PLEDGE.CONSTITUENTID = PLEDGECONSTITUENT.ID
                where
                    @ROWTORETURN = ROWNUMBER
                    and PLEDGE.DELETEDON is null