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
| PROCEDURE proc_liste_pratiques_epandages (
p_campagne IN VARCHAR2,
p_liste_champs IN VARCHAR2,
p_cur OUT r_cursor)
IS
TYPE tab_liste_champs IS TABLE OF t_champs.id_champ%TYPE;
CURSOR r_liste_champs
IS
WITH t AS (SELECT p_liste_champs c FROM DUAL)
SELECT EXTRACTVALUE (COLUMN_VALUE, '/x') AS id_champ
FROM t,
TABLE (
XMLSEQUENCE (
EXTRACT (
XMLTYPE (
'<list><x>'
|| REPLACE (c, '|', '</x><x>')
|| '</x></list>'),
'/list/x')));
v_liste_champs tab_liste_champs;
v_row t_champs.id_champ%TYPE;
i NUMBER := 0;
BEGIN
/* Remplissage d'un tableau avec la liste des champs passés en paramètre */
OPEN r_liste_champs;
LOOP
FETCH r_liste_champs INTO v_row;
v_liste_champs.EXTEND;
v_liste_champs (i) := v_row;
i := i + 1;
END LOOP;
CLOSE r_liste_champs;
/* Fin remplissage */
OPEN p_cur FOR
SELECT DISTINCT
champ.id_champ,
lot_boue.dp,
lot_boue.dr,
champ.num_sdea AS num_sdea_champ,
liste_parcelles.id_parcelle AS id_parcelle,
liste_parcelles.num_sdea AS num_sdea_parcelle,
champ.ilot_num_pac AS num_ilot,
champ.cad_lieu_dit AS lieu_dit,
com.nom AS commune,
exploit.id_exploitation,
exploit.nom_exploitation,
champ.champ_surf_declaree AS surface_totale,
DECODE (liste_parcelles.num_sdea,
'', '',
liste_parcelles.surface)
AS surface_parcelle,
DECODE (liste_parcelles.num_sdea,
'', champ.champ_surf_epandable,
liste_parcelles.surface)
AS surface_epandable,
DECODE (liste_parcelles.num_sdea,
'', champ.champ_surf_epandable,
liste_parcelles.surface)
AS surface_a_epandre,
type_cultures.id_culture_avant,
type_cultures.lib_culture_avant,
type_cultures.id_culture_intermediaire,
type_cultures.lib_culture_intermediaire,
type_cultures.id_culture_apres,
type_cultures.lib_culture_apres
FROM t_champs champ,
t_exploitations exploit,
v_communes com,
epf.lpe lot_boue,
t_campagnes_epandages camp,
( SELECT DISTINCT id_parcelle,
id_champ,
surface,
num_sdea
FROM t_parcelles_culturales
ORDER BY id_champ, num_sdea) liste_parcelles,
( SELECT DISTINCT
pc.id_parcelle,
pc.id_champ,
pc.dte_fin,
pc.avant AS id_culture_avant,
tc_avant.lib AS lib_culture_avant,
pc.intermediaire AS id_culture_intermediaire,
tc_cipan.lib AS lib_culture_intermediaire,
pc.apres AS id_culture_apres,
tc_apres.lib AS lib_culture_apres
FROM t_parcelles_culturales pc,
t_type_culture tc_avant,
t_type_culture tc_cipan,
t_type_culture tc_apres
WHERE tc_avant.id_culture = pc.avant
AND tc_cipan.id_culture = pc.intermediaire
AND tc_apres.id_culture = pc.apres
AND pc.dte_fin IS NULL
ORDER BY id_champ, id_parcelle) type_cultures
WHERE champ.id_champ IN (v_liste_champs)
AND camp.id_campagne = p_campagne
AND lot_boue.id = camp.lot_boues
AND champ.id_exploitation = exploit.id_exploitation
AND com.id = champ.idtcom
AND liste_parcelles.id_champ(+) = champ.id_champ
AND type_cultures.id_parcelle(+) =
liste_parcelles.id_parcelle
ORDER BY id_champ, num_sdea_champ, num_sdea_parcelle;
END; |
