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
| PROCEDURE proc_liste_pratiques_epandages (
p_campagne IN VARCHAR2,
p_liste_champs IN VARCHAR2,
p_cur OUT r_cursor)
IS
BEGIN
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 (p_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; |
Partager