ZY00 Les agents ou salariés

accept Matricule prompt "Matricule:" default 0025627

select a.matcle "Matricule",
trim(a.prenom) "Prénom",
trim(a.nomuse) "Nom",
c.idcy00 "Société",
c.datent "Date entrée", c.datsor "Date sortie",
g.idesta "Etablissement", trim(h.liblon) "Libellé étab",
e.classi "Classification", trim(f.liblon) "Libellé classi",
e.qualif "Qualif",
b.typcon "Type contrat", trim(d.liblon) "Libellé contrat", b.datcon "Début contrat"
from zy00 a, zyco b, zyes c,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIP' and zb.cdlang = 'F') d,
zyca e,
(select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'UIX' and xb.cdlang = 'F' ) f,
zy38 g,
(select ta.cdcode, tb.liblon from zd00 ta, zd01 tb where ta.nudoss = tb.nudoss and ta.cdstco = 'DRE' and tb.cdlang = 'F' ) h
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = e.nudoss
and a.nudoss = g.nudoss
-- jointure pour le code contrat
-- la nature de contrat ne semble pas être utilisée 
and b.typcon = d.cdcode
-- jointure pour la classification
and e.classi = f.cdcode
-- jointure pour le code établissement
and g.idesta = h.cdcode
and a.matcle = '&Matricule'
and b.datfin = (select max(datfin) from zyco where nudoss = a.nudoss)
and c.datsor = (select max(datsor) from zyes where nudoss = a.nudoss)
and e.datfin = (select max(datfin) from zyca where nudoss = a.nudoss)
and g.dtef00 = (select max(dtef00) from zy38 where nudoss = a.nudoss)
;

select a.matcle "Matr.",
to_char(b.datgra,'dd/mm/yyyy') "Date début",
to_char(b.datfin,'dd/mm/yyyy') "Date fin",
b.codcar "Carrière",
b.adecod "Grade",
b.echelo "Echelon",
b.chevro "Chevron",
b.motind "Type Ind. Man.",
b.indman "Indice Man."
from zy00 a, zygr b
where a.nudoss = b.nudoss
and b.datfin = to_date('31/12/2999','dd/mm/yyyy')
and a.nudoss in (
select nudoss from zygr
where datfin = to_date('31/12/2999','dd/mm/yyyy')
and codcar = 'N'
group by nudoss
having count(*) > 1)
;

select a.matcle, a.prenom, a.nomuse,
c.datent, c.datsor, c.idcy00,
e.classi, f.liblon,
e.qualif,
b.datcon, b.typcon, d.liblon
from zy00 a, zyco b, zyes c,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIP') d,
zyca e,
(select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'UIX') f,
zy0j g
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = e.nudoss
and a.nudoss = g.nudoss
and b.typcon = d.cdcode
-- la nature de contrat ne semble pas être utilisée
and e.classi = f.cdcode
--and a.matcle = '0155727'  - Sarah
and b.datfin = '31/12/2999'
and c.datsor = '31/12/2999'
and e.datfin = '31/12/2999'
and g.cochex <> 'D'
;

select a.matcle "Matricule", to_char(b.dateff, 'DD/MM/YYYY') "Date début grade", b.catego "Catégorie", b.adecod "Grade",
to_char(c.dateff, 'DD/MM/YYYY') "Date début position", c.posits "Position statut.", c.sitcod "Position adm", t.liblon "Position adm Libellé",
to_char(d.datcon, 'DD/MM/YYYY') "Date début contrat", d.typcon "Type de contrat", x.liblon "Type de contrat Libellé", d.natcon "Nature de contrat", y.liblon "Nature de contrat Libellé",
to_char(e.dateff, 'DD/MM/YYYY') "Date début PIP", e.statut "PIP Code", z.liblon "PIP Libellé",
to_char(g.dateff, 'DD/MM/YYYY') "Date début TL", g.codtra "Code Temps travail", g.rtsthr "% Travaillé", g.rtpdhr "% Payé",
to_char(f.datsor, 'DD/MM/YYYY') "Date sortie"
from zy00 a,
(select nudoss, dateff, catego, adecod from zygr where datfin = '31/12/2999') b,
(select nudoss, dateff, posits, sitcod from zypo where datxxx = '31/12/2999') c,
(select nudoss, datcon, typcon, natcon from zyco where datfin = '31/12/2999') d,
(select nudoss, dateff, statut from zyfl where datxxx = '31/12/2999') e,
zyes f,
(select nudoss, dateff, codtra, rtsthr, rtpdhr from zytl b where b.datxxx = to_date('31/12/2999', 'dd/MM/YYYY')) g,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'HJ8' ) z,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIP' ) x,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIQ' ) y,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'HKK' ) t
where a.nudoss = b.nudoss(+)
and a.nudoss = c.nudoss(+)
and a.nudoss = d.nudoss(+)
and a.nudoss = e.nudoss(+)
and a.nudoss = f.nudoss(+) 
and a.nudoss = g.nudoss(+) 
and e.statut = z.cdcode(+)
and d.typcon = x.cdcode(+) 
and d.natcon = y.cdcode(+)
and c.sitcod = t.cdcode(+) ;

