UFN_SITEID_MAPFROM_EVENTDONORID

    CREATE function dbo.UFN_SITEID_MAPFROM_EVENTDONORID
    (
          @EVENTDONORID uniqueidentifier -- this is the revenueid from the event donor query view.

    )
    returns table
    as
    return
            select DISTINCT TOP(1) (
                select top 1 coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)    
                from dbo.DESIGNATION     
                left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID    
                left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID    
                left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID    
                left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID    
                left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID    
                where DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
            ) as [SITEID]
            from dbo.REVENUESPLIT
            where REVENUESPLIT.REVENUEID = @EVENTDONORID
      AND REVENUESPLIT.APPLICATIONCODE IN (0,7,17)
            ORDER BY SITEID DESC