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 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
|
CREATE OR REPLACE PROCEDURE P_DEL_DOUBLONS_FDET_DET_HISTO(p_dir_log IN VARCHAR2, p_fic_log IN VARCHAR2)
IS
FILE_LOG UTL_FILE.FILE_TYPE; -- journal de traces
NB_DET_BEFORE INTEGER ;
NB_DET_AFTER INTEGER ;
NB_DET_PURGE INTEGER ;
chaine VARCHAR2(200);
PROCEDURE tracer_info_operation(p_chaine IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUTF(FILE_LOG,'\n' || p_chaine);
END;
FUNCTION f_tab_count(p_nom_tab IN VARCHAR2) RETURN INTEGER
IS
retval INTEGER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_nom_tab INTO retval;
RETURN retval;
END;
BEGIN
FILE_LOG := UTL_FILE.FOPEN(p_dir_log,p_fic_log,'W');
NB_DET_BEFORE := f_tab_count('fdet_det_histo');
chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo avant purge : ' || NB_DET_BEFORE;
tracer_info_operation(chaine);
DELETE FROM fdet_det_histo T1
WHERE EXISTS ( SELECT 1
FROM fdet_det_histo T2
WHERE T1.num_ecro_ini = T2.num_ecro_ini
AND T1.cod_typ_evt_pen = T2.cod_typ_evt_pen
AND T1.cod_cat_pen = T2.cod_cat_pen
AND NVL(T1.dcod_nbr_aff,'zwx') = NVL(T2.dcod_nbr_aff,'zwx')
AND NVL(T1.cod_proc_pen,'zwx') = NVL(T2.cod_proc_pen,'zwx')
AND NVL(T1.cod_tran_age,'zwx') = NVL(T2.cod_tran_age,'zwx')
AND NVL(T1.cod_cat_adm,'zwx') = NVL(T2.cod_cat_adm,'zwx')
AND NVL(T1.num_ecro_cou,-99) = NVL(T2.num_ecro_cou,-99)
AND NVL(T1.cod_etab_penit,'zwx') = NVL(T2.cod_etab_penit,'zwx')
AND NVL(T1.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.nbr_enf_det,-99) = NVL(T2.nbr_enf_det,-99)
AND NVL(T1.nbr_enf_deten,-99) = NVL(T2.nbr_enf_deten,-99)
AND NVL(T1.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.ind_fran_det,'W') = NVL(T2.ind_fran_det,'W')
AND NVL(T1.ind_incar,'W') = NVL(T2.ind_incar,'W')
AND NVL(T1.sexe,'W') = NVL(T2.sexe,'W')
AND NVL(T1.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY'))
AND NVL(T1.qtm_ii_val,'W') = NVL(T2.qtm_ii_val,'W')
AND NVL(T1.qtm_hh_val,-99) = NVL(T2.qtm_hh_val,-99)
AND NVL(T1.qtm_mm_val,-99) = NVL(T2.qtm_mm_val,-99)
AND NVL(T1.qtm_jj_val,-99) = NVL(T2.qtm_jj_val,-99)
AND NVL(T1.qtm_mm_pen,-99) = NVL(T2.qtm_mm_pen,-99)
AND NVL(T1.qtm_aa_pen,'zwx') = NVL(T2.qtm_aa_pen,'zwx')
AND NVL(T1.qtm_ss_pen,-99) = NVL(T2.qtm_ss_pen,-99)
AND NVL(T1.qtm_hh_pen,-99) = NVL(T2.qtm_hh_pen,-99)
AND NVL(T1.qtm_jj_pen,-99) = NVL(T2.qtm_jj_pen,-99)
AND NVL(T1.sum_qtm_aa_pen,-99) = NVL(T2.sum_qtm_aa_pen,-99)
AND NVL(T1.sum_qtm_mm_pen,-99) = NVL(T2.sum_qtm_mm_pen,-99)
AND NVL(T1.sum_qtm_ss_pen,-99) = NVL(T2.sum_qtm_ss_pen,-99)
AND NVL(T1.sum_qtm_hh_pen,-99) = NVL(T2.sum_qtm_hh_pen,-99)
AND NVL(T1.sum_qtm_jj_pen,-99) = NVL(T2.sum_qtm_jj_pen,-99)
AND NVL(T1.sum_aa_rem_pen,-99) = NVL(T2.sum_aa_rem_pen,-99)
AND NVL(T1.sum_mm_rem_pen,-99) = NVL(T2.sum_mm_rem_pen,-99)
AND NVL(T1.sum_ss_rem_pen,-99) = NVL(T2.sum_ss_rem_pen,-99)
AND NVL(T1.sum_hh_rem_pen,-99) = NVL(T2.sum_hh_rem_pen,-99)
AND NVL(T1.sum_jj_rem_pen,-99) = NVL(T2.sum_jj_rem_pen,-99)
AND NVL(T1.age_det,-99) = NVL(T2.age_det,-99)
AND NVL(T1.ind_gide,'W') = NVL(T2.ind_gide,'W')
AND NVL(T1.libl_typ_evt_pen,'zxw') = NVL(T2.libl_typ_evt_pen,'zwx')
AND NVL(T1.libc_cat_pen,'zwx') = NVL(T2.libc_cat_pen,'zwx')
AND NVL(T1.dlibl_nbr_aff,'zwx') = NVL(T2.dlibl_nbr_aff,'zwx')
AND NVL(T1.libc_proc_pen,'zwx') = NVL(T2.libc_proc_pen,'zwx')
AND NVL(T1.libc_tran_age,'zwx') = NVL(T2.libc_tran_age,'zwx')
AND NVL(T1.libl_cat_adm,'zwx') = NVL(T2.libl_cat_adm,'zwx')
AND NVL(T1.grpevt_pen_det,-9) = NVL(T2.grpevt_pen_det,-9)
AND NVL(T1.qtm_tot_pen,-99) = NVL(T2.qtm_tot_pen,-99)
AND NVL(T1.qtm_tot_val,-99) = NVL(T2.qtm_tot_val,-99)
AND NVL(T1.cod_tra_peine,'zwx') = NVL(T2.cod_tra_peine,'')
AND NVL(T1.libl_tra_peine,'zwx') = NVL(T2.libl_tra_peine,'zwx')
AND NVL(T1.cod_natio,'zwx') = NVL(T2.cod_natio,'zwx')
AND NVL(T1.libl_natio,'zwx') = NVL(T2.libl_natio,'zwx')
AND NVL(T1.cod_typ_mvt,'zwx') = NVL(T2.cod_typ_mvt,'zwx')
AND NVL(T1.libc_typ_mvt,'zwx') = NVL(T2.libc_typ_mvt,'zwx')
AND NVL(T1.cod_mot_lib,'zwx') = NVL(T2.cod_mot_lib,'zwx')
AND NVL(T1.cod_typ_mes,'zwx') = NVL(T2.cod_typ_mes,'zwx')
AND NVL(T1.cod_niv_ins,'zwx') = NVL(T2.cod_niv_ins,'zwx')
AND NVL(T1.libl_niv_ins,'zwx') = NVL(T2.libl_niv_ins,'zwx')
AND NVL(T1.libl_typ_mes,'zwx') = NVL(T2.libl_typ_mes,'zwx')
AND NVL(T1.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY')) = NVL(T2.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY'))
AND T2.dat_der_modif = ( SELECT MAX(T3.dat_der_modif)
FROM fdet_det_histo T3
WHERE T3.num_ecro_ini = T2.num_ecro_ini
AND T3.dat_der_modif < T1.dat_der_modif
)
);
COMMIT;
NB_DET_AFTER := SQL%ROWCOUNT;
NB_DET_PURGE := NB_DET_BEFORE - NB_DET_AFTER;
chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo apres purge : ' || NB_DET_PURGE;
tracer_info_operation(chaine);
UTL_FILE.FCLOSE(FILE_LOG);
EXCEPTION
WHEN OTHERS
THEN ROLLBACK;
chaine:='Probleme lors de la purge de fdet_det_histo ' || SQLERRM(SQLCODE);
tracer_info_operation(chaine);
UTL_FILE.FCLOSE(FILE_LOG);
END P_DEL_DOUBLONS_FDET_DET_HISTO;
/ |
Partager