CREATE OR REPLACE FORCE VIEW "AB03B"."AB03B50V00" ("IDFASE", "IDTRAMITE", "IDPROV", "IDANO", "IDNUMEXP", "IDHIST", "IDCOMUNICACION", "TIPOORG", "IDORGANISMO", "DESCES", "DESCEU", "EMAIL", "ESTADO", "TIPOTRAMITE", "FECHACOMUNICACIONC", "FECHACOMUNICACIONE")
AS
  SELECT T28.ID_017_FASE,
    T28.ID_018_TRAMITE,
    T28.ID_PROV_024,
    T28.ID_ANO_024,
    T28.ID_024_NUM_EXP,
    T28.ID_028,
    t50.ID_050,
    'E',
    t24.ID_047_EMPRESA,
    'Empresa' ,
    '*Empresa',
    'Mis Gestiones',
    NVL(ESTADO_050,'0') ESTADO_050,
    t18.ENT_SAL_018,
    TO_CHAR(t50.FEC_COMUNIC_050, 'dd/mm/yyyy'),
    TO_CHAR(t50.FEC_COMUNIC_050, 'yyyy/mm/dd')
  FROM ab03b28t00 T28
  INNER JOIN ab03b24t00 t24
  ON t24.ID_PROV_024 = T28.ID_PROV_024
  AND t24.ID_ANO_024 = T28.ID_ANO_024
  AND t24.ID_024     = T28.ID_024_NUM_EXP
  INNER JOIN ab03b50t00 t50
  ON t50.ID_028_HISTO_TRAM = T28.ID_028
  AND t50.ID_047_EMPRESA   = t24.ID_047_EMPRESA
  INNER JOIN ab03b18t00 t18
  ON t18.ID_017_FASE = T28.ID_017_FASE
  AND t18.ID_018     = T28.ID_018_TRAMITE
  UNION
  SELECT T28.ID_017_FASE,
    T28.ID_018_TRAMITE,
    T28.ID_PROV_024,
    T28.ID_ANO_024,
    T28.ID_024_NUM_EXP,
    T28.ID_028,
    T50.ID_050,
    'O',
    t49.ID_049,
    t48.DESC_ES_048,
    t48.DESC_EU_048 ,
    t49.EMAIL_049,
    NVL(ESTADO_050,'0') ESTADO_050,
    t18.ENT_SAL_018,
    TO_CHAR(t50.FEC_COMUNIC_050, 'dd/mm/yyyy'),
    TO_CHAR(t50.FEC_COMUNIC_050, 'yyyy/mm/dd')
  FROM ab03b28t00 T28
  INNER JOIN ab03b50t00 t50
  ON t50.ID_028_HISTO_TRAM = T28.ID_028
  INNER JOIN ab03b49t00 t49
  ON t49.ID_049 = t50.ID_049_ORGAN
  INNER JOIN ab03b48t00 t48
  ON t48.ID_048 = t49.ID_048_ORG
  INNER JOIN ab03b18t00 t18
  ON t18.ID_017_FASE = T28.ID_017_FASE
  AND t18.ID_018     = T28.ID_018_TRAMITE;