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,
		B.idBeneficiaire,
		pm.numeroFeuilleMaladie,
		pm.numeroBonOrdonnance,
		f.codeAffection,af.libelle AS affection, 
		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 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 (B.codeTypeFacture="PHAR")
	AND (CASE WHEN 168 IS NULL THEN B.idPolice IS NOT NULL ELSE B.idPolice=168 END)  
	AND G.id!='45' AND C.idClient!='49' 
	AND B.valide=1 AND B.okDecompte=1
	-- AND pm.codePrestataire IN ('PR0544') 
	AND pm.codePrestatairePrescription 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 B.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 	
	FROM temp_facture_leger A
	GROUP BY A.numeroFeuilleMaladie,A.numeroBonOrdonnance
	HAVING (CASE WHEN 0>0 THEN SUM(IFNULL(A.Arembourser,0))>0 ELSE SUM(IFNULL(A.Arembourser,0))>0 END )	;	
	
 
	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) )
	WHERE A.Arembourser>0
	ORDER BY B.idPolice,B.idCollege,B.idAdherent,B.idBeneficiaire,A.Arembourser;
	
	DROP TEMPORARY TABLE  temp_facture_leger;
	DROP TEMPORARY TABLE  temp_facture_medu;