CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B46V00" ("IDFASE", "IDTRAMITE", "IDPROV", "IDANO", "IDNUMEXP", "IDHIST", "IDCOMUNICACION", "TIPOORG", "IDORGANISMO", "DESCES", "DESCEU", "EMAILES", "EMAILEU", "ESTADO", "TIPOTRAMITE", "FECHACOMUNICACIONC", "FECHACOMUNICACIONE") AS 
  SELECT T14.ID_001_FASE,    t14.ID_002_TRAMITE,    t14.ID_PROV_010,    t14.ID_ANO_010,    t14.ID_010_NUM_EXP,    T14.ID_014,    t46.ID_046,
    'E',    t10.ID_047_EMPRESA,  'Empresa' , 'Enpresa', 'Mis Gestiones', 'Nire Kudeaketak',  NVL(ESTADO_046,'0') ESTADO_046,    t02.ENT_SAL_002,
    TO_CHAR(t46.FEC_COMUNIC_046, 'dd/mm/yyyy'),    TO_CHAR(t46.FEC_COMUNIC_046, 'yyyy/mm/dd')
  FROM ab03b14t00 t14
  INNER JOIN ab03b10t00 t10  ON t10.ID_PROV_010 = t14.ID_PROV_010  AND t10.ID_ANO_010 = t14.ID_ANO_010  AND t10.ID_010     = t14.ID_010_NUM_EXP
  INNER JOIN ab03b46t00 t46  ON t46.ID_014_HISTO_TRAM = t14.ID_014  AND t46.ID_047_EMPRESA   = t10.ID_047_EMPRESA
  INNER JOIN ab03b02t00 t02  ON t02.ID_001_FASE = t14.ID_001_FASE  AND t02.ID_002     = t14.ID_002_TRAMITE
  UNION
  (SELECT T14.ID_001_FASE,
    t14.ID_002_TRAMITE,    
    t14.ID_PROV_010,    
    t14.ID_ANO_010,    
    t14.ID_010_NUM_EXP,    
    T14.ID_014,
    T46.ID_046,    
    'O',    
    t45.ID_045,    
    t45.DESC_ES_045,    
    t45.DESC_EU_045 ,    
    T39.EMAIL_039,  
    T39.EMAIL_039, 
    NVL(ESTADO_046,'0') ESTADO_046, 
    t02.ENT_SAL_002,    
    TO_CHAR(t46.FEC_COMUNIC_046, 'dd/mm/yyyy'),   
    TO_CHAR(t46.FEC_COMUNIC_046, 'yyyy/mm/dd')
  FROM ab03b14t00 t14
  INNER JOIN ab03b46t00 t46  ON t46.ID_014_HISTO_TRAM = t14.ID_014
  INNER JOIN ab03b39t00 t39  ON t39.ID_PERFIL_039 = t14.ID_PROV_010 and t46.ID_039_ORGAN = t39.ID_045_ORG
  INNER JOIN ab03b45t00 t45  ON t45.ID_045 = t39.ID_045_ORG
  INNER JOIN ab03b02t00 t02  ON t02.ID_001_FASE = t14.ID_001_FASE  AND t02.ID_002 = t14.ID_002_TRAMITE
  AND t14.ID_014 NOT IN (
   SELECT T14.ID_014
     FROM ab03b14t00 t14
      INNER JOIN ab03b46t00 t46  ON t46.ID_014_HISTO_TRAM = t14.ID_014
      INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014 AND T56.PROVINCIA_TRABAJO_056 <> T14.ID_PROV_010
      INNER JOIN ab03b39t00 t39  ON t39.ID_PERFIL_039 = PROVINCIA_TRABAJO_056 and t46.ID_039_ORGAN = t39.ID_045_ORG
      INNER JOIN ab03b45t00 t45  ON t45.ID_045 = t39.ID_045_ORG
      INNER JOIN ab03b02t00 t02  ON t02.ID_001_FASE = t14.ID_001_FASE  AND t02.ID_002 = t14.ID_002_TRAMITE)
UNION ALL
  SELECT T14.ID_001_FASE,
    t14.ID_002_TRAMITE,    
    t14.ID_PROV_010,    
    t14.ID_ANO_010,    
    t14.ID_010_NUM_EXP,    
    T14.ID_014,
    T46.ID_046,    
    'O',    
    t45.ID_045,    
    t45.DESC_ES_045,    
    t45.DESC_EU_045 ,    
    T39.EMAIL_039,  
    T39.EMAIL_039, 
    NVL(ESTADO_046,'0') ESTADO_046, 
    t02.ENT_SAL_002,    
    TO_CHAR(t46.FEC_COMUNIC_046, 'dd/mm/yyyy'),   
    TO_CHAR(t46.FEC_COMUNIC_046, 'yyyy/mm/dd')
  FROM ab03b14t00 t14
      INNER JOIN ab03b46t00 t46  ON t46.ID_014_HISTO_TRAM = t14.ID_014
      INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014 AND T56.PROVINCIA_TRABAJO_056 <> T14.ID_PROV_010
      INNER JOIN ab03b39t00 t39  ON t39.ID_PERFIL_039 = PROVINCIA_TRABAJO_056 and t46.ID_039_ORGAN = t39.ID_045_ORG
      INNER JOIN ab03b45t00 t45  ON t45.ID_045 = t39.ID_045_ORG
      INNER JOIN ab03b02t00 t02  ON t02.ID_001_FASE = t14.ID_001_FASE  AND t02.ID_002 = t14.ID_002_TRAMITE);
      

CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B56V00INIOBRA" ("ID_014", "ID_001_FASE", "ID_002_TRAMITE", "ID_010_NUM_EXP", "ID_ANO_010", "ID_PROV_010", "ID_056", "ID_PROVINCIA_TRABAJO_056", "PROVINCIA_TRABAJO_056", "ID_MUNICIP_TRABAJO_056", "MUNICIP_TRABAJO_056", "CALLE_TRABAJO_056", "NUMERO_TRABAJO_056", "PISO_TRABAJO_056", "LETRA_TRABAJO_056", "CPOSTAL_TRABAJO_056", "F_INICIO_TRABAJO_056", "ID_057") AS 
  SELECT t14.id_014,
    T14.Id_001_Fase,
    T14.Id_002_Tramite,
    T14.Id_010_Num_Exp,
    T14.Id_Ano_010,
    T14.Id_Prov_010,
    T56.Id_056,
    t56.PROVINCIA_TRABAJO_056,
    NORAPROV.DS_O PROV_DO,
    t56.MUNICIP_TRABAJO_056,
    NORAMUN.DS_O MUN_DO,
    CASE
      WHEN NORACALLE.DS_O IS NULL
      THEN t56.CALLE_TRABAJO_056
      ELSE CONCAT(CONCAT(CONCAT(NORACALLE.DS_O,' ('),NORACALLE.TIPO_VIA),')')
    END AS CALLE_TRABAJO,
    t56.NUMERO_TRABAJO_056,
    t56.PISO_TRABAJO_056,
    t56.LETRA_TRABAJO_056,
    t56.CPOSTAL_TRABAJO_056,
    t56.F_INICIO_TRABAJO_056,
    t56.ID_057
  FROM ab03b14t00 t14
  INNER JOIN ab03b56t00 t56 ON t14.id_014 = T56.Id_014
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV ON NORAPROV.ID = t56.PROVINCIA_TRABAJO_056
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN ON NORAMUN.PROVINCIA_ID = t56.PROVINCIA_TRABAJO_056
                                          AND NORAMUN.ID          = t56.MUNICIP_TRABAJO_056
  LEFT JOIN T17I.T17_CALLE NORACALLE ON NORACALLE.PROVINCIA_ID  = t56.PROVINCIA_TRABAJO_056
                                          AND NORACALLE.MUNICIPIO_ID = t56.MUNICIP_TRABAJO_056
                                          AND t56.CALLE_TRABAJO_056 LIKE NORACALLE.ID
  where t14.id_001_fase=4 and t14.id_002_tramite=2;
  
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B09V00" ("ID_009", "ID_008_TIPO_PLAN", "DESCPLAN_ES", "DESCPLAN_EU", "DESCPLANR_ES", "DESCPLANR_EU", "NUMANO_009", "FLECTURA_009", "JMSID_009", "NUMREG_009", "FREGISTRO_009", "MENSAJE_009", "ID_001_FASE", "DESC_ES_001", "DESC_EU_001", "ID_002_TRAMITE", "DESC_ES_002", "DESC_EU_002", "REMITENTE_NOM_009", "SITUACION_009", "REMITENTE_ID_009", "EXP_RELAC_009", "ID_PROV_010", "ID_ANO_010", "ID_010_EXP") AS 
  SELECT T09.ID_009,
    T09.ID_008_TIPO_PLAN,
    DESC_ES_008,
    DESC_EU_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_ES_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_ES_008,1,1)
    END DESCR_ES_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_EU_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_EU_008,1,1)
    END DESCR_EU_008,
    T09.NUMANO_009,
    T09.FLECTURA_009,
    T09.JMSID_009,
    T09.NUMREG_009,
    T09.FREGISTRO_009,
    T09.MENSAJE_009,
    T09.ID_001_FASE,
    T01.DESC_ES_001,
    T01.DESC_EU_001,
    T09.ID_002_TRAMITE,
    T02.DESC_ES_002,
    T02.DESC_EU_002,
    T09.REMITENTE_NOM_009,
    T09.SITUACION_009,
    T09.REMITENTE_ID_009,
    t09.ID_PROV_010
    ||'/'
    ||t09.ID_ANO_010
    ||'/'
    || lpad(t09.ID_010_EXP,5,'0'),
    t09.ID_PROV_010,
    t09.ID_ANO_010,
    t09.ID_010_EXP
  FROM AB03B09T00 T09
  INNER JOIN AB03B01T00 T01
  ON T01.ID_001 = T09.ID_001_FASE
  INNER JOIN AB03B02T00 T02
  ON T02.ID_001_FASE = T09.ID_001_FASE
  AND T02.ID_002     = T09.ID_002_TRAMITE
  INNER JOIN AB03B08T00 T08
  ON T08.ID_008 = T09.ID_008_TIPO_PLAN
  INNER JOIN AB03B10T00 T10
  ON T10.ID_PROV_010 = T09.ID_PROV_010
  AND T10.ID_ANO_010 = T09.ID_ANO_010
  AND T10.ID_010     = T09.ID_010_EXP
  INNER JOIN AB03B47T00 T47
  ON T47.ID_047           = T10.ID_047_EMPRESA
  WHERE T09.SITUACION_009 = 'V'
