CREATE OR REPLACE FUNCTION buscarAnioPromo (codPromo IN VARCHAR)
	   RETURN VARCHAR AS
BEGIN
    DECLARE
	   anio2Digitos	  VARCHAR(2);
	   nAnio2Digitos  NUMBER;
	   anio  		  NUMBER;
BEGIN
     anio2Digitos:=SUBSTR(codPromo,10,2);

	 nAnio2Digitos:=TO_NUMBER(anio2Digitos);
	 IF (nAnio2Digitos>70) THEN
	   anio:=nAnio2Digitos+1900;
	 ELSE
	   anio:=nAnio2Digitos+2000;
	 END IF;

	 RETURN anio;

END;
END;
/
CREATE OR REPLACE FUNCTION buscarDenoPromo (codPromo IN NUMBER,
	   tipoDef IN VARCHAR, tipoProv IN VARCHAR, activa IN NUMBER)
	   RETURN VARCHAR AS
BEGIN
	 DECLARE CURSOR cCal (codPromo NUMBER, tipo VARCHAR, activa NUMBER)
	 	IS SELECT GREATEST(NVL(CALI_SOLC_009, TO_DATE('01/01/0001', 'DD/MM/YYYY')),
	   				NVL(CALI_CONC_009,TO_DATE('01/01/0001', 'DD/MM/YYYY')),
	   				NVL(CALI_ACTA_009, TO_DATE('01/01/0001', 'DD/MM/YYYY'))) AS FECHA,
					CALI_DENO_009
			 FROM Q0209S00
			 WHERE PR_ID_009 = codPromo
	  	   	 AND CALI_ACT_009 = activa
	  	   	 AND CALI_TIPO_009 = tipo
			 ORDER BY FECHA DESC;

	   fecha  		  DATE;
	   denom  		  VARCHAR(100);
BEGIN
	 -- Se buscan las calificaciones definitivas de la promoción
	 -- que estén activas y se selecciona la que tenga la mayor fecha de todas
	 OPEN cCal(codPromo, tipoDef, activa);

 	 FETCH cCal INTO fecha, denom;
--	 DBMS_OUTPUT.Put_Line('fecha ' || fecha || ' - denom ' || denom);
	 CLOSE cCal;

	 IF (FECHA IS NOT NULL) THEN
--		DBMS_OUTPUT.Put_Line('tiene calificaciones definitivas');
	 	RETURN denom;
	 ELSE
--	 	 DBMS_OUTPUT.Put_Line('no tiene calificaciones definitivas');

 		 --Si no tiene calificaciones definitivas se hace la misma búsqueda para
 		 --las provisionales
		 OPEN cCal(codPromo, tipoProv, activa);

	 	 FETCH cCal INTO fecha, denom;
--		 DBMS_OUTPUT.Put_Line('fecha ' || fecha || ' - denom ' || denom);
		 CLOSE cCal;

		 IF (FECHA IS NOT NULL) THEN
--			DBMS_OUTPUT.Put_Line('tiene calificaciones provisionales');
		 	RETURN denom;
		 ELSE
--		 	 DBMS_OUTPUT.Put_Line('no tiene calificaciones provisionales');
		 	 RETURN '';
	     END IF;
	 END IF;


END;
END;
/
CREATE OR REPLACE FUNCTION buscarFechaCaliProv (caliId IN NUMBER,
	   caliMod IN NUMBER,promoId IN NUMBER)
	   RETURN DATE AS
BEGIN
	   DECLARE

	   FECHA DATE;

BEGIN
	   SELECT DECODE(PROMOS.TIPR_OV_ID_008,'EE',CALIS.CALI_ACTA_009,CALIS.CALI_CONC_009) INTO FECHA
	   FROM Q0208S00 PROMOS,
	   		Q0209S00 CALIS
	   WHERE
	   		PROMOS.PR_ID_008=promoId
		AND	PROMOS.PR_ID_008=CALIS.PR_ID_009
		AND	CALIS.CALI_ID_009=caliId
		AND	CALIS.CALI_MOD_009=caliMod;

	   RETURN FECHA;

	  EXCEPTION
	 	 WHEN NO_DATA_FOUND
		 THEN RETURN '';
END;
END;
/
CREATE OR REPLACE FUNCTION buscarFechaCaliProvPromo (codPromo IN NUMBER,
	   tipoDef IN VARCHAR, tipoProv IN VARCHAR, activa IN NUMBER)
	   RETURN DATE AS
