ZDDSJ Le répertoire des absences

select a.cdcode, b.liblon
from zd00 a, zd01 b
where a.nudoss = b.nudoss
and a.cdregl = 'PRC'
and a.cdstco = 'DSJ'
and b.cdlang = 'F' ;

select a.CDCODE, b.LIBLON, c.DADEB, c.DAFIN,  c.CDUNIT, c.CDDROI, c.CDRUB1, d.LIBLON, d.RGPOSB, c.CDRUB2, e.LIBLON, e.RGPOSB, c.CDRUB3, c.CDRUB4, c.CDRUB5, c.CDTRAI
from DSNHR.ZD00 a, DSNHR.ZD01 b, DSNHR.ZDCO c
left outer join 
(select za.CDCODE, zb.LIBLON, zc.RGPOSB from DSNHR.ZD00 za, DSNHR.ZD01 zb, DSNHR.ZDAO zc
where za.NUDOSS = zb.NUDOSS
and za.NUDOSS = zc.NUDOSS
and za.CDSTCO = 'DRC' and zb.CDLANG = 'F') d
on c.CDRUB1 = d.CDCODE
left outer join
(select ya.CDCODE, yb.LIBLON, yc.RGPOSB from DSNHR.ZD00 ya, DSNHR.ZD01 yb, DSNHR.ZDAO yc
where ya.NUDOSS = yb.NUDOSS
and ya.NUDOSS = yc.NUDOSS
and ya.CDSTCO = 'DRC' and yb.CDLANG = 'F') e
on c.CDRUB2 = e.CDCODE
where a.NUDOSS = b.NUDOSS
and a.NUDOSS = c.NUDOSS
and a.CDREGL = 'FRO'
and a.CDSTCO = 'DSJ'
and b.CDLANG = 'F'
and a.CDCODE = '&Motif' ; -- Remplacer &Motif avant d'exécuter

select a.cdcode, b.liblon, c.dadeb, c.dafin,  c.cdunit, c.cddroi, c.cdrub1, d.liblon, d.rgposb, c.cdrub2, e.liblon, e.rgposb, c.cdrub3, c.cdrub4, c.cdrub5, c.cdtrai
from zd00 a, zd01 b, zdco c,
(select za.cdcode, zb.liblon, zc.rgposb from zd00 za, zd01 zb, zdao zc
where za.nudoss = zb.nudoss
and za.nudoss = zc.nudoss
and za.cdstco = 'DRC' and zb.cdlang = 'F') d,
(select ya.cdcode, yb.liblon, yc.rgposb from zd00 ya, zd01 yb, zdao yc
where ya.nudoss = yb.nudoss
and ya.nudoss = yc.nudoss
and ya.cdstco = 'DRC' and yb.cdlang = 'F') e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdregl = 'FRO'
and a.cdstco = 'DSJ'
and c.cdrub1 = d.cdcode(+)
and c.cdrub2 = e.cdcode(+)
and a.cdcode = '&Motif' ;

select a.CDCODE, b.LIBLON, c.DADEB, c.DAFIN,  c.CDUNIT, c.CDDROI, c.CDRUB1, c.CDRUB2
from DEVHR.ZD00 a
left outer join DEVHR.ZD01 b on a.NUDOSS = b.NUDOSS,
DEVHR.ZDCO c
where a.NUDOSS = c.NUDOSS
and a.CDSTCO = 'DSJ'
and ( c.CDRUB1 = '&Motif'
or c.CDRUB2 = '&Motif' ) ;

select a.cdregl, a.cdcode, b.liblon, c.dadeb, c.dafin,  c.cdunit, c.cdtrai,
c.cddroi, d.liblon, d.cdstat, d.duanmi, d.carenc, d.tranc1, d.tranc2, d.tranc3, d.tranc4, d.tranc5
from zd00 a, zd01 b, zdco c,
(select distinct za.cdcode, zb.liblon, zc.* from zd00 za, zd01 zb, zdcd zc
where za.nudoss = zb.nudoss
and za.nudoss = zc.nudoss
and za.cdstco = 'DSF' and zb.cdlang = 'F') d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
--and a.cdregl = 'FRO'
and a.cdstco = 'DSJ'
and c.cddroi = d.cdcode
 ;

set verify off
accept p_rubrique prompt 'Liste des absences déclenchant une rubrique de paie - Entrez le code DRC : '
select trim(a.cdcode) || ' : ' || trim(b.liblon)
from zd00 a,
zd01 b,
zdco c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DSJ'
and ( b.cdlang = 'F' or b.cdlang = ' ')
and (c.cdrub1 = '&&p_rubrique'
  or c.cdrub2 = '&&p_rubrique'
  or c.cdrub3 = '&&p_rubrique'
  or c.cdrub4 = '&&p_rubrique'
  or c.cdrub5 = '&&p_rubrique' );

select a.cdregl, a.cdcode, b.liblon, c.dadeb, c.dafin,  c.cdunit, c.cddroi, c.cdrub1, d.liblon, d.rgposb, c.cdrub2, e.liblon, e.rgposb, c.cdrub3, c.cdrub4, c.cdrub5, c.cdtrai
from zd00 a, zd01 b, zdco c,
(select za.cdcode, zb.liblon, zc.rgposb from zd00 za, zd01 zb, zdao zc
where za.nudoss = zb.nudoss
and za.nudoss = zc.nudoss
and za.cdstco = 'DRC' and zb.cdlang = 'F') d,
(select ya.cdcode, yb.liblon, yc.rgposb from zd00 ya, zd01 yb, zdao yc
where ya.nudoss = yb.nudoss
and ya.nudoss = yc.nudoss
and ya.cdstco = 'DRC' and yb.cdlang = 'F') e
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
--and a.cdregl = 'FRO'
and a.cdstco = 'DSJ'
and c.cdrub1 = d.cdcode(+)
and c.cdrub2 = e.cdcode(+)
 ;

select a.cdregl, trim(a.cdcode) "Code", trim(b.liblon) "Libellé",
c.flgcet "Impacts sur le CET",
c.tyoper "Type d'opération associé (CET)",
c.cptde1 "Compte débité n1"
from zd00 a, zd01 b, zdcn c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.cdstco = 'DSJ'
and b.cdlang = 'F' order by a.cdcode;