DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;
 
CREATE TEMPORARY TABLE temp_beneficiaire_l
SELECT A.id AS idBeneficiaire, A.dateSortie
FROM beneficiaire A 
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)	
WHERE (C.codeGcAssureur='FIDRA')
AND (CASE WHEN '2023-02-15' IS NULL THEN A.dateEffet IS NOT NULL ELSE A.dateEffet <= '2023-02-15' END); 

SELECT	DISTINCT 
	CL.libelleCollege AS Categorie,
	A.numeroBeneficiaire AS NoBeneficiaire,A.id AS idBeneficiaire,A.numeroAdherent AS NoFamille,
	A.nomBeneficiaire AS Nom, A.prenomsBeneficiaire AS Prenoms,
	A.codeLienParente,N.libelle AS naturepiece,A.numeroPiece AS NoPiece,
	A.sexe,A.codeGroupeSanguin,	
	DATE_FORMAT(A.dateNaissance, '%d/%m/%Y') AS dateNaissance,
	B.telephonePortable AS telAdherent, 
	CASE WHEN (A.numeroSociete IS NULL OR A.numeroSociete=(NULL)) THEN A.numeroMatricule ELSE  A.numeroSociete END AS MlleEmploye
FROM beneficiaire A 
JOIN temp_beneficiaire_l L ON (L.idBeneficiaire=A.id)
JOIN adherent B ON (B.id=A.idAdherent)
JOIN police C ON (C.id=A.idPolice)	
LEFT JOIN produit P ON (P.codeSociete=A.codeSociete AND P.codeProduit=A.codeProduit AND P.codeGcAssureur = C.codeGcAssureur)
LEFT JOIN naturepiece N ON (N.codeSociete=A.codeSociete AND N.codeNaturePiece=A.codeNaturePiece)
LEFT JOIN motifsortie M ON (M.codeMotifSortie=A.codeMotifSortie)
LEFT JOIN etatpersonne H ON (H.codeEtatPersonne=A.codeEtatBeneficiaire)
LEFT JOIN college CL ON (CL.id=A.idCollege AND CL.codeGcAssureur = C.codeGcAssureur)
LEFT JOIN avenant AV ON (AV.id=A.idAvenant)
WHERE (A.idPolice>0)
AND A.codeLienParente='A'
AND (A.nomBeneficiaire NOT LIKE "%AVENANT%")
AND (C.codeGcAssureur='FIDRA')
ORDER BY CL.libelleCollege,A.numeroAdherent,A.codeLienParente;

DROP TEMPORARY TABLE IF EXISTS temp_beneficiaire_l;