-- View: mf.vu_actes_par_site

-- DROP VIEW mf.vu_actes_par_site;

CREATE OR REPLACE VIEW mf.vu_histo_actes_par_site AS 

SELECT	join3.*,
		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 DISTINCT ON (mf.histo_parcelle.code_site, join2.code_acte)
						mf.histo_parcelle.code_site,
						join2.code_acte,
						join2.type_acte,
						join2.type_maitrise
		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_parcelle ON mf.histo_parcelle.code_acte::text = join2.code_acte::text) AS join3
JOIN mf.histo_acte ON mf.histo_acte.code_acte=join3.code_acte;

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