AND T09.ID_009 NOT IN
  (SELECT T09.ID_009
    FROM AB03B09T00 T09
    INNER JOIN AB03B01T00 T01 ON T01.ID_001 = T09.ID_001_FASE
    INNER JOIN AB03B02T00 T02 ON T02.ID_001_FASE = T09.ID_001_FASE
                                AND T02.ID_002 = T09.ID_002_TRAMITE
    INNER JOIN AB03B08T00 T08 ON T08.ID_008 = T09.ID_008_TIPO_PLAN
    INNER JOIN AB03B10T00 T10 ON T10.ID_PROV_010 = T09.ID_PROV_010
                                AND T10.ID_ANO_010 = T09.ID_ANO_010
                                AND T10.ID_010     = T09.ID_010_EXP
    INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
    INNER JOIN AB03B14T00 T14 ON T09.Id_014_Tram = T14.ID_014
    INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014 AND T56.PROVINCIA_TRABAJO_056 <> T10.ID_PROV_010
    WHERE T09.SITUACION_009 = 'V'
    UNION ALL
    SELECT T09.ID_009
    FROM AB03B09T00 T09
    INNER JOIN AB03B01T00 T01 ON T01.ID_001 = T09.ID_001_FASE
    INNER JOIN AB03B02T00 T02 ON T02.ID_001_FASE = T09.ID_001_FASE
                                AND T02.ID_002 = T09.ID_002_TRAMITE
    INNER JOIN AB03B08T00 T08 ON T08.ID_008 = T09.ID_008_TIPO_PLAN
    INNER JOIN AB03B10T00 T10 ON T10.ID_PROV_010 = T09.ID_PROV_010
                                AND T10.ID_ANO_010 = T09.ID_ANO_010
                                AND T10.ID_010     = T09.ID_010_EXP
    INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
    INNER JOIN AB03B14T00 T14 ON T14.ID_014 = T09.Id_014_Tram
    INNER JOIN AB03B57T00 T57 ON T14.ID_014 = T57.ID_014 
    INNER JOIN AB03B56T00 T56 ON T56.ID_056 = T57.ID_056
                        AND T56.PROVINCIA_TRABAJO_056 <> T10.ID_PROV_010
    WHERE T09.SITUACION_009 = 'V')
UNION ALL
  SELECT T09.ID_009,
    T09.ID_008_TIPO_PLAN,
    DESC_ES_008,
    DESC_EU_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_ES_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_ES_008,1,1)
    END DESCR_ES_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_EU_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_EU_008,1,1)
    END DESCR_EU_008,
    T09.NUMANO_009,
    T09.FLECTURA_009,
    T09.JMSID_009,
    T09.NUMREG_009,
    T09.FREGISTRO_009,
    T09.MENSAJE_009,
    T09.ID_001_FASE,
    T01.DESC_ES_001,
    T01.DESC_EU_001,
    T09.ID_002_TRAMITE,
    T02.DESC_ES_002,
    T02.DESC_EU_002,
    T09.REMITENTE_NOM_009,
    T09.SITUACION_009,
    T09.REMITENTE_ID_009,
    t09.ID_PROV_010
    ||'/'
    ||t09.ID_ANO_010
    ||'/'
    || lpad(t09.ID_010_EXP,5,'0'),
    T56.PROVINCIA_TRABAJO_056,
    t09.ID_ANO_010,
    t09.ID_010_EXP
    FROM AB03B09T00 T09
    INNER JOIN AB03B01T00 T01 ON T01.ID_001 = T09.ID_001_FASE
    INNER JOIN AB03B02T00 T02 ON T02.ID_001_FASE = T09.ID_001_FASE
                                AND T02.ID_002 = T09.ID_002_TRAMITE
    INNER JOIN AB03B08T00 T08 ON T08.ID_008 = T09.ID_008_TIPO_PLAN
    INNER JOIN AB03B10T00 T10 ON T10.ID_PROV_010 = T09.ID_PROV_010
                                AND T10.ID_ANO_010 = T09.ID_ANO_010
                                AND T10.ID_010     = T09.ID_010_EXP
    INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
    INNER JOIN AB03B14T00 T14 ON T14.ID_014 = T09.Id_014_Tram
    INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014 AND T56.PROVINCIA_TRABAJO_056 <> T10.ID_PROV_010
    WHERE T09.SITUACION_009 = 'V'
