-- Depense genérale
SELECT  

 CONCAT (B.codeMois,'/',B.codeExercice) AS DatePaiement,
fn_codeNormalise_prestataire_rp(B.codePrestataire) AS Compte,
fn_libelle_prestataire(B.codePrestataire) AS Beneficiaire,
P.numeroPolice,P.libellePolice,ga.codeGarantie,ga.libelle,	
prt.codeActe AS codeActe, 
CASE WHEN  (fn_libelle_acte_rp (prt.codeActe) IS NULL OR fn_libelle_acte_rp (prt.codeActe)='') 
THEN 'CONSOMMABLES HOSPITALISATION' ELSE fn_libelle_acte_rp (prt.codeActe) END AS acte ,
IFNULL(prt.fraisReel,0) AS montantReclame,
IFNULL(prt.fraisRetenu,0) AS montantFacture,
IFNULL(prt.montantApayer,0) AS apresDecompte		
	
FROM p_prestationactes prt 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 gc_typegarant tg ON (G.typeGarant=tg.id) 
INNER JOIN adherent a ON ((P.id=a.idPolice) AND (P.codeSociete=a.codeSociete) AND ( a.id=B.idAdherent))
INNER JOIN p_feuillemaladie F ON (F.numeroFeuilleMaladie=B.numeroFeuilleMaladie)
INNER JOIN prestataire pr ON (B.codePrestataire=pr.codePrestataire)
INNER JOIN typeprestataire t ON (t.codeTypePrestataire=pr.codeTypePrestataire)
INNER JOIN  garantie ga ON (prt.CodeGarantie=ga.CodeGarantie) 
-- AND prt.CodeGarantie='HOS'
WHERE (LEFT(B.dateSysteme,10) BETWEEN '2022-03-01' AND '2022-03-31' )
-- AND G.codeGcAssureur='AMSA' 
AND G.id!='45'  AND (B.codeSociete="vitalis")  
AND prt.codeActe IN ('CS')  AND prt.fraisReel	>14000
AND tg.id='2'
AND B.codePrestataire NOT IN ('CE0416','PR1176','CE1215','CE1232','PR1175')
ORDER BY  B.codeMois DESC ,B.codeExercice DESC ,Compte


-- 32 578 790 