-- View: vu_sites_niv1_statut

DROP VIEW vu_sites_niv1_statut;

CREATE OR REPLACE VIEW vu_sites_niv1_statut AS */
 SELECT sites_carto.code_site,
	sites_carto.libl_site,
	sites_carto.code_ensemble,
	sites_carto.site_ln,
	sites_carto.ens,
	sites_carto.rnr,
	sites_carto.statut,
	sites_carto.etat_carto,
	sit_liste_statut.statut AS extension
   FROM ( SELECT sites_statut.code_site,
		sites_statut.libl_site,
		sites_statut.code_ensemble,
		sites_statut.site_ln,
		sites_statut.ens,
		sites_statut.rnr,
		sites_statut.ref_extension,
		sites_statut.statut,
		sit_liste_carto.etat_carto
           FROM ( SELECT sites_typo.code_site,
			sites_typo.libl_site,
			sites_typo.code_ensemble,
			sites_typo.site_ln,
			sites_typo.ens,
			sites_typo.rnr,
			sites_typo.ref_statut,
			sites_typo.ref_carto,
			sites_typo.ref_extension,
			sit_liste_statut.id_statut,
			sit_liste_statut.statut
                   FROM ( SELECT sites.code_site,
				sites.libl_site,
				sites.code_ensemble,
				sites.site_ln,
				sites.ens,
				sites.rnr,
				sites.ref_statut,
				sites.ref_carto,
				sites.ref_extension
                           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;

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;

