UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK

create function dbo.UFN_REVENUESPLIT_CONSTITUENTREVENUEHISTORYDETAIL_BULK()
returns table
as return (
    select
        REVENUESPLIT.ID,
        convert(nvarchar(1024), case
            when REVENUESPLIT.TYPECODE = 1 then (
                select top 1 EVENT.NAME
                from dbo.EVENTREGISTRANTPAYMENT
                inner join dbo.REGISTRANT on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                where EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
            )
            when REVENUESPLIT.TYPECODE = 2 then (
                select top 1 (MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME) as NAME
                from dbo.MEMBERSHIPTRANSACTION
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                where MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
            )
            else
                case
                    when REVENUESPLIT.TYPECODE = 9 then
                        case
                            when REVENUESPLIT.APPLICATIONCODE = 0 then (
                                select
                                    case
                                        when SUBQ.DETAIL is null or SUBQ.DETAIL = '' then (
                                            select DESIGNATIONNAME.NAME from dbo.UFN_DESIGNATION_BUILDNAME_BULK() DESIGNATIONNAME
                                            where DESIGNATIONNAME.ID = REVENUESPLIT.DESIGNATIONID
                                        )
                                        else SUBQ.DETAIL
                                    end
                                from (
                                    select top 1
                                        dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) +
                                        case when SPONSORSHIP.CONSTITUENTID <> REVENUE.CONSTITUENTID then (select ' (Gift to ' + NAME + ')' from dbo.CONSTITUENT where ID = SPONSORSHIP.CONSTITUENTID) else '' end
                                        as DETAIL
                                    from dbo.REVENUESPLIT RSINNER
                                    inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = RSINNER.ID 
                                    inner join dbo.REVENUE on REVENUE.ID = RSINNER.REVENUEID
                                    where RSINNER.ID = REVENUESPLIT.ID
                                    and SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1
                                ) as SUBQ
                            )
                            else (
                                select top 1
                                    dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) +
                                    case when SPONSORSHIPPAYMENT.GIFTFINANCIALSPONSORID is not null then ' (Gift to ' + coalesce(CONSTITUENT.NAME, '') + ')' else '' end
                                from dbo.REVENUESPLIT RSINNER
                                inner join dbo.SPONSORSHIPPAYMENT on SPONSORSHIPPAYMENT.ID = RSINNER.ID
                                inner join dbo.SPONSORSHIP on SPONSORSHIP.ID = SPONSORSHIPPAYMENT.SPONSORSHIPID
                                left join dbo.CONSTITUENT on SPONSORSHIP.CONSTITUENTID = CONSTITUENT.ID
                                where RSINNER.ID = REVENUESPLIT.ID
                            )
                        end
                    else (
                        select DESIGNATIONNAME.NAME from dbo.UFN_DESIGNATION_BUILDNAME_BULK() DESIGNATIONNAME
                        where DESIGNATIONNAME.ID = REVENUESPLIT.DESIGNATIONID
                    )
                end
        end) as DETAIL
    from dbo.REVENUESPLIT
)