 CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B04V00" ("ID004", "NOMBRE004", "APEL1004", "APEL2004", "ID003CARGO", "DESC003ES", "DESC003EU", "ACTIVOSN004", "ID_PROV_004", "PROV_DO", "TIPO_DOC_044", "DESC_ES_044", "DESC_EU_044", "NIF_FIRMANTE_004", "BUZON_004", "USUARIO_004", "F_VIGENCIA_DS_004", "F_VIGENCIA_HS_004", "FIRMA_ESCANEADA_004") AS 
  SELECT T1.ID_004 ID004,
    T1.NOMBRE_004 NOMBRE004,
    T1.APEL1_004 APEL1004,
    T1.APEL2_004 APEL2004,
    T1.ID_003_CARGO ID003CARGO,
    T3.DESC_ES_003 DESC003ES,
    T3.DESC_EU_003 DESC003EU,
    T1.ACTIVO_SN_004 ACTIVOSN004,
    T1.ID_PROV_004 ID_PROV_004,
    CASE
      WHEN T1.ID_PROV_004 = 99 THEN ''
      ELSE NORAPROV.DS_O
    END PROV_DO,
    T1.TIPO_DOC_044 TIPO_DOC_044,
    T44.DESC_ES_044 DESC_ES_044,
    T44.DESC_EU_044 DESC_EU_044,
    T1.NIF_FIRMANTE_004,
    T1.BUZON_004,
    T1.USUARIO_004,
    T1.F_VIGENCIA_DS_004,
    T1.F_VIGENCIA_HS_004,
    T1.FIRMA_ESCANEADA_004
  FROM AB03B04T00 T1
  INNER JOIN AB03B03T00 T3 ON T3.ID_003 = T1.ID_003_CARGO
  LEFT OUTER JOIN T17I.T17_PROVINCIA NORAPROV ON NORAPROV.ID = T1.ID_PROV_004
  INNER JOIN AB03B44T00 T44 ON T44.ID_044 = T1.TIPO_DOC_044;
  
/************************************************************/  
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B07V00" ("ID007", "DESCES007", "DESCEU007", "IDCODPADRE", "ACTONOTIF007", "ID013", "ID001FASE", "ID002TRAMITE", "DESCES013", "DESCEU013", "ID005PLANTILLA", "ID008TIPOPLAN", "ID015", "ID010NUMEXP", "IDANO010", "IDPROV010", "ID014HISTOTRAM", "IDPIF015", "OID015", "OBSERVACIONESES015", "OBSERVACIONESEU015")
AS
  SELECT t07.id_007,
    t07.desc_es_007,
    t07.desc_eu_007,
    t07.id_cod_padre,
    t07.acto_notif_007,
    t13.id_013,
    t13.id_001_fase,
    t13.id_002_tramite,
    t13.desc_es_013,
    t13.desc_eu_013,
    t13.id_005_plantilla,
    t13.id_008_tipo_plan,
    t15.id_015,
    t15.id_010_num_exp,
    t15.id_ano_010,
    t15.id_prov_010,
    t15.id_014_histo_tram,
    t15.id_pif_015,
    t15.oid_015,
    t15.observaciones_es_015,
    t15.observaciones_eu_015
  FROM ab03b07t00 t07
  INNER JOIN ab03b13t00 t13
  ON T07.Id_007 = T13.Id_007_Tp_Doc_Dokusi
  INNER JOIN ab03b15t00 t15
  ON t15.id_013_tipo_doc_g= t13.id_013;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B07V01" ("ID007", "DESCES007", "DESCEU007", "IDCODPADRE", "ACTONOTIF007", "ID013", "ID001FASE", "ID002TRAMITE", "DESCES013", "DESCEU013", "ID005PLANTILLA", "ID008TIPOPLAN")
AS
  SELECT t07.id_007,
    t07.desc_es_007,
    t07.desc_eu_007,
    t07.id_cod_padre,
    t07.acto_notif_007,
    t13.id_013,
    t13.id_001_fase,
    t13.id_002_tramite,
    t13.desc_es_013,
    t13.desc_eu_013,
    t13.id_005_plantilla,
    t13.id_008_tipo_plan
  FROM ab03b07t00 T07
  INNER JOIN AB03B13T00 T13
  ON T13.ID_007_TP_DOC_DOKUSI = T07.ID_007;
/************************************************************/
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"."AB03B10V00" ("ID_ANO_010", "ID_010", "ID_PROV_010", "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", "TIPO_TRABAJO_053", "ID_054_ACTIV", "ID_041_MATERIAL", "SUBTIPO_ACTIVIDAD_055", "PREVISION_MCA_010", "ESTIMACION_MCA_010", 
    "NIF_REPRESENTANTE_010", 
    "NOMBRE_REPRESENTANTE_010") AS 
  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'),
    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,
    T10.TIPO_TRABAJO_053,
    T10.ID_054_ACTIV,
    t10.ID_041_MATERIAL,
    T10.SUBTIPO_ACTIVIDAD_055,
    T10.PREVISION_MCA_010,
    T10.ESTIMACION_MCA_010, 
    T10.NIF_REPRESENTANTE_010, 
    T10.NOMBRE_REPRESENTANTE_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;

 
