FITOMN of AM v3
5/23/2017 - 6:16 PM

BIT/IAM-1605 - Adecuación para mostrar asignaturas que no se hayan tomado sin considerar si éstas pertenecen o no a un grado menor al actual

BIT/IAM-1605 - Adecuación para mostrar asignaturas que no se hayan tomado sin considerar si éstas pertenecen o no a un grado menor al actual del alumno.


----------------------------INICIO PROCEDIMIENTO ------------------------
IF OBJECT_ID('spRPTKardexCompletoSEPV2', 'P') IS NOT NULL
      DROP PROC spRPTKardexCompletoSEPV2 
GO

CREATE PROCEDURE [dbo].[spRPTKardexCompletoSEPV2]
    @sMatricula VARCHAR(20)
  , @idOfEduc SMALLINT
  , @idPlantel SMALLINT
  , @dFechaActual DATETIME
  , @iPanel TINYINT
  , @bPeriodoConMeses BIT
/* --------------------------------------------------------------------------------------------------------------
	FECHA	   |    AUTOR		|	DESCRIPCION
 23/May/2017   | Luis Bernal	| BIT/IAM-1605 - HISTORIAL ACADÉMICO TIPO 2 - Punto 4
--------------------------------------------------------------------------------------------------------------*/
AS
    BEGIN
        SET NOCOUNT ON;
        SET LANGUAGE spanish
   --CONTENIDO PROCEDIMIENTO

--DECLARE @sMatricula varchar(20) = '14maa2686', -- varchar(20)
--    @idOfEduc SMALLINT = 7, -- smallint
--    @dFechaActual DATETIME = '2016-01-23 00:34:22', -- datetime
--    @iPanel TINYINT = 0 -- tinyint


        DECLARE @Alumno listaInt 

        INSERT  INTO @Alumno
                (
                 id
                )
        SELECT TOP 1
                ca.idAlumno
        FROM    dbo.Cat_Alumnos AS ca
        INNER JOIN dbo.Ctrl_Inscripciones AS ci
        ON      ci.idAlumno = ca.idAlumno
        WHERE   ca.sMatricula = @sMatricula
                AND @idPlantel = ci.idPlantel

        IF 0 = (SELECT  COUNT(*)
                FROM    @Alumno AS a
               )
            BEGIN
                INSERT  INTO @Alumno
                        (
                         id
                        )
                SELECT TOP 1
                        ca.idAlumno
                FROM    dbo.Cat_Alumnos AS ca
                INNER JOIN dbo.Ctrl_Inscripciones AS ci
                ON      ci.idAlumno = ca.idAlumno
                WHERE   ca.sMatricula = @sMatricula
            END


        DECLARE @bMostrarSoloApro BIT = (SELECT TOP 1
                                                CAST(csp.sValor AS BIT)
                                         FROM   dbo.Cat_SistemaParametros AS csp
                                         WHERE  csp.idParametro = 57
                                        )


        DECLARE @TR TABLE
            (
             sMatricula VARCHAR(20)
           , sAlumno VARCHAR(150)
           , sClaveAsig VARCHAR(15)
           , sAsignatura VARCHAR(150)
           , sAbreviaturaEval VARCHAR(5)
           , sNombreEval VARCHAR(50)
           , iOrdenEval TINYINT
           , iOrdenPlan TINYINT
           , sCalificacion VARCHAR(50)
           , dCalificacion DECIMAL(7, 3)
           , bPromedio BIT
           , bObligatoria BIT
           , sRubrica VARCHAR(150)
           , sAbRubrica VARCHAR(5)
           , bCuantitativa BIT
           , iFolioTrayectoria BIGINT
           , idGrupo SMALLINT
           , idAsignatura SMALLINT
           , iFolioPeriodo BIGINT
           , idEvaluacion SMALLINT
           , idFormEvaluacion SMALLINT
           , sCiclo VARCHAR(50)
           , sGradoGrupo VARCHAR(50)
           , sObservaciones VARCHAR(1000)
           , iDecimalesProm TINYINT
           , iTipoCriterio TINYINT
           , iFaltasEval TINYINT
           , iFaltasInsc TINYINT
           , sAbreviaturaRubEval VARCHAR(50)
           , iOrdenClasifAsig TINYINT
           , sClasifAsig VARCHAR(150)
           , sOferta VARCHAR(150)
           , sPlanEst VARCHAR(150)
           , sRubricaCompuesta VARCHAR(300)
           , idClasifAsig SMALLINT
           , idRubrica SMALLINT
           , idAlumno INT
           , idOfEduc SMALLINT
           , iFolioInsc BIGINT
           , idPlanEst SMALLINT
           , bPromediar BIT
           , bEquivalencia BIT
           , dFechaReg DATETIME
           , dPromedio VARCHAR(15)
           , sObservacionesAcademicas VARCHAR(MAX)
           , iGradoInsc TINYINT
		   , iGradoAsig TINYINT
           , sPromedioClasif VARCHAR(15)
            );

        INSERT  INTO @TR
                (
                 sMatricula
               , sAlumno
               , sClaveAsig
               , sAsignatura
               , sAbreviaturaEval
               , sNombreEval
               , iOrdenEval
               , iOrdenPlan
               , sCalificacion
               , dCalificacion
               , bPromedio
               , bObligatoria
               , sRubrica
               , sAbRubrica
               , bCuantitativa
               , iFolioTrayectoria
               , idGrupo
               , idAsignatura
               , iFolioPeriodo
               , idEvaluacion
               , idFormEvaluacion
               , sCiclo
               , sGradoGrupo
               , sObservaciones
               , iDecimalesProm
               , iTipoCriterio
               , iFaltasEval
               , iFaltasInsc
               , sAbreviaturaRubEval
               , iOrdenClasifAsig
               , sClasifAsig
               , sOferta
               , sPlanEst
               , sRubricaCompuesta
               , idClasifAsig
               , idRubrica
               , idAlumno
               , idOfEduc
               , iFolioInsc
               , idPlanEst
               , bPromediar
               , bEquivalencia
               , dFechaReg
               , dPromedio
               , sObservacionesAcademicas
			   , iGradoInsc
			   , iGradoAsig
			   , sPromedioClasif
                )
                EXEC dbo.spListarCalificacionesBoletaSencilla @idAlumno = @Alumno, -- listaInt
                    @iFolioPeriodo = -1, -- bigint
                    @bRubricas = 0, -- bit
                    @idClasificacion = -1, -- smallint
                    @idOfEduc = @idOfEduc, -- smallint
                    @bEtiquetaHTML = 0, -- bit
                    @dFechaActual = @dFechaActual, @iPanel = @iPanel,
                    @iTipoConsulta = 2;
  
