
  CREATE OR REPLACE FORCE EDITIONABLE 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 T14.ID_PROV_010 = T10.ID_PROV_010
                                AND T14.ID_ANO_010 = T10.ID_ANO_010
                                AND T14.ID_010_NUM_EXP = T10.ID_010
    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_PROV_010 = T10.ID_PROV_010
                                AND T14.ID_ANO_010 = T10.ID_ANO_010
                                AND T14.ID_010_NUM_EXP = T10.ID_010
    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';