BEGIN
	 DECLARE CURSOR cCal (codPromo NUMBER, tipo VARCHAR, activa NUMBER)
	 	IS SELECT DECODE(Q0208S00.TIPR_OV_ID_008,'EE',Q0209S00.CALI_ACTA_009,Q0209S00.CALI_CONC_009)
		   		  AS FECHA
			 FROM Q0209S00,
			      Q0208S00
			 WHERE Q0209S00.PR_ID_009 = codPromo
			 AND Q0209S00.PR_ID_009=Q0208S00.PR_ID_008
	  	   	 AND Q0209S00.CALI_MOD_009 = 1
	  	   	 AND Q0209S00.CALI_TIPO_009 = tipo
			 ORDER BY FECHA ASC;

	   fecha  		  DATE;

BEGIN

 		 --Si no tiene calificaciones definitivas se hace la misma búsqueda para
 		 --las provisionales
		 OPEN cCal(codPromo, tipoProv, activa);

	 	 FETCH cCal INTO fecha;
--		 DBMS_OUTPUT.Put_Line('fecha ' || fecha || ' - denom ' || denom);
		 CLOSE cCal;

		 IF (FECHA IS NOT NULL) THEN
--			DBMS_OUTPUT.Put_Line('tiene calificaciones provisionales');
		 	RETURN FECHA;
		 ELSE
--		 	 DBMS_OUTPUT.Put_Line('no tiene calificaciones provisionales');
		 	 RETURN '';
	     END IF;



END;
END;
/
CREATE OR REPLACE FUNCTION checkTieneDocsDependientes (idNodo IN NUMBER,tipoDoc IN NUMBER,fechaActual IN VARCHAR,idioma IN NUMBER)
	   RETURN VARCHAR AS
BEGIN
	 DECLARE CURSOR NODOSHIJOS
	 IS SELECT ARBOL.ARB_ID_020 FROM Q0220S00 ARBOL WHERE ARBOL.ARB_NODPAD_020=idNodo;

	 COD_NODOHIJO		 NUMBER(3);
	 NODO_NOFINAL		 NUMBER(1);
	 NUMERO_HIJOS		 NUMBER(4);
	 TIENE_DOCSDEP		 NUMBER(1) := 0;
 	 TIENE_DOCSDEP_TEMP	 NUMBER(1) := 0;
	 NUM_DOCS			 NUMBER(4) := 0;

	 --Constantes
	 TIPO_PUBLICO    CONSTANT NUMBER(1) := 0;
     TIPO_GOV		 CONSTANT NUMBER(1) := 1;
     TIPO_DEP		 CONSTANT NUMBER(1) := 2;
	 TIPO_ADM		 CONSTANT NUMBER(1) := 3;
	 CAS		 	 CONSTANT NUMBER(1) := 0;
	 EUS		 	 CONSTANT NUMBER(1) := 1;
