· 6 years ago · Aug 23, 2019, 11:22 AM
1USE [Dobrogea]
2GO
3/****** Object: StoredProcedure [dbo].[RecalulPretCmp_StocInternFaraStoc] Script Date: 8/23/2019 2:17:22 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11--USE [Dobrogea]
12--GO
13--/****** Object: StoredProcedure [dbo].[RecalulPretCmp_StocInternFaraStoc] Script Date: 11/16/2018 3:12:54 PM ******/
14--SET ANSI_NULLS ON
15--GO
16--SET QUOTED_IDENTIFIER ON
17--GO
18ALTER Procedure [dbo].[RecalulPretCmp_StocInternFaraStoc]
19 @SYS_UNITID INT = NULL
20 ,@SYS_DIVID INT = NULL
21 ,@DataInceputCalcul DATE =NULL
22 ,@SYS_LANGID VARCHAR(10) = NULL
23 ,@SYS_USERID INT = NULL
24 ,@StocSfLuna BIT = NULL
25 ,@StocZilnic BIT = NULL
26 ,@ProdusId int =null
27as
28BEGIN
29DECLARE @IntrPozId int
30
31BEGIN TRY
32BEGIN TRANSACTION
33
34 BEGIN
35
36
37 IF(OBJECT_ID('tempdb..#Produse') IS NOT NULL) DROP TABLE #Produse
38
39 --SELECT pd.Produsid
40 --INTO #Produse
41 --FROM
42 -- tblProduseDivizii pd (NOLOCK)
43 -- where ContabProdusId in (79,86,87,88) --and isnull(inactiv,0) = 0
44 -- and divizieid=1
45 -- and @SYS_UNITID=2
46 -- and 1=2
47
48 IF(@DataInceputCalcul IS NULL)
49 BEGIN
50 SELECT @DataInceputCalcul = ISNULL(MIN(Data),GETDATE())
51 FROM dbo.tblDataCmp (NOLOCK)
52 WHERE Firmaid = @SYS_UNITID
53 AND DivizieId = @SYS_DIVID
54 AND ISNULL(Prelucrat,0) = 0
55 END
56
57 DECLARE
58 @IsOpenedData BIT
59 , @eroarePerioada NVARCHAR(500)
60 , @DocId INT
61 , @Setare420 NVARCHAR(MAX)
62 , @SYS_PARTID INT
63
64 SELECT @SYS_PARTID = PartenerId
65 FROM dbo.vwUnitati (NOLOCK)
66 WHERE UnitateId = @SYS_UNITID
67
68 SELECT @setare420 = dbo.ValoareSetare(420,@SYS_UNITID,@SYS_DIVID)
69
70 SELECT @DocId = DocId
71 FROM dbo.tblDataCmp (NOLOCK)
72 WHERE Firmaid = @SYS_UNITID
73 AND DivizieId = @SYS_DIVID
74 AND ISNULL(Prelucrat,0) = 0
75
76 SELECT @eroarePerioada = SysErp.ERP.textdictionary('Luna inchisa',@SYS_LANGID)
77 SELECT @IsOpenedData = 1--[dbo].[Fct_OpenPeriod](@DataInceputCalcul,0,@SYS_USERID,@SYS_UNITID,@SYS_DIVID,4)
78
79 IF(@IsOpenedData = 0 )--AND @Setare420 = 'CMP')
80 BEGIN
81 SET @eroarePerioada = @eroarePerioada + ' ' + CONVERT(NVARCHAR(10),@DataInceputCalcul,103)
82
83 IF @eroarePerioada IS NULL
84 SET @eroarePerioada = SysErp.ERP.textdictionary('Luna inchisa',@SYS_LANGID)
85
86 RAISERROR(@eroarePerioada,16,1)
87 END
88
89 UPDATE dbo.tblDataCmp
90 SET Prelucrat = -1
91 WHERE Firmaid = @SYS_UNITID
92 AND DivizieId = @SYS_DIVID
93
94 /*definire table*/
95 IF(OBJECT_ID('tempdb..#Err') IS NOT NULL) DROP TABLE #Err
96 IF(OBJECT_ID('tempdb..#IntrPoz') IS NOT NULL) DROP TABLE #IntrPoz
97 IF(OBJECT_ID('tempdb..#Iespoz') IS NOT NULL) DROP TABLE #Iespoz
98 IF(OBJECT_ID('tempdb..#TrsfPoz') IS NOT NULL) DROP TABLE #TrsfPoz
99 IF(OBJECT_ID('tempdb..#returFurnizor') IS NOT NULL) DROP TABLE #returFurnizor
100 IF(OBJECT_ID('tempdb..#StocInitial') IS NOT NULL) DROP TABLE #StocInitial
101 IF(OBJECT_ID('tempdb..#DataIntrare') IS NOT NULL) DROP TABLE #DataIntrare
102 IF(OBJECT_ID('tempdb..#sorderpoz') IS NOT NULL) DROP TABLE #sorderpoz
103 IF(OBJECT_ID('tempdb..#Stocbalanta') IS NOT NULL) DROP TABLE #Stocbalanta
104 IF(OBJECT_ID('tempdb..#ReturClient') IS NOT NULL) DROP TABLE #ReturClient
105 IF(OBJECT_ID('tempdb..#pt') IS NOT NULL) DROP TABLE #pt
106 IF(OBJECT_ID('tempdb..#cmp2') IS NOT NULL) DROP TABLE #cmp2
107 IF(OBJECT_ID('tempdb..#StocInitial2') IS NOT NULL) DROP TABLE #StocInitial2
108 IF(OBJECT_ID('tempdb..#StocInitialref') IS NOT NULL) DROP TABLE #StocInitialref
109 IF(OBJECT_ID('tempdb..#stoc') IS NOT NULL) DROP TABLE #stoc
110 IF(OBJECT_ID('tempdb..#StocLaData') IS NOT NULL) DROP TABLE #StocLaData
111 IF(OBJECT_ID('tempdb..#IesiriCumulate') IS NOT NULL) DROP TABLE #IesiriCumulate
112 IF(OBJECT_ID('tempdb..#IntrariCumulate') IS NOT NULL) DROP TABLE #IntrariCumulate
113 IF(OBJECT_ID('tempdb..#TrsfNew') IS NOT NULL) DROP TABLE #TrsfNew
114
115 CREATE TABLE #stoc
116 (
117 ProdusId INT
118
119
120 , GestiuneId INT
121 , StocInitial DECIMAL(22,8)
122 , Stoc DECIMAL(22,8)
123 , PretCmp DECIMAL(22,8)
124 , PretCost DECIMAL(22,8)
125 , CantIntrare DECIMAL(22,8)
126 , CantIesire DECIMAL(22,8)
127 , CantTransferataPozitiv DECIMAL(22,8)
128 , CantTransferataNegativ DECIMAL(22,8)
129 , CantRezervata DECIMAL(22,8)
130 , DataIntr DATE
131 , ValoareCost DECIMAL(22,8)
132 )
133
134 CREATE TABLE #stocladata
135 (
136 ProdusId INT
137
138 , GestiuneId INT
139 , StocInitial DECIMAL(22,8)
140 , Stoc DECIMAL(22,8)
141 , PretCmp DECIMAL(22,8)
142 , PretCost DECIMAL(22,8)
143 , DataIntr DATE
144 , ValoareCost DECIMAL(22,8)
145 , Data DATE
146 )
147
148 CREATE TABLE #IntrPoz
149 (
150 ProdusId INT
151
152 , GestiuneId INT
153 , CantIntrare DECIMAL(22,8)
154 , PretCost DECIMAL(22,8)
155 , ValoareCost decimal(22,8)
156 , DataIntr DATE
157 , Prelucrat BIT
158 , DataInceput DATE
159 , DataSfarsit DATE
160 )
161
162 CREATE TABLE #IesPoz
163 (
164 IesPozId INT
165 , ProdusId INT
166
167 , GestiuneId INT
168 , Stoc DECIMAL(22,8)
169 , PretCmp DECIMAL(22,8)
170 , PretcmpVechi DECIMAL(22,8)
171 , PretCost DECIMAL(22,8)
172 , CantIesire DECIMAL(22,8)
173 , ValoareCost DECIMAL(22,8)
174 , dataIntr DATE
175 , Prelucrat BIT
176 , DataInceput DATE
177 , DataSfarsit DATE
178 )
179
180 CREATE TABLE #IesiriCumulate
181 (
182 ProdusId INT
183
184 , GestiuneId INT
185 , CantIesire DECIMAL(22,8)
186 , PretCost DECIMAL(22,8)
187 , ValoareCost DECIMAL(22,8)
188 , PretCmp DECIMAL(22,8)
189 , Prelucrat BIT
190 , Data DATE
191 )
192
193 CREATE TABLE #IntrariCumulate
194 (
195 ProdusId INT
196
197 , GestiuneId INT
198 , CantIntrare DECIMAL(22,8)
199 , PretCost DECIMAL(22,8)
200 , Prelucrat BIT
201 , Data DATE
202 , ValoareCost DECIMAL(22,8)
203 )
204
205 CREATE TABLE #returClient
206 (
207 IesPozId INT
208 , ProdusId INT
209
210 , GestiuneId INT
211 , Stoc DECIMAL(22,8)
212 , PretCmp DECIMAL(22,8)
213 , PretcmpVechi DECIMAL(22,8)
214 , PretCost DECIMAL(22,8)
215 , CantIesire DECIMAL(22,8)
216 , ValoareCost DECIMAL(22,8)
217 , dataIntr DATE
218 , Prelucrat BIT
219 , DataInceput DATE
220 , DataSfarsit DATE
221 )
222
223 CREATE TABLE #SorderPoz
224 (
225 SorderPozId INT
226 , ProdusId INT
227
228 , GestiuneId INT
229 , Stoc DECIMAL(22,8)
230 , PretCmp DECIMAL(22,8)
231 , PretcmpVechi DECIMAL(22,8)
232 , PretCost DECIMAL(22,8)
233 , CantIesire DECIMAL(22,8)
234 , ValoareCost DECIMAL(22,8)
235 , dataIntr DATE
236 , Prelucrat BIT
237 , DataInceput DATE
238 , DataSfarsit DATE
239 )
240
241 CREATE TABLE #TrsfPoz
242 (
243 TrsfPozId INT
244 , ProdusId INT
245
246 , GestiuneOldId INT
247
248 , PretCost DECIMAL(22,8)
249 , PretCmp DECIMAL(22,8)
250 , CantTrsf DECIMAL(22,8)
251 , ValoareCost DECIMAL(22,8)
252 , DataIntr DATE
253 , Prelucrat BIT
254 , DataInceput DATE
255 , DataSfarsit DATE
256 )
257
258 CREATE TABLE #returFurnizor
259 (
260 IntrPozId INT
261 , ProdusId INT
262 , IntrPozRefId INT
263
264 , GestiuneId INT
265 , CantIntrare DECIMAL(22,8)
266 , PretCost DECIMAL(22,8)
267 , PretCmp DECIMAL(22,8)
268 , ValoareCost DECIMAL(22,8)
269 , DataIntr DATE
270 , Prelucrat BIT
271 , DataInceput DATE
272 , DataSfarsit DATE
273 )
274
275 CREATE TABLE #TrsfNew
276 (
277 ProdusId INT
278
279 , GestiuneNewId INT
280
281 , DataFabricare DATE
282 , CantTrsf DECIMAL(18,5)
283 , PretCost DECIMAL(18,5)
284 , ValoareCost DECIMAL(22,8)
285 , DataIntr DATE
286 , Prelucrat BIT
287 , DataInceput DATE
288 , DataSfarsit DATE
289 )
290
291
292 CREATE NONCLUSTERED INDEX Ix_Iespoz_Prelucrat ON #IesPoz ([Prelucrat])INCLUDE ([ProdusId],[GestiuneId],[PretCmp],[PretCost],[CantIesire])
293 CREATE NONCLUSTERED INDEX Ix_Iespoz_IesPozId ON #IesPoz (IesPozId)
294 DECLARE @DataPornire DATE
295
296 DELETE FROM inv.StocLaData
297 WHERE Firmaid = @SYS_UNITID
298 AND DivizieId = @SYS_DIVID
299 AND Data >= @DataInceputCalcul
300 and intrpozid is null
301 and (@ProdusId is null or produsid=@ProdusId)
302 --and produsid not in (select produsid from #Produse)
303
304
305
306 IF NOT EXISTS
307 (
308 SELECT TOP 1 *
309 FROM inv.StocLaData (NOLOCK)
310 WHERE FirmaId = @SYS_UNITID
311 AND DivizieId = @SYS_DIVID
312 and (@ProdusId is null or produsid=@ProdusId)
313 -- and produsid not in (select produsid from #Produse)
314
315 )
316 BEGIN
317 SELECT @DataPornire = DATEADD(DD,-1,@DataInceputCalcul)
318
319 INSERT INTO #stoc
320 (
321 ProdusId
322
323 , GestiuneId
324 , StocInitial
325 , Stoc
326 , ValoareCost
327 , PretCost
328 , DataIntr
329 )
330 SELECT
331 s.ProdusId
332
333 , s.GestiuneId
334 , SUM(s.Stoc) AS Stoc
335 , SUM(s.Stoc) AS Stoc
336 , SUM(s.Stoc * s.PretCost) AS ValoareCost
337 , MIN(s.PretCost) AS PretCost
338 , MAX(s.DataIntr) AS DataIntr
339 --FROM [inv].[StocFunctionClientLaData](@SYS_UNITID, @SYS_DIVID, @SYS_PartID, @DataPornire, 0)
340 FROM [inv].[StocFunctionLaData](@SYS_UNITID, @SYS_DIVID, @DataPornire)s
341 --left join #produse p on p.produsid=s.produsid
342 --where p.produsid is null
343 where (@ProdusId is null or produsid=@ProdusId)
344 GROUP BY
345 s.ProdusId
346
347 , GestiuneId
348
349 END
350 ELSE
351 BEGIN
352 SELECT TOP 1 @DataPornire = s.Data
353 FROM inv.StocLaData s (NOLOCK)
354 --left join #produse p on p.produsid=s.produsid
355 where
356 s.Firmaid = @SYS_UNITID
357 AND s.DivizieId = @SYS_DIVID
358 AND s.Data < @DataInceputCalcul
359 and (@ProdusId is null or s.produsid=@ProdusId)
360 -- ANd p.produsid is null
361 ORDER BY s.Data DESC
362
363--- select @DataPornire
364 INSERT INTO #stoc
365 (
366 ProdusId
367
368 , GestiuneId
369 , StocInitial
370 , Stoc
371 , ValoareCost
372 , PretCost
373 , DataIntr
374 )
375 SELECT
376 s.ProdusId
377
378 , s.GestiuneId
379 , SUM(s.stoc) as Stoc
380 , SUM(s.stoc) as Stoc
381 , SUM(s.pretcost*s.stoc) as ValoareCost
382 , MIN(s.PretCost)
383 , MAX(s.DataIntr) as DataIntr
384 FROM inv.StocLaData s (NOLOCK)
385 -- left join #produse p on p.produsid=s.produsid
386 WHERE FirmaId = @SYS_UNITID
387 AND DivizieId = @SYS_DIVID
388 AND Data = @DataPornire
389 and intrpozid is null
390 and (@ProdusId is null or s.produsid=@ProdusId)
391 --and p.produsid is null
392 GROUP BY
393 s.ProdusId
394
395 , s.GestiuneId
396 --, ClientcustodieId
397 END
398
399 SELECT
400 ProdusId
401 , SUM(ValoareCost) AS ValoareCost
402 , SUM(Stoc) AS Stoc
403 INTO #pt
404 FROM #stoc
405 GROUP BY Produsid
406
407 SELECT
408 ProdusId
409 , Valoarecost/Stoc AS PretCmp
410 , Stoc
411 INTO #cmp2
412 FROM #pt
413 WHERE Stoc > 0
414
415 UPDATE s
416 SET s.PretCmp = a.PretCmp
417 FROM #Stoc s
418 INNER JOIN #cmp2 a
419 ON a.ProdusId = s.Produsid
420
421 --DELETE FROM inv.StocLaData
422 --WHERE FirmaId = @SYS_UNITID
423 -- AND DivizieId = @SYS_DIVID
424 -- AND Data >= @DataInceputCalcul
425
426 SELECT
427 a.DataValidare
428 , 0 as Prelucrat
429 INTO #DataIntrare
430 FROM
431 (
432 SELECT DISTINCT
433 c.DataIntr AS DataValidare
434 FROM inv.IntrCap c (NOLOCK)
435 INNER JOIN inv.TipDoc t (NOLOCK)
436 ON t.TipDocId = c.TipDocId
437 INNER JOIN inv.IntrPoz p (NOLOCK)
438 ON p.IntrCapId = c.IntrCapId
439 --left join #produse pt on pt.produsid=p.produsid
440 --AND ISNULL(t.stoc,0) = 1
441 WHERE c.FirmaId = @SYS_UNITID
442 AND c.DivizieId = @SYS_DIVID
443 AND c.DataAnulare IS NULL
444 AND c.DataValidare IS NOT NULL
445 AND c.DataIntr >= @DataInceputCalcul
446 AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
447 AND (@IntrPozId IS NULL OR p.IntrPozId = @IntrPozId)
448 AND p.IntrPozRefId IS NULL
449 --+++++++++++++++++++++++++++++++++++++
450 AND p.IntrPozAvizId IS NULL
451 --AND ISNULL(p.FaraStoc,0) = 1
452 AND p.GestiuneId IS NOT NULL
453 and (@ProdusId is null or p.produsid=@ProdusId)
454 -- and pt.produsid is null
455
456
457 UNION
458
459 SELECT DISTINCT
460 c.DataTrsf
461 FROM inv.TrsfCap c (NOLOCK)
462 inner join inv.TrsfPoz p (NOLOCK) on p.TrsfCapId=c.TrsfCapId
463 --INNER JOIN inv.TrsfCap c (NOLOCK)
464 -- ON p.TrsfCapId = c.TrsfCapId
465
466 WHERE c.FirmaId = @SYS_UNITID
467 AND c.DivizieId = @SYS_DIVID
468 AND c.DataAnulare IS NULL
469 AND c.DataValidare IS NOT NULL
470 AND c.DataTrsf >= @DataInceputCalcul
471 and (@ProdusId is null or p.produsid=@ProdusId)
472 --AND p.IntrPozId IS NULL
473 --AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
474 --AND (@IntrPozId IS NULL OR p.IntrPozId = @IntrPozId)
475
476
477 UNION
478
479 SELECT DISTINCT
480 c.DataFactIes
481 FROM inv.IesCap c (NOLOCK)
482 INNER JOIN inv.IesPoz p (NOLOCK)
483 ON p.IesCapId = c.IesCapId
484 --INNER JOIN inv.IesPoz ref (NOLOCK)
485 -- ON ref.IesPozId = p.IesPozRefId
486 -- --and isnull(t.stoc,0)=1
487 WHERE c.FirmaId = @SYS_UNITID
488 AND c.DivizieId = @SYS_DIVID
489 AND c.DataAnulare IS NULL
490 AND c.DataValidare IS NOT NULL
491 AND c.DataFactIes >= @DataInceputCalcul
492 and c.retur=1
493 and (@ProdusId is null or p.produsid=@ProdusId)
494 --AND p.IntrPozId IS NULL
495 --AND p.IesPozAvizId IS NULL
496 --AND ref.IesPozAvizId IS NULL
497 --AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
498 --AND (@IntrPozId IS NULL OR p.IntrPozId = @IntrPozId)
499
500 --UNION
501
502 --SELECT CAST(GETDATE() as DATE)
503 --WHERE ISNULL(@StocZilnic,0) = 1
504 ) a
505 ORDER BY a.DataValidare
506
507 DECLARE @DataValidareInceput DATETIME = NULL,
508 @DataValidareSfarsit DATETIME = NULL
509
510 WHILE EXISTS (SELECT TOP 1 DataValidare FROM #DataIntrare WHERE Prelucrat = 0)
511 BEGIN
512
513 IF ISNULL(@StocZilnic,0) = 1
514 BEGIN
515 IF @DataValidareInceput IS NOT NULL
516 SELECT @DataValidareInceput = DATEADD(dd,1,@DataValidareInceput)
517 ELSE
518 SELECT @DataValidareInceput = @DataInceputCalcul
519 END
520 ELSE
521 SELECT TOP 1 @DataValidareInceput = DataValidare
522 FROM #DataIntrare
523 WHERE Prelucrat = 0
524 ORDER BY DataValidare
525
526 IF ISNULL(@StocZilnic,0) = 1
527 SET @DataValidareSfarsit = DATEADD(dd,1,@DataValidareInceput)
528 ELSE
529 SELECT @DataValidareSfarsit = MIN(DataValidare)
530 FROM #DataIntrare
531 WHERE DataValidare > @DataValidareInceput
532
533 SELECT @DataValidareSfarsit = ISNULL(@DataValidareSfarsit,DATEADD(dd,1,@DataValidareInceput))
534
535 UPDATE #DataIntrare
536 SET Prelucrat = 1
537 WHERE DataValidare = @DataValidareInceput
538
539 INSERT INTO #returClient
540 (
541 IesPozId
542 , ProdusId
543
544 , GestiuneId
545
546 , CantIesire
547 , PretCost
548 , ValoareCost
549 , DataIntr
550 , PretCmp
551 , Prelucrat
552 , DataInceput
553 , DataSfarsit
554 )
555 SELECT
556 p.IesPozId
557 , p.ProdusId
558
559 , isnull(p.GestiuneId,p.GestiuneLivrareId)
560
561 , -p.CantIesire
562 , ISNULL(p.PretCost, 0)
563 , ISNULL(p.PretCost, 0)*(-p.CantIesire)
564 , p.DataIntr
565 , ISNULL(p.PretCost, 0)
566 , 0
567 , @DataValidareInceput
568 , @DataValidareSfarsit
569 FROM inv.iescap c (NOLOCK)
570
571 INNER JOIN inv.iespoz p (NOLOCK)
572 ON p.IesCapId = c.IesCapId
573 INNER JOIN inv.iespoz pref (NOLOCK)
574 ON pref.IesPozId = p.IesPozRefId
575 INNER JOIN inv.IesCap cref (NOLOCK)
576 ON cref.IesCapId = pref.IesCapId
577 -- left join #produse t on t.produsid=p.produsid
578 WHERE c.FirmaId = @SYS_UNITID
579 AND c.DivizieId = @SYS_DIVID
580 AND c.DataAnulare IS NULL
581 AND c.DataValidare IS NOT NULL
582 --AND isnull(t.stoc,0) = 1
583 AND c.DatafactIes >= @DataValidareInceput
584 AND c.DatafactIes < @DataValidareSfarsit
585 AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
586 AND (@IntrPozId IS NULL OR p.IntrPozId = @IntrPozId)
587 --AND p.IntrPozId IS NULL
588 AND p.IesPozAvizId IS NULL
589 -- AND pref.IesPozAvizId IS NULL
590 and c.docid<>46
591 --and t.produsid is null
592 and (@ProdusId is null or p.produsid=@ProdusId)
593
594 INSERT INTO #IntrPoz
595 (
596 Produsid
597
598 , GestiuneId
599
600 , CantIntrare
601 , PretCost
602 , Valoarecost
603 , DataIntr
604 , Prelucrat
605 , DataInceput
606 , DataSfarsit
607 )
608 SELECT
609 p.ProdusId
610
611 , p.GestiuneId
612
613 , p.CantIntrare
614 , ISNULL(p.PretCost, 0)
615 , p.CantIntrare* ISNULL(p.PretCost, 0) as valoarecost
616 , c.DataIntr
617 , 0
618 , @DataValidareInceput
619 , @DatavalidareSfarsit
620 FROM inv.IntrCap c (NOLOCK)
621 inner join inv.tipdoc t (NOLOCK) on t.TipDocId=c.TipDocId
622 inner join inv.intrpoz p (NOLOCK)on p.IntrCapId=c.IntrCapId
623 --left join #produse pt on pt.produsid=p.produsid
624 where c.FirmaId=@SYS_UNITID
625 and c.divizieId=@SYS_DIVID
626 and c.dataanulare is null
627 and c.datavalidare is not null
628 and c.Dataintr>=@DataValidareInceput
629 and c.Dataintr<@DataValidareSfarsit
630 --and isnull(t.stoc,0)=1
631 --and c.IntrCapId=4772
632 and (@produsid is null or p.ProdusId=@produsid)
633 and (@intrpozid is null or p.IntrPozId=@IntrPozId)
634 and p.gestiuneid is not null
635 and p.IntrPozRefId is null
636 --+++++++++++++++++++++++++++++++++++
637 and p.IntrPozAvizId is null
638 --and pt.produsid is null
639 --and isnull(p.FaraStoc,0) = 1
640 and (@ProdusId is null or p.produsid=@ProdusId)
641
642
643 INSERT INTO #TrsfNew
644 (
645 ProdusId
646
647 , GestiuneNewId
648
649 , CantTrsf
650 , PretCost
651 , ValoareCost
652 , DataIntr
653 , Prelucrat
654 , DataInceput
655 , DataSfarsit
656 )
657 SELECT
658 p.ProdusId
659
660 , ISNULL(p.GestiuneNewId,c.gestiunenewId) as GestiuneId
661
662 , p.CantTrsf
663 , ISNULL(p.PretCost, 0)
664 , p.CantTrsf* ISNULL(p.PretCost, 0) as valoarecost
665 , p.DataIntr
666 , 0
667 , @DataValidareInceput
668 , @DataValidareSfarsit
669 FROM inv.TrsfPoz p (NOLOCK)
670 INNER JOIN inv.TrsfCap c (NOLOCK)
671 ON p.TrsfCapId = c.TrsfCapId
672 --left join #produse pt on pt.produsid=p.produsid
673 WHERE c.FirmaId = @SYS_UNITID
674 AND c.DivizieId = @SYS_DIVID
675 AND c.DataAnulare IS NULL
676 AND c.DataValidare IS NOT NULL
677 AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
678 AND (@IntrPozId IS NULL OR p.IntrPozId = @IntrPozId)
679 AND c.DataTrsf >= @DataValidareInceput
680 AND c.DataTrsf < @DataValidareSfarsit
681 and (@ProdusId is null or p.produsid=@ProdusId)
682 -- and pt.produsid is null
683 -- AND p.IntrPozId IS NULL
684
685
686
687 INSERT INTO #IntrariCumulate
688 (
689 ProdusId
690
691 , GestiuneId
692
693 , CantIntrare
694 , PretCost
695 , ValoareCost
696 , Prelucrat
697 , Data
698 )
699 SELECT
700 a.ProdusId
701
702 , a.GestiuneId
703
704 , SUM(a.CantIntrare) as CantIntrare
705 , MIN(ISNULL(a.PretCost, 0))
706 , SUM(a.CantIntrare * ISNULL(a.PretCost, 0)) as ValoareCost
707 , 0
708 , @DataValidareInceput
709 FROM
710 (
711 SELECT
712 Produsid
713 , GestiuneId
714
715 , CantIntrare
716 , PretCost
717 , DataIntr
718
719 FROM #IntrPoz
720 WHERE Prelucrat = 0
721
722 UNION ALL
723
724 SELECT
725 ProdusId
726 , GestiuneId
727
728 , CantIesire
729 , PretCost
730 , DataIntr
731
732 FROM #returClient p
733 WHERE Prelucrat = 0
734
735 UNION ALL
736
737 SELECT
738 ProdusId
739 , GestiuneNewId
740
741
742 , CantTrsf
743 , PretCost
744 , DataIntr
745
746 FROM #TrsfNew
747 WHERE Prelucrat = 0
748 )a
749 GROUP BY
750 a.ProdusId
751 , a.GestiuneId
752
753
754 UPDATE #returclient
755 SET Prelucrat = 1
756 WHERE Prelucrat = 0
757
758 UPDATE #intrpoz
759 SET Prelucrat = 1
760 WHERE Prelucrat = 0
761
762 UPDATE #trsfnew
763 SET Prelucrat = 1
764 WHERE Prelucrat = 0
765
766 UPDATE s
767 SET s.stoc = s.Stoc + p.CantIntrare
768 , s.CantIntrare = ISNULL(s.CantIntrare,0) + p.CantIntrare
769 , s.valoarecost = ISNULL(s.ValoareCost, 0)+p.ValoareCost--(s.stoc + p.CantIntrare) * s.Pretcost
770 , s.StocInitial = CASE WHEN s.Stoc = 0 THEN p.CantIntrare ELSE s.Stoc END
771 FROM #Stoc s
772 INNER JOIN #IntrariCumulate p
773 ON p.Produsid = s.Produsid
774 AND p.GestiuneId = s.GestiuneId
775
776 WHERE ISNULL(p.Prelucrat,0) = 0
777 AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
778
779
780 INSERT INTO #Stoc
781 (
782 Produsid
783
784 , GestiuneId
785
786 , StocInitial
787 , stoc
788 , PretCost
789 , CantIntrare
790 , ValoareCost
791 )
792 SELECT
793 p.Produsid
794
795 , p.GestiuneId
796
797 , p.CantIntrare
798 , p.CantIntrare
799 , p.PretCost
800 , p.CantIntrare
801
802 , p.ValoareCost
803 FROM #Intraricumulate p
804 LEFT JOIN #Stoc s
805 ON p.produsid = s.produsid
806 AND p.gestiuneid = s.GestiuneId
807
808 WHERE ISNULL(p.Prelucrat,0) = 0
809 AND s.produsid IS NULL
810
811 UPDATE #IntrariCumulate
812 SET Prelucrat = 1
813 WHERE Prelucrat = 0
814
815 --UPDATE s
816 -- SET s.PretCmp = CAST(p.VCostStoc/ISNULL(NULLIF(p.Stoc,0.0000000),1.00) AS DECIMAL(22,8))
817 --FROM #Stoc s
818 -- INNER JOIN(
819 -- SELECT
820 -- s.ProdusId
821 -- , cast(SUM(s.Stoc)as decimal(22,8)) as Stoc
822 -- , SUM(s.Stoc*cast(ISNULL(s.[PretCmp], s.PretCost)as decimal(22,8))) as VCostStoc
823 -- FROM #Stoc s
824 -- WHERE (@produsid is null or s.ProdusId=@produsid)
825
826 -- GROUP BY s.ProdusId
827 -- ) p on s.ProdusId=p.ProdusId
828 --WHERE p.Stoc > 0
829 -- AND (@ProdusId IS NULL OR s.ProdusId = @ProdusId)
830
831
832 INSERT INTO #IesPoz
833 (
834 IesPozId
835 , ProdusId
836
837 , GestiuneId
838 , Stoc
839 , PretCmp
840 , PretcmpVechi
841 , PretCost
842 , CantIesire
843 , DataIntr
844 , Prelucrat
845 , DataInceput
846 , DataSfarsit
847 )
848 SELECT
849 p.IespozId
850 , p.ProdusId
851
852 , isnull(p.GestiuneId,p.GestiuneLivrareId) as GestiuneId
853 , null--,p.Stoc
854 , 0 as PretCmp
855 , p.Pretcmp as PretcmpVechi
856 , ISNULL(p.PretCost, 0)
857 , p.CantIesire
858 , p.dataintr
859 , 0
860 , @DataValidareInceput
861 , @DataValidareSfarsit
862 FROM inv.iescap c (NOLOCK)
863 INNER JOIN inv.Iespoz p (NOLOCK)
864 ON p.IesCapId = c.IesCapId
865
866
867 INNER JOIN inv.tipdoc t (NOLOCK)
868 ON t.tipdocid = c.tipdocid
869 --AND ISNULL(t.stoc,0) = 1
870
871 --left join #produse pt on pt.produsid=p.produsid
872 WHERE p.IesPozRefId IS NULL
873 AND p.IesPozAvizId IS NULL
874 AND c.FirmaId = @SYS_UNITID
875 AND c.DivizieId = @SYS_DIVID
876 AND c.Dataanulare IS NULL
877 AND c.datavalidare IS NOT NULL
878 AND c.Dataies >= @DataValidareInceput
879 AND c.Dataies < @DataValidareSfarsit
880 AND p.IesPozId NOT IN (SELECT IespozID FROM #Iespoz)
881 and c.docid<>46
882 --and pt.produsid is null
883 and (@ProdusId is null or p.produsid=@ProdusId)
884
885 --UPDATE p
886 --SET p.PretCmp = s.PretCmp
887 --FROM #IesPoz p
888 -- INNER JOIN #stocladata s
889 -- ON s.ProdusId = p.ProdusId
890 --WHERE s.Data = @DataInceputCalcul
891
892
893 INSERT INTO #ReturFurnizor
894 (
895
896 ProdusId
897
898 , GestiuneId
899
900 , CantIntrare
901 , PretCost
902 , DataIntr
903 , Prelucrat
904 , Pretcmp
905 , DataInceput
906 , DAtaSfarsit
907 )
908 SELECT
909
910 p.ProdusId
911
912 , p.GestiuneId
913
914 , -p.CantIntrare
915 , ISNULL(p.PretCost, 0)
916 , c.DataIntr
917 , 0
918 , 0 as PretCmp
919 , @DataValidareInceput
920 , @DataValidareSfarsit
921 FROM inv.intrcap c (NOLOCK)
922 INNER JOIN inv.tipdoc t (NOLOCK)
923 ON t.TipDocId = c.TipDocId
924 INNER JOIN inv.intrpoz p (NOLOCK)
925 ON p.IntrCapId = c.IntrCapId
926 INNER JOIN inv.Intrpoz ref (NOLOCK)
927 ON ref.intrpozid = p.intrpozrefid
928 --INNER JOIN #stoc s
929 -- ON s.GestiuneId = p.GestiuneId
930 -- AND s.ProdusId = p.Produsid
931
932 -- left join #produse pt on pt.produsid=p.produsid
933 WHERE c.FirmaId = @SYS_UNITID
934 AND c.divizieId = @SYS_DIVID
935 AND c.DataAnulare IS NULL
936 AND c.DataValidare IS NOT NULL
937 AND c.Dataintr >= @DataValidareInceput
938 AND c.dataintr < @DataValidareSfarsit
939
940
941 ---AND ISNULL(p.FaraStoc,0) = 1
942 AND p.IntrPozAvizId IS NULL
943 -- AND ISNULL(ref.FaraStoc,0) = 1
944 AND ref.IntrPozAvizId IS NULL
945 --AND ref.GestiuneId IS NOT NULL
946 AND (@ProdusId IS NULL OR p.ProdusId = @ProdusId)
947 AND (@IntrPozId IS NULL OR p.IntrPozrefId = @IntrPozId)
948
949 --and pt.produsid is null
950
951 INSERT INTO #TrsfPoz
952 (
953 TrsfPozId
954 , ProdusId
955
956 , GestiuneOldId
957
958 , CantTrsf
959 , PretCost
960 , DataIntr
961 , Prelucrat
962 , PretCmp
963 , DataInceput
964 , DataSfarsit
965 )
966 SELECT
967 p.TrsfPozId
968 , p.ProdusId
969
970 , p.GestiuneOldId
971
972 , p.CantTrsf
973 , ISNULL(p.PretCost, 0)
974 , p.DataIntr
975 , 0
976 , 0 as PretCmp
977 , @DataValidareInceput
978 , @DatavalidareSfarsit
979 FROM inv.trsfpoz p (NOLOCK)
980 INNER JOIN inv.trsfcap c (NOLOCK)
981 ON p.TrsfCapId = c.TrsfCapId
982
983 --left join #produse pt on pt.produsid=p.produsid
984 WHERE c.firmaid = @SYS_UNITID
985 AND c.DivizieId = @SYS_DIVID
986 AND c.dataanulare IS NULL
987 AND c.DataValidare IS NOT NULL
988
989 AND c.datatrsf >= @DataValidareInceput
990 AND c.datatrsf < @DataValidareSfarsit
991 --AND p.IntrPozId IS NULL
992 AND p.TrsfPozId NOT IN (SELECT TrsfPozId FROM #TrsfPoz)
993 AND (@ProdusId IS NULL OR p.ProdusId=@produsid)
994 AND (@IntrPozId IS NULL OR p.IntrPozId=@IntrPozId)
995
996 -- and pt.produsid is null
997
998
999 INSERT INTO #Iesiricumulate
1000 (
1001
1002 ProdusId
1003 , GestiuneId
1004
1005 , CantIesire
1006 , Prelucrat
1007
1008 --, PretCost
1009 --, PretCMp
1010 , ValoareCost
1011 , Data
1012 )
1013 SELECT
1014
1015 p.ProdusId
1016 , p.GestiuneId
1017
1018 , SUM(p.CantIesire) as CantIesire
1019 , 0
1020 --, PretCost
1021 --, PretCMp
1022 , SUM(p.Valoarecost) as ValoareCost
1023 , @DataValidareInceput
1024 FROM
1025 (
1026 SELECT
1027
1028 p.ProdusId
1029 , p.GestiuneId
1030
1031 , SUM(p.CantIesire) as CantIesire
1032
1033
1034 , SUM(p.CantIesire*ISNULL(p.PretCost, 0)) as Valoarecost
1035
1036 FROM #IesPoz p
1037 WHERE p.Prelucrat = 0
1038 GROUP BY
1039 p.ProdusId
1040 , p.GestiuneId
1041
1042
1043
1044
1045
1046 UNION ALL
1047
1048 SELECT
1049 p.ProdusId
1050 , p.GestiuneId
1051
1052 , SUM(p.CantIesire) as CantIesire
1053
1054
1055 , SUM(p.CantIesire*ISNULL(p.PretCost, 0)) as Valoarecost
1056
1057 FROM #Sorderpoz p
1058 WHERE p.Prelucrat = 0
1059 GROUP BY
1060
1061 p.ProdusId
1062 , p.GestiuneId
1063
1064
1065
1066
1067
1068 UNION ALL
1069
1070 SELECT
1071
1072 p.ProdusId
1073 , p.GestiuneId
1074
1075 , SUM(p.CantIntrare) as CantIesire
1076
1077
1078 , SUM(p.CantIntrare*ISNULL(p.PretCost, 0)) as Valoarecost
1079
1080 FROM #returFurnizor p
1081 WHERE p.Prelucrat=0
1082 GROUP BY
1083 p.ProdusId
1084 , p.GestiuneId
1085
1086
1087
1088
1089
1090 UNION ALL
1091
1092 SELECT
1093
1094 p.ProdusId
1095 , p.GestiuneoldId
1096
1097 , SUM(p.CantTrsf) as CantIesire
1098
1099
1100 , SUM(p.CantTrsf*ISNULL(p.PretCost, 0)) as Valoarecost
1101 FROM #TrsfPoz p
1102 WHERE p.Prelucrat = 0
1103 GROUP BY
1104
1105 p.ProdusId
1106 , p.GestiuneOldId
1107
1108
1109
1110 ) p
1111 GROUP BY
1112 p.ProdusId
1113 , p.GestiuneId
1114
1115
1116
1117
1118 UPDATE #IesPoz
1119 SET Prelucrat = 1
1120 WHERE Prelucrat = 0
1121
1122 UPDATE #sorderpoz
1123 SET Prelucrat = 1
1124 WHERE Prelucrat = 0
1125
1126 UPDATE #returFurnizor
1127 SET Prelucrat = 1
1128 WHERE Prelucrat = 0
1129
1130 UPDATE #TrsfPoz
1131 SET Prelucrat = 1
1132 WHERE Prelucrat = 0
1133
1134
1135 UPDATE s
1136 SET s.stoc = s.stoc - p.CantIesire
1137
1138 , s.ValoareCost =ISNULL(s.ValoareCost, 0)-p.valoarecost
1139 FROM #stoc s
1140 INNER JOIN
1141 (
1142 SELECT
1143 sum(p.CantIesire) as CantIesire
1144 , sum(p.valoarecost) as valoarecost
1145 , p.Produsid
1146 , p.GestiuneId
1147
1148 FROM #Iesiricumulate p
1149 WHERE Prelucrat = 0
1150 GROUP BY
1151 p.Produsid
1152 , p.GestiuneId
1153
1154 ) p
1155 ON p.Produsid = s.ProdusId
1156 AND p.GestiuneId=s.GestiuneId
1157
1158 INSERT INTO #Stoc
1159 (
1160 Produsid
1161
1162 , GestiuneId
1163
1164 , StocInitial
1165 , stoc
1166 , PretCost
1167 , CantIntrare
1168 , ValoareCost
1169 )
1170 select p.ProdusId
1171 ,p.gestiuneid
1172 ,-p.CantIesire
1173 ,-p.CantIesire
1174 ,0
1175 ,0,
1176 -p.ValoareCost
1177 FROM
1178
1179 (
1180 SELECT
1181 sum(p.CantIesire) as CantIesire
1182 , sum(p.valoarecost) as ValoareCost
1183 , p.Produsid
1184 , p.GestiuneId
1185
1186 FROM #Iesiricumulate p
1187 WHERE Prelucrat = 0
1188 GROUP BY
1189 p.Produsid
1190 , p.GestiuneId
1191
1192 ) p
1193 left join #stoc s
1194 ON p.Produsid = s.ProdusId
1195 AND p.GestiuneId=s.GestiuneId
1196 where s.produsid is null
1197
1198 UPDATE ic
1199 SET Prelucrat = 1
1200 FROM #IesiriCumulate ic
1201 INNER JOIN #stoc st
1202 ON st.ProdusId = ic.ProdusId
1203 AND st.GestiuneId = ic.GestiuneId
1204
1205 WHERE Prelucrat = 0
1206
1207 INSERT INTO #stocLadata
1208 (
1209 ProdusId
1210
1211 , GestiuneId
1212 , StocInitial
1213 , Stoc
1214 , ValoareCost
1215 , PretCost
1216 , DataIntr
1217 , Data
1218 , PretCmp
1219 )
1220 SELECT
1221 ProdusId
1222
1223 , GestiuneId
1224 , SUM(StocInitial) AS StocInitial
1225 , SUM(stoc) as Stoc
1226 , SUM(ValoareCost) as ValoareCost
1227 , MIN(PretCost)
1228 , MAX(DataIntr) as DataIntr
1229 , @DataValidareInceput
1230 , PretCmp
1231 FROM #Stoc
1232 GROUP BY
1233 ProdusId
1234
1235 , GestiuneId
1236 , pretcmp
1237 --HAVING SUM(stoc) > 0 OR SUM(StocInitial) > 0
1238
1239 UPDATE #stoc
1240 SET StocInitial = Stoc
1241 WHERE StocInitial <> Stoc
1242 END
1243
1244 INSERT INTO inv.StocLaData
1245 (
1246 ProdusId
1247 , GestiuneId
1248
1249
1250 , StocInitial
1251 , Stoc
1252 , Data
1253
1254 , FirmaId
1255 , DivizieId
1256
1257 , PretCmp
1258 , Pretcost
1259
1260 )
1261 SELECT
1262 s.ProdusId
1263 , s.GestiuneId
1264
1265
1266 , s.StocInitial
1267 , s.Stoc
1268 , s.Data
1269
1270
1271
1272 , @SYS_UNITID FirmaId
1273 , @SYS_DIVID DivizieId
1274
1275
1276 , s.PretCmp
1277
1278 , abs(case when s.stoc<>0 then s.ValoareCost/s.stoc else 0.00 end )
1279
1280 FROM #StocLaData s
1281 -- WHERE ISNULL(s.Stoc,0)>0
1282
1283
1284 UPDATE dbo.tblDataCmp
1285 SET Prelucrat = 1
1286 WHERE Firmaid = @SYS_UNITID
1287 AND DivizieId = @SYS_DIVID
1288 AND ISNULL(Prelucrat,0) = -1
1289 END
1290
1291
1292--ROLLBACK
1293COMMIT TRANSACTION
1294END TRY
1295
1296BEGIN CATCH
1297 DECLARE @ErrorMessage NVARCHAR(4000)
1298 DECLARE @ErrorSeverity INT
1299 DECLARE @ErrorState INT
1300
1301 SELECT
1302 @ErrorMessage = ERROR_MESSAGE()
1303 , @ErrorSeverity = ERROR_SEVERITY()
1304 , @ErrorState = ERROR_STATE() ;
1305
1306 IF XACT_STATE() <> 0
1307 BEGIN
1308 ROLLBACK TRANSACTION
1309 END
1310
1311 RAISERROR ( @ErrorMessage , @ErrorSeverity , @ErrorState )
1312END CATCH
1313END
1314;