FITOMN of AM v3
5/17/2017 - 9:13 PM

BIT/IAM-1622 - Se agrega validación de idPlanEstudios igual a cero en comprobación de asignatura optativa

BIT/IAM-1622 - Se agrega validación de idPlanEstudios igual a cero en comprobación de asignatura optativa


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

CREATE PROCEDURE dbo.spPanelAlumnoValidaInscAsignatura
/* --------------------------------------------------------------------------------------------------------------
 FECHA		|  AUTOR				|  DESCRIPCION
00/00/2015  | Leonardo Dominguez	|
22/02/2017	| Luis Bernal			|
16/05/2017	| Luis Bernal			| BIT/IAM-1626 - INSCRIPCIONES EN LÍNEA EMPALMES
17/05/2017  | Luis Bernal     | BIT/IAM-1622 - INSCRIPCIONES EN LÍNEA OPTATIVAS
--------------------------------------------------------------------------------------------------------------*/
--@Variables INT
    @idAlumno INT ,
    @iFolioPeriodo BIGINT ,
    @idOfEduc SMALLINT ,
    @idPlanEstudios SMALLINT ,
    @idGrupo SMALLINT ,
    @idAula SMALLINT ,
    @idProfesor SMALLINT ,
    @idAsignatura SMALLINT
