	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
	 
	CREATE TEMPORARY TABLE temp_facture_leger
	( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
	SELECT
		B.id AS idFacture,
		B.dateSysteme AS dateComptable,G.codeGcAssureur,B.idPolice,
		prt.codePrestataire,
		fn_libelle_prestataire(prt.codePrestataire) AS Prestataire,
		prt.codeGarantie,
		B.idBeneficiaire,
		prt.numeroFeuilleMaladie,		
		IFNULL(prt.fraisReel,0)AS montantReclame,IFNULL(prt.montantApayer,0) AS Arembourser 
	FROM p_prestationactes prt JOIN p_facture B ON (B.id=prt.idfacture)
	INNER JOIN  police C ON  (B.idPolice = C.id)
	INNER JOIN  gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)
	INNER JOIN adherent a ON ((C.id=a.idPolice) AND (C.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
	INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
	WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-01-01' AND '2022-31-12' )
	AND G.id!='45' AND C.idClient!='49'  AND G.codeGcAssureur IN ('VEC','VS')
	AND prt.codeGarantie IN ('BIO','IMA','PHA')
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276') 
	ORDER BY B.id;
	
		
	DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
	CREATE TEMPORARY TABLE temp_facture_medu
	SELECT  
		A.idPolice,A.codeGarantie,A.idBeneficiaire,SUM(IFNULL(A.Arembourser,0)) AS Total 	
	FROM temp_facture_leger A
	GROUP BY A.idPolice,A.codeGarantie,A.idBeneficiaire
	HAVING (CASE WHEN 200000>0 THEN SUM(IFNULL(A.Arembourser,0))>200000 ELSE SUM(IFNULL(A.Arembourser,0))>0 END )	;	
	
 

	SELECT 	
		A.codeGcAssureur,A.idPolice,fn_libelle_policeID(A.idPolice) AS Police,	
		A.codeGarantie AS codeGarantie,	
		fn_libelle_garantie (A.codeGarantie) AS Garantie,	
		B.numeroAdherent AS Adhérent,
		B.numeroBeneficiaire AS Bénéficiaire,
		B.beneficiaire AS Bénéficiaire,
		SUM(IFNULL(A.montantReclame,0)) AS montantReclame,
		SUM(IFNULL(A.Arembourser,0)) AS Arembourser
	FROM temp_facture_leger A
	LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
	INNER JOIN temp_facture_medu M ON ((M.idPolice = A.idPolice) AND (M.codeGarantie = A.codeGarantie)AND (M.idBeneficiaire = A.idBeneficiaire))
	WHERE A.Arembourser>0
	GROUP BY A.codeGcAssureur,A.idPolice,A.codeGarantie,B.numeroAdherent,B.numeroBeneficiaire,B.beneficiaire
	ORDER BY A.codeGcAssureur,A.idPolice,A.codeGarantie,B.idAdherent,B.idBeneficiaire,A.Arembourser;
	
	DROP TEMPORARY TABLE  temp_facture_leger;
	DROP TEMPORARY TABLE  temp_facture_medu;