UFN_STUDENTCOURSEREQUESTS_GETEXCEPTIONS

CREATE function dbo.UFN_STUDENTCOURSEREQUESTS_GETEXCEPTIONS(
    @STUDENTID uniqueidentifier, 
    @SESSIONID uniqueidentifier,
    @COURSEREQUESTS xml)

returns @RESULTS table
(
    ID uniqueidentifier,
    COURSEID uniqueidentifier,
    STARTTERMID uniqueidentifier,
    COURSE nvarchar(100),
    EXCEPTION nvarchar(100)
)    
as begin
    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select 
        @SESSION_STARTDATE = STARTDATE, 
        @SESSION_ENDDATE = ENDDATE 
    from 
        dbo.UFN_SESSION_GETDATES(@SESSIONID)

    declare @COURSEREQUESTS_TEMP table
    (
        [COURSEID] uniqueidentifier,
        [ID] uniqueidentifier,
        [STARTTERMID] uniqueidentifier
    )

    insert into @COURSEREQUESTS_TEMP
    (
        COURSEID,
        ID,
        STARTTERMID
    )
    select
        COURSEID,
        ID,
        STARTTERMID
    from (select
        T.c.value('(COURSEID)[1]','uniqueidentifier') AS 'COURSEID',
        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
        T.c.value('(STARTTERMID)[1]','uniqueidentifier') AS 'STARTTERMID'
        from @COURSEREQUESTS.nodes('/COURSEREQUESTS/ITEM') T(c)) COURSEREQUESTS

    insert into @RESULTS
    select 
        COURSEREQUESTS.ID, 
        COURSEREQUESTS.COURSEID, 
        COURSEREQUESTS.STARTTERMID,
        COURSE.COURSEID + ' - ' + COURSE.NAME as COURSE, 
        EXCEPTIONS.EXCEPTIONLIST as EXCEPTION
    from dbo.COURSE
    inner join @COURSEREQUESTS_TEMP COURSEREQUESTS
        on COURSEREQUESTS.COURSEID = COURSE.ID
    cross apply 
        dbo.UFN_STUDENTCOURSEREQUEST_GETEXCEPTIONLIST(@STUDENTID,COURSEREQUESTS.COURSEID,@SESSIONID,COURSEREQUESTS.STARTTERMID) as EXCEPTIONS
    where 
        --ID not in db and either no match by course/student/date or adding more than one of the same by course

        not exists (select ID from dbo.STUDENTCOURSE where STUDENTCOURSE.ID = COURSEREQUESTS.ID) 
        and (not exists (
                select STUDENTCOURSEREQUEST.ID 
                from dbo.STUDENTCOURSEREQUEST 
                join dbo.STUDENTCOURSE 
                    on STUDENTCOURSE.ID = STUDENTCOURSEREQUEST.ID
                where STUDENTCOURSE.COURSEID = COURSEREQUESTS.COURSEID and
                    STUDENTCOURSE.STUDENTID = @STUDENTID and
                    STUDENTCOURSEREQUEST.STARTDATE >= @SESSION_STARTDATE and
                    STUDENTCOURSEREQUEST.ENDDATE <= @SESSION_ENDDATE) or
             (select count(1) from @COURSEREQUESTS_TEMP REQS where REQS.COURSEID = COURSEREQUESTS.COURSEID) > 1
            )                

    return
end