
  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;
 
