CREATE MATERIALIZED VIEW ZAGS_DATA1.V_PEOPLE_ZAGS (GUID,ID,SURNAME,NAME,PATRONYMIC,ID_PEOPLE_INFO,ID_GENDER
		,DAY_BIRTH,MONTH_BIRTH,YEAR_BIRTH,ID_LOCATION_BORN,ID_LOCATION,ID_TYPE_IDENT_DOC,SERIES_IDENT_DOC,NUMB_IDENT_DOC)
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS 

SELECT people.guid, people.ID, people.SURNAME, people.NAME, people.PATRONYMIC, people.ID_PEOPLE_INFO,
       people.ID_GENDER, people.DAY_BIRTH, people.MONTH_BIRTH, people.YEAR_BIRTH, people.ID_LOCATION AS id_location_born,
       ppl.ID_LOCATION, pdoc.ID_TYPE_IDENT_DOC, pdoc.SERIES_IDENT_DOC, pdoc.NUMB_IDENT_DOC
FROM 
(
	SELECT people_all.guid, people_all.ID, people_all.SURNAME, people_all.NAME, people_all.PATRONYMIC,
			people_all.ID_PEOPLE_INFO, people_all.ID_GENDER, people_all.DAY_BIRTH, people_all.MONTH_BIRTH,
			people_all.YEAR_BIRTH, people_all.ID_LOCATION
	FROM 
	(
		SELECT lpp.guid, PP.ID, PP.SURNAME, PP.NAME, PP.PATRONYMIC, PP.ID_PEOPLE_INFO, PP.ID_ROLES_PEOPLE,
				PP.DATE_REC, RP.FLAG_PROTAGONIST, PP.ID_GENDER, PI.DAY_BIRTH, PI.MONTH_BIRTH, PI.YEAR_BIRTH, PL.ID_LOCATION,
				COUNT (pp.id) OVER (PARTITION BY lpp.guid) AS count_people,
				MAX (pp.id) OVER (PARTITION BY lpp.guid, RP.FLAG_PROTAGONIST) AS max_id_pro,
				COUNT (DISTINCT RP.FLAG_PROTAGONIST) OVER (PARTITION BY lpp.guid) AS cound_distinct_pro
		FROM t_people pp
		INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = LPp.ID_PEOPLE
		LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
		LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 1
		LEFT JOIN SP_ROLES_PEOPLE rp ON rp.id = PP.ID_ROLES_PEOPLE
		INNER JOIN 
			(
				SELECT LP.GUID, MAX(TRUNC (p.date_az)) mday
				FROM LNK_PEOPLES_GUID lp
				INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
				GROUP BY LP.GUID
			) max_day
			ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
	) people_all
	WHERE cound_distinct_pro <= 1 AND people_all.id = people_all.max_id_pro
	
	UNION

	SELECT people_all.guid, people_all.ID, people_all.SURNAME, people_all.NAME, people_all.PATRONYMIC, people_all.ID_PEOPLE_INFO, people_all.ID_GENDER,
			people_all.DAY_BIRTH, people_all.MONTH_BIRTH, people_all.YEAR_BIRTH, people_all.ID_LOCATION
	FROM 
	(
		SELECT lpp.guid, PP.ID, PP.SURNAME, PP.NAME, PP.PATRONYMIC, PP.ID_PEOPLE_INFO, PP.ID_ROLES_PEOPLE,
				PP.DATE_REC, RP.FLAG_PROTAGONIST, PP.ID_GENDER, PI.DAY_BIRTH, PI.MONTH_BIRTH, PI.YEAR_BIRTH, PL.ID_LOCATION,
				COUNT (pp.id) OVER (PARTITION BY lpp.guid) AS count_people,
				MAX (pp.id) OVER (PARTITION BY lpp.guid, RP.FLAG_PROTAGONIST) AS max_id_pro,
				COUNT (DISTINCT RP.FLAG_PROTAGONIST) OVER (PARTITION BY lpp.guid) AS cound_distinct_pro
		FROM t_people pp
		INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = LPp.ID_PEOPLE
		LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
		LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 1
		LEFT JOIN SP_ROLES_PEOPLE rp ON rp.id = PP.ID_ROLES_PEOPLE
		INNER JOIN 
			(
				SELECT LP.GUID, MAX(TRUNC (p.date_az)) mday
				FROM LNK_PEOPLES_GUID lp
				INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
				GROUP BY LP.GUID
			) max_day
			ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
	) people_all
	WHERE cound_distinct_pro > 1
		AND people_all.id = people_all.max_id_pro
		AND people_all.FLAG_PROTAGONIST = 1
) people
LEFT JOIN 
(
	SELECT lpp.guid, MAX (pl.id_location) AS id_location
	FROM t_people pp
	INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = lpp.ID_PEOPLE
	INNER JOIN 
	(
		SELECT LP.GUID, MAX (TRUNC (p.date_az)) mday
		FROM LNK_PEOPLES_GUID lp
		INNER JOIN t_people p ON LP.ID_PEOPLE = P.ID_UNIQ AND P.FLAG_VALID = 1
		GROUP BY LP.GUID
	) max_day
	ON PP.DATE_AZ >= max_day.mday AND LPP.GUID = max_day.guid
	LEFT JOIN LNK_PEOPLES_LOCATION pl ON PL.ID_PEOPLE = PP.ID AND PL.ID_TYPE_LOCATION = 2
	GROUP BY lpp.guid
) ppl ON ppl.guid = people.guid
LEFT JOIN 
(
	SELECT lpp.guid, MAX (pd.ID) AS id_doc
	FROM t_people pp
	INNER JOIN LNK_PEOPLES_GUID lpp ON PP.ID_UNIQ = lpp.ID_PEOPLE
	LEFT JOIN t_people_info pi ON PP.ID_PEOPLE_INFO = pi.id
	LEFT JOIN T_PEOPLE_DOC pd ON pi.ID_DOC_PASSPORT = pd.ID
	WHERE ID_TYPE_IDENT_DOC IS NOT NULL
		AND SERIES_IDENT_DOC IS NOT NULL
		AND NUMB_IDENT_DOC IS NOT NULL
	GROUP BY lpp.guid
) ppd ON ppd.guid = people.guid
LEFT JOIN T_PEOPLE_DOC pdoc ON ppd.id_doc = pdoc.ID
;