--SELECT * FROM @TR T


        DECLARE @Oportunidades TABLE
            (
             iFolioTrayectoria BIGINT
           , idAsignatura SMALLINT
           , iAnioInicial SMALLINT
           , iMesInicial TINYINT
           , iCicloEscolar TINYINT
           , iOportunidad TINYINT
            );

        INSERT  INTO @Oportunidades
                (
                 iFolioTrayectoria
               , idAsignatura
               , iAnioInicial
               , iMesInicial
               , iCicloEscolar
               , iOportunidad
                )
        SELECT  tps.iFolioTrayectoria
              , tps.idAsignatura
              , tps.iAnioInicial
              , tps.iMesInicial
              , tps.iCicloEscolar
              , ROW_NUMBER() OVER (PARTITION BY tps.idAsignatura ORDER BY tps.iAnioInicial ASC, tps.iMesInicial ASC, tps.iCicloEscolar ASC) AS no_op
        FROM    (SELECT DISTINCT
                        t.iFolioTrayectoria
                      , t.idAsignatura
                      , cp.iAnioInicial
                      , cp.iMesInicial
                      , cp.iCicloEscolar
                 FROM   @TR AS t
                 LEFT JOIN dbo.Ctrl_Inscripciones AS ci
                 ON     t.iFolioInsc = ci.iFolio
                 LEFT JOIN dbo.Ctrl_Periodos AS cp
                 ON     cp.iFolio = ci.iFolioPeriodo
                ) tps
        ORDER BY tps.iAnioInicial
              , tps.iMesInicial
              , tps.iCicloEscolar;

--SELECT * FROM @Oportunidades O
            
  --dbo.fnCalificacionConLetra(eq.dCalificacion, 0) AS sCalidicacion
        DECLARE @TS TABLE
            (
             sPlantel VARCHAR(250)
           , sOfEduc VARCHAR(150)
           , sPlanEstudios VARCHAR(150)
           , idPlanEstudios SMALLINT
           , idClasificacion SMALLINT
           , sClasificacion VARCHAR(150)
           , bClasificacion BIT
           , sOpClasificacion VARCHAR(10)
           , iGrado TINYINT
           , sTipoPeriodo VARCHAR(15)
           , sGrupo VARCHAR(50)
           , idAsignatura SMALLINT
           , sClave VARCHAR(15)
           , iCreditos DECIMAL(7, 3)
           , sAsignatura VARCHAR(150)
           , bOficial BIT
           , bObligatoria BIT
           , iOportunidad SMALLINT
           , bAsigAprobada BIT
           , iDecimales TINYINT
           , iOrden TINYINT
           , bPromedio BIT
           , dCalificacion DECIMAL(7, 3)
           , sEstCalificacion VARCHAR(50)
           , dMinimaAprobatoria DECIMAL(7, 3)
           , bEquivalencia BIT
           , sPeriodo VARCHAR(50)
           , bInscrito BIT
           , bAprobado BIT
           , sObservaciones VARCHAR(1000)
           , bPromediar BIT
           , iOrdenUltimaOblig TINYINT
           , sCalificacion VARCHAR(50)
           , dFechaRegCal DATETIME
           , sEvaluacion VARCHAR(50)
           , sEvalAbreviatura VARCHAR(15)
           , sEstatusEval VARCHAR(50)
           , sAbEstatusEval VARCHAR(15)
           , sCalifLetra VARCHAR(50)
           , idEval SMALLINT
           , iFolioTrayectoria BIGINT
           , idPlantel SMALLINT
           , iFolioInsc BIGINT
		   , iGradoInsc TINYINT
		   , sPromClasif VARCHAR(15)
            );




        INSERT  INTO @TS
                (
                 sPlantel
               , sOfEduc
               , sPlanEstudios
               , idPlanEstudios
               , idClasificacion
               , sClasificacion
               , bClasificacion
               , sOpClasificacion
               , iGrado
               , sTipoPeriodo
               , sGrupo
               , idAsignatura
               , sClave
               , iCreditos
               , sAsignatura
               , bOficial
               , bObligatoria
               , iOportunidad
               , bAsigAprobada
               , iDecimales
               , iOrden
               , bPromedio
               , dCalificacion
               , sEstCalificacion
               , dMinimaAprobatoria
               , bEquivalencia
               , sPeriodo
               , bInscrito
               , bAprobado
               , sObservaciones
               , bPromediar
               , iOrdenUltimaOblig
               , sCalificacion
               , dFechaRegCal
               , sEvaluacion
               , sEvalAbreviatura
               , sEstatusEval
               , sAbEstatusEval
               , sCalifLetra
               , idEval
               , iFolioTrayectoria
               , idPlantel
               , iFolioInsc
			   , iGradoInsc
			   , sPromClasif
                )
        SELECT  cp.sNombre AS sPlantel
              , t.sOferta
              , t.sPlanEst
              , t.idPlanEst
              , t.idClasifAsig
              , t.sClasifAsig
              , CASE WHEN t.idClasifAsig IS NULL THEN 0
                     ELSE 1
                END AS bClasif
              , NULL AS sOpClasificacion --sOpClasificacion
              , cpe.iGrado
              , cp2.sUnidad -- sTipoPeriodo
              , cg.sNombre
              , ISNULL(t.idAsignatura, cpe.idAsignatura) AS idAsignatura
              , t.sClaveAsig
              , ca.iCreditos
              , ca.sAsignatura
              , cpe.bOficial
              , t.bObligatoria
 --, t.idFormEvaluacion
              , CASE WHEN t.bEquivalencia = 1 THEN 1
                     ELSE o.iOportunidad
                END
              , CASE WHEN cee.bAprobado IS NOT NULL THEN cee.bAprobado
                     WHEN (CASE WHEN TRY_CAST(t.sCalificacion AS DECIMAL(7, 3)) IS NULL
                                THEN cee.dValorNumerico
                                ELSE CAST(t.sCalificacion AS DECIMAL(7, 3))
                           END) >= ISNULL(cfe2.dMinimaAprobatoria, 0) THEN 1
                     ELSE 0
                END AS bAprobada
              , cfe.iDecimalesCalif
              , t.iOrdenEval
              , ISNULL(cfe.bPromedioGral,0) AS bPromedio
              , CASE WHEN TRY_CAST(t.sCalificacion AS DECIMAL(7, 3)) IS NULL
                     THEN ISNULL(t.dCalificacion, cee.dValorNumerico)
                     ELSE CAST(t.sCalificacion AS DECIMAL(7, 3))
                END AS dCalificacion
              , '' --sEstCalif
              , cfe.dCalifMinima
              , t.bEquivalencia
              , CASE WHEN ISNULL(@bPeriodoConMeses,1) = 0 THEN  REPLACE(SUBSTRING(t.sCiclo,0,7),'(','') ELSE t.sCiclo  END  AS sCiclo
              , 1 --binscrito
              , CASE WHEN cee.bAprobado IS NOT NULL THEN cee.bAprobado
                     WHEN (CASE WHEN TRY_CAST(t.sCalificacion AS DECIMAL(7, 3)) IS NULL
                                THEN cee.dValorNumerico
                                ELSE CAST(t.sCalificacion AS DECIMAL(7, 3))
                           END) >= ISNULL(cfe2.dMinimaAprobatoria, 1) THEN 1
                     ELSE 0
                END AS bAprobada --baprovado
              , t.sObservaciones
              , t.bPromediar
              , t.iOrdenPlan
              , t.sCalificacion
              , t.dFechaReg
              , t.sNombreEval
              , t.sAbreviaturaEval
              , cee.sEstatus
              , cee.sAbreviatura
              , dbo.fnCalificacionConLetra(CASE WHEN TRY_CAST(t.sCalificacion AS DECIMAL(7,
                                                              3)) IS NULL
                                                THEN cee.dValorNumerico
                                                ELSE CAST(t.sCalificacion AS DECIMAL(7,
                                                              3))
                                           END, 0)
              , t.idEvaluacion
              , t.iFolioTrayectoria
              , ci.idPlantel
              , t.iFolioInsc
			  , t.iGradoInsc
			  , t.sPromedioClasif
        FROM    @TR AS t
        LEFT JOIN dbo.Ctrl_Inscripciones AS ci
        ON      t.iFolioInsc = ci.iFolio
        LEFT JOIN dbo.Cat_Planteles AS cp
        ON      cp.idPlantel = ci.idPlantel
        LEFT JOIN dbo.Ctrl_PlanEstudios AS cpe
        ON      cpe.idAsignatura = t.idAsignatura
                AND cpe.idPlanEstudios = t.idPlanEst
        LEFT JOIN dbo.Ctrl_Grupos AS cg
        ON      cg.idGrupo = t.idGrupo
        LEFT JOIN dbo.Cat_Asignaturas AS ca
        ON      ca.idAsignatura = t.idAsignatura
        LEFT JOIN dbo.Ctrl_FormEvaluacion AS cfe
        ON      cfe.idEvaluacion = t.idEvaluacion
        LEFT JOIN dbo.Cat_FormEvaluacion AS cfe2
        ON      cfe2.idFormEvaluacion = t.idFormEvaluacion
        LEFT JOIN dbo.Ctrl_TrayectoriaAcademicaDet AS ctad
        ON      ctad.idEvaluacion = t.idEvaluacion
                AND ctad.iFolioTrayectoria = t.iFolioTrayectoria
        LEFT JOIN dbo.Cat_EstatusEvaluacion AS cee
        ON      cee.idEstatus = ctad.idEstatus
        LEFT JOIN dbo.Cat_PlanEstudios AS cpe2
        ON      cpe2.idPlanEstudios = t.idPlanEst
        LEFT JOIN dbo.Cat_Planes AS cp2
        ON      cp2.idPlan = cpe2.idPlan
        LEFT JOIN dbo.Ctrl_Periodos AS cp3
        ON      cp3.iFolio = ci.iFolioPeriodo
        LEFT JOIN @Oportunidades AS o
        ON      o.iFolioTrayectoria = t.iFolioTrayectoria
        WHERE   t.iTipoCriterio = 1;
        
