UFN_STUDENTSCHEDULE_TRANSFORM_COURSETERMS_TO_COURSEREQUESTS

CREATE function dbo.UFN_STUDENTSCHEDULE_TRANSFORM_COURSETERMS_TO_COURSEREQUESTS
(
    @ID uniqueidentifier = null,    --STUDENTID

    @SESSIONID uniqueidentifier = null,
    @COURSETERMS xml = null
)
returns @RESULTS TABLE
(
    [ID] uniqueidentifier,
    [COURSEID] uniqueidentifier,
    [STARTTERMID] uniqueidentifier
)
as begin

    declare @COURSEREQUESTS xml

    declare @SESSION_STARTDATE date
    declare @SESSION_ENDDATE date

    select
        @SESSION_STARTDATE = min(STARTDATE),
        @SESSION_ENDDATE = max(ENDDATE)
    from dbo.TERM
    where TERM.SESSIONID = @SESSIONID

    insert into @RESULTS
    select
        T.ID,
        T.COURSEID,
        T.TERMID as STARTTERMID
    from
        (
        select
            COURSETERMS.ID,
            COURSETERMS.COURSEID,
            case when STUDENTCOURSEREQUEST.ID is not null and STUDENTCOURSEREQUEST.CLASSSTARTDATE is null or
                    (COURSETERMS.TERMCOUNT > COURSERESTRICTION.LENGTHINTERMS)
                then null
                else TERM.ID
            end as TERMID,        
            row_number() over (partition by COURSETERMS.ID, COURSETERMS.COURSEID order by TERM.STARTDATE) as ROW
        from (select 
                    T.c.value('(ID)[1]','uniqueidentifier') AS [ID],
                    T.c.value('(COURSEID)[1]','uniqueidentifier') AS [COURSEID],
                    T.c.value('(CLASSID)[1]','uniqueidentifier') AS [CLASSID],
                    T.c.value('(TERMID)[1]','uniqueidentifier') AS [TERMID],
                    count(T.c.value('(TERMID)[1]','uniqueidentifier')) over (partition by T.c.value('(ID)[1]','uniqueidentifier')) as TERMCOUNT
                from @COURSETERMS.nodes('/COURSETERMS/ITEM') T(c)
            ) as COURSETERMS
            left join dbo.STUDENTCOURSEREQUEST on COURSETERMS.ID = STUDENTCOURSEREQUEST.ID
            inner join dbo.COURSE on COURSETERMS.COURSEID = COURSE.ID
            inner join dbo.V_COURSERESTRICTION COURSERESTRICTION on COURSETERMS.COURSEID = COURSERESTRICTION.COURSEID
            inner join dbo.COURSERESTRICTIONSTARTTERM on COURSERESTRICTION.ID = COURSERESTRICTIONSTARTTERM.COURSERESTRICTIONID
            inner join dbo.TERM on
                TERM.STARTDATE = COURSERESTRICTIONSTARTTERM.STARTDATE and
                TERM.ENDDATE = COURSERESTRICTIONSTARTTERM.ENDDATE and
                TERM.SESSIONID = @SESSIONID and
                TERM.ID = COURSETERMS.TERMID
        ) T
    where T.ROW = 1

    return 
end