UNION ALL
  SELECT T09.ID_009,
    T09.ID_008_TIPO_PLAN,
    DESC_ES_008,
    DESC_EU_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_ES_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_ES_008,1,1)
    END DESCR_ES_008,
    CASE
      WHEN T09.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T08.DESC_EU_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T08.DESC_EU_008,1,1)
    END DESCR_EU_008,
    T09.NUMANO_009,
    T09.FLECTURA_009,
    T09.JMSID_009,
    T09.NUMREG_009,
    T09.FREGISTRO_009,
    T09.MENSAJE_009,
    T09.ID_001_FASE,
    T01.DESC_ES_001,
    T01.DESC_EU_001,
    T09.ID_002_TRAMITE,
    T02.DESC_ES_002,
    T02.DESC_EU_002,
    T09.REMITENTE_NOM_009,
    T09.SITUACION_009,
    T09.REMITENTE_ID_009,
    t09.ID_PROV_010
    ||'/'
    ||t09.ID_ANO_010
    ||'/'
    || lpad(t09.ID_010_EXP,5,'0'),
    T56.PROVINCIA_TRABAJO_056,
    t09.ID_ANO_010,
    t09.ID_010_EXP
    FROM AB03B09T00 T09
    INNER JOIN AB03B01T00 T01 ON T01.ID_001 = T09.ID_001_FASE
    INNER JOIN AB03B02T00 T02 ON T02.ID_001_FASE = T09.ID_001_FASE
                                AND T02.ID_002 = T09.ID_002_TRAMITE
    INNER JOIN AB03B08T00 T08 ON T08.ID_008 = T09.ID_008_TIPO_PLAN
    INNER JOIN AB03B10T00 T10 ON T10.ID_PROV_010 = T09.ID_PROV_010
                                AND T10.ID_ANO_010 = T09.ID_ANO_010
                                AND T10.ID_010     = T09.ID_010_EXP
    INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
    INNER JOIN AB03B14T00 T14 ON T14.ID_014 = T09.Id_014_Tram
    INNER JOIN AB03B57T00 T57 ON T14.ID_014 = T57.ID_014 
    INNER JOIN AB03B56T00 T56 ON T56.ID_056 = T57.ID_056 AND T56.PROVINCIA_TRABAJO_056 <> T10.ID_PROV_010
    WHERE T09.SITUACION_009 = 'V';

    
 CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B10V00INIOBRA" ("ID_ANO_010", "ID_010", "ID_PROV_010", "PROVINCIA_TRABAJO_056", "NUM_REG_010", "ID_EXPEDIENTE", "FEC_REG_010", "TIPO_DOC_044", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "CALLE_047", "NUM_CALLE_047", "PISO_047", "LETRA_047", "CP_047", "LOCALIDAD_047", "MUNICIPIO_047", "PROVINCIA_047", "REPRESENTANTE_LEGAL_047", "TELF1_047", "TELF2_047", "EMAIL_047", "NUM_RERA_047", "ACTIVO_SN_047", "PROVINCIA_TRABAJO_010", "PROV_DO", "MUNICIP_TRABAJO_010", "MUN_DO", "CALLE_TRABAJO_010", "NUMERO_TRABAJO_010", "PISO_TRABAJO_010", "LETRA_TRABAJO_010", "CPOSTAL_TRABAJO_010", "F_INICIO_TRABAJO_010", "DURACION_DIAS_010", "ID_008_TIPO_PLAN", "PLAN_ES", "PLAN_EU", "DESCPLANR_ES", "DESCPLANR_EU", "EXP_SANC_010", "TECN_ASIGN_010", "EXP_ESPECIF_010", "REQ_LEIDO_010", "NOTIF_LEIDA_010", "EXP_CERRADO_010", "ESTADO_EXP_010", "IDIOMA_COMUNIC_010", "ID_001_FASE", "FASE_ES", "FASE_EU", "ID_002_TRAMITE", "TRAMITE_ES", "TRAMITE_EU", "ID_034_ESTADO", "ESTADO_ES", "ESTADO_EU", "OBSERVACIONES_014", "ID_047", "FECHA_VISITA_010", "NIF_TITULAR_010", "NOMBRE_TITULAR_010", "APE1_TITULAR_010", "APE2_TITULAR_010", "OID_DOC_VISITA_010", "OID_DOC_SANCION_010") AS 
  SELECT T10.ID_ANO_010,
    T10.ID_010,
    T10.ID_PROV_010,
    T10.ID_PROV_010 as provincia_trabajo,
    T10.NUM_REG_010,
    T10.ID_PROV_010
    || '/'
    || T10.ID_ANO_010
    || '/'
    || lpad(T10.ID_010,5,'0') ID_EXPEDIENTE, 
    T10.FEC_REG_010,
    T47.TIPO_DOC_044,
    T47.NIF_EMPRESA_047,
    NVL(T47.NOMBRE_EMPRESA_047, T47.RAZON_SOCIAL_047) AS RAZON_SOCIAL_047,
    CASE
      WHEN NORACALLEEMP.DS_O IS NULL
      THEN T47.CALLE_047
      ELSE CONCAT(CONCAT(CONCAT(NORACALLEEMP.DS_O,' ('),NORACALLEEMP.TIPO_VIA),')')
    END AS CALLE_EMPRESA,
    T47.NUM_CALLE_047,
    T47.PISO_047,
    T47.LETRA_047,
    T47.CP_047,
    T47.LOCALIDAD_047,
    T47.MUNICIPIO_047,
    T47.PROVINCIA_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T47.NUM_RERA_047,
    T47.ACTIVO_SN_047,
    T10.PROVINCIA_TRABAJO_010,
    NORAPROV.DS_O PROV_DO,
    T10.MUNICIP_TRABAJO_010,
    NORAMUN.DS_O MUN_DO,
    CASE
      WHEN NORACALLE.DS_O IS NULL
      THEN T10.CALLE_TRABAJO_010
      ELSE CONCAT(CONCAT(CONCAT(NORACALLE.DS_O,' ('),NORACALLE.TIPO_VIA),')')
    END AS CALLE_TRABAJO,
    T10.NUMERO_TRABAJO_010,
    T10.PISO_TRABAJO_010,
    T10.LETRA_TRABAJO_010,
    T10.CPOSTAL_TRABAJO_010,
    T10.F_INICIO_TRABAJO_010,
    T10.DURACION_DIAS_010,
    T10.ID_008_TIPO_PLAN,
    T8.DESC_ES_008,
    T8.DESC_EU_008,
    CASE
      WHEN T10.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T8.DESC_ES_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T8.DESC_ES_008,1,1)
    END DESCR_ES_008,
    CASE
      WHEN T10.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T8.DESC_EU_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T8.DESC_EU_008,1,1)
    END DESCR_EU_008,
    T10.EXP_SANC_010,
    T10.TECN_ASIGN_010,
    T10.EXP_ESPECIF_010,
    T10.REQ_LEIDO_010,
    T10.NOTIF_LEIDA_010,
    T10.EXP_CERRADO_010,
    T10.ESTADO_EXP_010,
    T10.IDIOMA_COMUNIC_010,
    T14.ID_001_FASE,
    FASES.DESC_ES_001 FASE_DESC,
    FASES.DESC_EU_001 FASE_DESE,
    T14.ID_002_TRAMITE,
    TRAMITES.DESC_ES_002 TRAMITE_DESC,
    TRAMITES.DESC_EU_002 TRAMITE_DESE,
    T14.ID_034_ESTADO,
    ESTADOS.DESC_ES_034,
    ESTADOS.DESC_EU_034,
    t14.OBSERVACIONES_014,
    T47.id_047,
    T10.FECHA_VISITA_010,
    T10.NIF_TITULAR_010,
    T10.NOMBRE_TITULAR_010,
    T10.APE1_TITULAR_010,
    T10.APE2_TITULAR_010,
    T10.OID_DOC_VISITA_010,
    T10.OID_DOC_SANCION_010
  FROM AB03B10T00 T10
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T10.ID_047_EMPRESA
  INNER JOIN AB03B14T00 T14
  ON T14.ID_010_NUM_EXP = T10.ID_010
  AND T14.ID_ANO_010    = T10.ID_ANO_010
  AND T14.ID_PROV_010   = T10.ID_PROV_010
  AND T14.ID_014       IN
  (SELECT MAX(id_014)
    FROM AB03B14T00
    GROUP BY ID_ANO_010,
      ID_010_NUM_EXP,
      ID_PROV_010)
  INNER JOIN AB03B08T00 T8  ON T8.ID_008 = T10.ID_008_TIPO_PLAN
  INNER JOIN AB03B01T00 FASES  ON FASES.ID_001 = T14.ID_001_FASE
  INNER JOIN AB03B02T00 TRAMITES  ON TRAMITES.ID_001_FASE = T14.ID_001_FASE
                                     AND TRAMITES.ID_002     = T14.ID_002_TRAMITE
  INNER JOIN AB03B34T00 ESTADOS  ON ESTADOS.ID_034= T14.ID_034_ESTADO
    --AND TRAMITES.ID_001_FASE||TRAMITES.ID_002<>11
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV  ON NORAPROV.ID = T10.PROVINCIA_TRABAJO_010
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN  ON NORAMUN.PROVINCIA_ID = T10.PROVINCIA_TRABAJO_010
                                      AND NORAMUN.ID          = T10.MUNICIP_TRABAJO_010
  LEFT JOIN T17I.T17_CALLE NORACALLE  ON NORACALLE.PROVINCIA_ID  = T10.PROVINCIA_TRABAJO_010
                                  AND NORACALLE.MUNICIPIO_ID = T10.MUNICIP_TRABAJO_010
                                  AND T10.CALLE_TRABAJO_010 LIKE NORACALLE.ID
  LEFT JOIN T17I.T17_LOCALIDAD NORALOCA ON NORALOCA.PROVINCIA_ID  = T47.PROVINCIA_047
                                AND NORALOCA.MUNICIPIO_ID = T47.MUNICIPIO_047
                                AND NORALOCA.ID           = T47.LOCALIDAD_047
  LEFT JOIN T17I.T17_CALLE NORACALLEEMP ON NORACALLEEMP.PROVINCIA_ID  = T47.PROVINCIA_047
                                AND NORACALLEEMP.MUNICIPIO_ID = T47.MUNICIPIO_047
                                AND T47.CALLE_047 LIKE NORACALLEEMP.ID
  WHERE T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010 NOT IN