accept Matricule prompt "Matricule:" default 0025627
-- SQL KO pour l'instant

select a.matcle, b.codcai "Mutuelle", b.regime "Régime", b.debcai "Début mutuelle", b.fincai "Fin mutuelle",
c.codcai "Retraite", c.regime "Régime retraite", c.debcai "Début retraite", c.fincai "Fin retraite",
d.codcai "Prévoyance", d.regime "Régime prévoyance", d.debcai "Début prévoyance", d.fincai "Fin prévoyance"
from zy00 a, zycs b, zycs c, zycs d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and a.matcle = '&Matricule'
and b.debcai <= sysdate
and sysdate <= b.fincai
and b.codcai in (select za.cdcode
                  from zd00 za, zd01 zb, zdcc zc
                  where za.nudoss = zb.nudoss
                  and za.nudoss = zc.nudoss
                  and za.cdstco = 'DRK' and zb.cdlang = 'F' and zc.tycais = 'M' )
and c.debcai <= sysdate
and sysdate <= c.fincai
and c.codcai in (select za.cdcode
                  from zd00 za, zd01 zb, zdcc zc
                  where za.nudoss = zb.nudoss
                  and za.nudoss = zc.nudoss
                  and za.cdstco = 'DRK' and zb.cdlang = 'F' and zc.tycais in ('D', 'U') )
and d.debcai <= sysdate
and sysdate <= d.fincai
and d.codcai in (select za.cdcode
                  from zd00 za, zd01 zb, zdcc zc
                  where za.nudoss = zb.nudoss
                  and za.nudoss = zc.nudoss
                  and za.cdstco = 'DRK' and zb.cdlang = 'F' and zc.tycais = 'P' )
;
                  

select a.matcle, a.nomuse, b.typcon, b.datcon, b.datfin, c.typcon, c.datcon, c.datfin
from zy00 a, zyco b,
(select nudoss, nuligs, typcon, datcon, datfin from zyco
where nudoss in (select nudoss from zytd12 where cdinfo = 'CO' and nombre > 1)
and typcon = 'DD' and nulign = 1) c
where a.nudoss = b.nudoss and b.nudoss = c.nudoss
and b.nulign = c.nuligs 
and b.typcon = 'DD' 
;

accept Matricule prompt "Matricule:" default 0025627
 
select a.matcle "Matricule",
trim(a.prenom) "Prénom",
trim(a.nomuse) "Nom",
c.idcy00 "Société",
c.datent "Date entrée", c.datsor "Date sortie",
g.idesta "Etablissement", trim(h.liblon) "Libellé étab",
e.classi "Classification", trim(f.liblon) "Libellé classi",
e.qualif "Qualif",
b.typcon "Type contrat", trim(d.liblon) "Libellé contrat", b.datcon "Début contrat"
from zy00 a, zyco b, zyes c,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'UIP' and zb.cdlang = 'F') d,
zyca e,
(select xa.cdcode, xb.liblon from zd00 xa, zd01 xb where xa.nudoss = xb.nudoss and xa.cdstco = 'UIX' and xb.cdlang = 'F' ) f,
zy38 g,
(select ta.cdcode, tb.liblon from zd00 ta, zd01 tb where ta.nudoss = tb.nudoss and ta.cdstco = 'DRE' and tb.cdlang = 'F' ) h
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = e.nudoss
and a.nudoss = g.nudoss
-- jointure pour le code contrat
-- la nature de contrat ne semble pas être utilisée
and b.typcon = d.cdcode
-- jointure pour la classification
and e.classi = f.cdcode
-- jointure pour le code établissement
and g.idesta = h.cdcode
and a.matcle = '&Matricule'
and b.datfin = (select max(datfin) from zyco where nudoss = a.nudoss)
and c.datsor = (select max(datsor) from zyes where nudoss = a.nudoss)
and e.datfin = (select max(datfin) from zyca where nudoss = a.nudoss)
and g.dtef00 = (select max(dtef00) from zy38 where nudoss = a.nudoss)
;

select c.matcle from zy00 c
where c.nudoss in (
(select a.nudoss from zy00 a,
zypo b
where
a.nudoss = b.nudoss
and b.sitcod = 'ACTI'
and b.dateff <= '2015-01-01-00.00.00'
and b.datxxx > '2015-01-01-00.00.00'
and a.matcle not like 'R%')
minus
(select distinct(nudoss) from zy3b where dtef00 like '2015-01-01-%'))
;