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';

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", "MUNICIPIO_047", "PROVINCIA_047", "REPRESENTANTE_LEGAL_047", "TELF1_047", "TELF2_047", "EMAIL_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", "EXP_SANC_010", "TECN_ASIGN_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")
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,
    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,
    T47.PROVINCIA_047,
    T47.REPRESENTANTE_LEGAL_047,
    T47.TELF1_047,
    T47.TELF2_047,
    T47.EMAIL_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,
    T10.EXP_SANC_010,
    T10.TECN_ASIGN_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
  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_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"."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", "ID_047", "OBSERVACIONES_014")
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,
    T47.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,
    T47.id_047,
    t14.OBSERVACIONES_014
  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
  LEFT JOIN AB03B47T00 T47
  ON T47.ID_047 = T10.ID_047_EMPRESA;
  
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")
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,
    T47.CALLE_047,
    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
  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;
  

  
  CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B10V00FICHA" ("ID_ANO_010", "ID_010", "ID_PROV_010", "ID_EXPEDIENTE", "NUM_REG_010", "FEC_REG_010", "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", "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.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.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,
      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,
      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
    ) C1;