--SELECT * FROM @TS T

        DECLARE @TRFinal TABLE
            (
             sPlantel VARCHAR(250)
           , sOfEduc VARCHAR(150)
           , sPlanEstudios VARCHAR(150)
           , idPlanEstudios SMALLINT
           , idClasificacion SMALLINT
           , sClasificacion VARCHAR(150)
           , bClasificacion BIT
           , sOpClasificacion VARCHAR(10)
           , iGrado TINYINT
           , sTipoPeriodo VARCHAR(15)
           , sGrupo VARCHAR(50)
           , idAsignatura SMALLINT
           , sClave VARCHAR(15)
           , iCreditos DECIMAL(7, 3)
           , sAsignatura VARCHAR(200)
           , bOficial BIT
           , bObligatoria BIT
           , iOportunidad TINYINT
           , bAsigAprobada BIT
           , iDecimales TINYINT
           , iOrden TINYINT
           , bPromedio BIT
           , dCalificacion DECIMAL(7, 3)
           , sEstCalificacion VARCHAR(5)
           , dMinimaAprobatoria DECIMAL(7, 3)
           , bEquivalencia BIT
           , sPeriodo VARCHAR(500)
           , bInscrito BIT
           , bAprobado BIT
           , sObservaciones VARCHAR(1000)
           , bPromediar BIT
           , iOrdenUltimaOblig TINYINT
           , sCalificacion VARCHAR(50) NULL
           , dFechaRegCal DATETIME NULL
           , iOportunidadesEval1 SMALLINT
           , sAbreEval1 VARCHAR(5)
           , sEvaluacion1 VARCHAR(50)
           , iOportunidadesEval2 SMALLINT
           , sAbreEval2 VARCHAR(5)
           , sEvaluacion2 VARCHAR(50)
           , iOportunidadesEval3 SMALLINT
           , sAbreEval3 VARCHAR(5)
           , sEvaluacion3 VARCHAR(50)
           , sCalificacionLetra VARCHAR(50)
           , idPlantel SMALLINT
           , sEvalR VARCHAR(50)
           , sEvalAbR VARCHAR(15)
           , iFolioInsc BIGINT
		   , iGradoInsc TINYINT
		   , sPromClasif VARCHAR(15)
            ); 

        DECLARE @sAbreEval1 VARCHAR(5)
          , @sEvaluacion1 VARCHAR(50)
          , @sAbreEval2 VARCHAR(5)
          , @sEvaluacion2 VARCHAR(50)
          , @sAbreEval3 VARCHAR(5)
          , @sEvaluacion3 VARCHAR(50);

