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