(select T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010
    FROM AB03B10T00 T10
    INNER JOIN AB03B14T00 T14 ON T14.ID_010_NUM_EXP = T10.ID_010
                              AND T14.ID_ANO_010    = T10.ID_ANO_010
                              AND T14.ID_PROV_010   = T10.ID_PROV_010
    INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014
    GROUP BY T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010)
UNION ALL
SELECT CONS1.ID_ANO_010,
    CONS1.ID_010,
    CONS1.ID_PROV_010,
    T56.PROVINCIA_TRABAJO_056, 
    CONS1.NUM_REG_010,
    CONS1.ID_EXPEDIENTE, 
    CONS1.FEC_REG_010,
    CONS1.TIPO_DOC_044,
    CONS1.NIF_EMPRESA_047,
    CONS1.RAZON_SOCIAL_047,
    CONS1.CALLE_EMPRESA,
    CONS1.NUM_CALLE_047,
    CONS1.PISO_047,
    CONS1.LETRA_047,
    CONS1.CP_047,
    CONS1.LOCALIDAD_047,
    CONS1.MUNICIPIO_047,
    CONS1.PROVINCIA_047,
    CONS1.REPRESENTANTE_LEGAL_047,
    CONS1.TELF1_047,
    CONS1.TELF2_047,
    CONS1.EMAIL_047,
    CONS1.NUM_RERA_047,
    CONS1.ACTIVO_SN_047,
    CONS1.PROVINCIA_TRABAJO_010,
    CONS1.PROV_DO,
    CONS1.MUNICIP_TRABAJO_010,
    CONS1.MUN_DO,
    CONS1.CALLE_TRABAJO,
    CONS1.NUMERO_TRABAJO_010,
    CONS1.PISO_TRABAJO_010,
    CONS1.LETRA_TRABAJO_010,
    CONS1.CPOSTAL_TRABAJO_010,
    CONS1.F_INICIO_TRABAJO_010,
    CONS1.DURACION_DIAS_010,
    CONS1.ID_008_TIPO_PLAN,
    CONS1.DESC_ES_008,
    CONS1.DESC_EU_008,
    CONS1.DESCR_ES_008,
    CONS1.DESCR_EU_008,
    CONS1.EXP_SANC_010,
    CONS1.TECN_ASIGN_010,
    CONS1.EXP_ESPECIF_010,
    CONS1.REQ_LEIDO_010,
    CONS1.NOTIF_LEIDA_010,
    CONS1.EXP_CERRADO_010,
    CONS1.ESTADO_EXP_010,
    CONS1.IDIOMA_COMUNIC_010,
    CONS1.ID_001_FASE,
    CONS1.FASE_DESC,
    CONS1.FASE_DESE,
    CONS1.ID_002_TRAMITE,
    CONS1.TRAMITE_DESC,
    CONS1.TRAMITE_DESE,
    CONS1.ID_034_ESTADO,
    CONS1.DESC_ES_034,
    CONS1.DESC_EU_034,
    CONS1.OBSERVACIONES_014,
    CONS1.id_047,
    CONS1.FECHA_VISITA_010,
    CONS1.NIF_TITULAR_010,
    CONS1.NOMBRE_TITULAR_010,
    CONS1.APE1_TITULAR_010,
    CONS1.APE2_TITULAR_010,
    CONS1.OID_DOC_VISITA_010,
    CONS1.OID_DOC_SANCION_010