AS
    BEGIN
        SET NOCOUNT ON;
        SET LANGUAGE spanish;
        SET DATEFORMAT MDY;
   --CONTENIDO PROCEDIMIENTO


   ---horarios del alumno en el periodo escolar

     /*------------------------------------------------------------------------------------------------------------------------------------*/
        DECLARE @dHoraInicio INT ,
            @dHoraFin INT ,
            @iDia TINYINT ,
            @dHoraInicio2 INT ,
            @dHoraFin2 INT ,
            @iDia2 TINYINT;
               
        DECLARE cCursor CURSOR LOCAL
        FOR
            SELECT  CAST(SUBSTRING(CAST(CAST(chc.dHoraInicio AS TIME) AS VARCHAR(25)), 1, 2) AS INT) AS dHoraInicio ,
                    CAST(SUBSTRING(CAST(CAST(chc.dHoraFin AS TIME) AS VARCHAR(25)), 1, 2) AS INT) AS dHoraFin ,
                    cgcd.iDia
            FROM    dbo.Cat_HorarioClase AS chc
                    LEFT JOIN dbo.Ctrl_GrupoClasesDet AS cgcd ON cgcd.idHorarioClase = chc.idHorarioClase
                    INNER JOIN dbo.Ctrl_GrupoClases AS cgc ON cgc.iFolio = cgcd.iFolioGrupoClases
                                                              AND cgc.iFolioPeriodo = @iFolioPeriodo
                    INNER JOIN dbo.Ctrl_Grupos AS cg ON cg.idGrupo = cgc.idGrupo
                    INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cgc.idAsignatura
                    INNER JOIN dbo.Cat_Profesores AS cp ON cp.idProfesor = cgc.idProfesor
                    INNER JOIN dbo.Cat_Aulas AS ca2 ON ca2.idAula = cgcd.idAula
                    INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.idAsignatura = cgc.idAsignatura
                                                                       AND cta.idGrupo = cg.idGrupo
                                                                       AND cta.idPlanEstudios = cg.idPlanEstudios 
                    INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
                                                               AND ci.iFolioPeriodo = cgc.iFolioPeriodo
            WHERE   ci.idAlumno = @idAlumno
                    AND ci.idOfEduc = @idOfEduc
                    AND (cg.idPlanEstudios = @idPlanEstudios OR @idPlanEstudios = 0);
		
 ---horario de la materia que quiere inscribirse
        SELECT TOP 1
                @dHoraInicio2 = CAST(SUBSTRING(CAST(CAST(chc.dHoraInicio AS TIME) AS VARCHAR(25)), 1, 2) AS INT) ,
                @dHoraFin2 = CAST(SUBSTRING(CAST(CAST(chc.dHoraFin AS TIME) AS VARCHAR(25)), 1, 2) AS INT) ,
                @iDia2 = cgcd.iDia
        FROM    dbo.Cat_HorarioClase AS chc
                LEFT JOIN dbo.Ctrl_GrupoClasesDet AS cgcd ON cgcd.idHorarioClase = chc.idHorarioClase
                INNER JOIN dbo.Ctrl_GrupoClases AS cgc ON cgc.iFolio = cgcd.iFolioGrupoClases
                                                          AND cgc.idGrupo = @idGrupo
                                                          AND cgc.iFolioPeriodo = @iFolioPeriodo
                INNER JOIN dbo.Ctrl_Grupos AS cg ON cg.idGrupo = cgc.idGrupo
                INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cgc.idAsignatura
                INNER JOIN dbo.Cat_Profesores AS cp ON cp.idProfesor = cgc.idProfesor
                INNER JOIN dbo.Cat_Aulas AS ca2 ON ca2.idAula = cgcd.idAula
                --INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.idAsignatura = cgc.idAsignatura						>>>>>>> 16/May/2017 - Se comenta para obtener el horario y día de la materia a inscribir <<<<<<<
                --                                                   AND cta.idGrupo = cg.idGrupo
                --                                                   AND cta.idPlanEstudios = cg.idPlanEstudios
                --INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
                --                                           AND ci.iFolioPeriodo = cgc.iFolioPeriodo
        WHERE   ca2.idAula = @idAula
                AND cgc.idProfesor = @idProfesor
                AND ca.idAsignatura = @idAsignatura
                --AND ci.idOfEduc = @idOfEduc
                AND (cg.idPlanEstudios = @idPlanEstudios OR @idPlanEstudios = 0)
        ORDER BY chc.dHoraInicio ,
                chc.dHoraFin ,
                iDia;
				
        DECLARE @bSeEmpalmaConOtraAsignatura BIT= 0;
             ---si coincide en alguna materia del alumno regresa un true en el empalme
        OPEN cCursor;
        FETCH cCursor INTO @dHoraInicio, @dHoraFin, @iDia;
         
        WHILE ( @@FETCH_STATUS = 0 )
            BEGIN
             	
                IF ( @dHoraInicio2 >= @dHoraInicio AND @dHoraInicio2 < @dHoraFin )
                    AND ( @iDia = @iDia2 )
                    BEGIN
                        SET @bSeEmpalmaConOtraAsignatura = 1;
                    END;
                ELSE
                    BEGIN
                        IF ( @dHoraFin2 BETWEEN @dHoraInicio AND @dHoraFin )
                            AND ( @iDia = @iDia2 )
                            BEGIN
                                SET @bSeEmpalmaConOtraAsignatura = 1;
                            END;
                    END;


                FETCH cCursor INTO @dHoraInicio, @dHoraFin, @iDia;
            END;
         
        CLOSE cCursor;
        DEALLOCATE cCursor;
         /*------------------------------------------------------------------------------------------------------------------------------------*/


--DECLARE @idAlumno INT = 106
--  , @iFolioPeriodo BIGINT = 7
--  , @idOfEduc SMALLINT = 1
--  , @idPlanEstudios SMALLINT = 0
--  , @idGrupo SMALLINT = 0
--  , @idAula SMALLINT = 0
--  , @idProfesor SMALLINT = 0
--  , @idAsignatura SMALLINT = 0

