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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
| Declare
*
ERREUR à la ligne 1 :
ORA-06511: PL/SQL : curseur déjà ouvert
ORA-06512: à ligne 17
ORA-06512: à ligne 60
LE SCRIPT :
-- Création de deux tables temporaires pour stockage des doublons
create table doublon_md as (Select a.MVI_NOART, MAX(a.MVI_NOMVTINV) AS MVI_NOMVTINV, a.mvi_novidage, a.mvi_noinvent,a.mvi_qtmvtinv
FROM MGMVI A
inner join MGMVI B
on a.mvi_noart=b.mvi_noart
and a.mvi_novidage=b.mvi_novidage
and a.mvi_noinvent=b.mvi_noinvent
and a.mvi_qtmvtinv=b.mvi_qtmvtinv
where a.mvi_nomvtinv<>b.mvi_nomvtinv
and a.mvi_noinvent=96
and a.mvi_noart!=99999999
group by a.mvi_noart,a.mvi_novidage,a.mvi_noinvent,a.mvi_qtmvtinv);
create table doublon_md1 as (select a.dvi_cdarti as article,
a.dvi_qtpdrel as qtee,
a.dvi_novidage as vidage,
a.dvi_noinvent as invent,
max(a.dvi_noordre) as ordre,
a.dvi_cdemp as emplacement
from mgdvi a
inner join mgdvi b
on a.dvi_cdarti=b.dvi_cdarti
and a.dvi_noinvent=b.dvi_noinvent
and a.dvi_qtpdrel=b.dvi_qtpdrel
and a.dvi_cdemp=b.dvi_cdemp
and a.dvi_novidage=b.dvi_novidage
where a.dvi_noordre<>b.dvi_noordre
and a.dvi_cdarti!=99999999
and a.dvi_noinvent=96
group by a.dvi_novidage,a.dvi_cdarti,a.dvi_qtpdrel,a.dvi_cdarti,a.dvi_noinvent,a.dvi_cdemp);
Declare
article_dvi MGDVI.DVI_CDARTI%TYPE;
inventaire_dvi MGDVI.DVI_NOINVENT%TYPE;
vidage_dvi MGDVI.DVI_NOVIDAGE%TYPE;
ordre_dvi MGDVI.DVI_NOORDRE%TYPE;
quantite_dvi MGDVI.DVI_QTPDREL%TYPE;
emplacement_dvi MGDVI.DVI_CDEMP%TYPE;
article_mvi MGMVI.MVI_NOART%TYPE;
inventaire_mvi MGMVI.MVI_NOINVENT%TYPE;
vidage_mvi MGMVI.MVI_NOVIDAGE%TYPE;
mouvement_mvi MGMVI.MVI_NOMVTINV%TYPE;
quantite_mvi MGMVI.MVI_QTMVTINV%TYPE;
-- Déclaration curseur MVI
cursor curs is
Select a.MVI_NOART as article_mvi, MAX(a.MVI_NOMVTINV) AS mouvement_mvi, a.mvi_noinvent as inventaire_mvi,a.mvi_qtmvtinv as quantite_mvi,a.mvi_novidage as vidage_mvi
FROM MGMVI A
inner join MGMVI B
on a.mvi_noart=b.mvi_noart
and a.mvi_novidage=b.mvi_novidage
and a.mvi_noinvent=b.mvi_noinvent
and a.mvi_qtmvtinv=b.mvi_qtmvtinv
where a.mvi_nomvtinv<>b.mvi_nomvtinv
and a.mvi_noinvent=96
and a.mvi_noart!=99999999
group by a.mvi_noart,a.mvi_novidage,a.mvi_noinvent,a.mvi_qtmvtinv
order by a.mvi_noart;
CLOSE curs;
-- Déclaration curseur DVI
cursor curs2 is
select a.dvi_cdarti as article_dvi,
a.dvi_qtpdrel as quantite_dvi,
a.dvi_novidage as vidage_dvi,
a.dvi_noinvent as inventaire_dvi,
max(a.dvi_noordre) as ordre_dvi,
a.dvi_cdemp as emplacement_dvi
from mgdvi a
inner join mgdvi b
on a.dvi_cdarti=b.dvi_cdarti
and a.dvi_noinvent=b.dvi_noinvent
and a.dvi_qtpdrel=b.dvi_qtpdrel
and a.dvi_cdemp=b.dvi_cdemp
and a.dvi_novidage=b.dvi_novidage
where a.dvi_noordre<>b.dvi_noordre
and a.dvi_cdarti!=99999999
and a.dvi_noinvent=96
group by a.dvi_novidage,a.dvi_cdarti,a.dvi_qtpdrel,a.dvi_cdarti,a.dvi_noinvent,a.dvi_cdemp
order by a.dvi_cdarti;
CLOSE curs2;
BEGIN
open curs;
LOOP
BEGIN
FETCH curs INTO article_mvi,mouvement_mvi,inventaire_mvi,quantite_mvi,vidage_mvi;
exit when curs%NOTFOUND;
Dbms_output.put_line('article supprimés :' ||article_mvi|| ' sur inventaire' ||inventaire_mvi|| 'vidage' ||vidage_mvi);
for i in curs loop
delete mgmvi a
where a.mvi_noart = i.article_mvi
and a.mvi_nomvtinv = i.mouvement_mvi
and a.mvi_novidage = i.vidage_mvi
and a.mvi_noinvent = i.inventaire_mvi
and a.mvi_noart <> 99999999 and a.mvi_cdmag=8556;
END loop;
END;
END LOOP;
CLOSE curs;
open curs2;
LOOP
BEGIN
FETCH curs2 INTO article_dvi,quantite_dvi,vidage_dvi,inventaire_dvi,ordre_dvi,emplacement_dvi;
exit when curs%NOTFOUND;
Dbms_output.put_line('article supprimés :' ||article_dvi|| ' sur inventaire' ||inventaire_dvi|| 'vidage' ||vidage_dvi);
for i in curs2 loop
delete mgdvi
where dvi_cdarti = i.article_dvi
and dvi_noordre = i.ordre_dvi
and dvi_novidage = i.vidage_dvi
and dvi_noinvent = i.inventaire_dvi
and dvi_cdemp = i.emplacement_dvi
and dvi_cdarti <> 99999999;
end loop;
end;
end loop;
CLOSE curs2;
end;
/ |
