-- View: mf.vu_actes_par_site

-- DROP VIEW mf.vu_actes_par_site_sans_parcelle;

CREATE OR REPLACE VIEW mf.vu_actes_par_site_sans_parcelle AS 

SELECT	join2.*,
		mf.histo_acte.date_acte,
		mf.histo_acte.nom_notaire,
		mf.histo_acte.prix_acquisition,
		mf.histo_acte.origine_propriete,
		mf.histo_acte.duree_conv,
		mf.histo_acte.echeance_prevue,
		mf.histo_acte.code_acte_histo,
		mf.histo_acte.ref_code_site
		
FROM (SELECT	join1.code_acte,
					join1.type_acte,
					mf.type_maitrise.type_maitrise
			FROM	(SELECT mf.histo_acte.code_acte,
						mf.type_acte.type_acte,
						mf.histo_acte.ref_type_maitrise
					FROM mf.histo_acte
					LEFT JOIN mf.type_acte ON mf.histo_acte.ref_type_acte=mf.type_acte.id_type_acte) AS join1
			LEFT JOIN mf.type_maitrise ON join1.ref_type_maitrise=mf.type_maitrise.id_type_maitrise) AS join2
LEFT JOIN mf.histo_acte ON mf.histo_acte.code_acte=join2.code_acte
WHERE mf.histo_acte.ref_code_site<>'';

ALTER TABLE mf.vu_actes_par_site_sans_parcelle OWNER TO ludodo;
GRANT ALL ON TABLE mf.vu_actes_par_site_sans_parcelle TO geomatique;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE mf.vu_actes_par_site_sans_parcelle TO public;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE mf.vu_actes_par_site_sans_parcelle TO cpnrc;

