	DROP TEMPORARY TABLE IF EXISTS temp_facture_leger;
	 
	CREATE TEMPORARY TABLE temp_facture_leger
	( INDEX(idFacture) , INDEX(idBeneficiaire) , INDEX(codePrestataire))
	SELECT
		prt.id AS idFacture,
		prt.dateSysteme AS dateFacture,
		prt.codePrestataire,
		prt.idBeneficiaire,
		pm.numeroFeuilleMaladie,
		pm.numeroBonOrdonnance,
		fn_libelle_prestataire(pm.codePrestataire) AS Executant,
		fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		m.codeForme,
		m.codeMedicament,
		m.CIP1,
		-- m.codeForme,
		m.libelle,
		IFNULL(pm.fraisReel,0)AS montantReclame,IFNULL(pm.montantTm,0) AS TM,IFNULL(pm.montantArembourser,0) AS Arembourser 
	FROM p_prestationactes prt 
	INNER JOIN beneficiaire e ON (e.id=prt.idBeneficiaire)
	INNER JOIN adherent a ON ( a.id=e.idAdherent)
	INNER JOIN police C ON  (e.idPolice = C.id)
	INNER JOIN gc_assureur G ON  (C.codeGcAssureur = G.codeGcAssureur)	
	INNER JOIN p_prestationmedicaments pm ON (pm.idPrestationactes = prt.id)
	INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
	INNER JOIN  prestataire r ON (r.codePrestataire= prt.codePrestataire) 
	WHERE (LEFT(prt.dateSysteme,10)  BETWEEN '2022-10-01' AND '2022-10-31' )
	AND r.codeTypePrestataire='PHA' 
	AND G.id!='45' AND C.idClient!='49' 
	/*AND prt.codePrestataire IN ('PR1639','PR1640','PR0400','PR0466','PR0409','PR0328','PR0341',
	'PR0414','PR0249','PR1650','PR1652','PR0235','PR0455','PR0578','PR1655','PR0708','PR1657','PR0673',
	'PR1656','PR0842','PR1668','PR0290','PR1664','PR1665','PR0684','PR1666','PR0751','PR0719','PR0629',
	'PR0619','PR0729','PR1407','PR1679','PR0726','PR1680','PR0733','PR1685','PR1686')*/
	ORDER BY prt.id;
	
	
	-- SELECT * FROM prestataire WHERE codeTypePrestataire='PHA' AND libelle LIKE '%ROND%'
	
	DROP TEMPORARY TABLE IF EXISTS temp_facture_medu;	 
	CREATE TEMPORARY TABLE temp_facture_medu
	SELECT  
		-- A.numeroFeuilleMaladie,	A.numeroBonOrdonnance,SUM(IFNULL(A.Arembourser,0)) AS Total 
		A.codePrestataire,SUM(IFNULL(A.Arembourser,0)) AS Total 	
	FROM temp_facture_leger A
	-- GROUP BY A.numeroFeuilleMaladie,A.numeroBonOrdonnance
	GROUP BY A.codePrestataire
	HAVING (CASE WHEN 40000>0 THEN SUM(IFNULL(A.Arembourser,0))>40000 ELSE SUM(IFNULL(A.Arembourser,0))>40000 END )	;	
	
	-- 1  PAR PRESTATAIRES
	SELECT  -- B.garant,
		B.idPolice,-- B.numeroPolice,
		B.libellePolice,B.idCollege,-- B.codeProduit,
		-- A.idFacture,
		-- A.dateFacture,
		B.idAdherent, -- B.numeroAdherent AS NoAdherent,B.adherent,		
		B.idBeneficiaire,-- B.numeroBeneficiaire AS NoBeneficiaire,
		B.beneficiaire,	
		A.Executant,		
		A.Prescripteur,
		-- A.codeForme,
		-- A.codeMedicament,
		A.libelle,
		-- A.numeroFeuilleMaladie,A.numeroBonOrdonnance,
		-- A.codeAffection,A.affection, 
		A.montantReclame AS Reclame,
		A.TM,
		A.Arembourser
		,IFNULL(M.Total,0) AS Total 
	FROM temp_facture_leger A
	LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
	LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
	-- INNER JOIN temp_facture_medu M ON ((M.numeroFeuilleMaladie = A.numeroFeuilleMaladie) AND (M.numeroBonOrdonnance = A.numeroBonOrdonnance) )
	INNER JOIN temp_facture_medu M ON (M.codePrestataire = A.codePrestataire)
	WHERE A.Arembourser>0
	ORDER BY B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.Arembourser;
	
	-- 2  PAR PRESTATAIRES
	SELECT  A.codePrestataire,fn_libelle_prestataire(A.codePrestataire) AS Executant  ,
		SUM(A.montantReclame) AS Reclamer,
		SUM(A.Arembourser) AS Rembourser		
	FROM temp_facture_leger A
	LEFT JOIN prestataire PR ON (PR.codePrestataire = A.codePrestataire)
	LEFT JOIN vw_infos_contrat_assures B ON (B.idBeneficiaire = A.idBeneficiaire) 
	-- INNER JOIN temp_facture_medu M ON ((M.numeroFeuilleMaladie = A.numeroFeuilleMaladie) AND (M.numeroBonOrdonnance = A.numeroBonOrdonnance) )
	INNER JOIN temp_facture_medu M ON (M.codePrestataire = A.codePrestataire)
	-- WHERE A.Arembourser>0
	GROUP BY A.codePrestataire
	ORDER BY Rembourser DESC;
	
		
	DROP TEMPORARY TABLE  temp_facture_leger;
	DROP TEMPORARY TABLE  temp_facture_medu;