--Cambio sql 

ALTER TABLE AD74A14T00
  ADD (
    OBSERVACIONES_14 VARCHAR2(1750 BYTE) NULL
  );

COMMENT ON COLUMN AD74A14T00.OBSERVACIONES_14
  IS 'Observaciones de la entrada';


CREATE OR REPLACE FORCE EDITIONABLE VIEW "AD74A"."AD74A01V00" ("IDTIPOENTRADA", "IDENTRADA", "ESTADO", "IDJMS", "PROCEDUREID", "FREGISTRO", "NUMREGISTRO", "NUMEXPEDIENTE", "IDEXPEDIENTE", "URGENCIA", "ERROREXPEDIENTE", "IDSOLICITANTE", "IDENTIFICADOR", "NOMBRE", "APELLIDO1", "APELLIDO2", "TIPO", "NUMREGORIGINAL", "FREGORIGINAL", "REMITENTE", "BLOQUEO", "USURESPONSABLE", "NUMDOCUMENTOS", "QRCODE", "MOTIVOOCULTAR", "OBSERVACIONES", "RESUMEN") AS 
  SELECT
    --T2.ID_TIPO_ENTRADA_00,
    -- [jbhg] en vez del tipo de entrada del expediente, se pasa a consultar el submission type de la accion telematica.
    -- Si tiene mÃ¡s de 3 caracteres, es presencial (r02d1869618978B004C6E5823C4C682F2C2B681A2E3F/r02dFE0673A510281D750C9F9758A73EE2A8DD217E5C)
    -- si no es telematica
    CASE
        WHEN LENGTH(T1.SUBMISIONTYPE_14) > 3 THEN '3'
        ELSE '1'
    END,
    T1.ID_ENTRADA_14, T1.ESTADO_14, T1.IDJMS_14, T1.PROCEDUREID_14, T1.F_REGISTRO_14, T1.NUM_REGISTRO_14,
T2.NUM_EXPEDIENTE_00, T2.ID_EXPEDIENTE_00, NVL(T2.URGENCIA_00,0), T2.ERROR_EXPEDIENTE_00, T3.ID_SOLICITANTE_01, T3.IDENTIFICADOR_01, T3.NOMBRE_01, T3.APELLIDO1_01, T3.APELLIDO2_01, '0' || T1.SUBMISIONTYPE_14,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, OBSERVACIONES_14, NULL
FROM
  BANDEJA_TELEMATICA T1
  INNER JOIN EXPEDIENTES T2 ON (T1.FOLDERID_14 = T2.FOLDER_ID_00)
  LEFT JOIN SOLICITANTES T3 ON (
    T2.ID_SOLICITANTE_00 = T3.ID_SOLICITANTE_01
  )
  INNER JOIN ESTADOS T4 ON (
    T2.ID_ESTADO_00 = T4.ID_ESTADO_30
  )
WHERE
  1 = 1
UNION
SELECT '2', T1.ID_ENTRADA_12, T1.ESTADO_12, T1.IDJMS_12, T1.PROCEDUREID_12, T1.F_REGISTRO_12, T1.NUM_REGISTRO_12,
T1.NUM_EXPEDIENTE_12, T2.ID_EXPEDIENTE_00, NVL(T1.URGENCIA_12,0), NULL, NULL, NULL,
NULL, NULL, NULL, T1.TIPO_12,
NUM_REG_ORIGINAL_12, T1.F_REG_ORIGINAL_12, T1.REMITENTE_12, T1.BLOQUEO_12, T1.USU_RESPONSABLE_12,
  (
    SELECT
      COUNT(*)
    FROM
      DOCUMENTO_PAPEL0 T2
    WHERE
      T2.ID_ENTRADA_13 = T1.ID_ENTRADA_12
  ),
  T1.QRCODE_12, MOTIVO_OCULTAR_12, OBSERVACIONES_12,RESUMEN_12
FROM
  BANDEJA_PAPEL0 T1 LEFT JOIN EXPEDIENTES T2 ON (T1.NUM_EXPEDIENTE_12 = T2.NUM_EXPEDIENTE_00)
WHERE
  1 = 1;

/



CREATE OR REPLACE FORCE EDITIONABLE VIEW "AD74A"."AD74A07V20" (
    "ID_EXPEDIENTE_00",
    "OIDDOKUSI_18",
    "NOMBRE_DOC_18",
    "F_INFORME_02",
    "SOLA_02",
    "ESTA_SOLA_MANANA_02",
    "ESTA_SOLA_TARDE_02",
    "ESTA_SOLA_NOCHE_02",
    "RELACION_ENTORNO_02",
    "FAMILIAR_FALLECIDO_02",
    "VINCULO_FALLECIDO_02",
    "VISITAS_FAMILIA_02",
    "VISITAS_AMIGOS_02",
    "FRECUENCIA_SALIDAS_02",
    "PERIODO_SIN_VISITAS_02",
    "TIEMPO_SIN_VISITAS_02",
    "OBSERVACIONES_02"
) AS
    SELECT
        *
    FROM
        (
            SELECT
                docs.id_expediente_00,
                dt.oiddokusi_18,
                dt.nombre_doc_18,
                inf_soc.f_informe_02,
                inf_soc.sola_02,
                inf_soc.esta_sola_manana_02,
                inf_soc.esta_sola_tarde_02,
                inf_soc.esta_sola_noche_02,
                inf_soc.relacion_entorno_02,
                inf_soc.familiar_fallecido_02,
                inf_soc.vinculo_fallecido_02,
                inf_soc.visitas_familia_02,
                inf_soc.visitas_amigos_02,
                inf_soc.frecuencia_salidas_02,
                inf_soc.periodo_sin_visitas_02,
                inf_soc.tiempo_sin_visitas_02,
                inf_soc.observaciones_02
            FROM
                documentos_tarea dt,
                informes         inf,
                informe_social   inf_soc,
                (
                    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 = 3--INFORME SOCIAL
                    GROUP BY
                        ex.id_expediente_00,
                        dt.id_tipo_doc_18
                )                docs
            WHERE
                    dt.id_doc_tarea_18 = docs.id_doc_tarea_18
                AND inf.id_expediente_16 (+) = docs.id_expediente_00
                AND inf_soc.id_informe_02 (+) = inf.id_informe_16
            UNION
            SELECT
                de.id_expediente_13,
                de.oid_documento_13,
                de.nombre_doc_13
                || '.'
                || de.extension_13,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL
            FROM
                     ad74a12t00 be
                INNER JOIN ad74a13t00 de ON ( de.id_entrada_13 = be.id_entrada_12 )
            WHERE
                de.id_tipo_doc_13 = 3
        );

