
DROP VIEW AD74A07V00;
DROP VIEW AD74A07V10;


 CREATE OR REPLACE FORCE EDITIONABLE VIEW "AD74A"."AD74A07V00" ("ID_EXPEDIENTE_00", "OIDDOKUSI_18", "OIDDOKUSI_18_MAX", "NOMBRE_DOC_18") AS 
  SELECT id_expediente_00,OIDDOKUSI_18, MAX(OIDDOKUSI_18)OVER (PARTITION BY id_expediente_00) AS OIDDOKUSI_18_max, NOMBRE_DOC_18
FROM(SELECT DOCS.id_expediente_00,
DT.OIDDOKUSI_18,
DT.NOMBRE_DOC_18
FROM DOCUMENTOS_TAREA DT,(
SELECT
--EX
ex.id_expediente_00 id_expediente_00,
--DT
MAX(dt.id_doc_tarea_18) id_doc_tarea_18,
dt.id_tipo_doc_18 id_tipo_doc_18
FROM DOCUMENTOS_TAREA DT,TAREAS_TRAMITE TT, TRAMITES_EXPEDIENTE TE, EXPEDIENTES EX
WHERE 1=1
AND tt.id_tarea_tram_11=dt.id_tarea_tram_18
AND TE.ID_TRAMITE_EXP_10 = TT.ID_TRAMITE_EXP_11
AND ex.id_expediente_00=te.id_expediente_10
AND TE.ID_ESTADO_10=2--TAREA FINALIZADA
AND OIDDOKUSI_18 IS NOT NULL
AND dt.id_tipo_doc_18 =20--INFORME MEDICO
GROUP BY ex.id_expediente_00,dt.id_tipo_doc_18) DOCS
WHERE DT.id_doc_tarea_18=DOCS.id_doc_tarea_18
UNION 
SELECT de.id_expediente_13, de.oid_documento_13, de.nombre_doc_13 || '.' || de.extension_13
FROM AD74A12T00 BE INNER JOIN AD74A13T00 DE ON (de.id_entrada_13 = BE.ID_ENTRADA_12)
WHERE de.id_tipo_doc_13 = 20);

CREATE OR REPLACE FORCE EDITIONABLE VIEW "AD74A"."AD74A07V10" ("ID_EXPEDIENTE_00", "OIDDOKUSI_18", "NOMBRE_DOC_18") AS 
  SELECT id_expediente_00,
OIDDOKUSI_18,
NOMBRE_DOC_18
FROM AD74A07V00 WHERE OIDDOKUSI_18=OIDDOKUSI_18_MAX;


ALTER VIEW AD74A07V20 COMPILE;

commit;

/