PRDB Les Résultats de paie, autrement dit le bulletin de paie calculé

select a.matric, a.perpai, b.perval, b.codrub, b.source, b.calcul, b.libell, b.nbrbas, b.tausal, b.monsal, b.taupat, b.monpat
from zx00 a, zx8k b
where a.nudoss = b.nudoss
and a.matric = '0147741'
and a.perpai = 'MT202002'
and b.perval = '201912' ;

select c.timval, a.matric, b.codrub, b.perpai, b.monsal from zx00 a, zx8k b, zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and b.matric = '&Matricule'
and c.validt = 'I';

select a.matric, b.codrub, b.perpai, b.monsal, c.codsoc, d.validt
from zx00 a, zx8k b, zx6b c, zx5v d
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and a.nudoss = d.nudoss
and c.codsoc = '&Société' 
and b.codrub = '&Rubrique' 
and b.perpai = '&Période'
and d.validt = 'I';

with rub_et_mat as (select b.codrub,
a.perpai,
a.matric
from zx00 a,
zx8k b
where a.nudoss = b.nudoss
and b.codrub in (select a.cdcode from zd00 a, zdao b where a.nudoss = b.nudoss and b.rgposb <> ' ')
and b.perpai >= 'MT202201'
),
rub_et_mat_rn as (
select codrub, matric, perpai, row_number() over (partition by codrub order by perpai desc ) as rn
from rub_et_mat )
select codrub, matric, perpai from rub_et_mat_rn where rn = 1
;

select c.timval, a.matric, b.codrub, b.perpai, b.datrub , b.monsal from zx00 a,
(select * from zx8k where codrub in (select a.cdcode from zd00 a, zdcq b where a.nudoss = b.nudoss and a.cdstco = 'DRC' and b.cdassi = '&Assiette')) b,
zx5v c
where a.nudoss = b.nudoss
and a.nudoss = c.nudoss
and b.matric = '500213'
and c.validt = 'I'
order by b.datrub, b.codrub;

select a.codrub, c.liblon, d.rgposb, count(*)
from zx8k a, zd00 b, zd01 c, zdao d
where a.codrub = b.cdcode
and b.cdstco = 'DRC'
and b.nudoss = c.nudoss
and c.cdlang = 'F'
and b.nudoss = d.nudoss
and d.rgposb <> ' '
group by a.codrub, c.liblon, d.rgposb
order by nlssort(d.rgposb, 'NLS_SORT=BINARY'), a.codrub ;

select e.statut "PIP", f.liblon "Libellé PIP",
a.codrub "Code", c.liblon "Libellé rubrique",
d.rgposb "BP", count(*) "Nb"
from zx8k a, zd00 b, zd01 c, zdao d, zxv4 e,
(select za.cdcode, zb.liblon from zd00 za, zd01 zb where za.nudoss = zb.nudoss and za.cdstco = 'HJ8' and zb.cdlang = 'F') f,
zx00 g
where a.codrub = b.cdcode
and b.cdstco = 'DRC'
and b.nudoss = c.nudoss
and c.cdlang = 'F'
and b.nudoss = d.nudoss
and d.rgposb <> ' '
and a.nudoss = e.nudoss
and e.statut = f.cdcode
and e.nulign = ( select max(zc.nulign) from zxv4 zc where zc.nudoss = a.nudoss)
and a.nudoss = g.nudoss
and g.perpai = 'MT201501'
group by e.statut, f.liblon, a.codrub, c.liblon, d.rgposb
order by e.statut, nlssort(d.rgposb, 'NLS_SORT=BINARY'), a.codrub ;

select a.MATRIC, b.CODRUB, b.IDGLAR, b.CODRUB, b.DEBCRE, b.CDSIGN, b.AMOUNT
from PRDHR.ZX00 a, PRDHR.ZX4K b
where a.NUDOSS = b.NUDOSS
and a.MATRIC = 'P01279'
and a.PERPAI = 'MT201901'

