SQL pour lister des EFC

-- Voir à ajouter le modèle
select a.cdstco "Répertoire", f.lilong "Libellé Rép", a.cdcode "Code", e.liblon "Libellé",
b.dadeb "Date début", b.cdrubr "Rubrique", c.liblon"Libellé Rubrique", b.cdform "Format", b.mtsala "Montant sal",
b.cdregr "Regroupement", d.liblon "Libellé regroupement"
from zd00 a, zdef b,
 ( select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'DRC' and xb.cdlang = 'F') c,
 (select ya.cdcode, yb.liblon from zd00 ya, zd01 yb where ya.nudoss = yb.nudoss and ya.cdstco = 'DRM' and yb.cdlang = 'F') d,
 zd01 e,
 (select za.vacdst, zb.lilong from di30 za, di31 zb where za.cdstdo = zb.cdstdo and za.vacdst = zb.vacdst) f
where a.nudoss = b.nudoss
and a.nudoss = e.nudoss
and e.cdlang = 'F'
and b.dafin = to_date('31/12/2999', 'DD/MM/YYYY')
and b.cdrubr = c.cdcode(+)
and b.cdregr = d.cdcode(+)
and a.cdstco = f.vacdst(+)
order by a.cdstco, a.cdcode ;

select a.cdrubr,  a.cdregr, a.dadeb, a.dafin, b.cdregl, b.cdstco, b.cdcode
from zdef a, zd00 b
where a.nudoss = b.nudoss
and a.dafin = '31/12/2999' 
and a.cdrubr = '&code' ;

select a.cdrubr,  a.cdregr, a.dadeb, a.dafin, b.cdregl, b.cdcode, b.cdstco
from zdef a, zd00 b
where a.nudoss = b.nudoss
and a.dafin = '31/12/2999' 
and a.cdregr = '&code' ;

select a.CDSTCO "Répertoire", f.LILONG "Libellé Rép", a.CDCODE "Code", e.LIBLON "Libellé",
b.DADEB "Date début", b.DAFIN "Date fin", b.CDRUBR "Rubrique", c.LIBLON "Libellé Rubrique", b.CDFORM "Format", b.MTSALA "Montant sal",
b.CDREGR "Regroupement", d.LIBLON "Libellé regroupement"
from HR.ZD00 a
left outer join
(select za.VACDST, zb.LILONG from HR.DI30 za, HR.DI31 zb where za.CDSTDO = zb.CDSTDO and za.VACDST = zb.VACDST) f
on a.CDSTCO = f.VACDST,
HR.ZDEF b
left outer join
(select xa.CDCODE, xb.LIBLON from HR.ZD00 xa, HR.ZD01 xb where xa.NUDOSS = xb.NUDOSS and xa.CDSTCO = 'DRC' and xb.CDLANG = 'F') c
on b.CDRUBR = c.CDCODE
left outer join
(select ya.CDCODE, yb.LIBLON from HR.ZD00 ya, HR.ZD01 yb where ya.NUDOSS = yb.NUDOSS and ya.CDSTCO = 'DRM' and yb.CDLANG = 'F') d
on b.CDREGR = d.CDCODE,
HR.ZD01 e
where a.NUDOSS = b.NUDOSS
and a.NUDOSS = e.NUDOSS
and e.CDLANG = 'F'
order by a.CDSTCO, a.CDCODE ;

select a.cdstco "Répertoire", f.lilong "Libellé Rép", a.cdcode "Code", e.liblon "Libellé",
b.dadeb "Date début", b.dafin "Date fin", b.cdrubr "Rubrique", c.liblon"Libellé Rubrique", b.cdform "Format", b.mtsala "Montant sal",
b.cdregr "Regroupement", d.liblon "Libellé regroupement"
from zd00 a, zdef b,
 ( select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'DRC' and xb.cdlang = 'F') c,
 (select ya.cdcode, yb.liblon from zd00 ya, zd01 yb where ya.nudoss = yb.nudoss and ya.cdstco = 'DRM' and yb.cdlang = 'F') d,
 zd01 e,
 (select za.vacdst, zb.lilong from di30 za, di31 zb where za.cdstdo = zb.cdstdo and za.vacdst = zb.vacdst) f
where a.nudoss = b.nudoss
and a.nudoss = e.nudoss
and e.cdlang = 'F'
and b.cdrubr = c.cdcode(+)
and b.cdregr = d.cdcode(+)
and a.cdstco = f.vacdst(+)
order by a.cdstco, a.cdcode ;

select a.modele "Modèle de l EFC", a.modcli "Modèle paie client", a.cdrubr "Code rubrique Paie",  a.cdregr "Code regroupement", a.dadeb "Date début", a.dafin "Date fin", b.idou00 "UO", b.idcy00 "Rubrique société", b.cyou00 "Règlementation"
from zeef a, ze00 b
where a.nudoss = b.nudoss
and a.modele = '&Modele'
and a.dafin = '31/12/2999'
order by a.cdrubr, a.cdregr ;

select a.modele "Modèle de l EFC", a.modcli "Modèle paie client", a.cdrubr "Code rubrique Paie",  a.cdregr "Code regroupement", a.dadeb "Date début", a.dafin "Date fin", b.idou00 "UO", b.idcy00 "Rubrique société", b.cyou00 "Règlementation"
from zeef a, ze00 b
where a.nudoss = b.nudoss
and a.modele = '&Modele'
and a.dafin = '31/12/2999'
and a.cdrubr = '&code'
order by a.cdrubr, a.cdregr ;