Cacher is the code snippet organizer for pro developers

----------------------------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;