UFN_REVENUE_BUILDSPLITLIST_FORBATCHREVENUEID

            CREATE function dbo.UFN_REVENUE_BUILDSPLITLIST_FORBATCHREVENUEID
            (
                @BATCHREVENUEID uniqueidentifier
            )
            returns nvarchar(max)
            as
            begin
                --Designations for a batch row can come from three places: 

                --The BATCHREVENUESPLIT table for the 'Payment' portion of the batch row

                --The REVENUESPLIT table for the REVENUEID from the BATCHREVENUEAPPLICATION table for the pledge/etc. splits

                --The EVENT table for the REGISTRANT for from the BATCHREVENUEAPPLICATION table for the event.


                declare @DESIGNATIONS table
                (
                    DESIGNATIONID uniqueidentifier,
                    DESIGNATION nvarchar(512)
                );

                insert into @DESIGNATIONS(DESIGNATIONID, DESIGNATION)
                select D.DESIGNATIONID, isnull(D.NAME, dbo.UFN_DESIGNATION_BUILDNAME(D.DESIGNATIONID)) [DESIGNATION]
                from (
                    select SINGLEDESIGNATIONID [DESIGNATIONID], null [NAME]
                    from dbo.BATCHREVENUE where BATCHREVENUE.ID = @BATCHREVENUEID
                    and BATCHREVENUE.AMOUNT > (select coalesce(sum(APPLIED), 0) from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @BATCHREVENUEID)

                    union all

                    select [SPLIT].DESIGNATIONID, null
                    from dbo.BATCHREVENUESPLIT as [SPLIT] where [SPLIT].BATCHREVENUEID = @BATCHREVENUEID

                    union all

                    select SPLIT.DESIGNATIONID, null
                    from dbo.BATCHREVENUEADDITIONALAPPLICATIONS SPLIT
                    where SPLIT.BATCHREVENUEID = @BATCHREVENUEID and SPLIT.APPLIED > 0

                    union all

                    select REVENUESPLIT_EXT.DESIGNATIONID, null
                    from dbo.BATCHREVENUEAPPLICATION APP
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = APP.REVENUEID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = LI.ID
                    where APP.BATCHREVENUEID = @BATCHREVENUEID and LI.DELETEDON is null and LI.TYPECODE != 1 and APP.APPLICATIONTYPECODE = 0
                        and APP.APPLIED > 0

                    union all

                    select EVENT.ID, EVENT.NAME
                    from dbo.BATCHREVENUEAPPLICATION APP
                    inner join dbo.REGISTRANT on REGISTRANT.ID = APP.REGISTRANTID
                    inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                    where APP.BATCHREVENUEID = @BATCHREVENUEID and APP.APPLICATIONTYPECODE = 1
                        and APP.APPLIED > 0

                    union all

                    select SPLIT.DESIGNATIONID, null
                    from dbo.BATCHREVENUEAPPLICATION APP
                    inner join dbo.BATCHREVENUEAPPLICATIONPLEDGESPLIT SPLIT on SPLIT.BATCHREVENUEAPPLICATIONPLEDGEID = APP.BATCHREVENUEAPPLICATIONPLEDGEID
                    where APP.BATCHREVENUEID = @BATCHREVENUEID and APP.APPLICATIONTYPECODE = 2
                        and APP.APPLIED > 0
                ) D
                group by D.DESIGNATIONID, D.NAME

                return coalesce((select dbo.UDA_BUILDLIST(DESIGNATION) from @DESIGNATIONS), N'');
            end