USP_ADDREQUESTSFORMULTIPLESTUDENTS_GETCOURSES

CREATE procedure dbo.USP_ADDREQUESTSFORMULTIPLESTUDENTS_GETCOURSES
(
    @CORECURRICULUM xml,
    @ACADEMICYEARID uniqueidentifier = null,
    @SESSIONNAMECODEID uniqueidentifier = null
)
as
set nocount on

declare @SESSIONID uniqueidentifier

if @ACADEMICYEARID in ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000002')
    set @ACADEMICYEARID = null

if @ACADEMICYEARID is not null
    select @SESSIONID = SESSION.ID
    from dbo.SESSION
    where ACADEMICYEARID = @ACADEMICYEARID and SESSIONNAMECODEID = @SESSIONNAMECODEID        

if @SESSIONID is null    
    select distinct
        CORECURRICULUM.ID as CORECURRICULUMID,
        CORECURRICULUMCOURSE.ID as CORECURRICULUMCOURSEID,
        COURSE.ID as COURSEID,
        CORECURRICULUM.NAME as CORE_NAME, 
        COURSE.NAME as COURSE_NAME, 
        COURSE.COURSEID AS COURSE_ID,
        dbo.UFN_COURSE_GETGRADELEVELSTRING(COURSE.ID) as GRADE_LEVELS
    from dbo.COURSE
    inner join dbo.CORECURRICULUMCOURSE 
        on CORECURRICULUMCOURSE.COURSEID = COURSE.ID
    inner join dbo.CORECURRICULUM 
        on CORECURRICULUM.ID = CORECURRICULUMCOURSE.CORECURRICULUMID
    inner join dbo.UFN_ADDREQUESTSFORMULTIPLESTUDENTS_GETCORE_FROMITEMLISTXML(@CORECURRICULUM) as CORE 
        on CORE.ID = CORECURRICULUM.ID     
    order by CORECURRICULUM.NAME, COURSE.COURSEID
else
    select distinct
        CORECURRICULUM.ID as CORECURRICULUMID,
        CORECURRICULUMCOURSE.ID as CORECURRICULUMCOURSEID,
        COURSE.ID as COURSEID,
        CORECURRICULUM.NAME as CORE_NAME, 
        COURSE.NAME as COURSE_NAME, 
        COURSE.COURSEID AS COURSE_ID,
        dbo.UFN_COURSE_GETGRADELEVELSTRING(COURSE.ID) as GRADE_LEVELS
    from dbo.COURSE
    inner join dbo.CORECURRICULUMCOURSE 
        on CORECURRICULUMCOURSE.COURSEID = COURSE.ID
    inner join dbo.CORECURRICULUM 
        on CORECURRICULUM.ID = CORECURRICULUMCOURSE.CORECURRICULUMID
    inner join dbo.UFN_ADDREQUESTSFORMULTIPLESTUDENTS_GETCORE_FROMITEMLISTXML(@CORECURRICULUM) as CORE 
        on CORE.ID = CORECURRICULUM.ID     
    inner join dbo.V_COURSERESTRICTION as COURSERESTRICTION
        on COURSE.ID = COURSERESTRICTION.COURSEID
    where dbo.UFN_SESSION_GET_FORSCHOOL_BYDATE(COURSE.SCHOOLID, COURSERESTRICTION.STARTDATE, COURSERESTRICTION.ENDDATE) = @SESSIONID
    order by CORECURRICULUM.NAME, COURSE.COURSEID