UFN_REVENUEBATCH_GETCONSTITUENTIDFROMAPPLICATIONS

            CREATE function dbo.UFN_REVENUEBATCH_GETCONSTITUENTIDFROMAPPLICATIONS
            (
                @REVENUESTREAMS as xml
            )
            returns uniqueidentifier
            with execute as caller
            as begin
                declare @CONSTITUENTID uniqueidentifier;

                declare @REVENUEAPPLICATIONS table
                (
                    CONSTITUENTID uniqueidentifier,
                    APPLICATIONCOMMITMENTID uniqueidentifier
                );

                -- Hold all of the constituents from the commitments to see if they are all the same

                declare @CONSTITUENTS table
                (
                    ID uniqueidentifier
                );

                insert into @REVENUEAPPLICATIONS(CONSTITUENTID, APPLICATIONCOMMITMENTID)
                    select CONSTITUENTID, APPLICATIONCOMMITMENTID
                    from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_FROMITEMLISTXML(@REVENUESTREAMS);

                declare @NUMBEROFAPPLICATIONCONSTITUENTS integer;
                select @NUMBEROFAPPLICATIONCONSTITUENTS = COUNT(distinct CONSTITUENTID) from @REVENUEAPPLICATIONS where CONSTITUENTID is not null

                if @NUMBEROFAPPLICATIONCONSTITUENTS = 1
                begin
                    select top 1 @CONSTITUENTID = CONSTITUENTID  from @REVENUEAPPLICATIONS where CONSTITUENTID is not null 
                end
                else if @NUMBEROFAPPLICATIONCONSTITUENTS < 1
                begin
                    declare @NUMBEROFAPPLICATIONCOMMITMENTIDS integer;
                    select @NUMBEROFAPPLICATIONCOMMITMENTIDS = COUNT(APPLICATIONCOMMITMENTID) from @REVENUEAPPLICATIONS where APPLICATIONCOMMITMENTID is not null

                    if @NUMBEROFAPPLICATIONCOMMITMENTIDS > 0
                    begin

                        insert into @CONSTITUENTS
                            select REGISTRANT.CONSTITUENTID 
                            from @REVENUEAPPLICATIONS REVAPPS
                            inner join dbo.REGISTRANT on REVAPPS.APPLICATIONCOMMITMENTID = REGISTRANT.ID

                        insert into @CONSTITUENTS                          
                            select 
                                case
                                    when MEMBERSHIP.ISGIFT = 1 and MEMBERSHIP.SENDRENEWALCODE = 0 then MEMBERSHIP.GIVENBYID
                                    else MEMBER.CONSTITUENTID
                                end
                            from @REVENUEAPPLICATIONS REVAPPS
                            inner join dbo.MEMBERSHIP on REVAPPS.APPLICATIONCOMMITMENTID = MEMBERSHIP.ID
                            inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                            where MEMBER.ISPRIMARY = 1

                        insert into @CONSTITUENTS                          
                            select REVENUE.CONSTITUENTID
                            from @REVENUEAPPLICATIONS REVAPPS
                            inner join dbo.REVENUE on REVAPPS.APPLICATIONCOMMITMENTID = REVENUE.ID       

                        declare @DISTINCTCONSTITUENTS as integer;
                        select @DISTINCTCONSTITUENTS = COUNT(DISTINCT ID)
                            from @CONSTITUENTS

                        if @DISTINCTCONSTITUENTS = 1
                            select top 1 @CONSTITUENTID = ID from @CONSTITUENTS
                        else
                            select @CONSTITUENTID = null
                    end
                end
                else
                    select @CONSTITUENTID = null

                return @CONSTITUENTID
            end