Juste parce que j'en suis fier (je crois que c'est la requête la plus complexe que j'ai eu à écrire... mais bon, il y a très probablement de nombreuses optimisation à faire - en particulier au niveau des BETWEEN ou des NVL à développer ou pas suivant les cas) :
SQL final
SELECT DISTINCT
i.no_individu,
'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 (i.no_individu = a2.no_dossier_pers)
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 (i.no_individu = a2.no_dossier_pers)
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 c_corps
FROM (SELECT ec2.c_corps, ec2.no_dossier_pers
FROM element_carriere ec2, carriere c2, corps co2
WHERE (c2.no_dossier_pers = ec2.no_dossier_pers(+))
AND (c2.d_deb_carriere <= sysdate)
AND (ec2.c_corps = co2.c_corps)
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 (no_dossier_pers = i.no_individu) AND (ROWNUM=1)) AS CODE_CORPS,
(SELECT lc_corps
FROM (SELECT co2.lc_corps, ec2.no_dossier_pers
FROM element_carriere ec2, carriere c2, corps co2
WHERE (c2.no_dossier_pers = ec2.no_dossier_pers(+))
AND (c2.d_deb_carriere <= sysdate)
AND (ec2.c_corps = co2.c_corps)
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 (no_dossier_pers = i.no_individu) AND (ROWNUM=1)) AS CORPS,
iem.no_e_mail as EMAIL,
NULL AS GRADE,
decode(sign((sysdate - to_date(concat('31/08',to_char(sysdate,'YYYY'))))),-1,to_char(add_months(sysdate,-12),'YYYY')||'/'|| to_char(sysdate,'YYYY'),0,to_char(add_months(sysdate,-12),'YYYY')||'/'|| to_char(sysdate,'YYYY'),
to_char(sysdate,'YYYY')||'/'||to_char(add_months(sysdate,12),'YYYY')) AS ANNEE,
(CASE
WHEN (a.d_deb_affectation <= sysdate) THEN
(CASE
WHEN (a.d_fin_affectation >= sysdate) THEN 'Carte professionnelle'
WHEN (a.d_fin_affectation IS NULL) THEN 'Carte professionnelle'
END)
-- Comme c'est un fallback, on n'a pas à refiltrer comme on fait dans le WHERE : ceux qui sont en partie personnel et en partie hébergés ne passeront pas dans la suite du CASE
WHEN (c.d_deb_str_trav <= sysdate) THEN
(CASE
WHEN (c.d_fin_str_trav >= sysdate) THEN 'Carte services'
WHEN (c.d_fin_str_trav IS NULL) THEN 'Carte services'
END)
END) AS TYPE_CARTE,
NULL AS QUALITE,
NULL AS ADH_SPORT,
NULL AS LICENCE_SPORT,
NULL AS CODE_ACTIVITE,
concat('p', lpad(to_char(i.no_individu), 6, '0')) as LOGIN,
'88' AS CODE_SOCIETE,
--nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) AS INM
(CASE
WHEN (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) <320) THEN '1'
WHEN (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) <400) THEN '2'
WHEN (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) <465) THEN '3'
WHEN (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) <550) THEN '4'
WHEN (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) >=550) THEN '5'
ELSE '6'
END) AS TARIF_CROUS,
nvl(nvl(a.d_fin_affectation, c.d_fin_str_trav), concat('3010', decode(sign((sysdate - to_date(concat('31/08',to_char(sysdate,'YYYY'))))),-1,to_char(sysdate,'YYYY'),0,to_char(to_char(sysdate,'YYYY'),to_char(add_months(sysdate,12),'YYYY'))))) AS DATE_FIN_VALIDITE
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, contrat_travail ct, contrat_avenant av, type_contrat_travail tct , evolution_chevron ev_ch, majoration_indice mi
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 (i.no_individu = ct.no_dossier_pers(+))
AND (ct.no_dossier_pers = av.no_dossier_pers(+))
AND (ct.c_type_contrat_trav = tct.c_type_contrat_trav(+))
AND (ec.no_dossier_pers = ev_ch.no_dossier_pers(+))
AND (av.indice_contrat = mi.c_indice_chevron(+))
AND (
(
((a.d_deb_affectation <= sysdate) AND ((a.d_fin_affectation >= sysdate) OR (a.d_fin_affectation IS NULL)))
AND ((
(ec.tem_provisoire = 'N')
AND (ec.tem_confirme = 'O')
AND (ec.no_arrete_annulation IS NULL)
AND (to_date(to_char(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN ec.d_effet_element AND nvl(ec.d_fin_element, sysdate) or (ec.d_fin_element < ec.d_effet_element))
)
OR
(
(av.tem_annulation = 'N')
AND (to_date(to_char(sysdate, 'DD/MM/YYYY'), 'DD/MM/YYYY') BETWEEN nvl(av.d_deb_contrat_trav,sysdate) AND nvl(av.d_fin_execution, nvl(av.d_fin_contrat_trav,sysdate)))
))
)--(NOT((a.d_deb_affectation <= sysdate) AND ((a.d_fin_affectation >= sysdate) OR (a.d_deb_affectation IS NOT NULL AND 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)) AND (nvl(indice_majore(recup_remuneration_brute(ec.no_dossier_pers, ec.no_seq_carriere, ec.no_seq_element), sysdate), indice_majore(av.indice_contrat, av.d_deb_contrat_trav)) IS NULL) AND (NOT((a.d_deb_affectation <= sysdate) OR (a.d_fin_affectation >= sysdate)) OR (a.d_deb_affectation IS NULL AND a.d_fin_affectation IS NULL))))
)
AND (i.nom_patronymique LIKE '{0}%' OR i.prenom LIKE '{0}%')
AND (length('{0}')>=2)
ORDER BY i.no_individu;