DROP TEMPORARY TABLE IF EXISTS temp_prime;
CREATE TEMPORARY TABLE temp_prime

SELECT -- o.*
 g.codeGcAssureur,p.codeApporteur,a.`libelle` AS apporteur,p.id AS idPolice,o.id AS idCollege,p.numeroPolice,p.libellePolice,c.dateEffet,c.dateFin AS dateEcheance,o.codeBaseTarification
 ,r.codeLienParente,r.prime AS primeNette
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)
INNER JOIN college o ON (o.idPolice=p.id)
LEFT JOIN primecollege r ON ((r.idPolice=p.id) AND (r.idCollege=o.id)) -- AND (r.codeLienParente=A.codeLienParente)  
WHERE c.id=fn_id_entetecontrat_police_en_cours(c.idPolice) 
AND p.codeEtatPolice IN ('EC','SU')
AND c.`exercice`=2023 AND c.enCours=1  
-- AND p.id=333
ORDER BY g.codeGcAssureur,p.id,o.id;

DROP TEMPORARY TABLE IF EXISTS temp_primeNEWS;
CREATE TEMPORARY TABLE temp_primeNEWS
SELECT DISTINCT p.* -- ,r.codeLienParente,r.prime AS primeNette
FROM temp_prime p
INNER JOIN primecollege r ON ((r.idPolice=p.idPolice) AND (r.idCollege=p.idCollege))
WHERE  p.idPolice IN (333,229,364)
-- AND CASE WHEN 
-- AND CASE WHEN p.codeBaseTarification='FAM' THEN p.codeLienParente='A'  ELSE p.codeLienParente IN ('A','C','E','O','F','D') END
AND (CASE WHEN (p.codeBaseTarification='FAM' AND p.codeLienParente='A') THEN r.prime=0 
	-- WHEN (p.codeBaseTarification='IND' AND p.codeLienParente IN ('A','C','E','O','F','D'))  THEN r.prime=0 END)
	  ELSE p.codeLienParente IN ('A','C','E','O','F','D') END)
-- AND r.prime=0
ORDER BY p.codeGcAssureur,p.idPolice,p.idCollege,r.codeLienParente ;



DROP TEMPORARY TABLE IF EXISTS temp_primeNEWS;
DROP TEMPORARY TABLE IF EXISTS temp_prime;


