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))
				OR
				DOCS.DOC_CD_021  IS NOT NULL

			   );
		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))
				OR
				DOCS.DOC_CD_021  IS NOT NULL

			   );
		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))
				OR
				DOCS.DOC_CD_021  IS NOT NULL

			   );
		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))
				OR
				DOCS.DOC_CD_021  IS NOT NULL

			   );
		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_URLE_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))
				OR
				DOCS.DOC_CD_E_021  IS NOT NULL

			   );
		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_URLE_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))
				OR
				DOCS.DOC_CD_E_021  IS NOT NULL

			   );
		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_URLE_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))
				OR
				DOCS.DOC_CD_E_021  IS NOT NULL

			   );
		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_URLE_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))
				OR
				DOCS.DOC_CD_E_021  IS NOT NULL

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