SELECT c.idPolice,c.numeroPolice,fn_libelle_policeID( c.idPolice) AS Police ,c.id idCollege,c.libelleCollege
,l.codeLocalite AS codeLocalite,l.libelle AS localite,p.codeTypePrestataire,t.libelle AS typeprestataire,
p.codePrestataire AS CODE,p.libelle AS Prestataire
FROM beneficiaire b
INNER JOIN college c ON (c.id=b.idCollege)
INNER JOIN detailreseausoins d ON (d.codeReseau=c.codeReseau)
INNER JOIN prestataire p ON (d.codePrestataire=p.codePrestataire)
INNER JOIN typeprestataire t ON (t.codeTypePrestataire=p.codeTypePrestataire)
INNER JOIN ville v ON (v.codeVille=p.codeVille)
INNER JOIN localite l ON (l.codePays=p.codePays AND l.codeVille=p.codeVille AND l.codeLocalite=p.codeLocalite)
WHERE t.actif='1' AND p.actif='1'  -- AND t.codeTypePrestataire NOT IN ('MDR' ,'CME','PHA' ,'OPT','NCV' ,'FUN','SYS')
AND t.codeTypePrestataire IN ('PHA')
AND CASE WHEN l.codePays='CI' THEN  p.codePrestataire NOT IN ('CE1359','CE1276','CE0001','CE1338','CE1360') END
AND b.id=25007
-- AND idAdherent=9297
-- AND (CASE WHEN 9297 IS NULL THEN b.idAdherent IS NOT NULL ELSE b.idAdherent=9297 END)  

-- AND (CASE WHEN 25007 IS NULL THEN b.id IS NOT NULL ELSE b.id=25007 END)  

ORDER BY  c.idPolice,c.id ,codeLocalite,codeTypePrestataire,p.codePrestataire

-- select * from typeprestataire

-- POUR RECHERCHER LE RESEAU DE SOINS D'UN BENEFICIAIRES
CALL sp_rechercher_reseau_soins_lie(NULL,107,'00760015','0076001501')


-- RECHERCCHE RESEAU POUR LE STOUS LES PRESTATAIRES AGREES
CALL sp_rechercher_reseau_soins_liePHOPT(NULL,107,'00760015','0076001501')







