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
|
DECLARE
(...)
BEGIN
(...)
-- recherche des za à dupliquer
<<za_loop>>
for r in (
select ID_ZA,
ID_COMBINAISON_ZT,
ID_CTC,
FLAG_VALIDITE,
HEURE_DEBUT,
FORFAIT_LUNDI,
FORFAIT_MARDI,
FORFAIT_MERCREDI,
FORFAIT_JEUDI,
FORFAIT_VENDREDI,
FORFAIT_SAMEDI,
FORFAIT_DIMANCHE,
HEURE_FIN,
DATE_CREATION,
DATE_SUPPRESSION,
DATE_MODIFICATION,
LIBELLE_ZA,
FLAG_IMPORT
from ZA
where za.date_creation <= PD_DATE_EFFET_IMPORT
and PD_DATE_EFFET_IMPORT <= za.date_suppression
and za.id_ctc = PNI_ID_CTC
)
LOOP
-- parcours des CA appartenant à la ZA
<<ca_loop>>
for s in (
select ca.id_ca,
ca.code_ca
from chantier_annexe ca, rattachement_ca_za ratt
where ratt.id_za = r.id_za
and ratt.id_ca = ca.id_ca
)
LOOP
UTL_FILE.PUT_LINE(fileHandler,'CA actuel' || s.id_ca);
duplicateZA := true;
-- nombre de double du CD
select count(*) into nbChantierAnnexe
from chantier_annexe ca
where ca.flag_import = PI_FLAG_IMPORT
and ca.code_ca = s.code_ca
and ca.date_fin is null;
if (nbChantierAnnexe != 0) then
-- recuperation de l'id du CA futur
select ca.id_ca into idChantierAnnexe
from chantier_annexe ca
where ca.flag_import = PI_FLAG_IMPORT
and ca.code_ca = s.code_ca
and ca.date_fin is null;
UTL_FILE.PUT_LINE(fileHandler,'CA futur ' || idChantierAnnexe);
-- verification que le CA actuel et le CA futur sont rattaché à la meme liste de chaine
<<test>>
for p in (
select ch.code_chaine
from chaine ch, composition_chaine cc
where ch.id_chaine = cc.id_chaine
and cc.id_ca = s.id_ca
and ( cc.flag_amont_aval = 'M' or cc.flag_amont_aval = 'V')
)
LOOP
select count(*) into nbChantierAnnexe
from chaine ch, composition_chaine cc
where ch.id_chaine = cc.id_chaine
and ( cc.flag_amont_aval = 'V' or cc.flag_amont_aval = 'M')
and cc.id_ca = idChantierAnnexe
and ch.code_chaine = p.code_chaine;
UTL_FILE.PUT_LINE(fileHandler,'count ' || nbChantierAnnexe );
if (nbChantierAnnexe = 0) then
duplicateZA := false;
EXIT ca_loop;
end if;
END LOOP test;
else
duplicateZA := false;
EXIT ca_loop;
end if ;
END LOOP ca_loop;
END LOOP za_loop;
END; |