select a.MATRIC, c.IDACCR,
sum(case
when c.SALPAT = 'A' and c.DEBCRE = 'D'
  then (b.MONSAL + b.MONPAT)
when c.SALPAT = 'S' and c.DEBCRE = 'D'
  then b.MONSAL
when c.SALPAT = 'P' and c.DEBCRE = 'D'
  then b.MONPAT
else 0
end) "Débit",
sum(case
when c.SALPAT = 'A' and c.DEBCRE = 'C'
  then (b.MONSAL + b.MONPAT)
when c.SALPAT = 'S' and c.DEBCRE = 'C'
  then b.MONSAL
when c.SALPAT = 'P' and c.DEBCRE = 'C'
  then b.MONPAT
else 0
end) "Crédit"
from PRDHR.ZX00 a, PRDHR.ZX8K b
left outer join
(select za.CDCODE, zb.CRIT11, zb.SALPAT, zb.DEBCRE, zb.IDGLCO, zb.IDCSCR, zb.IDACCR, zb.IDSUBR from PRDHR.ZD00 za, PRDHR.ZD4M zb
where za.NUDOSS = zb. NUDOSS and za.CDSTCO = 'DRC' and zb.CRIT11 <> 'BAH') c
on b.CODRUB = c.CDCODE
where a.NUDOSS = b.NUDOSS
and a.MATRIC = 'P01279'
and a.PERPAI = 'MT201901'
--and b.POSAGR <> ' '
group by a.MATRIC, c.IDACCR

select a.MATRIC, b.CODRUB, b.PERPAI, b.DATRUB, b.MONSAL from DEVHR.ZX00 a,
(select * from DEVHR.ZX8K where CODRUB in ('KCS', 'KSA')) b
where a.NUDOSS = b.NUDOSS
and b.MATRIC in ('H20039F','H20192X')
order by b.PERPAI, b.DATRUB, a.MATRIC, b.CODRUB
;

select a.MATRIC, b.CODRUB, b.LIBELL, b.MONSAL, b.MONPAT
from PRDHR.ZX00 a, PRDHR.ZX8K b
where a.NUDOSS = b.NUDOSS
and a.MATRIC = 'P01279'
and a.PERPAI = 'MT201901'
and b.CODRUB in
(select za.CDCODE from PRDHR.ZD00 za, PRDHR.ZDCQ zb
where za.NUDOSS = zb.NUDOSS
and za.CDSTCO = 'DRC'
and zb.CDASSI = 'AY1')

select a.MATRIC, b.CODRUB, b.PERVAL, b.MONSAL, c.DATDEB, c.DATFIN, c.TYPCON, c.NATCON
from PRDHR.ZX00 a, PRDHR.ZX8K b, PRDHR.ZX0M c
where a.NUDOSS = b.NUDOSS
and a.NUDOSS = c.NUDOSS
and a.PERPAI = 'MT201903'
and b.CODRUB in ('A02', 'A03', '975', 'A04', 'C42', 'BC1')

select a.MATRIC, b.POSAGR, b.CODRUB, b.LIBELL, b.MONSAL, b.MONPAT, c.CRIT11, c.SALPAT, c.DEBCRE, c.IDACCR,
case
when c.SALPAT = 'A' and c.DEBCRE = 'D'
  then b.MONSAL + b.MONPAT
when c.SALPAT = 'S' and c.DEBCRE = 'D'
  then b.MONSAL
when c.SALPAT = 'P' and c.DEBCRE = 'D'
  then b.MONPAT
else 0
end "Débit",
case
when c.SALPAT = 'A' and c.DEBCRE = 'C'
  then b.MONSAL + b.MONPAT
when c.SALPAT = 'S' and c.DEBCRE = 'C'
  then b.MONSAL
when c.SALPAT = 'P' and c.DEBCRE = 'C'
  then b.MONPAT
else 0
end "Crédit"
from PRDHR.ZX00 a, PRDHR.ZX8K b
left outer join
(select za.CDCODE, zb.CRIT11, zb.SALPAT, zb.DEBCRE, zb.IDGLCO, zb.IDCSCR, zb.IDACCR, zb.IDSUBR from PRDHR.ZD00 za, PRDHR.ZD4M zb
where za.NUDOSS = zb. NUDOSS and za.CDSTCO = 'DRC' and zb.CRIT11 <> 'BAH') c
on b.CODRUB = c.CDCODE
where a.NUDOSS = b.NUDOSS
and a.MATRIC = 'P01279'
and a.PERPAI = 'MT201901'
--and b.POSAGR <> ' '
order by b.POSAGR, b.CODRUB