1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
with partie_ouv as
(
select nat.idf_agent MATRICULE,
his.nom_usuel || ' ' || his.nom_prenom NOM_PRENOM,
substr(bul.cod_rub,1,3) COD_RUB,
sum(bul.bas_rub) BASE_OUVR ,
bul.tau_rub TAU_OUVR,
sum(bul.mnt_rub) MNT_OUVR
FROM og.ident_his his,
rh.agtnat nat ,
rh.agtpai pai ,
rh.bulpai bul,
rh.vue_charges vue
WHERE nat.ident_id = his.ident_id
and pai.idf_agent = nat.idf_agent
and pai.num_emploi = 1
and pai.num_periode = 1
and pai.cod_coll ='INRIA'
and nat.cod_coll ='INRIA'
and pai.cod_regret <>4
and pai.idf_agent = bul.idf_agent
--and pai.idf_agent= 75
and pai.idf_cle = bul.idf_cle
and pai.cod_regcot not in (61,62,63,64,65,66,67,68)
AND nat.cod_coll ='INRIA'
AND his.dat_debut <= sysdate
AND his.dat_fin > sysdate
and bul.cod_rub not like '8%R'
and bul.cod_rub = vue.cod_rub
and vue.typ_cotis ='IRC'
and ind_cotis ='O'
group by nat.idf_agent,his.nom_usuel ||' '|| his.nom_prenom,substr(bul.cod_rub,1,3),bul.tau_rub
ORDER BY nat.idf_agent,bul.tau_rub
),
partie_patr as
(
select nat.idf_agent MATRICULE,
his.nom_usuel || ' ' || his.nom_prenom NOM_PRENOM,
substr(bul.cod_rub,1,3) COD_RUB,
sum(bul.bas_rub) BASE_PATR ,
bul.tau_rub TAU_PATR,
sum(bul.mnt_rub) MNT_PATR
FROM og.ident_his his,
rh.agtnat nat ,
rh.agtpai pai ,
rh.bulpai bul,
rh.vue_charges vue
WHERE nat.ident_id = his.ident_id
and pai.idf_agent = nat.idf_agent
and pai.num_emploi = 1
and pai.num_periode = 1
and pai.cod_coll ='INRIA'
and nat.cod_coll ='INRIA'
and pai.cod_regret <>4
and pai.idf_agent = bul.idf_agent
--and pai.idf_agent= 75
and pai.idf_cle = bul.idf_cle
and pai.cod_regcot not in (61,62,63,64,65,66,67,68)
AND nat.cod_coll ='INRIA'
AND his.dat_debut <= sysdate
AND his.dat_fin > sysdate
and bul.cod_rub not like '8%R'
and bul.cod_rub = vue.cod_rub
and vue.typ_cotis ='IRC'
and ind_cotis ='P'
group by nat.idf_agent,his.nom_usuel ||' '|| his.nom_prenom,substr(bul.cod_rub,1,3),bul.tau_rub
ORDER BY nat.idf_agent,bul.tau_rub
)
select distinct 'IRCANTEC sans rappel' REGIME_RETRAITE,
partie_ouv.matricule,partie_ouv.nom_prenom,BASE_OUVR,TAU_OUVR,MNT_OUVR,BASE_PATR,TAU_PATR,MNT_PATR
from partie_ouv,partie_patr
where partie_ouv.MATRICULE=partie_patr.MATRICULE
and partie_ouv.cod_rub=partie_patr.cod_rub-1
and partie_patr.cod_rub=partie_ouv.cod_rub+1
and ( MNT_OUVR <> 0 or MNT_PATR <> 0)
order by partie_ouv.matricule,tau_ouvr; |
