-- 0  -- PRIME TOTAL PAR POLICE / PAR COLLEGE / PAR STATUT DES BENEFICIAIRES / BASE DE TARIFICATION
SELECT  
	C.idPolice,fn_libelle_policeID(C.idPolice) AS Police,
	C.id AS idCollege,C.primeFamille,C.codeBaseTarification,
	fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') AS nbpolice,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A') AS nbassure,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','C') AS nbconjoint,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','D') AS nbautreconjoint,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','E') AS nbenfant,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','F') AS nbgeniteur,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','O') AS nbautreparent,
	fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','X') AS nbenfantsup,
	CASE WHEN C.codeBaseTarification='IND' 
	THEN (fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') * C.primeFamille )
	ELSE (fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A')* C.primeFamille) END AS primeTotale
FROM college  C
WHERE fn_nb_beneficiaire_police2_date(C.idPolice,'2022-05-27')>0
AND C.primeFamille>0  -- AND C.idpolice=185
GROUP BY C.idPolice,C.id,C.primeFamille,C.codeBaseTarification



-- 1  -- PRIME TOTAL PAR POLICE / PAR COLLEGE / PAR STATUT DES BENEFICIAIRES
	SELECT  
		C.idPolice,fn_libelle_policeID(C.idPolice) AS Police,
		C.id AS idCollege,
		fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') AS nbpolice,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A') AS nbassure,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','C') AS nbconjoint,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','D') AS nbautreconjoint,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','E') AS nbenfant,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','F') AS nbgeniteur,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','O') AS nbautreparent,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','X') AS nbenfantsup,
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') * C.primeFamille )
		ELSE (fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A')* C.primeFamille) END AS primeTotale
	FROM college  C
	WHERE fn_nb_beneficiaire_police2_date(C.idPolice,'2022-05-27')>0
	AND C.primeFamille>0
	GROUP BY C.idPolice,C.id


-- 2 -- PRIME TOTAL PAR POLICE  / PAR STATUT DES BENEFICIAIRES
	SELECT  
		C.idPolice,fn_libelle_policeID(C.idPolice) AS Police,
		fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') AS nbpolice,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A') AS nbassure,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','C') AS nbconjoint,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','D') AS nbautreconjoint,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','E') AS nbenfant,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','F') AS nbgeniteur,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','O') AS nbautreparent,
		fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','X') AS nbenfantsup,
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_dateCol_rp(C.idPolice,C.id ,'2022-05-27') * C.primeFamille )
		ELSE (fn_nb_statut_police_dateCol_rp(C.idPolice,C.id ,'2022-05-27','A')* C.primeFamille) END AS primeTotale
	FROM college  C
	WHERE fn_nb_beneficiaire_police2_date(C.idPolice,'2022-05-27')>0
	AND C.primeFamille>0
	GROUP BY C.idPolice,C.id
	
-- 3 -- PRIME TOTAL PAR POLICE  / PAR STATUT DES BENEFICIAIRES
SELECT  
	C.idPolice,fn_libelle_policeID(C.idPolice) AS Police,
	CASE WHEN C.codeBaseTarification='IND' 
	THEN (fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice ,'2022-05-27') * C.primeFamille )
	ELSE (fn_nb_statut_police_SansCol_rp(C.idPolice,'2022-05-27','A')* C.primeFamille) END AS primeTotale
FROM college  C
WHERE fn_nb_beneficiaire_police2_date(C.idPolice,'2022-05-27')>0
AND C.primeFamille>0
GROUP BY C.idPolice,C.codeBaseTarification,C.primeFamille 