/*------------------------------------------------------------------------------------------------------------------------------------*/
        DECLARE @idAsignatura SMALLINT
          , @iGrado TINYINT
          , @sAsignatura VARCHAR(200);
--, @idAsignaturaOld SMALLINT = 0    

        DECLARE cCursor CURSOR LOCAL
        FOR
            SELECT DISTINCT
                    idAsignatura
                  , iGrado
                  , sAsignatura
            FROM    @TS AS t
            ORDER BY iGrado
                  , sAsignatura;
    
        OPEN cCursor;
        FETCH cCursor INTO @idAsignatura, @iGrado, @sAsignatura;

        WHILE (@@FETCH_STATUS = 0)
            BEGIN
    
    /*------------------------------------------------------------------------------------------------------------------------------------*/
                DECLARE @idEval SMALLINT
                  , @dCalificacion DECIMAL
                  , @sCalificacion VARCHAR(50)
                  , @sCalifLetra VARCHAR(50)
                  , @iOportunidad TINYINT
                  , @sEvaluacion VARCHAR(50)
                  , @sEvalAbreviatura VARCHAR(15)
                  , @bAprobado BIT
                  , @bAprobadoR BIT = 0
                  , @iFolioTray BIGINT
                  , @iCont1 TINYINT
                  , @iCont2 TINYINT
                  , @iCont3 TINYINT
                  , @iCont TINYINT = 1
                  , @dCalificacionR DECIMAL
                  , @sCalificacionR VARCHAR(50)
                  , @sCalifLetraR VARCHAR(50)
                  , @iFolioTrayR BIGINT
                  , @idEvalR SMALLINT
                  , @sEvalR VARCHAR(50)
                  , @sEvalAbR VARCHAR(15)
                  , @iOrdenExtra TINYINT
                  , @bExtra BIT
                  , @bPromedio BIT
                  , @iFolioInscR BIGINT
                  , @iFolioInsc BIGINT
                  , @iTipoEval TINYINT
                  , @sPeriodoApro VARCHAR(50)
                  , @sPeriodoAproR VARCHAR(50) = ''
                  , @sPeriodoAlt VARCHAR(500)= '';
           

                DECLARE cCursorCalif CURSOR LOCAL
                FOR
                    SELECT  t.idEval
                          , t.dCalificacion
                          , t.sCalificacion
                          , CASE WHEN TRY_CAST(t.sCalificacion AS DECIMAL(7, 3)) IS NULL
                                 THEN t.sCalificacion
                                 ELSE t.sCalifLetra
                            END
                          , t.iOportunidad
                          , t.sEvaluacion
                          , t.sEvalAbreviatura
                          , t.bAprobado
                          , t.iFolioTrayectoria
                          , t.bPromedio
                          , dbo.fnObtenerOrdenEvaluacionExtraordinaria(t.idEval)
                          , cfe.bExtraordinaria
                          , t.iFolioInsc
                          , ISNULL(cfe.iTipoEvaluacion, 1) AS iTipoEvaluacion
                          , t.sPeriodo
                    FROM    @TS AS t
                    LEFT JOIN dbo.Ctrl_FormEvaluacion AS cfe
                    ON      t.idEval = cfe.idEvaluacion
                    WHERE   t.idAsignatura = @idAsignatura
					AND (t.bPromedio = 1 OR cfe.bExtraordinaria = 1)
                    ORDER BY t.idAsignatura
                          , iOportunidad
                          , t.iOrden;
        
                OPEN cCursorCalif;
                FETCH cCursorCalif INTO @idEval, @dCalificacion,
                    @sCalificacion, @sCalifLetra, @iOportunidad, @sEvaluacion,
                    @sEvalAbreviatura, @bAprobado, @iFolioTray, @bPromedio,
                    @iOrdenExtra, @bExtra, @iFolioInsc, @iTipoEval,
                    @sPeriodoApro;
    
                WHILE (@@FETCH_STATUS = 0)
                    BEGIN
				
                        SET @sPeriodoAlt = REPLACE(REPLACE(@sPeriodoAlt,
                                                           CASE
                                                              WHEN @sPeriodoAlt = ''
                                                              THEN ''
                                                              ELSE ', '
                                                           END + @sPeriodoApro,
                                                           ''), @sPeriodoApro,
                                                   '')
                            + CASE WHEN REPLACE(REPLACE(@sPeriodoAlt,
                                                        CASE WHEN @sPeriodoAlt = ''
                                                             THEN ''
                                                             ELSE ', '
                                                        END + @sPeriodoApro,
                                                        ''), @sPeriodoApro, '') = ''
                                   THEN ''
                                   ELSE ', '
                              END + @sPeriodoApro;
        
                        IF UPPER(@sEvalAbreviatura) = 'ORD'
                            OR @bPromedio = 1
                            OR @iTipoEval = 1
                            BEGIN
                                IF @dCalificacion IS NOT NULL
                                    BEGIN
                                        SET @iCont1 = ISNULL(@iCont1, 0) + 1;
                                    END;
                                
                                IF (
                                    @dCalificacion IS NOT NULL
                                    AND @bMostrarSoloApro = 0
                                   )
                                    OR (
                                        @bMostrarSoloApro = 1
                                        AND @bAprobado = 1
                                       )
                                    BEGIN
            
                                        SET @sAbreEval1 = @sEvalAbreviatura;
                                        SET @sEvaluacion1 = @sEvaluacion;
                                        SET @dCalificacionR = @dCalificacion;
                                        SET @sCalificacionR = @sCalificacion;
                                        SET @sCalifLetraR = @sCalifLetra;
                                        SET @iFolioTrayR = @iFolioTray;
                                        SET @iFolioInscR = @iFolioInsc;
                                        SET @idEvalR = @idEval;
                                        SET @sEvalR = @sEvaluacion;
                                        SET @sEvalAbR = @sEvalAbreviatura;
                                        SET @bAprobadoR = @bAprobado;
                                        SET @sPeriodoAproR = @sPeriodoApro
                                    END;
                            END;
                        IF UPPER(@sEvalAbreviatura) = 'EXT'
                            OR (
                                @bExtra = 1
                                AND @iOrdenExtra = 1
                               )
                            OR @iTipoEval = 2
                            BEGIN
                                IF @dCalificacion IS NOT NULL
                                    BEGIN
                                        SET @iCont2 = ISNULL(@iCont2, 0) + 1;
                                    END;
                                IF (
                                    @dCalificacion IS NOT NULL
                                    AND @bMostrarSoloApro = 0
                                   )
                                    OR (
                                        @bMostrarSoloApro = 1
                                        AND @bAprobado = 1
                                       )
                                    BEGIN
                                        SET @sAbreEval2 = @sEvalAbreviatura;
                                        SET @sEvaluacion2 = @sEvaluacion;
                                        SET @dCalificacionR = @dCalificacion;
                                        SET @sCalificacionR = @sCalificacion;
                                        SET @sCalifLetraR = @sCalifLetra;
                                        SET @iFolioTrayR = @iFolioTray;
                                        SET @iFolioInscR = @iFolioInsc;
                                        SET @idEvalR = @idEval;
                                        SET @sEvalR = @sEvaluacion;
                                        SET @sEvalAbR = @sEvalAbreviatura;
                                        SET @bAprobadoR = @bAprobado;
                                        SET @sPeriodoAproR = @sPeriodoApro
                                    END;
                            END;
                        IF UPPER(@sEvalAbreviatura) = 'ESP'
                            OR (
                                @bExtra = 1
                                AND @iOrdenExtra = 2
                               )
                            OR @iTipoEval = 3
                            BEGIN
                                IF @dCalificacion IS NOT NULL
                                    BEGIN
                                        SET @iCont3 = ISNULL(@iCont3, 0) + 1;
                                    END;
                                IF (
                                    @dCalificacion IS NOT NULL
                                    AND @bMostrarSoloApro = 0
                                   )
                                    OR (
                                        @bMostrarSoloApro = 1
                                        AND @bAprobado = 1
                                       )
                                    BEGIN
                                        SET @sAbreEval3 = @sEvalAbreviatura;
                                        SET @sEvaluacion3 = @sEvaluacion;
                                        SET @dCalificacionR = @dCalificacion;
                                        SET @sCalificacionR = @sCalificacion;
                                        SET @sCalifLetraR = @sCalifLetra;
                                        SET @iFolioTrayR = @iFolioTray;
                                        SET @iFolioInscR = @iFolioInsc;
                                        SET @idEvalR = @idEval;
                                        SET @sEvalR = @sEvaluacion;
                                        SET @sEvalAbR = @sEvalAbreviatura;
                                        SET @bAprobadoR = @bAprobado;
                                        SET @sPeriodoAproR = @sPeriodoApro
                                    END;
                            END;

                        FETCH cCursorCalif INTO @idEval, @dCalificacion,
                            @sCalificacion, @sCalifLetra, @iOportunidad,
                            @sEvaluacion, @sEvalAbreviatura, @bAprobado,
                            @iFolioTray, @bPromedio, @iOrdenExtra, @bExtra,
                            @iFolioInsc, @iTipoEval, @sPeriodoApro;
                    END;
    
                CLOSE cCursorCalif;
                DEALLOCATE cCursorCalif;
    /*------------------------------------------------------------------------------------------------------------------------------------*/

    
                INSERT  INTO @TRFinal
                        (
                         sPlantel
                       , sOfEduc
                       , sPlanEstudios
                       , idPlanEstudios
                       , idClasificacion
                       , sClasificacion
                       , bClasificacion
                       , sOpClasificacion
                       , iGrado
                       , sTipoPeriodo
                       , sGrupo
                       , idAsignatura
                       , sClave
                       , iCreditos
                       , sAsignatura
                       , bOficial
                       , bObligatoria
                       , iOportunidad
                       , bAsigAprobada
                       , iDecimales
                       , iOrden
                       , bPromedio
                       , dCalificacion
                       , sEstCalificacion
                       , dMinimaAprobatoria
                       , bEquivalencia
                       , sPeriodo
                       , bInscrito
                       , bAprobado
                       , sObservaciones
                       , bPromediar
                       , iOrdenUltimaOblig
                       , sCalificacion
                       , dFechaRegCal
                       , iOportunidadesEval1
                       , sAbreEval1
                       , sEvaluacion1
                       , iOportunidadesEval2
                       , sAbreEval2
                       , sEvaluacion2
                       , iOportunidadesEval3
                       , sAbreEval3
                       , sEvaluacion3
                       , sCalificacionLetra
                       , idPlantel
                       , sEvalR
                       , sEvalAbR
                       , iFolioInsc
					   , iGradoInsc
					   , sPromClasif
                        )
                SELECT TOP 1
                        t.sPlantel
                      , t.sOfEduc
                      , t.sPlanEstudios
                      , t.idPlanEstudios
                      , t.idClasificacion
                      , t.sClasificacion
                      , t.bClasificacion
                      , t.sOpClasificacion
                      , t.iGrado
                      , t.sTipoPeriodo
                      , t.sGrupo
                      , @idAsignatura--t.idAsignatura
                      , t.sClave
                      , t.iCreditos
                      , t.sAsignatura
                      , t.bOficial
                      , t.bObligatoria
                      , t.iOportunidad
                      , @bAprobadoR
                      , t.iDecimales
                      , t.iOrden
                      , t.bPromedio
                      , @dCalificacionR
                      , t.sEstCalificacion
                      , t.dMinimaAprobatoria
                      , t.bEquivalencia
                      , CASE WHEN @bAprobadoR = 1 THEN @sPeriodoAproR
                             ELSE @sPeriodoAlt
                        END --, t.sPeriodo
                      , t.bInscrito
                      , @bAprobadoR
                      , t.sObservaciones
                      , t.bPromediar
                      , t.iOrdenUltimaOblig
                      , @sCalificacionR
                      , t.dFechaRegCal
                      , @iCont1
                      , @sAbreEval1
                      , @sEvaluacion1
                      , @iCont2
                      , @sAbreEval2
                      , @sEvaluacion2
                      , @iCont3
                      , @sAbreEval3
                      , @sEvaluacion3
                      , @sCalifLetraR
                      , t.idPlantel
                      , @idEvalR
                      , @sEvalAbR
                      , @iFolioInscR
					  , t.iGradoInsc
					  , t.sPromClasif
                FROM    @TS AS t
                WHERE   t.idAsignatura = @idAsignatura
                        AND (
                             t.idEval = @idEvalR
                             OR @idEvalR IS NULL
                            )
                ORDER BY t.iOportunidad
                      , t.iOrden;

                SET @iCont1 = 0;
                SET @iCont2 = 0;
                SET @iCont3 = 0;
                SET @dCalificacionR = NULL;
                SET @sCalificacionR = NULL;
                SET @sAbreEval1 = NULL;
                SET @sEvaluacion1 = NULL;
                SET @sAbreEval2 = NULL;
                SET @sEvaluacion2 = NULL;
                SET @sAbreEval3 = NULL;
                SET @sEvaluacion3 = NULL;
                SET @sCalifLetraR = NULL;
                SET @idEvalR = NULL;
                SET @sEvalAbR = NULL;
                SET @bAprobadoR = 0;
    
      
                FETCH cCursor INTO @idAsignatura, @iGrado, @sAsignatura;
            END;

        CLOSE cCursor;
        DEALLOCATE cCursor;


