select ut_fcg.fct, ut_fcg.tit, ut_pled.codfct, ut_pled.codpled, ut_pled.titedt, ut_pled.vue_util
from ut_uti
inner join (
select hab.codsoc, hab.uti, hab.fct, hab.opt
from ut_hab hab
where hab.poste = 'N'
union
select hab1.codsoc, hab1.uti, hab2.fct, hab2.opt
from ut_hab hab1
inner join ut_hab hab2 on hab2.codsoc = hab1.codsoc and hab2.poste = 'O' and hab2.uti = hab1.cha
where hab1.poste = 'P'
union
select ut_fcg.codsoc, ut_uti.uti, ut_fcg.fct, ' ' opt
from ut_uti
left outer join ut_hab on ut_hab.codsoc = ut_uti.codsoc and ut_hab.uti = ut_uti.uti
inner join ut_fcg on ut_fcg.codsoc = ut_uti.codsoc and ut_fcg.modfct = 'B'
where ut_hab.fct is null
) lstfct on lstfct.codsoc = ut_uti.codsoc and lstfct.uti = ut_uti.uti
inner join ut_fcg on ut_fcg.codsoc = lstfct.codsoc and ut_fcg.fct like lstfct.fct || '%' and ut_fcg.eshs = 'ES' and (ut_fcg.fic like '%gedte.exe' or ut_fcg.fic like '%gedte2.exe') and ut_fcg.modfct = 'B' inner join ut_pled on ut_pled.codsoc = ut_fcg.codsoc and ut_pled.eshs = 'ES' and ut_pled.codfct = ut_fcg.fct and ut_pled.codlan = ut_uti.codlan
where ut_uti.eshs = 'ES' and ut_uti.poste = 'N'
and (lstfct.opt = ' ' or substr(lstfct.opt, 5, 1) = 'E')
and ut_pled.vue_util != ' '
-- paramètres
and ut_uti.uti = 'GNC'
and ut_uti.codsoc = 20
-- tri
order by tit, titedt
|