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