· 7 years ago · Nov 23, 2018, 11:30 AM
1USE [DevPluriva]
2GO
3/****** Object: StoredProcedure [dbo].[ReteteStocuri_LegareStoc] Script Date: 11/22/2018 10:39:18 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9ALTER PROCEDURE [dbo].[ReteteStocuri_LegareStoc]
10 @FirmaId INT = NULL
11, @DivizieId INT = NULL
12, @GestiuneIds NVARCHAR(MAX) = NULL
13, @IesCapId INT = NULL
14, @DinJob BIT = NULL
15, @DeLa DATE = NULL
16, @PanaLa DATE = NULL
17, @DocIds NVARCHAR(MAX) = NULL
18, @ProdusIds NVARCHAR(MAX) = NULL
19, @GenerareTotala INT = NULL
20, @DocFltId INT =NULL
21, @TipDocFltId INT =NULL
22AS
23BEGIN
24 SET NOCOUNT ON;
25
26 DECLARE
27 @OK BIT = 0
28 , @DataExec DATETIME = GETDATE()
29
30 EXEC dbo.StartSemafor
31 'DESCARCARESTOC'
32 , 30
33 , @DataExec
34 , @DinJob
35 , @OK OUTPUT
36 , @FirmaId
37 , @DivizieID
38
39 IF ISNULL(@OK,0) = 1
40 BEGIN
41
42 BEGIN TRY
43
44 BEGIN TRAN
45 DECLARE
46 @DataCrt DATE = GETDATE()
47
48 , @GestiunePFId INT
49 , @ContabProdusGPFId INT
50 , @CentruCostGPFId INT
51 , @ArticolBugetGPFId INT
52 , @BusinessLineGPFId INT
53
54 , @GestiuniMPIds NVARCHAR(MAX)
55 , @GestiuniRetail NVARCHAR(MAX)
56 --, @GestiuneSursaRetetaIds NVARCHAR(MAX) = ''
57
58 , @PartenerId INT
59
60 , @NrZecimalePretVanzare INT
61 , @NrZecimalePretIntrare INT
62 , @Setare351 INT -- nr zecimale valoare iesire
63 , @Setare623 INT -- nr zecimale cantitate iesire detalii
64 , @setare518 INT -- blocare vanzare pentru intrari ulterioare
65 , @setare604 INT -- dimensiuni din stoc
66
67 IF(NOT EXISTS( SELECT TOP 1 InvCapId
68 FROM inv.InvCap (NOLOCK)
69 WHERE Firmaid = @FirmaId
70 AND DivizieId = @DivizieId
71 AND DataValidare IS NULL
72 AND DataAnulare IS NULL))
73 OR ISNULL(@GenerareTotala,0) = 1
74 BEGIN
75
76 IF ISNULL(@DocIds,'') = ''
77 SELECT @DocIds = dbo.ValoareSetare(702,@FirmaId,@DivizieId)
78
79 IF OBJECT_ID('tempdb..#Docs') IS NOT NULL
80 DROP TABLE #Docs
81 CREATE TABLE #Docs(DocId INT)
82
83 IF OBJECT_ID('tempdb..#Produse') IS NOT NULL
84 DROP TABLE #Produse
85 CREATE TABLE #Produse(ProdusId INT)
86
87 INSERT #Docs (DocId)
88 SELECT x.val
89 FROM dbo.SplitString(@DocIds,',') x
90
91 INSERT #Produse (ProdusId)
92 SELECT x.val
93 FROM dbo.SplitString(@ProdusIds,',') x
94
95
96 -- Gestiune de tip Finite
97 -- Setez aceasta gestiune pe inregistrarile care contin detalii
98 SELECT TOP 1
99 @GestiunePFId = GestiuneId
100 , @ContabProdusGPFId = ContabProdusId
101 , @CentruCostGPFId = CentruCostId
102 , @ArticolBugetGPFId = ArticolBugetId
103 , @BusinessLineGPFId = BusinessLineId
104 FROM dbo.tblGestiuni (NOLOCK)
105 WHERE FirmaId = @FirmaId
106 AND DivizieId = @DivizieId
107 AND ISNULL(Activ,0) = 1
108 AND ISNULL(TipGestiune,0) = 3
109 ORDER BY OrdineVanzare
110
111 -- Toate gestiunile de consum/materii prime
112 -- Gestiuni din care descarc materialele
113 SELECT @GestiuniMPIds = ISNULL(@GestiuniMPIds + ',','') + CAST(GestiuneId AS NVARCHAR(MAX))
114 FROM dbo.tblGestiuni (NOLOCK)
115 WHERE FirmaId = @FirmaId
116 AND DivizieId = @DivizieId
117 AND ISNULL(Activ,0) = 1
118 AND ISNULL(TipGestiune,0) = 6
119
120 -- Toate gestiunile configurate in retail
121 -- Gestiuni din care descarc produsele care nu contin detalii
122 SELECT @GestiuniRetail = ISNULL(@GestiuniRetail + ',','') + CAST(g.GestiuneId AS NVARCHAR(MAX))
123 FROM retail.Config cfg (NOLOCK)
124 CROSS APPLY dbo.SplitString(cfg.Gestiuni,',') codGest
125 INNER JOIN dbo.tblGestiuni g (NOLOCK)
126 ON g.CodGestiune = codGest.val
127 AND g.FirmaId = @FirmaId
128 AND g.DivizieId = @DivizieId
129 WHERE cfg.FirmaId = @FirmaId
130 AND cfg.DivizieId = @DivizieId
131 GROUP BY g.GestiuneId
132
133 -- Adaug si gestiunile configurate pe tip document
134 IF OBJECT_ID('tempdb..#GestiuniRetail') IS NOT NULL
135 DROP TABLE #GestiuniRetail
136 CREATE TABLE #GestiuniRetail(TipDocId INT, GestiuneId INT)
137
138 IF ISNULL(@GestiuneIds,'') = '' -- Daca nu vine completat, iau gestiunile configurate in Retail
139 BEGIN
140 INSERT #GestiuniRetail(TipDocId, GestiuneId)
141 SELECT
142 td.TipDocId
143 , g.GestiuneId
144 FROM inv.TipDoc td (NOLOCK)
145 CROSS APPLY dbo.SplitString(ISNULL(@GestiuniRetail + ',','') + ISNULL(td.GestiuneIds,''),',') ids
146 INNER JOIN dbo.tblGestiuni g (NOLOCK)
147 ON g.GestiuneId = ids.val
148 AND g.FirmaId = @FirmaId
149 AND g.DivizieId = @DivizieId
150 WHERE td.FirmaId = @FirmaId
151 AND td.DivizieId = @DivizieId
152 GROUP BY
153 td.TipDocId
154 , g.GestiuneId
155
156 --INSERT #GestiuniMP(GestiuneId)
157 --SELECT GestiuneId
158 --FROM dbo.tblGestiuni (NOLOCK)
159 --WHERE FirmaId = @FirmaId
160 -- AND DivizieId = @DivizieId
161 -- AND ISNULL(Activ,0) = 1
162 -- AND ISNULL(TipGestiune,0) = 6
163 END
164
165 ELSE
166 BEGIN
167 INSERT #GestiuniRetail(GestiuneId)
168 SELECT val
169 FROM dbo.SplitString(@GestiuneIds,',')
170
171 --SELECT @GestiuniMPIds = @GestiuneIds
172
173 --INSERT #GestiuniMP(GestiuneId)
174 --SELECT val
175 --FROM dbo.SplitString(@GestiuneIds,',')
176 END
177
178 IF OBJECT_ID('tempdb..#ReteteCap') IS NOT NULL
179 DROP TABLE #ReteteCap
180 -- Toate retetele de pe divizia curenta
181 -- active
182 -- neanulate
183 -- care nu au bifa de IgnoraDescarcare
184 -- care au completat Cant sau Cant2
185
186 SELECT
187 rc.ProdusId
188 , rc.RetetaCapId
189 , rc.ValabilDeLa
190 , rc.ValabilPanaLa
191 , COALESCE(g1.GestiuneId,g2.GestiuneId,@GestiunePFId) AS GestiuneDestinatieId
192 , x.val
193
194 , MAX(cpf.CentruCostId) AS CentruCostContareProdusId
195 , MAX(cpf.ArticolBugetId) AS ArticolBugetContareProdusId
196 , MAX(cpf.BusinessLineId) AS BusinessLineContareProdusId
197
198 , MAX(cpg.CentruCostId) AS CentruCostContareGestiuneId
199 , MAX(cpg.ArticolBugetId) AS ArticolBugetContareGestiuneId
200 , MAX(cpg.BusinessLineId) AS BusinessLineContareGestiuneId
201
202 , MAX(COALESCE(g1.CentruCostId,g2.CentruCostId,@CentruCostGPFId)) AS CentruCostGestiuneId
203 , MAX(COALESCE(g1.ArticolBugetId,g2.ArticolBugetId,@ArticolBugetGPFId)) AS ArticolBugetGestiuneId
204 , MAX(COALESCE(g1.BusinessLineId,g2.BusinessLineId,@BusinessLineGPFId)) AS BusinessLineGestiuneId
205 INTO #ReteteCap
206 FROM dbo.tblRetetaCap rc (NOLOCK)
207 INNER JOIN dbo.tblProduseDivizii pd (NOLOCK)
208 ON pd.ProdusId = rc.ProdusId
209 AND pd.DivizieId = @DivizieId
210 INNER JOIN dbo.tblRetetaPoz rp (NOLOCK)
211 ON rp.RetetaCapId = rc.RetetaCapId
212 LEFT JOIN dbo.tblRetetaCapGestiuniDest rgd (NOLOCK)
213 ON rgd.RetetaCapId = rc.RetetaCapId
214 AND rgd.FirmaId = @FirmaId
215 LEFT JOIN dbo.tblGestiuni g1 (NOLOCK)
216 ON g1.GestiuneId = rc.GestiuneDestinatieId
217 AND g1.FirmaId = @FirmaId
218 AND g1.DivizieId = @DivizieId
219 LEFT JOIN dbo.tblGestiuni g2 (NOLOCK)
220 ON g2.GestiuneId = rgd.GestiuneId
221 AND g2.FirmaId = @FirmaId
222 AND g2.DivizieId = @DivizieId
223 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
224 ON cpf.ContabProdusId = pd.ContabProdusId
225 AND cpf.FirmaId = @FirmaId
226 AND cpf.DivizieId = @DivizieId
227 LEFT JOIN dbo.tblContabProduseFirma cpg (NOLOCK)
228 ON cpg.ContabProdusId = COALESCE(g1.ContabProdusId,g2.ContabProdusId,@ContabProdusGPFId)
229 AND cpg.FirmaId = @FirmaId
230 AND cpg.DivizieId = @DivizieId
231 OUTER APPLY (SELECT * FROM dbo.SplitString(COALESCE(NULLIF(@GestiuneIds,''),rc.GestiuneSursaIds,@GestiuniMPIds),',')) x
232 WHERE rc.DivizieId = @DivizieId
233 AND ISNULL(rc.Inactiv,0) = 0
234 AND rc.DataAnulare IS NULL
235 AND ISNULL(rc.IgnoraDescarcare,0) = 0
236 AND (rc.Cant IS NOT NULL OR rc.Cant2 IS NOT NULL)
237 GROUP BY
238 rc.ProdusId
239 , rc.RetetaCapId
240 , rc.ValabilDeLa
241 , rc.ValabilPanaLa
242 , COALESCE(g1.GestiuneId,g2.GestiuneId,@GestiunePFId)
243 , x.val
244
245 --Partenerul firmei curente
246 SELECT @PartenerId = PartenerFirmaId
247 FROM dbo.tblFirme (NOLOCK)
248 WHERE FirmaId = @FirmaId
249
250 SELECT
251 @NrZecimalePretVanzare = dbo.ValoareSetare(247,@FirmaId,@DivizieId)
252 , @NrZecimalePretIntrare = dbo.ValoareSetare(295,@FirmaId,@DivizieId)
253 , @setare351 = dbo.ValoareSetare(351,@FirmaId,@DivizieId)
254 , @setare518 = dbo.ValoareSetare(518,@FirmaId,@DivizieId)
255 , @setare604 = dbo.ValoareSetare(604,@FirmaId,@DivizieId)
256 , @setare623 = dbo.ValoareSetare(623,@FirmaId,@DivizieId)
257
258 IF OBJECT_ID('tempdb..#TempSync') IS NOT NULL
259 DROP TABLE #TempSync
260
261 CREATE TABLE #TempSync
262 (
263 TempSyncId INT IDENTITY(1,1)
264
265 , IesCapId INT
266 , ClientId INT
267 , DocId INT
268 , TipDocId INT
269 , DataIes DATE
270
271 , ProdusId INT
272 , IesPozId INT
273 , SorderPozId INT
274 , ParentIesPozId INT
275
276 , GestiuneLivrareId INT
277
278 , RetetaPozId INT
279 , CantIesire DECIMAL(22,8)
280 , CantRetur DECIMAL(22,8)
281 , Prelucrat BIT
282 , RecalculPret BIT
283
284 , CentruCostTipDocId INT
285 , ArticolBugetTipDocId INT
286 , BusinessLineTipDocId INT
287
288 , CentruCostCapId INT
289 , ArticolBugetCapId INT
290 , BusinessLineCapId INT
291
292 , CentruCostContareProdusId INT
293 , ArticolBugetContareProdusId INT
294 , BusinessLineContareProdusId INT
295 )
296 INSERT #TempSync
297 (
298 IesCapId
299 , ClientId
300 , DocId
301 , TipDocId
302 , DataIes
303
304 , ProdusId
305 , IesPozId
306 , SorderPozId
307 , ParentIesPozId
308
309 , GestiuneLivrareId
310
311 , RetetaPozId
312 , CantIesire
313 , CantRetur
314 , Prelucrat
315 , RecalculPret
316
317 , CentruCostTipDocId
318 , ArticolBugetTipDocId
319 , BusinessLineTipDocId
320
321 , CentruCostCapId
322 , ArticolBugetCapId
323 , BusinessLineCapId
324
325 , CentruCostContareProdusId
326 , ArticolBugetContareProdusId
327 , BusinessLineContareProdusId
328 )
329 SELECT
330 p.IesCapId
331 , cap.ClientId
332 , cap.DocId
333 , cap.TipDocId
334 , cap.DataIes
335
336 , p.ProdusId
337 , p.IesPozId
338 , p.SOrderPozId
339 , p.ParentIesPozId
340
341 , p.GestiuneLivrareId
342
343 , p.RetetaPozId
344 , p.CantIesire
345 , p.CantRetur
346 , 0
347 , td.RecalculareDetalii
348
349 , td.CentruCostId
350 , td.ArticolBugetId
351 , td.BusinessLineId
352
353 , cap.CentruCostId
354 , cap.ArticolBugetId
355 , cap.BusinessLineId
356
357 , cpf.CentruCostId
358 , cpf.ArticolBugetId
359 , cpf.BusinessLineId
360 FROM inv.IesPoz p (NOLOCK)
361 INNER JOIN inv.IesCap cap (NOLOCK)
362 ON cap.IesCapId = p.IesCapId
363 AND cap.FirmaId = @FirmaId
364 AND cap.DivizieId = @DivizieId
365 AND cap.DataAnulare IS NULL
366 INNER JOIN #Docs tempD
367 ON tempD.DocId = cap.DocId
368 INNER JOIN inv.TipDoc td (NOLOCK)
369 ON td.TipDocId = cap.TipDocId
370 AND ISNULL(td.Stoc,0) = 1
371 INNER JOIN dbo.tblProduse pr (NOLOCK)
372 ON pr.ProdusId = p.Produsid
373 AND ISNULL(pr.TipItem,'P') <> 'S'
374 INNER JOIN dbo.tblProduseDivizii pd (NOLOCK)
375 ON pd.ProdusId = p.ProdusId
376 AND pd.DivizieId = @DivizieId
377 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
378 ON cpf.ContabProdusId = pd.ContabProdusId
379 AND cpf.FirmaId = @FirmaId
380 AND cpf.DivizieId = @DivizieId
381 LEFT JOIN #Produse tempP
382 ON tempP.ProdusId = p.ProdusId
383 WHERE p.CantIesire > 0
384 AND p.IesPozRefId IS NULL -- sa nu fie retur
385 AND p.IntrpozId IS NULL -- nelegat la stoc
386 AND ISNULL(p.ContineDetalii,0) = 0 -- sa nu contina detalii
387 AND (
388 --(cap.DocId = 21 AND cap.IesCapFacturaId IS NULL) -- descarcare pe bon fiscal
389 (cap.DocId = 21 AND (
390 (p.RetailPozIesireId IS NOT NULL AND cap.RetailCapIesireId IS NOT NULL AND cap.DocGeneratorId IS NULL AND cap.CapDocGeneratorId IS NULL)
391 OR (p.RetailPozIesireId IS NULL AND cap.RetailCapIesireId IS NULL AND cap.DocGeneratorId = 26)
392 )
393 )
394 OR (cap.DocId IN (5,6) AND p.IesPozAvizId IS NULL) -- descarcare pe factura
395 OR cap.DocId = 8 -- descarcare pe aviz
396 OR cap.DocId = 13 -- descarcare pe bon consum
397 )
398 AND (@IesCapId IS NULL OR cap.IesCapId = @IesCapId)
399 AND (@DeLa IS NULL OR cap.DataIes >= @DeLa)
400 AND (@PanaLa IS NULL OR cap.DataIes <= @PanaLa)
401 AND (ISNULL(@ProdusIds,'') = '' OR tempP.ProdusId IS NOT NULL)
402 AND (@DocfltId is null or cap.Docid=@DocfltId)
403 AND (@TipDocFltId is null or cap.TipDocID =@TipDocFltId)
404
405 DECLARE
406 @TempSyncId INT
407
408 , @CapIesId INT
409 , @ClientId INT
410 , @DocId INT
411 , @TipDocId INT
412 , @DataIes DATE
413
414 , @ProdusId INT
415 , @IesPozId INT
416 , @IesPozRefId INT
417 , @SorderPozId INT
418 , @ParentIesPozId INT
419
420 , @GestiuneLivrareId INT
421
422 , @RetetaPozId INT
423 , @CantIesire DECIMAL(22,8)
424 , @CantRetur DECIMAL(22,8)
425
426 , @RetetaCapId INT
427 , @IesPozNewId INT
428 , @RezervareId INT
429 , @RecalculPret BIT
430
431 DECLARE
432 @IntrPozId INT
433 , @IntrPozOrigId INT
434 , @GestiuneId INT
435 , @GestiuneDestinatieId INT
436 , @SerieIntrare NVARCHAR(50)
437 , @LotIntrare NVARCHAR(50)
438 , @DataFabricare DATE
439 , @DataExpirare DATE
440 , @ClientCustodieId INT
441 , @Stoc DECIMAL(22,8)
442
443 , @FurnizorID INT
444 , @FurnizorOriginalID INT
445 , @NumarIntr NVARCHAR(50)
446 , @DataIntr DATE
447 , @DocIntrId INT
448 , @TipDocIntrID INT
449 , @DataScadenta DATE
450 , @NumarFactIntr NVARCHAR(50)
451 , @DataFactIntr DATE
452 , @PretIntrare DECIMAL(22,8)
453 , @PretIntrareRedus DECIMAL(22,8)
454 , @PretCost DECIMAL(22,8)
455 , @PretCMP DECIMAL(22,8)
456 , @POOrderPozId INT
457 , @Amanunt BIT
458 , @PretAmanuntStoc DECIMAL(18,5)
459
460 , @CentruCostStocId INT
461 , @ArticolBugetStocId INT
462 , @BusinessLineStocId INT
463
464 , @CentruCostGestiuneId INT
465 , @ArticolBugetGestiuneId INT
466 , @BusinessLineGestiuneId INT
467
468 , @CentruCostContareGestiuneId INT
469 , @ArticolBugetContareGestiuneId INT
470 , @BusinessLineContareGestiuneId INT
471
472 , @CentruCostTipDocId INT
473 , @ArticolBugetTipDocId INT
474 , @BusinessLineTipDocId INT
475
476 , @CentruCostCapId INT
477 , @ArticolBugetCapId INT
478 , @BusinessLineCapId INT
479
480 , @CentruCostContareProdusId INT
481 , @ArticolBugetContareProdusId INT
482 , @BusinessLineContareProdusId INT
483
484 , @TaxCode INT
485 , @TaxInv BIT
486 , @FaraTva BIT
487 , @Tva DECIMAL(22,8)
488 , @cod1 nvarchar(100)
489 , @Cod2 nvarchar(100)
490 , @Cod3 nvarchar(100)
491
492 WHILE EXISTS ( SELECT TOP 1 IesPozId FROM #TempSync WHERE ISNULL(Prelucrat,0) = 0 )
493 BEGIN
494
495 SELECT
496 @TempSyncId = NULL
497
498 , @CapIesId = NULL
499 , @ClientId = NULL
500 , @DocId = NULL
501 , @TipDocId = NULL
502 , @DataIes = NULL
503
504 , @ProdusId = NULL
505 , @IesPozId = NULL
506 , @SorderPozId = NULL
507 , @ParentIesPozId = NULL
508
509 , @GestiuneLivrareId = NULL
510
511 , @RetetaPozId = NULL
512 , @CantIesire = NULL
513 , @CantRetur = NULL
514
515 , @RetetaCapId = NULL
516 , @IesPozNewId = NULL
517 , @RezervareId = NULL
518 , @RecalculPret = NULL
519
520 , @IntrPozId = NULL
521 , @IntrPozOrigId = NULL
522 , @GestiuneId = NULL
523 , @GestiuneDestinatieId = NULL
524 --, @GestiuneSursaRetetaIds = CASE WHEN ISNULL(@GestiuneIds,'') <> '' THEN @GestiuneIds ELSE NULL END
525 , @SerieIntrare = NULL
526 , @LotIntrare = NULL
527 , @DataFabricare = NULL
528 , @DataExpirare = NULL
529 , @ClientCustodieId = NULL
530 , @Stoc = NULL
531
532 , @FurnizorID = NULL
533 , @FurnizorOriginalID = NULL
534 , @NumarIntr = NULL
535 , @DataIntr = NULL
536 , @DocIntrId = NULL
537 , @TipDocIntrID = NULL
538 , @DataScadenta = NULL
539 , @NumarFactIntr = NULL
540 , @DataFactIntr = NULL
541 , @PretIntrare = NULL
542 , @PretIntrareRedus = NULL
543 , @PretCost = NULL
544 , @PretCMP = NULL
545 , @POOrderPozId = NULL
546 , @Amanunt = NULL
547 , @PretAmanuntStoc = NULL
548
549 , @CentruCostStocId = NULL
550 , @ArticolBugetStocId = NULL
551 , @BusinessLineStocId = NULL
552
553 , @CentruCostGestiuneId = NULL
554 , @ArticolBugetGestiuneId = NULL
555 , @BusinessLineGestiuneId = NULL
556
557 , @CentruCostContareGestiuneId = NULL
558 , @ArticolBugetContareGestiuneId = NULL
559 , @BusinessLineContareGestiuneId = NULL
560
561 , @CentruCostTipDocId = NULL
562 , @ArticolBugetTipDocId = NULL
563 , @BusinessLineTipDocId = NULL
564
565 , @CentruCostCapId = NULL
566 , @ArticolBugetCapId = NULL
567 , @BusinessLineCapId = NULL
568
569 , @CentruCostContareProdusId = NULL
570 , @ArticolBugetContareProdusId = NULL
571 , @BusinessLineContareProdusId = NULL
572
573 , @TaxCode = NULL
574 , @TaxInv = 0
575 , @FaraTva = 0
576 , @Tva = NULL
577 , @Cod1 = NULL
578 , @Cod2 = NULL
579 , @Cod3 = NULL
580
581 SELECT TOP 1
582 @TempSyncId = TempSyncId
583
584 , @CapIesId = IesCapId
585 , @ClientId = ClientId
586 , @DocId = DocId
587 , @TipDocId = TipDocId
588 , @DataIes = DataIes
589
590 , @ProdusId = ProdusId
591 , @IesPozId = IesPozId
592 , @SorderPozId = SorderPozId
593 , @ParentIesPozId = ParentIesPozId
594
595 , @GestiuneLivrareId = GestiuneLivrareId
596
597 , @RetetaPozId = RetetaPozId
598 , @CantIesire = CantIesire
599 , @CantRetur = CantRetur
600 , @RecalculPret = RecalculPret
601
602 , @CentruCostTipDocId = CentruCostTipDocId
603 , @ArticolBugetTipDocId = ArticolBugetTipDocId
604 , @BusinessLineTipDocId = BusinessLineTipDocId
605
606 , @CentruCostCapId = CentruCostCapId
607 , @ArticolBugetCapId = ArticolBugetCapId
608 , @BusinessLineCapId = BusinessLineCapId
609
610 , @CentruCostContareProdusId = CentruCostContareProdusId
611 , @ArticolBugetContareProdusId = ArticolBugetContareProdusId
612 , @BusinessLineContareProdusId = BusinessLineContareProdusId
613 FROM #TempSync
614 WHERE ISNULL(Prelucrat,0) = 0
615 ORDER BY DataIes
616 /*
617 EXEC [inv].[TaxCodeProdus_vanzare]
618 @TaxCode = @TaxCode out
619 , @FaraTva = @FaraTva out
620 , @Tva = @Tva out
621 , @ClientId = @ClientId
622 , @ProdusID = @ProdusID
623 , @DocId = @DocId
624 , @TipDocId = @TipDocId
625 , @SYS_PARTID = @PartenerId
626 , @SYS_UNITID = @FirmaId
627 , @SYS_DIVID = @DivizieId
628 , @sys_langID = 'RO'
629 , @IesCapId = @CapIesId
630
631 IF EXISTS( SELECT TaxCode FROM SysErp.ERP.TaxMode (NOLOCK) WHERE TaxCode = @TaxCode AND ISNULL(TaxInv,0) = 1
632 UNION ALL
633 SELECT TaxCode FROM dbo.MyTaxMode (NOLOCK) WHERE TaxCode = @TaxCode AND ISNULL(TaxInv,0) = 1)
634 BEGIN
635 SET @TaxInv = 1
636 END
637
638 IF EXISTS( SELECT TaxCode FROM SysErp.ERP.TaxMode (NOLOCK) WHERE TaxCode = @TaxCode AND ISNULL(FaraTva,0) = 1
639 UNION ALL
640 SELECT TaxCode FROM dbo.MyTaxMode (NOLOCK) WHERE TaxCode = @TaxCode AND ISNULL(FaraTva,0) = 1)
641 BEGIN
642 SET @FaraTva = 1
643 SET @Tva = 0
644 END
645 */
646 -- Daca vine din comanda, verific daca am rezervare
647 IF @SorderPozId IS NOT NULL
648 BEGIN
649 SELECT TOP 1
650 @IntrPozId = sp.IntrPozId
651 , @IntrPozOrigId = sp.IntrPozOrigId
652 , @GestiuneId = sp.GestiuneId
653 , @SerieIntrare = sp.SerieIntrare
654 , @LotIntrare = sp.LotIntrare
655 , @DataFabricare = sp.DataFabricare
656 , @DataExpirare = sp.DataExpirare
657 , @ClientCustodieId = sp.ClientCustodieId
658 , @Stoc = sp.Cant
659
660 , @FurnizorID = sp.FurnizorId
661 , @FurnizorOriginalID = sp.FurnizorOriginalId
662 , @NumarIntr = sp.NumarIntr
663 , @DataIntr = sp.DataIntr
664 , @DocIntrId = sp.DocIntrId
665 , @TipDocIntrID = sp.TipDocIntrId
666 , @DataScadenta = sp.DataScadentaIntr
667 , @NumarFactIntr = sp.NumarFactIntr
668 , @DataFactIntr = sp.DataFactIntr
669 , @PretIntrare = sp.PretIntrare
670 , @PretIntrareRedus = sp.PretIntrareRedus
671 , @PretCost = sp.PretCost
672 , @PretCMP = sp.PretCmp
673 , @POOrderPozId = sp.POrderPozId
674 , @Amanunt = null
675 , @PretAmanuntStoc = sp.PretAmanunt
676
677 , @RezervareId = sp.SOrderPozId
678
679 , @CentruCostStocId = stoc.CentruCostId
680 , @ArticolBugetStocId = stoc.ArticolBugetId
681 , @BusinessLineStocId = stoc.BusinessLineId
682
683 , @CentruCostGestiuneId = g.CentruCostId
684 , @ArticolBugetGestiuneId = g.ArticolBugetId
685 , @BusinessLineGestiuneId = g.BusinessLineId
686
687 , @CentruCostContareGestiuneId = cpf.CentruCostId
688 , @ArticolBugetContareGestiuneId = cpf.ArticolBugetId
689 , @BusinessLineContareGestiuneId = cpf.BusinessLineId
690 , @Cod1 =stoc.Cod1
691 , @cod2=stoc.Cod2
692 , @cod3=stoc.Cod3
693 FROM dbo.SOrderPoz sp (NOLOCK)
694 INNER JOIN inv.Stoc stoc (NOLOCK)
695 ON stoc.IntrPozId = sp.IntrPozId
696 AND stoc.GestiuneId = sp.GestiuneId
697 AND ISNULL(stoc.SerieIntrare,'') = ISNULL(sp.SerieIntrare,'')
698 AND ISNULL(stoc.LotIntrare,'') = ISNULL(sp.LotIntrare,'')
699 AND ISNULL(stoc.DataFabricare,'') = ISNULL(sp.DataFabricare,'')
700 AND ISNULL(stoc.DataExpirare,'') = ISNULL(sp.DataExpirare,'')
701 AND ISNULL(stoc.ClientCustodieId,0) = ISNULL(sp.ClientCustodieId,0)
702 INNER JOIN dbo.tblGestiuni g (NOLOCK)
703 ON g.GestiuneId = sp.GestiuneId
704 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
705 ON cpf.ContabProdusId = g.ContabProdusId
706 AND cpf.FirmaId = @FirmaId
707 AND cpf.DivizieId = @DivizieId
708 WHERE sp.SOrderPozParentId = @SorderPozId
709 AND sp.Cant > 0
710 ORDER BY sp.DataIntr
711 END
712
713 -- Daca nu vine din comanda/daca nu am rezervare, verific daca am stoc
714 IF @IntrPozId IS NULL
715 BEGIN
716 IF @RetetaPozId IS NOT NULL
717 BEGIN
718 SELECT @RetetaCapId = RetetaCapId
719 FROM dbo.tblRetetaPoz (NOLOCK)
720 WHERE RetetaPozId = @RetetaPozId
721
722 SELECT TOP 1
723 @IntrPozId = stoc.IntrPozId
724 , @IntrPozOrigId = stoc.IntrPozOrigId
725 , @GestiuneId = stoc.GestiuneId
726 , @SerieIntrare = stoc.SerieIntrare
727 , @LotIntrare = stoc.LotIntrare
728 , @DataFabricare = stoc.DataFabricare
729 , @DataExpirare = stoc.DataExpirare
730 , @Stoc = stoc.Stoc
731
732 , @FurnizorID = stoc.FurnizorID
733 , @FurnizorOriginalID = stoc.FurnizorOriginalID
734 , @NumarIntr = stoc.NumarIntr
735 , @DataIntr = stoc.DataIntr
736 , @DocIntrId = stoc.DocIntrId
737 , @TipDocINtrID = stoc.TipDocINtrID
738 , @DataScadenta = stoc.DataScadenta
739 , @NumarFactIntr = stoc.NumarFactIntr
740 , @DataFactIntr = stoc.DataFactIntr
741 , @PretIntrare = stoc.PretIntrare
742 , @PretIntrareRedus = stoc.PretIntrareRedus
743 , @PretCost = stoc.PretCost
744 , @PretCMP = stoc.PretCmp
745 , @POOrderPozId = stoc.POrderPozId
746 , @Amanunt = ISNULL(g.Amanunt,0)
747 , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
748
749 , @CentruCostStocId = stoc.CentruCostId
750 , @ArticolBugetStocId = stoc.ArticolBugetId
751 , @BusinessLineStocId = stoc.BusinessLineId
752
753 , @CentruCostGestiuneId = g.CentruCostId
754 , @ArticolBugetGestiuneId = g.ArticolBugetId
755 , @BusinessLineGestiuneId = g.BusinessLineId
756
757 , @CentruCostContareGestiuneId = cpf.CentruCostId
758 , @ArticolBugetContareGestiuneId = cpf.ArticolBugetId
759 , @BusinessLineContareGestiuneId = cpf.BusinessLineId
760 , @Cod1 =stoc.Cod1
761 , @cod2=stoc.Cod2
762 , @cod3=stoc.Cod3
763 FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
764 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
765 ON G.GestiuneId = stoc.GestiuneID
766 INNER JOIN #ReteteCap gr
767 ON gr.RetetaCapId = @RetetaCapId
768 AND gr.val = g.GestiuneId
769 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
770 ON cpf.ContabProdusId = g.ContabProdusId
771 AND cpf.FirmaId = @FirmaId
772 AND cpf.DivizieId = @DivizieId
773 WHERE stoc.FirmaId = @FirmaId
774 AND stoc.DivizieId = @DivizieId
775 AND Stoc > 0
776 AND stoc.ProdusID = @ProdusId
777 AND stoc.ClientCustodieId IS NULL
778 AND (ISNULL(@setare518,0) = 0
779 OR ( ISNULL(@setare518,0) = 1 AND stoc.DataIntr <= @DataIes
780 )
781 OR ( ISNULL(@setare518,0) = 2 AND CONVERT(NVARCHAR(6),stoc.DataIntr,112) <= CONVERT(NVARCHAR(6),@DataIes,112)
782 )
783 )
784 ORDER BY stoc.DataIntr
785 END
786
787 ELSE
788 BEGIN
789 SELECT TOP 1
790 @IntrPozId = stoc.IntrPozId
791 , @IntrPozOrigId = stoc.IntrPozOrigId
792 , @GestiuneId = stoc.GestiuneId
793 , @SerieIntrare = stoc.SerieIntrare
794 , @LotIntrare = stoc.LotIntrare
795 , @DataFabricare = stoc.DataFabricare
796 , @DataExpirare = stoc.DataExpirare
797 , @Stoc = stoc.Stoc
798
799 , @FurnizorID = stoc.FurnizorID
800 , @FurnizorOriginalID = stoc.FurnizorOriginalID
801 , @NumarIntr = stoc.NumarIntr
802 , @DataIntr = stoc.DataIntr
803 , @DocIntrId = stoc.DocIntrId
804 , @TipDocINtrID = stoc.TipDocINtrID
805 , @DataScadenta = stoc.DataScadenta
806 , @NumarFactIntr = stoc.NumarFactIntr
807 , @DataFactIntr = stoc.DataFactIntr
808 , @PretIntrare = stoc.PretIntrare
809 , @PretIntrareRedus = stoc.PretIntrareRedus
810 , @PretCost = stoc.PretCost
811 , @POOrderPozId = stoc.POrderPozId
812 , @Amanunt = ISNULL(g.Amanunt,0)
813 , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
814
815 , @CentruCostStocId = stoc.CentruCostId
816 , @ArticolBugetStocId = stoc.ArticolBugetId
817 , @BusinessLineStocId = stoc.BusinessLineId
818
819 , @CentruCostGestiuneId = g.CentruCostId
820 , @ArticolBugetGestiuneId = g.ArticolBugetId
821 , @BusinessLineGestiuneId = g.BusinessLineId
822
823 , @CentruCostContareGestiuneId = cpf.CentruCostId
824 , @ArticolBugetContareGestiuneId = cpf.ArticolBugetId
825 , @BusinessLineContareGestiuneId = cpf.BusinessLineId
826 , @Cod1 =stoc.Cod1
827 , @cod2=stoc.Cod2
828 , @cod3=stoc.Cod3
829 FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
830 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
831 ON G.GestiuneId = stoc.GestiuneID
832 LEFT JOIN #GestiuniRetail gest
833 ON gest.GestiuneId = g.GestiuneId
834 AND (gest.TipDocId IS NULL OR gest.TipDocId = @TipDocId)
835 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
836 ON cpf.ContabProdusId = g.ContabProdusId
837 AND cpf.FirmaId = @FirmaId
838 AND cpf.DivizieId = @DivizieId
839 WHERE stoc.FirmaId = @FirmaId
840 AND stoc.DivizieId = @DivizieId
841 AND Stoc > 0
842 AND stoc.ProdusID = @ProdusId
843 AND stoc.ClientCustodieId IS NULL
844 AND (ISNULL(@setare518,0) = 0
845 OR ( ISNULL(@setare518,0) = 1 AND stoc.DataIntr <= @DataIes
846 )
847 OR ( ISNULL(@setare518,0) = 2 AND CONVERT(NVARCHAR(6),stoc.DataIntr,112) <= CONVERT(NVARCHAR(6),@DataIes,112)
848 )
849 )
850 AND ((@GestiuneLivrareId IS NOT NULL AND stoc.GestiuneId = @GestiuneLivrareId)
851 OR (@GestiuneLivrareId IS NULL AND gest.GestiuneId IS NOT NULL))
852 ORDER BY stoc.DataIntr
853 END
854 END
855
856 -- Daca nu exista stoc, verific daca exista reteta
857 IF @IntrPozId IS NULL
858 BEGIN
859
860 SELECT @RetetaCapId = NULL
861 SELECT @GestiuneDestinatieId = NULL
862
863 SELECT TOP 1
864 @RetetaCapId = RetetaCapId
865 , @GestiuneDestinatieId = GestiuneDestinatieId
866
867 , @CentruCostContareProdusId = CentruCostContareProdusId
868 , @ArticolBugetContareProdusId = ArticolBugetContareProdusId
869 , @BusinessLineContareProdusId = BusinessLineContareProdusId
870
871 , @CentruCostContareGestiuneId = CentruCostContareGestiuneId
872 , @ArticolBugetContareGestiuneId = ArticolBugetContareGestiuneId
873 , @BusinessLineContareGestiuneId = BusinessLineContareGestiuneId
874
875 , @CentruCostGestiuneId = CentruCostGestiuneId
876 , @ArticolBugetGestiuneId = ArticolBugetGestiuneId
877 , @BusinessLineGestiuneId = BusinessLineGestiuneId
878 FROM #ReteteCap
879 WHERE ProdusId = @ProdusId
880 AND @DataIes BETWEEN ISNULL(ValabilDeLa,@DataIes) AND ISNULL(ValabilPanaLa,@DataIes)
881 ORDER BY
882 ISNULL(ValabilDeLa,@DataIes)
883
884 IF @RetetaCapId IS NOT NULL
885 AND EXISTS(SELECT TOP 1 * FROM dbo.tblRetetaPoz (NOLOCK) WHERE RetetaCapId = @RetetaCapId AND ISNULL(ExcludereDinMeniu,0) = 0 AND ISNULL(Cant,0) <> 0)
886 --AND NOT EXISTS( SELECT TOP 1 * FROM dbo.tblRetetaPoz (NOLOCK) WHERE RetetaCapId = @RetetaCapId AND ISNULL(Cant,0) = 0 AND ISNULL(ExcludereDinMeniu,0) = 0)
887
888 BEGIN
889 -- Inserez detaliile din reteta produsului
890 INSERT INTO inv.IesPoz
891 (
892 IesCapId
893 , FirmaID
894 , DivizieId
895 , ProdusID
896 , IntrPozId
897 , IntrPozOrigId
898 , CantIesire
899 , CantFacturata
900 , Cantitate2
901 --, PretVanzare
902 --, PretVanzareRedus
903 , TaxCode
904 , TaxInv
905 , Tva
906 , GestiuneID
907 , PretAmanunt
908 , PretAmanuntRedus
909 , SerieIntrare
910 , SOrderPozId
911 , ValoareRedusaFaraTva
912 , ValoareRedusaTva
913 , ValoareFaraTva
914 , ValoareTva
915 , ParentIesPozId
916 , RetetaPozId
917 , RetailPozIesireId
918 , DinJob
919 )
920 SELECT
921 p.IesCapId
922 , p.FirmaId
923 , p.DivizieId
924 , retpoz.ProdusId
925 , null -- IntrPozId
926 , null -- IntrPozOrigId
927 , ROUND(CASE WHEN retcap.Cant IS NOT NULL
928 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
929 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
930 END,@Setare623) -- CantIesire
931 , ROUND(CASE WHEN cap.DocId <> 8
932 THEN CASE WHEN retcap.Cant IS NOT NULL
933 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
934 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
935 END
936 ELSE 0
937 END,@setare623) -- CantFacturata
938 , ROUND(CASE WHEN retcap.Cant IS NOT NULL
939 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
940 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
941 END * pdet.GreutateNeta,@Setare623)
942 --, retpoz.Pret -- PretVanzare
943 --, retpoz.Pret -- PretVanzareRedus
944 , p.TaxCode
945 , p.TaxInv
946 , p.Tva --t.Tva
947 , null -- GestiuneId
948 , null -- PretAmanunt
949 , null -- PretAmanuntRedus
950 , null -- SerieIntrare
951 , null
952 , ROUND(ROUND(CASE WHEN retcap.Cant IS NOT NULL
953 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
954 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
955 END,@Setare623) * retpoz.Pret,2)
956 , ROUND(ROUND(CASE WHEN retcap.Cant IS NOT NULL
957 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
958 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) *ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
959 END,@setare623) * retpoz.Pret * t.Tva/100.00,2)
960 , ROUND(ROUND(CASE WHEN retcap.Cant IS NOT NULL
961 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
962 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
963 END,@Setare623) * retpoz.Pret,@setare351)
964 , ROUND(ROUND(CASE WHEN retcap.Cant IS NOT NULL
965 THEN (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
966 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant2) * ISNULL(NULLIF(p.Cantitate2,0),p.CantIesire*pp.GreutateNeta)
967 END,@setare623) * retpoz.Pret * t.Tva/100.00,@setare351)
968 , ISNULL(p.ParentIesPozId,p.IesPozId)
969 , retpoz.RetetaPozId
970 , p.RetailPozIesireId
971 , 2
972 FROM inv.IesPoz p (NOLOCK)
973 INNER JOIN dbo.tblProduse pp (NOLOCK)
974 ON pp.ProdusId = p.ProdusId
975 INNER JOIN inv.IesCap cap (NOLOCK)
976 ON cap.IesCapId = p.IesCapId
977 AND cap.DataAnulare IS NULL
978 INNER JOIN dbo.tblRetetaCap retcap (NOLOCK)
979 ON retcap.RetetaCapId = @RetetaCapId
980 INNER JOIN dbo.tblRetetaPoz retpoz (NOLOCK)
981 ON retpoz.RetetaCapId = @RetetaCapId
982 INNER JOIN dbo.tblProduse pdet (NOLOCK)
983 ON pdet.ProdusId = retpoz.ProdusID
984 INNER JOIN dbo.tblTva t (NOLOCK)
985 on t.TvaId = pdet.TvaId
986 LEFT JOIN inv.TipDoc td (NOLOCK)
987 ON td.TipDocId = cap.TipDocId
988 LEFT JOIN inv.IesPoz principal (NOLOCK)
989 ON principal.IesPozId = p.ParentIesPozId
990 WHERE p.IesPozId = @IesPozId
991 AND (ISNULL(td.IgnoraAmbalaje,0) = 0 OR ISNULL(pdet.Ambalaj,0) = 0)
992 AND (ISNULL(retpoz.ExcludereDinMeniu,0) = 0 OR COALESCE(principal.IesPoz2Id,p.IesPoz2Id,0) = 0)
993 AND ISNULL(retpoz.Cant,0) <> 0
994
995 --Daca este reteta la reteta fac update pe cantitate
996 UPDATE inv.IesPoz
997 SET CantIesire = 0
998 WHERE IesPozId = @IesPozId
999 AND ParentIesPozId IS NOT NULL
1000
1001
1002 -- Setez gestiunea destinatie
1003 UPDATE inv.IesPoz
1004 SET GestiuneId = @GestiuneDestinatieId
1005 , RetetaCapId = @RetetaCapId
1006 , ContineDetalii = 1
1007 , DinJob = 2
1008 , CentruCostId = COALESCE(CentruCostId, CASE WHEN @Setare604 = 1 THEN @CentruCostStocId END, @CentruCostTipDocId, @CentruCostContareProdusId, @CentruCostContareGestiuneId, @CentruCostGestiuneId, @CentruCostCapId, @CentruCostStocId)
1009 , ArticolBugetId = COALESCE(ArticolBugetId, CASE WHEN @Setare604 = 1 THEN @ArticolBugetStocId END, @ArticolBugetTipDocId, @ArticolBugetContareProdusId, @ArticolBugetContareGestiuneId, @ArticolBugetGestiuneId, @ArticolBugetCapId, @ArticolBugetStocId)
1010 , BusinessLineId = COALESCE(BusinessLineId, CASE WHEN @Setare604 = 1 THEN @BusinessLineStocId END, @BusinessLineTipDocId, @BusinessLineContareProdusId, @BusinessLineContareGestiuneId, @BusinessLineGestiuneId, @BusinessLineCapId, @BusinessLineStocId)
1011 WHERE IesPozId = @IesPozId
1012 AND ParentIesPozId IS NULL
1013
1014 INSERT #TempSync
1015 (
1016 IesCapId
1017 , ClientId
1018 , DocId
1019 , TipDocId
1020 , DataIes
1021
1022 , ProdusId
1023 , IesPozId
1024 , ParentIesPozId
1025
1026 , RetetaPozId
1027 , CantIesire
1028 , Prelucrat
1029 , RecalculPret
1030
1031 , CentruCostTipDocId
1032 , ArticolBugetTipDocId
1033 , BusinessLineTipDocId
1034
1035 , CentruCostCapId
1036 , ArticolBugetCapId
1037 , BusinessLineCapId
1038
1039 , CentruCostContareProdusId
1040 , ArticolBugetContareProdusId
1041 , BusinessLineContareProdusId
1042 )
1043 SELECT
1044 p.IesCapId
1045 , cap.ClientId
1046 , cap.DocId
1047 , cap.TipDocId
1048 , cap.DataIes
1049
1050 , p.ProdusId
1051 , p.IesPozId
1052 , p.ParentIesPozId
1053
1054 , p.RetetaPozId
1055 , p.CantIesire
1056 , 0
1057 , @RecalculPret
1058
1059 , @CentruCostTipDocId
1060 , @ArticolBugetTipDocId
1061 , @BusinessLineTipDocId
1062
1063 , @CentruCostCapId
1064 , @ArticolBugetCapId
1065 , @BusinessLineCapId
1066
1067 , cpf.CentruCostId
1068 , cpf.ArticolBugetId
1069 , cpf.BusinessLineId
1070 FROM inv.IesPoz p (NOLOCK)
1071 INNER JOIN inv.IesCap cap (NOLOCK)
1072 ON cap.IesCapId = p.IesCapId
1073 INNER JOIN dbo.tblProduseDivizii pd (NOLOCK)
1074 ON pd.ProdusId = p.ProdusId
1075 AND pd.DivizieId = @DivizieId
1076 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
1077 ON cpf.ContabProdusId = pd.ContabProdusId
1078 AND cpf.FirmaId = @FirmaId
1079 AND cpf.DivizieId = @DivizieId
1080 WHERE p.ParentIesPozId = @IesPozId
1081 END
1082 END
1083
1084 IF @IntrPozId IS NOT NULL
1085 BEGIN
1086 UPDATE inv.IesPoz
1087 SET IntrPozId = @IntrPozId
1088 , IntrPozOrigId = @IntrPozOrigId
1089 , GestiuneId = @GestiuneId
1090 , SerieIntrare = @SerieIntrare
1091 , LotIntrare = @LotIntrare
1092 , DataFabricare = @DataFabricare
1093 , DataExpirare = @DataExpirare
1094 , ClientCustodieId = @ClientCustodieId
1095
1096 , FurnizorID = @FurnizorID
1097 , FurnizorOriginalID = @FurnizorOriginalID
1098 , NumarIntr = @NumarIntr
1099 , DataIntr = @DataIntr
1100 , DocIntrId = @DocIntrId
1101 , TipDocINtrID = @TipDocINtrID
1102 , DataScadentaIntr = @DataScadenta
1103 , NumarFactIntr = @NumarFactIntr
1104 , DataFactIntr = @DataFactIntr
1105 , PretIntrare = @PretIntrare
1106 , PretIntrareRedus = @PretIntrareRedus
1107 , PretCost = @PretCost
1108 , PretCmp = @PretCMP
1109 , POrderPozId = @POOrderPozId
1110 , Amanunt = @Amanunt
1111 , PretAmanunt = ISNULL(PretAmanunt,@PretAmanuntStoc)
1112 , PretAmanuntRedus = ISNULL(PretAmanuntRedus,@PretAmanuntStoc)
1113 , PretVanzare = CASE WHEN @DocId = 13 THEN @PretCost ELSE PretVanzare END
1114 , PretVanzareRedus = CASE WHEN @DocId = 13 THEN @PretCost ELSE PretVanzareRedus END
1115
1116 , SorderPozId = ISNULL(@RezervareId,@SorderPozId)
1117
1118 --, TaxCode = ISNULL(TaxCode,@TaxCode)
1119 --, TaxInv = CASE WHEN TaxCode IS NULL THEN @TaxInv ELSE TaxCode END
1120 --, Tva = CASE WHEN TaxCode IS NULL THEN @Tva ELSE Tva END
1121
1122 , CantIesire = CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE CantIesire END
1123 , CantRetur = CASE WHEN ISNULL(@CantRetur,0) > @Stoc THEN @Stoc ELSE CantRetur END
1124 , ValoareRedusaFaraTva = CASE WHEN @DocId <> 13 AND @CantIesire <= @Stoc
1125 THEN ValoareRedusaFaraTva
1126 ELSE ROUND( CASE WHEN @DocId = 13 THEN @PretCost ELSE ISNULL(PretVanzareRedus,PretVanzare) END
1127 * CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE @CantIesire END, 2)
1128 END
1129 , ValoareRedusaTva = CASE WHEN @DocId <> 13 AND @CantIesire <= @Stoc
1130 THEN ValoareRedusaTva
1131 ELSE ROUND( CASE WHEN @DocId = 13 THEN @PretCost ELSE ISNULL(PretVanzareRedus,PretVanzare) END
1132 * CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE @CantIesire END
1133 * CASE WHEN TaxCode IS NULL THEN @Tva ELSE Tva END
1134 * CASE WHEN TaxCode IS NULL THEN (1-ISNULL(@TaxInv,0)) ELSE (1-ISNULL(TaxInv,0)) END / 100.00, 2)
1135 END
1136 , ValoareFaraTva = CASE WHEN @DocId <> 13 AND @CantIesire <= @Stoc
1137 THEN ValoareFaraTva
1138 ELSE ROUND( CASE WHEN @DocId = 13 THEN @PretCost ELSE PretVanzare END
1139 * CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE @CantIesire END, @Setare351)
1140 END
1141 , ValoareTva = CASE WHEN @DocID <> 13 AND @CantIesire <= @Stoc
1142 THEN ValoareTva
1143 ELSE ROUND( CASE WHEN @DocID = 13 THEN @PretCost ELSE PretVanzare END
1144 * CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE @CantIesire END
1145 * CASE WHEN TaxCode IS NULL THEN @Tva ELSE Tva END
1146 * CASE WHEN TaxCode IS NULL THEN (1-ISNULL(@TaxInv,0)) ELSE (1-ISNULL(TaxInv,0)) END / 100.00, @setare351)
1147 END
1148 , DinJob = CASE WHEN ISNULL(DinJob,0) = 2 THEN 2 ELSE 1 END
1149
1150 , CentruCostId = COALESCE(CentruCostId, CASE WHEN @Setare604 = 1 THEN @CentruCostStocId END, @CentruCostTipDocId, @CentruCostContareProdusId, @CentruCostContareGestiuneId, @CentruCostGestiuneId, @CentruCostCapId, @CentruCostStocId)
1151 , ArticolBugetId = COALESCE(ArticolBugetId, CASE WHEN @Setare604 = 1 THEN @ArticolBugetStocId END, @ArticolBugetTipDocId, @ArticolBugetContareProdusId, @ArticolBugetContareGestiuneId, @ArticolBugetGestiuneId, @ArticolBugetCapId, @ArticolBugetStocId)
1152 , BusinessLineId = COALESCE(BusinessLineId, CASE WHEN @Setare604 = 1 THEN @BusinessLineStocId END, @BusinessLineTipDocId, @BusinessLineContareProdusId, @BusinessLineContareGestiuneId, @BusinessLineGestiuneId, @BusinessLineCapId, @BusinessLineStocId)
1153 , Cod1 = @Cod1
1154 , Cod2 = @Cod2
1155 , Cod3 = @Cod3
1156 WHERE IesPozId = @IesPozId
1157 AND FirmaID = @FirmaID
1158 AND DivizieId = @DivizieID
1159
1160 IF @RezervareId IS NOT NULL -- Scad rezervarea
1161 BEGIN
1162 UPDATE dbo.SOrderPoz
1163 SET Cant = CASE WHEN @CantIesire > @Stoc THEN 0 ELSE Cant - @CantIesire END
1164 WHERE SOrderPozId = @RezervareId
1165
1166 UPDATE dbo.SOrderPoz
1167 SET CantRezervata = CantRezervata - CASE WHEN @CantIesire > @Stoc THEN @Stoc ELSE @CantIesire END
1168 WHERE SOrderPozId = @SorderPozId
1169 END
1170
1171 ELSE -- Scad stocul
1172 BEGIN
1173
1174 UPDATE inv.Stoc
1175 SET Stoc = CASE WHEN @CantIesire > @Stoc THEN 0 ELSE Stoc - @CantIesire END
1176 WHERE FirmaID = @FirmaID
1177 AND DivizieID = @DivizieID
1178 AND IntrPozId = @IntrPozId
1179 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
1180 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
1181 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
1182 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
1183 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'')
1184 AND ISNULL(ClientCustodieId,0) = ISNULL(@ClientCustodieId,0)
1185 END
1186
1187 IF (@CantIesire > @Stoc)
1188 BEGIN
1189 INSERT INTO inv.IesPoz
1190 (
1191 IesCapId
1192 , ParentIesPozId
1193 , FirmaID
1194 , DivizieId
1195 , ProdusID
1196 , IntrPozId
1197 , IntrPozOrigId
1198 , CantIesire
1199 , CantRetur
1200 , CantFacturata
1201 , PretVanzare
1202 , PretVanzareRedus
1203 , PretAmanunt
1204 , PretAmanuntRedus
1205 , RetetaPozId
1206 , SOrderPozId
1207 , ProdusFinitId
1208 , RetailPozIesireId
1209 , RetailPozIesireReturId
1210
1211 , TaxCode
1212 , TaxInv
1213 , Tva
1214
1215 , ValoareRedusaFaraTva
1216 , ValoareRedusaTva
1217 , ValoareFaraTva
1218 , ValoareTva
1219 , DinJob
1220 )
1221 SELECT
1222 IesCapId
1223 , ParentIesPozId
1224 , FirmaID
1225 , DivizieId
1226 , ProdusID
1227 , NULL
1228 , NULL
1229 , @CantIesire - @Stoc
1230 , CASE WHEN ISNULL(@CantRetur,0) > 0 THEN @CantRetur - @Stoc END
1231 , 0
1232 , PretVanzare
1233 , PretVanzareRedus
1234 , PretAmanunt
1235 , PretAmanuntRedus
1236 , RetetaPozId
1237 , SOrderPozId
1238 , ProdusFinitId
1239 , RetailPozIesireId
1240 , RetailPozIesireReturId
1241
1242 , TaxCode
1243 , TaxInv
1244 , Tva
1245
1246 , ROUND(ISNULL(PretVanzareRedus, PretVanzare) * (@CantIesire - @Stoc), 2)
1247 , ROUND(ISNULL(PretVanzareRedus, PretVanzare) * (@CantIesire - @Stoc) * Tva * (1-ISNULL(TaxInv,0)) / 100.00, 2)
1248 , ROUND(PretVanzare * (@CantIesire - @Stoc),@Setare351)
1249 , ROUND(PretVanzare * (@CantIesire - @Stoc) * Tva * (1-ISNULL(TaxInv,0)) / 100.00,@Setare351)
1250 , DinJob
1251
1252 FROM inv.IesPoz (NOLOCK)
1253 WHERE IesPozId = @IesPozID
1254 AND FirmaID = @FirmaID
1255 AND DivizieID = @DivizieID
1256
1257 SET @IesPozNewId = SCOPE_IDENTITY()
1258
1259 INSERT #TempSync
1260 (
1261 IesCapId
1262 , ClientId
1263 , DocId
1264 , TipDocId
1265 , DataIes
1266
1267 , ProdusId
1268 , IesPozId
1269 , SorderPozId
1270 , ParentIesPozId
1271
1272 , RetetaPozId
1273 , CantIesire
1274 , CantRetur
1275 , Prelucrat
1276 , RecalculPret
1277
1278 , CentruCostTipDocId
1279 , ArticolBugetTipDocId
1280 , BusinessLineTipDocId
1281
1282 , CentruCostCapId
1283 , ArticolBugetCapId
1284 , BusinessLineCapId
1285
1286 , CentruCostContareProdusId
1287 , ArticolBugetContareProdusId
1288 , BusinessLineContareProdusId
1289
1290 )
1291 SELECT
1292 p.IesCapId
1293 , cap.ClientId
1294 , cap.DocId
1295 , cap.TipDocId
1296 , cap.DataIes
1297
1298 , p.ProdusId
1299 , p.IesPozId
1300 , @SorderPozId
1301 , p.ParentIesPozId
1302
1303 , p.RetetaPozId
1304 , p.CantIesire
1305 , p.CantRetur
1306 , 0
1307 , @RecalculPret
1308
1309 , @CentruCostTipDocId
1310 , @ArticolBugetTipDocId
1311 , @BusinessLineTipDocId
1312
1313 , @CentruCostCapId
1314 , @ArticolBugetCapId
1315 , @BusinessLineCapId
1316
1317 , @CentruCostContareProdusId
1318 , @ArticolBugetContareProdusId
1319 , @BusinessLineContareProdusId
1320 FROM inv.IesPoz p (NOLOCK)
1321 INNER JOIN inv.IesCap cap (NOLOCK)
1322 ON cap.IesCapId = p.IesCapId
1323 WHERE p.IesPozId = @IesPozNewId
1324 END
1325 END
1326
1327 UPDATE #TempSync
1328 SET Prelucrat = 1
1329 WHERE TempSyncId = @TempSyncId
1330
1331 END
1332
1333 IF EXISTS (SELECT TOP 1 * FROM #TempSync WHERE ParentIesPozId IS NOT NULL AND ISNULL(Prelucrat,0) = 1)
1334 BEGIN
1335 UPDATE poz
1336 SET poz.PretCost = ROUND(ISNULL(det.ValoareCost,0) / ISNULL(NULLIF(poz.CantIesire,0),1),@NrZecimalePretIntrare)
1337 FROM inv.IesPoz poz
1338 INNER JOIN (SELECT p.ParentIesPozId
1339 , SUM(CASE WHEN prod.TipItem = 'P' THEN p.CantIesire * ISNULL(p.PretCost,0) ELSE 0 END) AS ValoareCost
1340 FROM inv.IesPoz p (NOLOCK)
1341 INNER JOIN dbo.tblProduse prod (NOLOCK)
1342 ON prod.ProdusId = p.ProdusId
1343 INNER JOIN (SELECT temp.ParentIesPozId as IesPozId
1344 FROM #TempSync temp
1345 WHERE ISNULL(Prelucrat,0) = 1
1346 --AND ISNULL(RecalculPret,0) = 1
1347 GROUP BY temp.ParentIesPozId) temp
1348 ON temp.IesPozId = p.ParentIesPozId
1349 GROUP BY p.ParentIesPozId) det
1350 on poz.IesPozId = det.ParentIesPozId
1351
1352 UPDATE fact
1353 SET fact.PretCost = poz.PretCost
1354 FROM inv.IesPoz fact
1355 INNER JOIN inv.IesPoz poz
1356 ON poz.IesPozId = fact.IesPozAvizId
1357 INNER JOIN (SELECT temp.ParentIesPozId AS IesPozId
1358 FROM #TempSync temp
1359 WHERE ISNULL(Prelucrat,0) = 1
1360 --AND ISNULL(RecalculPret,0) = 1
1361 GROUP BY temp.ParentIesPozId) temp
1362 ON temp.IesPozId = poz.IesPozId
1363 END
1364
1365 IF EXISTS (SELECT TOP 1 * FROM #TempSync WHERE ParentIesPozId IS NOT NULL AND ISNULL(RecalculPret,0) = 1 AND ISNULL(Prelucrat,0) = 1)
1366 BEGIN -- propagare pret vanzare in detalii
1367
1368 UPDATE poz
1369 SET poz.PretVanzare = ROUND(ROUND(CASE WHEN pr.TipItem = 'S'
1370 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
1371 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1372 END,2) / (1 + poz.Tva / 100.00), @NrZecimalePretVanzare)
1373 ,poz.PretVanzareRedus = ROUND(ROUND(CASE WHEN pr.TipItem = 'S'
1374 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
1375 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1376 END,2) / (1 + poz.Tva / 100.00), @NrZecimalePretVanzare)
1377 ,poz.PretAmanunt = ROUND(CASE WHEN pr.TipItem = 'S'
1378 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
1379 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1380 END,2)
1381 ,poz.PretAmanuntRedus = ROUND(CASE WHEN pr.TipItem = 'S'
1382 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
1383 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1384 END,2)
1385
1386 ,poz.ValoareRedusaFaraTva = ROUND(poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1387 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
1388 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1389 END - ROUND(((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1390 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
1391 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1392 END * poz.Tva) / (100.00 + poz.Tva)), 6), 6)
1393 ,poz.ValoareRedusaTva = ROUND(((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1394 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
1395 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1396 END * poz.Tva) / (100.00 + poz.Tva)), 6)
1397 ,poz.ValoareFaraTva = ROUND(poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1398 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
1399 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1400 END - ROUND((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1401 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
1402 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1403 END * poz.Tva) / (100.00 + poz.Tva), 6), 6)
1404 ,poz.ValoareTva = ROUND((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
1405 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
1406 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
1407 END * poz.Tva) / (100.00 + poz.Tva), 6)
1408 FROM inv.IesPoz poz
1409 INNER JOIN dbo.tblProduse pr (NOLOCK)
1410 ON pr.ProdusId = poz.ProdusId
1411 INNER JOIN dbo.tblTva t (NOLOCK)
1412 ON t.TvaId = pr.TvaId
1413 INNER JOIN (SELECT
1414 p.ParentIesPozId
1415 , SUM(CASE WHEN prod.TipItem = 'P' THEN p.CantIesire * ISNULL(p.PretCost,0) * (1 + p.Tva / 100.00)
1416 ELSE 0 END) AS ValoareCostCuTva
1417
1418 , MAX(pp.CantIesire * pp.PretAmanunt) AS ValAmanuntParinte
1419 , MAX(pp.CantIesire * ISNULL(pp.PretAmanuntRedus,pp.PretAmanunt)) AS ValAmanuntRedusParinte
1420
1421 , SUM(CASE WHEN prod.TipItem = 'S' THEN p.CantIesire * ISNULL(p.PretVanzare,0)
1422 ELSE 0 END) AS ValoareServicii
1423 , SUM(CASE WHEN prod.TipItem = 'S' THEN p.CantIesire * COALESCE(p.PretVanzareRedus,p.PretVanzare,0)
1424 ELSE 0 END) AS ValoareRedusaServicii
1425 FROM inv.IesPoz p (nolock)
1426 INNER JOIN inv.IesPoz (nolock) pp
1427 on pp.IesPozId = p.ParentIesPozId
1428 INNER JOIN dbo.tblProduse prod (NOLOCK)
1429 ON prod.ProdusId = p.ProdusId
1430 INNER JOIN (SELECT temp.ParentIesPozId as IesPozId
1431 FROM #TempSync temp
1432 WHERE ISNULL(RecalculPret,0) = 1
1433 AND ISNULL(Prelucrat,0) = 1
1434 GROUP BY temp.ParentIesPozId) temp
1435 ON temp.IesPozId = p.ParentIesPozId
1436 GROUP BY p.ParentIesPozId) det
1437 ON poz.ParentIesPozId = det.ParentIesPozId
1438
1439 END
1440
1441 IF EXISTS (SELECT TOP 1 * FROM #TempSync WHERE DocId = 13 AND ISNULL(Prelucrat,0) = 1)
1442 BEGIN
1443 UPDATE cap
1444 SET ValoareFaraTva = a.ValoareFaraTva
1445 , ValoareTva = a.ValoareTva
1446 , ValoareReducereFaraTva = a.ValoareReducereFaraTva
1447 , ValoareReducereTva = a.ValoareReducereTva
1448 , NrPozitii = a.NrPozitii
1449 FROM inv.IesCap cap
1450 INNER JOIN (
1451 SELECT
1452 p.IesCapId
1453 , ROUND( SUM(p.CantIesire * ISNULL(p.PretVanzare,p.PretCost)), CAST(@setare351 AS INT)) AS ValoareFaraTva
1454 , ROUND( SUM(p.CantIesire * ISNULL(p.PretVanzare,p.PretCost) * p.Tva * (1 - ISNULL(p.TaxInv,0))/100), CAST(@setare351 AS INT)) AS ValoareTva
1455 , ROUND( SUM(p.CantIesire * ISNULL(p.PretVanzareRedus,p.PretCost)), CAST(@setare351 AS INT)) AS ValoareReducereFaraTva
1456 , ROUND( SUM(p.CantIesire * ISNULL(p.PretVanzareRedus,p.PretCost) * p.Tva * (1 - ISNULL(p.TaxInv,0))/100), CAST(@setare351 AS INT)) AS ValoareReducereTva
1457 , COUNT(p.IesPozId) NrPozitii
1458 FROM inv.IesPoz p (NOLOCK)
1459 INNER JOIN (SELECT IesCapId
1460 FROM #TempSync
1461 WHERE DocId = 13
1462 AND ISNULL(Prelucrat,0) = 1
1463 GROUP BY IesCapId) cap
1464 ON cap.IesCapId = p.IesCapId
1465 WHERE p.ParentIesPozId IS NULL
1466 GROUP BY p.IesCapId) a
1467 ON a.IesCapId = cap.IesCapId
1468 END
1469
1470 IF OBJECT_ID('tempdb..#TempSync') IS NOT NULL
1471 DROP TABLE #TempSync
1472
1473 IF OBJECT_ID('tempdb..#TempSyncRetur') IS NOT NULL
1474 DROP TABLE #TempSyncRetur
1475
1476 CREATE TABLE #TempSyncRetur
1477 (
1478 TempSyncId INT IDENTITY(1,1)
1479 ,IesCapId INT
1480 ,DataIes DATE
1481 ,IesPozId INT
1482 ,IesPozRefId INT
1483 ,RetailPozIesireReturId INT
1484
1485 ,ProdusId INT
1486 ,CantRetur DECIMAL(18,5)
1487 ,Prelucrat BIT
1488 )
1489
1490 INSERT #TempSyncRetur
1491 (
1492 IesCapId
1493 , DataIes
1494 , IesPozId
1495 , IesPozRefId
1496 , RetailPozIesireReturID
1497
1498 , ProdusId
1499 , CantRetur
1500 , Prelucrat
1501 )
1502 SELECT
1503 p.IesCapId
1504 , cap.DataIes
1505 , p.IesPozId
1506 , p.IesPozRefId
1507 , p.RetailPozIesireReturId
1508
1509 , p.ProdusId
1510 , p.CantIesire
1511 , 0
1512 FROM inv.IesPoz p (NOLOCK)
1513 INNER JOIN inv.IesPoz ref (NOLOCK)
1514 ON ref.IesPozId = p.IesPozRefId
1515 AND ref.IesPozAvizId IS NULL
1516 AND ref.IntrPozId IS NOT NULL
1517 INNER JOIN inv.IesCap cap (NOLOCK)
1518 ON cap.IesCapId = p.IesCapId
1519 AND cap.FirmaId = @FirmaId
1520 AND cap.DivizieId = @DivizieId
1521 AND cap.DataAnulare IS NULL
1522 INNER JOIN #Docs tempD
1523 ON tempD.DocId = cap.DocId
1524 INNER JOIN inv.TipDoc td (NOLOCK)
1525 ON td.TipDocId = cap.TipDocId
1526 AND ISNULL(td.Stoc,0) = 1
1527 INNER JOIN dbo.tblProduse pr (NOLOCK)
1528 ON pr.ProdusId = p.Produsid
1529 AND ISNULL(pr.TipItem,'P') <> 'S'
1530 LEFT JOIN #Produse tempP
1531 ON tempP.ProdusId = p.ProdusId
1532 WHERE p.CantIesire < 0
1533 AND p.IntrpozId IS NULL -- nelegat la stoc
1534 AND ISNULL(p.ContineDetalii,0) = 0 -- sa nu contina detalii
1535 AND (
1536 --(cap.DocId = 21 AND cap.IesCapFacturaId IS NULL) -- descarcare pe bon fiscal
1537 (cap.DocId = 21 AND (
1538 (p.RetailPozIesireId IS NOT NULL AND cap.RetailCapIesireId IS NOT NULL AND cap.DocGeneratorId IS NULL AND cap.CapDocGeneratorId IS NULL)
1539 OR (cap.DocId = 21 AND p.RetailPozIesireId IS NULL AND cap.RetailCapIesireId IS NULL AND cap.DocGeneratorId = 26)
1540 )
1541 )
1542 OR (cap.DocId IN (5,6) AND p.IesPozAvizId IS NULL) -- descarcare pe factura
1543 OR cap.DocId = 8 -- descarcare pe aviz
1544 OR cap.DocId = 13 -- descarcare pe bon consum
1545 )
1546 AND (@IesCapId IS NULL OR cap.IesCapId = @IesCapId)
1547 AND (@DeLa IS NULL OR cap.DataIes >= @DeLa)
1548 AND (@PanaLa IS NULL OR cap.DataIes <= @PanaLa)
1549 AND (ISNULL(@ProdusIds,'') = '' OR tempP.ProdusId IS NOT NULL)
1550 AND (@DocFltId is null or cap.Docid=@DocFltId)
1551 AND (@tipDocFltid is null or cap.TipDocId=@tipDocFltid)
1552
1553 WHILE EXISTS (SELECT TOP 1 * FROM #TempSyncRetur WHERE ISNULL(Prelucrat,0) = 0)
1554 BEGIN
1555 SELECT
1556 @TempSyncId = NULL
1557
1558 , @CapIesId = NULL
1559 , @DataIes = NULL
1560 , @IesPozId = NULL
1561 , @IesPozRefId = NULL
1562
1563 , @ProdusId = NULL
1564 , @CantIesire = NULL
1565
1566 , @IntrPozId = NULL
1567 , @IntrPozOrigId = NULL
1568 , @GestiuneId = NULL
1569 , @GestiuneDestinatieId = NULL
1570 , @SerieIntrare = NULL
1571 , @LotIntrare = NULL
1572 , @DataFabricare = NULL
1573 , @DataExpirare = NULL
1574 , @ClientCustodieId = NULL
1575 , @Stoc = NULL
1576
1577 , @FurnizorID = NULL
1578 , @FurnizorOriginalID = NULL
1579 , @NumarIntr = NULL
1580 , @DataIntr = NULL
1581 , @DocIntrId = NULL
1582 , @TipDocIntrID = NULL
1583 , @DataScadenta = NULL
1584 , @NumarFactIntr = NULL
1585 , @DataFactIntr = NULL
1586 , @PretIntrare = NULL
1587 , @PretIntrareRedus = NULL
1588 , @PretCost = NULL
1589 , @PretCMP = NULL
1590 , @POOrderPozId = NULL
1591 , @Amanunt = NULL
1592 , @PretAmanuntStoc = NULL
1593
1594 SELECT TOP 1
1595 @TempSyncId = TempSyncId
1596
1597 , @CapIesId = IesCapId
1598 , @DataIes = DataIes
1599 , @IesPozId = IesPozId
1600 , @IesPozRefId = IesPozRefId
1601
1602 , @ProdusId = ProdusId
1603 , @CantIesire = CantRetur
1604 FROM #TempSyncRetur
1605 WHERE ISNULL(Prelucrat,0) = 0
1606 ORDER BY DataIes
1607
1608 SELECT TOP 1
1609 @IntrPozId = IntrPozId
1610 , @IntrPozOrigId = IntrPozOrigId
1611 , @GestiuneId = GestiuneId
1612 , @SerieIntrare = SerieIntrare
1613 , @LotIntrare = LotIntrare
1614 , @DataFabricare = DataFabricare
1615 , @DataExpirare = DataExpirare
1616 , @ClientCustodieId = ClientCustodieId
1617 , @Stoc = CantIesire
1618
1619 , @FurnizorID = FurnizorId
1620 , @FurnizorOriginalID = FurnizorOriginalId
1621 , @NumarIntr = NumarIntr
1622 , @DataIntr = DataIntr
1623 , @DocIntrId = DocIntrId
1624 , @TipDocIntrID = TipDocIntrId
1625 , @DataScadenta = DataScadentaIntr
1626 , @NumarFactIntr = NumarFactIntr
1627 , @DataFactIntr = DataFactIntr
1628 , @PretIntrare = PretIntrare
1629 , @PretIntrareRedus = PretIntrareRedus
1630 , @PretCost = PretCost
1631 , @PretCMP = PretCMP
1632 , @POOrderPozId = PorderPozId
1633 , @Amanunt = Amanunt
1634 FROM inv.IesPoz (NOLOCK)
1635 WHERE IesPozId = @IesPozRefId
1636
1637 IF @IntrPozId IS NOT NULL
1638 BEGIN
1639 UPDATE inv.IesPoz
1640 SET CantRetur = ISNULL(CantRetur,0) - ISNULL(@Stoc,0)
1641 WHERE IesPozId = @IesPozRefId
1642
1643 UPDATE inv.IesPoz
1644 SET IntrPozId = @IntrPozId
1645 , IntrPozOrigId = @IntrPozOrigId
1646 , GestiuneId = @GestiuneId
1647 , SerieIntrare = @SerieIntrare
1648 , LotIntrare = @LotIntrare
1649 , DataFabricare = @DataFabricare
1650 , DataExpirare = @DataExpirare
1651 , ClientCustodieId = @ClientCustodieId
1652
1653 , FurnizorID = @FurnizorID
1654 , FurnizorOriginalID = @FurnizorOriginalID
1655 , NumarIntr = @NumarIntr
1656 , DataIntr = @DataIntr
1657 , DocIntrId = @DocIntrId
1658 , TipDocIntrId = @TipDocIntrId
1659 , DataScadentaIntr = @DataScadenta
1660 , NumarFactIntr = @NumarFactIntr
1661 , DataFactIntr = @DataFactIntr
1662 , PretIntrare = @PretIntrare
1663 , PretIntrareRedus = @PretIntrareRedus
1664 , PretCost = @PretCost
1665 , PretCMP = @PretCMP
1666 , PorderPozId = @POOrderPozId
1667 , Amanunt = @Amanunt
1668 WHERE IesPozId = @IesPozId
1669
1670 UPDATE inv.Stoc
1671 SET Stoc = Stoc - ISNULL(@Stoc,0)
1672 WHERE IntrPozId = @IntrPozId
1673 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
1674 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
1675 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
1676 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
1677 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'')
1678 AND ISNULL(ClientCustodieId,0) = ISNULL(@ClientCustodieId,0)
1679 END
1680
1681 UPDATE #TempSyncRetur
1682 SET Prelucrat = 1
1683 WHERE TempSyncId = @TempSyncId
1684 END
1685 END
1686 COMMIT TRANSACTION
1687
1688 EXEC dbo.StopSemafor
1689 'DESCARCARESTOC'
1690 , @FirmaId
1691 , @DivizieId
1692
1693 END TRY
1694
1695 BEGIN CATCH
1696
1697
1698 IF XACT_STATE() <> 0
1699 BEGIN
1700 ROLLBACK TRANSACTION
1701 END
1702
1703 EXEC dbo.StopSemafor
1704 'DESCARCARESTOC'
1705 , @FirmaId
1706 , @DivizieId
1707
1708 DECLARE @ErrorMessage nvarchar(4000)
1709 DECLARE @ErrorSeverity int
1710 DECLARE @ErrorState int
1711
1712 SELECT
1713 @ErrorMessage = ERROR_MESSAGE()
1714 , @ErrorSeverity = ERROR_SEVERITY()
1715 , @ErrorState = ERROR_STATE();
1716
1717 RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
1718
1719 END CATCH
1720 END
1721
1722 ELSE
1723 BEGIN
1724 RAISERROR(N'JOB-ul este deja in rulare!', 16, 1)
1725 END
1726END
1727;