SELECT 
	pm.`codePrestatairePrescription` ComptePrescripteur,fn_libelle_prestataire(pm.codePrestatairePrescription) AS Prescripteur,
	pm.`codePrestataire` CompteExecutant,fn_libelle_prestataire(pm.codePrestataire) AS Executant,
	A.codeActe,A.datePrestation,
	a.id AS idAdherent,a.numeroAdherent,CONCAT(a.nom,' ',a.prenoms)  adherent,
	B.id AS idbenef,B.numeroBeneficiaire,CONCAT(B.nomBeneficiaire,' ',B.prenomsBeneficiaire)  Beneficiaire,
	m.`codeMedicament`,m.`CIP1`,m.`codeForme`,m.`libelle`,
	SUM(IFNULL(A.fraisReel,0)) AS montantReclame,SUM(IFNULL(A.montantApayer,0)) AS montantRembourse
FROM p_prestationactes A
INNER JOIN `p_prestationmedicaments` pm ON (pm.idPrestationactes = A.id)
INNER JOIN p_medicament m ON (pm.codeMedicament= m.codeMedicament)
INNER JOIN beneficiaire B ON ( B.id=A.idBeneficiaire )
INNER JOIN police Po ON (Po.id=B.idPolice)
INNER JOIN gc_assureur Gc ON  (Po.codeGcAssureur = Gc.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=A.numeroFeuilleMaladie)
INNER JOIN adherent a ON ((Po.id=a.idPolice) AND (Po.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN prestataire p  ON (A.codePrestataire=p.codePrestataire)
INNER JOIN entetecontrat C ON (C.idPolice=B.idPolice)
WHERE C.id = fn_id_entetecontrat_police_courant(C.idPolice)
AND  pm.codePrestataire='PR1286'
AND A.montantApayer>0  AND m.exclu=1
GROUP BY pm.codePrestatairePrescription,A.codeMedecin,A.codeActe,A.datePrestation,A.tiersPayant, F.codeAffection,
a.id,a.numeroAdherent, a.nom, a.prenoms,B.id,B.numeroBeneficiaire,B.nomBeneficiaire,B.prenomsBeneficiaire 
,m.`codeMedicament`,m.`CIP1`,m.`codeForme`,m.`libelle`
ORDER BY A.datePrestation,idAdherent,idbenef,montantRembourse DESC  ;