FROM
(SELECT T10.ID_ANO_010,
    T10.ID_010,
    T10.ID_PROV_010,
    T10.NUM_REG_010,
    T10.ID_PROV_010
    || '/'
    || T10.ID_ANO_010
    || '/'
    || lpad(T10.ID_010,5,'0') ID_EXPEDIENTE, 
    T10.FEC_REG_010,
    T47.TIPO_DOC_044,
    T47.NIF_EMPRESA_047,
    NVL(T47.NOMBRE_EMPRESA_047, T47.RAZON_SOCIAL_047) AS RAZON_SOCIAL_047,
    CASE
      WHEN NORACALLEEMP.DS_O IS NULL
      THEN T47.CALLE_047
      ELSE CONCAT(CONCAT(CONCAT(NORACALLEEMP.DS_O,' ('),NORACALLEEMP.TIPO_VIA),')')
    END AS CALLE_EMPRESA,
    T47.NUM_CALLE_047,
    T47.PISO_047,
    T47.LETRA_047,
    T47.CP_047,
    T47.LOCALIDAD_047,
    T47.MUNICIPIO_047,
    T47.PROVINCIA_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T47.NUM_RERA_047,
    T47.ACTIVO_SN_047,
    T10.PROVINCIA_TRABAJO_010,
    NORAPROV.DS_O PROV_DO,
    T10.MUNICIP_TRABAJO_010,
    NORAMUN.DS_O MUN_DO,
    CASE
      WHEN NORACALLE.DS_O IS NULL
      THEN T10.CALLE_TRABAJO_010
      ELSE CONCAT(CONCAT(CONCAT(NORACALLE.DS_O,' ('),NORACALLE.TIPO_VIA),')')
    END AS CALLE_TRABAJO,
    T10.NUMERO_TRABAJO_010,
    T10.PISO_TRABAJO_010,
    T10.LETRA_TRABAJO_010,
    T10.CPOSTAL_TRABAJO_010,
    T10.F_INICIO_TRABAJO_010,
    T10.DURACION_DIAS_010,
    T10.ID_008_TIPO_PLAN,
    T8.DESC_ES_008,
    T8.DESC_EU_008,
    CASE
      WHEN T10.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T8.DESC_ES_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T8.DESC_ES_008,1,1)
    END DESCR_ES_008,
    CASE
      WHEN T10.ID_008_TIPO_PLAN = 1
      THEN SUBSTR(T8.DESC_EU_008,1,1)
        ||
        CASE
          WHEN (T47.PROVINCIA_047 = '01'
          OR T47.PROVINCIA_047    = '20'
          OR T47.PROVINCIA_047    = '48')
          THEN 'T'
          ELSE 'F'
        END
      ELSE SUBSTR(T8.DESC_EU_008,1,1)
    END DESCR_EU_008,
    T10.EXP_SANC_010,
    T10.TECN_ASIGN_010,
    T10.EXP_ESPECIF_010,
    T10.REQ_LEIDO_010,
    T10.NOTIF_LEIDA_010,
    T10.EXP_CERRADO_010,
    T10.ESTADO_EXP_010,
    T10.IDIOMA_COMUNIC_010,
    T14.ID_001_FASE,
    FASES.DESC_ES_001 FASE_DESC,
    FASES.DESC_EU_001 FASE_DESE,
    T14.ID_002_TRAMITE,
    TRAMITES.DESC_ES_002 TRAMITE_DESC,
    TRAMITES.DESC_EU_002 TRAMITE_DESE,
    T14.ID_034_ESTADO,
    ESTADOS.DESC_ES_034,
    ESTADOS.DESC_EU_034,
    t14.OBSERVACIONES_014,
    T47.id_047,
    T10.FECHA_VISITA_010,
    T10.NIF_TITULAR_010,
    T10.NOMBRE_TITULAR_010,
    T10.APE1_TITULAR_010,
    T10.APE2_TITULAR_010,
    T10.OID_DOC_VISITA_010,
    T10.OID_DOC_SANCION_010
  FROM AB03B10T00 T10
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T10.ID_047_EMPRESA
  INNER JOIN AB03B14T00 T14
  ON T14.ID_010_NUM_EXP = T10.ID_010
  AND T14.ID_ANO_010    = T10.ID_ANO_010
  AND T14.ID_PROV_010   = T10.ID_PROV_010
  AND T14.ID_014       IN
  (SELECT MAX(id_014)
    FROM AB03B14T00
    GROUP BY ID_ANO_010,
      ID_010_NUM_EXP,
      ID_PROV_010)
  INNER JOIN AB03B08T00 T8  ON T8.ID_008 = T10.ID_008_TIPO_PLAN
  INNER JOIN AB03B01T00 FASES  ON FASES.ID_001 = T14.ID_001_FASE
  INNER JOIN AB03B02T00 TRAMITES  ON TRAMITES.ID_001_FASE = T14.ID_001_FASE
                                     AND TRAMITES.ID_002     = T14.ID_002_TRAMITE
  INNER JOIN AB03B34T00 ESTADOS  ON ESTADOS.ID_034= T14.ID_034_ESTADO
    --AND TRAMITES.ID_001_FASE||TRAMITES.ID_002<>11
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV  ON NORAPROV.ID = T10.PROVINCIA_TRABAJO_010
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN  ON NORAMUN.PROVINCIA_ID = T10.PROVINCIA_TRABAJO_010
                                      AND NORAMUN.ID          = T10.MUNICIP_TRABAJO_010
  LEFT JOIN T17I.T17_CALLE NORACALLE  ON NORACALLE.PROVINCIA_ID  = T10.PROVINCIA_TRABAJO_010
                                  AND NORACALLE.MUNICIPIO_ID = T10.MUNICIP_TRABAJO_010
                                  AND T10.CALLE_TRABAJO_010 LIKE NORACALLE.ID
  LEFT JOIN T17I.T17_LOCALIDAD NORALOCA ON NORALOCA.PROVINCIA_ID  = T47.PROVINCIA_047
                                AND NORALOCA.MUNICIPIO_ID = T47.MUNICIPIO_047
                                AND NORALOCA.ID           = T47.LOCALIDAD_047
  LEFT JOIN T17I.T17_CALLE NORACALLEEMP ON NORACALLEEMP.PROVINCIA_ID  = T47.PROVINCIA_047
                                AND NORACALLEEMP.MUNICIPIO_ID = T47.MUNICIPIO_047
                                AND T47.CALLE_047 LIKE NORACALLEEMP.ID
  WHERE T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010 IN
