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; |
Partager