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
|
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
/* Stored procedures */
CREATE PROCEDURE "SP_TREE_SORT_VIEW_ANM"
(
"BG_SUIV" INTEGER,
"BG_P" INTEGER,
"BD_P" INTEGER,
"NIVEAU_P" INTEGER,
"RECURS" SMALLINT
)
RETURNS
(
"ID_S" INTEGER,
"BG_S" INTEGER,
"BD_S" INTEGER,
"NIVEAU_S" INTEGER,
"REF_ID_S" INTEGER,
"QTE_S" DOUBLE PRECISION,
"REF_ART_S" VARCHAR(32),
"ANM_DATE_VALIDITE" TIMESTAMP
)
AS
BEGIN EXIT; END ^
ALTER PROCEDURE "SP_TREE_SORT_VIEW_ANM"
(
"BG_SUIV" INTEGER,
"BG_P" INTEGER,
"BD_P" INTEGER,
"NIVEAU_P" INTEGER,
"RECURS" SMALLINT
)
RETURNS
(
"ID_S" INTEGER,
"BG_S" INTEGER,
"BD_S" INTEGER,
"NIVEAU_S" INTEGER,
"REF_ID_S" INTEGER,
"QTE_S" DOUBLE PRECISION,
"REF_ART_S" VARCHAR(32),
"ANM_DATE_VALIDITE" TIMESTAMP
)
AS
--declare variable BG_Suiv integer;
declare variable BG integer;
declare variable BD integer;
declare variable Niveau integer;
-- declare variable REF_ART varchar(32);
-- declare variable BD_P integer;
-- declare variable BG_P integer;
-- declare variable Niveau_P integer;
declare variable ID integer;
declare BD_Suiv integer;
declare Ecart integer;
declare Deplacement integer;
declare REF_ID integer;
declare Qte double precision;
declare Ref varchar(32);
begin
-- BG_Suiv=BG_P;
for select ANM_ID,ANM_BG,ANM_BD,REF_ART,ANM_QTE,ANM_NIVEAU,ANM_REF_ID,ANM_DATE_VALIDITE
from Art_Nomenclature inner join Article on Article_ID=ANM_REF_ID
where ANM_BD<:BD_P and ANM_BG>:BG_P and ANM_Niveau=:Niveau_P+1
order by REF_ART
into :ID,:BG,:BD,:REF,:QTE,:NIVEAU,:REF_ID,:ANM_DATE_VALIDITE
do
begin
Ecart=BD-BG;
BG_Suiv=BG_Suiv+1;
Deplacement=BG-BG_Suiv;
REF_ID_S= REF_ID;
NIVEAU_S=NIVEAU;
QTE_S=QTE;
REF_ART_S=REF;
BG_S=BG_SUIV;
BD_Suiv=BG_Suiv+Ecart;
BD_S=BD_SUIV;
ID_S=ID;
suspend;
if (Ecart>1) then
begin
if (RECURS=1) then
for select ID_S,BG_S,BD_S,Niveau_S,REF_ID_S,QTE_S,REF_ART_S,:ANM_DATE_VALIDITE
from SP_TREE_SORT_VUE_ANM(:BG_SUIV,:BG,:BD,:NIVEAU_S,:RECURS)
into :ID_S,:BG_S,:BD_S,:Niveau_S,:REF_ID_S,:QTE_S,:REF_ART_S,:ANM_DATE_VALIDITE
do
begin
suspend;
end
end --end Ecart>1
BG_Suiv=BD_Suiv;
end --end for
end
^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON; |
Partager