BIT/IAM-1922 - spListaInfoRVOEPlantelPlanEstudios
----------------------------INICIO PROCEDIMIENTO ------------------------
IF OBJECT_ID('spListaInfoRVOEPlantelPlanEstudios', 'P') IS NOT NULL
DROP PROC spListaInfoRVOEPlantelPlanEstudios
GO
CREATE PROCEDURE [dbo].[spListaInfoRVOEPlantelPlanEstudios]
@idPlantel SMALLINT ,
@idPlanEst SMALLINT ,
@idOfEduc SMALLINT
/* --------------------------------------------------------------------------------------------------------------
FECHA | AUTOR | DESCRIPCION
01/ago./2017 | Luis Bernal | BIT/IAM-1922 - Se corrige el listado de Plan de Estudios y oferta educativa que se consultaba
--------------------------------------------------------------------------------------------------------------*/
AS
BEGIN
SET NOCOUNT ON;
SET LANGUAGE spanish;
SET DATEFORMAT MDY;
--CONTENIDO PROCEDIMIENTO
DECLARE @iTotalCredito INT = 0;
SELECT @iTotalCredito = SUM(ca.iCreditos)
FROM dbo.Ctrl_PlanEstudios AS cpe
INNER JOIN dbo.Cat_Asignaturas AS ca ON ca.idAsignatura = cpe.idAsignatura
WHERE cpe.idPlanEstudios = @idPlanEst
AND cpe.idOptativa IS NULL;
SET @iTotalCredito = @iTotalCredito
+ ( SELECT ISNULL(SUM(TR.iCreditos), 0)
FROM ( SELECT DISTINCT
cpe.idOptativa ,
( SELECT TOP 1
ca1.iCreditos
FROM dbo.Ctrl_PlanEstudios AS cpe1
INNER JOIN dbo.Cat_Asignaturas
AS ca1 ON ca1.idAsignatura = cpe1.idAsignatura
WHERE cpe1.idOptativa IS NOT NULL
AND cpe1.idOptativa = cpe.idOptativa
ORDER BY cpe1.idPlanEstudios ,
ca1.iCreditos DESC
) AS iCreditos
FROM dbo.Ctrl_PlanEstudios AS cpe
WHERE cpe.idOptativa IS NOT NULL
AND cpe.idPlanEstudios = @idPlanEst
) TR
);
SELECT TOP 1
cp.idPlantel ,
cp.sNombre AS sPlantel ,
coe.idOfEduc ,
coe.sNombre AS sOfEduc ,
cpe.idPlanEstudios ,
cpe.sNombre AS sPlanEstudios ,
cpoe.sAutCentroT AS sClaveCentroTrabajo ,
ISNULL(cpe.sRVOE,'') AS sNoRVOE ,
cpe.dRVOE AS dFechaRVOE ,
ISNULL(cpe.sCveRegistro,'') AS sClaveRVOE ,
ISNULL(ISNULL(cper.sRespFirmaCertif, cpe.sRespFirmaCertif), '') AS sRespFirmaCertif ,
ISNULL(ISNULL(cper.sPuestoRespFirmaCertif,
cpe.sPuestoRespFirmaCertif), '') AS sPuestoRespFirmaCertif ,
ISNULL(ISNULL(cper.sRespExternoFirmaCertif,
cpe.sRespExternoFirmaCertif), '') AS sRespFirmaCertifExterno ,
ISNULL(ISNULL(cper.sPuestoRespExternoFirmaCertif,
cpe.sPuestoRespExternoFirmaCertif), '') AS sPuestoRespFirmaCertifExterno ,
ISNULL(( SELECT COUNT(cpe2.idAsignatura)
FROM dbo.Ctrl_PlanEstudios AS cpe2
WHERE cpe2.idPlanEstudios = @idPlanEst
), 0) AS iTotalAsigPlanEst ,
ISNULL(@iTotalCredito, 0) AS iTotalCreditoPlanEst ,
cfe.dMinimaAprobatoria ,
cfe.dMinima ,
cfe.dMaxima ,
fdbc.sEstado ,
fdbc.sMunicipio ,
fdbc.sPais
FROM dbo.Cat_OfEduc AS coe
INNER JOIN dbo.Ctrl_PlantelesOfEduc AS cpoe ON cpoe.idOfEduc = coe.idOfEduc AND cpoe.idOfEduc = @idOfEduc
INNER JOIN dbo.Cat_Planteles AS cp ON cp.idPlantel = cpoe.idPlantel
INNER JOIN dbo.Cat_PlanEstudios AS cpe ON cpe.idOfEduc = coe.idOfEduc AND cpe.idPlanEstudios = @idPlanEst
LEFT JOIN dbo.Ctrl_PlanEstudiosRVOE AS cper ON cper.idPlanEstudios = cpe.idPlanEstudios
AND cper.idPlantel = @idPlantel
LEFT JOIN dbo.Cat_FormEvaluacion AS cfe ON cfe.idFormEvaluacion = cpe.idFormEvaluacion
OUTER APPLY dbo.fnDomicilioByColonia(cp.idColonia) AS fdbc;
END;