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
|
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Cloture]
-- Add the parameters for the stored procedure here
@iRDS int ,
@iRDev int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
END
DECLARE
@iRF int,
@iRC int,
@iRD int,
@dPM decimal(15,3),
@dDPA decimal(15,3),
@dDPV decimal(15,3),
@dQ decimal(15,3),
@dDP datetime,
@sDS varchar(19),
@dDS datetime,
@dCE decimal(11,3),
@dCS decimal(11,3)
SELECT @dDS = isnull(Date,'01/01/1900') FROM DS WHERE Ref = @iRDS
SET @sDS = convert(varchar,@dDS,3)+' '+convert(varchar,@dDS,108)
-- Cas des Prs Cnds sans dépot affecté
DECLARE CPCursor CURSOR FOR
SELECT Pr,Cnd FROM CndPr
OPEN CPCursor
FETCH NEXT FROM CPCursor INTO @iRF, @iRC
WHILE @@FETCH_STATUS=0
BEGIN
SET @dPM = 0
SET @dDPA = 0
SET @dDPV = 0
SET @dQ = 0
SET @dCE =0
SET @dCS =0
SELECT @dPM = ISNULL(PMP,0),@dDPA =isnull(DPA,0), @dQ = isnull(Qté,0), @dCE = QtéEntrée, @dCS= QtéSortie,@dDP = datepièce,@dDPV = Vente
FROM [dbo].fn_StockPr(@iRF,@iRC,@sDS,'ST',@iRDev,NULL,0)
INSERT INTO Stock (DS,Pr,Cnd,Dep,Devs,PMP,DPA,Qté,DM,CE,CS,PMPC,PMPA,DPV)
VALUES (@iRDS, @iRF,@iRC,@iRD,@iRDev,@dPM,@dDPA, @dQ, @dDP, @dCE, @dCS,NULL,@dPM,@dDPV)
FETCH NEXT FROM CPCursor INTO @iRF, @iRC
END
CLOSE CPCursor
DEALLOCATE CPCursor
-- Cas des Prs Cnd avec un dépot affecté
DECLARE CPDCursor CURSOR FOR
SELECT CP.Pr,CP.Cnd, D.Ref FROM CndPr AS CP
CROSS JOIN Dep AS D
OPEN CPDCursor
FETCH NEXT FROM CPDCursor INTO @iRF, @iRC, @iRD
WHILE @@FETCH_STATUS=0
BEGIN
SET @dPM = 0
SET @dDPA = 0
SET @dQ = 0
SET @dCE =0
SET @dCS =0
SELECT @dPM = ISNULL(PMP,0),@dDPA =isnull(DPA,0), @dQ = isnull(Qté,0), @dCE = QtéEntrée, @dCS= QtéSortie,@dDP = datepièce
FROM [dbo].fn_StockPr(@iRF,@iRC,@sDS,'STOCK',@iRDev,@iRD,0)
INSERT INTO Stock (DS,Pr,Cnd,Dep,Devs,PMP,DPA,Qté,DM,CE,CS,PMPC,PMPA,DPV)
VALUES (@iRDS, @iRF,@iRC,@iRD,@iRDev,@dPM,@dDPA, @dQ, @dDP, @dCE, @dCS,NULL,@dPM,NULL)
FETCH NEXT FROM CPDCursor INTO @iRF, @iRC, @iRD
END
CLOSE CPDCursor
DEALLOCATE CPDCursor
RETURN |
