? (pas testé)
SELECT DISTINCT
'p' || i.no_individu as IDENTIFIANT,
concat('p', lpad(to_char(i.no_individu), 6, '0')) as UID_LDAP,
i.nom_patronymique as NOM,
i.nom_usuel as NOM_USUEL,
i.prenom as PRENOM,
i.c_civilite as CIVILITE,
'ECM' || lpad(to_char(i.no_individu), 6, '0') as CODE_BARRE,
'0340112M' as RNE_ETABLISSEMENT,
nvl((s.lc_structure),
(SELECT MIN(s2.c_structure)
FROM structure s2, affectation a2
WHERE (a2.no_dossier_pers = i.no_individu)
AND (a2.c_structure = s2.c_structure)
AND (a2.d_deb_affectation <= sysdate)
AND ((a.d_fin_affectation >= sysdate) OR (a.d_fin_affectation IS NULL))
)) AS CODE_COMPOSANTE,
nvl((s.lc_structure),
(SELECT MIN(s2.lc_structure)
FROM structure s2, affectation a2
WHERE (a2.no_dossier_pers = i.no_individu)
AND (a2.c_structure = s2.c_structure)
AND (a2.d_deb_affectation <= sysdate)
AND ((a.d_fin_affectation >= sysdate) OR (a.d_fin_affectation IS NULL))
)) AS COMPOSANTE,
(SELECT CODE_CORPS.*
FROM individu i2, (SELECT ec2.c_corps
FROM element_carriere ec2, carriere c2
WHERE (i2.no_individu = ec2.no_dossier_pers)
AND (ec2.no_dossier_pers = c2.no_dossier_pers)
AND (c2.d_deb_carriere <= sysdate)
AND ((ec2.d_fin_element >= sysdate) OR (ec2.d_fin_element IS NULL))
AND (ec2.d_effet_element <= sysdate)
AND ((c2.d_fin_carriere >= sysdate) OR (c2.d_fin_carriere IS NULL))
ORDER BY ec2.d_effet_element DESC )
WHERE (ROWNUM=1) AND (i2.no_individu = i.no_individu)) AS CODE_CORPS,
iem.no_e_mail as EMAIL
FROM individu i, affectation a, code_insee ci, chercheur c, structure s, personnel p, individu_e_mail iem, corps co, element_carriere ec, carriere c
WHERE
(i.no_individu = ci.no_dossier_pers(+))
AND (i.no_individu = a.no_dossier_pers(+))
AND (i.no_individu = iem.no_individu(+))
AND (i.no_individu = c.no_individu(+))
AND (i.no_individu = p.no_dossier_pers(+))
AND (p.c_structure = s.c_structure(+))
AND (i.no_individu = c.no_dossier_pers(+))
AND (c.no_dossier_pers = ec.no_dossier_pers(+))
AND (
(((a.d_deb_affectation <= sysdate) AND ((a.d_fin_affectation >= sysdate) OR (a.d_fin_affectation IS NULL))))
OR
(((c.d_deb_str_trav <= sysdate) AND ((c.d_fin_str_trav >= sysdate) OR (c.d_fin_str_trav IS NULL))))
)