-- View: vu_sites_niv1_statut

DROP VIEW vu_sites_niv1_statut;

CREATE OR REPLACE VIEW vu_sites_niv1_statut AS
SELECT	sites.code_site,
	sites.libl_site,
	sites.code_ensemble,
	sites_milieu.statut,
	sites_milieu.etat_carto,
	sites_milieu.extension,
	sites.site_ln,
	sites.ens,
	sites.rnr,
	sites_milieu.milieu
	
FROM(	SELECT  sites_extens.code_site,
		sites_extens.statut,
		sites_extens.etat_carto,
		sites_extens.extension,
		sit_typo_milieu_fede.milieu
	FROM(	SELECT sites_carto.code_site,
			sites_carto.statut,
			sites_carto.etat_carto,
			sit_liste_statut.statut AS extension,
			sites_carto.ref_typmilieu_fede
		FROM ( SELECT	sites_statut.code_site,
				sites_statut.ref_extension,
				sites_statut.ref_typmilieu_fede,
				sites_statut.statut,
				sit_liste_carto.etat_carto
		       FROM ( SELECT	sites_typo.code_site,
					sites_typo.ref_carto,
					sites_typo.ref_extension,
					sites_typo.ref_typmilieu_fede,
					sit_liste_statut.statut
			      FROM ( SELECT	sites.code_site,
						sites.ref_statut,
						sites.ref_carto,
						sites.ref_extension,
						sites.ref_typmilieu_fede
				      FROM sites
				      LEFT JOIN sit_typologie ON sites.ref_typ_spe = sit_typologie.id_site_spe_typ
				      WHERE sites.ref_typ_spe = 5) sites_typo
			      LEFT JOIN sit_liste_statut ON sites_typo.ref_statut = sit_liste_statut.id_statut) sites_statut
		      LEFT JOIN sit_liste_carto ON sites_statut.ref_carto = sit_liste_carto.id_carto) sites_carto
		LEFT JOIN sit_liste_statut ON sites_carto.ref_extension = sit_liste_statut.id_statut) sites_extens
	LEFT JOIN sit_typo_milieu_fede ON sites_extens.ref_typmilieu_fede = sit_typo_milieu_fede.id_typmilieu_fede) sites_milieu
LEFT JOIN sites ON sites_milieu.code_site=sites.code_site;

ALTER TABLE vu_sites_niv1_statut OWNER TO ludodo;
GRANT ALL ON TABLE vu_sites_niv1_statut TO ludodo, geomatique;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE vu_sites_niv1_statut TO cpnrc;