BEGIN

	 --DBMS_OUTPUT.Put_Line('hola mundo SOY EL NODO '||idNodo);

	 SELECT ARBOL.ARB_ESCARP_020 INTO NODO_NOFINAL FROM Q0220S00 ARBOL WHERE ARBOL.ARB_ID_020=idNodo;

	 IF (NODO_NOFINAL = 1) THEN
	  	--comprobar nodos hijos
		--DBMS_OUTPUT.Put_Line('nodo no final');

		 SELECT COUNT(*) INTO NUMERO_HIJOS FROM Q0220S00 ARBOL WHERE ARBOL.ARB_NODPAD_020=idNodo;

		 --DBMS_OUTPUT.Put_Line('NUMERO_HIJOS'||NUMERO_HIJOS);

		 OPEN NODOSHIJOS;

		 FOR i IN 1 .. NUMERO_HIJOS LOOP
		 	   FETCH NODOSHIJOS INTO COD_NODOHIJO;
			   EXIT WHEN NODOSHIJOS%NOTFOUND;

			   --DBMS_OUTPUT.Put_Line('COD_NODOHIJO'||COD_NODOHIJO);

			   TIENE_DOCSDEP_TEMP:=checkTieneDocsDependientes (COD_NODOHIJO,tipoDoc,fechaActual,idioma);

			   --DBMS_OUTPUT.Put_Line('TIENE_DOCSDEP_TEMP'||TIENE_DOCSDEP_TEMP);

			   IF(TIENE_DOCSDEP_TEMP = 1) THEN
			   			TIENE_DOCSDEP:=1;
						EXIT;
			   END IF;

		 END LOOP;

		 CLOSE NODOSHIJOS;

		 --DBMS_OUTPUT.Put_Line('salgo bucle');

		 --DBMS_OUTPUT.Put_Line('TIENE_DOCSDEP = retorno'||TIENE_DOCSDEP);

         RETURN TIENE_DOCSDEP;
	 ELSE
	  IF(idioma=CAS) THEN
	    --buscar documentos
		--DBMS_OUTPUT.Put_Line('nodo final');
		IF(tipoDoc=TIPO_PUBLICO) THEN
			--DBMS_OUTPUT.Put_Line('documentos publicos');
			SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND	  DOCS.DOC_PUBLIC_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEC_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEC_021)));
		ELSIF(tipoDoc=TIPO_GOV) THEN
			--DBMS_OUTPUT.Put_Line('documentos gobierno');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		DOCS.DOC_GOBNO_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEC_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEC_021)));
		ELSIF(tipoDoc=TIPO_DEP) THEN
		    --DBMS_OUTPUT.Put_Line('documentos departamento');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		DOCS.DOC_DEPTO_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEC_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEC_021)));
		ELSIF(tipoDoc=TIPO_ADM) THEN
		    --DBMS_OUTPUT.Put_Line('documentos departamento');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		(DOCS.DOC_DEPTO_021=1 OR DOCS.DOC_GOBNO_021=1)
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEC_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEC_021)));
		ELSE
			NUM_DOCS:=0;
		END IF;

	 ELSE
	 	--buscar documentos
		--DBMS_OUTPUT.Put_Line('nodo final');
		IF(tipoDoc=TIPO_PUBLICO) THEN
			--DBMS_OUTPUT.Put_Line('documentos publicos');
			SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND	  DOCS.DOC_PUBLIC_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEE_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEE_021)));
		ELSIF(tipoDoc=TIPO_GOV) THEN
			--DBMS_OUTPUT.Put_Line('documentos gobierno');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		DOCS.DOC_GOBNO_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEE_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEE_021)));
		ELSIF(tipoDoc=TIPO_DEP) THEN
		    --DBMS_OUTPUT.Put_Line('documentos departamento');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		DOCS.DOC_DEPTO_021=1
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEE_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEE_021)));
		ELSIF(tipoDoc=TIPO_ADM) THEN
		    --DBMS_OUTPUT.Put_Line('documentos departamento');
	 	 	SELECT COUNT(*) INTO NUM_DOCS
			FROM Q0220S00 ARBOL,
				 Q0222S00 REL,
				 Q0221S00 DOCS
			WHERE	ARBOL.ARB_ID_020=REL.ARB_ID_022
			AND		ARBOL.ARB_ID_020=idNodo
			AND		REL.DOC_ID_022=DOCS.DOC_ID_021
			AND (DOCS.DOC_FVIGD_021 IS NULL
            OR   DOCS.DOC_FVIGD_021 <= TO_DATE(fechaActual,'DD/MM/YYYY'))
            AND  (DOCS.DOC_FVIGH_021 IS NULL
            OR    DOCS.DOC_FVIGH_021 >= TO_DATE(fechaActual,'DD/MM/YYYY'))
			AND		(DOCS.DOC_DEPTO_021=1 OR DOCS.DOC_GOBNO_021=1)
			AND (DOCS.DOC_URL_021 IS NOT NULL OR
			     (DOCS.DOC_FILEE_021 IS NOT NULL AND
				  1= (SELECT COUNT(*)
				      FROM Q0268S00 BLOBS
					  WHERE BLOBS.DOC_ID_068=DOCS.DOC_ID_021 AND
				            BLOBS.FIC_NOM_068=DOCS.DOC_FILEE_021)));
		ELSE
			NUM_DOCS:=0;
		END IF;
	 END IF;


		--DBMS_OUTPUT.Put_Line('NUM_DOCS'||NUM_DOCS);

		IF(NUM_DOCS=0) THEN
				RETURN 0;
		ELSE
				RETURN 1;
		END IF;

	 END IF;

