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;