set search_path = negocio;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- SIU-GUARANI 3
-- Version 3.22.0
--
-- Function: f_int_becas_belgrano_progresar
-- Proceso que genera información para becas MANUEL BELGRANO y PROGRESAR
-- 
-- Para la verificacion anual de materias aprobadas/cursadas se considera el siguiente período:
--   Becas BELGRANO: Del 01/01/<pAnio> al 31/12/<pAnio> 
--   Becas PROGRESAR: Del 01/04/<pAnio> al 31/03/<pAnio + 1> (Idem Araucano)
--
-- Se puede informar todo el año completo o 1er semestre. En el caso de informar el 1er semestre, en ambos casos se consider hasta el 31/08/año a informar
--
-- El codigo de institucion araucano sale de la tabla de Responsables Academicas
-- 
-- * Retorna alumnos en las propuestas donde tenga inscripcion en estado aceptada o pendiente
-- * Informa actividad academica del titulo araucano que corresponde con la propuesta del alumno
--   Si el titulo araucano se encuentra en otra propuesta donde el alumno no se encuentra entonces informa actividad academica del alumno 
--   de todas las propuestas en la que se encuentra que tengan un titulo araucano asociado.
-- * Respecto de la fecha de finalización del curso de ingreso, en aquellas propuestas que tengan
--   una propuesta vinculada de tipo "curso de ingreso", devolverá la fecha máxima de aprobación de materia del curso de ingreso,
--   solo en el caso que haya cumplido con todas las materias del curso de ingreso.
--   En caso contrario se devuelve vacio el dato de fecha de cumplimiento de curso de ingreso.
-- 
-- Tabla donde se carga la info de los alumnos: int_becas_belgrano_progresar
--
-- Recibe: 
--    1. pTipoBeca = Tipo de Beca. Valores: B = Becas Manuel Belgrano / P = Becas PROGRESAR
--    2. pAnio = Año a evaluar  
--    3. pPeriodoInformar = Periodo a informar. 
--                          Valores: A = Anual (valor default) / S = Semestral (1er semestre)
--    4. pResponsableAcademica = ID de Responsable Academica a procesar. Si existe el dato, solo alumnos de propuestas de esta responsable Academica
--    5. pActividadesPlanActual = Indica si solo se consideran actividades aprobadas/cursadas que son del plan actual del alumno o todas de la propuesta sin considerar plan y versión.
--                                Valores: TRUE (valor por defecto) / FALSE 
--    6. pTituloNivelFinal = Indica si solo se informan actividades relacionadas con el titulo de nivel final o tambien se informan titulos de nivel "Intermedio"
--                           Valores: TRUE (Solo titulo de Nivel Final) / FALSE (Titulos de cualquier nivel: Final/Intermedio. Valor por defecto)
--    7. pNrodocumento = Si se quiere recuperar inforación de un alumno en particular. Valor default NULL.
--
-- Retorna: 
--   1. Cada registro con info de los alumnos, los planes de estudio y las materias aprobadas/cursadas.
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DROP FUNCTION IF EXISTS f_int_becas_belgrano_progresar(char, integer, char, integer, boolean, boolean, varchar);

CREATE OR REPLACE FUNCTION f_int_becas_belgrano_progresar(
  pTipoBeca Char(1),
  pAnio integer,
  pPeriodoInformar char(1) DEFAULT 'A',
  pResponsableAcademica Integer DEFAULT NULL,
  pActividadesPlanActual BOOLEAN DEFAULT TRUE,
  pTituloNivelFinal BOOLEAN DEFAULT FALSE,
  pNroDocumento varchar(20) DEFAULT NULL)
  RETURNS SETOF text AS 
$BODY$
DECLARE 
  _datos text;
  _alu  record;
  _ci record;
  _planes record;
  _arch1 record;
  _arch2 record;
  _arch3 record;
  _errores record;
  _cant_aprobadas integer;
  _cant_aprobadas_anio_a_evaluar integer;
  _fecha_curso_ingreso varchar(10);
  _fecha_aprobacion varchar(10);
  _periodo_cursado char(1);
  _cursada char(1);
  _inst_codigo integer;
  _inst_nombre varchar(100);
  _TipoCursoIngreso integer;
  _AlumnoCI integer;
  _PropuestaCI integer;
  _PlanCI integer;
  _PlanVersionCI integer;        
  _cant_no_aprobadas_ci integer;
  _dfecha_ci date;
  _aux_plan varchar(10);
  _aux_titulo integer;
  _cant integer;
  _id integer;
  _error_alumno text;
  _existe_titulo_en_su_propuesta boolean;
  _fecha_inscripcion varchar(10);
  _FechaDesde date;
  _FechaHasta date;
  
