UFN_STUDENT_CURRENTENROLLMENTINFO

        CREATE function dbo.UFN_STUDENT_CURRENTENROLLMENTINFO(
            @STUDENTID uniqueidentifier,
            @INCLUDEENROLLMENTSWITHNOPROGRESSIONS bit = 1
        )
        returns @UFN_STUDENT_CURRENTENROLLMENTINFO TABLE
       (
        ENROLLMENTID    uniqueidentifier,
        SCHOOLID   uniqueidentifier,
        SCHOOL nvarchar(100),
        GRADELEVELID uniqueidentifier,
        GRADELEVEL nvarchar(10),
        HOMEROOMTEACHERID uniqueidentifier,
        HOMEROOMTEACHER nvarchar(154),
        ADVISORID uniqueidentifier,
        ADVISOR nvarchar(154),
        ROW tinyint
       )
        with execute as caller
        as begin
              begin
                if @INCLUDEENROLLMENTSWITHNOPROGRESSIONS = 1
                begin
                    INSERT INTO @UFN_STUDENT_CURRENTENROLLMENTINFO 
                        (
                        ENROLLMENTID, 
                        SCHOOLID, 
                        SCHOOL,
                        GRADELEVELID,
                        GRADELEVEL,
                        HOMEROOMTEACHERID, 
                        HOMEROOMTEACHER,
                        ADVISORID,
                        ADVISOR,
                        ROW
                        )
                    select 
                        CURR_ENROLL.[ENROLLMENTID], 
                        ISNULL(CONSTIT_PROG_SCHOOL.[ID], CONSTIT_ENROLL_SCHOOL.[ID]), 
                        ISNULL(CONSTIT_PROG_SCHOOL.[KEYNAME], CONSTIT_ENROLL_SCHOOL.[KEYNAME]),
                        dbo.GRADELEVEL.[ID],
                        dbo.GRADELEVEL.[ABBREVIATION],
                        CURR_PROG.[HOMEROOMTEACHERID],
                        CONSTIT_HOMEROOMTEACHER.[NAME],
                        dbo.STUDENTADVISOR.[FACULTYID],
                        CONSTIT_ADVISOR.[NAME],
                        ROW_NUMBER() OVER (ORDER BY CURR_ENROLL.[ENROLLMENTID])
                    from 
                        dbo.UFN_STUDENTENROLLMENT_CURRENTINFORMATION(@STUDENTID) as CURR_ENROLL 
                    inner join dbo.CONSTITUENT as CONSTIT_ENROLL_SCHOOL on CURR_ENROLL.[SCHOOLID] = CONSTIT_ENROLL_SCHOOL.[ID]
                    left outer join dbo.STUDENTPROGRESSION as CURR_PROG on CURR_ENROLL.[ENROLLMENTID] = CURR_PROG.[ENROLLMENTID] and (GETDATE() BETWEEN CURR_PROG.[STARTDATE] AND CURR_PROG.[ENDDATE])
                    left outer join dbo.SCHOOLGRADELEVEL on CURR_PROG.[SCHOOLGRADELEVELID] = dbo.SCHOOLGRADELEVEL.[ID]
                    left outer join dbo.GRADELEVEL on dbo.SCHOOLGRADELEVEL.[GRADELEVELID] = dbo.GRADELEVEL.[ID]
                    left outer join dbo.CONSTITUENT as CONSTIT_PROG_SCHOOL on dbo.SCHOOLGRADELEVEL.[SCHOOLID] = CONSTIT_PROG_SCHOOL.[ID] 
                    left outer join dbo.CONSTITUENT as CONSTIT_HOMEROOMTEACHER on CURR_PROG.[HOMEROOMTEACHERID] = CONSTIT_HOMEROOMTEACHER.[ID]
                    left outer join dbo.STUDENTADVISOR on dbo.STUDENTADVISOR.[ID] = dbo.UFN_STUDENTADVISOR_GETFIRSTID(CURR_PROG.[ID])
                    left outer join dbo.CONSTITUENT CONSTIT_ADVISOR on dbo.STUDENTADVISOR.[FACULTYID] = CONSTIT_ADVISOR.[ID] 
                    order by CURR_ENROLL.[STARTDATE], CURR_PROG.[STARTDATE]
                end
              end              
            return
        end