SELECT p.codeGcAssureur,fn_libelle_garant_rp(p.codeGcAssureur) AS Garant,
p.codeApporteur,-- t.libelle AS CategorieContrat,
idPolice,
Police,BaseTarification,decompte,Prime,
SUM(primeTotale) AS PrimeTotale, (SUM(primeTotale)*0.1)  AS Commission  FROM (
	SELECT  
		C.codeGcAssureur,C.idPolice,fn_libelle_policeID(C.idPolice) AS Police,
		C.codeBaseTarification AS BaseTarification,C.primeFamille AS Prime,
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice ,'2021-12-31'))
		ELSE (fn_nb_statut_police_SansCol_rp(C.idPolice,'2021-12-31','A')) END AS decompte,
		CASE WHEN C.codeBaseTarification='IND' 
		THEN (fn_nb_beneficiaire_police2_SansCol_rp(C.idPolice ,'2021-12-31') * C.primeFamille )
		ELSE (fn_nb_statut_police_SansCol_rp(C.idPolice,'2021-12-31','A')* C.primeFamille) END AS primeTotale
	FROM college  C
	WHERE fn_nb_beneficiaire_police2_date(C.idPolice,'2021-12-31')>0
	AND C.primeFamille>0 -- and codeGcAssureur='SIDAM'  --  AND C.idPolice=115
	GROUP BY C.codeGcAssureur,C.idPolice ,C.codeBaseTarification,C.primeFamille 
) prime 
INNER JOIN police p ON (idPolice=p.id) 
-- INNER JOIN typecontrat t ON (t.codeTypeContrat=p.codeTypeContrat)
-- INNER JOIN apporteur a ON (a.codeApporteur=p.codeApporteur)
WHERE p.codeGcAssureur IN ('VEC','VS')
GROUP BY p.codeGcAssureur,idPolice,Police,p.codeApporteur-- ,t.libelle
,p.numeroPoliceGarant,Prime,BaseTarification,decompte
ORDER BY codeGcAssureur,codeApporteur-- ,t.libelle
,idPolice
