CALL sp_s_rapportsp_CompagnieNews_rp('GNA',NULL,NULL,NULL,'2023-01-01','2023-12-31',"QTM")




CALL sp_s_rapportsp_CompagnieNewsPrecedent_rp('GNA',NULL,NULL,NULL,'2022-01-01','2022-12-31',"QTM")




SELECT g.codeGcAssureur,p.codeApporteur,a.`libelle` AS apporteur,p.id,p.numeroPolice,p.libellePolice,c.dateEffet,c.dateFin AS dateEcheance,' ' dateEmission,0 primeNette
,p.tauxCommissionGerant
FROM  gc_assureur g
INNER JOIN police p ON (g.codeGcAssureur=p.CodeGcAssureur)
INNER JOIN `apporteur` a ON (a.codeApporteur=p.codeApporteur)
INNER JOIN entetecontrat c ON (p.id=c.idPolice)
WHERE g.typeGarant=1
AND p.codeEtatPolice IN ('EC','SU')
AND c.`exercice`=2023 AND c.enCours=1  
AND p.CodeGcAssureur='GNA'
AND c.id=fn_id_entetecontrat_police_en_cours(c.idPolice)
AND p.id NOT IN ( SELECT e.idPolice 
		  FROM emission e 
		  INNER JOIN entetecontrat c ON (c.idPolice=e.idPolice)
		  WHERE  e.codeTypeAvenant="QTM"
		  AND c.`exercice`=2023 AND c.enCours=1 AND c.dateEffet=e.dateEffet
		  AND p.CodeGcAssureur='GNA'
		  AND c.id=fn_id_entetecontrat_police_en_cours(c.idPolice)
		  )
ORDER BY g.codeGcAssureur,p.codeApporteur DESC

