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
|
insert into EVP65.ELTVAR_H (ELTVAR_H.COD_COLL, ELTVAR_H.IDF_AGENT, ELTVAR_H.IDF_CLE, ELTVAR_H.NUM_EMPLOI, ELTVAR_H.NUM_PAIE, ELTVAR_H.DAT_CALPAI,
ELTVAR_H.DAT_DEBUT, ELTVAR_H.DAT_FIN, ELTVAR_H.COD_RUB, ELTVAR_H.MNT_ELT, ELTVAR_H.NBR_ELT, ELTVAR_H.TAU_ELT, ELTVAR_H.COD_ELT, ELTVAR_H.IND_ORIGINE,
ELTVAR_H.DAT_MAJ_J, ELTVAR_H.HEU_MAJ, ELTVAR_H.COD_VENT_BUDG, ELTVAR_H.TYP_VENT_BUDG, ELTVAR_H.NUM_CRIT, ELTVAR_H.DAT_DEB_REF, ELTVAR_H.DAT_FIN_REF,
ELTVAR_H.DAT_MAJ, ELTVAR_H.USER_MAJ)
WITH MaTable AS
(select 'CG65', e.AGE_CDN as idf_agent,
a.AGE_IDF_CLE_LB as idf_cle,
e.EV_NUM_EMPLOI_NB as num_emp,
1,
to_number(to_char(e.EV_DEBUT_DT,'J')) as dat_calpai,
to_number(to_char(e.EV_DEBUT_DT,'J')) as dat_debut,
to_number(to_char(e.EV_FIN_DT,'J')) as dat_fin,
r.RUB_CODE_LB as cod_rub,
e.EV_MONTANT_NB,
e.EV_NOMBRE_NB,
e.EV_TAUX_NB,
e.ELT_CDA as cod_elt,
e.EV_IND_ORIGINE_CM,
null, null, null, null, null, null, null,
e.EV_MODIF_DT,
e.EV_MODIF_LB
from EVP65.ELEMENT_VARIABLE e, EVP65.AGENT a, EVP65.RUBRIQUE r
where e.AGE_CDN = a.AGE_CDN (+)
and e.RUB_CDN = r.RUB_CDN (+)
and e.EV_CDN in (select e.EV_CDN
from EVP65.ELEMENT_VARIABLE e
where e.EV_RAPPEL_ON = 'O'
and e.EV_VALIDCTRL_ON = 'O'
and e.EV_VALIDGEST_ON = 'O'
and e.EV_INJECTEE_ON = 'N'))
SELECT 'CG65',idf_agent,idf_cle,num_emp,1,
to_number(to_char(greatest(to_date(DAT_DEBUT,'J'), trunc(add_months(to_date(DAT_DEBUT,'J'), level - 1), 'mm')),'J')) AS dat_calpai,
to_number(to_char(greatest(to_date(DAT_DEBUT,'J'), trunc(add_months(to_date(DAT_DEBUT,'J'), level - 1), 'mm')),'J')) AS date_debut,
to_number(to_char(least(to_date(DAT_FIN,'J'), last_day(add_months(to_date(DAT_FIN,'J'), level - 1 - months_between(trunc(to_date(DAT_FIN,'J'), 'mm'), trunc(to_date(DAT_DEBUT,'J'), 'mm'))))),'J'))+1 AS date_fin,
cod_rub,
EV_MONTANT_NB,
EV_NOMBRE_NB,
EV_TAUX_NB,
cod_elt,
EV_IND_ORIGINE_CM,
null, null, null, null, null, null, null,
EV_MODIF_DT,
EV_MODIF_LB
FROM MaTable
CONNECT BY level <= months_between(trunc(to_date(DAT_FIN,'J'), 'mm'), trunc(to_date(DAT_DEBUT,'J'), 'mm')) + 1
AND PRIOR idf_agent=idf_agent
AND PRIOR cod_rub=cod_rub
AND prior num_emp=num_emp
AND PRIOR sys_guid() IS NOT NULL
ORDER BY 2,6; |
Partager