ZDDS9 Le répertoire des constantes

select trim(a.cdcode) "Code", trim(b.liblon) "Libellé long", trim(b.libabr) "Libellé court", c.daeff, c.mtcl1c , c.valeur
from zd00 a, zd01 b, zdcu c
where a.nudoss = b.nudoss(+)
and a.nudoss = c.nudoss
and a.cdstco = 'DS9'
and b.cdlang = 'F'
order by a.cdcode, c.daeff ;

select rtrim(a.CDCODE) "Code", rtrim(b.LIBLON) "Libellé long", rtrim(b.LIBABR) "Libellé court", c.DAEFF, c.MTCL1C, c.MTCL2C, c.MTCL3C, c.VALEUR
from HR.ZD00 a
left outer join
HR.ZD01 b
on a.NUDOSS = b.NUDOSS,
HR.ZDCU c
where
a.NUDOSS = c.NUDOSS
and a.CDSTCO = 'DS9'
and b.CDLANG = 'F'
order by a.CDCODE, c.DAEFF

select ya.cdregl, ya.cdcode, yb.datri, yb.valeur
from zd00 ya, zdcu yb
where ya.nudoss = yb.nudoss
and ya.cdstco = 'DS9'
and ya.cdcode = '&&Code'
and yb.datri = (select max(yd.datri)
                from zd00 yc, zdcu yd
                where yc.nudoss = yd.nudoss
                and yc.cdstco = 'DS9'
                and yc.cdcode = '&&Code'
                and yd.datri <= to_date('2020/01/01', 'yyyy/mm/dd'));

select ya.cdregl, ya.cdcode, yd.liblon, yb.datri, yb.valeur, case when nbmclef > 0 then 'X' else ' ' end mtclef
from zd00 ya, zdcu yb,
(select nudoss, count(*) nbmclef from zdbu group by nudoss) yc,
zd01 yd,
(select zc.nudoss, max(zd.datri) maxdat
from zd00 zc, zdcu zd
where zc.nudoss = zd.nudoss
and zc.cdstco = 'DS9' group by zc.nudoss) ye
where ya.nudoss = yb.nudoss(+)
and ya.nudoss = yc.nudoss(+)
and ya.nudoss = yd.nudoss
and ya.nudoss = ye.nudoss
and ya.cdstco = 'DS9'
and yb.datri = ye.maxdat
order by ya.cdcode
 ;

select trim(a.cdcode) "Code", trim(b.liblon) "Libellé long", c.daeff, c.mtcl1c, w.liblon, c.valeur
from zd00 a,
zd01 b,
zdcu c,
(select d.cdcode, e.liblon from zd00 d, zd01 e where d.nudoss = e.nudoss and d.cdregl = 'PRC' and d.cdstco = 'DRE' and e.cdlang = 'F' ) w
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdregl = 'VPR'
and a.cdstco = 'DS9'
and b.cdlang = 'F'
and a.cdcode = '&constante'
and c.mtcl1c = w.cdcode(+)
order by c.mtcl1c, c.daeff ;

select trim(a.cdcode) "Code", trim(b.liblon) "Libellé long", c.daeff, c.mtcl1c, w.liblon, c.mtcl2c, x.liblon, c.valeur
from zd00 a,
zd01 b,
zdcu c,
(select d.cdcode, e.liblon from zd00 d, zd01 e where d.nudoss = e.nudoss and d.cdregl = 'PRC' and d.cdstco = 'UAA' and e.cdlang = 'F' ) w,
(select f.cdcode, g.liblon from zd00 f, zd01 g where f.nudoss = g.nudoss and f.cdregl = 'PRC' and f.cdstco = 'DRE' and g.cdlang = 'F' ) x
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdregl = 'VPR'
and a.cdstco = 'DS9'
and b.cdlang = 'F'
and a.cdcode = '&constante'
and c.mtcl1c = w.cdcode(+)
and c.mtcl2c = x.cdcode(+)
order by c.mtcl1c, c.mtcl2c, c.daeff ;

select trim(a.cdcode) "Code", trim(b.liblon) "Libellé long", c.daeff, c.mtcl1c, w.liblon, c.valeur
from zd00 a,
zd01 b,
zdcu c,
(select d.cdcode, e.liblon from zd00 d, zd01 e where d.nudoss = e.nudoss and d.cdregl = 'PRC' and d.cdstco = 'UAA' and e.cdlang = 'F' ) w
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdregl = 'VPR'
and a.cdstco = 'DS9'
and b.cdlang = 'F'
and a.cdcode = '&constante'
and c.mtcl1c = w.cdcode(+)
order by c.mtcl1c, c.daeff ;

select a.cdcode, b.liblon, c.daeff, c.mtcl1c, c.valeur
from zd00 a, zd01 b, zdcu c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DS9'
and c.daeff = ( select max(d.daeff) from zdcu d where d.nudoss = a.nudoss )
and a.cdcode = '&p_constante'
order by a.cdcode ;

select c.valeur "Valeur",
trim(a.cdcode) "Code",
c.mtcl1c "Société",
trim(b.liblon) "Libellé long",
trim(b.libabr) "Libellé court",
c.daeff "Date effet"
from zd00 a, zd01 b, zdcu c,
(select za.nudoss, max(zb.daeff) as daeff from zd00 za, zdcu zb where za.nudoss = zb.nudoss and za.cdstco = 'DS9' group by za.nudoss) d
where a.nudoss = b.nudoss(+)
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.cdstco = 'DS9'
and c.daeff = d.daeff
--order by a.cdcode
order by c.valeur 
;

select trim(a.cdcode) "Code", trim(b.liblon) "Libellé long", trim(b.libabr) "Libellé court", c.daeff, c.mtcl1c, c.mtcl3n , c.valeur
from zd00 a, zd01 b, zdcu c
where a.nudoss = b.nudoss(+)
and a.nudoss = c.nudoss
and a.cdregl = 'POM'
and a.cdstco = 'DS9'
and b.cdlang = 'F'
and a.cdcode = '&Code'
order by a.cdcode, c.daeff, c.mtcl1c, c.mtcl3n ;