UFN_STUDENTMARKINGCOLUMN_GETGRADES

            CREATE function dbo.UFN_STUDENTMARKINGCOLUMN_GETGRADES
            (
                @STUDENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier
            )
            returns @GRADESTABLE table (
              ID uniqueidentifier null,
              StudentCourseID uniqueidentifier not null,
              ClassID uniqueidentifier not null,
              CourseID uniqueidentifier not null,
              CourseShortID nvarchar(12) not null,
              CourseName nvarchar(60) not null,
              Class nvarchar(20) not null,
              WithdrawalDate date not null,
              Teacher nvarchar(1000) null,
              CourseGradingID uniqueidentifier not null,
              MCID uniqueidentifier not null,
              MCDisplayName nvarchar(10) not null,
              TranslationTableID uniqueidentifier null,
              Graded tinyint not null,
              ValuesAllowedCode tinyint not null,
              GradeTypeCode tinyint not null,
              NumericGrade numeric(14, 2) null,
              LetterGrade nvarchar(10) null,
              TranslationTableGradeID uniqueidentifier null,
              StudentClassMeetingGroupID uniqueidentifier not null,
              StudentClassMeetingGroupStatusCode tinyint not null,
              CourseGradingMarkingColumnID uniqueidentifier not null,
              LowestScoreAllowed numeric(14,2) null,
              HighestScoreAllowed numeric(14,2) null,
              MarkingColumnSetID uniqueidentifier not null,
              MCSequence int not null
          )
        as
        begin
            declare @SESSIONID uniqueidentifier
            declare @SESSION_STARTDATE date
            declare @SESSION_ENDDATE date

            select
                @SESSIONID = dbo.GRADINGAPPUSERSESSION.SESSIONID
            from dbo.GRADINGAPPUSERSESSION
            where dbo.GRADINGAPPUSERSESSION.ID = @CURRENTAPPUSERID

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

            declare @CLASSES table (
              StudentCourseID uniqueidentifier not null,
              ClassID uniqueidentifier,
              CourseID uniqueidentifier,
              CourseShortID nvarchar(12),
              CourseName nvarchar(60) null,
              Class nvarchar(20) null,
              Teacher nvarchar(1000) null,
              StudentClassMeetingGroupID uniqueidentifier,
              StudentClassMeetingGroupStatusCode tinyint not null,
              TermNameCodeID uniqueidentifier,
              TermStart date,
              TermEnd date,
              WithdrawalDate date,
              SessionID uniqueidentifier,
              SessionStart date,
              SessionEnd date,
              SchoolID uniqueidentifier)

            --Get the classes the student is enrolled in

            Insert @CLASSES
            Select
                STUDENTCOURSE.ID,
                CLASS.ID,
                COURSE.ID,
                COURSE.COURSEID,
                COURSE.NAME,
                CLASS.SECTION,
                dbo.UFN_CLASS_GETFACULTYSTRING(CLASS.ID),
                STUDENTCLASSMEETINGGROUP.ID,
                STUDENTCLASSMEETINGGROUP.STATUSCODE,
                TERM.TERMNAMECODEID,
                TERM.STARTDATE,
                TERM.ENDDATE,
                TERM.WITHDRAWALDATE,
                TERM.SESSIONID,
                (select MIN(STARTDATE) from dbo.TERM t1 where t1.SESSIONID = SESSION.ID),
                (select MAX(ENDDATE) from dbo.TERM t2 where t2.SESSIONID = SESSION.ID),
                ACADEMICYEAR.SCHOOLID
            from dbo.STUDENTCOURSE 
                inner join dbo.STUDENTCLASSMEETINGGROUP on STUDENTCOURSE.ID = STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID
                inner join dbo.CLASSMEETINGGROUP on STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID = CLASSMEETINGGROUP.ID
                inner join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
                inner join dbo.COURSE on Class.COURSEID = COURSE.ID
                inner join dbo.TERM on CLASSMEETINGGROUP.STARTDATE = TERM.STARTDATE and CLASSMEETINGGROUP.ENDDATE = TERM.ENDDATE
                inner join dbo.SESSION on TERM.SESSIONID = SESSION.ID
                inner join dbo.ACADEMICYEAR on SESSION.ACADEMICYEARID = ACADEMICYEAR.ID
            where (STUDENTCOURSE.STUDENTID = @STUDENTID)                
                --and TERM.STARTDATE >= @SESSION_STARTDATE

                --and TERM.ENDDATE <= @SESSION_ENDDATE

                --We want to include overlapping sessions to account for different schools that have slightly different date ranges,

                --   so just getting records where the Term is wholly contained within the Session doesn't work.

                and (TERM.STARTDATE between @SESSION_STARTDATE and @SESSION_ENDDATE
                or TERM.ENDDATE between @SESSION_STARTDATE and @SESSION_ENDDATE
                or @SESSION_STARTDATE between TERM.STARTDATE and TERM.ENDDATE
                or @SESSION_ENDDATE between TERM.STARTDATE and TERM.ENDDATE)
                and ACADEMICYEAR.SCHOOLID = COURSE.SCHOOLID


            --Now get the Course Grading info and any actual Grades that go with those classes.

            Insert @GRADESTABLE
            Select
                STUDENTMARKINGCOLUMNGRADE.ID,
                c.STUDENTCOURSEID,
                c.CLASSID,
                c.COURSEID,
                c.COURSESHORTID,
                c.COURSENAME,
                c.CLASS,
                c.WITHDRAWALDATE,
                c.TEACHER,
                COURSEGRADING.ID,
                MARKINGCOLUMN.ID,
                MARKINGCOLUMN.DISPLAYNAME,
                COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID,
                (Select Case COURSEGRADINGMARKINGCOLUMN.GRADED when 0 then 0 else (Case MarkingColumn.AllTerms When 1 Then 1 else (Case When (select ID from dbo.MARKINGCOLUMNTERM where c.TERMNAMECODEID = MARKINGCOLUMNTERM.TERMNAMECODEID and 
                 MARKINGCOLUMNTERM.MARKINGCOLUMNID = MARKINGCOLUMN.ID) is null then 0 else 1 End) End) End) GRADED,  
                 COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE,
                ISNULL(STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE,''),
                STUDENTMARKINGCOLUMNGRADE.NUMERICGRADEVALUE,
                ISNULL((SELECT GRADE From TRANSLATIONTABLEGRADE where ID = STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID),''),
                STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID,
                c.STUDENTCLASSMEETINGGROUPID,
                c.StudentClassMeetingGroupStatusCode,
                COURSEGRADINGMARKINGCOLUMN.ID,
                TRANSLATIONTABLE.LOWESTSCOREALLOWED,
                TRANSLATIONTABLE.HIGHESTSCOREALLOWED,
                MARKINGCOLUMN.MARKINGCOLUMNSETID, 
                MARKINGCOLUMN.SEQUENCE
            from @CLASSES c
                inner join dbo.V_COURSEGRADING as COURSEGRADING on COURSEGRADING.COURSEID = c.CourseID and COURSEGRADING.STARTDATE = c.SESSIONSTART and COURSEGRADING.ENDDATE = c.SESSIONEND
                inner join dbo.COURSEGRADINGMARKINGCOLUMN on COURSEGRADING.ID = COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID
                left join dbo.TRANSLATIONTABLE on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = TRANSLATIONTABLE.ID
                inner join dbo.MARKINGCOLUMN on COURSEGRADINGMARKINGCOLUMN.MARKINGCOLUMNID = MARKINGCOLUMN.ID
                left join dbo.STUDENTMARKINGCOLUMNGRADE on c.STUDENTCLASSMEETINGGROUPID = STUDENTMARKINGCOLUMNGRADE.STUDENTCLASSMEETINGGROUPID
                    and STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID

            return
            end