USP_DATALIST_EVENTSWORKCENTERLOCATIONCODE

CREATE procedure dbo.USP_DATALIST_EVENTSWORKCENTERLOCATIONCODE
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = 0
)
as
    set nocount on;

    select ID, DESCRIPTION, ASSOCIATEDSITES, ASSOCIATEDCATEGORYCODES
    from (
        select 
            EVENTLOCATION.ID,
            EVENTLOCATION.NAME as DESCRIPTION,
            0 as TYPE,
            dbo.UFN_EVENTLOCATION_ASSOCIATEDSITES(EVENTLOCATION.ID) as ASSOCIATEDSITES,
            dbo.UFN_EVENTLOCATION_ASSOCIATEDCATEGORYCODES(EVENTLOCATION.ID) as ASSOCIATEDCATEGORYCODES
        from 
            dbo.EVENTLOCATION
        where exists (select 1 from dbo.EVENT left join dbo.EVENTSITE on EVENTSITE.EVENTID = EVENT.ID where EVENTLOCATION.ID = EVENT.EVENTLOCATIONID and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))) or
            exists (select PROGRAMEVENTLOCATION.ID from dbo.PROGRAMEVENTLOCATION where EVENTLOCATION.ID = PROGRAMEVENTLOCATION.EVENTLOCATIONID)

        union all

        select 
            '00000000-0000-0000-0000-000000000000' as ID,
            'No location' as DESCRIPTION,
            1 as TYPE,
            dbo.UFN_EVENTLOCATION_ASSOCIATEDSITES(null) as ASSOCIATEDSITES,
            dbo.UFN_EVENTLOCATION_ASSOCIATEDCATEGORYCODES(null) as ASSOCIATEDCATEGORYCODES
        where exists (select ID from dbo.EVENT where EVENT.EVENTLOCATIONID is null) or
        exists (select ID from dbo.PROGRAM where PROGRAM.PROGRAMCATEGORYCODEID is null)
    ) as LOCATION
    order by TYPE, DESCRIPTION