END;
END;
/
CREATE OR REPLACE FUNCTION obtenerDatosNodoPadre (idNodo IN NUMBER,idIdioma IN NUMBER)
	   RETURN VARCHAR AS
BEGIN
	 DECLARE

	 COD_NODO		 NUMBER(3);
	 DESNODO	     VARCHAR2(100);

 	 CAS			 CONSTANT NUMBER(1):=0;
	 EUS			 CONSTANT NUMBER(1):=1;

BEGIN



	 	SELECT
		  ARBOLPADRE.ARB_ID_020,
		  DECODE(idIdioma,CAS,ARBOLPADRE.ARB_DESC_020,ARBOLPADRE.ARB_DESE_020)
		  into COD_NODO,DESNODO
		FROM
		  Q0220S00 ARBOL,
		  Q0220S00 ARBOLPADRE
		WHERE
		  ARBOL.ARB_ID_020=idNodo AND
		  ARBOLPADRE.ARB_ID_020=ARBOL.ARB_NODPAD_020 AND
		  ARBOLPADRE.ARB_ID_020!=0;

		--DBMS_OUTPUT.Put_Line('obtener nodo padre '||COD_NODO||'@'||DESNODO);

		RETURN COD_NODO||'@'||DESNODO;

	 EXCEPTION
	 	 WHEN NO_DATA_FOUND
		 THEN RETURN '';

END;
END;
/
CREATE OR REPLACE FUNCTION obtenerRutaDoc (idDoc IN NUMBER,idIdioma IN NUMBER)
	   RETURN VARCHAR AS
BEGIN
	 DECLARE
	 --Constantes
	 NODO_PADRE      CONSTANT NUMBER(3) := 0;
	 CAS			 CONSTANT NUMBER(1) := 0;
 	 EUS			 CONSTANT NUMBER(1) := 1;

	 CURSOR nodos (idNodo NUMBER,idIdioma NUMBER)
	 IS SELECT REL.ARB_ID_022 AS cod_nodo_padre,
	           DECODE(idIdioma,CAS,ARBOL.ARB_DESC_020,ARBOL.ARB_DESE_020) AS des_nodo_padre
	 	FROM Q0222S00 REL,
		     Q0220S00 ARBOL
		WHERE REL.DOC_ID_022=idDoc
		  AND REL.ARB_ID_022=ARBOL.ARB_ID_020;

	 DATOS_NODO_PADRE    VARCHAR2(120);

	 COD_NODO_PADRE		 NUMBER(3);
	 DES_NODO_PADRE      VARCHAR2(100);

	 RUTA				 VARCHAR2(4000):='';
	 RUTA_TOTAL			 VARCHAR2(8000):='';




BEGIN

	 --DBMS_OUTPUT.Put_Line('hola mundo SOY EL DOC '||idDoc);


	 OPEN nodos(idDoc,idIdioma);


	 LOOP

		 FETCH nodos INTO COD_NODO_PADRE,DES_NODO_PADRE;
		 EXIT WHEN nodos%NOTFOUND;

		 RUTA:=COD_NODO_PADRE||'@'||DES_NODO_PADRE;

		 WHILE (COD_NODO_PADRE!=NODO_PADRE)
		 LOOP
		  	--buscar datos nodo padre
			 --DBMS_OUTPUT.Put_Line('nodo padre'||COD_NODO_PADRE);

			 DATOS_NODO_PADRE:=obtenerDatosNodoPadre(COD_NODO_PADRE,idIdioma);

			 --DBMS_OUTPUT.Put_Line('DATOS_NODO_PADRE'||DATOS_NODO_PADRE);

			 COD_NODO_PADRE:=TO_NUMBER(SUBSTR(DATOS_NODO_PADRE,0,INSTR(DATOS_NODO_PADRE,'@')-1));

	 		 RUTA:=RUTA||'&'||DATOS_NODO_PADRE;

		 END LOOP;


		 RUTA_TOTAL:=RUTA_TOTAL||'$'||RUTA;

		 RUTA:='';

		 --DBMS_OUTPUT.Put_Line('RUTA'||RUTA);
		 --DBMS_OUTPUT.Put_Line('RUTA_TOTAL'||RUTA_TOTAL);
	 END LOOP;

	 CLOSE nodos;

	 --DBMS_OUTPUT.Put_Line('RETORNO'||RUTA_TOTAL);

	 RETURN RUTA_TOTAL;

END;
END;
/

