SELECT P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,
a.id AS idAdherent,a.numeroAdherent AS numeroAdherent,CONCAT(a.nom ,' ',a.prenoms ) AS Adherent,
b.id,b.numeroBeneficiaire,CONCAT(b.nomBeneficiaire,' ',b.prenomsBeneficiaire) AS Beneficiaire,
f.id,f.idBaremePriseEncharge,f.codeGarantie,f.DelaiCarenceFamilleActe,f.tauxPlafond,f.ticketModerateur,f.codePeriodicite,f.forfaitPlafond
,SUM(IFNULL(prt.montantApayer,0))  AS Depense
,ABS(SUM(IFNULL(prt.montantApayer,0)) -f.forfaitPlafond )AS depassement    
FROM beneficiaire b 
INNER JOIN adherent a ON ((b.codeSociete=a.codeSociete) AND  (a.id=b.idAdherent) AND (a.idPolice=b.idPolice) AND (a.idCollege=b.idCollege ))
INNER JOIN college c ON (b.idCollege=c.id)
INNER JOIN garantiesbaremepriseencharge f ON (f.idBaremePriseEnCharge=c.idBaremePriseEnCharge)
INNER JOIN p_prestationactes prt ON (prt.idBeneficiaire=b.id)
INNER JOIN p_facture B ON (B.id=prt.idfacture)
INNER JOIN police P ON  (B.idPolice = P.id)
INNER JOIN clients cl ON (cl.id=P.idClient) AND cl.id<>'49'
INNER JOIN gc_assureur G ON  (P.codeGcAssureur = G.codeGcAssureur)
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-01-01' AND '2022-12-31' )
AND prt.CodeGarantie=f.codeGarantie AND f.forfaitPlafond NOT LIKE '%999'
AND G.id!='45'  AND (B.codeSociete="vitalis") AND prt.CodeGarantie='PHA'  AND prt.montantApayer>0
AND G.codeGcAssureur='GNA'  AND B.idPolice=82
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
GROUP BY P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,a.id,a.numeroAdherent,a.nom,a.prenoms,
b.id,b.numeroBeneficiaire,b.nomBeneficiaire,b.prenomsBeneficiaire,
f.id,f.idBaremePriseEncharge,f.codeGarantie,f.DelaiCarenceFamilleActe,f.tauxPlafond,f.ticketModerateur,f.forfaitPlafond,f.codePeriodicite
HAVING SUM(IFNULL(prt.montantApayer,0))>=140000 
-- AND (SUM(IFNULL(prt.montantApayer,0))-SUM(IFNULL(prt.montantRedresse,0)))>f.forfaitPlafond
ORDER BY P.codeGcAssureur,b.idPolice,b.idCollege,b.codeProduit,f.codeGarantie,a.id,Depense DESC