	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 dateFacture,
		B.idPolice,C.numeroPolice,
		`fn_libelle_policeID`(B.idPolice) AS Police,
		B.codePrestataire,
		prt.codeGarantie,
		B.idBeneficiaire,prt.codeActe,prt.fraisReel,
		CASE WHEN (prt.codeActe='C' AND prt.fraisReel>8000) THEN '1'
		     WHEN (prt.codeActe='CS' AND prt.fraisReel>10000) THEN '1'
		     ELSE '0' END AS fraisReelPlus,
		/*CASE WHEN (prt.codeActe='C' AND prt.fraisReel>8000) THEN IFNULL(prt.montantApayer,0)-(8000-ROUND((8000*prt.tm)/100,0))
		     WHEN (prt.codeActe='CS' AND prt.fraisReel>10000) THEN IFNULL(prt.montantApayer,0)-(10000-ROUND((10000*prt.tm)/100,0)) 
		     ELSE IFNULL(prt.montantApayer,0) END AS Arembourser*/ -- MONTANT DE PLUS
		CASE WHEN (prt.codeActe='C' AND prt.fraisReel>8000) THEN (8000-ROUND((8000*prt.tm)/100,0))
	     WHEN (prt.codeActe='CS' AND prt.fraisReel>10000) THEN (10000-ROUND((10000*prt.tm)/100,0)) 
	     ELSE IFNULL(prt.montantApayer,0) END AS Arembourser -- NORMAL NORMAL QU4ON AURRAI DU RECLAMER
		  --  IFNULL(prt.montantApayer,0) AS Arembourser  -- MONTANT REBOURSER ET RECLAMER
	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)
	INNER JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie =B.numeroFeuilleMaladie )
	WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2023-01-01' AND '2023-03-31' )
	AND (CASE WHEN 'FIDRA' IS NULL THEN G.codeGcAssureur IS NOT NULL ELSE G.codeGcAssureur='FIDRA' END)  
	AND G.id!='45' AND C.idClient!='49' 
	AND B.valide=1 AND B.okDecompte=1 AND prt.ententePrealable NOT IN ('2','9')  --  AND prt.codeActe='C' AND prt.fraisReel>8000
	AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','CE1287','PR1175','CE1276')
	-- GROUP BY B.id,B.dateSysteme,B.idPolice,C.numeroPolice, B.codePrestataire,prt.codeGarantie,B.idBeneficiaire,prt.codeActe,prt.fraisReel
	ORDER BY idFacture;
	
	-- SELECT * FROM temp_facture_leger
 
	SELECT type_prestataire,codePrestataire,Prestataire,SUM(JANVIER) AS JANVIER,SUM(FEVRIER) AS FEVRIER 
	,SUM(MARS) AS MARS,SUM(AVRIL) AS AVRIL ,SUM(MAI) AS MAI ,SUM(JUIN) AS JUIN
	 ,SUM(JUILLET) AS JUILLET ,SUM(AOUT) AS AOUT ,SUM(SEPTEMBRE) AS SEPTEMBRE ,SUM(OCTOBRE) AS OCTOBRE
	 ,SUM(NOVEMBRE) AS NOVEMBRE,SUM(DECEMBRE) AS DECEMBRE,
	(SUM(JANVIER)+SUM(FEVRIER)+SUM(MARS)+SUM(AVRIL)+SUM(MAI)+SUM(JUIN)+SUM(JUILLET)+SUM(AOUT) 
	+SUM(SEPTEMBRE)+SUM(OCTOBRE)+SUM(NOVEMBRE)+SUM(DECEMBRE)) AS TOTAUX
	 FROM (
		SELECT 
			`fn_type_prestataireNEWS`(PR.codePrestataire) AS type_prestataire,PR.codePrestataire,PR.libelle AS Prestataire,	
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='1' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'JANVIER',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='2' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'FEVRIER',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='3' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'MARS',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='4' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'AVRIL',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='5' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'MAI',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='6' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'JUIN',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='7' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'JUILLET',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='8' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'AOUT',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='9' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'SEPTEMBRE',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='10' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'OCTOBRE',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='11' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'NOVEMBRE',
			CASE WHEN MONTH(LEFT(A.dateFacture,10))='12' THEN COALESCE(SUM(A.Arembourser),0) ELSE '0' END 'DECEMBRE'
		FROM temp_facture_leger A
		INNER JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
		-- WHERE A.fraisReelPlus=1
		-- INNER JOIN typeprestataire TP ON (PR.codeTypePrestataire = TP.codeTypePrestataire)
		GROUP BY type_prestataire,PR.codePrestataire,PR.libelle,A.dateFacture
	) depense  GROUP BY type_prestataire,codePrestataire,Prestataire HAVING TOTAUX>0 ORDER BY type_prestataire,TOTAUX DESC;
	
	DROP TEMPORARY TABLE  temp_facture_leger;
	