--SET NOCOUNT ON;
--SET LANGUAGE spanish
   --CONTENIDO PROCEDIMIENTO
        DECLARE @iResultado TINYINT = 0 ,
            @sResultado VARCHAR(150) = '';
		
        DECLARE @iFolioInscUlt BIGINT ,
            @idPlanEst SMALLINT ,
            @idPlantel SMALLINT;
        SELECT TOP 1
                @iFolioInscUlt = ci.iFolio
        FROM    dbo.Ctrl_Inscripciones AS ci
                INNER JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
        WHERE   ci.idAlumno = @idAlumno
                AND ci.iFolioPeriodo = @iFolioPeriodo
                AND ci.idOfEduc = @idOfEduc
        ORDER BY cp.iAnioInicial DESC ,
                cp.iMesInicial DESC ,
                cp.iCicloEscolar ASC;



  --IF 0 < ( SELECT COUNT(*)
  --               FROM   dbo.Ctrl_Inscripciones AS ci
  --                      INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
  --                      LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
  --               WHERE  ci.iFolio = @iFolioInscUlt
  --                      AND ci.idOfEduc = @idOfEduc
  --             )
  --          BEGIN

  --              SELECT TOP 1
  --                      @idPlanEst = cta.idPlanEstudios ,
  --                      @idPlantel = ci.idPlantel
  --              FROM    dbo.Ctrl_Inscripciones AS ci
  --                      INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
  --                      LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
  --              WHERE   ci.iFolio = @iFolioInscUlt
  --                      AND ci.idOfEduc = @idOfEduc
  --              ORDER BY cp.iAnioInicial DESC ,
  --                      cp.iMesInicial DESC ,
  --                      cp.iCicloEscolar ASC
  --          END
  --      ELSE
  --          BEGIN
  --              SELECT TOP 1
  --                      @idPlanEst = cta.idPlanEstudios ,
  --                      @idPlantel = ci.idPlantel
  --              FROM    dbo.Ctrl_Inscripciones AS ci
  --                      INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON ci.iFolio = cta.iFolioInscripcion
  --                      LEFT JOIN dbo.Ctrl_Periodos AS cp ON cp.iFolio = ci.iFolioPeriodo
  --              WHERE   ci.iFolio <> @iFolioInscUlt
  --                      AND ci.idOfEduc = @idOfEduc
  --              ORDER BY cp.iAnioInicial DESC ,
  --                      cp.iMesInicial DESC ,
  --                      cp.iCicloEscolar ASC

  --          END

  --      IF @idPlanEst IS NULL
  --          BEGIN

    ----REVISAR SI EXISTE PLAN DE ESTUDIOS
  --              IF 0 < ( SELECT COUNT(cta.idPlanEstudios)
  --                       FROM   dbo.Ctrl_Inscripciones AS ci
  --                              INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.iFolioInscripcion = ci.iFolio
  --                       WHERE  ci.iFolioPeriodo = @iFolioPeriodo
  --                              AND ci.idAlumno = @idAlumno
  --                              AND ci.idOfEduc = @idOfEduc
  --                     )
  --                  BEGIN
  --                      SELECT  @idPlanEst = cta.idPlanEstudios
  --                      FROM    dbo.Ctrl_Inscripciones AS ci
  --                              INNER JOIN dbo.Ctrl_TrayectoriaAcademica AS cta ON cta.iFolioInscripcion = ci.iFolio
  --                      WHERE   ci.iFolioPeriodo = @iFolioPeriodo
  --                              AND ci.idAlumno = @idAlumno
  --                              AND ci.idOfEduc = @idOfEduc
  --                  END
  --              ELSE
  --                  BEGIN
    --  --REVISAR SI EXISTE PLAN DE ESTUDIOS

  --                      SELECT TOP 1
  --                               cpe.idPlanEstudios
  --                      FROM    dbo.Ctrl_Inscripciones AS ci
  --                              INNER JOIN dbo.Cat_PlanEstudios AS cpe ON cpe.idOfEduc = ci.idOfEduc
  --                      WHERE   ci.iFolioPeriodo = @iFolioPeriodo
  --                              AND ci.idAlumno = @idAlumno
  --                              AND ci.idOfEduc = @idOfEduc
  --                      ORDER BY idPlanEstudios DESC
  --                  END
  --          END

        IF @bSeEmpalmaConOtraAsignatura = 0
            BEGIN

                SELECT  @idPlanEst = foupep.idPlanEstudios,
                        @idPlantel = foupep.idPlantel
                FROM    dbo.fnObtenerUltimoPlanEstPlantel(@idAlumno, @idOfEduc, @iFolioPeriodo) AS foupep;

  ----Checa tipo de cupo a validar (Por Grupo o por Aula)
				DECLARE @bTipoCupo TINYINT, @idTipoHorario TINYINT, @cupoActual INT

				SELECT @bTipoCupo = CASE WHEN csp.sValor = '0' THEN 0 ELSE 1 END
                FROM     dbo.Cat_SistemaParametros AS csp
                WHERE    csp.idParametro = 48

				IF @bTipoCupo = 0 --Por Aula
					BEGIN
						SELECT @idTipoHorario = cg.idTipoHorario FROM dbo.Ctrl_Grupos AS cg WHERE cg.idGrupo = @idGrupo

						SELECT ca.iCapacidad - dbo.fnObtenerInscritosAulaConHorarios(@iFolioPeriodo, @idPlanEst, @idPlantel, @idAsignatura, @idTipoHorario, @idAula) AS iCupoActualAula
						FROM   dbo.Cat_Aulas AS ca
						WHERE  CA.idAula = @idAula
					END
				ELSE			  --Por Grupo
					BEGIN
						SELECT @cupoActual = (cg.iCapacidad - dbo.fnObtenerInscritosGrupo(@iFolioPeriodo, @idPlanEst, @idPlantel, @idAsignatura, @idGrupo))
						FROM dbo.Ctrl_Grupos cg
						WHERE cg.idGrupo = @idGrupo
					END

  ----VALIDANDO CUPO EN GRUPO
				IF @cupoActual > 0
					BEGIN

  ----VALIDANDO SI YA EXISTE UNA INSCRIPCION EN LA MISMA MATERIA

						IF 0 = ( SELECT COUNT(*)
								 FROM   dbo.Ctrl_TrayectoriaAcademica AS cta
										INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
								 WHERE  ci.iFolioPeriodo = @iFolioPeriodo
										AND cta.idAsignatura = @idAsignatura
										AND ci.idOfEduc = @idOfEduc
										AND ci.idAlumno = @idAlumno
							   )

							   -------------revisa que no tenga una inscripcion en la misma optativa
							   AND 0 = (SELECT COUNT(*)
								 FROM   dbo.Ctrl_TrayectoriaAcademica AS cta
										INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
										INNER JOIN dbo.Ctrl_PlanEstudios AS cpe ON cpe.idAsignatura = cta.idAsignatura AND cpe.idPlanEstudios = cta.idPlanEstudios
								 WHERE  ci.iFolioPeriodo = @iFolioPeriodo
										--AND cta.idAsignatura = @idAsignatura
										AND cpe.idOptativa IN ( SELECT DISTINCT cpe.idOptativa FROM dbo.Ctrl_PlanEstudios AS cpe 
																	WHERE cpe.idAsignatura = @idAsignatura 
																		AND (cpe.idPlanEstudios = @idPlanEstudios OR @idPlanEstudios = 0))
										AND ci.idOfEduc = @idOfEduc
										AND ci.idAlumno = @idAlumno
										)
							BEGIN

