-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- View: vw_ug_elementos
-- Version: 3.0.0
-- 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- DROP VIEW IF EXISTS vw_ug_elementos;
CREATE OR REPLACE VIEW vw_ug_elementos 
(
 unidad_gestion,
 elemento,
 ubicacion
 )
AS 
 SELECT 
    sga_ug_elementos.unidad_gestion, 
    sga_ug_elementos.elemento, 
    sga_ug_elementos.ubicacion
 FROM sga_ug_elementos

UNION

 SELECT 
    pr.unidad_gestion, 
    e.elemento, 
    pr.ubicacion
 FROM sga_elementos as e, 
      sga_elementos_plan as ep, 
      vw_ug_propuestas as pr, 
      sga_planes as p, 
      sga_planes_versiones as pv, 
      sga_elementos_revision as erv
 WHERE p.propuesta = pr.propuesta 
   AND p.estado IN ('V','A')
   AND pv.plan = p.plan 
   AND pv.estado IN ('V','A')
   AND ep.plan_version = pv.plan_version 
   AND erv.elemento_revision = ep.elemento_revision 
   AND e.elemento = erv.elemento

UNION

 SELECT 
    ura.unidad_gestion, 
    era.elemento, 
    cast(NULL as integer) as ubicacion
 FROM sga_elementos_ra as era, 
      sga_ug_responsables_academicas as ura
 WHERE era.responsable_academica = ura.responsable_academica
;

-- ++++++++++++++++++++++++++++++ Fin view vw_ug_elementos +++++++++++++++++++++++++++++++++++++++

GRANT SELECT ON vw_ug_elementos to public;