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
|
SELECT
reg.lib_caisse LIB_CAISSE,
bul.idf_agent MATRICULE,
his.nom_usuel || ' ' || his.nom_prenom NOM_PRENOM,
bul.cod_rub COD_RUBRIQUE,
bul.bas_rub BAS_RUB,
bul.tau_rub TAU_RUB,
bul.mnt_rub MNT_RUB
FROM rh.bulpai bul,
og.ident_his his ,
rh.agtnat nat,
rh.agtpai pai,
rh.inriaregcot reg,
rh.vue_charges vue
WHERE pai.idf_agent = nat.idf_agent
and nat.idf_agent = 21413
and pai.idf_cle = nat.idf_cle
and pai.cod_coll = nat.cod_coll
--and pai.cod_regcot = reg.cod_regime
and reg.cod_regime = (
select cod_regcot from ( select distinct cod_regcot,dat_calpai from agtpai_h where idf_agent=pai.idf_agent
and cod_regcot <> 0 and num_emploi = 1
union select distinct cod_regcot,dat_calpai from agtpai where idf_agent= pai.idf_agent
and cod_regcot <> 0 and num_emploi=1
order by dat_calpai desc )
where rownum=1
)
and nat.ident_id = his.ident_id
AND bul.idf_agent = nat.idf_agent
AND bul.idf_cle = nat.idf_cle
AND bul.cod_coll = nat.cod_coll
and pai.num_emploi =1
and pai.num_periode = 1
and bul.mnt_rub <>0
AND bul.cod_coll ='test'
AND pai.cod_coll ='test'
AND nat.cod_coll ='test'
and bul.cod_rub = vue.cod_rub
and vue.typ_cotis ='ASS'
AND to_date(to_char(his.dat_debut,'DD/MM/YYYY'),'DD/MM/YYYY') <= to_date(to_char(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')
AND to_date(to_char(his.dat_fin,'DD/MM/YYYY'),'DD/MM/YYYY') > to_date(to_char(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')
ORDER BY LIB_CAISSE,MATRICULE; |
Partager