--------Valida creditos maximos y minimos

								DECLARE @iCreditosTotales DECIMAL(7, 3) ,
									@iCreditosAsig DECIMAL(7, 3);
								SELECT  @iCreditosTotales = SUM(ca.iCreditos)
								FROM    dbo.Ctrl_TrayectoriaAcademica AS cta
										INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cta.idAsignatura
								WHERE   cta.iFolioInscripcion = @iFolioInscUlt;

								IF ( @iCreditosTotales + @iCreditosAsig ) > ( SELECT    ISNULL(cpe.iCreMaxXGrado, 99999999)
																			  FROM      dbo.Cat_PlanEstudios AS cpe
																			  WHERE     cpe.idPlanEstudios = @idPlanEstudios
																			)
									BEGIN
										SET @iResultado = 1;
										SET @sResultado = 'Superaste los creditos permitidos por grado.';
									END;


								DECLARE @asigActuales INT;

								SELECT  @asigActuales = COUNT(*)
								FROM    ( SELECT DISTINCT
													ca.idAsignatura
										  FROM      dbo.Ctrl_TrayectoriaAcademica AS cta
													INNER JOIN dbo.Ctrl_Inscripciones AS ci ON ci.iFolio = cta.iFolioInscripcion
													INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cta.idAsignatura
													INNER JOIN dbo.Ctrl_PlanEstudios AS cpe ON cpe.idAsignatura = cta.idAsignatura
																							   AND cpe.idPlanEstudios = cta.idPlanEstudios
													LEFT JOIN dbo.Ctrl_NucleosCantidadAsignaturas AS cnca ON cnca.idNucleo = cpe.idNucleo
										  WHERE     ci.idAlumno = @idAlumno
													AND cnca.idNucleo = ( SELECT DISTINCT
																					cpe33.idNucleo
																		  FROM      dbo.Ctrl_PlanEstudios AS cpe33
																		  WHERE     cpe33.idPlanEstudios = @idPlanEst
																					AND cpe33.idAsignatura = @idAsignatura
																					AND ci.idPlantel = @idPlantel
																		)
										) TR;


								DECLARE @sNucleo VARCHAR(50) = '' ,
									@bSuperaAsig BIT = 0;


								SELECT DISTINCT
										@bSuperaAsig = ( CASE WHEN cnca.iMaxAsig >= @asigActuales THEN 0
															  ELSE 1
														 END ) ,
										@sNucleo = cn.sNucleo
								FROM    dbo.Ctrl_NucleosCantidadAsignaturas AS cnca
										INNER JOIN dbo.Cat_Nucleos AS cn ON cn.idNucleo = cnca.idNucleo
								WHERE   cnca.idNucleo = ( SELECT DISTINCT
																	cpe33.idNucleo
														  FROM      dbo.Ctrl_PlanEstudios AS cpe33
														  WHERE     cpe33.idPlanEstudios = @idPlanEst
																	AND cpe33.idAsignatura = @idAsignatura
																	AND cnca.idPlantel = @idPlantel
														);

								IF 1 = @bSuperaAsig
									BEGIN
										SET @iResultado = 2;
										SET @sResultado = 'Superaste la cantidad de materias para el núcleo ' + UPPER(@sNucleo) + '.';
									END;

							END;
						ELSE
							BEGIN
								SET @iResultado = 3;
								SET @sResultado = 'Ya cuentas con una inscripción a esta materia.';
							END;
				END
				ELSE
					BEGIN
						SET @iResultado = 5;
						SET @sResultado = 'Se ha llegado al cupo máximo permitido del grupo.';
					END
            END;
        ELSE
            BEGIN
                SET @iResultado = 4;
                SET @sResultado = 'La materia se empalma con otra asignatura.';
            END;


			IF @iResultado = 0 --VALIDA QUE NO HAYA APROBADO LA MATERIA EN OTRO GRADO
			AND 0 < (
			SELECT COUNT(*) FROM (
				SELECT CASE WHEN  ctad.dCalificacion >=  cfe3.dMinimaAprobatoria THEN 1 ELSE 0 END AS bAprobada, cta.idAsignatura FROM dbo.Ctrl_TrayectoriaAcademica AS cta
				INNER JOIN dbo.Ctrl_TrayectoriaAcademicaDet AS ctad 
					ON ctad.iFolioTrayectoria = cta.iFolio
				INNER JOIN dbo.Ctrl_Inscripciones AS ci 
					ON ci.iFolio = cta.iFolioInscripcion
				INNER JOIN dbo.Ctrl_PlanEstudios AS cpe 
					ON cpe.idAsignatura = cta.idAsignatura 
						AND cpe.idPlanEstudios = cta.idPlanEstudios
				INNER JOIN dbo.Cat_PlanEstudios AS cpe2 
					ON cpe2.idPlanEstudios = cpe.idPlanEstudios
				INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe 
					ON cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,cpe2.idFormEvaluacion)
						AND cfe.idEvaluacion = ctad.idEvaluacion
				INNER JOIN dbo.Cat_FormEvaluacion AS cfe3 ON cfe3.idFormEvaluacion = cfe.idFormEvaluacion
				WHERE ci.idAlumno = @idAlumno
				AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
				AND cta.idAsignatura = @idAsignatura
				--AND ci.iFolioPeriodo = @iFolioPeriodo
				AND cfe.bPromedioGral = 1
				) TR
				WHERE TR.bAprobada = 1
				)
			BEGIN
				SET @iResultado = 6
				SET @sResultado = 'No te puedes inscribir anteriormente has aprobado la materia.'
			END

			
            IF @iResultado = 0 ---VALIDA LA SERIACION DE LA MATERIA
                AND 0 < (SELECT COUNT(cpe.idAsignatura)
                         FROM   dbo.Ctrl_PlanEstudios AS cpe
                         INNER JOIN dbo.Ctrl_SeriacionAsignaturas AS csa
                         ON     csa.idAsignatura = cpe.idAsignatura
                                AND csa.idPlanEstudios = cpe.idPlanEstudios
                         INNER JOIN dbo.Cat_PlanEstudios AS cpe2
                         ON     cpe2.idPlanEstudios = cpe.idPlanEstudios
                         INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe
                         ON     cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,
                                                              cpe2.idFormEvaluacion)
                         WHERE  cpe.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
                                AND csa.idAsignatura = @idAsignatura
                                AND cfe.bPromedioGral = 1
                                AND csa.bSimultanea = 0
                        )
                AND (
                     (0 = (SELECT   COUNT(*)
                           FROM     dbo.Ctrl_TrayectoriaAcademica AS cta
                           INNER JOIN dbo.Ctrl_Inscripciones AS ci
                           ON       ci.iFolio = cta.iFolioInscripcion
                           INNER JOIN dbo.Ctrl_SeriacionAsignaturas AS csa
                           ON       csa.idAsignatura = cta.idAsignatura
                                    AND csa.idPlanEstudios = cta.idPlanEstudios
                           WHERE    ci.idAlumno = @idAlumno
                                    AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
                                    AND csa.idAsignatura = @idAsignatura
                          ))
                     OR 0 < (SELECT COUNT(*)
                             FROM   (SELECT CASE WHEN ctad.dCalificacion >= cfe2.dMinimaAprobatoria
                                                 THEN 1
                                                 ELSE 0
                                            END AS bAprobada
                                          , cta.idAsignatura
                                     FROM   dbo.Ctrl_TrayectoriaAcademica AS cta
                                     INNER JOIN dbo.Ctrl_TrayectoriaAcademicaDet
                                            AS ctad
                                     ON     ctad.iFolioTrayectoria = cta.iFolio
                                     INNER JOIN dbo.Ctrl_Inscripciones AS ci
                                     ON     ci.iFolio = cta.iFolioInscripcion
                                     INNER JOIN dbo.Ctrl_PlanEstudios AS cpe
                                     ON     cpe.idAsignatura = cta.idAsignatura
                                            AND cpe.idPlanEstudios = cta.idPlanEstudios
                                     INNER JOIN dbo.Cat_PlanEstudios AS cpe2
                                     ON     cpe2.idPlanEstudios = cpe.idPlanEstudios
                                     INNER JOIN dbo.Ctrl_FormEvaluacion AS cfe
                                     ON     cfe.idFormEvaluacion = ISNULL(cpe.idFormEvaluacion,
                                                              cpe2.idFormEvaluacion)
                                            AND cfe.idEvaluacion = ctad.idEvaluacion
									INNER JOIN dbo.Cat_FormEvaluacion AS cfe2 ON cfe2.idFormEvaluacion = cfe.idFormEvaluacion
                                     INNER JOIN dbo.Ctrl_SeriacionAsignaturas
                                            AS csa
                                     ON     csa.idAsignatura = cta.idAsignatura
                                            AND csa.idPlanEstudios = cta.idPlanEstudios
                                     WHERE  ci.idAlumno = @idAlumno
                                            AND cta.idPlanEstudios = (CASE WHEN @idPlanEstudios > 0 THEN @idPlanEstudios ELSE @idPlanEst END)
                                            AND csa.idAsignatura = @idAsignatura
				--AND ci.iFolioPeriodo = @iFolioPeriodo
                                            AND cfe.bPromedioGral = 1
                                            AND csa.bSimultanea = 0
                                    ) TR
                             WHERE  TR.bAprobada = 0
                            )
                    )
                BEGIN
                    SET @iResultado = 7;
                    SET @sResultado = 'No puedes agregar esta materia ya que te falta aprobar alguna de las que van searidas.';
                END;

        SELECT  @iResultado AS iResultado ,
                @sResultado AS sResultado;
    END;