BEGIN
  _TipoCursoIngreso := 204; -- Curso de Ingreso
  _id := 0;
  
  -- +++++++++++++++++++++++++++ INICIO VALIDACIONES +++++++++++++++++++++++++++++++++++
  IF pTipoBeca <> 'B' AND pTipoBeca <> 'P' THEN
    RETURN NEXT 'El tipo de Beca debe ser P (PROGRESAR) o B (BELGRANO)';
    RETURN;
  END IF;
  -- +++++++++++++++++++++++++++ FIN VALIDACIONES +++++++++++++++++++++++++++++++++++
  
  -- BECAS MANUEL BELGRANO
  IF pTipoBeca = 'B' THEN
    -- El rango de fechas a evaluar las actividades aprobadas son desde el 01/04/Año a Evaluar al 31/03/Año + 1
    IF pPeriodoInformar = 'A' THEN
      -- Todo el año
      _FechaDesde := to_date('0101' || cast(pAnio as varchar), 'DDMMYYYY');
      _FechaHasta := to_date('3112' || cast(pAnio as varchar), 'DDMMYYYY');
    ELSE
      -- 1er semestre
      _FechaDesde := to_date('0101' || cast(pAnio as varchar), 'DDMMYYYY');
      _FechaHasta := to_date('3108' || cast((pAnio) as varchar), 'DDMMYYYY');
    END IF;  
  END IF;

   -- BECAS PRO.GRES.AR
   IF pTipoBeca = 'P' THEN
    -- El rango de fechas a evaluar las actividades aprobadas son año calendario, desde el 01/01/Año a Evaluar al 31/12/Año
    IF pPeriodoInformar = 'A' THEN
      -- Todo el año
      _FechaDesde := to_date('0104' || cast(pAnio as varchar), 'DDMMYYYY');
      _FechaHasta := to_date('3103' || cast((pAnio + 1) as varchar), 'DDMMYYYY');
    ELSE
      -- 1er semestre
      _FechaDesde := to_date('0104' || cast(pAnio as varchar), 'DDMMYYYY');
      _FechaHasta := to_date('3108' || cast(pAnio as varchar), 'DDMMYYYY');
    END IF;  
  END IF;

  
  -- Registra todas las cursadas del alumno
  CREATE TEMP TABLE _Tcursadas (
	  comision integer, 
	  plan_version integer, 
	  elemento integer, 
	  periodo_lectivo integer, 
	  periodo_cursado char(1), 
	  resultado varchar(2), 
	  fecha_regularidad date, 
	  fecha_inscripcion date,
	  anio_academico integer);

  CREATE TEMP TABLE _Terrores (id integer, error text);
   
  CREATE TEMP TABLE _Tplanes (
     plan integer, 
     cnt_materias integer, 
     titulo_araucano integer,
     institucion_araucano integer
    );

   CREATE TEMP TABLE _TMaterias (
	alumno integer,
	tipo_documento_becas varchar(5),
	nro_documento varchar(20),
	titulo_araucano integer,
	elemento integer,
	plan_version integer,
	nombre_materia varchar(255),
	periodo_cursado char(1),
	cursada char(1),
	fecha_aprobacion date,
	curricular char(1),
	origen char(1),
	fecha_inscripcion date,
	regularizo varchar(2));
  
 -- Limpio datos por si viene el string vacio
 UPDATE int_becas_belgrano_progresar SET codigo_titulo_araucano = NULL WHERE trim(COALESCE(codigo_titulo_araucano,'')) = '';
 UPDATE int_becas_belgrano_progresar SET codigo_facultad  = NULL WHERE trim(COALESCE(codigo_facultad,'')) = '';
 UPDATE int_becas_belgrano_progresar SET codigo_institucion  = NULL  WHERE trim(COALESCE(codigo_institucion,'')) = '';
 
 CREATE INDEX _Tmaterias01 ON _Tmaterias (alumno);
 
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 -- Devuelvo el archivo de alumnos
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 _datos := '#SECCION1# **************** Inicio archivo alumnos.txt *****************';  
 RETURN NEXT _datos;

 -- Recupero una vez cada persona (por si esta informado mas de una vez en el archivo)  
 -- Lo informo por cada titulo araucano que viene en el archivo a informar.
 FOR _alu IN 
     SELECT DISTINCT
        CASE tipo_documento
             WHEN 'DNI' THEN 0
             WHEN 'DNT' THEN 1
             WHEN 'CI'  THEN 2
             WHEN 'LE'  THEN 18
             WHEN 'LC'  THEN 19             
             WHEN 'PAS' THEN 90             
        END as tipo_documento, 
        numero_documento,
        tipo_documento as tipo_documento_archivo,
        cast(trim(codigo_titulo_araucano) as integer) as codigo_titulo_araucano,
        trim(codigo_facultad) as codigo_facultad,
        COALESCE(apellido,'') as apellido, 
        COALESCE(nombre,'') as nombre
      FROM int_becas_belgrano_progresar
      WHERE (pNroDocumento IS NULL OR trim(numero_documento) = pNroDocumento)
     ORDER BY numero_documento
  
  LOOP
    -- ******************************* INICIO VALIDACIONES ****************************************************
    -- Formato de salida del archivo de errores: DOC|12345678|Apellido|Nombre|Descripción del Error
    _error_alumno := _alu.tipo_documento_archivo || '|' ||  _alu.numero_documento || '|' ||  _alu.apellido|| '|' ||  _alu.nombre || '|'; 
	
    -- Si no encuentra el alumno por su numero de documento.
    SELECT count(1) INTO _cant
     FROM mdp_personas_documentos as d
    WHERE d.tipo_documento = _alu.tipo_documento
      AND d.nro_documento  = _alu.numero_documento;   
	IF _cant = 0 THEN 
	   _id := _id + 1;
	   INSERT INTO _Terrores (id, error) VALUES (_id, _error_alumno || 'No se encontró el numero de documento');
	   CONTINUE;		
	END IF;  

    -- No existe el codigo de Título Araucano en la propuesta del alumno
	_existe_titulo_en_su_propuesta := true;
    SELECT count(1) INTO _cant
     FROM mdp_personas_documentos as d
     JOIN mdp_personas as p ON p.persona = d.persona
     JOIN sga_alumnos as alu ON alu.persona = p.persona
     JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
     JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
     JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
     JOIN sga_certificados as c ON c.certificado = pc.certificado
     JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
     JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
     WHERE d.tipo_documento = _alu.tipo_documento
        AND d.nro_documento = _alu.numero_documento
        AND sa.resultado_asp IN ('P','A')
        AND c.titulo_araucano = _alu.codigo_titulo_araucano;
	IF _cant = 0 THEN 
	   _existe_titulo_en_su_propuesta := false;
	   -- NO LO INFORMO COMO ERROR DEBIDO A QUE AL NO ENCONTRAR EL TITULO EN ALGUNA DE LAS PROPUESTAS DEL ALUMNO INFORMO POR TODAS.
	   -- _id := _id + 1;
	   -- INSERT INTO _Terrores (id, error) 
	   --   VALUES (_id, _error_alumno || 'No existe el código de titulo araucano (' || _alu.codigo_titulo_araucano || ') definido en algún título de las propuestas donde se encuentra el alumno o el alumno no tiene la inscripción a propuesta en estado aceptada o pendiente.');

       -- Verifico si el titulo araucano esta definido para otra propuesta en la que no esta el alumno.
		SELECT count(1) INTO _cant
		 FROM sga_certificados as c
		 JOIN sga_planes_certificados as pc ON c.certificado = pc.certificado
		WHERE c.titulo_araucano = _alu.codigo_titulo_araucano
		 AND pc.plan_version NOT IN (SELECT alu.plan_version 
							FROM mdp_personas_documentos as d
							JOIN mdp_personas as p ON p.persona = d.persona
							JOIN sga_alumnos as alu ON alu.persona = p.persona
							JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
							JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
							JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
							WHERE d.tipo_documento = _alu.tipo_documento
							  AND d.nro_documento  = _alu.numero_documento
							  AND sa.resultado_asp IN ('P','A')
							 );
		IF _cant > 0 THEN 		
	     _id := _id + 1;
	     INSERT INTO _Terrores (id, error) 
	         VALUES (_id, _error_alumno || 'El título araucano (' || _alu.codigo_titulo_araucano || ') se oferta por otra propuesta en la que no se encuentra el alumno.');
		END IF;

       -- Verifico si puedo informarlo por algun titulo
		SELECT count(1) INTO _cant
			FROM mdp_personas_documentos as d
			JOIN mdp_personas as p ON p.persona = d.persona
			JOIN sga_alumnos as alu ON alu.persona = p.persona
			JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
			JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
			JOIN sga_certificados as c ON c.certificado = pc.certificado
			JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
			JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
			WHERE d.tipo_documento = _alu.tipo_documento
			  AND d.nro_documento  = _alu.numero_documento
			  AND sa.resultado_asp IN ('P','A')
			  AND c.titulo_araucano IS NOT NULL
			  AND prop.propuesta_tipo NOT IN (207, 208); -- No informo propuestas de tipo VOCACIONAL ni CURSO
			
		IF _cant = 0 THEN 		
		     _id := _id + 1;
		     INSERT INTO _Terrores (id, error) 
		         VALUES (_id, _error_alumno || 'El alumno se encuentra en propuesta/s que NO tiene/n definido algún código de título araucano en su oferta. No se puede informar.');
		END IF;
   END IF;  
		

    -- ******************************* FIN VALIDACIONES ****************************************************
	
   -- Datos del alumno a informar
   FOR _arch2 IN
   -- Titulos Araucano por los que va a informar...
   -- Por el que viene informado desde el ministerio y si no viene titulo se informa por todos en los que se encuentre el alumno.
   SELECT 
           CASE d.tipo_documento
             WHEN 0 THEN 'DNI'
             WHEN 1 THEN 'DNT' 
             WHEN 2 THEN 'CI'  
             WHEN 18 THEN 'LE' 
             WHEN 19 THEN 'LC'              
             WHEN 90 THEN 'PAS'             
          END as tipo_documento_becas, 
          alu.persona as persona, 
          alu.alumno as alumno,
          alu.regular as regular,
          alu.propuesta as propuesta,
          alu.plan_version,
          d.tipo_documento,
          d.nro_documento,
          p.apellido,
          p.nombres,
          CASE p.sexo WHEN 'M' THEN '1' WHEN 'F' THEN '2' WHEN 'X' THEN '3' END as genero,
          to_char(p.fecha_nacimiento, 'YYYYMMDD') as fecha_nacimiento,
          c.titulo_araucano as titulo_araucano,
          pa.anio_academico as anio_ingreso,
          alu.plan_version as plan_version,
          pv.plan as plan,
          sga_planes.codigo as plan_codigo,
          pv.cnt_materias as cnt_materias,
          prop.nombre as propuesta_nombre,
          c.titulo_nivel as titulo_nivel, -- INTERMEDIO / FINAL 
	  c.nombre as titulo_nombre
     FROM mdp_personas_documentos as d
     JOIN mdp_personas as p ON p.persona = d.persona
     JOIN sga_alumnos as alu ON alu.persona = p.persona
     JOIN sga_propuestas as prop ON prop.propuesta = alu.propuesta
     JOIN sga_planes_versiones as pv ON pv.plan_version = alu.plan_version
     JOIN sga_planes ON sga_planes.plan = pv.plan
     JOIN sga_planes_certificados as pc ON pc.plan_version = alu.plan_version
     JOIN sga_certificados as c ON c.certificado = pc.certificado
     JOIN sga_propuestas_aspira as pa ON pa.propuesta = alu.propuesta AND pa.persona = alu.persona
     JOIN sga_situacion_aspirante as sa ON sa.situacion_asp = pa.situacion_asp
          
      WHERE d.tipo_documento = _alu.tipo_documento
        AND d.nro_documento  = _alu.numero_documento
        AND sa.resultado_asp IN ('P','A')  -- Inscripcion a propuesta aceptada/pendiente
        AND c.titulo_araucano IS NOT NULL		-- Que el plan tenga un titulo con titulo araucano definido.
        AND (NOT _existe_titulo_en_su_propuesta OR (_existe_titulo_en_su_propuesta AND c.titulo_araucano = _alu.codigo_titulo_araucano))
	AND prop.propuesta_tipo NOT IN (207, 208) -- No informo propuestas de tipo VOCACIONAL ni CURSO
        AND ((pTituloNivelFinal AND c.titulo_nivel = 'FINAL') OR NOT pTituloNivelFinal)
        -- Filtro alumnos de propuesta de la responsable academica
        AND (pResponsableAcademica IS NULL OR alu.propuesta IN (SELECT propuesta FROM sga_propuestas_ra WHERE responsable_academica = pResponsableAcademica))
        
        -- AND alu.calidad = 'A'
      ORDER BY p.apellido, p.nombres, prop.nombre, c.nombre
  LOOP
  
  
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	-- Recupero codigo de institucion araucano y nombre en base a la responsable academica
	-- Tomo la primera que encuentre si existe mas de una RA para la propuesta
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _inst_codigo := null;
    _inst_nombre := null;
    SELECT ra.institucion_araucano, ra.nombre
      INTO _inst_codigo, _inst_nombre
      FROM sga_propuestas_ra as pra
      JOIN sga_responsables_academicas as ra ON pra.responsable_academica = ra.responsable_academica
     WHERE pra.propuesta = _arch2.propuesta
       AND ra.institucion_araucano IS NOT NULL
     ORDER BY ra.responsable_academica
     LIMIT 1;

     IF _inst_codigo IS NULL THEN 
        _id := _id + 1;
        INSERT INTO _Terrores (id, error) 
         VALUES (_id, _error_alumno || 'La responsable académica de la propuesta (' || _arch2.propuesta_nombre || ') no tiene definido el código de institución araucano');
    END IF;  

     -- Inserto los planes
     INSERT INTO _Tplanes (plan, cnt_materias, titulo_araucano, institucion_araucano) 
          VALUES (_arch2.plan, _arch2.cnt_materias, _arch2.titulo_araucano, _inst_codigo);

	 
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Cuento TOTAL de materias aprobadas por el alumno en la propuesta (año a evaluar y anteriores)
	-- Solo cuento actividades que se encuentren en la version del plan actual del alumno
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _cant_aprobadas := 0;
    _cant_aprobadas_anio_a_evaluar := 0;
    SELECT COUNT(*), 
           SUM(CASE WHEN (fecha >= _FechaDesde AND fecha <= _FechaHasta) THEN 1 ELSE 0 END)
      INTO _cant_aprobadas, _cant_aprobadas_anio_a_evaluar          
      FROM vw_hist_academica_basica as ha
     WHERE ha.alumno = _arch2.alumno
       AND ha.resultado = 'A'
       -- AND EXTRACT(year FROM fecha) <= pAnio
       AND ha.fecha <= _FechaHasta
       AND ((pActividadesPlanActual AND ha.elemento IN (SELECT elemento FROM vw_elementos_plan WHERE plan_version = _arch2.plan_version)) 
            OR NOT pActividadesPlanActual);
 
 	-- MATERIAS APROBADAS EN EL AÑO A EVALUAR
	-- Se insertan las materias aprobadas por el alumno en el rango de fechas a evaluar. Incluye las equivalencias.
	INSERT INTO _TMaterias (alumno, tipo_documento_becas, nro_documento, titulo_araucano, elemento, plan_version, nombre_materia, fecha_aprobacion, cursada, origen)
	SELECT	_arch2.alumno,
		_arch2.tipo_documento_becas,
		_arch2.nro_documento,
		_arch2.titulo_araucano,
		ha.elemento,
		ha.plan_version,
		COALESCE(ep.nombre, e.nombre) as nombre_materia,
		ha.fecha,
		CASE WHEN ha.origen = 'B' THEN 'E' ELSE NULL END as cursada, -- Aprobada por equivalencia
		ha.origen
	FROM	vw_hist_academica_basica as ha
	JOIN	sga_elementos as e ON e.elemento = ha.elemento
	JOIN	sga_elementos_revision as er ON er.elemento = e.elemento
	LEFT JOIN sga_elementos_plan as ep ON (ep.plan_version = ha.plan_version AND ep.elemento_revision = er.elemento_revision)
	WHERE	ha.alumno = _arch2.alumno 
	  AND	ha.resultado = 'A' 
	  AND ha.fecha BETWEEN _FechaDesde AND _FechaHasta
	  AND ((pActividadesPlanActual AND ha.elemento IN (SELECT elemento FROM vw_elementos_plan WHERE plan_version = _arch2.plan_version)) OR 
	        NOT pActividadesPlanActual );
			
	
	-- Recupero cursadas del alumno con fecha menor o igual al año a informar. Con cualquier resultado.
	TRUNCATE TABLE _Tcursadas;
	INSERT INTO _Tcursadas (comision, elemento, plan_version, fecha_regularidad, periodo_lectivo, periodo_cursado, resultado, fecha_inscripcion, anio_academico)
	  SELECT ha.comision, ha.elemento, ha.plan_version, ha.fecha, c.periodo_lectivo,
	        CASE WHEN sga_periodos_genericos.periodo_lectivo_tipo = 'anual' THEN 'A' ELSE 'C' END as periodo_cursado,
	        ha.resultado,
	        -- Si no tiene la inscripcion porque fue agregado al acta de cursada entonces devuelvo la fecha de inicio del periodo lectivo
	        COALESCE((SELECT date(i.fecha_inscripcion) FROM sga_insc_cursada as i WHERE i.comision = c.comision AND i.alumno = _arch2.alumno), sga_periodos.fecha_inicio) as fecha_inscripcion,
	        sga_periodos.anio_academico
	    FROM vw_regularidades_basica as ha
	    JOIN sga_comisiones as c ON c.comision = ha.comision
	    JOIN sga_periodos_lectivos ON sga_periodos_lectivos.periodo_lectivo = c.periodo_lectivo
	    JOIN sga_periodos ON sga_periodos.periodo = sga_periodos_lectivos.periodo
	    JOIN sga_periodos_genericos ON sga_periodos_genericos.periodo_generico = sga_periodos.periodo_generico
	   WHERE ha.alumno = _arch2.alumno
	     -- AND EXTRACT(year FROM ha.fecha) <= pAnio
	     AND ha.fecha <= _FechaHasta
	     AND ((pActividadesPlanActual AND ha.elemento IN (SELECT elemento FROM vw_elementos_plan WHERE plan_version = _arch2.plan_version)) OR 
	           NOT pActividadesPlanActual  
		   );
    
	-- Cursadas actuales del año academico a informar que no tienen resultado cargado. resultado = EC
	INSERT INTO _Tcursadas (elemento, plan_version, fecha_regularidad, periodo_lectivo, periodo_cursado, resultado, fecha_inscripcion, anio_academico)
	  SELECT c.elemento, i.plan_version, cast(null as date), c.periodo_lectivo,
	        CASE WHEN sga_periodos_genericos.periodo_lectivo_tipo = 'anual' THEN 'A' ELSE 'C' END as periodo_cursado,
	        'EC' as resultado, 
	        date(i.fecha_inscripcion) as fecha_inscripcion,
	        sga_periodos.anio_academico
	    FROM sga_insc_cursada as i
	    JOIN sga_comisiones as c ON c.comision = i.comision
	    JOIN sga_periodos_lectivos ON sga_periodos_lectivos.periodo_lectivo = c.periodo_lectivo
	    JOIN sga_periodos ON sga_periodos.periodo = sga_periodos_lectivos.periodo
	    JOIN sga_periodos_genericos ON sga_periodos_genericos.periodo_generico = sga_periodos.periodo_generico
	   WHERE i.alumno = _arch2.alumno
	     -- AND EXTRACT(year FROM i.fecha_inscripcion) = pAnio -- Inscripto a cursada en el año a informar
	     AND sga_periodos.anio_academico = pAnio -- Comisiones del año academico a informar.
	     AND i.comision NOT IN (SELECT T.comision FROM _Tcursadas as T) -- No existe en acta de cursada cerrada
	     AND ((pActividadesPlanActual AND c.elemento IN (SELECT ep.elemento FROM vw_elementos_plan as ep WHERE ep.plan_version = _arch2.plan_version)) OR 
	           NOT pActividadesPlanActual  
		   );


	-- Actualizo por cada materia aprobada del alumno los datos de la ultima cursada.
	UPDATE	_TMaterias
	   SET	(fecha_inscripcion, cursada, periodo_cursado, regularizo) = 
	        (SELECT
		     cur.fecha_inscripcion,
		     CASE
	               WHEN EXTRACT(year FROM cur.fecha_regularidad) < pAnio THEN 'N'
	               WHEN EXTRACT(year FROM cur.fecha_regularidad) = pAnio THEN 'S'
	               ELSE 'L' -- Rindió libre.
	             END as cursada,
		     cur.periodo_cursado,
		     CASE cur.resultado 
		       WHEN 'A' THEN 'R'  -- Regularizó la cursada
		       WHEN 'R' THEN 'NR' -- No Regularizó la cursada
		       WHEN 'U' THEN 'AU' -- Ausente.
		       WHEN 'EC' THEN 'EC' -- Cursandose actualmente
		       ELSE 'NR' 		    -- No regularizó la cursada
		     END as resultado_cursada
	         FROM _Tcursadas as cur
	         WHERE cur.elemento = _TMaterias.elemento
	         ORDER BY coalesce(cur.fecha_regularidad,cur.fecha_inscripcion) DESC
	         LIMIT 1 -- Obtengo la ultima cursada de la actividad.
		 )
	  WHERE alumno = _arch2.alumno;

	 
	-- Cursadas con resultado o cursandose actualmente sin aprobacion final de la materia.
	-- Agrego al archivo de materias, las materias que el alumno haya cursado en el año a evaluar y no aprobó por examen final/promocion/equivalencia..
	INSERT INTO _TMaterias (alumno, tipo_documento_becas, nro_documento, titulo_araucano, elemento, 
	plan_version, nombre_materia, fecha_aprobacion, origen, fecha_inscripcion, cursada, periodo_cursado, regularizo)
	SELECT	_arch2.alumno,
	        _arch2.tipo_documento_becas,
		_arch2.nro_documento,
		_arch2.titulo_araucano,
		cur.elemento,
		cur.plan_version,
		COALESCE(ep.nombre, e.nombre) as nombre_materia,
		cast(null as date) as fecha_aprobacion,
		'R' as origen, -- Regularidad
		date(cur.fecha_inscripcion) as fecha_inscripcion_cursada,
		'S' as cursada, -- La curso en el año a evaluar.
		cur.periodo_cursado,
		CASE cur.resultado 
		  WHEN 'EC' THEN 'EC' -- Cursandose actualmente
		  WHEN 'A' THEN 'R' -- Regularizo
		  WHEN 'R' THEN 'NR' -- No regularizo
		  WHEN 'U' THEN 'AU' -- No curso. Ausente.
		  ELSE 'NR'
		END as regularizo  
	FROM _TCursadas as cur
	JOIN sga_elementos as e ON e.elemento = cur.elemento
	JOIN sga_elementos_revision as er ON er.elemento = e.elemento
	LEFT JOIN sga_elementos_plan as ep ON (ep.plan_version = cur.plan_version AND ep.elemento_revision = er.elemento_revision)
	WHERE cur.elemento NOT IN (SELECT aprobadas.elemento FROM _TMaterias as aprobadas WHERE aprobadas.alumno = _arch2.alumno AND aprobadas.fecha_aprobacion IS NOT NULL)
	  -- AND EXTRACT(year FROM COALESCE(cur.fecha_regularidad,cur.fecha_inscripcion)) = pAnio;
	  AND cur.anio_academico = pAnio;
	 

	-- Se marcan las materias que son optativas en el plan de estudios (forman parte de alguna generica).		
	UPDATE	_TMaterias
	SET		curricular = (CASE	
						  	WHEN (SELECT COUNT(1)
									FROM vw_optativas_plan
									WHERE vw_optativas_plan.plan_version = _TMaterias.plan_version
									  AND vw_optativas_plan.elemento = _TMaterias.elemento
								 ) > 0 THEN 'N'
							ELSE 'S'
						END)
	 WHERE alumno = _arch2.alumno;


    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Fecha cumplimiento Curso de Ingreso
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _fecha_curso_ingreso := '';
    _AlumnoCI    := NULL;
    _PropuestaCI := NULL;
    _PlanCI      := NULL;
    _PlanVersionCI := NULL;
    _dfecha_ci     := NULL;
    
    -- Verifico si la propuesta tiene un curso de ingreso vinculado y la persona esta como alumno en esa propuesta
    SELECT sga_alumnos.alumno, sga_planes.propuesta, sga_planes.plan, sga_alumnos.plan_version
         INTO _AlumnoCI, _PropuestaCI, _PlanCI, _PlanVersionCI 
         FROM sga_propuestas_relacion_grupo
         JOIN sga_propuestas_relacion_plan ON sga_propuestas_relacion_plan.relacion_grupo = sga_propuestas_relacion_grupo.relacion_grupo
         JOIN sga_planes ON sga_planes.plan = sga_propuestas_relacion_plan.plan 
         JOIN sga_propuestas ON sga_propuestas.propuesta = sga_planes.propuesta
         JOIN sga_alumnos ON sga_alumnos.propuesta = sga_planes.propuesta 
        WHERE sga_propuestas_relacion_grupo.relacion IN (SELECT relacion FROM get_relacion_propuesta(_arch2.alumno))
          AND sga_propuestas.propuesta_tipo = _TipoCursoIngreso
          AND sga_alumnos.persona   = _arch2.persona
          LIMIT 1;
    
    IF FOUND THEN
       -- Existe Curso de ingreso. 
       -- Verifico si cumplio todas las actividades y si es asi recupero la fecha maxima de aprobación.
       
       _cant_no_aprobadas_ci := 0;
       
       SELECT COUNT(*) INTO _cant_no_aprobadas_ci
         FROM vw_actividades_plan
         WHERE plan_version = _PlanVersionCI
           AND elemento NOT IN (SELECT elemento FROM vw_hist_academica_basica
                                 WHERE alumno = _AlumnoCI
                                   AND resultado = 'A'
                                );
       
       IF _cant_no_aprobadas_ci = 0 THEN
          -- Recupero fecha maxima de aprobacion de las materias del curso de ingreso.  
          SELECT MAX(fecha) INTO _dfecha_ci
            FROM vw_hist_academica_basica
           WHERE alumno    = _AlumnoCI
             AND resultado = 'A'
             AND elemento IN (SELECT elemento FROM vw_actividades_plan WHERE plan_version = _PlanVersionCI);
		  
          _fecha_curso_ingreso := to_char(_dfecha_ci, 'DD-MM-YYYY');
       END IF;
     
    END IF; -- Existe Curso de Ingreso


    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    -- Devuelvo los datos
    -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    _datos := _arch2.tipo_documento_becas;
    _datos := _datos || '|' || _arch2.nro_documento;
    _datos := _datos || '|' || LEFT(_arch2.apellido,30);
    _datos := _datos || '|' || LEFT(_arch2.nombres,30);
    _datos := _datos || '|' || COALESCE(_inst_codigo::text,'');
    _datos := _datos || '|' || COALESCE(LEFT(_inst_nombre,60),'');
    _datos := _datos || '|' || _arch2.titulo_araucano; 
    _datos := _datos || '|' || _arch2.propuesta_nombre;
    _datos := _datos || '|' || _arch2.plan_codigo;
    _datos := _datos || '|' || _arch2.anio_ingreso::text;
    _datos := _datos || '|' || COALESCE(_fecha_curso_ingreso,'');
    _datos := _datos || '|' || COALESCE(_cant_aprobadas::text, '0');
    _datos := _datos || '|' || COALESCE(_cant_aprobadas_anio_a_evaluar::text, '0');
    _datos := _datos || '|' || _arch2.regular;
    
    RETURN NEXT _datos;
  
  END LOOP; -- Datos de cada alumno
      
 END LOOP; -- Archivo de alumnos

 RETURN NEXT '#SECCION1# **************** Fin archivo alumnos.txt *****************';  

 RETURN NEXT '#SECCION2# **************** Inicio archivo planes_estudio.txt *****************';  
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 -- Devuelvo el archivo de planes de estudios.
 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 _aux_plan   := '';
 _aux_titulo := 0;
 FOR _arch1 IN SELECT DISTINCT t.institucion_araucano, p.codigo, t.cnt_materias, t.titulo_araucano, p.duracion_en_anios
                 FROM _Tplanes as t 
                 JOIN sga_planes as p ON t.plan = p.plan
               ORDER BY t.institucion_araucano, p.codigo, t.cnt_materias DESC
 LOOP
    -- Puede haber versiones de plan para informar el mismo titulo con cantidad de materias diferentes.
	-- Informo por la version del plan que mas materias tiene.
    IF _aux_titulo = _arch1.titulo_araucano AND _aux_plan = _arch1.codigo THEN 
       CONTINUE;
    END IF;   
	
    _datos := COALESCE(_arch1.institucion_araucano::text,''); 
    _datos := _datos || '|' || COALESCE(_arch1.titulo_araucano::text, '') ;
    _datos := _datos || '|' || COALESCE(_arch1.codigo, ''); 
    _datos := _datos || '|' || COALESCE(_arch1.cnt_materias::text, '0');  
    _datos := _datos || '|' || COALESCE(_arch1.duracion_en_anios::text, '0') ;
    RETURN NEXT _datos;
	
    _aux_titulo := _arch1.titulo_araucano;
    _aux_plan   := _arch1.codigo;
 END LOOP;

 _datos := '#FINSECCION2# **************** Fin archivo planes_estudio.txt ************';  
 RETURN NEXT _datos;
 
  _datos := '#SECCION3# **************** Inicio archivo materias.txt *****************';  
 RETURN NEXT _datos;
 
 FOR _arch3 IN SELECT DISTINCT tipo_documento_becas, nro_documento, titulo_araucano, nombre_materia, periodo_cursado, 
                      CASE _Tmaterias.origen WHEN 'B' THEN 'E' ELSE _Tmaterias.cursada END as cursada, 
                      fecha_aprobacion, curricular, fecha_inscripcion, regularizo
                FROM _TMaterias 
              ORDER BY tipo_documento_becas, nro_documento, fecha_aprobacion
 LOOP
 
 	_datos := _arch3.tipo_documento_becas;
 	_datos := _datos || '|' || _arch3.nro_documento;
 	_datos := _datos || '|' || _arch3.titulo_araucano;
 	_datos := _datos || '|' || _arch3.nombre_materia;
 	_datos := _datos || '|' || COALESCE(_arch3.periodo_cursado,'C');
 	_datos := _datos || '|' || COALESCE(_arch3.cursada, 'L'); 
 	_datos := _datos || '|' || COALESCE(to_char(_arch3.fecha_aprobacion, 'DD-MM-YYYY'),'');
 	_datos := _datos || '|' || _arch3.curricular;
 	_datos := _datos || '|' || COALESCE(to_char(_arch3.fecha_inscripcion, 'DD-MM-YYYY'),'');   -- Fecha inscripcion cursada
 	_datos := _datos || '|' || COALESCE(_arch3.regularizo,'NI');  -- Resultado de la cursada (NI = No disponde de información)
 	
 	RETURN NEXT _datos;
 END LOOP;
 
 RETURN NEXT '#FINSECCION3# **************** Fin archivo materias.txt ************';  

 RETURN NEXT '#SECCION4# **************** Inicio archivo de Errores alumnos_con_error.txt ************';  
 FOR _errores IN (SELECT error FROM _TErrores ORDER BY id)
 LOOP
    RETURN NEXT _errores.error;
 END LOOP;
 RETURN NEXT '#FINSECCION4# **************** Fin archivo de Errores alumnos_con_error.txt ************';


 -- Borro la tabla temporal   
 DROP TABLE IF EXISTS _Tplanes;
 DROP TABLE IF EXISTS _TMaterias;
 DROP TABLE IF EXISTS _TErrores;
 DROP TABLE IF EXISTS _TCursadas;
 
    
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- ++++++++++++++++++++++++++++++ Fin Function f_int_becas_belgrano_progresar ++++++++++++++++

-- REVOKE EXECUTE ON FUNCTION f_int_becas_belgrano_progresar (char, integer, char, integer, boolean, boolean, varchar) FROM public;
GRANT EXECUTE ON FUNCTION f_int_becas_belgrano_progresar (char, integer, char, integer, boolean, boolean, varchar) to public;

