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