  /*************************************************************************/

  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", "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.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,
      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
    ) C1;
 
    
     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", "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") 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,
    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
  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_CALLE NORACALLEEMP
  ON NORACALLEEMP.PROVINCIA_ID  = T47.PROVINCIA_047
  AND NORACALLEEMP.MUNICIPIO_ID = T47.MUNICIPIO_047
  AND T47.CALLE_047 LIKE NORACALLEEMP.ID;