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.codePrestataire,
		fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
		fn_libelle_prestataire(B.codePrestataire) AS Exécutant,
		B.idAdherent,B.idBeneficiaire,
		pm.numeroFeuilleMaladie,
		pm.numeroBonOrdonnance,
		f.codeAffection,af.libelle AS affection, 		
		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 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_prestationmedicaments pm ON (pm.idPrestationactes = prt.id)
	INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
	LEFT JOIN p_feuillemaladie f ON (f.numeroFeuilleMaladie =B.numeroFeuilleMaladie )
	LEFT JOIN `p_affection` af ON (f.codeAffection =af.codeAffection )
	WHERE (LEFT(B.dateSysteme,10)  BETWEEN '2022-01-01' AND '2022-12-31' )
	AND (CASE WHEN '82' IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice='82' END)  
	AND G.id!='45' AND C.idClient!='49' 
	AND B.valide=1 AND B.okDecompte=1
	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.idAdherent,A.idBeneficiaire,SUM(IFNULL(A.Arembourser,0)) AS Total 	
	FROM temp_facture_leger A
	GROUP BY A.idAdherent,A.idBeneficiaire
	HAVING (CASE WHEN 200000>0 THEN SUM(IFNULL(A.Arembourser,0))>=200000 ELSE SUM(IFNULL(A.Arembourser,0))>0 END )	;	
	
	-- 1 DETAIL
	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.Prescripteur,
		A.Exécutant,
		A.codeForme,
		A.codeMedicament,
		A.libelle,
		A.numeroFeuilleMaladie,A.numeroBonOrdonnance,A.codeAffection, -- A.affection, 
		A.montantReclame,
		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.idBeneficiaire = A.idBeneficiaire))
	WHERE A.Arembourser>0
	ORDER BY B.garant,B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.numeroFeuilleMaladie,A.Arembourser;
	
	SELECT * FROM temp_facture_medu
	
	-- 2 BENEEFICIAIRE
	SELECT  DISTINCT B.idPolice,B.numeroPolice,B.libellePolice,B.idCollege,
		B.idAdherent,B.numeroAdherent AS NoAdherent,B.adherent,		
		B.idBeneficiaire,B.numeroBeneficiaire AS NoBeneficiaire,B.beneficiaire,	
		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.idBeneficiaire = A.idBeneficiaire))
	WHERE A.Arembourser>0

	
	
	DROP TEMPORARY TABLE  temp_facture_leger;
	DROP TEMPORARY TABLE  temp_facture_medu;