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
|
create or replace
PROCEDURE INTEG_TEPS
IS
PRAGMA AUTONOMOUS_TRANSACTION ;
ParamDate date:= to_date('01011950','DDMMYYYY');
SEQ_D_TEPS sequence ;
old_annee number(4):=Extract(YEAR FROM ParamDate);
old_mois number(2):=datepart('MM', ParamDate);
Begin
delete d_teps;
While ParamDate < to_date('31122025','DDMMYYYY')
Loop
insert into d_teps
(code_teps,
annee,
annee_1,
annee_en_cours,
date,
libelle_mois,
mois,mois_1,
mois_aa_1,
mois_en_cours,
qtd,
qtd_1,
semestre,
trimestre,
trimestre_1,
trimestre_aa_1,
trimestre_en_cours,
ytd,
ytd_1)
values (
sequence.SEQ_D_TEPS.nextval,
Extract(YEAR FROM ParamDate),
case when annee=old_annee-1 then 1
else 0
end as annee_1,
case when annee=old_annee then 1
else 0
end as annee_en_cours,
ParamDate as date,
to_char(extract(MONTH FROM ParamDate)),
extract (MM FROM ParamDate),
case when annee=old_annee and mois=old_mois-1 then 1
else 0
end as mois_1,
case when annee=old_annee-1 and mois=old_mois then 1
else 0
end as mois_aa_1,
case when annee=old_annee and mois=old_mois then 1
else 0
end as mois_en_cours,
null,
null,
'S'+ to_char( Ceiling((extract (MM FROM ParamDate))/6.0)),
'T'+ to_char(DatePart(Quarter, ParamDate)),
case when annee=old_annee and Ceiling(mois/3.0)= Ceiling((extract (MM FROM ParamDate))/3.0)-1 then 1
else 0
end as trimestre_1,
case when annee=old_annee-1 and Ceiling(mois/3.0)= Ceiling((extract (MM FROM ParamDate))/3.0) then 1
else 0
end as trimestre_aa_1,
case when annee=old_annee and Ceiling(mois/3.0)= Ceiling((extract (MM FROM ParamDate))/3.0) then 1
else 0
end as trimestre_en_cours,
null,
null);
ParamDate := ParamDate + 1 ;
old_annee:=old_annee+1;
old_mois:=old_mois+1;
End loop ;
commit;
END INTEG_TEPS; |
Partager