/******************************************************************/
 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", "TIPO_TRABAJO_053", "ID_054_ACTIV", "ID_041_MATERIAL", "SUBTIPO_ACTIVIDAD_055", "PREVISION_MCA_010", "ESTIMACION_MCA_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,
    TIPO_TRABAJO_053,
    ID_054_ACTIV,
    ID_041_MATERIAL,
    SUBTIPO_ACTIVIDAD_055,
    PREVISION_MCA_010,
    ESTIMACION_MCA_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
  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,
    CONS1.TIPO_TRABAJO_053,
    CONS1.ID_054_ACTIV,
    CONS1.ID_041_MATERIAL,
    CONS1.SUBTIPO_ACTIVIDAD_055,
    CONS1.PREVISION_MCA_010,
    CONS1.ESTIMACION_MCA_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,
      T10.TIPO_TRABAJO_053,
      T10.ID_054_ACTIV,
      T10.ID_041_MATERIAL,
      T10.SUBTIPO_ACTIVIDAD_055,
      T10.PREVISION_MCA_010,
      T10.ESTIMACION_MCA_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
    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,
    CONS1.TIPO_TRABAJO_053,
    CONS1.ID_054_ACTIV,
    CONS1.ID_041_MATERIAL,
    CONS1.SUBTIPO_ACTIVIDAD_055,
    CONS1.PREVISION_MCA_010,
    CONS1.ESTIMACION_MCA_010; 
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B10V00DATOSRESOLUCION" ("IDANO010", "ID010", "IDPROV010", "IDEXPEDIENTE", "FECREG010", "RAZONSOCIAL047", "ID047EMPRESA", "DIRECCIONEMP", "CP047", "MUNICIPIO047", "MUNICIPIOEMPES", "PROVINCIA047", "PROVEMPES", "DIRECCIONTRAB", "CPOSTALTRABAJO010", "PROVINCIATRABAJO010", "PROVTRABDO", "MUNICIPTRABAJO010", "MUNDO", "ID008TIPOPLAN", "EXPSANC010", "RESUMENACTUACES010", "RESUMENACTUACEU010", "EXPESPECIF010", "FINICIOTRABAJO")
AS
  SELECT T10.ID_ANO_010 IDANO010,
    T10.ID_010 ID010,
    T10.ID_PROV_010 IDPROV010,
    T10.ID_PROV_010
    || '/'
    || T10.ID_ANO_010
    || '/'
    || lpad(T10.ID_010,5,'0'),
    T10.FEC_REG_010 FECREG010,
    NVL(T47.NOMBRE_EMPRESA_047,T47.RAZON_SOCIAL_047) RAZONSOCIAL010,
    T10.ID_047_EMPRESA ID047EMPRESA,
    CASE
      WHEN NORACALLEEMP.DS_O IS NULL
      THEN T47.CALLE_047
        || ', '
        || T47.NUM_CALLE_047
        || DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
      ELSE CONCAT(CONCAT(CONCAT(NORACALLEEMP.DS_O,' ('),NORACALLEEMP.TIPO_VIA),')')
        || ', '
        || T47.NUM_CALLE_047
        || DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
    END AS DIRECCIONEMP,
    T47.CP_047 CP047,
    T47.MUNICIPIO_047 MUNICIPIO047,
    NORAMUNEMP.DS_O MUNICIPIOEMPES,
    T47.PROVINCIA_047 PROVINCIA010,
    NORAPROVEMP.DS_O PROVEMPES,
    CASE
      WHEN NORACALLE.DS_O IS NULL
      THEN T10.CALLE_TRABAJO_010
        || ', '
        || T10.NUMERO_TRABAJO_010
        || DECODE(T10.PISO_TRABAJO_010, NULL, '', ' - '
        || T10.PISO_TRABAJO_010)
        || ''
        || T10.LETRA_TRABAJO_010
      ELSE CONCAT(CONCAT(CONCAT(NORACALLE.DS_O,' ('),NORACALLE.TIPO_VIA),')')
        || ', '
        || T10.NUMERO_TRABAJO_010
        || DECODE(T10.PISO_TRABAJO_010, NULL, '', ' - '
        || T10.PISO_TRABAJO_010)
        || ''
        || T10.LETRA_TRABAJO_010
    END AS DIRECCIONTRAB,
    T10.CPOSTAL_TRABAJO_010 CPOSTALTRABAJO010,
    NVL(T10.PROVINCIA_TRABAJO_010,T10.ID_PROV_010) PROVINCIATRABAJO010,
    NORAPROV.DS_O PROVTRABDO,
    T10.MUNICIP_TRABAJO_010 MUNICIPTRABAJO010,
    NORAMUN.DS_O MUNDO,
    T10.ID_008_TIPO_PLAN ID008TIPOPLAN,
    T10.EXP_SANC_010 EXPSANC010,
    T10.RESUMEN_ACTUAC_ES_010 RESUMENACTUACES010,
    T10.RESUMEN_ACTUAC_EU_010 RESUMENACTUACEU010,
    T10.EXP_ESPECIF_010 EXPESPECIF010,
    T10.F_INICIO_TRABAJO_010 FINICIOTRABAJO
  FROM AB03B10T00 T10
  INNER JOIN AB03B47T00 T47
  ON T47.ID_047=t10.ID_047_EMPRESA
  INNER JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID = NVL(T10.PROVINCIA_TRABAJO_010,T10.ID_PROV_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_CALLE NORACALLEEMP
  ON NORACALLEEMP.PROVINCIA_ID  = T47.PROVINCIA_047
  AND NORACALLEEMP.MUNICIPIO_ID = T47.MUNICIPIO_047
  AND T47.CALLE_047 LIKE NORACALLEEMP.ID
  LEFT JOIN T17I.T17_PROVINCIA NORAPROVEMP
  ON NORAPROVEMP.ID = T47.PROVINCIA_047
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUNEMP
  ON NORAMUNEMP.PROVINCIA_ID = T47.PROVINCIA_047
  AND NORAMUNEMP.ID          = T47.MUNICIPIO_047;
  
/************************************************************/
 CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B10V00FICHA" ("ID_ANO_010", "ID_010", "ID_PROV_010", "ID_EXPEDIENTE", "NUM_REG_010", "FEC_REG_010", "NIF_TITULAR_010", "NOMBRE_TITULAR_010", "APE1_TITULAR_010", "APE2_TITULAR_010", "ID_047", "TIPO_DOC_044", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "DIREC_EMP", "CP_047", "LOC_DO_EMP", "MUN_DO_EMP", "PROV_DO_EMP", "REPRESENTANTE_LEGAL_047", "NUM_RERA_047", "DIREC_TRAB", "CPOSTAL_TRABAJO_010", "PROV_DO_TRAB", "MUN_DO_TRAB", "F_INICIO_TRABAJO_010", "DURACION_DIAS_010", "ID_008_TIPO_PLAN", "PLAN_ES", "PLAN_EU", "EXP_SANC_010", "TECN_ASIGN_010", "IDIOMA_COMUNIC_010", "FECHA_VISITA_010")
AS
  SELECT C1.ID_ANO_010,
    C1.ID_010,
    C1.ID_PROV_010,
    C1.IDEXPEDIENTE,
    C1.NUM_REG_010,
    C1.FEC_REG_010,
    C1.NIF_TITULAR_010,
    C1.NOMBRE_TITULAR_010,
    C1.APE1_TITULAR_010,
    C1.APE2_TITULAR_010,
    C1.ID_047,
    C1.TIPO_DOC_044,
    C1.NIF_EMPRESA_047,
    C1.RAZON_SOCIAL_047,
    C1.CALLE_EMPRESA
    || ' Nš'
    || C1.NUM_CALLE_047
    || ' '
    || C1.PISO_047
    || ''
    || C1.LETRA_047 AS DIREC_EMP,
    C1.CP_047,
    C1.LOC_DO_EMP,
    C1.MUN_DO_EMP,
    C1.PROV_DO_EMP,
    C1.REPRESENTANTE_LEGAL_047,
    C1.NUM_RERA_047,
    C1.CALLE_TRABAJO
    || ' Nš'
    || C1.NUMERO_TRABAJO_010
    || ' '
    || C1.PISO_TRABAJO_010
    || ''
    || C1.LETRA_TRABAJO_010 AS DIREC_TRAB,
    C1.CPOSTAL_TRABAJO_010,
    C1.PROV_DO_TRAB,
    C1.MUN_DO_TRAB,
    C1.F_INICIO_TRABAJO_010,
    C1.DURACION_DIAS_010,
    C1.ID_008_TIPO_PLAN,
    C1.DESC_ES_008,
    C1.DESC_EU_008,
    C1.EXP_SANC_010,
    C1.TECN_ASIGN_010,
    CASE
      WHEN C1.IDIOMA_COMUNIC_010 = 0
      THEN 'Gastelaniaz/Castellano'
      ELSE 'Euskaraz/Euskera'
    END AS IDIOMA_COMUNICACION,
    C1.FECHA_VISITA_010
  FROM
    (SELECT T10.ID_ANO_010,
      T10.ID_010,
      T10.ID_PROV_010,
      T10.ID_PROV_010
      || '/'
      || T10.ID_ANO_010
      || '/'
      || lpad(T10.ID_010,5,'0') AS IDEXPEDIENTE,
      T10.NUM_REG_010,
      T10.FEC_REG_010,
      T10.NIF_TITULAR_010,
      T10.NOMBRE_TITULAR_010,
      T10.APE1_TITULAR_010,
      T10.APE2_TITULAR_010,
      T47.ID_047,
      T47.TIPO_DOC_044,
      T47.NIF_EMPRESA_047,
      NVL(T47.NOMBRE_EMPRESA_047, T47.NIF_EMPRESA_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,
      NORALOCA.DS_O LOC_DO_EMP,
      T47.LOCALIDAD_047,
      T47.MUNICIPIO_047,
      NORAMUNEMP.DS_O MUN_DO_EMP,
      NORAPROVEMP.DS_O PROV_DO_EMP,
      T47.PROVINCIA_047,
      T47.REPRESENTANTE_LEGAL_047,
      t47.NUM_RERA_047,
      T10.PROVINCIA_TRABAJO_010,
      NORAPROV.DS_O PROV_DO_TRAB,
      T10.MUNICIP_TRABAJO_010,
      NORAMUN.DS_O MUN_DO_TRAB,
      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,
      T10.EXP_SANC_010,
      T10.TECN_ASIGN_010,
      T10.IDIOMA_COMUNIC_010,
      T10.FECHA_VISITA_010
    FROM AB03B10T00 T10
    LEFT JOIN AB03B47T00 T47
    ON T47.ID_047 = T10.ID_047_EMPRESA
    INNER JOIN AB03B08T00 T8
    ON T8.ID_008 = T10.ID_008_TIPO_PLAN
    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_CALLE NORACALLEEMP
    ON NORACALLEEMP.PROVINCIA_ID  = T47.PROVINCIA_047
    AND NORACALLEEMP.MUNICIPIO_ID = T47.MUNICIPIO_047
    AND T47.CALLE_047 LIKE NORACALLEEMP.ID
    LEFT JOIN T17I.T17_PROVINCIA NORAPROVEMP
    ON NORAPROVEMP.ID = T47.PROVINCIA_047
    LEFT JOIN T17I.T17_MUNICIPIO NORAMUNEMP
    ON NORAMUNEMP.PROVINCIA_ID = T47.PROVINCIA_047
    AND NORAMUNEMP.ID          = T47.MUNICIPIO_047
    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
    ) C1;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B10V00TRAMITE" ("ID_ANO_010", "ID_010", "ID_PROV_010", "ID_014", "NUM_REG_010", "ID_EXPEDIENTE", "FEC_REG_010", "TIPO_DOC_044", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "REPRESENTANTE_LEGAL_047", "TELF1_047", "TELF2_047", "EMAIL_047", "ID_008_TIPO_PLAN", "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", "ID_047", "OBSERVACIONES_014", "NIF_TITULAR_010", "NOMBRE_TITULAR_010", "APE1_TITULAR_010", "APE2_TITULAR_010")
AS
  SELECT T10.ID_ANO_010,
    T10.ID_010,
    T10.ID_PROV_010,
    T14.ID_014,
    T10.NUM_REG_010,
    T10.ID_PROV_010
    || '/'
    || T10.ID_ANO_010
    || '/'
    || lpad(T10.ID_010,5,'0'),
    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,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T10.ID_008_TIPO_PLAN,
    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,
    T47.id_047,
    t14.OBSERVACIONES_014,
    T10.NIF_TITULAR_010,
    T10.NOMBRE_TITULAR_010,
    T10.APE1_TITULAR_010,
    T10.APE2_TITULAR_010
  FROM AB03B14T00 T14
  INNER JOIN AB03B10T00 T10
  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 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
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T10.ID_047_EMPRESA;

/************************************************************/
CREATE OR REPLACE VIEW AB03B11V00 (ID011,ID001FASE,ID002TRAMITE,ID008TIPOPLAN,DESCES011,DESCEU011,ID007TPDOCDOKUSI,ID007STDOCDOKUSI,
ID007,DESCES007,DESCEU007,IDCODPADRE,ACTONOTIF007) AS
select ID_011,ID_001_FASE,ID_002_TRAMITE,ID_008_TIPO_PLAN,DESC_ES_011,DESC_EU_011,ID_007_TP_DOC_DOKUSI,ID_007_ST_DOC_DOKUSI,
ID_007,DESC_ES_007,DESC_EU_007,ID_COD_PADRE,ACTO_NOTIF_007
from ab03b11t00 t11
inner join ab03b07t00 t07 on t07.ID_007 = t11.ID_007_TP_DOC_DOKUSI
order by 4,2,3;

/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B12V00" ("ID_012", "OID_012", "DESC_ES_012", "DESC_EU_012", "ID_009_CODJMS", "META_INF_012", "ORG_DOC_ENTREGADO_015", "NOM_DOC_ENTREGADO_015", "FEC_DOC_ENTREGADO_015", "TIPO_APORTACION_015", "ID_011", "ID_001_FASE", "ID_002_TRAMITE", "ID_008_TIPO_PLAN", "DESC_ES_011", "DESC_EU_011", "ID_007_TP_DOC_DOKUSI", "ID_007_ST_DOC_DOKUSI")
AS
  SELECT T12.ID_012,
    t12.OID_012 ,
    t12.DESC_ES_012,
    t12.DESC_EU_012,
    t12.ID_009_CODJMS,
    t12.META_INF_012,
    T12.ORG_DOC_ENTREGADO_015,
    T12.NOM_DOC_ENTREGADO_015,
    T12.FEC_DOC_ENTREGADO_015,
    T12.TIPO_APORTACION_015,
    t11.ID_011,
    t11.ID_001_FASE,
    t11.ID_002_TRAMITE,
    t11.ID_008_TIPO_PLAN,
    t11.DESC_ES_011,
    t11.DESC_EU_011,
    t11.ID_007_TP_DOC_DOKUSI,
    t11.ID_007_ST_DOC_DOKUSI
  FROM ab03b12t00 t12
  INNER JOIN ab03b11t00 t11
  ON t11.ID_011 = t12.ID_011_DOC_SOL;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B14V00" ("ID014", "FACTU014", "USUA014", "FREGSAL014", "NREGSAL014", "FNOTIF014", "FEXPIR014", "FTRAMITACION014", "ID010NUMEXP", "IDANO010", "ID001FASE", "ID002TRAMITE", "IDPROV010", "TRAMITEES", "TRAMITEEU", "TIPOTRAMITE", "FASEES", "FASEEU", "IDESTADO", "ESTADOES", "ESTADOEU", "OBSERVACIONES014")
AS
  SELECT t1.ID_014 ID014,
    t1.F_ACTU_014 FACTU014,
    t1.USUA_014 USUA014,
    t1.F_REG_SAL_014 FREGSAL014,
    t1.N_REG_SAL_014 NREGSAL014,
    t1.F_NOTIF_014 FNOTIF014,
    t1.F_EXPIR_014 FEXPIR014,
    t1.F_TRAMITACION_014 FTRAMITACION014,
    t1.ID_010_NUM_EXP ID010NUMEXP,
    t1.ID_ANO_010 IDANO010,
    t1.ID_001_FASE ID001FASE,
    t1.ID_002_TRAMITE ID002TRAMITE,
    t1.ID_PROV_010 IDPROV010,
    TRAMITES.DESC_ES_002 TRAMITEES,
    TRAMITES.DESC_EU_002 TRAMITEEU,
    TRAMITES.ENT_SAL_002 TIPOTRAMITE,
    FASES.DESC_ES_001 FASEES,
    FASES.DESC_EU_001 FASEEU,
    ESTADOS.ID_034 IDESTADO,
    ESTADOS.DESC_ES_034 ESTADOES,
    ESTADOS.DESC_EU_034 ESTADOEU,
    t1.OBSERVACIONES_014
  FROM AB03B14T00 t1
  INNER JOIN AB03B01T00 FASES
  ON FASES.ID_001 = t1.ID_001_FASE
  INNER JOIN AB03B02T00 TRAMITES
  ON TRAMITES.ID_001_FASE = t1.ID_001_FASE
  AND TRAMITES.ID_002     = t1.ID_002_TRAMITE
  INNER JOIN AB03B34T00 ESTADOS
  ON ESTADOS.ID_034 = t1.ID_034_ESTADO
  ORDER BY t1.ID_014 DESC;
/************************************************************/
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B15V00" ("ID015", "IDPIF015", "ID010NUMEXP", "IDANO010", "IDPROV010", "ID013TIPODOCG", "DESCDOCEXP013", "DESEDOCEXP013", "ID014HISTOTRAM", "OID015", "OBSERVACIONESES015", "OBSERVACIONESEU015", "CONDICESPECIF015", "TIPOAPORTACION015", "ORGDOCENTREGADO015", "NOMDOCENTREGADO015", "FECDOCENTREGADO015", "ID001FASE", "ID002TRAMITE", "ID004FIRMANTE", "MOTIVORECHAZO015") AS 
  SELECT t15.ID_015 ID015,
    t15.ID_PIF_015 IDPIF015,
    t15.ID_010_NUM_EXP ID010NUMEXP,
    t15.ID_ANO_010 IDANO010,
    t15.ID_PROV_010 IDPROV010,
    t15.ID_013_TIPO_DOC_G ID013TIPODOCG,
    T13.Desc_Es_013 DESCDOCEXP013,
    T13.Desc_Eu_013 DESEDOCEXP013,
    t15.ID_014_HISTO_TRAM ID014HISTOTRAM,
    t15.OID_015 OID015,
    t15.OBSERVACIONES_ES_015 OBSERVACIONESES015,
    t15.OBSERVACIONES_EU_015 OBSERVACIONESEU015,
    t15.CONDIC_ESPECIF_015 CONDICESPECIF015,
    t15.TIPO_APORTACION_015 TIPOAPORTACION015,
    t15.ORG_DOC_ENTREGADO_015 ORGDOCENTREGADO015,
    t15.NOM_DOC_ENTREGADO_015 NOMDOCENTREGADO015,
    t15.FEC_DOC_ENTREGADO_015 FECDOCENTREGADO015,
    t13.ID_001_FASE ID001FASE,
    t13.ID_002_TRAMITE ID002TRAMITE,
    t15.ID_004_FIRMANTE ID004FIRMANTE,
    T15.MOTIVO_RECHAZO_015 MOTIVORECHAZO015
  FROM AB03B15T00 t15
  INNER JOIN AB03B13T00 t13
  ON T15.Id_013_Tipo_Doc_G = t13.id_013
  UNION
  SELECT t15.ID_015 ID015,
    t15.ID_PIF_015 IDPIF015,
    t15.ID_010_NUM_EXP ID010NUMEXP,
    t15.ID_ANO_010 IDANO010,
    t15.ID_PROV_010 IDPROV010,
    t15.ID_013_TIPO_DOC_G ID013TIPODOCG,
    T11.Desc_Es_011 DESCDOCEXP013,
    T11.Desc_Eu_011 DESEDOCEXP013,
    t15.ID_014_HISTO_TRAM ID014HISTOTRAM,
    t15.OID_015 OID015,
    t15.OBSERVACIONES_ES_015 OBSERVACIONESES015,
    t15.OBSERVACIONES_EU_015 OBSERVACIONESEU015,
    t15.CONDIC_ESPECIF_015 CONDICESPECIF015,
    t15.TIPO_APORTACION_015 TIPOAPORTACION015,
    t15.ORG_DOC_ENTREGADO_015 ORGDOCENTREGADO015,
    t15.NOM_DOC_ENTREGADO_015 NOMDOCENTREGADO015,
    t15.FEC_DOC_ENTREGADO_015 FECDOCENTREGADO015,
    t11.ID_001_FASE ID001FASE,
    t11.ID_002_TRAMITE ID002TRAMITE,
    t15.ID_004_FIRMANTE ID004FIRMANTE,
    T15.MOTIVO_RECHAZO_015 MOTIVORECHAZO015
  FROM AB03B15T00 t15
  INNER JOIN AB03B11T00 t11
  ON T15.ID_011_DOCENTRADA = t11.id_011;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B15V00ULTDOC" ("ID015", "IDPIF015", "ID010NUMEXP", "IDANO010", "IDPROV010", "ID013TIPODOCG", "DESCDOCEXP013", "DESEDOCEXP013", "ID014HISTOTRAM", "OID015", "OBSERVACIONESES015", "OBSERVACIONESEU015", "CONDICESPECIF015", "TIPOAPORTACION015", "ORGDOCENTREGADO015", "NOMDOCENTREGADO015", "FECDOCENTREGADO015", "ID004FIRMANTE", "ID001FASE", "ID002TRAMITE", "ID007TPDOCDOKUSI")
AS
  SELECT t15.ID_015 ID015,
    t15.ID_PIF_015 IDPIF015,
    t15.ID_010_NUM_EXP ID010NUMEXP,
    t15.ID_ANO_010 IDANO010,
    t15.ID_PROV_010 IDPROV010,
    t15.ID_013_TIPO_DOC_G ID013TIPODOCG,
    T11.Desc_Es_011 DESCDOCEXP013,
    T11.Desc_Eu_011 DESEDOCEXP013,
    t15.ID_014_HISTO_TRAM ID014HISTOTRAM,
    t15.OID_015 OID015,
    t15.OBSERVACIONES_ES_015 OBSERVACIONESES015,
    t15.OBSERVACIONES_EU_015 OBSERVACIONESEU015,
    t15.CONDIC_ESPECIF_015 CONDICESPECIF015,
    t15.TIPO_APORTACION_015 TIPOAPORTACION015,
    t15.ORG_DOC_ENTREGADO_015 ORGDOCENTREGADO015,
    t15.NOM_DOC_ENTREGADO_015 NOMDOCENTREGADO015,
    t15.FEC_DOC_ENTREGADO_015 FECDOCENTREGADO015,
    T15.ID_004_FIRMANTE ID004FIRMANTE,
    t11.ID_001_FASE ID001FASE,
    t11.ID_002_TRAMITE ID002TRAMITE,
    t11.ID_007_TP_DOC_DOKUSI ID007TPDOCDOKUSI
  FROM AB03B15T00 T15
  INNER JOIN AB03B11T00 t11
  ON T15.ID_011_DOCENTRADA     = t11.id_011
  WHERE t15.ID_014_HISTO_TRAM IN
    (SELECT MAX(T14.id_014)
    FROM AB03B14T00 T14
    INNER JOIN AB03B15T00 T15B
    ON T14.Id_014 = T15B.Id_014_Histo_Tram
    INNER JOIN AB03B11T00 t11
    ON T15B.ID_011_DOCENTRADA     = t11.id_011
    WHERE t11.ID_007_TP_DOC_DOKUSI=2
    AND T15B.TIPO_APORTACION_015  ='A'
    GROUP BY T14.ID_ANO_010,
      T14.ID_010_NUM_EXP,
      T14.ID_PROV_010
    );
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B15V01" ("ID14", "OID", "NOMDOCUMENTO", "TIPODOCDOKUSI", "DESCDOCES", "DESCDOCEU", "OBSERVES", "OBSERVEU")
AS
  SELECT t15.ID_014_HISTO_TRAM,
    t15.OID_015,
    T07.DESC_EU_007,
    T07.DESC_ES_007,
    T13.DESC_ES_013,
    T13.DESC_EU_013,
    t15.OBSERVACIONES_ES_015,
    t15.OBSERVACIONES_EU_015
  FROM AB03B15T00 T15
  INNER JOIN AB03B13T00 T13
  ON T13.ID_013 = T15.ID_013_TIPO_DOC_G
  INNER JOIN AB03B07T00 T07
  ON T07.ID_007 = T13.ID_007_TP_DOC_DOKUSI;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B23V00" ("ID_023", "ID_022_TIPO_PLAN", "DESCPLAN_ES_022", "DESCPLAN_EU_022", "NUMANO_023", "FLECTURA_023", "JMSID_023", "NUMREG_023", "FREGISTRO_023", "MENSAJE_023", "ID_017_FASE", "DESC_ES_017", "DESC_EU_017", "ID_018_TRAMITE", "DESC_ES_018", "DESC_EU_018", "REMITENTE_NOM_023", "SITUACION_023", "REMITENTE_ID_023", "EXP_RELAC_023", "ID_024_EXP", "ID_PROV_024", "ID_ANO_024", "ID_028_TRAM", "ID_047", "NUM_RERA_047")
AS
  SELECT T23.ID_023,
    T23.ID_022_TIPO_PLAN,
    T22.DESC_ES_022,
    T22.DESC_EU_022,
    T23.NUMANO_023,
    T23.FLECTURA_023,
    T23.JMSID_023,
    T23.NUMREG_023,
    T23.FREGISTRO_023,
    T23.MENSAJE_023,
    T23.ID_017_FASE,
    T17.DESC_ES_017,
    T17.DESC_EU_017,
    T23.ID_018_TRAMITE,
    T18.DESC_ES_018,
    T18.DESC_EU_018,
    T23.REMITENTE_NOM_023,
    T23.SITUACION_023,
    T23.REMITENTE_ID_023,
    T23.ID_PROV_024
    ||'/'
    ||T23.ID_ANO_024
    ||'/'
    || lpad(T23.ID_024_EXP, 5, '0') ,
    T23.ID_024_EXP,
    T23.ID_PROV_024,
    T23.ID_ANO_024,
    T23.ID_028_TRAM,
    t47.ID_047,
    t47.NUM_RERA_047
  FROM AB03B23T00 T23
  INNER JOIN AB03B17T00 T17
  ON T17.ID_017 = T23.ID_017_FASE
  INNER JOIN AB03B18T00 T18
  ON T18.ID_017_FASE = T23.ID_017_FASE
  AND T18.ID_018     = T23.ID_018_TRAMITE
  INNER JOIN AB03B22T00 T22
  ON T22.ID_022 = T23.ID_022_TIPO_PLAN
  INNER JOIN AB03B24T00 T24
  ON T24.ID_024       = T23.ID_024_EXP
  AND T24.ID_PROV_024 = T23.ID_PROV_024
  AND T24.ID_ANO_024  = T23.ID_ANO_024
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047           = t24.ID_047_EMPRESA
  WHERE T23.SITUACION_023 = 'V';
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B24V00FICHA" ("ID_ANO_024", "ID_024", "ID_PROV_024", "ID_EXPEDIENTE", "NUM_REG_024", "FEC_REG_024", "ID_047", "TIPO_DOC_044", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "DIREC_EMP", "CP_047", "LOC_DO_EMP", "MUN_DO_EMP", "PROV_DO_EMP", "REPRESENTANTE_LEGAL_047", "NSS_047", "REF_CTRAB_047", "NUM_RERA_047", "ID_022_TIPO_PLAN", "PLAN_ES", "PLAN_EU", "IDIOMA_COMUNIC_024")
AS
  SELECT C1.ID_ANO_024,
    C1.ID_024,
    C1.ID_PROV_024,
    C1.IDEXPEDIENTE,
    C1.NUM_REG_024,
    C1.FEC_REG_024,
    C1.ID_047,
    C1.TIPO_DOC_044,
    C1.NIF_EMPRESA_047,
    C1.RAZON_SOCIAL_047,
    C1.CALLE_EMPRESA
    || ' Nš'
    || C1.NUM_CALLE_047
    || ' '
    || C1.PISO_047
    || ''
    || C1.LETRA_047 AS DIREC_EMP,
    C1.CP_047,
    C1.LOC_DO_EMP,
    C1.MUN_DO_EMP,
    C1.PROV_DO_EMP,
    C1.REPRESENTANTE_LEGAL_047,
    C1.nss_047,
    C1.ref_ctrab_047,
    C1.num_rera_047,
    C1.ID_022_TIPO_PLAN,
    C1.DESC_ES_022,
    C1.DESC_EU_022,
    CASE
      WHEN C1.IDIOMA_COMUNIC_024 = 0
      THEN 'Gastelaniaz/Castellano'
      ELSE 'Euskaraz/Euskera'
    END AS IDIOMA_COMUNICACION
  FROM
    (SELECT T24.ID_ANO_024,
      T24.ID_024,
      T24.ID_PROV_024,
      T24.ID_PROV_024
      || '/'
      || T24.ID_ANO_024
      || '/'
      || lpad(T24.ID_024,5,'0') AS IDEXPEDIENTE,
      T24.NUM_REG_024,
      T24.FEC_REG_024,
      T47.ID_047,
      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,
      NORALOCA.DS_O LOC_DO_EMP,
      T47.LOCALIDAD_047,
      T47.MUNICIPIO_047,
      NORAMUNEMP.DS_O MUN_DO_EMP,
      NORAPROVEMP.DS_O PROV_DO_EMP,
      T47.PROVINCIA_047,
      T47.REPRESENTANTE_LEGAL_047,
      t47.nss_047,
      t47.ref_ctrab_047,
      t47.num_rera_047,
      T24.ID_022_TIPO_PLAN,
      T22.DESC_ES_022,
      T22.DESC_EU_022,
      T24.IDIOMA_COMUNIC_024
    FROM AB03B24T00 T24
    LEFT JOIN AB03B47T00 T47
    ON T47.ID_047 = T24.ID_047_EMPRESA
    INNER JOIN AB03B22T00 T22
    ON T22.ID_022 = T24.ID_022_TIPO_PLAN
    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
    LEFT JOIN T17I.T17_PROVINCIA NORAPROVEMP
    ON NORAPROVEMP.ID = T47.PROVINCIA_047
    LEFT JOIN T17I.T17_MUNICIPIO NORAMUNEMP
    ON NORAMUNEMP.PROVINCIA_ID = T47.PROVINCIA_047
    AND NORAMUNEMP.ID          = T47.MUNICIPIO_047
    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
    ) C1;
  
/************************************************************/
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B24V00DATOSRESOLUCION" ("ID_024", "ID_PROV_024", "ID_ANO_024", "NUM_REG_024", "ID_EXPEDIENTE", "FEC_REG_024", "TIPO_DOC_024", "NIF_SOLICITANTE_024", "NOMBRE_SOLICIT_024", "APE1_SOLICIT_024", "APE2_SOLICIT_024", "CARGO_EMPRESA_024", "IDIOMA_COMUNIC_024", "INSCRIPCION_FAVORABLE_024", "EXP_CERRADO_024", "NOTIF_LEIDA_024", "REQ_LEIDO_024", "CAMBIO_ESTATUTOS_024", "ESTADO_EMPRESA_024", "F_CAMBIO_ESTADO_024", "ID_022_TIPO_PLAN", "ID_047_EMPRESA", "PLAN_ES", "PLAN_EU", "ID_047", "TIPO_DOC_047", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "CALLE_047", "NUM_CALLE_047", "PISO_047", "LETRA_047", "CP_047", "DIRECCION_EMP", "MUNDO", "MUNICIPIO_047", "PROVINCIA_DESC", "PROVINCIA_047", "REPRESENTANTE_LEGAL_047", "TELF1_047", "TELF2_047", "EMAIL_047", "FAX_047", "NSS_047", "CNAE_047", "NOMBRE_EMPRESA_047", "NUM_RERA_047", "ID_017_FASE", "FASE_ES", "FASE_EU", "ID_018_TRAMITE", "TRAMITE_ES", "TRAMITE_EU") AS 
  SELECT T24.ID_024,
    T24.ID_PROV_024,
    T24.ID_ANO_024,
    T24.NUM_REG_024,
    T24.ID_PROV_024
    || '/'
    || T24.ID_ANO_024
    || '/'
    || lpad(T24.ID_024, 5, '0') ,
    T24.FEC_REG_024,
    T24.TIPO_DOC_044,
    T24.NIF_SOLICITANTE_024,
    T24.NOMBRE_SOLICIT_024,
    T24.APE1_SOLICIT_024,
    T24.APE2_SOLICIT_024,
    T24.CARGO_EMPRESA_024,
    T24.IDIOMA_COMUNIC_024,
    T24.INSCRIPCION_FAVORABLE_024,
    T24.EXP_CERRADO_024,
    T24.NOTIF_LEIDA_024,
    T24.REQ_LEIDO_024,
    T24.CAMBIO_ESTATUTOS_024,
    T24.ESTADO_EMPRESA_024,
    T24.F_CAMBIO_ESTADO_024,
    T24.ID_022_TIPO_PLAN,
    T24.ID_047_EMPRESA,
    T22.DESC_ES_022,
    T22.DESC_EU_022,
    T47.id_047,
    T47.TIPO_DOC_044,
    T47.NIF_EMPRESA_047,
    T47.RAZON_SOCIAL_047,
    T47.CALLE_047,
    T47.NUM_CALLE_047,
    T47.PISO_047,
    T47.LETRA_047,
    T47.CP_047,
    CASE
      WHEN NORACALLEEMP.DS_O IS NULL
      THEN T47.CALLE_047
        || ', '
        || T47.NUM_CALLE_047
        ||  DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
      ELSE CONCAT(CONCAT(CONCAT(NORACALLEEMP.DS_O,' ('),NORACALLEEMP.TIPO_VIA),')')
        || ', '
        || T47.NUM_CALLE_047
        || DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
    END AS DIRECCIONEMP,
    NORAMUN.DS_O MUN_DO,
    T47.MUNICIPIO_047,
    NORAPROV.DS_O PROV_DO,
    T47.PROVINCIA_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T47.FAX_047,
    T47.NSS_047,
    T47.CNAE_047,
    T47.NOMBRE_EMPRESA_047,
    T47.NUM_RERA_047,
    T28.ID_017_FASE,
    FASES.DESC_ES_017 FASE_DESC,
    FASES.DESC_EU_017 FASE_DESE,
    T28.ID_018_TRAMITE,
    TRAMITES.DESC_ES_018 TRAMITE_DESC,
    TRAMITES.DESC_EU_018 TRAMITE_DESE
  FROM AB03B24T00 T24
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T24.ID_047_EMPRESA
  INNER JOIN AB03B28T00 T28
  ON T28.ID_024_NUM_EXP = T24.ID_024
  AND T28.ID_PROV_024   = T24.ID_PROV_024
  AND T28.ID_ANO_024    = T24.ID_ANO_024
  AND T28.ID_028       IN
    (SELECT MAX(id_028)
    FROM AB03B28T00
    GROUP BY ID_024_NUM_EXP,
      ID_PROV_024,
      ID_ANO_024
    )
  INNER JOIN AB03B22T00 T22
  ON T22.ID_022 = T24.ID_022_TIPO_PLAN
  INNER JOIN AB03B17T00 FASES
  ON FASES.ID_017 = T28.ID_017_FASE
  INNER JOIN AB03B18T00 TRAMITES
  ON TRAMITES.ID_017_FASE = T28.ID_017_FASE
  AND TRAMITES.ID_018     = T28.ID_018_TRAMITE
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID =T47.PROVINCIA_047
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN
  ON NORAMUN.PROVINCIA_ID = T47.PROVINCIA_047
  AND NORAMUN.ID          = T47.MUNICIPIO_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;
 
/************************************************************/
CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B24V00FICHA" ("ID_ANO_024", "ID_024", "ID_PROV_024", "ID_EXPEDIENTE", "NUM_REG_024", "FEC_REG_024", "ID_047", "TIPO_DOC_044", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "DIREC_EMP", "CP_047", "LOC_DO_EMP", "MUN_DO_EMP", "PROV_DO_EMP", "REPRESENTANTE_LEGAL_047", "NSS_047", "REF_CTRAB_047", "NUM_RERA_047", "ID_022_TIPO_PLAN", "PLAN_ES", "PLAN_EU", "IDIOMA_COMUNIC_024") AS 
  SELECT C1.ID_ANO_024,
    C1.ID_024,
    C1.ID_PROV_024,
    C1.IDEXPEDIENTE,
    C1.NUM_REG_024,
    C1.FEC_REG_024,
    C1.ID_047,
    C1.TIPO_DOC_044,
    C1.NIF_EMPRESA_047,
    C1.RAZON_SOCIAL_047,
    C1.CALLE_EMPRESA
    || ' Nš'
    || C1.NUM_CALLE_047
    || ' '
    || C1.PISO_047
    || ''
    || C1.LETRA_047 AS DIREC_EMP,
    C1.CP_047,
    C1.LOC_DO_EMP,
    C1.MUN_DO_EMP,
    C1.PROV_DO_EMP,
    C1.REPRESENTANTE_LEGAL_047,
    C1.nss_047,
    C1.ref_ctrab_047,
    C1.num_rera_047,
    C1.ID_022_TIPO_PLAN,
    C1.DESC_ES_022,
    C1.DESC_EU_022,
    CASE
      WHEN C1.IDIOMA_COMUNIC_024 = 0
      THEN 'Gastelaniaz/Castellano'
      ELSE 'Euskaraz/Euskera'
    END AS IDIOMA_COMUNICACION
  FROM
    (SELECT T24.ID_ANO_024,
      T24.ID_024,
      T24.ID_PROV_024,
      T24.ID_PROV_024
      || '/'
      || T24.ID_ANO_024
      || '/'
      || lpad(T24.ID_024,5,'0') AS IDEXPEDIENTE,
      T24.NUM_REG_024,
      T24.FEC_REG_024,
      T47.ID_047,
      T47.TIPO_DOC_044,
      T47.NIF_EMPRESA_047,
      T47.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,
      NORALOCA.DS_O LOC_DO_EMP,
      T47.LOCALIDAD_047,
      T47.MUNICIPIO_047,
      NORAMUNEMP.DS_O MUN_DO_EMP,
      NORAPROVEMP.DS_O PROV_DO_EMP,
      T47.PROVINCIA_047,
      T47.REPRESENTANTE_LEGAL_047,
      t47.nss_047,
      t47.ref_ctrab_047,
      t47.num_rera_047,
      T24.ID_022_TIPO_PLAN,
      T22.DESC_ES_022,
      T22.DESC_EU_022,
      T24.IDIOMA_COMUNIC_024
    FROM AB03B24T00 T24
    LEFT JOIN AB03B47T00 T47
    ON T47.ID_047 = T24.ID_047_EMPRESA
    INNER JOIN AB03B22T00 T22
    ON T22.ID_022 = T24.ID_022_TIPO_PLAN
    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
    LEFT JOIN T17I.T17_PROVINCIA NORAPROVEMP
    ON NORAPROVEMP.ID = T47.PROVINCIA_047
    LEFT JOIN T17I.T17_MUNICIPIO NORAMUNEMP
    ON NORAMUNEMP.PROVINCIA_ID = T47.PROVINCIA_047
    AND NORAMUNEMP.ID          = T47.MUNICIPIO_047
     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) C1;

/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B24V00TRAMITE" ("IDANO024", "ID024", "IDPROV024", "ID028", "NUMREG024", "ID_EXPEDIENTE", "FECREG024", "NIFSOLICITANTE024", "INSCRIPCIONFAVORABLE024", "TIPODOC044", "NIFEMPRESA047", "RAZONSOCIAL047", "REPRESENTANTELEGAL047", "TELF1047", "TELF2047", "EMAIL047", "REQLEIDO024", "NOTIFLEIDA024", "EXP_CERRADO_024", "IDIOMACOMUNIC024", "ID017FASE", "FASE_DESC", "FASE_DESE", "ID018TRAMITE", "TRAMITE_DESC", "TRAMITE_DESE", "ID035ESTADO", "DESCES035", "DESCEU035", "ID047", "OBSERVACIONES028")
AS
  SELECT T24.ID_ANO_024,
    T24.ID_024,
    T24.ID_PROV_024,
    T28.ID_028,
    T24.NUM_REG_024,
    T24.ID_PROV_024
    || '/'
    || T24.ID_ANO_024
    || '/'
    || lpad(T24.ID_024,5,'0'),
    T24.FEC_REG_024,
    T24.NIF_SOLICITANTE_024,
    t24.INSCRIPCION_FAVORABLE_024,
    T47.TIPO_DOC_044,
    T47.NIF_EMPRESA_047,
    T47.RAZON_SOCIAL_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T24.REQ_LEIDO_024,
    T24.NOTIF_LEIDA_024,
    T24.EXP_CERRADO_024,
    T24.IDIOMA_COMUNIC_024,
    T28.ID_017_FASE,
    FASES.DESC_ES_017 FASE_DESC,
    FASES.DESC_EU_017 FASE_DESE,
    T28.ID_018_TRAMITE,
    TRAMITES.DESC_ES_018 TRAMITE_DESC,
    TRAMITES.DESC_EU_018 TRAMITE_DESE,
    T28.ID_035_ESTADO,
    T35.DESC_ES_035,
    T35.DESC_EU_035,
    T47.id_047,
    t28.OBSERVACIONES_028
  FROM AB03B28T00 t28
  INNER JOIN AB03B24T00 T24
  ON T24.ID_PROV_024 = t28.ID_PROV_024
  AND T24.ID_ANO_024 = t28.ID_ANO_024
  AND T24.ID_024     = t28.ID_024_NUM_EXP
  INNER JOIN AB03B17T00 FASES
  ON FASES.ID_017 = t28.ID_017_FASE
  INNER JOIN AB03B18T00 TRAMITES
  ON TRAMITES.ID_017_FASE = t28.ID_017_FASE
  AND TRAMITES.ID_018     = t28.ID_018_TRAMITE
  INNER JOIN AB03B35T00 T35
  ON T35.ID_035 = t28.ID_035_ESTADO
  INNER JOIN AB03B47T00 T47
  ON T47.ID_047 = T24.ID_047_EMPRESA;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B26V00" ("ID_026", "OID_026", "DESC_ES_026", "DESC_EU_026", "ID_023_CODJMS", "META_INF_026", "ORG_DOC_ENTREGADO_026", "NOM_DOC_ENTREGADO_026", "FEC_DOC_ENTREGADO_026", "TIPO_APORTACION_026", "ID_025", "ID_017_FASE", "ID_018_TRAMITE", "ID_022_TIPO_PLAN", "DESC_ES_025", "DESC_EU_025", "ID_007_TP_DOC_DOKUSI", "ID_007_ST_DOC_DOKUSI")
AS
  SELECT T26.ID_026,
    t26.OID_026 ,
    t26.DESC_ES_026,
    t26.DESC_EU_026,
    t26.ID_023_CODJMS,
    t26.META_INF_026,
    T26.ORG_DOC_ENTREGADO_026,
    T26.NOM_DOC_ENTREGADO_026,
    T26.FEC_DOC_ENTREGADO_026,
    T26.TIPO_APORTACION_026,
    t25.ID_025,
    t25.ID_017_FASE,
    t25.ID_018_TRAMITE,
    t25.ID_022_TIPO_PLAN,
    t25.DESC_ES_025,
    t25.DESC_EU_025,
    t25.ID_007_TP_DOC_DOKUSI,
    t25.ID_007_ST_DOC_DOKUSI
  FROM ab03b26t00 t26
  INNER JOIN ab03b25t00 t25
  ON t25.ID_025 = t26.ID_025_DOC_SOL;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B28V00" ("ID028", "FACTU028", "USUA028", "FREGSAL028", "NREGSAL028", "FNOTIF028", "FEXPIR028", "FTRAMITACION028", "ID024NUMEXP", "ID017FASE", "ID018TRAMITE", "IDPROV024", "IDANO024", "OBSERVACIONES028", "TRAMITEES", "TRAMITEEU", "TIPOTRAMITE", "FASEES", "FASEEU", "IDESTADO", "ESTADOES", "ESTADOEU")
AS
  SELECT t1.ID_028 ID028,
    t1.F_ACTU_028 FACTU028,
    t1.USUA_028 USUA028,
    t1.F_REG_SAL_028 FREGSAL028,
    t1.N_REG_SAL_028 NREGSAL028,
    t1.F_NOTIF_028 FNOTIF028,
    t1.F_EXPIR_028 FEXPIR028,
    t1.F_TRAMITACION_028 FTRAMITACION028,
    t1.ID_024_NUM_EXP ID024NUMEXP,
    t1.ID_017_FASE ID017FASE,
    t1.ID_018_TRAMITE ID018TRAMITE,
    t1.ID_PROV_024 IDPROV024,
    t1.ID_ANO_024 IDANO024,
    t1.OBSERVACIONES_028 OBSERVACIONES028,
    TRAMITES.DESC_ES_018 TRAMITEES,
    TRAMITES.DESC_EU_018 TRAMITEEU,
    TRAMITES.ENT_SAL_018 TIPOTRAMITE,
    FASES.DESC_ES_017 FASEES,
    FASES.DESC_EU_017 FASEEU,
    ESTADOS.ID_035 IDESTADO,
    ESTADOS.DESC_ES_035 ESTADOES,
    ESTADOS.DESC_EU_035 ESTADOEU
  FROM AB03B28T00 t1
  INNER JOIN AB03B17T00 FASES
  ON FASES.ID_017 = t1.ID_017_FASE
  INNER JOIN AB03B18T00 TRAMITES
  ON TRAMITES.ID_017_FASE = t1.ID_017_FASE
  AND TRAMITES.ID_018     = t1.ID_018_TRAMITE
  INNER JOIN AB03B35T00 ESTADOS
  ON ESTADOS.ID_035 = t1.ID_035_ESTADO
  ORDER BY t1.ID_028 DESC;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B29V00" ("ID029", "IDPIF029", "ID024NUMEXP", "IDPROV024", "IDANO024", "ID027TIPODOCG", "DESCDOCEXP027", "DESEDOCEXP027", "ID028HISTOTRAM", "OID029", "OBSERVACIONESES029", "OBSERVACIONESEU029", "TIPOAPORTACION029", "ORGDOCENTREGADO029", "NOMDOCENTREGADO029", "FECDOCENTREGADO029", "ID017FASE", "ID018TRAMITE")
AS
  SELECT t29.ID_029 ID029,
    t29.ID_PIF_029 IDPIF029,
    t29.ID_024_NUM_EXP ID024NUMEXP,
    t29.ID_PROV_024 IDPROV024,
    t29.ID_ANO_024 IDANO024,
    t29.ID_027_TIPO_DOC_G ID027TIPODOCG,
    T27.Desc_Es_027 DESCDOCEXP027,
    T27.Desc_Eu_027 DESEDOCEXP027,
    t29.ID_028_HISTO_TRAM ID028HISTOTRAM,
    t29.OID_029 OID029,
    t29.OBSERVACIONES_ES_029 OBSERVACIONESES029,
    t29.OBSERVACIONES_EU_029 OBSERVACIONESEU029,
    t29.TIPO_APORTACION_029 TIPOAPORTACION029,
    t29.ORG_DOC_ENTREGADO_029 ORGDOCENTREGADO029,
    t29.NOM_DOC_ENTREGADO_029 NOMDOCENTREGADO029,
    t29.FEC_DOC_ENTREGADO_029 FECDOCENTREGADO029,
    t27.ID_017_FASE ID017FASE,
    t27.ID_018_TRAMITE ID018TRAMITE
  FROM AB03B29T00 t29
  INNER JOIN AB03B27T00 t27
  ON T29.Id_027_Tipo_Doc_G = t27.id_027
  UNION
  SELECT t29.ID_029 ID029,
    t29.ID_PIF_029 IDPIF029,
    t29.ID_024_NUM_EXP ID024NUMEXP,
    t29.ID_PROV_024 IDPROV024,
    t29.ID_ANO_024 IDANO024,
    t29.ID_027_TIPO_DOC_G ID027TIPODOCG,
    T25.Desc_Es_025 DESCDOCEXP027,
    T25.Desc_Eu_025 DESEDOCEXP027,
    t29.ID_028_HISTO_TRAM ID028HISTOTRAM,
    t29.OID_029 OID029,
    t29.OBSERVACIONES_ES_029 OBSERVACIONESES029,
    t29.OBSERVACIONES_EU_029 OBSERVACIONESEU029,
    t29.TIPO_APORTACION_029 TIPOAPORTACION029,
    t29.ORG_DOC_ENTREGADO_029 ORGDOCENTREGADO029,
    t29.NOM_DOC_ENTREGADO_029 NOMDOCENTREGADO029,
    t29.FEC_DOC_ENTREGADO_029 FECDOCENTREGADO029,
    t25.ID_017_FASE ID017FASE,
    t25.ID_018_TRAMITE ID018TRAMITE
  FROM AB03B29T00 t29
  INNER JOIN AB03B25T00 t25
  ON T29.Id_025_DOCENTRADA = t25.id_025;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B29V01" ("ID28", "OID", "NOMDOCUMENTO", "TIPODOCDOKUSI", "DESCDOCES", "DESCDOCEU", "OBSERVES", "OBSERVEU")
AS
  SELECT t29.ID_028_HISTO_TRAM,
    t29.OID_029,
    T07.DESC_EU_007,
    T07.DESC_ES_007,
    T27.DESC_ES_027,
    T27.DESC_EU_027,
    t29.OBSERVACIONES_ES_029,
    t29.OBSERVACIONES_EU_029
  FROM AB03B29t00 t29
  INNER JOIN ab03b27t00 t27
  ON t27.ID_027 = t29.ID_027_TIPO_DOC_G
  INNER JOIN AB03B07T00 T07
  ON T07.ID_007 = T27.ID_007_TP_DOC_DOKUSI;
/************************************************************/
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B31V00" ("ID_047", "NIF_EMPRESA_047", "NUM_RERA_047", "RAZON_SOCIAL_047", "DIRECCIONEMP", "PROVINCIA_047", "ID_PROVINCIA_047", "FECHA_ALTA_031", "FECHA_BAJA_031", "ULTIMO_ESTADO_031") AS 
  SELECT distinct(con1.id047),
      con1.nif, 
      con1.numRera,
      con1.razonSocial, 
      con1.domicilio, 
      con1.provincia, 
      con1.IDPROVINCIA,
      (SELECT F_CAMBIO_ESTADO_031 FROM AB03B31T00 t31 WHERE t31.ID_047_EMPRESA = con1.id047 AND t31.ESTADO_031 = 0) AS fechaAlta,
      (SELECT F_CAMBIO_ESTADO_031 FROM AB03B31T00 t31 WHERE t31.ID_047_EMPRESA = con1.id047 AND t31.ESTADO_031 = '1') AS fechaBaja, 
      (SELECT ESTADO_031 FROM AB03B31T00 t31 WHERE ID_047_EMPRESA = con1.id047 AND F_CAMBIO_ESTADO_031 = (SELECT MAX(F_CAMBIO_ESTADO_031) FROM AB03B31T00 WHERE ID_047_EMPRESA = con1.id047)) AS ultimoEstado
  from    
      (SELECT t47.ID_047 as id047,
         T47.NIF_EMPRESA_047 AS nif, 
         T47.NUM_RERA_047 AS numRera, 
         T47.RAZON_SOCIAL_047 AS razonSocial, 
          ((CASE
            WHEN NORACALLE.DS_O IS NULL
            THEN T47.CALLE_047
            ELSE CONCAT(CONCAT(CONCAT(NORACALLE.DS_O,' ('),NORACALLE.TIPO_VIA),')')
          END) || ' ' || T47.NUM_CALLE_047 || ' ' || T47.PISO_047 || T47.LETRA_047 || ', ' || T47.CP_047 || ' ' || NORAMUN.DS_O) AS domicilio, 
          NORAPROV.DS_O AS provincia, 
          t47.PROVINCIA_047 as IDPROVINCIA,
          t31.F_CAMBIO_ESTADO_031,
          T31.Estado_031
      FROM AB03B47T00 t47 
      INNER JOIN AB03B31T00 t31 ON t31.ID_047_EMPRESA = t47.ID_047
      INNER JOIN t17i.T17_PROVINCIA NORAPROV ON NORAPROV.ID = t47.PROVINCIA_047 
      INNER JOIN t17i.T17_MUNICIPIO NORAMUN ON NORAMUN.ID = t47.MUNICIPIO_047 AND NORAMUN.PROVINCIA_ID = t47.PROVINCIA_047 
      LEFT JOIN T17I.T17_CALLE NORACALLE ON NORACALLE.PROVINCIA_ID  = T47.PROVINCIA_047
                      AND NORACALLE.MUNICIPIO_ID = T47.MUNICIPIO_047
                      AND T47.CALLE_047 LIKE NORACALLE.ID) con1;
/*****************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B32V00" ("ID_032", "TIPODOC_032", "NIF_032", "NOMBRE_032", "APELLIDO1_032", "APELLIDO2_032", "ESTADO_032", "NSS_032", "SEXO_032", "ID_EMPRESA_047", "TIPODOC_047", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "CALLE_047", "NUMCALLE_047", "PISO_047", "LETRA_047", "CP_047", "MUNICIPIO_047", "PROVINCIA_047", "REPRESENTANTE_047", "TELF1_047", "TELF2_047", "FAX_047", "EMAIL_047", "ACTIVO_SN_047", "NSS_047", "CNAE_047", "NOMBRE_EMPRESA_047", "NUM_RERA_047")
AS
  SELECT t32.ID_032,
    t32.tipo_doc_044,
    t32.nif_032,
    t32.nombre_032,
    t32.apellido1_032,
    t32.apellido2_032,
    t32.estado_032,
    t32.nss_032,
    t32.sexo_032,
    t47.id_047,
    t47.tipo_doc_044,
    t47.nif_empresa_047,
    t47.razon_social_047,
    t47.calle_047,
    t47.num_calle_047,
    t47.piso_047,
    t47.letra_047,
    t47.cp_047,
    t47.municipio_047,
    t47.provincia_047,
    t47.Representante_Legal_047,
    t47.Telf1_047,
    t47.Telf2_047,
    t47.Fax_047,
    t47.Email_047,
    t47.activo_sn_047,
    t47.nss_047,
    t47.Cnae_047,
    t47.nombre_empresa_047,
    t47.num_rera_047
  FROM ab03b32t00 t32
  INNER JOIN ab03b47t00 t47
  ON t32.id_047_empresa = t47.id_047;
/************************************************************/
CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B32V00TRABEXP" ("ID_010", "ID_ANO_010", "ID_PROV_010", "ID_EMPRESA_047", "ACTIVO_SN_047", "TIPODOC_032", "NIF_032", "NOMBRE_032", "APELLIDO1_032", "APELLIDO2_032", "ESTADO_032", "NSS_032", "SEXO_032", "FECHA_APTITUD_032", "TITULACIONES_032", "ACTIVO_033", "ID032") AS 
 SELECT t10.Id_010,
    t10.Id_Ano_010,
    t10.Id_Prov_010,
    T32.Id_047_Empresa,
    t47.activo_sn_047,
    t32.tipo_doc_044,
    t32.nif_032,
    t32.nombre_032,
    t32.apellido1_032,
    t32.apellido2_032,
    t32.estado_032,
    t32.nss_032,
    t32.sexo_032,
    t32.fecha_aptitud_032,
    t32.titulaciones_032,
    t33.activo_sn_033,
    t32.ID_032
  FROM ab03b32t00 t32
  INNER JOIN ab03b33t00 t33 ON t32.id_032 = t33.Id_032_Trab
  INNER JOIN ab03b10t00 t10
            ON T33.Id_010_Exp      = T10.Id_010
            AND T33.Id_Ano_010     = T10.Id_Ano_010
            AND T33.Id_Prov_010    = T10.Id_Prov_010
            AND T10.Id_047_Empresa = T32.Id_047_Empresa
  INNER JOIN ab03b47t00 t47 ON T47.Id_047 = T32.Id_047_Empresa
  order by t32.ID_032;
/************************************************************/
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B39V00" ("ID_039", "TERRITORIO_039", "TERRITORIO_DO", "CALLE_039", "NUMERO_039", "PISO_039", "LETRA_039", "COD_POSTAL_039", "MUNICIPIO_039", "MUN_DO", "TELEFONO_039", "FAX_039", "DESC_ES_039", "DESC_EU_039", "ID_045_ORG", "DESC_ES_045", "DESC_EU_045", "REG_MANUAL_039", "EMAIL_039", "ID_PERFIL_039") AS 
  SELECT T39.ID_039,
    T39.TERRITORIO_039,
    NORAPROV.DS_O TERRITORIO_DO,
    T39.CALLE_039,
    T39.NUMERO_039,
    T39.PISO_039,
    T39.LETRA_039,
    T39.COD_POSTAL_039,
    T39.MUNICIPIO_039,
    NORAMUN.DS_O MUN_DO,
    T39.TELEFONO_039,
    T39.FAX_039,
    T39.DESC_ES_039,
    T39.DESC_EU_039,
    T39.ID_045_ORG,
    T45.DESC_ES_045,
    T45.DESC_EU_045,
    T39.REG_MANUAL_039,
    T39.EMAIL_039,
    T39.ID_PERFIL_039
  FROM AB03B39T00 T39
  INNER JOIN AB03B45T00 T45
  ON T45.ID_045 = T39.ID_045_ORG
  INNER JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID = T39.TERRITORIO_039
  INNER JOIN T17I.T17_MUNICIPIO NORAMUN
  ON NORAMUN.PROVINCIA_ID = T39.TERRITORIO_039
  AND NORAMUN.ID          = T39.MUNICIPIO_039;
  
/************************************************************/
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', 'Sede Electrónica', 'Egoitza Elektronikoa',  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 EDITIONABLE VIEW "AB03B"."AB03B47V00" ("ID_047", "NIF_EMPRESA_047", "NUM_RERA_047", "FECHA_INSCRIPCION", "RAZON_SOCIAL_047", "DIRECCIONEMP1", "DIRECCIONEMP2", "TELF1_047", "TELF2_047", "FAX_047", "EMAIL_047", "PROVINCIA_047", "ACTIVO_SN_047")
AS
  SELECT t47.id_047,
    t47.nif_empresa_047,
    t47.Num_Rera_047,
    t31.f_cambio_estado_031,
    T47.Razon_Social_047,
    CASE
      WHEN NORACALLEEMP.DS_O IS NULL
      THEN T47.CALLE_047
        || ', '
        || T47.NUM_CALLE_047
        || DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
      ELSE CONCAT(CONCAT(CONCAT(NORACALLEEMP.DS_O,' ('),NORACALLEEMP.TIPO_VIA),')')
        || ', '
        || T47.NUM_CALLE_047
        || DECODE(T47.PISO_047, NULL, '', ' - '
        || T47.PISO_047)
        || ''
        || T47.LETRA_047
    END AS DIRECCIONEMP1,
    CASE
      WHEN LOCALIDAD_047 IS NOT NULL
      THEN T47.CP_047
        || ' '
        || NORALOC.DS_O
      ELSE T47.CP_047
        || ' '
        || NORAMUN.DS_O
    END AS DIRECCIONEMP2,
    T47.Telf1_047,
    T47.Telf2_047,
    T47.fax_047,
    T47.email_047,
    T47.Provincia_047,
    T47.Activo_Sn_047
  FROM ab03b47t00 t47
  LEFT JOIN ab03b31t00 t31
  ON t47.id_047     = T31.Id_047_Empresa
  AND t31.ESTADO_031=0
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID =T47.PROVINCIA_047
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN
  ON NORAMUN.PROVINCIA_ID = T47.PROVINCIA_047
  AND NORAMUN.ID          = T47.MUNICIPIO_047
  LEFT JOIN T17I.T17_LOCALIDAD NORALOC
  ON NORALOC.PROVINCIA_ID  = T47.PROVINCIA_047
  AND NORALOC.MUNICIPIO_ID = T47.MUNICIPIO_047
  AND NORALOC.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 t47.Activo_Sn_047 IS NOT NULL;
/************************************************************/
CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B49V00" ("ID_049", "TERRITORIO_049", "TERRITORIO_DO", "CALLE_049", "NUMERO_049", "PISO_049", "LETRA_049", "COD_POSTAL_049", "MUNICIPIO_049", "MUN_DO", "TELEFONO_049", "FAX_049", "DESC_ES_049", "DESC_EU_049", "ID_048_ORG", "DESC_ES_048", "DESC_EU_048", "REG_MANUAL_049", "EMAIL_049", "ID_PERFIL_049") AS 
  SELECT T49.ID_049,
    T49.TERRITORIO_049,
    NORAPROV.DS_O TERRITORIO_DO,
    T49.CALLE_049,
    T49.NUMERO_049,
    T49.PISO_049,
    T49.LETRA_049,
    T49.COD_POSTAL_049,
    T49.MUNICIPIO_049,
    NORAMUN.DS_O MUN_DO,
    T49.TELEFONO_049,
    T49.FAX_049,
    T49.DESC_ES_049,
    T49.DESC_EU_049,
    T49.ID_048_ORG,
    T48.DESC_ES_048,
    T48.DESC_EU_048,
    T49.REG_MANUAL_049,
    T49.EMAIL_049,
    T49.ID_PERFIL_049
  FROM AB03B49T00 T49
  INNER JOIN AB03B48T00 T48
  ON T48.ID_048 = T49.ID_048_ORG
  INNER JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID = T49.TERRITORIO_049
  INNER JOIN T17I.T17_MUNICIPIO NORAMUN
  ON NORAMUN.PROVINCIA_ID = T49.TERRITORIO_049
  AND NORAMUN.ID          = T49.MUNICIPIO_049;
 
/************************************************************/
  CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B50V00" ("IDFASE", "IDTRAMITE", "IDPROV", "IDANO", "IDNUMEXP", "IDHIST", "IDCOMUNICACION", "TIPOORG", "IDORGANISMO", "DESCES", "DESCEU", "EMAILES", "EMAILEU", "ESTADO", "TIPOTRAMITE", "FECHACOMUNICACIONC", "FECHACOMUNICACIONE") AS 
  SELECT T28.ID_017_FASE,
    T28.ID_018_TRAMITE,
    T28.ID_PROV_024,
    T28.ID_ANO_024,
    T28.ID_024_NUM_EXP,
    T28.ID_028,
    t50.ID_050,
    'E',
    t24.ID_047_EMPRESA,
    'Empresa' ,
    'Enpresa', 
    'Mis Gestiones', 
    'Nire Kudeaketak',
    NVL(ESTADO_050,'0') ESTADO_050,
    t18.ENT_SAL_018,
    TO_CHAR(t50.FEC_COMUNIC_050, 'dd/mm/yyyy'),
    TO_CHAR(t50.FEC_COMUNIC_050, 'yyyy/mm/dd')
  FROM ab03b28t00 T28
  INNER JOIN ab03b24t00 t24
  ON t24.ID_PROV_024 = T28.ID_PROV_024
  AND t24.ID_ANO_024 = T28.ID_ANO_024
  AND t24.ID_024     = T28.ID_024_NUM_EXP
  INNER JOIN ab03b50t00 t50
  ON t50.ID_028_HISTO_TRAM = T28.ID_028
  AND t50.ID_047_EMPRESA   = t24.ID_047_EMPRESA
  INNER JOIN ab03b18t00 t18
  ON t18.ID_017_FASE = T28.ID_017_FASE
  AND t18.ID_018     = T28.ID_018_TRAMITE
  UNION
  SELECT T28.ID_017_FASE,
    T28.ID_018_TRAMITE,
    T28.ID_PROV_024,
    T28.ID_ANO_024,
    T28.ID_024_NUM_EXP,
    T28.ID_028,
    T50.ID_050,
    'O',
    t49.ID_049,
    t48.DESC_ES_048,
    t48.DESC_EU_048 ,
    t49.EMAIL_049,
    t49.EMAIL_049,
    NVL(ESTADO_050,'0') ESTADO_050,
    t18.ENT_SAL_018,
    TO_CHAR(t50.FEC_COMUNIC_050, 'dd/mm/yyyy'),
    TO_CHAR(t50.FEC_COMUNIC_050, 'yyyy/mm/dd')
  FROM ab03b28t00 T28
  INNER JOIN ab03b50t00 t50
  ON t50.ID_028_HISTO_TRAM = T28.ID_028
  INNER JOIN ab03b49t00 t49 ON t49.ID_PERFIL_049 = T28.ID_PROV_024 and t49.ID_048_ORG = t50.ID_049_ORGAN
  INNER JOIN ab03b48t00 t48
  ON t48.ID_048 = t49.ID_048_ORG
  INNER JOIN ab03b18t00 t18
  ON t18.ID_017_FASE = T28.ID_017_FASE
  AND t18.ID_018     = T28.ID_018_TRAMITE;
/************************************************************/
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B56V00" ("ID_014", "ID_001_FASE", "ID_002_TRAMITE", "ID_010_NUM_EXP", "ID_ANO_010", "ID_PROV_010", "ID_056", "PREVISION_MCA_056", "ESTIMACION_MCA_056", "PROVINCIA_TRABAJO_056", "MUNICIP_TRABAJO_056", "CALLE_TRABAJO_056", "NUMERO_TRABAJO_056", "PISO_TRABAJO_056", "LETRA_TRABAJO_056", "CPOSTAL_TRABAJO_056", "F_INICIO_TRABAJO_056", "DURACION_DIAS_056", "ID_041", "DESC_ES_041", "DESC_EU_041", "ID_054", "DESC_ES_054", "DESC_EU_054", "ID_055", "DESC_ES_055", "DESC_EU_055") 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.Prevision_Mca_056,
    T56.Estimacion_Mca_056,
    t56.PROVINCIA_TRABAJO_056,
    t56.MUNICIP_TRABAJO_056,
    t56.CALLE_TRABAJO_056,
    t56.NUMERO_TRABAJO_056,
    t56.PISO_TRABAJO_056,
    t56.LETRA_TRABAJO_056,
    t56.CPOSTAL_TRABAJO_056,
    t56.F_INICIO_TRABAJO_056,
    t56.DURACION_DIAS_056,
    T41.Id_041,
    T41.Desc_Es_041,
    T41.Desc_Eu_041,
    T54.Id_054,
    T54.Desc_Es_054,
    T54.Desc_Eu_054,
    T55.Id_055,
    T55.Desc_Es_055,
    T55.Desc_Eu_055
  FROM ab03b14t00 t14
  LEFT OUTER JOIN ab03b56t00 t56
  ON t14.id_014 = T56.Id_014
  LEFT OUTER JOIN ab03b41t00 t41
  ON T56.Id_041_Material = T41.Id_041
  LEFT OUTER JOIN ab03b54t00 t54
  ON T56.Id_054_Actividad = T54.Id_054
  LEFT OUTER JOIN ab03b55t00 t55
  ON T56.Id_055_Sub_Actividad = T55.Id_055;
/***************************************************************/
 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 EDITIONABLE VIEW "AB03B"."AB03B57V00" ("ID_014", "ID_001_FASE", "ID_002_TRAMITE", "ID_010_NUM_EXP", "ID_ANO_010", "ID_PROV_010", "ID_057", "NIMA_057", "EXISTE_DSC_057", "CANTIDAD_057", "CORRECTO_NIMA_057", "NUMERO_DA_057", "ACTIV_EVID_057", "SOLIC_PLAN_057", "RECIBIDO_057", "CORRECTO_ANEXO_057", "REENVIADO_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,
    t57.id_057,
    t57.nima_057,
    t57.existe_dsc_057,
    t57.cantidad_057,
    t57.correcto_nima_057,
    t57.numero_da_057,
    t57.activ_evid_057,
    t57.solic_plan_057,
    t57.recibido_057,
    t57.correcto_anexo_057,
    t57.reenviado_057
  FROM ab03b14t00 t14
  LEFT OUTER JOIN ab03b57t00 t57
  ON t14.id_014 = T57.Id_014;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B59V00" ("ID_056", "ID_059", "ID_053", "DESC_ES_053", "DESC_EU_053")
AS
  SELECT T56.Id_056,
    T59.Id_059,
    T53.Id_053,
    T53.Desc_Es_053,
    T53.Desc_Eu_053
  FROM ab03b56t00 t56
  LEFT OUTER JOIN ab03b59t00 t59
  ON T56.Id_056 = T59.Id_056_Ejecucion
  LEFT OUTER JOIN ab03b53t00 t53
  ON T59.Id_053_Trabajo = T53.Id_053;
 /***********************************************************/
    CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."AB03B62V00" ("ID011", "DESCES011", "DESCEU011", "ID015DOC", "ID008TIPOPLAN", "ID011TPDOC", "ID062") AS 
  SELECT ID011, DESCES011, DESCEU011, ID015DOC, ID008TIPOPLAN, ID011TPDOC, SUM(ID062)
FROM 
(
SELECT T11.ID_011 ID011, T11.DESC_ES_011 DESCES011, T11.DESC_EU_011 DESCEU011,T62.ID_015_DOC ID015DOC, T11.ID_008_TIPO_PLAN ID008TIPOPLAN
, T11.ID_007_TP_DOC_DOKUSI ID011TPDOC, T62.ID_062 ID062
FROM AB03B62T00 T62
INNER JOIN AB03B11T00 T11 ON T11.ID_011 = T62.ID_011_TP_DOC 
UNION
SELECT T11.ID_011 ID011, T11.DESC_ES_011 DESCES011, T11.DESC_EU_011 DESCEU011, T15.ID_015 ID015DOC, T11.ID_008_TIPO_PLAN ID008TIPOPLAN
, T11.ID_007_TP_DOC_DOKUSI ID011TPDOC, 0 ID062
FROM AB03B11T00 T11, AB03B15T00 T15 
--INNER JOIN AB03B15T00 T15 ON T15.ID_015 = 9
WHERE T11.ID_001_FASE=2 AND T11.ID_002_TRAMITE=3 AND T11.ID_007_TP_DOC_DOKUSI NOT IN (1,10)
) TABLA
GROUP BY ID011, DESCES011, DESCEU011, ID015DOC, ID008TIPOPLAN, ID011TPDOC
order by 1;
/************************************************************/
 CREATE OR REPLACE FORCE VIEW "AB03B"."ESTADISTICAS_INDICADORES" ("ANYO", "ID", "FECREG", "MES", "IDPROVINCIA", "DS_O", "ESTADO", "EXPEDIENTE", "FECHAVISITA", "INCUMPLIMIENTO") AS  
 SELECT t10.ID_ANO_010 anyo,
    t10.ID_010 id,
    t10.FEC_REG_010 FECREG,
    EXTRACT( MONTH FROM t10.FEC_REG_010)MES,
    t10.ID_PROV_010 IDPROVINCIA,
    NORAPROV.DS_O,
    t10.ESTADO_EXP_010 estado,
    t10.ID_PROV_010
    ||'/'
    ||t10.ID_ANO_010
    ||'/'
    || lpad(T10.ID_010,5,'0') expediente,
    T58.FECHA_HORA_058,
    T58.INCUMPLIMIENTO_058
  FROM AB03B10T00 t10
  INNER JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID = t10.ID_PROV_010
  INNER JOIN AB03B14T00 T14
  ON T14.ID_ANO_010     =t10.ID_ANO_010
  AND T14.ID_PROV_010   =t10.ID_PROV_010
  AND T14.ID_010_NUM_EXP=t10.ID_010
  INNER JOIN AB03B58T00 T58
  ON T58.ID_014=T14.ID_014;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."ESTADISTICAS_PARLAMENTARIAS" ("TIPOPLAN", "TIPOPLANEU", "IDTIPO", "ANYO", "ID", "FECREG", "MES", "DS_O", "ACTIVIDAD", "ACTIVIDADEU", "ESTADO", "EXPEDIENTE", "IDPROVINCIA", "IDACTIVIDAD")
AS
  SELECT T08.DESC_ES_008 tipoPlan,
    T08.DESC_EU_008 tipoPlanEu,
    t08.id_008 idTipo,
    t10.ID_ANO_010 anyo,
    t10.ID_010 id,
    t10.FEC_REG_010 FECREG,
    EXTRACT( MONTH FROM t10.FEC_REG_010)MES,
    NORAPROV.DS_O,
    T54.DESC_ES_054 actividad,
    T54.DESC_EU_054 actividadEu,
    ESTADO_EXP_010 estado,
    T10.ID_PROV_010
    || '/'
    || T10.ID_ANO_010
    || '/'
    || lpad(T10.ID_010,5,'0') expediente,
    t10.ID_PROV_010 IDPROVINCIA,
    T10.ID_054_ACTIV
  FROM AB03B10T00 t10
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID = t10.ID_PROV_010
  LEFT JOIN AB03B54T00 T54
  ON T54.ID_054 = t10.ID_054_ACTIV
  INNER JOIN AB03B08T00 T08
  ON T08.ID_008=t10.ID_008_TIPO_PLAN;
/************************************************************/
CREATE OR REPLACE FORCE EDITIONABLE VIEW "AB03B"."J73BCNAE" ("SECC_003", "DIVI_003", "GRUP_003", "CLAS_003", "CNAE", "DESC_003", "FECHORACT_003")
AS
  SELECT SECC_003,
    DIVI_003,
    GRUP_003,
    CLAS_003,
    DIVI_003
    || TRIM(GRUP_003)
    || TRIM(CLAS_003) AS cnae,
    desc_003,
    fechoract_003
  FROM j73b.j73b03t00;
/************************************************************/
  
 CREATE OR REPLACE FORCE  VIEW "AB03B"."AB03B63V00TRABINICIO" ("ID_010", "ID_ANO_010", "ID_PROV_010", "ID_EMPRESA_047", "ACTIVO_SN_047", "TIPODOC_032", "NIF_032", "NOMBRE_032", "APELLIDO1_032", "APELLIDO2_032", "ESTADO_032", "NSS_032", "SEXO_032", "FECHA_APTITUD_032", "TITULACIONES_032", "ACTIVO_033", "ID032", "ID063","ID056") AS 
  SELECT t10.Id_010,
    t10.Id_Ano_010,
    t10.Id_Prov_010,
    T32.Id_047_Empresa,
    t47.activo_sn_047,
    t32.tipo_doc_044,
    t32.nif_032,
    t32.nombre_032,
    t32.apellido1_032,
    t32.apellido2_032,
    t32.estado_032,
    t32.nss_032,
    t32.sexo_032,
    t32.fecha_aptitud_032,
    t32.titulaciones_032,
    t33.activo_sn_033,
    t32.ID_032,
    t63.ID_063,
    t56.ID_056
    FROM ab03b63t00 t63
  INNER JOIN ab03b33t00 t33  ON t63.Id_033_TRABEXP = t33.Id_033
  INNER JOIN ab03b56t00 t56  ON t63.Id_056_DATEJEC = t56.Id_056
  INNER JOIN ab03b32t00 t32 ON t32.id_032 = t33.Id_032_Trab
  INNER JOIN ab03b10t00 t10
            ON T33.Id_010_Exp      = T10.Id_010
            AND T33.Id_Ano_010     = T10.Id_Ano_010
            AND T33.Id_Prov_010    = T10.Id_Prov_010
            AND T10.Id_047_Empresa = T32.Id_047_Empresa
  INNER JOIN ab03b47t00 t47 ON T47.Id_047 = T32.Id_047_Empresa
  order by t32.ID_032;

/************************************************************/
CREATE OR REPLACE VIEW AB03B10V00ARCHIVO (
IDEXPEDIENTE, ID010, IDANO010, IDPROV010, NIFTITULAR010, NOMBRETITULAR010, APE1TITULAR010, APE2TITULAR010, ID047EMPRESA, NIFEMPRESA047, NOMBREEMPRESA047,
ESTADOEXP010, EXPCERRADO010, OIDDOCVISITA010, OIDDOCSANCION010, EXPARCHIVADO010) AS
SELECT 
T10.ID_PROV_010||'/'||T10.ID_ANO_010||'/'||LPAD(T10.ID_010,5,'0'),
T10.ID_010,
T10.ID_ANO_010,
T10.ID_PROV_010,
T10.NIF_TITULAR_010,
T10.NOMBRE_TITULAR_010,
T10.APE1_TITULAR_010,
T10.APE2_TITULAR_010,
T10.ID_047_EMPRESA,
T47.NIF_EMPRESA_047,
T47.NOMBRE_EMPRESA_047,
T10.ESTADO_EXP_010,
T10.EXP_CERRADO_010,
T10.OID_DOC_VISITA_010,
T10.OID_DOC_SANCION_010,
T10.EXP_ARCHIVADO_010
FROM AB03B10T00 T10
INNER JOIN AB03B47T00 T47 ON T47.ID_047 = T10.ID_047_EMPRESA
WHERE T10.EXP_CERRADO_010 = 'S' AND T10.EXP_ARCHIVADO_010=0;

/************************************************************/
CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B15V00ARCHIVO" ("ID", "IDEXPEDIENTE", "IDPROV", "IDANO", "IDNUMEXP", "ORIGEN", "DESCDOCEXP", 
"DESEDOCEXP", "OID", "IDPIF", "ORGDOCENTREGADO", "FECDOCENTREGADO", "NOMDOCENTREGADO", "IDDOCENTRADA", "IDTPDOCDOKUSI", "TIPODOCDOKUSI", "DESCTIPODOCDOKUSI",
"MOTIVORECHAZO") AS 
  (select t15.id_015, T15.ID_PROV_010||'/'||T15.ID_ANO_010||'/'||T15.ID_010_NUM_EXP, T15.ID_PROV_010, T15.ID_ANO_010, T15.ID_010_NUM_EXP,
'CIUDADANO' origen, T11.DESC_ES_011, T11.DESC_EU_011, T15.OID_015, T15.ID_PIF_015, T15.ORG_DOC_ENTREGADO_015,
T15.FEC_DOC_ENTREGADO_015, T15.NOM_DOC_ENTREGADO_015, T15.ID_011_DOCENTRADA, 
T11.ID_007_TP_DOC_DOKUSI, T07.DESC_ES_007, T07.DESC_EU_007, T15.MOTIVO_RECHAZO_015
FROM AB03B15T00 t15
INNER JOIN AB03B11T00 t11  ON T15.ID_011_DOCENTRADA = t11.id_011
INNER JOIN AB03B07T00 T07 ON T07.ID_007 = T11.ID_007_TP_DOC_DOKUSI
WHERE (T15.MOTIVO_RECHAZO_015 IS NULL OR T15.MOTIVO_RECHAZO_015 != '')
UNION
select t15.id_015, T15.ID_PROV_010||'/'||T15.ID_ANO_010||'/'||T15.ID_010_NUM_EXP, T15.ID_PROV_010, T15.ID_ANO_010, T15.ID_010_NUM_EXP,
'ADMINISTRACION' origen, T13.DESC_ES_013, T13.DESC_EU_013, T15.OID_015, T15.ID_PIF_015, T15.ORG_DOC_ENTREGADO_015,
T15.FEC_DOC_ENTREGADO_015, T15.NOM_DOC_ENTREGADO_015, T15.ID_011_DOCENTRADA,
T13.ID_007_TP_DOC_DOKUSI, T07.DESC_ES_007, T07.DESC_EU_007, T15.MOTIVO_RECHAZO_015
FROM AB03B15T00 t15
INNER JOIN AB03B13T00 t13  ON T15.ID_013_TIPO_DOC_G= t13.id_013
INNER JOIN AB03B07T00 T07 ON T07.ID_007 = T13.ID_007_TP_DOC_DOKUSI
WHERE (T15.MOTIVO_RECHAZO_015 IS NULL OR T15.MOTIVO_RECHAZO_015 != ''));  
  
CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B24V00" ("ID_024", "ID_PROV_024", "ID_ANO_024", "NUM_REG_024", "ID_EXPEDIENTE", "FEC_REG_024", "TIPO_DOC_024", "NIF_SOLICITANTE_024", "NOMBRE_SOLICIT_024", "APE1_SOLICIT_024", "APE2_SOLICIT_024", "CARGO_EMPRESA_024", "IDIOMA_COMUNIC_024", "INSCRIPCION_FAVORABLE_024", "EXP_CERRADO_024", "NOTIF_LEIDA_024", "REQ_LEIDO_024", "CAMBIO_ESTATUTOS_024", "ESTADO_EMPRESA_024", "F_CAMBIO_ESTADO_024", "ID_022_TIPO_PLAN", "PLAN_ES", "PLAN_EU", "ID_047", "TIPO_DOC_047", "NIF_EMPRESA_047", "RAZON_SOCIAL_047", "CALLE_047", "NUM_CALLE_047", "PISO_047", "LETRA_047", "CP_047", "LOCALIDAD_DESC", "LOCALIDAD_047", "MUNICIPIO_DESC", "MUNICIPIO_047", "PROVINCIA_DESC", "PROVINCIA_047", "REPRESENTANTE_LEGAL_047", "TELF1_047", "TELF2_047", "EMAIL_047", "FAX_047", "NSS_047", "CNAE_047", "NOMBRE_EMPRESA_047", "NUM_RERA_047", "ID_017_FASE", "FASE_ES", "FASE_EU", "ID_018_TRAMITE", "TRAMITE_ES", "TRAMITE_EU", "ID_035_ESTADO", "ESTADO_ES", "ESTADO_EU", "REF_CTRAB_047", 
    "NIF_REPRESENTANTE_024", "NOMBRE_REPRESENTANTE_024") AS 
  SELECT T24.ID_024,
    T24.ID_PROV_024,
    T24.ID_ANO_024,
    T24.NUM_REG_024,
    T24.ID_PROV_024
    || '/'
    || T24.ID_ANO_024
    || '/'
    || lpad(T24.ID_024, 5, '0') ,
    T24.FEC_REG_024,
    T24.TIPO_DOC_044,
    T24.NIF_SOLICITANTE_024,
    T24.NOMBRE_SOLICIT_024,
    T24.APE1_SOLICIT_024,
    T24.APE2_SOLICIT_024,
    T24.CARGO_EMPRESA_024,
    T24.IDIOMA_COMUNIC_024,
    T24.INSCRIPCION_FAVORABLE_024,
    T24.EXP_CERRADO_024,
    T24.NOTIF_LEIDA_024,
    T24.REQ_LEIDO_024,
    T24.CAMBIO_ESTATUTOS_024,
    T24.ESTADO_EMPRESA_024,
    T24.F_CAMBIO_ESTADO_024,
    T24.ID_022_TIPO_PLAN,
    T22.DESC_ES_022,
    T22.DESC_EU_022,
    T47.id_047,
    T47.TIPO_DOC_044,
    T47.NIF_EMPRESA_047,
    T47.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,
    NORALOCA.DS_O LOCA_DO,
    T47.LOCALIDAD_047,
    NORAMUN.DS_O MUN_DO,
    T47.MUNICIPIO_047,
    NORAPROV.DS_O PROV_DO,
    T47.PROVINCIA_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_047,
    T47.FAX_047,
    T47.NSS_047,
    T47.CNAE_047,
    T47.NOMBRE_EMPRESA_047,
    T47.NUM_RERA_047,
    T28.ID_017_FASE,
    FASES.DESC_ES_017 FASE_DESC,
    FASES.DESC_EU_017 FASE_DESE,
    T28.ID_018_TRAMITE,
    TRAMITES.DESC_ES_018 TRAMITE_DESC,
    TRAMITES.DESC_EU_018 TRAMITE_DESE,
    T28.ID_035_ESTADO,
    ESTADOS.DESC_ES_035 ESTADO_DESC,
    ESTADOS.DESC_EU_035 ESTADO_DESE,
    T47.REF_CTRAB_047 REF_CTRAB_047,
    T24.NIF_REPRESENTANTE_024 NIF_REPRESENTANTE_024,
    T24.NOMBRE_REPRESENTANTE_024
  FROM AB03B24T00 T24
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T24.ID_047_EMPRESA
  INNER JOIN AB03B28T00 T28
  ON T28.ID_024_NUM_EXP = T24.ID_024
  AND T28.ID_PROV_024   = T24.ID_PROV_024
  AND T28.ID_ANO_024    = T24.ID_ANO_024
  AND T28.ID_028       IN
    (SELECT MAX(id_028)
    FROM AB03B28T00
    GROUP BY ID_024_NUM_EXP,
      ID_PROV_024,
      ID_ANO_024
    )
  INNER JOIN AB03B22T00 T22
  ON T22.ID_022 = T24.ID_022_TIPO_PLAN
  INNER JOIN AB03B17T00 FASES
  ON FASES.ID_017 = T28.ID_017_FASE
  INNER JOIN AB03B18T00 TRAMITES
  ON TRAMITES.ID_017_FASE = T28.ID_017_FASE
  AND TRAMITES.ID_018     = T28.ID_018_TRAMITE
  INNER JOIN AB03B35T00 ESTADOS
  ON ESTADOS.ID_035 = T28.ID_035_ESTADO
  LEFT JOIN T17I.T17_PROVINCIA NORAPROV
  ON NORAPROV.ID =T47.PROVINCIA_047
  LEFT JOIN T17I.T17_MUNICIPIO NORAMUN
  ON NORAMUN.PROVINCIA_ID = T47.PROVINCIA_047
  AND NORAMUN.ID          = T47.MUNICIPIO_047
  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;