SQL to list rule system codes that trigger pay items
By AMO
List rule system codes that trigger pay items
-- 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 ;
List rule system codes that trigger a pay item
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' ;
List rule system files that trigger a DRM 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' ;
List rule system codes that trigger a pay item, whether it is active or not
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 ;
List rule system codes that trigger a pay item, whether it is active or not
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 ;
List rule system code triggered by organization units - All
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 ;
List rule system code triggered by organization units - Code
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 ;