--SELECT * FROM @TRFinal TF   
/*------------------------------------------------------------------------------------------------------------------------------------*/
        DECLARE @sPromedioGeneral VARCHAR(20) = CAST(CAST((SELECT
                                                              AVG(dCalificacion)
                                                           FROM
                                                              @TRFinal
                                                          ) AS DECIMAL(7, 1)) AS VARCHAR(50));
        DECLARE @idPlanEst SMALLINT = (SELECT TOP 1
                                                tf.idPlanEstudios
                                       FROM     @TRFinal AS tf
                                       WHERE    tf.idPlanEstudios IS NOT NULL
                                       ORDER BY tf.iGrado DESC
                                      ); 
        DECLARE @dPromedioGralPonderado DECIMAL(7, 3); 
        IF (SELECT  SUM(iCreditos)
            FROM    @TRFinal
           ) > 0
            BEGIN
                SELECT  @dPromedioGralPonderado = SUM(tf.dCalificacion
                                                      * tf.iCreditos)
                        / SUM(tf.iCreditos)
                FROM    @TRFinal AS tf
                WHERE   tf.sCalificacion IS NOT NULL;
            END;
        ELSE
            BEGIN
                SET @dPromedioGralPonderado = 0;
            END;
        
        SELECT DISTINCT
                TR.sPlantel
              , TR.sOfEduc
              , TR.sPlanEstudios
              , TR.idPlanEstudios
              , TR.idClasificacion
              , TR.sClasificacion
              , TR.bClasificacion
              , TR.sOpClasificacion
              , TR.iGrado
              , TR.sTipoPeriodo
              , TR.sGrupo
              , TR.idAsignatura
              , TR.sClave
              , TR.iCreditos
              , TR.sAsignatura
              , TR.bOficial
              , TR.bObligatoria
              , TR.iOportunidad
              , TR.bAsigAprobada
              , TR.iDecimales
              , TR.iOrden
              , TR.bPromedio
              , TR.dCalificacion
              , TR.sEstCalificacion
              , TR.dMinimaAprobatoria
              , TR.bEquivalencia
              , TR.sPeriodo
              , TR.bInscrito
              , TR.bAprobado
              , TR.sObservaciones
              , TR.bPromediar
              , TR.iOrdenUltimaOblig
              , CASE WHEN TRY_CAST(TR.sCalificacion AS DECIMAL(7, 3)) IS NULL
                     THEN TR.sCalificacion
                     ELSE CAST(CAST(CAST(TR.sCalificacion AS DECIMAL(7, 3)) AS INT) AS VARCHAR(50))
                END AS sCalificacion
              , TR.dFechaRegCal
              , TR.iOportunidadesEval1
              , TR.sAbreEval1
              , TR.sEvaluacion1
              , TR.iOportunidadesEval2
              , TR.sAbreEval2
              , TR.sEvaluacion2
              , TR.iOportunidadesEval3
              , TR.sAbreEval3
              , TR.sEvaluacion3
              , TR.sCalificacionLetra
              , TR.idPlantel
              , TR.dPromedioGeneral
              , TR.sEvalR
              , TR.sEvalAbR
              , TR.iOrdenAsig
              , TR.iFolioInsc
              , ISNULL(@dPromedioGralPonderado, 0) AS dPromedioGeneralPonderado
              , CASE WHEN TR.iFolioInsc > 0 THEN ci2.dPromedio
                     ELSE 0
                END AS PromedioGralInscripcion
              , CASE WHEN TR.iFolioInsc > 0 THEN ci2.dPromedioPonderado
                     ELSE 0
                END AS PromedioGralPonderadoInscripcion
              , TR.iOrdenClasif
			  
        FROM    (SELECT ISNULL(tf.sPlantel, '') AS sPlantel
                      , ISNULL(tf.sOfEduc, '') AS sOfEduc
                      , ISNULL(tf.sPlanEstudios, '') AS sPlanEstudios
                      , ISNULL(tf.idPlanEstudios, 0) AS idPlanEstudios
                      , ISNULL(tf.idClasificacion, 0) AS idClasificacion
                      , ISNULL(tf.sClasificacion, '') AS sClasificacion
                      , ISNULL(tf.bClasificacion, 0) AS bClasificacion
                      , ISNULL(tf.sOpClasificacion, '') AS sOpClasificacion
                      , ISNULL(tf.iGrado, 0) AS iGrado
                      , ISNULL(tf.sTipoPeriodo, '') AS sTipoPeriodo
                      , ISNULL(tf.sGrupo, '') AS sGrupo
                      , ISNULL(tf.idAsignatura, 0) AS idAsignatura
                      , ISNULL(tf.sClave, '') AS sClave
                      , ISNULL(tf.iCreditos, 0) AS iCreditos
                      , ISNULL(tf.sAsignatura, '') AS sAsignatura
                      , ISNULL(tf.bOficial, 0) AS bOficial
                      , ISNULL(tf.bObligatoria, 0) AS bObligatoria
                      , ISNULL(tf.iOportunidad, 0) AS iOportunidad
                      , ISNULL(tf.bAsigAprobada, 0) AS bAsigAprobada
                      , ISNULL(tf.iDecimales, 0) AS iDecimales
                      , ISNULL(tf.iOrden, 0) AS iOrden
                      , ISNULL(tf.bPromedio, 0) AS bPromedio
                      , ISNULL(tf.dCalificacion, 0) AS dCalificacion
                      , ISNULL(tf.sEstCalificacion, '') AS sEstCalificacion
                      , ISNULL(tf.dMinimaAprobatoria, 0) AS dMinimaAprobatoria
                      , ISNULL(tf.bEquivalencia, 0) AS bEquivalencia
                      , ISNULL(CASE WHEN tf.dCalificacion IS NULL THEN '' ELSE tf.sPeriodo END, '') AS sPeriodo
                      , ISNULL(tf.bInscrito, 0) AS bInscrito
                      , ISNULL(tf.bAprobado, 0) AS bAprobado
                      , ISNULL(tf.sObservaciones, '') AS sObservaciones
                      , ISNULL(tf.bPromediar, 0) AS bPromediar
                      , ISNULL(tf.iOrdenUltimaOblig, 0) AS iOrdenUltimaOblig
                      , ISNULL(tf.sCalificacion, '') AS sCalificacion
                      , ISNULL(tf.dFechaRegCal, 0) AS dFechaRegCal
                      , ISNULL(tf.iOportunidadesEval1, 0) AS iOportunidadesEval1
                      , ISNULL(tf.sAbreEval1, '') AS sAbreEval1
                      , ISNULL(tf.sEvaluacion1, '') AS sEvaluacion1
                      , ISNULL(tf.iOportunidadesEval2, 0) AS iOportunidadesEval2
                      , ISNULL(tf.sAbreEval2, '') AS sAbreEval2
                      , ISNULL(tf.sEvaluacion2, '') AS sEvaluacion2
                      , ISNULL(tf.iOportunidadesEval3, 0) AS iOportunidadesEval3
                      , ISNULL(tf.sAbreEval3, '') AS sAbreEval3
                      , ISNULL(tf.sEvaluacion3, '') AS sEvaluacion3
                      , ISNULL(tf.sCalificacionLetra, '') AS sCalificacionLetra
                      , ISNULL(tf.idPlantel, (SELECT TOP 1
                                                        tf.idPlantel
                                              FROM      @TRFinal AS tf
                                              WHERE     tf.idPlantel > 0
                                             )) AS idPlantel
                      , ISNULL(@sPromedioGeneral, 0) AS dPromedioGeneral
                      , ISNULL(tf.sEvalR, '') AS sEvalR
                      , ISNULL(tf.sEvalAbR, '') AS sEvalAbR
                      , cpe3.iOrden AS iOrdenAsig
                      , tf.iFolioInsc
                      , cca2.iOrden AS iOrdenClasif
					  , tf.iGradoInsc
					  , tf.sPromClasif
                 FROM   @TRFinal AS tf
                 LEFT JOIN dbo.Ctrl_PlanEstudios AS cpe3
                 ON     cpe3.idAsignatura = tf.idAsignatura
                        AND cpe3.idPlanEstudios = tf.idPlanEstudios
                 LEFT JOIN dbo.Cat_Optativas AS co
                 ON     co.idOptativa = cpe3.idOptativa
                 LEFT JOIN dbo.Cat_ClasifAsignaturas AS cca2
                 ON     cca2.idClasificacion = cpe3.idClasificacion
                 UNION ALL
                 SELECT DISTINCT '' AS sPlantel
                      , '' AS sOfEduc
                      , '' AS sPlanEstudios
                      , cpe.idPlanEstudios AS idPlanEstudios
                      , cca3.idClasificacion AS idClasificacion
                      , cca3.sClasificacion AS sClasificacion
                      , 0 AS bClasificacion
                      , '' AS sOpClasificacion
                      , cpe.iGrado AS iGrado
                      , cp.sUnidad AS sTipoPeriodo
                      , '' AS sGrupo
                      , ISNULL(co2.idOptativa, ca.idAsignatura) AS idAsignatura
                      , CASE WHEN co2.idOptativa IS NOT NULL THEN ''
                             ELSE ca.sClave
                        END AS sClave
                      , ca.iCreditos AS iCreditos
                      , ISNULL(CAST(co2.sOptativa AS VARCHAR(150)), CAST(ca.sAsignatura AS VARCHAR(150))) AS sAsignatura
                      , 0 AS bOficial
                      , ca.bObligatoria
                      , 0 AS iOportunidad
                      , 0 AS bAsigAprobada
                      , 0 AS iDecimales
                      , 0 AS iOrden
                      , 0 AS bPromedio
                      , 0 AS dCalificacion
                      , '' AS sEstCalificacion
                      , 0 AS dMinimaAprobatoria
                      , 0 AS bEquivalencia
                      , '' AS sPeriodo
                      , 0 AS bInscrito
                      , 0 AS bAprobado
                      , '' AS sObservaciones
                      , 0 AS bPromediar
                      , 0 AS iOrdenUltimaOblig
                      , '' AS sCalificacion
                      , 0 AS dFechaRegCal
                      , 0 AS iOportunidadesEval1
                      , '' AS sAbreEval1
                      , '' AS sEvaluacion1
                      , 0 AS iOportunidadesEval2
                      , '' AS sAbreEval2
                      , '' AS sEvaluacion2
                      , 0 AS iOportunidadesEval3
                      , '' AS sAbreEval3
                      , '' AS sEvaluacion3
                      , '' AS sCalificacionLetra
                      , (SELECT TOP 1
                                CI.idPlantel
                         FROM   dbo.Cat_Alumnos ca
                         INNER JOIN dbo.Ctrl_Inscripciones CI
                         ON     CI.idAlumno = ca.idAlumno
                         WHERE  ca.sMatricula = @sMatricula
                        ) AS idPlantel
                      , ISNULL(@sPromedioGeneral, 0) AS dPromedioGeneral
                      , '' AS sEvalR
                      , '' AS sEvalAbR
                      , cpe.iOrden AS iOrdenAsig
                      , RPI.iFolioInsc AS iFolioInsc
                      , cca3.iOrden AS iOrdenClasif
					  , 0 AS iGradoInsc
					  , '' AS sPromClasif 
                 FROM   dbo.Ctrl_PlanEstudios AS cpe
                 LEFT JOIN dbo.Cat_Asignaturas AS ca
                 ON     ca.idAsignatura = cpe.idAsignatura
                 LEFT JOIN dbo.Cat_Optativas AS co2
                 ON     co2.idOptativa = cpe.idOptativa
                 LEFT JOIN dbo.Cat_PlanEstudios AS cpe2
                 ON     cpe2.idPlanEstudios = cpe.idPlanEstudios
                 LEFT JOIN dbo.Cat_Planes AS cp
                 ON     cp.idPlan = cpe2.idPlan
                 LEFT JOIN dbo.Cat_ClasifAsignaturas AS cca3
                 ON     cca3.idClasificacion = cpe.idClasificacion
                 OUTER APPLY (SELECT TOP 1
                                        ciG.dPromedio
                                      , ciG.dPromedioPonderado
                                      , ciG.iFolio AS iFolioInsc
                              FROM      dbo.Ctrl_Inscripciones AS ciG
                              INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS ctaG
                              ON        ctaG.iFolioInscripcion = ciG.iFolio
                              INNER JOIN dbo.Ctrl_PlanEstudios AS cpeG
                              ON        cpeG.idAsignatura = ctaG.idAsignatura
                                        AND cpeG.idPlanEstudios = ctaG.idPlanEstudios
                              WHERE     cpeG.iGrado = cpe.iGrado
                             ) AS RPI
                 WHERE  cpe.idPlanEstudios = @idPlanEst
                        --AND cpe.iGrado >= (SELECT DISTINCT TOP 1			>>>>>> 23/May/2017 -  Se comenta para mostrar tpdas las asignaturas sin importar si éstas no fueron tomadas en un grado menor al actual del alumno
                        --                            tf.iGrado
                        --                   FROM     @TRFinal AS tf
                        --                   ORDER BY tf.iGrado DESC
                        --                  )
                        AND ca.idAsignatura NOT IN (SELECT DISTINCT
                                                            tf2.idAsignatura
                                                    FROM    @TRFinal AS tf2)
						AND (cpe.idOptativa NOT IN ( SELECT DISTINCT
															cpe3.idOptativa
													FROM @TRFinal AS tf3
													JOIN dbo.Ctrl_PlanEstudios AS cpe3 ON cpe3.idAsignatura = tf3.idAsignatura
													WHERE cpe3.idOptativa IS NOT NULL
												  ) 
							  OR cpe.idOptativa IS NULL )
                            --AND ca.bObligatoria = 1
                 UNION ALL
                 SELECT '' AS sPlantel
                      , '' AS sOfEduc
                      , '' AS sPlanEstudios
                      , cpe.idPlanEstudios AS idPlanEstudios
                      , cca4.idClasificacion AS idClasificacion
                      , cca4.sClasificacion AS sClasificacion
                      , 0 AS bClasificacion
                      , '' AS sOpClasificacion
                      , cpe.iGrado AS iGrado
                      , cp.sUnidad AS sTipoPeriodo
                      , '' AS sGrupo
                      , ISNULL(co.idOptativa, cpe.idAsignatura) AS idAsignatura
                      , CASE WHEN co.idOptativa IS NOT NULL THEN ''
                             ELSE ca.sClave
                        END AS sClave
                      , ca.iCreditos AS iCreditos
                      , ISNULL(CAST(co.sOptativa AS VARCHAR(150)), CAST(ca.sAsignatura AS VARCHAR(150))) AS sAsignatura
                      , 0 AS bOficial
                      , bObligatoria
                      , 0 AS iOportunidad
                      , 0 AS bAsigAprobada
                      , 0 AS iDecimales
                      , 0 AS iOrden
                      , 0 AS bPromedio
                      , 0 AS dCalificacion
                      , '' AS sEstCalificacion
                      , 0 AS dMinimaAprobatoria
                      , 0 AS bEquivalencia
                      , '' AS sPeriodo
                      , 0 AS bInscrito
                      , 0 AS bAprobado
                      , '' AS sObservaciones
                      , 0 AS bPromediar
                      , 0 AS iOrdenUltimaOblig
                      , '' AS sCalificacion
                      , 0 AS dFechaRegCal
                      , 0 AS iOportunidadesEval1
                      , '' AS sAbreEval1
                      , '' AS sEvaluacion1
                      , 0 AS iOportunidadesEval2
                      , '' AS sAbreEval2
                      , '' AS sEvaluacion2
                      , 0 AS iOportunidadesEval3
                      , '' AS sAbreEval3
                      , '' AS sEvaluacion3
                      , '' AS sCalificacionLetra
                      , (SELECT TOP 1
                                CI.idPlantel
                         FROM   dbo.Cat_Alumnos ca
                         INNER JOIN dbo.Ctrl_Inscripciones CI
                         ON     CI.idAlumno = ca.idAlumno
                         WHERE  ca.sMatricula = @sMatricula
                        ) AS idPlantel
                      , ISNULL(@sPromedioGeneral, 0) AS dPromedioGeneral
                      , '' AS sEvalR
                      , '' AS sEvalAbR
                      , cpe.iOrden AS iOrdenAsig
                      , RPI.iFolioInsc AS iFolioInsc
                      , cca4.iOrden AS iOrdenClasif
					  , 0 AS iGradoInsc
					  , '' AS sPromClasif
                 FROM   dbo.Ctrl_PlanEstudios AS cpe
                 LEFT JOIN dbo.Cat_Asignaturas AS ca
                 ON     ca.idAsignatura = cpe.idAsignatura
                 LEFT JOIN dbo.Cat_Optativas AS co
                 ON     co.idOptativa = cpe.idOptativa
                 LEFT JOIN dbo.Cat_PlanEstudios AS cpe2
                 ON     cpe2.idPlanEstudios = cpe.idPlanEstudios
                 LEFT JOIN dbo.Cat_Planes AS cp
                 ON     cp.idPlan = cpe2.idPlan
                 LEFT JOIN dbo.Cat_ClasifAsignaturas AS cca4
                 ON     cca4.idClasificacion = cpe.idClasificacion
                 OUTER APPLY (SELECT TOP 1
                                        ciG.dPromedio
                                      , ciG.dPromedioPonderado
                                      , ciG.iFolio AS iFolioInsc
                              FROM      dbo.Ctrl_Inscripciones AS ciG
                              INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS ctaG
                              ON        ctaG.iFolioInscripcion = ciG.iFolio
                              INNER JOIN dbo.Ctrl_PlanEstudios AS cpeG
                              ON        cpeG.idAsignatura = ctaG.idAsignatura
                                        AND cpeG.idPlanEstudios = ctaG.idPlanEstudios
                              WHERE     cpeG.iGrado = cpe.iGrado
                             ) AS RPI
                 WHERE  cpe.idPlanEstudios = @idPlanEst
                        AND cpe.iGrado < (SELECT DISTINCT TOP 1
                                                    tf.iGrado
                                          FROM      @TRFinal AS tf
                                          ORDER BY  tf.iGrado DESC
                                         )
                        AND ca.idAsignatura NOT IN (SELECT DISTINCT
                                                            tf2.idAsignatura
                                                    FROM    @TRFinal AS tf2)
                        AND ca.bObligatoria = 1
                ) TR
        LEFT JOIN dbo.Ctrl_Inscripciones AS ci2
        ON      TR.iFolioInsc = ci2.iFolio
        ORDER BY TR.iGrado
              , TR.iOrdenAsig
              , TR.sAsignatura;

    END