-- 1 Recherche informations de modification le bareme/acte
SELECT p.codeGcAssureur,p.id AS idPolice,p.libellePolice,p.codeEtatPolice,c.id AS idCollege,
	c.libelleCollege,a.codeGarantie,a.codeFamilleActe,a.codeActe
	,a.libelle,a.tauxPlafond,a.forfaitPlafond,a.codePeriodicite
FROM `actesbaremepriseencharge` a
INNER JOIN baremepriseencharge b ON (a.idBaremepriseencharge=b.id)
INNER JOIN college c ON (c.codeProduit=a.codeProduit)
INNER JOIN police p ON (p.id=c.idPolice)
-- AND a.codeGarantie='PHA' 
AND idPolice!=62
AND a.codeGarantie='HOS'
AND a.forfaitPlafond!=9999999999
ORDER BY codeGcAssureur,idPolice,idCollege,a.codeGarantie;  

-- 2 Recherche informations de modification les garanties
SELECT p.codeGcAssureur,p.id AS idPolice,p.libellePolice,p.codeEtatPolice,c.id AS idCollege,
	c.libelleCollege,a.codeGarantie,`fn_libelle_garantie`(a.codeGarantie) AS libelle,a.tauxPlafond,a.forfaitPlafond,a.codePeriodicite
FROM `garantiesbaremepriseencharge` a
INNER JOIN baremepriseencharge b ON (a.idBaremepriseencharge=b.id)
INNER JOIN college c ON (c.codeProduit=b.codeProduit)
INNER JOIN police p ON (p.id=c.idPolice)
-- AND a.codeGarantie='PHA' 
AND idPolice!=62
AND a.codeGarantie='HOS'
AND a.forfaitPlafond=9999999999
ORDER BY codeGcAssureur,idPolice,idCollege,a.codeGarantie;  


-- 3 Recherche informations de garantir/ Famille actes
SELECT p.codeGcAssureur,p.id AS idPolice,p.libellePolice,p.codeEtatPolice,c.id AS idCollege,
	c.libelleCollege,a.codeFamilleActe,`fn_libelle_familleacte`(a.codeFamilleActe) AS Famille,a.tauxPlafond,a.forfaitPlafond,a.codePeriodicite
FROM `familleactebaremepriseencharge` a
INNER JOIN baremepriseencharge b ON (a.idBaremepriseencharge=b.id)
INNER JOIN college c ON (c.codeProduit=b.codeProduit)
INNER JOIN police p ON (p.id=c.idPolice)
AND idPolice!=62
AND a.codeGarantie='HOS'
AND a.forfaitPlafond!=9999999999
ORDER BY codeGcAssureur,idPolice,idCollege,a.codeGarantie;  