(select T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010
    FROM AB03B10T00 T10
    INNER JOIN AB03B14T00 T14 ON T14.ID_010_NUM_EXP = T10.ID_010
                              AND T14.ID_ANO_010    = T10.ID_ANO_010
                              AND T14.ID_PROV_010   = T10.ID_PROV_010
    INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14.ID_014
    GROUP BY T10.ID_PROV_010 || T10.ID_ANO_010 || T10.ID_010))CONS1
INNER JOIN AB03B14T00 T14BIS ON T14BIS.ID_010_NUM_EXP = CONS1.ID_010
                                AND T14BIS.ID_ANO_010    = CONS1.ID_ANO_010
                                AND T14BIS.ID_PROV_010   = CONS1.ID_PROV_010
                                AND T14BIS.ID_001_FASE = 4 AND T14BIS.ID_002_TRAMITE=2
INNER JOIN AB03B56T00 T56 ON T56.ID_014 = T14BIS.ID_014
GROUP BY CONS1.ID_ANO_010,
    CONS1.ID_010,
    CONS1.ID_PROV_010,
    T56.PROVINCIA_TRABAJO_056, 
    CONS1.NUM_REG_010,
    CONS1.ID_EXPEDIENTE, 
    CONS1.FEC_REG_010,
    CONS1.TIPO_DOC_044,
    CONS1.NIF_EMPRESA_047,
    CONS1.RAZON_SOCIAL_047,
    CONS1.CALLE_EMPRESA,
    CONS1.NUM_CALLE_047,
    CONS1.PISO_047,
    CONS1.LETRA_047,
    CONS1.CP_047,
    CONS1.LOCALIDAD_047,
    CONS1.MUNICIPIO_047,
    CONS1.PROVINCIA_047,
    CONS1.REPRESENTANTE_LEGAL_047,
    CONS1.TELF1_047,
    CONS1.TELF2_047,
    CONS1.EMAIL_047,
    CONS1.NUM_RERA_047,
    CONS1.ACTIVO_SN_047,
    CONS1.PROVINCIA_TRABAJO_010,
    CONS1.PROV_DO,
    CONS1.MUNICIP_TRABAJO_010,
    CONS1.MUN_DO,
    CONS1.CALLE_TRABAJO,
    CONS1.NUMERO_TRABAJO_010,
    CONS1.PISO_TRABAJO_010,
    CONS1.LETRA_TRABAJO_010,
    CONS1.CPOSTAL_TRABAJO_010,
    CONS1.F_INICIO_TRABAJO_010,
    CONS1.DURACION_DIAS_010,
    CONS1.ID_008_TIPO_PLAN,
    CONS1.DESC_ES_008,
    CONS1.DESC_EU_008,
    CONS1.DESCR_ES_008,
    CONS1.DESCR_EU_008,
    CONS1.EXP_SANC_010,
    CONS1.TECN_ASIGN_010,
    CONS1.EXP_ESPECIF_010,
    CONS1.REQ_LEIDO_010,
    CONS1.NOTIF_LEIDA_010,
    CONS1.EXP_CERRADO_010,
    CONS1.ESTADO_EXP_010,
    CONS1.IDIOMA_COMUNIC_010,
    CONS1.ID_001_FASE,
    CONS1.FASE_DESC,
    CONS1.FASE_DESE,
    CONS1.ID_002_TRAMITE,
    CONS1.TRAMITE_DESC,
    CONS1.TRAMITE_DESE,
    CONS1.ID_034_ESTADO,
    CONS1.DESC_ES_034,
    CONS1.DESC_EU_034,
    CONS1.OBSERVACIONES_014,
    CONS1.id_047,
    CONS1.FECHA_VISITA_010,
    CONS1.NIF_TITULAR_010,
    CONS1.NOMBRE_TITULAR_010,
    CONS1.APE1_TITULAR_010,
    CONS1.APE2_TITULAR_010,
    CONS1.OID_DOC_VISITA_010,
    CONS1.OID_DOC_SANCION_010;

    
 

