ZE00 Les UO

select a.idou00 as "CodeUO",
a.dtef00 as "DateDebUO",
a.idcy00 as "SocieteUO",
b.lboush as "LibelleUO",
a.tyou00 as "TypeUO",
d.tyou00 as "Niveau",
c.dtef00 as "DateDebRattachement",
c.tytrst as "Rattachement",
c.idou00 as "UOMere",
e.matcle as "MatriculeSurUO",
e.idps00 as "PosteSurUO",
(case when e.idps00 <> ' ' then 
        (select (trim(m.matcle) || ' ' || trim(m.prenom) || ' ' || trim(m.nomuse))
        from zy00 m, zy3b n
        where m.nudoss = n.nudoss
        and n.idps00 = e.idps00
        and n.dtef00 <= sysdate
        and n.dten00 > sysdate
        and rownum = 1 -- bug de données: plusieurs agents sur un même poste
        )
      when e.matcle <> ' ' then
        (select (trim(m.matcle) || ' ' || trim(m.prenom) || ' ' || trim(m.nomuse))
        from zy00 m
        where m.matcle = e.matcle
        )
      else ' '
end) "Agent"
from ze00 a,
ze01 b,
ze2e c,
(select za.cdcode, zb.tyou00
from zd00 za, zd4f zb
where za.nudoss = zb.nudoss
and za.cdstco = 'U3G'
) d,
(select * from ze0b where dtef00 <= sysdate and dten00 > sysdate) e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss(+)
and a.tyou00 = d.cdcode
and a.nudoss = e.nudoss(+)
;

set pagesize 10000
spool UO_extrait.txt
select a.idou00 as "CodeUO",
a.dtef00 as "DateDebUO",
a.idcy00 as "SocieteUO",
b.lboush as "LibelleUO",
a.tyou00 as "TypeUO",
c.dtef00 as "DateDebRattachement",
c.tytrst as "Rattachement",
c.idou00 as "UOMere"
from ze00 a,
ze01 b,
ze2e c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss(+)
/