· 6 years ago · Nov 20, 2019, 09:14 AM
1USE [DevPluriva]
2GO
3/****** Object: StoredProcedure [Retail].[SyncRetailNewErp] Script Date: 11/20/2019 11:10:05 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [Retail].[SyncRetailNewErp]
9 @FirmaId INT=null
10, @DivizieID INT =null
11, @DinJob BIT = NULL
12, @Descarcare BIT = 1
13--Sincronizare bonuri(cap/pozitii) din Retail.BufferPozitiiBon
14--versiune inclusiv pozitii din buffer cu intrpozid si serieintrare
15AS
16BEGIN
17 -- SET NOCOUNT ON added to prevent extra result sets from
18 -- interfering with SELECT statements.
19 SET NOCOUNT ON;
20 --SET XACT_ABORT ON;
21
22 DECLARE
23 @OK BIT = 0
24 , @DataCrt DATETIME = GETDATE()
25
26 EXEC dbo.StartSemafor
27 'POS_BONURI'
28 , 30
29 , @DataCrt
30 , @DinJob
31 , @OK OUTPUT
32 , @FirmaId
33 , @DivizieID
34
35 IF (ISNULL(@OK, 0) = 1)
36 BEGIN
37 WHILE EXISTS (SELECT TOP 1
38 [FirmaId]
39 , [DivizieId]
40 , [CapIesireId]
41 , [PozIesireId]
42 , [DataValidare]
43 , ISNULL([Identificator],'')
44 , ISNULL([IdentificatorStatieId],0)
45 , ISNULL([CodServer],'')
46 FROM retail.BufferPozitiiBon
47 WHERE FirmaID = @FirmaId
48 AND DivizieID = @DivizieId
49 GROUP BY
50 [FirmaId]
51 , [DivizieId]
52 , [CapIesireId]
53 , [PozIesireId]
54 , [DataValidare]
55 , ISNULL([Identificator],'')
56 , ISNULL([IdentificatorStatieId],0)
57 , ISNULL([CodServer],'')
58 HAVING COUNT(*) > 1)
59 BEGIN
60 DECLARE
61 @capiesireid INT
62 , @poziesireid INT
63 , @datavalidare DATETIME
64 , @identificator NVARCHAR(MAX)
65 , @identificatorid INT
66 , @codserver NVARCHAR(MAX)
67 , @x INT
68
69 SELECT TOP 1
70 @capiesireid = CapIesireID
71 , @poziesireid = PozIesireID
72 , @datavalidare = DataValidare
73 , @identificator = ISNULL(Identificator,'')
74 , @identificatorid = ISNULL(IdentificatorStatieID,0)
75 , @codserver = ISNULL(CodServer,'')
76 , @x = COUNT(*) - 1
77 FROM retail.BufferPozitiiBon
78 WHERE FirmaID = @FirmaId
79 AND DivizieID = @DivizieId
80 GROUP BY
81 [FirmaId]
82 , [DivizieId]
83 , [CapIesireId]
84 , [PozIesireId]
85 , [DataValidare]
86 , ISNULL([Identificator],'')
87 , ISNULL([IdentificatorStatieId],0)
88 , ISNULL([CodServer],'')
89 HAVING COUNT(*) > 1
90
91 DELETE TOP (@x) rb
92 FROM retail.BufferPozitiiBon rb
93 WHERE FirmaID = @firmaid
94 AND DivizieID = @divizieid
95 AND CapIesireID = @capiesireid
96 AND PozIesireID = @poziesireid
97 AND DataValidare = @datavalidare
98 AND ISNULL(Identificator,'') = ISNULL(@identificator,'')
99 AND ISNULL(IdentificatorStatieID,0) = ISNULL(@identificatorid,0)
100 AND ISNULL(CodServer,'') = ISNULL(@codserver,'')
101 END
102
103 BEGIN TRY
104
105 DECLARE
106 @DocIdBon INT
107 , @Debug INT = 0
108
109 , @IesCapId INT
110 , @IesPozId INT
111
112 , @IntrPozId INT
113 , @FurnizorID INT
114 , @FurnizorOriginalID INT
115 , @NumarIntr NVARCHAR(50)
116 , @DataIntr DATE
117 , @DocIntrId INT
118 , @TipDocINtrID INT
119 , @DataScadenta DATE
120 , @NumarFactIntr NVARCHAR(50)
121 , @DataFactIntr DATE
122 , @PretIntrare DECIMAL(18,5)
123 , @PretIntrareRedus DECIMAL(18,5)
124 , @PretCost DECIMAL(18,5)
125 , @PretCMP DECIMAL(18,5)
126 , @PretIntrareValuta DECIMAL(18,5)
127 , @ValutaIntrareId CHAR(3)
128 , @GestiuneId INT
129 , @LotIntrare NVARCHAR(50)
130 , @DataExpirare DATE
131 , @DataFabricare DATE
132 , @SerieIntrare NVARCHAR(50)
133 , @IntrPozOrigId INT
134 , @POOrderPozId INT
135 , @CantIesire DECIMAL(18,5)
136 , @Cant2 DECIMAL(18,5)
137 , @CantRetur DECIMAL(18,5)
138 , @Stoc DECIMAL(18,5)
139 , @TempSyncID INT
140 , @RetailPozIesireReturID INT
141 , @IesPozRefDetId INT
142 , @ProdusID INT
143 , @Serie NVARCHAR(100)
144 , @AgentId INT
145 , @Amanunt BIT
146 , @PretAmanuntBuffer DECIMAL(18,5)
147 , @PretAmanuntStoc DECIMAL(18,5)
148 , @Reev BIT
149 , @UserId int
150 , @GenerareReev BIT
151 , @NrZecimaleAmanunt int
152 , @NrZecimalePretVanzare int
153 , @NrZecimalePretIntrare int
154 , @IdIesPoz INT
155 , @GestiuneDestinatieId INT
156 , @AmanuntGestiuneDestinatie BIT
157 , @setare623 INT
158 , @setare351 INT
159 , @Setare350 INT
160 , @GestiuniMPIds NVARCHAR(MAX)
161
162 SELECT
163 @NrZecimaleAmanunt = ISNULL(dbo.ValoareSetare(529,@FirmaId,@DivizieId),2)
164 , @NrZecimalePretVanzare = ISNULL(dbo.ValoareSetare(247,@FirmaId,@DivizieId),2)
165 , @NrZecimalePretIntrare = ISNULL(dbo.ValoareSetare(295,@FirmaId,@divizieid),2)
166 , @setare623 = ISNULL(dbo.ValoareSetare(623,@FirmaId,@divizieid),2)
167 , @setare351 = dbo.ValoareSetare(351,@FirmaId,@DivizieID)
168 , @setare350 = dbo.ValoareSetare(350,@FirmaId,@DivizieID)
169
170 DECLARE @ErrorMessage NVARCHAR(4000)
171 DECLARE @ErrorSeverity INT
172 DECLARE @ErrorState INT
173 DECLARE @CrsStat INT
174 DECLARE @ValutaDefaultId VARCHAR(10)
175
176 -- Citire configurari din Retail.Config
177 DECLARE @ConfigTipDocIdFacturaCuStoc INT
178 DECLARE @ConfigTipDocIdBonCuStoc INT
179 DECLARE @ConfigTipDocBonConsum INT
180 DECLARE @ConfigVersiune NVARCHAR(10) -- 3-frmwrk vechi, 4-frmwrk nou
181 DECLARE @ConfigGestiuni NVARCHAR(4000) -- coduri gestiuni separate prin virgula
182 DECLARE @GestiuniIdsBonConsum NVARCHAR(MAX)
183 DECLARE @GestiuniRetail NVARCHAR(MAX)
184 DECLARE @ConfigCapListaPreturiID INT -- catalogul de preturi
185 DECLARE @ConfigClientImplicitID INT
186 DECLARE @ConfigID INT
187 DECLARE @ConfigTaxCode INT
188 DECLARE @ConfigTaxCodeBFF INT
189 DECLARE @ConfigTaxCodeBC INT
190 DECLARE @IgnoraAmbalaje BIT
191 DECLARE @TipDocStoc BIT
192 DECLARE @TipDocBCStoc BIT
193 DECLARE @PlajaBCId INT
194 DECLARE @StareCmdNoua INT
195
196 SELECT TOP 1 @StareCmdNoua = StareId
197 FROM dbo.tblStariDoc (NOLOCK)
198 WHERE DocId = 26--comanda client
199 AND FirmaId = @FirmaId
200 AND DivizieId = @DivizieId
201 AND ISNULL(Final, 0) = 1
202 AND ISNULL(FinalizatPartial, 0) = 1
203 AND ISNULL(DocValidat, 0) = 1
204
205 SELECT
206 @ConfigID = ConfigId
207 , @ConfigTipDocIdFacturaCuStoc = TipDocIdFacturaCuStoc
208 , @ConfigTipDocIdBonCuStoc = TipDocIdBonCuStoc
209 , @ConfigTipDocBonConsum = TipDocIdBonConsum
210 , @ConfigVersiune = ISNULL(Versiune,'3')
211 , @ConfigGestiuni = Gestiuni
212 , @ConfigCapListaPreturiID = ISNULL(CapListaPreturiID,0)
213 , @ConfigClientImplicitID = ClientImplicitID
214 , @ConfigTaxCode = TaxCode
215 , @ConfigTaxCodeBFF = TaxCodeBFFactura
216 , @GenerareReev = ISNULL(GenerareReev,0)
217 , @Descarcare = CASE WHEN FaraDescarcareStoc = 1 THEN 0 ELSE @Descarcare END
218 --, @NrZecimaleAmanunt = ISNULL(NrZecimaleAmanunt,2)
219 FROM Retail.Config (NOLOCK)
220 WHERE FirmaId = @FirmaId
221 AND DivizieId = @DivizieId
222
223 IF @ConfigTipDocBonConsum IS NULL
224 BEGIN
225 SELECT TOP 1 @ConfigTipDocBonConsum = TipDocId
226 FROM inv.TipDoc (NOLOCK)
227 WHERE DocId = 13
228 AND Firmaid = @FirmaId
229 AND DivizieId = @DivizieId
230 AND ISNULL(Inactiv,0) = 0
231 END
232
233 SELECT
234 @GestiuniIdsBonConsum = GestiuneIds
235 , @ConfigTaxCodeBC = TaxCodeP
236 , @PlajaBCId = TPlajaId
237 , @TipDocBCStoc = ISNULL(Stoc,0)
238 FROM inv.TipDoc (NOLOCK)
239 WHERE TipDocId = @ConfigTipDocBonConsum
240
241 IF @PlajaBCId IS NULL
242 BEGIN
243 SELECT TOP 1 @PlajaBCId = pd.PlajaId
244 FROM dbo.PlajaDoc pd (NOLOCK)
245 WHERE pd.FirmaId = @FirmaId
246 AND pd.DivizieId = @DivizieID
247 AND pd.DocId = 13
248 AND CAST(@DataCrt as DATE) BETWEEN CAST(ISNULL(pd.DataStart,'1979-01-01') AS DATE) AND CAST(ISNULL(pd.DataEnd,'2100-12-31') AS DATE)
249 END
250
251 SELECT
252 @IgnoraAmbalaje = IgnoraAmbalaje
253 , @TipDocStoc = ISNULL(Stoc,0)
254 FROM inv.TipDoc (NOLOCK)
255 WHERE TipDocId = @ConfigTipDocIdBonCuStoc
256
257 SELECT TOP 1 @GestiuneDestinatieId = GestiuneId
258 ,@AmanuntGestiuneDestinatie = ISNULL(Amanunt,0)
259 FROM dbo.tblGestiuni (NOLOCK)
260 WHERE FirmaId = @FirmaId
261 AND DivizieId = @DivizieId
262 AND ISNULL(Activ,0) = 1
263 AND ISNULL(TipGestiune,0) = 3
264 ORDER BY OrdineVanzare
265
266 IF @GestiuneDestinatieId IS NULL
267 BEGIN
268 SELECT TOP 1 @GestiuneDestinatieId = g.GestiuneId
269 ,@AmanuntGestiuneDestinatie = ISNULL(g.Amanunt,0)
270 FROM dbo.tblGestiuni g (NOLOCK)
271 INNER JOIN dbo.SplitString(@ConfigGestiuni,',') gest
272 on gest.val = g.CodGestiune
273 ORDER BY g.OrdineVanzare
274 END
275
276 SELECT @GestiuniMPIds = ISNULL(@GestiuniMPIds + ',','') + ',' + CAST(GestiuneId AS NVARCHAR(MAX))
277 FROM dbo.tblGestiuni (NOLOCK)
278 WHERE DivizieId = @DivizieId
279 AND FirmaId = @FirmaId
280 AND ISNULL(Activ,0) = 1
281 AND ISNULL(TipGestiune,0) = 6
282 ORDER BY OrdineVanzare
283
284 IF (ISNULL(@ConfigID,0) = 0)
285 RAISERROR ('---Nu au fost facute configurarile!', 16, 1)
286
287 DECLARE
288 @TipDocId INT
289 , @PlajaId INT
290 , @ReevCapId INT
291 , @SYS_PARTID INT
292 , @TaxCodeP INT
293
294 SELECT @SYS_PARTID = PartenerFirmaId
295 FROM dbo.tblFirme (NOLOCK)
296 WHERE FirmaId = @FirmaId
297
298 SELECT @ValutaDefaultId = pf.ValutaDefaultId
299 FROM dbo.tblParteneriFirma pf (NOLOCK)
300 WHERE pf.PartenerFirmaId = @SYS_PARTID
301
302 SELECT TOP 1
303 @TipDocId = TipDocId
304 , @TaxCodeP = TaxCodeP
305 FROM inv.TipDoc (NOLOCK)
306 WHERE DocId = 53
307 AND FirmaId = @FirmaId
308 AND DivizieId = @DivizieID
309 AND ISNULL(Stoc,0) = 1
310
311 SELECT TOP 1 @PlajaId = p.PlajaId
312 FROM dbo.Plaja p (NOLOCK)
313 INNER JOIN dbo.PlajaDoc pd (NOLOCK)
314 on pd.PlajaId = p.PlajaId
315 and pd.FirmaId = @FirmaId
316 and pd.DivizieId = @DivizieID
317 and pd.DocId = 53
318 and CAST(@DataCrt as DATE) BETWEEN CAST(ISNULL(DataStart,'1979-01-01') AS DATE) AND CAST(ISNULL(DataEnd,'2100-12-31') AS DATE)
319
320 --produse echivalate
321 PRINT 'Produse echivalate'
322 DECLARE @Crt INT
323 SET @Crt = 0
324 DECLARE @Nr INT
325 SET @Nr = 1
326
327 WHILE ISNULL(@Crt,0) < 50 AND ISNULL(@Nr,0) <> 0
328 BEGIN
329 SET @Nr = 0
330
331 UPDATE buff
332 SET buff.ProdusID = CAST(REPLACE(eq.NewData,'ProdusId=','') AS INT)
333 FROM Retail.BufferPozitiiBon buff (NOLOCK)
334 INNER JOIN dbo.EchivQueue EQ (NOLOCK)
335 ON 'ProdusId=' + CAST(buff.ProdusID AS VARCHAR(100)) = EQ.OldData
336 WHERE buff.FirmaID = @FirmaId
337 AND buff.DivizieID = @DivizieId
338 AND ISNULL(buff.Prelucrat,0) = 0 --Prelucrat=1 => pozitie sincronizata
339 AND eq.TabelaSursa = 'dbo.tblProduse'
340 AND eq.DataProcesare IS NOT NULL
341 AND ISNULL(eq.MsgProcesare,'') = ''
342
343 SET @Nr = @@ROWCOUNT
344 PRINT 'Nr. produse echivalate gasite pe care s-a facut update:' + CAST(@Nr AS VARCHAR)
345 SET @Crt = @Crt + 1
346 END
347
348 --DocId bon fiscal - 21
349 --DocId factura client - 5
350
351 --temporar Update in buffer docid=33->docid=21
352 UPDATE Retail.BufferPozitiiBon
353 SET DocId = 21
354 WHERE ISNULL(docID, 33) IN (33, 3310)
355
356 SET @DocIdBon = 21
357
358 IF OBJECT_ID('tempdb..#PozitiiBuffer') IS NOT NULL
359 DROP TABLE #PozitiiBuffer
360
361 CREATE TABLE #PozitiiBuffer
362 (
363 IesPozId INT IDENTITY(1,1)
364 ,IesCapId INT
365 ,IesCapInseratId INT
366 ,DocInseratId INT
367 ,DataIesInserat DATE
368 ,ProdusID INT
369 ,IntrPozId INT
370 ,CantIesire DECIMAL(18,4)
371 ,PretVanzare DECIMAL(18,2)
372 ,PretVanzareRedus DECIMAL(18,2)
373 ,Tva INT
374 ,GestiuneID INT
375 ,PretAmanunt DECIMAL(18,2)
376 ,PretAmanuntRedus DECIMAL(18,6)
377 ,Discount DECIMAL(18,2)
378 ,RetailPozIesireID INT
379 ,RetailPozIesireReturID INT
380 ,Identificator VARCHAR(10)
381 ,IdentificatorStatieID INT
382 ,RetailCapIesireID INT
383 ,DataDoc SMALLDATETIME
384 ,ValoareCash DECIMAL(18,2)
385 ,ValoareCard DECIMAL(18,2)
386 ,ValoareBonuri DECIMAL(18,2)
387 ,NrPozBon INT
388 ,UserCreareID INT
389 ,DataCreare DATETIME
390 ,UserValidareID INT
391 ,DataValidare DATETIME
392 ,Retur BIT
393 ,BufferPozitieBonID INT
394 ,Serie NVARCHAR(100) NULL
395 ,IntrPozOrigId INT NULL --se foloseste pt legatura la stoc a pozitiilor care vin cu intrpozid din buffer
396 ,NrBonFiscal NVARCHAR(100)
397 ,NrCard VARCHAR(50)
398 ,Observatii NVARCHAR(MAX)
399 ,ObservatiiInterne NVARCHAR(MAX)
400 ,ChitantaPos NVARCHAR(100)
401 ,IdCmdStornata INT
402 ,IdCmdAvans INT
403 ,SOrderPozId INT
404 ,AgentId INT
405 ,ValoareAmanuntRedus DECIMAL(18,6)
406 ,ValoareAmanunt DECIMAL(18,6)
407 ,ValoareFaraTva decimal(18,6)
408 ,Valoaretva decimal(18,6)
409 ,ValoareRedusaFaraTva decimal(18,6)
410 ,ValoareRedusaTva decimal(18,6)
411 ,IdIesPoz INT
412 ,CodServer nvarchar(50)
413 ,Plati xml
414 ,Promotii xml
415 ,Promotii_txt NVARCHAR(MAX)
416 ,IsMeniu BIT
417 ,ParentId INT
418 ,CardId INT
419 ,DocId INT
420 ,TipDocId INT
421 ,PlajaIncrement INT
422 )
423
424 IF OBJECT_ID('tempdb..#IesCap') IS NOT NULL
425 DROP TABLE #IesCap
426
427 CREATE TABLE #IesCap
428 (
429 IesCapID INT IDENTITY(1,1)
430 ,IesCapInseratId INT
431 ,ClientId INT
432 ,FirmaId INT
433 ,DivizieId INT
434 ,DocId INT
435 ,TipDocId INT
436 ,DataIes DATE
437 ,UserCreare NVARCHAR(50)
438 ,DataCreare DATETIME
439 ,ValoareFaraTVA DECIMAL(18,5)
440 ,ValoareTVA DECIMAL(18,5)
441 ,ValoareReducereFaraTVA DECIMAL(18,5)
442 ,ValoareReducereTVA DECIMAL(18,5)
443 ,ValoareAmanunt decimal(18,5)
444 ,Retur BIT
445 ,ValoareIncasata DECIMAL(18,2)
446 ,BonValoric DECIMAL(18,2)
447 ,[Card] DECIMAL(18,2)
448 ,[RetailCapIesireID] INT
449 ,CodCalc VARCHAR(10)
450 ,IdentificatorStatieID INT
451 ,DataValidare DATETIME
452 ,UserValidare NVARCHAR(50)
453 ,NrPozitii INT
454 ,NrBonFiscal NVARCHAR(100)
455 ,NrCard VARCHAR(50)
456 ,Observatii NVARCHAR(MAX)
457 ,ObservatiiInterne NVARCHAR(MAX)
458 ,UserCreareId INT
459 ,UserValidareId INT
460 ,ChitantaPos NVARCHAR(100)
461 ,IdCmdStornata INT
462 ,IdCmdAvans INT
463 ,AgentId INT
464 ,IesCapFacturaId INT
465 ,CodServer nvarchar(50)
466 ,Plati xml
467 ,CardId INT
468 ,PlajaIncrement INT
469 )
470
471 IF OBJECT_ID('tempdb..#Tichete') IS NOT NULL
472 DROP TABLE #Tichete
473
474 CREATE TABLE #Tichete
475 (
476 Id INT IDENTITY(1,1)
477 , IncasareBonuriID INT
478 , FirmaId INT
479 , DivizieId INT
480 , CapIesireId INT
481 , TichetId INT
482 , CantitateBonuri INT
483 , ValoareBon DECIMAL(18,2)
484 , UserCreareID INT
485 , DataCreare DATETIME
486 , Prelucrat BIT
487 , Identificator NVARCHAR(200)
488 , CodServer NVARCHAR(200)
489 , ModPlataId INT
490 , IesCapInseratId INT
491 )
492
493 --toate bonuri neprelucrate, inclusiv retur
494 INSERT INTO #PozitiiBuffer
495 (
496 IesCapId
497 ,ProdusID
498 ,IntrPozId
499 ,CantIesire
500 ,PretVanzare
501 ,PretVanzareRedus
502 ,Tva
503 ,GestiuneId
504 ,PretAmanunt
505 ,PretAmanuntRedus
506 ,Discount
507 ,RetailPozIesireID
508 ,RetailPozIesireReturID
509 --info cap
510 ,Identificator
511 ,IdentificatorStatieID
512 ,RetailCapIesireId
513 ,DataDoc
514 ,ValoareCash
515 ,ValoareCard
516 ,ValoareBonuri
517 ,NrPozBon
518 ,UserCreareID
519 ,DataCreare
520 ,UserValidareID
521 ,DataValidare
522 ,Retur
523 ,BufferPozitieBonID
524 ,Serie
525 ,IntrPozOrigId
526 ,NrBonFiscal
527 ,NrCard
528 ,Observatii
529 ,ObservatiiInterne
530 ,ChitantaPos
531 ,IdCmdStornata
532 ,SOrderPozId
533 ,AgentId
534 ,IdCmdAvans
535 ,ValoareAmanuntRedus
536 ,ValoareAmanunt
537 ,ValoareRedusaFaraTva
538 ,ValoareRedusaTva
539 ,ValoareFaraTva
540 ,Valoaretva
541 ,IdIesPoz
542 ,CodServer
543 ,Plati
544 ,Promotii
545 ,Promotii_txt
546 ,IsMeniu
547 ,ParentId
548 ,CardId
549 ,DocId
550 ,TipDocId
551 ,PlajaIncrement
552 )
553 SELECT
554 NULL AS IesCapId
555 , b.ProdusID
556 , NULL AS IntrPozId
557 , b.Cantitate AS CantIesire
558 , ROUND((b.PretAmanunt / ( 1 + ( CAST(t.Tva AS DECIMAL(18,2)) / 100 ) ) ),@NrZecimalePretVanzare) AS PretVanzare --??
559 , ROUND(b.PretAmanuntRedus / ( 1 + CAST(t.Tva AS DECIMAL(18,2)) / 100 ), @NrZecimalePretVanzare) AS PretVanzareRedus --??
560 , t.Tva AS TVA
561 , NULL AS GestiuneID
562 , b.PretAmanunt AS PretAmanunt
563 , b.PretAmanuntRedus AS PretAmanuntRedus
564 , b.Discount AS Discount
565 , b.PozIesireID AS RetailPozIesireId
566 , b.PozIesireReturID AS RetailPozIesireReturID
567 --info cap
568 , b.Identificator AS Identificator
569 , b.IdentificatorStatieID
570 , b.CapIesireId AS RetailCapIesireID
571 , b.DataDoc AS DataDoc
572 , b.ValoareCash
573 , b.ValoareCard
574 , b.ValoareBonuri
575 , b.NrPozBon
576 , b.UserCreareID
577 , b.DataCreare
578 , b.UserValidareID
579 , b.DataValidare
580 , b.Retur
581 , b.BufferPozitieBonID
582 , NULLIF(b.Serie,'')
583 , NULLIF(b.IntrPozId,0) --IntrPozOrigId
584 , b.NrBonFiscal
585 , cf.NumarCard
586 , b.Observatii
587 , b.ObservatiiInterne
588 , b.ChitantaPos
589 , b.CapIesireIdCmdStorn
590 , b.SOrderPozId
591 , b.AgentId AS AgentId
592 , b.CapIesireIdCmdIncas
593 , b.ValoareAmanuntRedus
594 , ROUND(b.Cantitate*b.PretAmanunt,2) as ValoareAmanunt
595 --, ROUND(isnull(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt)) - ROUND(((isnull(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt)) * t.Tva) / (100.00 + t.Tva)), 6), 6) AS ValoareRedusaFaraTva
596 , ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt))*100.00/(100.00+t.tva),6) AS ValoareRedusaFaraTva
597 --, ROUND(((isnull(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt)) * t.Tva) / (100.00 + t.Tva)), 6) AS ValoareRedusaTva
598 , ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt)),6) - ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt))*100.00/(100.00+t.tva),6) AS ValoareRedusaTva
599 --, ROUND(b.Cantitate*b.PretAmanunt - ROUND((b.Cantitate*b.PretAmanunt * t.Tva) / (100.00 + t.Tva), 6), 6) AS ValoareFaraTva
600 , CASE WHEN b.PretAmanunt = ISNULL(b.PretAmanuntRedus,b.PretAmanunt)
601 THEN ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt))*100.00/(100.00+t.tva),6)
602 ELSE ROUND(b.Cantitate*b.PretAmanunt*100.00/(100.00+t.tva),6)
603 END AS ValoareFaraTva
604 --, ROUND(( b.Cantitate*b.PretAmanunt * t.Tva) / (100.00 + t.Tva), 6) AS ValoareTva
605 , CASE WHEN b.PretAmanunt = ISNULL(b.PretAmanuntRedus,b.PretAmanunt)
606 THEN ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt)),6) - ROUND(ISNULL(b.ValoareAmanuntRedus,b.Cantitate*isnull(b.PretAmanuntRedus, b.PretAmanunt))*100.00/(100.00+t.tva),6)
607 ELSE ROUND(b.Cantitate*b.PretAmanunt,6) - ROUND(b.Cantitate*b.PretAmanunt*100.00/(100.00+t.tva),6)
608 END AS ValoareTva
609 , b.IdIesPoz
610 , b.CodServer
611 , b.ModuriPlata
612 , b.Promotii
613 , CAST(b.Promotii AS NVARCHAR(MAX))
614 , b.IsMeniu
615 , b.ParentId
616 , b.CardId
617 , b.DocID
618 , CASE WHEN b.DocID = 21 THEN @ConfigTipDocIdBonCuStoc ELSE @ConfigTipDocBonConsum END
619 , CASE WHEN b.DocId = 13 THEN DENSE_RANK() OVER (PARTITION BY b.DocId ORDER BY b.CapIesireId) END
620 FROM Retail.BufferPozitiiBon b ( NOLOCK )
621 INNER JOIN dbo.tblTva t ( NOLOCK )
622 ON b.TvaId = t.TvaId
623 LEFT JOIN dbo.tblCardFidelitate cf ( NOLOCK )
624 ON cf.CardId = b.CardId
625 WHERE b.FirmaId = @FirmaId
626 AND b.DivizieId = @DivizieId
627 AND ISNULL(b.Prelucrat,0) <> 1
628 AND ISNULL(b.DocId, @DocIdBon) IN (13,21)
629
630 IF @Debug=1 /*d*/
631 BEGIN
632 SELECT '#PozitiiBuffer:'
633 SELECT * FROM #PozitiiBuffer
634 END
635
636 INSERT INTO #IesCap
637 (
638 ClientId
639 ,FirmaId
640 ,DivizieId
641 ,DocId
642 ,TipDocId
643 ,DataIes
644 ,UserCreare
645 ,DataCreare
646 ,ValoareFaraTVA
647 ,ValoareTVA
648 ,ValoareReducereFaraTVA
649 ,ValoareReducereTVA
650 ,ValoareAmanunt
651 ,Retur
652 ,ValoareIncasata
653 ,BonValoric
654 ,[Card]
655 ,[RetailCapIesireID]
656 ,CodCalc
657 ,IdentificatorStatieID
658 ,DataValidare
659 ,UserValidare
660 ,NrPozitii
661 ,NrBonFiscal
662 ,NrCard
663 ,Observatii
664 ,ObservatiiInterne
665 ,UserCreareId
666 ,UserValidareId
667 ,ChitantaPos
668 ,IdCmdStornata
669 ,AgentId
670 ,IdCmdAvans
671 ,IesCapFacturaId
672 ,CodServer
673 ,Plati
674 ,CardId
675 ,PlajaIncrement
676 )
677 SELECT
678 @ConfigClientImplicitID
679 , @FirmaID
680 , @DivizieID
681 , MAX(poz.DocId) -- @DocIdBon
682 , MAX(poz.TipDocId) -- @ConfigTipDocIdBonCuStoc
683 --, DATEDIFF(dd, 0, poz.DataCreare)--fara ore/minute, altfel nu apare in fisa magazie
684 , poz.DataValidare
685 , ISNULL(MAX(u.Name),'POS') AS UserCreare
686 , poz.DataValidare AS DataCreare
687 , SUM(ISNULL(poz.ValoareFaraTva, ROUND(poz.CantIesire * poz.PretVanzare,2)))
688 --SUM(ROUND(poz.CantIesire * poz.PretVanzare * CAST(Tva AS DECIMAL(18, 2)) / 100, 2))
689 , SUM(ISNULL(poz.VAloareTva, poz.CantIesire * ( poz.PretAmanunt - poz.PretVanzare )))
690 , SUM(ISNULL(poz.ValoareRedusaFaraTva, ROUND(poz.CantIesire * poz.PretVanzareRedus,2)))
691 --, SUM(ROUND(poz.CantIesire * poz.PretVanzareRedus * CAST(Tva AS DECIMAL(18, 2)) / 100.0, 2))
692 , SUM(isnull(poz.ValoareRedusaTva, poz.CantIesire * ( poz.PretAmanuntRedus - poz.PretVanzareRedus )))
693 , SUM(isnull(poz.ValoareAmanunt,ROUND(poz.CantIesire*poz.PretAmanunt,2)))
694 , poz.Retur
695 , MAX(ValoareCash) AS ValoareCash
696 , MAX(ValoareBonuri) AS ValoareBonuri
697 , MAX(ValoareCard) AS ValoareCard
698 , MAX(poz.[RetailCapIesireID])
699 , ISNULL(poz.Identificator,'')
700 , MAX(poz.IdentificatorStatieID) AS IdentificatorStatieID
701 , MAX(poz.DataValidare) AS DataValidare
702 , MAX(u2.Name) AS UserValidare
703 , MAX(poz.NrPozBon) AS NrPozitii
704 , MAX(NrBonFiscal) AS NrBonFiscal
705 , MAX(NrCard) as NrCard
706 , MAX(poz.Observatii) AS Observatii
707 , MAX(poz.ObservatiiInterne) AS ObservatiiInterne
708 , MAX(UserCreareId) AS UserCreareId
709 , MAX(UserValidareId) AS UserValidareId
710 , MAX(ChitantaPos) AS ChitantaPos
711 , MAX(IdCmdStornata) AS IdCmdStornata
712 , MAX(isnull(poz.AgentId,u2.UnitateId)) AS AgentId
713 , MAX(IdCmdAvans) AS IdCmdAvans
714 , MAX(ipz.IesCapId) AS IesCapFacturaId
715 , poz.CodServer
716 , CAST(poz.Plati AS NVARCHAR(MAX))
717 , MAX(poz.CardId)
718 , MAX(poz.PlajaIncrement)
719 FROM #PozitiiBuffer poz
720 LEFT JOIN dbo.tblUsers u ( NOLOCK )
721 ON poz.UserCreareId = u.UserId
722 LEFT JOIN dbo.tblUsers u2 ( NOLOCK )
723 ON poz.UserValidareId = u2.UserId
724 LEFT JOIN inv.IesPoz ipz (NOLOCK)
725 ON poz.IdIesPoz = ipz.IesPozId
726 WHERE poz.IesCapId IS NULL
727 AND (ISNULL(poz.IsMeniu,0) = 1 OR poz.ParentId IS NULL)
728 GROUP BY
729 poz.RetailCapIesireId
730 , ISNULL(poz.Identificator,'')
731 , poz.CodServer
732 , poz.DataValidare
733 , poz.Retur
734 , CAST(poz.Plati AS NVARCHAR(MAX))
735
736 IF @Debug=1 /*d*/
737 BEGIN
738 SELECT '#IesCap:'
739 SELECT * FROM #IesCap
740 END
741
742 INSERT INTO #Tichete
743 (
744 IncasareBonuriID
745 , FirmaId
746 , DivizieId
747 , CapIesireId
748 , TichetId
749 , CantitateBonuri
750 , ValoareBon
751 , UserCreareID
752 , DataCreare
753 , Prelucrat
754 , Identificator
755 , CodServer
756 , ModPlataId
757 )
758
759 SELECT
760 b.IncasareBonuriID
761 , b.FirmaID
762 , b.DivizieID
763 , b.CapIesireID
764 , b.TichetID
765 , b.CantitateBonuri
766 , b.ValoareBon
767 , b.UserCreareID
768 , b.DataCreare
769 , 0
770 , b.Identificator
771 , b.CodServer
772 , b.ModPlataId
773 FROM Retail.BufferTichete b ( NOLOCK )
774 INNER JOIN #IesCap cap
775 ON cap.RetailCapIesireID = b.CapIesireID
776 AND cap.CodServer = b.CodServer
777 AND cap.CodCalc = b.Identificator
778 AND cap.DataValidare = b.DataCreare
779 WHERE b.FirmaId = @FirmaId
780 AND b.DivizieId = @DivizieId
781 AND ISNULL(b.Prelucrat,0) <> 1
782
783 SELECT @GestiuniRetail = ISNULL(@GestiuniRetail + ',','') + CAST(g.GestiuneId AS NVARCHAR(MAX))
784 FROM dbo.SplitString(@ConfigGestiuni,',') codGest
785 INNER JOIN dbo.tblGestiuni g (NOLOCK)
786 ON g.CodGestiune = codGest.val
787 AND g.FirmaId = @FirmaId
788 AND g.DivizieId = @DivizieId
789 GROUP BY g.GestiuneId
790
791 IF @GestiuniRetail = ''
792 SET @GestiuniRetail = NULL
793
794 IF OBJECT_ID('tempdb..#GestiuniBonFiscal') IS NOT NULL
795 DROP TABLE #GestiuniBonFiscal
796 IF OBJECT_ID('tempdb..#GestiuniBonConsum') IS NOT NULL
797 DROP TABLE #GestiuniBonConsum
798
799 SELECT x.val
800 INTO #GestiuniBonFiscal
801 FROM dbo.SplitString(@GestiuniRetail,',') x
802
803 SELECT x.val
804 INTO #GestiuniBonConsum
805 FROM dbo.SplitString(@GestiuniIdsBonConsum,',') x
806
807 IF OBJECT_ID('tempdb..#Retete') IS NOT NULL
808 DROP TABLE #Retete
809 -- Toate retetele de pe divizia curenta
810 -- active
811 -- neanulate
812 -- care nu au bifa de IgnoraDescarcare
813 -- care au completat Cant sau Cant2
814
815 SELECT
816 rc.ProdusId
817 , rc.RetetaCapId
818 , rc.ValabilDeLa
819 , rc.ValabilPanaLa
820 , rc.Cant
821 , rc.Cant2
822 , COALESCE(g1.GestiuneId,g2.GestiuneId,@GestiuneDestinatieId) AS GestiuneDestinatieId
823 , COALESCE(g1.Amanunt,g2.Amanunt,@AmanuntGestiuneDestinatie) AS Amanunt
824 , x.val
825 INTO #Retete
826 FROM dbo.tblRetetaCap rc (NOLOCK)
827 INNER JOIN dbo.tblProduseDivizii pd (NOLOCK)
828 ON pd.ProdusId = rc.ProdusId
829 AND pd.DivizieId = @DivizieId
830 INNER JOIN dbo.tblRetetaPoz rp (NOLOCK)
831 ON rp.RetetaCapId = rc.RetetaCapId
832 LEFT JOIN dbo.tblRetetaCapFirme rcf (NOLOCK)
833 ON rcf.RetetaCapId = rc.RetetaCapId
834 LEFT JOIN dbo.tblRetetaCapGestiuniDest rgd (NOLOCK)
835 ON rgd.RetetaCapId = rc.RetetaCapId
836 AND rgd.FirmaId = @FirmaId
837 LEFT JOIN dbo.tblGestiuni g1 (NOLOCK)
838 ON g1.GestiuneId = rc.GestiuneDestinatieId
839 AND g1.DivizieId = @DivizieId
840 AND g1.FirmaId = @FirmaId
841 LEFT JOIN dbo.tblGestiuni g2 (NOLOCK)
842 ON g2.GestiuneId = rgd.GestiuneId
843 AND g2.DivizieId = @DivizieId
844 AND g2.FirmaId = @FirmaId
845 OUTER APPLY (SELECT * FROM dbo.SplitString(COALESCE(rc.GestiuneSursaIds,@GestiuniMPIds,NULLIF(@GestiuniRetail,'')),',')) x
846 WHERE rc.DivizieId = @DivizieId
847 AND ISNULL(rc.Inactiv,0) = 0
848 AND rc.DataAnulare IS NULL
849 AND ISNULL(rc.IgnoraDescarcare,0) = 0
850 AND (rc.Cant IS NOT NULL OR rc.Cant2 IS NOT NULL)
851 AND (rcf.RetetaCapFirmaId IS NULL OR rcf.FirmaId = @FirmaId)
852 GROUP BY
853 rc.ProdusId
854 , rc.RetetaCapId
855 , rc.ValabilDeLa
856 , rc.ValabilPanaLa
857 , rc.Cant
858 , rc.Cant2
859 , COALESCE(g1.GestiuneId,g2.GestiuneId,@GestiuneDestinatieId)
860 , COALESCE(g1.Amanunt,g2.Amanunt,@AmanuntGestiuneDestinatie)
861 , x.val
862
863 DECLARE @CmdFin TABLE (SOrderCapId INT, FirmaId INT, DivizieId INT, UserId INT)
864
865 BEGIN TRAN T1
866
867 DECLARE @NrCifrePlajaBC INT
868 DECLARE @CurentPlajaBC INT
869 DECLARE @PrefixPlajaBC NVARCHAR(MAX)
870
871 IF @ConfigTipDocBonConsum IS NOT NULL
872 BEGIN
873 SELECT @CurentPlajaBC = Curent
874 , @PrefixPlajaBC = ISNULL(Prefix,'')
875 FROM dbo.Plaja WITH (UPDLOCK, HOLDLOCK)
876 WHERE PlajaId = @PlajaBCId
877
878 SELECT @NrCifrePlajaBC = NrCifre
879 FROM dbo.PlajaDoc (NOLOCK)
880 WHERE PlajaId = @PlajaBCId
881 AND DocId = 13
882
883 UPDATE dbo.Plaja
884 SET Curent = Curent + ISNULL((SELECT MAX(PlajaIncrement) FROM #IesCap),0)
885 WHERE PlajaId = @PlajaBCId
886 END
887
888 IF OBJECT_ID('tempdb..#IesCapInserate') IS NOT NULL
889 DROP TABLE #IesCapInserate
890
891 CREATE TABLE #IesCapInserate (IesCapId INT, RetailCapIesireId INT, CodPos NVARCHAR(100), DataValidare DATETIME, Retur BIT, DocId INT, DataIes DATE)
892
893 INSERT INTO inv.IesCap
894 (
895 ClientId
896 ,FirmaId
897 ,DivizieId
898 ,DocId
899 ,TipDocId
900 ,NumarIes
901 ,DataIes
902 ,NumarFactIes
903 ,DataFactIes
904 ,UserCreare
905 ,DataCreare
906 ,ValoareFaraTVA
907 ,ValoareTVA
908 ,ValoareReducereFaraTVA
909 ,ValoareReducereTVA
910 ,ValoareAmanunt
911 ,Retur
912 ,ValoareIncasata
913 ,BonValoric
914 ,[Card]
915 ,[RetailCapIesireID]
916 ,CodCalc
917 ,CodPos
918 ,DataValidare
919 ,UserValidare
920 ,NrPozitii
921 ,NrBonFiscal
922 ,NumarCard
923 ,Observatii
924 ,ObservatiiInterne
925 ,UserCreareId
926 ,UserValidareId
927 ,DataFacturare
928 ,UserFacturare
929 ,UserFacturareId
930 ,NumarChitantaCard
931 ,AgentId
932 ,IdentificatorStatieId
933 ,ValutaDefaultId
934 ,IesCapFacturaId
935 ,CapDocGeneratorId
936 ,DocGeneratorId
937 ,CodServer
938 )
939 OUTPUT inserted.IesCapId, inserted.RetailCapIesireId, inserted.CodPos, inserted.DataValidare, inserted.Retur, inserted.DocId, inserted.DataIes
940 INTO #IesCapInserate(IesCapId, RetailCapIesireId, CodPos, DataValidare, Retur, DocId, DataIes)
941 SELECT
942 ClientId
943 , FirmaId
944 , DivizieId
945 , DocId
946 , TipDocId
947 , CASE WHEN DocId = 13 THEN @PrefixPlajaBC + RIGHT(REPLICATE('0',ISNULL(@NrCifrePlajaBC,0)) + CAST(@CurentPlajaBC + PlajaIncrement - 1 AS NVARCHAR(MAX)),ISNULL(@NrCifrePlajaBC,40)) END
948 , DataIes
949 , CASE WHEN DocId = 13 THEN @PrefixPlajaBC + RIGHT(REPLICATE('0',ISNULL(@NrCifrePlajaBC,0)) + CAST(@CurentPlajaBC + PlajaIncrement - 1 AS NVARCHAR(MAX)),ISNULL(@NrCifrePlajaBC,40)) END
950 , DataIes
951 , UserCreare
952 , @DataCrt--DataCreare
953 , ValoareFaraTVA
954 , ValoareTVA
955 , ValoareReducereFaraTVA
956 , ValoareReducereTVA
957 , ValoareAmanunt
958 , Retur
959 , ValoareIncasata
960 , BonValoric
961 , [Card]
962 , [RetailCapIesireID]
963 , CodCalc
964 , CodCalc
965 , DataValidare
966 , UserValidare
967 , NrPozitii
968 , NrBonFiscal
969 , NrCard
970 , Observatii
971 , ObservatiiInterne
972 , UserCreareId
973 , UserValidareId
974 , CASE WHEN DocId = 13 THEN @DataCrt END
975 , CASE WHEN DocId = 13 THEN UserValidare END
976 , CASE WHEN DocId = 13 THEN UserValidareId END
977 , ChitantaPos
978 , AgentId
979 , IdentificatorStatieId
980 , @ValutaDefaultId
981 , IesCapFacturaId
982 , IesCapFacturaId
983 , CASE WHEN IesCapFacturaId IS NOT NULL THEN 5 END
984 , CodServer
985 FROM #IesCap
986
987
988 update c
989
990 set c.numaries=cast(c.iescapid as varchar(100))
991 from inv.iescap c
992 inner join #IesCapInserate ci on ci.IesCapId=c.IesCapId
993 where c.numaries is null
994
995 UPDATE tempCap
996 SET IesCapInseratId = temp.IesCapId
997 FROM #IesCap tempCap
998 INNER JOIN #IesCapInserate temp
999 ON temp.RetailCapIesireId = tempcap.RetailCapIesireID
1000 AND temp.CodPos = tempcap.CodCalc
1001 AND temp.DataValidare = tempcap.DataValidare
1002 AND ISNULL(temp.Retur,0) = ISNULL(temp.Retur,0)
1003
1004 UPDATE tempPoz
1005 SET IesCapInseratId = temp.IesCapId
1006 , DocInseratId = temp.DocId
1007 , DataIesInserat = temp.DataIes
1008 FROM #PozitiiBuffer tempPoz
1009 INNER JOIN #IesCapInserate temp
1010 ON temp.RetailCapIesireId = tempPoz.RetailCapIesireID
1011 AND temp.CodPos = tempPoz.Identificator
1012 AND temp.DataValidare = tempPoz.DataValidare
1013 AND ISNULL(temp.Retur,0) = ISNULL(temp.Retur,0)
1014
1015 UPDATE tempTichete
1016 SET IesCapInseratId = temp.IesCapId
1017 FROM #Tichete tempTichete
1018 INNER JOIN #IesCapInserate temp
1019 ON temp.RetailCapIesireId = tempTichete.CapIesireId
1020 AND temp.CodPos = tempTichete.Identificator
1021 AND temp.DataValidare = tempTichete.DataCreare
1022
1023 INSERT dbo.IesCapModPlata
1024 (
1025 IesCapId
1026 , Valoare
1027 , ModPlataId
1028 )
1029 SELECT
1030 temp.IesCapInseratId-- ies.IesCapId
1031 , CASE WHEN ISNULL(p.value('(./val)[1]', 'NVARCHAR(50)'),'') <> ''
1032 THEN p.value('(./val)[1]', 'DECIMAL(18,5)')
1033 ELSE NULL
1034 END AS Valoare
1035 , CAST(NULLIF(LTRIM(RTRIM(p.value('(./id)[1]', 'nvarchar(40)'))), '') AS INT) AS ModPlataId
1036 FROM #IesCap temp
1037 --INNER JOIN inv.Iescap ies (NOLOCK)
1038 -- ON ies.RetailCapIesireId = temp.RetailCapIesireId
1039 -- AND ies.CodCalc = temp.CodCalc
1040 -- AND ies.DataValidare = temp.DataValidare
1041 -- AND ies.FirmaId = @FirmaId
1042 -- AND ies.DivizieId = @DivizieID
1043 OUTER APPLY temp.Plati.nodes('/plati/mp') t (p)
1044 WHERE CAST(NULLIF(LTRIM(RTRIM(p.value('(./id)[1]', 'nvarchar(40)'))), '') AS INT) IS NOT NULL
1045 AND ISNULL(CASE WHEN ISNULL(p.value('(./val)[1]', 'NVARCHAR(50)'),'') <> ''
1046 THEN p.value('(./val)[1]', 'DECIMAL(18,5)')
1047 ELSE NULL
1048 END,0) <> 0
1049
1050 INSERT dbo.IesCapTichete
1051 (
1052 FirmaId
1053 , DivizieId
1054 , IescapId
1055 , ModPlataId
1056 , TichetId
1057 , ValoareBon
1058 , Cantitate
1059 , UserCreareId
1060 , DataCreare
1061 --, RetailIncasareBonuriId
1062 , RetailCapIesireId
1063 )
1064 --OUTPUT inserted.IesCapTichetID INTO @ict(IesCapTichetID)
1065 SELECT
1066 temp.FirmaId
1067 , temp.DivizieId
1068 , temp.IesCapInseratId --ies.IesCapId
1069 , temp.ModPlataId
1070 , temp.TichetId
1071 , temp.ValoareBon
1072 , SUM(temp.CantitateBonuri)
1073 , temp.UserCreareID
1074 , temp.DataCreare
1075 --, temp.IncasareBonuriID
1076 , temp.CapIesireId
1077 FROM #Tichete temp
1078 --INNER JOIN inv.IesCap ies (NOLOCK)
1079 -- ON ies.FirmaId = temp.FirmaId
1080 -- AND ies.DivizieId = temp.DivizieId
1081 -- AND ies.RetailCapIesireId = temp.CapIesireId
1082 -- AND ies.DataValidare = temp.DataCreare
1083 -- AND ies.CodCalc = temp.Identificator
1084 GROUP BY
1085 temp.FirmaId
1086 , temp.DivizieId
1087 , temp.IesCapInseratId --ies.IesCapId
1088 , temp.ModPlataId
1089 , temp.TichetId
1090 , temp.ValoareBon
1091 , temp.UserCreareId
1092 , temp.DataCreare
1093 , temp.CapIesireId
1094
1095 UPDATE b
1096 SET Prelucrat = 1
1097 FROM retail.BufferTichete b
1098 INNER JOIN #Tichete temp
1099 ON temp.IncasareBonuriID = b.IncasareBonuriID
1100 and temp.CapIesireId = b.CapIesireID
1101 WHERE b.FirmaId = @FirmaId
1102 AND b.DivizieID = @DivizieID
1103
1104 DECLARE @tcxs TABLE (TranContID INT)
1105
1106/* SELECT
1107 @DataCrt
1108 , p.value('cardid[1]', 'INT')
1109 , p.value('cid[1]', 'INT')
1110 , p.value('val[1]', 'DECIMAL(18, 5)')
1111 , temp.DataValidare
1112 , temp.UserValidareID
1113 , 1
1114 , @FirmaId
1115 , @DivizieID
1116 , temp.RetailCapIesireID
1117 , temp.RetailPozIesireID
1118 , temp.DataValidare
1119 , temp.Identificator
1120 , temp.IdentificatorStatieID
1121 , temp.CodServer
1122 --FROM #PuncteContoare temp
1123 FROM #PozitiiBuffer temp
1124 OUTER APPLY temp.Promotii.nodes('/promotii/promo') t (p)
1125 WHERE p.value('pozid[1]', 'INT') IS NOT NULL
1126 AND p.value('cid[1]', 'INT') <> 0
1127 AND p.value('val[1]', 'DECIMAL(18, 5)') <> 0
1128
1129 SELECT
1130 @DataCrt
1131 , temp.CardId
1132 , p.value('cid[1]', 'INT')
1133 , (-1) * p.value('val[1]', 'DECIMAL(18, 5)')
1134 , temp.DataValidare
1135 , temp.UserValidareID
1136 , 1
1137 , @FirmaId
1138 , @DivizieID
1139 , temp.RetailCapIesireID
1140 , temp.RetailPozIesireID
1141 , temp.DataValidare
1142 , temp.Identificator
1143 , temp.IdentificatorStatieID
1144 , temp.CodServer
1145 --FROM #PuncteContoare temp
1146 FROM #PozitiiBuffer temp
1147 OUTER APPLY temp.Promotii.nodes('/plati/mp') t (p)
1148 LEFT JOIN dbo.tblTranzactiiContoare tc (NOLOCK)
1149 ON tc.FirmaID = @FirmaId
1150 AND tc.DivizieID = @DivizieID
1151 AND tc.CapIesireID = temp.RetailCapIesireID
1152 AND tc.DataValidare = temp.DataValidare
1153 AND tc.Identificator = temp.Identificator
1154 AND tc.IdentificatorStatieID = temp.IdentificatorStatieID
1155 AND tc.CodServer = temp.CodServer
1156 WHERE p.value('cid[1]', 'INT') <> 0
1157 AND p.value('val[1]', 'DECIMAL(18, 5)') <> 0
1158 AND tc.TranContID IS NULL
1159*/
1160 --acumulare puncte
1161 INSERT dbo.tblTranzactiiContoare
1162 (
1163 [Data]
1164 , [CardId]
1165 , [ContorId]
1166 , [Valoare]
1167 , [DataCreare]
1168 , [UserCreareId]
1169 , [Prelucrat]
1170 , [FirmaID]
1171 , [DivizieID]
1172 , [CapIesireID]
1173 , [PozIesireID]
1174 , [DataValidare]
1175 , [Identificator]
1176 , [IdentificatorStatieID]
1177 , [CodServer]
1178 )
1179 OUTPUT inserted.TranContID INTO @tcxs(TranContID)
1180 SELECT
1181 @DataCrt
1182 , p.value('cardid[1]', 'INT')
1183 , p.value('cid[1]', 'INT')
1184 , p.value('val[1]', 'DECIMAL(18, 5)')
1185 , temp.DataValidare
1186 , temp.UserValidareID
1187 , 1
1188 , @FirmaId
1189 , @DivizieID
1190 , temp.RetailCapIesireID
1191 , temp.RetailPozIesireID
1192 , temp.DataValidare
1193 , temp.Identificator
1194 , temp.IdentificatorStatieID
1195 , temp.CodServer
1196 --FROM #PuncteContoare temp
1197 FROM #PozitiiBuffer temp
1198 OUTER APPLY temp.Promotii.nodes('/promotii/promo') t (p)
1199 WHERE p.value('pozid[1]', 'INT') IS NOT NULL
1200 AND p.value('cid[1]', 'INT') <> 0
1201 AND p.value('val[1]', 'DECIMAL(18, 5)') <> 0
1202 --consum puncte
1203 INSERT dbo.tblTranzactiiContoare
1204 (
1205 [Data]
1206 , [CardId]
1207 , [ContorId]
1208 , [Valoare]
1209 , [DataCreare]
1210 , [UserCreareId]
1211 , [Prelucrat]
1212 , [FirmaID]
1213 , [DivizieID]
1214 , [CapIesireID]
1215 --, [PozIesireID]
1216 , [DataValidare]
1217 , [Identificator]
1218 , [IdentificatorStatieID]
1219 , [CodServer]
1220 )
1221 OUTPUT inserted.TranContID INTO @tcxs(TranContID)
1222 SELECT
1223 @DataCrt
1224 , temp.CardId
1225 , p.value('cid[1]', 'INT')
1226 , (-1) * p.value('val[1]', 'DECIMAL(18, 5)')
1227 , temp.DataValidare
1228 , temp.UserValidareID
1229 , 1
1230 , @FirmaId
1231 , @DivizieID
1232 , temp.RetailCapIesireID
1233 --, temp.RetailPozIesireID
1234 , temp.DataValidare
1235 , temp.CodCalc
1236 , temp.IdentificatorStatieID
1237 , temp.CodServer
1238 --FROM #PuncteContoare temp
1239 FROM #IesCap temp -- #PozitiiBuffer temp
1240 OUTER APPLY temp.Plati.nodes('/plati/mp') t (p)
1241 LEFT JOIN dbo.tblTranzactiiContoare tc (NOLOCK)
1242 ON tc.FirmaID = @FirmaId
1243 AND tc.DivizieID = @DivizieID
1244 AND tc.CapIesireID = temp.RetailCapIesireID
1245 AND tc.DataValidare = temp.DataValidare
1246 AND tc.Identificator = temp.CodCalc
1247 AND tc.IdentificatorStatieID = temp.IdentificatorStatieID
1248 AND tc.CodServer = temp.CodServer
1249 WHERE p.value('cid[1]', 'INT') <> 0
1250 AND p.value('val[1]', 'DECIMAL(18, 5)') <> 0
1251 AND tc.TranContID IS NULL
1252
1253 UPDATE vc
1254 SET vc.Valoare = ISNULL(vc.Valoare, 0) + ISNULL(trx.Valoare, 0)
1255 FROM dbo.tblValoriContoare vc
1256 INNER JOIN (
1257 SELECT
1258 tc.ContorID
1259 , tc.CardId
1260 , SUM(tc.Valoare) AS Valoare
1261 FROM dbo.tblTranzactiiContoare tc
1262 INNER JOIN @tcxs x
1263 ON x.TranContID = tc.TranContID
1264 GROUP BY
1265 tc.ContorID
1266 , tc.CardId ) trx
1267 ON trx.ContorID = vc.ContorID
1268 AND trx.CardId = vc.CardID
1269
1270 IF OBJECT_ID('tempdb..#IesPozInserate') IS NOT NULL
1271 DROP TABLE #IesPozInserate
1272
1273 CREATE TABLE #IesPozInserate (CapDocId INT, DataDoc DATE, DocId INT, IesPozId INT, IesPoz2Id INT, FirmaId INT, DivizieId INT, Promotii XML )
1274
1275 IF EXISTS (SELECT TOP 1 * FROM #PozitiiBuffer WHERE ISNULL(IsMeniu,0) = 1)
1276 BEGIN
1277
1278 MERGE INTO inv.IesPoz2 T
1279 USING (
1280 SELECT DISTINCT
1281 pozRetail.IesCapInseratId AS IesCapId --cap.IesCapId
1282 , pozRetail.DataIesInserat AS DataIes --cap.DataIes
1283 , pozRetail.DocInseratId AS DocId --cap.DocId
1284 , pozRetail.ProdusID
1285 , pozRetail.CantIesire
1286 , pozRetail.PretVanzare
1287 , pozRetail.PretVanzareRedus
1288 , pozRetail.Discount
1289 , pozRetail.Tva
1290 , pozRetail.PretAmanunt
1291 , pozRetail.PretAmanuntRedus
1292 , pozRetail.RetailPozIesireID
1293 , pozRetail.RetailPozIesireReturID
1294 , NULL AS SorderPoz2Id
1295 , pozRetail.ValoareRedusaFaraTva
1296 , pozRetail.ValoareRedusaTva
1297 , pozRetail.ValoareFaraTva
1298 , pozRetail.ValoareTva
1299 , pozRetail.Promotii_txt AS Promotii
1300 FROM #PozitiiBuffer pozRetail
1301 INNER JOIN dbo.tblProduse pr (NOLOCK)
1302 ON pr.ProdusId = pozRetail.ProdusId
1303 --INNER JOIN #IesCap capRetail
1304 -- ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1305 -- AND pozRetail.Identificator = capRetail.CodCalc
1306 --INNER JOIN inv.IesCap cap ( NOLOCK )
1307 -- ON capRetail.CodCalc = cap.CodCalc
1308 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1309 -- AND cap.DocId IN (13,21)
1310 -- AND cap.DataValidare = capRetail.DataValidare
1311 WHERE /*cap.FirmaId = @FirmaID
1312 AND cap.DivizieId = @DivizieID
1313 AND */ISNULL(pozRetail.IsMeniu,0) = 1) AS S
1314 ON 1 = 2
1315 WHEN NOT MATCHED BY TARGET THEN
1316 INSERT
1317 (
1318 IesCapId
1319 , ProdusID
1320 , Cant
1321 , Pret
1322 , PretRedus
1323 , Discount
1324 , Tva
1325 , PretAmanunt
1326 , PretAmanuntRedus
1327 , RetailPozIesireID
1328 , RetailPozIesireReturID
1329 , SorderPoz2Id
1330 , ValoareRedusaFaraTva
1331 , ValoareRedusaTva
1332 , ValoareFaraTva
1333 , ValoareTva
1334 )
1335 VALUES
1336 (
1337 s.IesCapId
1338 , s.ProdusID
1339 , s.CantIesire
1340 , s.PretVanzare
1341 , s.PretVanzareRedus
1342 , s.Discount
1343 , s.Tva
1344 , s.PretAmanunt
1345 , s.PretAmanuntRedus
1346 , s.RetailPozIesireID
1347 , s.RetailPozIesireReturID
1348 , NULL
1349 , s.ValoareRedusaFaraTva
1350 , s.ValoareRedusaTva
1351 , s.ValoareFaraTva
1352 , s.ValoareTva
1353 )
1354 OUTPUT s.IesCapId, s.DataIes, s.DocId, inserted.IesPoz2Id, @FirmaId, @DivizieId, CAST(s.Promotii AS XML)
1355 INTO #IesPozInserate (CapDocId, DataDoc, DocId, IesPoz2Id, FirmaId, DivizieId, Promotii);
1356
1357 /*
1358 INSERT INTO inv.IesPoz2
1359 (
1360 IesCapId
1361 ,ProdusID
1362 ,Cant
1363 ,Pret
1364 ,PretRedus
1365 ,Tva
1366 ,PretAmanunt
1367 ,PretAmanuntRedus
1368 ,RetailPozIesireID
1369 ,RetailPozIesireReturID
1370 ,SorderPoz2Id
1371 ,ValoareRedusaFaraTva
1372 ,ValoareRedusaTva
1373 ,ValoareFaraTva
1374 ,ValoareTva
1375 )
1376 SELECT DISTINCT
1377 cap.IesCapId
1378 , pozRetail.ProdusID
1379 , pozRetail.CantIesire
1380 , pozRetail.PretVanzare
1381 , pozRetail.PretVanzareRedus
1382 , pozRetail.Tva
1383 , pozRetail.PretAmanunt
1384 , pozRetail.PretAmanuntRedus
1385 , pozRetail.RetailPozIesireID
1386 , pozRetail.RetailPozIesireReturID
1387 , NULL AS SorderPoz2Id
1388 , pozRetail.ValoareRedusaFaraTva
1389 , pozRetail.ValoareRedusaTva
1390 , pozRetail.ValoareFaraTva
1391 , pozRetail.ValoareTva
1392 FROM #PozitiiBuffer pozRetail
1393 INNER JOIN dbo.tblProduse pr (NOLOCK)
1394 ON pr.ProdusId = pozRetail.ProdusId
1395 INNER JOIN #IesCap capRetail
1396 ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1397 AND pozRetail.Identificator = capRetail.CodCalc
1398 INNER JOIN inv.IesCap cap ( NOLOCK )
1399 ON capRetail.CodCalc = cap.CodCalc
1400 AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1401 AND cap.DocId IN (13,21)
1402 AND cap.DataValidare = capRetail.DataValidare
1403 WHERE cap.FirmaId = @FirmaID
1404 AND cap.DivizieId = @DivizieID
1405 AND ISNULL(pozRetail.IsMeniu,0) = 1
1406 */
1407
1408 MERGE INTO inv.IesPoz T
1409 USING (
1410 SELECT DISTINCT
1411 pozRetail.IesCapInseratId AS IesCapId --cap.IesCapId
1412 , pozRetail.DataIesInserat AS DataIes --cap.DataIes
1413 , pozRetail.DocInseratId AS DocId --cap.DocId
1414 , meniu.IesPoz2Id
1415 , pozRetail.ProdusID
1416 , pozRetail.IntrPozId
1417 , pozRetail.IntrPozOrigId
1418 , pozRetail.CantIesire
1419 , ROUND(ISNULL(pozRetail.CantIesire * pr.GreutateNeta, CASE WHEN UPPER(um.Um) = 'KG' THEN pozRetail.CantIesire END),3) AS Cantitate2
1420 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzare END AS PretVanzare
1421 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzareRedus END AS PretVanzareRedus
1422 , pozRetail.Tva
1423 , pozRetail.GestiuneID
1424 , pozRetail.PretAmanunt
1425 , pozRetail.PretAmanuntRedus
1426 , pozRetail.Discount
1427 , pozRetail.RetailPozIesireID
1428 , pozRetail.RetailPozIesireReturID
1429 , pozRetail.Serie
1430 , CASE WHEN pozRetail.DocId = 13 THEN @ConfigTaxCodeBC
1431 ELSE CASE WHEN ISNULL(pozRetail.IdIesPoz, 0) = 0 THEN @ConfigTaxCode ELSE @ConfigTaxCodeBFF END
1432 END AS TaxCode
1433 , pozRetail.SOrderPozId
1434 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaFaraTva END AS ValoareRedusaFaraTva
1435 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaTva END AS ValoareRedusaTva
1436 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareFaraTva END AS ValoareFaraTva
1437 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareTva END AS ValoareTva
1438 , pozRetail.Promotii_txt AS Promotii
1439 FROM #PozitiiBuffer pozRetail
1440 INNER JOIN dbo.tblProduse pr (NOLOCK)
1441 ON pr.ProdusId = pozRetail.ProdusId
1442 INNER JOIN dbo.tblUm UM (NOLOCK)
1443 ON um.UmId = pr.UmId
1444 --INNER JOIN #IesCap capRetail
1445 -- ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1446 -- AND pozRetail.Identificator = capRetail.CodCalc
1447 --INNER JOIN inv.IesCap cap ( NOLOCK )
1448 -- ON capRetail.CodCalc = cap.CodCalc
1449 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1450 -- AND cap.DocId in (13,21)
1451 -- AND cap.DataValidare = capRetail.DataValidare
1452 INNER JOIN inv.IesPoz2 meniu (NOLOCK)
1453 ON meniu.IesCapId = pozRetail.IesCapInseratId --cap.IesCapId
1454 AND meniu.RetailPozIesireID = pozRetail.ParentId
1455 WHERE /*cap.FirmaId = @FirmaID
1456 AND cap.DivizieId = @DivizieID
1457 AND*/ pozRetail.ParentId IS NOT NULL) AS S
1458 ON 1 = 2
1459 WHEN NOT MATCHED BY TARGET THEN
1460 INSERT
1461 (
1462 IesCapId
1463 , IesPoz2Id
1464 , FirmaID
1465 , DivizieId
1466 , ProdusID
1467 , IntrPozId
1468 , IntrPozOrigId
1469 , CantIesire
1470 , Cantitate2
1471 , CantFacturata
1472 , PretVanzare
1473 , PretVanzareRedus
1474 , Tva
1475 , GestiuneID
1476 , PretAmanunt
1477 , PretAmanuntRedus
1478 , Discount
1479 , RetailPozIesireID
1480 , RetailPozIesireReturID
1481 , SerieIntrare
1482 , TaxCode
1483 , SOrderPozId
1484 , ValoareRedusaFaraTva
1485 , ValoareRedusaTva
1486 , ValoareFaraTva
1487 , ValoareTva
1488 )
1489 VALUES
1490 (
1491 s.IesCapId
1492 , s.IesPoz2Id
1493 , @FirmaId
1494 , @DivizieId
1495 , s.ProdusID
1496 , s.IntrPozId
1497 , s.IntrPozOrigId
1498 , s.CantIesire
1499 , s.Cantitate2
1500 , s.CantIesire
1501 , s.PretVanzare
1502 , s.PretVanzareRedus
1503 , s.Tva
1504 , s.GestiuneID
1505 , s.PretAmanunt
1506 , s.PretAmanuntRedus
1507 , s.Discount
1508 , s.RetailPozIesireID
1509 , s.RetailPozIesireReturID
1510 , s.Serie
1511 , s.TaxCode
1512 , s.SOrderPozId
1513 , s.ValoareRedusaFaraTva
1514 , s.ValoareRedusaTva
1515 , s.ValoareFaraTva
1516 , s.ValoareTva
1517 )
1518 OUTPUT s.IesCapId, s.DataIes, s.DocId, inserted.IesPozId, @FirmaId, @DivizieId, CAST(s.Promotii AS XML)
1519 INTO #IesPozInserate (CapDocId, DataDoc, DocId, IesPozId, FirmaId, DivizieId, Promotii);
1520
1521 /*
1522 INSERT INTO inv.IesPoz
1523 (
1524 IesCapId
1525 ,IesPoz2Id
1526 ,FirmaID
1527 ,DivizieId
1528 ,ProdusID
1529 ,IntrPozId
1530 ,IntrPozOrigId
1531 ,CantIesire
1532 ,CantFacturata
1533 --,Cantitate2
1534 ,PretVanzare
1535 ,PretVanzareRedus
1536 ,Tva
1537 ,GestiuneID
1538 ,PretAmanunt
1539 ,PretAmanuntRedus
1540 ,RetailPozIesireID
1541 ,RetailPozIesireReturID
1542 ,SerieIntrare
1543 ,TaxCode
1544 --,Amanunt
1545 ,SOrderPozId
1546 ,ValoareRedusaFaraTva
1547 ,ValoareRedusaTva
1548 ,ValoareFaraTva
1549 ,ValoareTva
1550 )
1551 SELECT DISTINCT
1552 cap.IesCapId
1553 , meniu.IesPoz2Id
1554 , @FirmaID
1555 , @DivizieID
1556 , pozRetail.ProdusID
1557 , pozRetail.IntrPozId
1558 , pozRetail.IntrPozOrigId
1559 , pozRetail.CantIesire
1560 , pozRetail.CantIesire
1561 --, CASE WHEN (retcap.GestiuneDestinatieId IS NOT NULL OR g.GestiuneId IS NOT NULL OR ISNULL(retcap.IgnoraDescarcare,0) = 0) AND retcap.Cant IS NULL
1562 -- THEN pozRetail.CantIesire * pr.GreutateNeta
1563 -- END
1564 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzare END
1565 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzareRedus END
1566 , pozRetail.Tva
1567 , pozRetail.GestiuneID
1568 , pozRetail.PretAmanunt
1569 , pozRetail.PretAmanuntRedus
1570 , pozRetail.RetailPozIesireID
1571 , pozRetail.RetailPozIesireReturID
1572 , pozRetail.Serie
1573 , CASE WHEN pozRetail.DocId = 13 THEN @ConfigTaxCodeBC
1574 ELSE CASE WHEN ISNULL(pozRetail.IdIesPoz, 0) = 0 THEN @ConfigTaxCode ELSE @ConfigTaxCodeBFF END
1575 END
1576 --, 1
1577 , pozRetail.SOrderPozId
1578 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaFaraTva END
1579 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaTva END
1580 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareFaraTva END
1581 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareTva END
1582 FROM #PozitiiBuffer pozRetail
1583 INNER JOIN dbo.tblProduse pr (NOLOCK)
1584 ON pr.ProdusId = pozRetail.ProdusId
1585 INNER JOIN #IesCap capRetail
1586 ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1587 AND pozRetail.Identificator = capRetail.CodCalc
1588 INNER JOIN inv.IesCap cap ( NOLOCK )
1589 ON capRetail.CodCalc = cap.CodCalc
1590 AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1591 AND cap.DocId in (13,21)
1592 AND cap.DataValidare = capRetail.DataValidare
1593 INNER JOIN inv.IesPoz2 meniu (NOLOCK)
1594 ON meniu.IesCapId = cap.IesCapId
1595 AND meniu.RetailPozIesireID = pozRetail.ParentId
1596 --LEFT JOIN dbo.tblRetetacap retcap (NOLOCK)
1597 -- ON retcap.ProdusId = pozRetail.ProdusId
1598 -- AND retcap.DivizieId = @divizieId
1599 -- AND ISNULL(retcap.Inactiv,0) = 0
1600 -- AND retcap.DataAnulare IS NULL
1601 -- AND cap.dataies BETWEEN ISNULL(retcap.ValabilDeLa,'') and ISNULL(retcap.ValabilPanaLA,'22000101')
1602 --LEFT JOIN dbo.tblRetetaCapGestiuniDest rgd (NOLOCK)
1603 -- ON rgd.RetetaCapId = retcap.RetetaCapId
1604 -- AND rgd.FirmaId = @FirmaId
1605 --LEFT JOIN dbo.tblGestiuni g (NOLOCK)
1606 -- ON g.GestiuneId = rgd.GestiuneId
1607 -- AND g.FirmaId = @FirmaId
1608 -- AND g.DivizieId = @DivizieID
1609 WHERE cap.FirmaId = @FirmaID
1610 AND cap.DivizieId = @DivizieID
1611 AND pozRetail.ParentId IS NOT NULL
1612 */
1613
1614 END
1615
1616 MERGE INTO inv.IesPoz T
1617 USING (
1618 SELECT DISTINCT
1619 pozRetail.IesCapInseratId AS IesCapId --cap.IesCapId
1620 , pozRetail.DataIesInserat AS DataIes --cap.DataIes
1621 , pozRetail.DocInseratId AS DocId --cap.DocId
1622 , pozRetail.ProdusID
1623 , pozRetail.IntrPozId
1624 , pozRetail.IntrPozOrigId
1625 , pozRetail.CantIesire
1626 , ROUND(ISNULL(pozRetail.CantIesire * pr.GreutateNeta, CASE WHEN UPPER(um.Um) = 'KG' THEN pozRetail.CantIesire END),3) AS Cantitate2
1627 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzare END AS PretVanzare
1628 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzareRedus END AS PretVanzareRedus
1629 , pozRetail.Tva
1630 , pozRetail.GestiuneID
1631 , pozRetail.PretAmanunt
1632 , pozRetail.PretAmanuntRedus
1633 , pozRetail.Discount
1634 , pozRetail.RetailPozIesireID
1635 , pozRetail.RetailPozIesireReturID
1636 , pozRetail.Serie
1637 , CASE WHEN pozRetail.DocId = 13 THEN @ConfigTaxCodeBC
1638 ELSE CASE WHEN ISNULL(pozRetail.IdIesPoz, 0) = 0 THEN @ConfigTaxCode ELSE @ConfigTaxCodeBFF END
1639 END AS TaxCode
1640 , pozRetail.SOrderPozId
1641 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaFaraTva END AS ValoareRedusaFaraTva
1642 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaTva END AS ValoareRedusaTva
1643 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareFaraTva END AS ValoareFaraTva
1644 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareTva END AS ValoareTva
1645 , pozRetail.Promotii_txt AS Promotii
1646 FROM #PozitiiBuffer pozRetail
1647 INNER JOIN dbo.tblProduse pr (NOLOCK)
1648 ON pr.ProdusId = pozRetail.ProdusId
1649 INNER JOIN dbo.tblUm um (NOLOCK)
1650 ON um.UmId = pr.UmId
1651 --INNER JOIN #IesCap capRetail
1652 -- ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1653 -- AND pozRetail.Identificator = capRetail.CodCalc
1654 --INNER JOIN inv.IesCap cap ( NOLOCK )
1655 -- ON capRetail.CodCalc = cap.CodCalc
1656 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1657 -- AND cap.DocId IN (13,21)
1658 -- AND cap.DataValidare = capRetail.DataValidare
1659 WHERE /*cap.FirmaId = @FirmaID
1660 AND cap.DivizieId = @DivizieID
1661 AND*/ pozRetail.ParentId IS NULL
1662 AND ISNULL(pozRetail.IsMeniu,0) = 0) AS S
1663 ON 1 = 2
1664 WHEN NOT MATCHED BY TARGET THEN
1665 INSERT
1666 (
1667 IesCapId
1668 , FirmaID
1669 , DivizieId
1670 , ProdusID
1671 , IntrPozId
1672 , IntrPozOrigId
1673 , CantIesire
1674 , Cantitate2
1675 , CantFacturata
1676 , PretVanzare
1677 , PretVanzareRedus
1678 , Tva
1679 , GestiuneID
1680 , PretAmanunt
1681 , PretAmanuntRedus
1682 , Discount
1683 , RetailPozIesireID
1684 , RetailPozIesireReturID
1685 , SerieIntrare
1686 , TaxCode
1687 , SOrderPozId
1688 , ValoareRedusaFaraTva
1689 , ValoareRedusaTva
1690 , ValoareFaraTva
1691 , ValoareTva
1692 )
1693 VALUES
1694 (
1695 s.IesCapId
1696 , @FirmaID
1697 , @DivizieID
1698 , s.ProdusID
1699 , s.IntrPozId
1700 , s.IntrPozOrigId
1701 , s.CantIesire
1702 , s.Cantitate2
1703 , s.CantIesire
1704 , s.PretVanzare
1705 , s.PretVanzareRedus
1706 , s.Tva
1707 , s.GestiuneID
1708 , s.PretAmanunt
1709 , s.PretAmanuntRedus
1710 , s.Discount
1711 , s.RetailPozIesireID
1712 , s.RetailPozIesireReturID
1713 , s.Serie
1714 , s.TaxCode
1715 , s.SOrderPozId
1716 , s.ValoareRedusaFaraTva
1717 , s.ValoareRedusaTva
1718 , s.ValoareFaraTva
1719 , s.ValoareTva
1720 )
1721 OUTPUT s.IesCapId, s.DataIes, s.DocId, inserted.IesPozId, @FirmaId, @DivizieId, CAST(s.Promotii AS XML)
1722 INTO #IesPozInserate (CapDocId, DataDoc, DocId, IesPozId, FirmaId, DivizieId, Promotii);
1723
1724 /*
1725 INSERT INTO inv.IesPoz
1726 (
1727 IesCapId
1728 ,FirmaID
1729 ,DivizieId
1730 ,ProdusID
1731 ,IntrPozId
1732 ,IntrPozOrigId
1733 ,CantIesire
1734 ,CantFacturata
1735 --,Cantitate2
1736 ,PretVanzare
1737 ,PretVanzareRedus
1738 ,Tva
1739 ,GestiuneID
1740 ,PretAmanunt
1741 ,PretAmanuntRedus
1742 ,RetailPozIesireID
1743 ,RetailPozIesireReturID
1744 ,SerieIntrare
1745 ,TaxCode
1746 --,Amanunt
1747 ,SOrderPozId
1748 ,ValoareRedusaFaraTva
1749 ,ValoareRedusaTva
1750 ,ValoareFaraTva
1751 ,ValoareTva
1752 )
1753 SELECT DISTINCT
1754 cap.IesCapId
1755 , @FirmaID
1756 , @DivizieID
1757 , pozRetail.ProdusID
1758 , pozRetail.IntrPozId
1759 , pozRetail.IntrPozOrigId
1760 , pozRetail.CantIesire
1761 , pozRetail.CantIesire
1762 --, CASE WHEN ret.RetetaCapId IS NOT NULL AND ret.Cant IS NULL
1763 -- THEN pozRetail.CantIesire * pr.GreutateNeta
1764 -- END
1765 --, CASE WHEN (retcap.GestiuneDestinatieId IS NOT NULL OR g.GestiuneId IS NOT NULL OR ISNULL(retcap.IgnoraDescarcare,0) = 0) AND retcap.Cant IS NULL
1766 -- THEN pozRetail.CantIesire * pr.GreutateNeta
1767 -- END
1768 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzare END
1769 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.PretVanzareRedus END
1770 , pozRetail.Tva
1771 , pozRetail.GestiuneID
1772 , pozRetail.PretAmanunt
1773 , pozRetail.PretAmanuntRedus
1774 , pozRetail.RetailPozIesireID
1775 , pozRetail.RetailPozIesireReturID
1776 , pozRetail.Serie
1777 , CASE WHEN pozRetail.DocId = 13 THEN @ConfigTaxCodeBC
1778 ELSE CASE WHEN ISNULL(pozRetail.IdIesPoz, 0) = 0 THEN @ConfigTaxCode ELSE @ConfigTaxCodeBFF END
1779 END
1780 --, 1
1781 , pozRetail.SOrderPozId
1782 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaFaraTva END
1783 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareRedusaTva END
1784 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareFaraTva END
1785 , CASE WHEN pozRetail.DocId <> 13 THEN pozRetail.ValoareTva END
1786 FROM #PozitiiBuffer pozRetail
1787 INNER JOIN dbo.tblProduse pr (NOLOCK)
1788 ON pr.ProdusId = pozRetail.ProdusId
1789 INNER JOIN #IesCap capRetail
1790 ON pozRetail.RetailCapIesireID = capRetail.RetailCapIesireId
1791 AND pozRetail.Identificator = capRetail.CodCalc
1792 INNER JOIN inv.IesCap cap ( NOLOCK )
1793 ON capRetail.CodCalc = cap.CodCalc
1794 AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
1795 AND cap.DocId IN (13,21)
1796 AND cap.DataValidare = capRetail.DataValidare
1797 --LEFT JOIN #Retete ret
1798 -- ON ret.ProdusId = pozRetail.ProdusID
1799 -- AND cap.DataIes BETWEEN ISNULL(ret.ValabilDeLa,'') AND ISNULL(ret.ValabilPanaLa,'22000101')
1800 --LEFT JOIN dbo.tblRetetacap retcap (NOLOCK)
1801 -- ON retcap.ProdusId = pozRetail.ProdusId
1802 -- AND retcap.DivizieId = @divizieId
1803 -- AND ISNULL(retcap.Inactiv,0) = 0
1804 -- AND retcap.DataAnulare IS NULL
1805 -- AND cap.dataies BETWEEN ISNULL(retcap.ValabilDeLa,'') and ISNULL(retcap.ValabilPanaLA,'22000101')
1806 --LEFT JOIN dbo.tblRetetaCapGestiuniDest rgd (NOLOCK)
1807 -- ON rgd.RetetaCapId = retcap.RetetaCapId
1808 -- AND rgd.FirmaId = @FirmaId
1809 --LEFT JOIN dbo.tblGestiuni g (NOLOCK)
1810 -- ON g.GestiuneId = rgd.GestiuneId
1811 -- AND g.FirmaId = @FirmaId
1812 -- AND g.DivizieId = @DivizieID
1813 WHERE cap.FirmaId = @FirmaID
1814 AND cap.DivizieId = @DivizieID
1815 AND pozRetail.ParentId IS NULL
1816 AND ISNULL(pozRetail.IsMeniu,0) = 0
1817 */
1818
1819 --INSERT dbo.tblPozDocPromo
1820 --(
1821 -- CapDocId
1822 --, DataDoc
1823 --, DocId
1824 --, PozDocId
1825 --, PozDoc2Id
1826 --, PromotieId
1827 --, ContorId
1828 --, Valoare
1829 --, ProdusId
1830 --, Procent
1831 --, CardId
1832 --, ClientId
1833 --, Mesaj
1834 --)
1835 --SELECT
1836 -- temp.CapDocId
1837 --, temp.DataDoc
1838 --, temp.DocId
1839 --, temp.IesPozId
1840 --, temp.IesPoz2Id
1841 --, p.value('pid[1]', 'INT')
1842 --, CASE WHEN p.value('cid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('cid[1]', 'INT') END
1843 --, CASE WHEN p.value('val[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('val[1]', 'DECIMAL(18, 5)') END
1844 --, CASE WHEN p.value('prodid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('prodid[1]', 'INT') END
1845 --, CASE WHEN p.value('proc[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('proc[1]', 'DECIMAL(18, 5)') END
1846 --, CASE WHEN p.value('cardid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('cardid[1]', 'INT') END
1847 --, CASE WHEN p.value('clientid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('clientid[1]', 'INT') END
1848 --, NULL
1849 --FROM #IesPozInserate temp
1850 -- OUTER APPLY temp.Promotii.nodes('/promotii/promo') t (p)
1851 --WHERE p.value('pid[1]', 'INT') IS NOT NULL
1852 -- AND p.value('pozid[1]', 'INT') IS NOT NULL
1853
1854 SELECT
1855 temp.CapDocId
1856 , temp.DataDoc
1857 , temp.DocId
1858 , temp.IesPozId
1859 , temp.IesPoz2Id
1860 , CAST(p.value('pid[1]', 'INT') AS INT) PId
1861 , CAST(CASE WHEN p.value('cid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('cid[1]', 'INT') END AS INT) CId
1862 , CAST(CASE WHEN p.value('val[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('val[1]', 'DECIMAL(18, 5)') END AS DECIMAL(18,5)) Val
1863 , CAST(CASE WHEN p.value('prodid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('prodid[1]', 'INT') END AS INT) ProdId
1864 , CAST(CASE WHEN p.value('proc[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('proc[1]', 'DECIMAL(18, 5)') END AS DECIMAL(18,5)) [Proc]
1865 , CAST(CASE WHEN p.value('cardid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('cardid[1]', 'INT') END AS INT) CardId
1866 , CAST(CASE WHEN p.value('clientid[1]', 'NVARCHAR(MAX)') = '' THEN NULL ELSE p.value('clientid[1]', 'INT') END AS INT) ClientId
1867 , CAST(NULL AS NVARCHAR(MAX)) Mesaj
1868 INTO #TempPromotii
1869 FROM #IesPozInserate temp
1870 OUTER APPLY temp.Promotii.nodes('/promotii/promo') t (p)
1871 WHERE p.value('pid[1]', 'INT') IS NOT NULL
1872 AND p.value('pozid[1]', 'INT') IS NOT NULL
1873
1874 IF EXISTS(SELECT TOP 1 * FROM #TempPromotii)
1875 BEGIN
1876
1877 INSERT dbo.tblPozDocPromo
1878 (
1879 CapDocId
1880 , DataDoc
1881 , DocId
1882 , PozDocId
1883 , PozDoc2Id
1884 , PromotieId
1885 , ContorId
1886 , Valoare
1887 , ProdusId
1888 , Procent
1889 , CardId
1890 , ClientId
1891 , Mesaj
1892 )
1893 SELECT
1894 temp.CapDocId
1895 , temp.DataDoc
1896 , temp.DocId
1897 , temp.IesPozId
1898 , temp.IesPoz2Id
1899 , temp.PId
1900 , temp.CId
1901 , temp.Val
1902 , temp.ProdId
1903 , temp.[Proc]
1904 , temp.CardId
1905 , ISNULL(temp.ClientId,@ConfigClientImplicitID) AS ClientId
1906 , temp.Mesaj
1907 FROM #TempPromotii temp
1908
1909 UPDATE capp
1910 set capp.Descrierepromotie = STUFF((SELECT ' ●' + isnull(temp.mesaj, pp.Promotie) + '<br>'
1911 from #TempPromotii temp
1912 inner join dbo.tblpromotii pp (NOLOCK)
1913 on pp.PromotieId = temp.PId
1914 WHERE temp.IesPozId = capp.IesPozId
1915 and (temp.val > 0 or temp.[Proc] > 0 or temp.mesaj is not null)
1916 and isnull(pp.NoDisplay,0) = 0
1917 order by pp.Promotie
1918 FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,0,'')
1919 from inv.IesPoz capp
1920 INNER JOIN #IesPozInserate ip
1921 ON ip.IesPozId = capp.IesPozId
1922
1923 UPDATE capp
1924 set capp.[Descriere Promotii] = STUFF((SELECT ' ●' + isnull(temp.mesaj, pp.Promotie) + '<br>'
1925 from #TempPromotii temp
1926 inner join dbo.tblpromotii pp (NOLOCK)
1927 on pp.PromotieId = temp.PId
1928 WHERE temp.IesPoz2Id = capp.IesPoz2Id
1929 and (temp.val > 0 or temp.[Proc] > 0 or temp.mesaj is not null)
1930 and isnull(pp.NoDisplay,0) = 0
1931 order by pp.Promotie
1932 FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,0,'')
1933 from inv.IesPoz2 capp
1934 INNER JOIN #IesPozInserate ip
1935 ON ip.IesPoz2Id = capp.IesPoz2Id
1936 END
1937
1938 --SELECT *
1939 --INTO TempPozPromo
1940 --FROM #IesPozInserate
1941
1942 -- inserare detalii din comanda
1943 IF OBJECT_ID('tempdb..#ParentIesPozInserati') IS NOT NULL
1944 DROP TABLE #ParentIesPozInserati
1945
1946 CREATE TABLE #ParentIesPozInserati (IesPozId INT)
1947 INSERT INTO inv.IesPoz
1948 (
1949 IesCapId
1950 ,FirmaID
1951 ,DivizieId
1952 ,ProdusID
1953 ,IntrPozId
1954 ,IntrPozOrigId
1955 ,CantIesire
1956 ,CantFacturata
1957 ,PretVanzare
1958 ,PretVanzareRedus
1959 ,Tva
1960 ,GestiuneID
1961 ,PretAmanunt
1962 ,PretAmanuntRedus
1963 --,RetailPozIesireID
1964 ,RetailPozIesireReturID
1965 ,SerieIntrare
1966 ,TaxCode
1967 --,Amanunt
1968 ,SOrderPozId
1969 ,ValoareRedusaFaraTva
1970 ,ValoareRedusaTva
1971 ,ValoareFaraTva
1972 ,ValoareTva
1973 ,ParentIesPozId
1974 ,RetailPozIesireID
1975 )
1976 OUTPUT inserted.ParentIesPozId INTO #ParentIesPozInserati(IesPozId)
1977 select p.IesCapId
1978 ,p.FirmaId
1979 ,p.DivizieId
1980 ,det.ProdusId
1981 ,null
1982 ,null
1983 ,ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623)
1984 ,ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623)
1985 ,det.pretvaluta * isnull(det.cursvalutar,1)
1986 ,isnull(det.pretvalutaredus,det.pretvaluta) * isnull(det.cursvalutar,1)
1987 ,t.Tva
1988 ,null
1989 ,null
1990 ,null
1991 --,null
1992 ,null
1993 ,null
1994 ,p.TaxCode--CASE WHEN pdet.TipItem='P' then td.TaxCodeP ELSE td.TaxCodeS END
1995 ,det.SOrderPozId
1996 ,ROUND(ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623) * ISNULL(det.pretvalutaredus,det.pretvaluta) * isnull(det.cursvalutar,1),2)
1997 ,ROUND(ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623) * ISNULL(det.pretvalutaredus,det.pretvaluta) * isnull(det.cursvalutar,1) * t.tva / 100.00,2)
1998 ,ROUND(ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623) * det.pretvaluta * isnull(det.cursvalutar,1),2)
1999 ,ROUND(ROUND((det.Cant/sp.Cant) * p.cantiesire,@setare623) * det.pretvaluta * isnull(det.cursvalutar,1) * t.tva / 100.00,2)
2000 ,p.IesPozId
2001 ,p.RetailPozIesireID
2002 FROM #Iescap capRetail
2003
2004 --INNER JOIN inv.IesCap cap ( NOLOCK )
2005 -- ON capRetail.CodCalc = cap.CodCalc
2006 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
2007 -- AND cap.DocId IN (13,21)
2008 -- AND cap.DataValidare = capRetail.DataValidare
2009 --INNER JOIN inv.TipDoc (NOLOCK) td on td.TipDocId=cap.TipDocId
2010 INNER JOIN inv.iespoz p (NOLOCK) on p.iescapid=capRetail.IesCapInseratId --cap.iescapid
2011 INNER JOIN dbo.SorderPoz sp (NOLOCK) on sp.SorderPozId = p.SorderPozId
2012 INNER JOIN dbo.SorderPoz det (NOLOCK) ON det.SOrderPozParentId IS NULL AND det.ParentSorderPozId = sp.SorderPozId
2013 INNER JOIN dbo.tblProduse pdet (NOLOCK) ON pdet.ProdusId=det.ProdusID
2014 INNER JOIN dbo.tblTva t (NOLOCK) on t.TvaId=pdet.TvaId
2015
2016 UPDATE p
2017 SET p.Continedetalii = 1
2018 , p.gestiuneid = @GestiuneDestinatieId --ISNULL(@GestiuneDestinatieId,p.gestiuneid)
2019 , p.Amanunt = @AmanuntGestiuneDestinatie --g.Amanunt
2020 FROM #ParentIesPozInserati poz
2021 INNER JOIN inv.IesPoz p
2022 ON p.IesPozId = poz.IesPozId
2023 --FROM #Iescap capRetail
2024 --INNER JOIN inv.IesCap cap ( NOLOCK )
2025 -- ON capRetail.CodCalc = cap.CodCalc
2026 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
2027 -- AND cap.DocId IN (13,21)
2028 -- AND cap.DataValidare = capRetail.DataValidare
2029 --INNER JOIN inv.TipDoc (NOLOCK) td on td.TipDocId = cap.TipDocId
2030 --INNER JOIN inv.iespoz p (NOLOCK) on p.iescapid = cap.iescapid
2031 --INNER JOIN inv.Iespoz pa (NOLOCK) on pa.ParentIesPozID = p.IesPozId
2032 --LEFT JOIN dbo.tblGestiuni g (NOLOCK) ON g.GestiuneId = ISNULL(@GestiuneDestinatieId,p.gestiuneid)
2033 --WHERE ISNULL(p.ContineDetalii,0) = 0
2034
2035
2036
2037 DECLARE @CmdIncasAvSauStorn TABLE (IesCapId INT, IdCmdStornata INT, IdCmdAvans INT, Valoare DECIMAL(18, 5), DataValidare DATE)
2038
2039 INSERT INTO @CmdIncasAvSauStorn(IesCapId, IdCmdStornata, IdCmdAvans, Valoare, DataValidare)
2040 SELECT cap.IesCapId,
2041 MAX(capRetail.IdCmdStornata) AS IdCmdStornata,
2042 MAX(capRetail.IdCmdAvans) AS IdCmdAvans,
2043 MAX(ISNULL(cap.ValoareFaraTva, 0) + ISNULL(cap.ValoareTva, 0)) AS Valoare,
2044 CAST(MAX(cap.DataValidare) AS DATE) AS DataValidare
2045 FROM #IesCap capRetail
2046 INNER JOIN inv.Iescap cap (NOLOCK)
2047 ON cap.IesCapId = capRetail.IesCapInseratId
2048 --FROM inv.IesCap cap (NOLOCK)
2049 --INNER JOIN #IesCap capRetail
2050 -- ON capRetail.CodCalc = cap.CodCalc
2051 -- AND capRetail.RetailCapIesireID = cap.RetailCapIesireID
2052 -- AND cap.DocId IN (13,21)
2053 -- AND capRetail.DataValidare = cap.DataValidare
2054 -- AND capRetail.IesCapFacturaId IS NULL
2055 WHERE cap.FirmaId = @FirmaID
2056 AND cap.DivizieId = @DivizieID
2057 AND (capRetail.IdCmdStornata IS NOT NULL OR capRetail.IdCmdAvans IS NOT NULL)
2058 --AND cap.IesCapFacturaId IS NULL
2059 --AND cap.DocGeneratorId IS NULL
2060 --AND cap.CapDocGeneratorId IS NULL
2061 GROUP BY cap.IesCapId
2062
2063 UPDATE cmd
2064 SET cmd.IesCapId = Ies4Cmd.IesCapId,
2065 cmd.StareFinalizata = 1,
2066 cmd.StareId = ISNULL(@StareCmdNoua,cmd.StareId) /* ISNULL((SELECT TOP 1 StareId
2067 FROM dbo.tblStariDoc (NOLOCK)
2068 WHERE DocId = 26--comanda client
2069 AND ISNULL(Final, 0) = 1
2070 AND ISNULL(FinalizatPartial, 0) = 1
2071 AND ISNULL(DocValidat, 0) = 1
2072 AND FirmaId = @FirmaId
2073 AND DivizieId = @DivizieId
2074 ), cmd.StareId)*/
2075 OUTPUT inserted.SOrderCapId, inserted.FirmaId, inserted.DivizieId, inserted.UserValidareId INTO @CmdFin(SOrderCapId, FirmaId, DivizieId, UserId)
2076 FROM dbo.SOrderCap cmd (NOLOCK)
2077 INNER JOIN @CmdIncasAvSauStorn Ies4Cmd
2078 ON Ies4Cmd.IdCmdStornata = cmd.SOrderCapId
2079 WHERE cmd.FirmaId = @FirmaID
2080 AND cmd.DivizieId = @DivizieID
2081 AND cmd.IesCapId IS NULL
2082
2083 --update valoare avans
2084 UPDATE cmd
2085 SET cmd.ValoareAvans = Ies4Cmd.Valoare
2086 FROM dbo.SOrderCap cmd (NOLOCK)
2087 INNER JOIN @CmdIncasAvSauStorn Ies4Cmd
2088 ON Ies4Cmd.IdCmdAvans = cmd.RetailCapIesireId
2089 AND Ies4Cmd.DataValidare = cmd.SOrderDate
2090 WHERE cmd.FirmaId = @FirmaID
2091 AND cmd.DivizieId = @DivizieID
2092
2093 --WHILE (EXISTS(SELECT TOP 1 SOrderCapId FROM @CmdFin))
2094 --BEGIN
2095
2096 -- DECLARE @SorderCapComId INT,
2097 -- @SYS_UNITID INT,
2098 -- @SYS_DIVID INT,
2099 -- @SYS_USERID INT
2100
2101 -- SELECT TOP 1
2102 -- @SorderCapComId = SOrderCapId ,
2103 -- @SYS_UNITID = FirmaId,
2104 -- @SYS_DIVID = DivizieId,
2105 -- @SYS_USERID = UserId
2106 -- FROM @CmdFin
2107
2108 -- EXEC dbo.ComandaClient_Finalizare
2109 -- @SorderCapID = @SorderCapComId,
2110 -- @SYS_UNITID = @SYS_UNITID,
2111 -- @SYS_DIVID = @SYS_DIVID,
2112 -- @SYS_USERID = @SYS_USERID,
2113 -- @SYS_LANGID = 'RO'
2114
2115 -- DELETE FROM @CmdFin WHERE SOrderCapId = @SorderCapComId
2116
2117 --END
2118 --=================== comenzi pos ===========================
2119
2120 if @Debug=1 /*d*/
2121 BEGIN
2122 SELECT 'Set prelucrat in buffer'
2123 END
2124
2125 UPDATE Retail.BufferPozitiiBon
2126 SET Prelucrat = 1
2127 FROM Retail.BufferPozitiiBon buff ( NOLOCK )
2128 INNER JOIN #PozitiiBuffer pozTemp
2129 ON buff.BufferPozitieBonID = pozTemp.BufferPozitieBonID
2130 AND buff.Identificator = pozTemp.Identificator
2131 WHERE FirmaID = @FirmaId
2132 AND DivizieId = @DivizieID
2133
2134 IF XACT_STATE() = 1
2135 COMMIT TRANSACTION T1
2136
2137 IF @Descarcare = 1 AND @GestiuniRetail IS NOT NULL
2138 BEGIN
2139 --=================================================== INCEPUT SINCRONIZARE CU STOC ==================================================================================
2140
2141
2142 --la sincronizarea cu stocul se vor completa in IesPoz toate campurile din IntrPoz
2143 --------------------------------------------------------------------------------------------------------------
2144 --Legatura la stoc la pozitiile cu cant pozitiva
2145
2146 IF OBJECT_ID('tempdb..#TempSync') IS NOT NULL
2147 DROP TABLE #TempSync
2148
2149 CREATE TABLE #TempSync
2150 (
2151 TempSyncId INT IDENTITY(1,1)
2152 ,IesCapId INT
2153 ,IesPozId INT
2154 ,CantIesire DECIMAL(18,5)
2155 ,Cantitate2 DECIMAL(18,5)
2156 ,Prelucrat BIT
2157 ,IntrPozOrigId INT NULL
2158 ,SerieIntrare NVARCHAR(200) NULL
2159 ,PretAmanunt DECIMAL(18,5)
2160 ,SOrderPozId int
2161 ,ProdusId int
2162 ,RetetaPozId Int NULL
2163 ,ParentIesPozId int NULL
2164 ,DataIes DATE
2165 ,DocId INT
2166 )
2167
2168 INSERT INTO #TempSync
2169 (IesCapId
2170 ,IesPozId
2171 ,CantIesire
2172 ,Cantitate2
2173 ,Prelucrat
2174 ,IntrPozOrigId
2175 ,SerieIntrare
2176 ,PretAmanunt
2177 ,SOrderPozId
2178 ,ProdusID
2179 ,RetetaPozId
2180 ,ParentIesPozId
2181 ,DataIes
2182 ,DocId
2183 )
2184 SELECT
2185 cap.IesCapId
2186 , poz.IesPozId
2187 , poz.CantIesire
2188 , poz.Cantitate2
2189 , 0
2190 , poz.IntrPozOrigId
2191 , poz.SerieIntrare
2192 , poz.PretAmanunt
2193 , poz.SorderPozId
2194 , poz.ProdusID
2195 , poz.RetetaPozId
2196 , poz.ParentIesPozId
2197 , cap.DataIes
2198 , cap.DocId
2199 FROM inv.IesPoz poz ( NOLOCK )
2200 INNER JOIN inv.Iescap cap ( NOLOCK )
2201 ON cap.IesCapId = poz.IesCapId
2202 LEFT JOIN dbo.tblProduse p ( NOLOCK )
2203 ON p.ProdusId = poz.ProdusId
2204 WHERE cap.FirmaId = @FirmaID
2205 AND cap.DivizieId = @DivizieId
2206 AND cap.DataAnulare IS NULL
2207 AND poz.IntrPozId IS NULL-- nesincronizate cu stocul
2208 AND ((cap.DocId = 13 AND cap.TipDocId = @ConfigTipDocBonConsum AND @TipDocBCStoc = 1)
2209 OR (cap.Docid = 21 AND cap.TipDocId = @ConfigTipDocIdBonCuStoc AND @TipDocStoc = 1))
2210 --AND cap.TipDocId in (@ConfigTipDocIdBonCuStoc, @ConfigTipDocBonConsum)
2211 --AND cap.IesCapFacturaId IS NULL--fara bonurile din factura
2212 AND cap.RetailCapIesireID IS NOT NULL
2213 --AND poz.RetailPozIesireID IS NOT NULL
2214 AND cap.DocGeneratorId IS NULL
2215 AND cap.CapDocGeneratorId IS NULL
2216 AND ISNULL(cap.Retur,0) = 0
2217 AND poz.CantIesire > 0
2218 AND ISNULL(p.TipItem,'P') <> 'S'
2219 and ISNULL(poz.ContineDetalii,0)=0
2220 --AND @Descarcare = 1
2221 --AND @GestiuniRetail IS NOT NULL
2222 ORDER BY cap.DataIes
2223
2224 --bonuri generate din comanda
2225 INSERT INTO #TempSync
2226 (
2227 IesCapId
2228 , IesPozId
2229 , CantIesire
2230 , Cantitate2
2231 , Prelucrat
2232 , IntrPozOrigId
2233 , SerieIntrare
2234 , PretAmanunt
2235 , SOrderPozId
2236 , ProdusID
2237 , RetetaPozId
2238 , ParentIesPozId
2239 , DataIes
2240 , DocId
2241 )
2242 SELECT
2243 cap.IesCapId
2244 , poz.IesPozId
2245 , poz.CantIesire
2246 , poz.Cantitate2
2247 , 0
2248 , poz.IntrPozOrigId
2249 , poz.SerieIntrare
2250 , poz.PretAmanunt
2251 , poz.SorderPozId
2252 , poz.ProdusID
2253 , poz.RetetaPozId
2254 , poz.ParentIesPozId
2255 , cap.DataIes
2256 , cap.DocId
2257 FROM inv.IesPoz poz ( NOLOCK )
2258 INNER JOIN inv.Iescap cap ( NOLOCK )
2259 ON cap.IesCapId = poz.IesCapId
2260 LEFT JOIN dbo.tblProduse p ( NOLOCK )
2261 ON p.ProdusId = poz.ProdusId
2262 WHERE cap.FirmaId = @FirmaID
2263 AND cap.DivizieId = @DivizieId
2264 AND cap.DataAnulare IS NULL
2265 AND poz.IntrPozId IS NULL-- nesincronizate cu stocul
2266 AND cap.DocId = @DocIdBon
2267 AND cap.TipDocId = @ConfigTipDocIdBonCuStoc
2268 AND ISNULL(@TipDocStoc,0) = 1
2269 --AND cap.IesCapFacturaId IS NULL--fara bonurile din factura
2270 AND cap.RetailCapIesireID IS NULL
2271 --AND poz.RetailPozIesireID IS NULL
2272 AND cap.DocGeneratorId = 26
2273 AND cap.CapDocGeneratorId IS NOT NULL
2274 AND ISNULL(cap.Retur,0) = 0
2275 AND poz.CantIesire > 0
2276 AND ISNULL(p.TipItem,'P') <> 'S'
2277 and ISNULL(poz.ContineDetalii,0)=0
2278 --AND @Descarcare = 1
2279 --AND @GestiuniRetail IS NOT NULL
2280 ORDER BY cap.DataIes
2281
2282
2283 if @Debug=1 /*d*/
2284 BEGIN
2285 SELECT '#TempSync:'
2286 SELECT * FROM #TempSync
2287 SELECT 'Begin WHILE Bonuri'
2288 END
2289
2290 WHILE EXISTS
2291 ( SELECT TOP 1 *
2292 FROM #TempSync
2293 WHERE ISNULL(Prelucrat,0) = 0
2294 )
2295 BEGIN
2296
2297 DECLARE @SorderPozId INT =null
2298 , @RetetaPozId INT =null
2299 , @RetetaCapId INT = NULL
2300 , @DataIes DATE = NULL
2301 , @DocIdSync INT = NULL
2302
2303 SELECT @CantIesire = NULL
2304 , @Cant2 = NULL
2305
2306 --iau prima pozitie nesincronizata
2307 SELECT TOP 1
2308 @TempSyncId = TempSyncId
2309 , @IesCapId = IesCapId
2310 , @IesPozId = IesPozId
2311 , @CantIesire = CantIesire
2312 , @Cant2 = Cantitate2
2313 , @IntrPozOrigId = IntrPozOrigId
2314 , @SerieIntrare = SerieIntrare
2315 , @PretAmanuntBuffer = PretAmanunt
2316 , @SorderPozId=SorderPozId
2317 , @ProdusId=ProdusId
2318 , @RetetaPozId=RetetaPozId
2319 , @DataIes = DataIes
2320 , @DocIdSync = DocId
2321 FROM #TempSync
2322 WHERE ISNULL(Prelucrat,0) = 0
2323
2324 PRINT '@TempSyncId:' + CAST(@TempSyncId AS NVARCHAR)
2325 PRINT '@IesCapId:' + CAST(@IesCapId AS NVARCHAR)
2326 PRINT '@IesPozId:' + CAST(@IesPozId AS NVARCHAR)
2327 PRINT '@CantIesire:' + CAST(@CantIesire AS NVARCHAR)
2328 PRINT '@IntrPozOrigId:' + CAST(ISNULL(@IntrPozOrigId,0) AS NVARCHAR)
2329 PRINT '@SerieIntrare:' + CAST(ISNULL(@SerieIntrare,'') AS NVARCHAR)
2330 PRINT '@PretAmanuntBuffer:' + CAST(ISNULL(@PretAmanuntBuffer,0) AS NVARCHAR)
2331
2332 BEGIN
2333
2334 --SET @ProdusID = NULL
2335 SET @FurnizorID = NULL
2336 SET @FurnizorOriginalID = NULL
2337 SET @NumarIntr = NULL
2338 SET @DataIntr = NULL
2339 SET @DocIntrId = NULL
2340 SET @TipDocINtrID = NULL
2341 SET @DataScadenta = NULL
2342 SET @NumarFactIntr = NULL
2343 SET @DataFactIntr = NULL
2344 SET @PretIntrare = NULL
2345 SET @PretIntrareRedus = NULL
2346 SET @PretCost = NULL
2347 SET @PretCMP = NULL
2348 SET @GestiuneId = NULL
2349 SET @Amanunt = NULL
2350 SET @LotIntrare = NULL
2351 SET @DataExpirare = NULL
2352 SET @DataFabricare = NULL
2353 --SET @SerieIntrare = NULL
2354 --SET @IntrPozOrigId = NULL
2355 SET @POOrderPozId = NULL
2356 SET @Stoc = NULL
2357 SET @IntrPozId = NULL
2358 SET @PretAmanuntStoc = NULL
2359 SET @Reev = NULL
2360
2361 DECLARE @SorderPozParentId int =null
2362
2363 BEGIN TRAN
2364
2365 IF(@SorderPozId IS NOT NULL)
2366 BEGIN
2367
2368 SELECT TOP 1
2369 @IntrPozId = stoc.IntrPozId
2370 , @IntrPozOrigId = stoc.IntrPozOrigId
2371 , @GestiuneId = stoc.GestiuneId
2372 , @Amanunt = ISNULL(g.Amanunt,0)
2373 , @SerieIntrare = stoc.SerieIntrare
2374 , @LotIntrare = stoc.LotIntrare
2375 , @DataFabricare = stoc.DataFabricare
2376 , @DataExpirare = stoc.DataExpirare
2377
2378 , @Stoc = stoc.Cant
2379
2380 , @FurnizorID = stoc.FurnizorID
2381 , @FurnizorOriginalID = stoc.FurnizorOriginalID
2382 , @NumarIntr = stoc.NumarIntr
2383 , @DataIntr = stoc.DataIntr
2384 , @DocIntrId = stoc.DocIntrId
2385 , @TipDocINtrID = stoc.TipDocIntrId
2386 , @DataScadenta = stoc.DataScadentaIntr
2387 , @NumarFactIntr = stoc.NumarFactIntr
2388 , @DataFactIntr = stoc.DataFactIntr
2389 , @PretIntrare = stoc.PretIntrare
2390 , @PretIntrareRedus = stoc.PretIntrareRedus
2391 , @PretCost = stoc.PretCost
2392 , @PretCMP = stoc.PretCmp
2393 , @POOrderPozId = stoc.POrderPozId
2394
2395 , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
2396 , @Reev = CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0) THEN 0 ELSE 1 END
2397 , @SorderPozParentId = stoc.SorderPozId
2398 FROM dbo.SorderPoz stoc WITH ( ROWLOCK,UPDLOCK )
2399 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
2400 ON G.GestiuneId = stoc.GestiuneID
2401 --AND G.FirmaID = @FirmaID
2402 --AND G.DivizieId = @DivizieID
2403 LEFT JOIN #GestiuniBonConsum g13
2404 ON g13.val = g.GestiuneId
2405 AND @DocIdSync = 13
2406 LEFT JOIN #GestiuniBonFiscal g21
2407 ON g21.val = g.GestiuneId
2408 AND @DocIdSync = 21
2409 --INNER JOIN
2410 -- (
2411 -- SELECT val
2412 -- FROM dbo.SplitString(@ConfigGestiuni,',')
2413 -- WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
2414 -- ) CodGestSetare1
2415 -- ON G.CodGestiune = CodGestSetare1.val
2416 WHERE stoc.SOrderPozParentId = @SorderPozId
2417 AND Stoc.Cant > 0
2418 AND (@IntrPozOrigId IS NULL OR stoc.IntrPozId = @IntrPozOrigId)
2419 AND (@SerieIntrare IS NULL OR stoc.SerieIntrare = @SerieIntrare)
2420 AND ((@DocIdSync = 13 AND g13.val IS NOT NULL) OR (@DocIdSync = 21 AND g21.val IS NOT NULL))
2421 ORDER BY
2422 CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0)
2423 THEN 1 ELSE 0
2424 END DESC
2425 , stoc.DataIntr
2426
2427 END
2428
2429 IF @IntrPozId IS NULL
2430 BEGIN
2431 IF(@RetetaPozId IS NOT NULL)
2432 BEGIN
2433 SELECT @RetetaCapId = RetetaCapId
2434 FROM dbo.tblRetetaPoz (NOLOCK)
2435 WHERE RetetaPozId = @RetetaPozId
2436
2437 --SELECT TOP 1 @GestiuneSursaRetetaIds =cap.GestiuneSursaIds
2438 --FROM dbo.tblRetetaPoz p (NOLOCK)
2439 -- INNER JOIN dbo.tblretetacap cap (NOLOCK)
2440 -- on cap.RetetaCapId = p.RetetaCapId
2441 --WHERE p.RetetaPozId=@RetetaPozId
2442
2443 --IF ISNULL(@GestiuneSursaRetetaIds,'') = ''
2444 -- SELECT @GestiuneSursaRetetaIds = @GestiuniMPIds
2445
2446 SELECT TOP 1
2447 @IntrPozId = stoc.IntrPozId
2448 , @IntrPozOrigId = stoc.IntrPozOrigId
2449 , @GestiuneId = stoc.GestiuneId
2450 , @Amanunt = ISNULL(g.Amanunt,0)
2451 , @SerieIntrare = stoc.SerieIntrare
2452 , @LotIntrare = stoc.LotIntrare
2453 , @DataFabricare = stoc.DataFabricare
2454 , @DataExpirare = stoc.DataExpirare
2455 , @Stoc = stoc.Stoc
2456
2457 , @FurnizorID = stoc.FurnizorID
2458 , @FurnizorOriginalID = stoc.FurnizorOriginalID
2459 , @NumarIntr = stoc.NumarIntr
2460 , @DataIntr = stoc.DataIntr
2461 , @DocIntrId = stoc.DocIntrId
2462 , @TipDocINtrID = stoc.TipDocINtrID
2463 , @DataScadenta = stoc.DataScadenta
2464 , @NumarFactIntr = stoc.NumarFactIntr
2465 , @DataFactIntr = stoc.DataFactIntr
2466 , @PretIntrare = stoc.PretIntrare
2467 , @PretIntrareRedus = stoc.PretIntrareRedus
2468 , @PretCost = stoc.PretCost
2469 , @PretCMP = stoc.PretCMP
2470 , @POOrderPozId = stoc.POrderPozId
2471 , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
2472 , @Reev = CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0) THEN 0 ELSE 1 END
2473 FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
2474 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
2475 ON G.GestiuneId = stoc.GestiuneID
2476 INNER JOIN #Retete gr
2477 ON gr.RetetaCapId = @RetetaCapId
2478 AND gr.val = g.GestiuneId
2479 --INNER JOIN
2480 -- (
2481 -- SELECT val
2482 -- FROM dbo.SplitString(@GestiuneSursaRetetaIds,',')
2483 -- WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
2484 -- ) CodGestSetare1
2485 -- ON G.gestiuneid = CodGestSetare1.val
2486 WHERE stoc.FirmaId = @FirmaId
2487 AND stoc.DivizieId = @DivizieId
2488 AND Stoc > 0
2489 AND stoc.PRodusID = @ProdusId
2490 AND stoc.ClientCustodieId IS NULL
2491 AND (@IntrPozOrigId IS NULL OR stoc.IntrPozId = @IntrPozOrigId)
2492 AND (@SerieIntrare IS NULL OR stoc.SerieIntrare = @SerieIntrare)
2493 ORDER BY
2494 stoc.DataIntr
2495 , CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0) THEN 1 ELSE 0 END DESC
2496 END
2497
2498 --IF(@SToc is null)
2499 ELSE
2500 BEGIN
2501
2502 --iau prima pozitie din stoc din gestiunile de retail cu produsid!
2503 SELECT TOP 1
2504 @IntrPozId = stoc.IntrPozId
2505 , @IntrPozOrigId = stoc.IntrPozOrigId
2506 , @GestiuneId = stoc.GestiuneId
2507 , @Amanunt = ISNULL(g.Amanunt,0)
2508 , @SerieIntrare = stoc.SerieIntrare
2509 , @LotIntrare = stoc.LotIntrare
2510 , @DataFabricare = stoc.DataFabricare
2511 , @DataExpirare = stoc.DataExpirare
2512 , @Stoc = stoc.Stoc
2513
2514 , @FurnizorID = stoc.FurnizorID
2515 , @FurnizorOriginalID = stoc.FurnizorOriginalID
2516 , @NumarIntr = stoc.NumarIntr
2517 , @DataIntr = stoc.DataIntr
2518 , @DocIntrId = stoc.DocIntrId
2519 , @TipDocINtrID = stoc.TipDocINtrID
2520 , @DataScadenta = stoc.DataScadenta
2521 , @NumarFactIntr = stoc.NumarFactIntr
2522 , @DataFactIntr = stoc.DataFactIntr
2523 , @PretIntrare = stoc.PretIntrare
2524 , @PretIntrareRedus = stoc.PretIntrareRedus
2525 , @PretCost = stoc.PretCost
2526 , @PretCMP = stoc.PretCMP
2527
2528 , @POOrderPozId = stoc.POrderPozId
2529 , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
2530 , @Reev = CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0) THEN 0 ELSE 1 END
2531 FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
2532 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
2533 ON G.GestiuneId = stoc.GestiuneID
2534 LEFT JOIN #GestiuniBonConsum g13
2535 ON g13.val = g.GestiuneId
2536 AND @DocIdSync = 13
2537 LEFT JOIN #GestiuniBonFiscal g21
2538 ON g21.val = g.GestiuneId
2539 AND @DocIdSync = 21
2540 --INNER JOIN
2541 -- (
2542 -- SELECT val
2543 -- FROM dbo.SplitString(@ConfigGestiuni,',')
2544 -- WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
2545 -- ) CodGestSetare1
2546 -- ON G.CodGestiune = CodGestSetare1.val
2547 WHERE stoc.FirmaId = @FirmaId
2548 AND stoc.DivizieId = @DivizieId
2549 AND Stoc > 0
2550 AND stoc.PRodusID = @ProdusId
2551 AND stoc.ClientCustodieId IS NULL
2552 AND (@IntrPozOrigId IS NULL OR stoc.IntrPozId = @IntrPozOrigId)
2553 AND (@SerieIntrare IS NULL OR stoc.SerieIntrare = @SerieIntrare)
2554 AND ((@DocIdSync = 13 AND g13.val IS NOT NULL) OR (@DocIdSync = 21 AND g21.val IS NOT NULL))
2555 ORDER BY stoc.DataIntr
2556 , CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0) THEN 1 ELSE 0 END DESC
2557
2558 END
2559
2560
2561 --IF (
2562 -- ( @IntrPozId IS NULL
2563 -- OR @Stoc IS NULL
2564 -- )
2565 -- AND @SerieIntrare IS NOT NULL
2566 -- )
2567 --SELECT TOP 1
2568 -- @FurnizorID = stoc.FurnizorID
2569 -- , @FurnizorOriginalID = stoc.FurnizorOriginalID
2570 -- , @NumarIntr = stoc.NumarIntr
2571 -- , @DataIntr = stoc.DataIntr
2572 -- , @DocIntrId = stoc.DocIntrId
2573 -- , @TipDocINtrID = stoc.TipDocINtrID
2574 -- , @DataScadenta = stoc.DataScadenta
2575 -- , @NumarFactIntr = stoc.NumarFactIntr
2576 -- , @DataFactIntr = stoc.DataFactIntr
2577 -- , @PretIntrare = stoc.PretIntrare
2578 -- , @PretIntrareRedus = stoc.PretIntrareRedus
2579 -- , @PretCost = stoc.PretCost
2580 -- , @GestiuneId = stoc.GestiuneId
2581 -- , @Amanunt = ISNULL(g.Amanunt,0)
2582 -- , @LotIntrare = stoc.LotIntrare
2583 -- , @DataExpirare = stoc.DataExpirare
2584 -- , @DataFabricare = stoc.DataFabricare
2585 -- , @SerieIntrare = stoc.SerieIntrare
2586 -- , @IntrPozOrigId = stoc.IntrPozOrigId
2587 -- , @POOrderPozId = stoc.POrderPozId
2588 -- , @Stoc = stoc.Stoc
2589 -- , @IntrPozId = stoc.IntrPozId
2590 -- --, @ProdusID = stoc.ProdusId
2591 -- , @PretAmanuntStoc = ISNULL(stoc.PretAmanunt,0)
2592 -- , @Reev = CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0)
2593 -- THEN 0
2594 -- ELSE 1
2595 -- END
2596 --FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
2597 --INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
2598 -- ON G.GestiuneId = stoc.GestiuneID
2599 -- --AND G.FirmaID = @FirmaID
2600 -- --AND G.DivizieId = @DivizieID
2601 --INNER JOIN
2602 -- (
2603 -- SELECT val
2604 -- FROM dbo.SplitString(@ConfigGestiuni,',')
2605 -- WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
2606 -- ) CodGestSetare1
2607 -- ON G.CodGestiune = CodGestSetare1.val
2608 ----INNER JOIN tblProduse p WITH (NOLOCK)
2609 ---- ON P.ProdusId = stoc.ProdusId
2610
2611 --WHERE stoc.FirmaId = @FirmaId
2612 --AND stoc.DivizieId = @DivizieId
2613 --AND Stoc > 0
2614 --AND stoc.ProdusId=@ProdusId
2615 --AND stoc.SerieIntrare = @SerieIntrare
2616 --AND stoc.ClientCustodieId IS NULL
2617 --ORDER BY CASE WHEN ISNULL(@PretAmanuntBuffer,0) = ISNULL(stoc.PretAmanunt,0)
2618 -- THEN 1
2619 -- ELSE 0
2620 -- END DESC
2621 -- , stoc.DataIntr
2622 ----ORDER BY DataExpirare
2623
2624 END
2625
2626 IF @IntrPozId IS NULL
2627 BEGIN
2628 SELECT @RetetaCapId = NULL
2629 , @GestiuneDestinatieId = NULL
2630 , @Amanunt = null
2631
2632 SELECT TOP 1
2633 @RetetaCapId = RetetaCapId
2634 , @GestiuneDestinatieId = GestiuneDestinatieId
2635 , @Amanunt = Amanunt
2636 FROM #Retete
2637 WHERE ProdusId = @ProdusId
2638 AND @DataIes BETWEEN ISNULL(ValabilDeLa,@DataIes) AND ISNULL(ValabilPanaLa,@DataIes)
2639 AND ((@Cant2 IS NOT NULL AND Cant2 IS NOT NULL)
2640 OR Cant IS NOT NULL)
2641 ORDER BY ISNULL(ValabilDeLa,@DataIes)
2642
2643 IF @RetetaCapId IS NOT NULL
2644 AND EXISTS(SELECT TOP 1 * FROM dbo.tblRetetaPoz (NOLOCK) WHERE RetetaCapId = @RetetaCapId AND ISNULL(ExcludereDinMeniu,0) = 0 AND ISNULL(Cant,0) <> 0)
2645
2646 BEGIN
2647 INSERT INTO inv.IesPoz
2648 (
2649 IesCapId
2650 , FirmaID
2651 , DivizieId
2652 , ProdusID
2653 , IntrPozId
2654 , IntrPozOrigId
2655 , CantIesire
2656 , Cantitate2
2657 , CantFacturata
2658 , PretVanzare
2659 --, PretVanzareRedus
2660 , TaxCode
2661 , TaxInv
2662 , Tva
2663 , GestiuneID
2664 , PretAmanunt
2665 , PretAmanuntRedus
2666 , SerieIntrare
2667 , SOrderPozId
2668 , ValoareRedusaFaraTva
2669 , ValoareRedusaTva
2670 , ValoareFaraTva
2671 , ValoareTva
2672 , ParentIesPozId
2673 , RetetaPozId
2674 , DinJob
2675 )
2676 SELECT
2677 p.IesCapId
2678 , p.FirmaId
2679 , p.DivizieId
2680 , retpoz.ProdusId
2681 , null -- IntrPozId
2682 , null -- IntrPozOrigId
2683 , ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2684 THEN retpoz.Cant
2685 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2686 THEN (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)
2687 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2688 END
2689 END,@Setare623) -- CantIesire
2690 , ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2691 THEN retpoz.Cant
2692 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2693 THEN (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)
2694 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2695 END
2696 END * CASE WHEN pdet.GreutateNeta IS NOT NULL THEN pdet.GreutateNeta
2697 WHEN UPPER(um.UM) = 'KG' THEN 1
2698 END,3) -- Cantitate2
2699 , ROUND(CASE WHEN cap.DocId <> 8
2700 THEN CASE WHEN retpoz.DescarcaIntreg = 1
2701 THEN retpoz.Cant
2702 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2703 THEN (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)
2704 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2705 END
2706 END
2707 ELSE 0
2708 END,@setare623) -- CantFacturata
2709 , pcat.PretRidicata -- PretVanzare
2710 --, retpoz.Pret -- PretVanzareRedus
2711 , p.TaxCode
2712 , p.TaxInv
2713 , p.Tva --t.Tva
2714 , null -- GestiuneId
2715 , pcat.PretAmanunt -- PretAmanunt
2716 , null -- PretAmanuntRedus
2717 , null -- SerieIntrare
2718 , null
2719 , ROUND(ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2720 THEN retpoz.Cant
2721 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2722 THEN (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)
2723 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2724 END
2725 END,@Setare623) * retpoz.Pret,2)
2726 , ROUND(ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2727 THEN retpoz.Cant
2728 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2729 THEN (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)
2730 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2731 END
2732 END,@setare623) * retpoz.Pret * t.Tva/100.00,2)
2733 , ROUND(ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2734 THEN retpoz.Cant
2735 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2736 THEN (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)
2737 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2738 END
2739 END,@Setare623) * retpoz.Pret,@setare351)
2740 , ROUND(ROUND(CASE WHEN retpoz.DescarcaIntreg = 1
2741 THEN retpoz.Cant
2742 ELSE CASE WHEN @Cant2 IS NOT NULL AND retcap.Cant2 IS NOT NULL
2743 THEN (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)
2744 ELSE (ISNULL(retpoz.Cant,0) / ISNULL(NULLIF(1-(ISNULL(retpoz.Pierdere,0)+ISNULL(retpoz.Pierdere2,0))/100.00,0),1) / retcap.Cant) * p.CantIesire
2745 END
2746 END,@setare623) * retpoz.Pret * t.Tva/100.00,@setare351)
2747 , ISNULL(p.ParentIesPozId,p.IesPozId)
2748 , retpoz.RetetaPozId
2749 , 2
2750 FROM inv.IesPoz p (NOLOCK)
2751 INNER JOIN dbo.tblProduse pp (NOLOCK)
2752 ON pp.ProdusId = p.ProdusId
2753 INNER JOIN inv.IesCap cap (NOLOCK)
2754 ON cap.IesCapId = p.IesCapId
2755 AND cap.DataAnulare IS NULL
2756 INNER JOIN dbo.tblRetetaCap retcap (NOLOCK)
2757 ON retcap.RetetaCapId = @RetetaCapId
2758 INNER JOIN dbo.tblRetetaPoz retpoz (NOLOCK)
2759 ON retpoz.RetetaCapId = @RetetaCapId
2760 INNER JOIN dbo.tblProduse pdet (NOLOCK)
2761 ON pdet.ProdusId = retpoz.ProdusID
2762 INNER JOIN dbo.tblTva t (NOLOCK)
2763 on t.TvaId = pdet.TvaId
2764 INNER JOIN dbo.tblUm um (NOLOCK)
2765 on um.UmId = pdet.UmId
2766 LEFT JOIN inv.TipDoc td (NOLOCK)
2767 ON td.TipDocId = cap.TipDocId
2768 LEFT JOIN inv.IesPoz principal (NOLOCK)
2769 ON principal.IesPozId = p.ParentIesPozId
2770 LEFT JOIN dbo.tblPreturiProduse pcat (NOLOCK)
2771 ON pcat.CapListaPreturiId = @ConfigCapListaPreturiID
2772 AND pcat.ProdusId = pdet.ProdusId
2773 AND cap.DataIes BETWEEN CAST(pcat.ValabilDela AS DATE) AND CAST(ISNULL(pcat.ValabilPanaLa,cap.DataIes) AS DATE)
2774 AND pcat.DataAnulare IS NULL
2775 WHERE p.IesPozId = @IesPozId
2776 AND (ISNULL(td.IgnoraAmbalaje,0) = 0 OR ISNULL(pdet.Ambalaj,0) = 0)
2777 AND (ISNULL(retpoz.ExcludereDinMeniu,0) = 0 OR COALESCE(principal.IesPoz2Id,p.IesPoz2Id,0) = 0)
2778 AND ISNULL(retpoz.Cant,0) <> 0
2779
2780 --Daca este reteta la reteta fac update pe cantitate
2781 UPDATE inv.IesPoz
2782 SET CantIesire = 0
2783 WHERE IesPozId = @IesPozId
2784 AND ParentIesPozId IS NOT NULL
2785
2786
2787 -- Setez gestiunea destinatie
2788 UPDATE inv.IesPoz
2789 SET GestiuneId = @GestiuneDestinatieId
2790 , RetetaCapId = @RetetaCapId
2791 , ContineDetalii = 1
2792 , DinJob = 2
2793 , Amanunt = @Amanunt
2794 WHERE IesPozId = @IesPozId
2795 AND ParentIesPozId IS NULL
2796
2797 INSERT #TempSync
2798 (
2799 IesCapId
2800 ,IesPozId
2801 ,CantIesire
2802 ,Cantitate2
2803 ,Prelucrat
2804 ,IntrPozOrigId
2805 ,SerieIntrare
2806 ,PretAmanunt
2807 ,SOrderPozId
2808 ,ProdusID
2809 ,RetetaPozId
2810 ,ParentIesPozId
2811 ,DataIes
2812 ,DocId
2813 )
2814 SELECT
2815 p.IesCapId
2816 , p.IesPozId
2817 , p.CantIesire
2818 , p.Cantitate2
2819 , 0
2820 , null
2821 , null
2822 , p.PretAmanunt
2823 , null
2824 , p.ProdusId
2825 , p.RetetaPozId
2826 , p.ParentIesPozId
2827 , cap.DataIes
2828 , cap.DocId
2829 FROM inv.IesPoz p (NOLOCK)
2830 INNER JOIN inv.IesCap cap (NOLOCK)
2831 ON cap.IesCapId = p.IesCapId
2832 INNER JOIN dbo.tblProduse pr (NOLOCK)
2833 ON pr.ProdusId = p.Produsid
2834 AND pr.tipitem = 'P'
2835 INNER JOIN dbo.tblProduseDivizii pd (NOLOCK)
2836 ON pd.ProdusId = p.ProdusId
2837 AND pd.DivizieId = @DivizieId
2838 LEFT JOIN dbo.tblContabProduseFirma cpf (NOLOCK)
2839 ON cpf.ContabProdusId = pd.ContabProdusId
2840 AND cpf.FirmaId = @FirmaId
2841 AND cpf.DivizieId = @DivizieId
2842 WHERE p.ParentIesPozId = @IesPozId
2843 AND p.IntrPozId IS NULL
2844 END
2845 END
2846
2847 IF ( @IntrPozId IS NULL
2848 OR @Stoc IS NULL
2849 )
2850 BEGIN
2851 --SELECT TOP 1
2852 -- @ProdusId = ProdusID
2853 --FROM
2854 -- inv.IesPoz poz ( NOLOCK )
2855 --WHERE
2856 -- IesPozId = @IesPozId
2857 PRINT 'Nu exista stoc pentru produsul cu ID:' + CAST(@ProdusID AS VARCHAR)
2858 END
2859
2860 ELSE
2861
2862 BEGIN
2863
2864 IF ( @Stoc >= @CantIesire )
2865 BEGIN
2866
2867 PRINT '@Stoc>=CantIesire:' + CAST(@Stoc AS NVARCHAR(20)) + '>=' + CAST(@CantIesire AS NVARCHAR)
2868
2869 UPDATE inv.IesPoz
2870 SET
2871 IntrPozId = @IntrPozId
2872 , FurnizorID = @FurnizorID
2873 , FurnizorOriginalID = @FurnizorOriginalID
2874 , NumarIntr = @NumarIntr
2875 , DataIntr = @DataIntr
2876 , DocIntrId = @DocIntrId
2877 , TipDocINtrID = @TipDocINtrID
2878 , NumarFactIntr = @NumarFactIntr
2879 , DataFactIntr = @DataFactIntr
2880 , PretIntrare = @PretIntrare
2881 , PretIntrareRedus = @PretIntrareRedus
2882 , PretCost = @PretCost
2883 , GestiuneId = @GestiuneId
2884 , Amanunt = @Amanunt
2885 , LotIntrare = @LotIntrare
2886 , DataExpirare = @DataExpirare
2887 , DataFabricare = @DataFabricare
2888 , SerieIntrare = @SerieIntrare
2889 , IntrPozOrigId = @IntrPozOrigId
2890 , POrderPozId = @POOrderPozId
2891 , SorderPozId=ISNULL(@SorderPozParentId,@SorderPozId)
2892 , DinJob=CASE WHEN ISNULL(DinJob,0) = 2 THEN 2 ELSE 1 END
2893 , PretVanzare = CASE WHEN @DocIdSync = 13 THEN @PretCost ELSE PretVanzare END
2894 , PretVanzareRedus = CASE WHEN @DocIdSync = 13 THEN @PretCost ELSE PretVanzareRedus END
2895 , ValoareRedusaFaraTva = CASE WHEN @DocIdSync = 13 THEN ROUND(CantIesire * @PretCost,2) ELSE ValoareRedusaFaraTva END
2896 , ValoareRedusaTva = CASE WHEN @DocIdSync = 13 THEN ROUND(CantIesire * @PretCost * Tva * (1-ISNULL(TaxInv,0)) / 100.00,2) ELSE ValoareRedusaTva END
2897 , ValoareFaraTva = CASE WHEN @DocIdSync = 13 THEN ROUND(CantIesire * @PretCost,@setare351) ELSE ValoareFaraTva END
2898 , ValoareTva = CASE WHEN @DocIdSync = 13 THEN ROUND(CantIesire * @PretCost * Tva * (1-ISNULL(TaxInv,0)) / 100.00,@setare351) ELSE ValoareTva END
2899 WHERE IesPozId = @IesPozId
2900 AND FirmaID = @FirmaID
2901 AND DivizieId = @DivizieID
2902
2903
2904 IF(@SOrderPozParentId is null)
2905 BEGIN
2906
2907 UPDATE inv.Stoc
2908 SET Stoc = Stoc - @CantIesire
2909 WHERE IntrPozId = @IntrPozId
2910 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'')
2911 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
2912 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
2913 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
2914 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
2915 AND ((ISNULL(@Reev,0) = 0 AND ISNULL(PretAmanunt,0) = ISNULL(@PretAmanuntBuffer,0)) OR ISNULL(PretAmanunt,0) = ISNULL(@PretAmanuntStoc,0))
2916 AND FirmaID = @FirmaID
2917 AND DivizieID = @DivizieID
2918 --AND Stoc>0
2919 AND ClientCustodieId IS NULL
2920
2921 END
2922
2923 ELSE
2924
2925 BEGIN
2926
2927 UPDATE SorderPoz
2928 set cant=cant-@CantIesire
2929 WHERE SorderPozId=@sorderPozParentId
2930
2931 END
2932 END
2933
2934 IF ( @Stoc < @CantIesire )
2935 BEGIN
2936
2937 PRINT '@Stoc<CantIesire:' + CAST(@Stoc AS NVARCHAR) + '<' + CAST(@CantIesire AS NVARCHAR)
2938
2939 UPDATE inv.IesPoz
2940 SET
2941 IntrPozId = @IntrPozId
2942 , CantIesire = @Stoc
2943 , CantFacturata = @Stoc
2944 , FurnizorID = @FurnizorID
2945 , FurnizorOriginalID = @FurnizorOriginalID
2946 , NumarIntr = @NumarIntr
2947 , DataIntr = @DataIntr
2948 , DocIntrId = @DocIntrId
2949 , TipDocINtrID = @TipDocINtrID
2950 , NumarFactIntr = @NumarFactIntr
2951 , DataFactIntr = @DataFactIntr
2952 , PretIntrare = @PretIntrare
2953 , PretIntrareRedus = @PretIntrareRedus
2954 , PretCost = @PretCost
2955 , GestiuneId = @GestiuneId
2956 , Amanunt = @Amanunt
2957 , LotIntrare = @LotIntrare
2958 , DataExpirare = @DataExpirare
2959 , DataFabricare = @DataFabricare
2960 , SerieIntrare = @SerieIntrare
2961 , IntrPozOrigId = @IntrPozOrigId
2962 , POrderPozId = @POOrderPozId
2963 , SorderPozId=isnull(@sorderPozParentId,@SorderPozId)
2964 , PretVanzare = CASE WHEN @DocIdSync = 13 THEN @PretCost ELSE PretVanzare END
2965 , PretVanzareRedus = CASE WHEN @DocIdSync = 13 THEN @PretCost ELSE PretVanzareRedus END
2966 , ValoareRedusaFaraTva = CASE WHEN @DocIdSync = 13 THEN ROUND(@Stoc * @PretCost,2) ELSE ROUND((ValoareRedusaFaraTva/CantIesire)*@Stoc,6) END
2967 , ValoareRedusaTva= CASE WHEN @DocIdSync = 13 THEN ROUND(@Stoc * @PretCost * Tva * (1-ISNULL(TaxInv,0)) / 100.00,2) ELSE ROUND((ValoareRedusaTva/CantIesire)*@Stoc,6) END
2968 , ValoareFaraTva=CASE WHEN @DocIdSync = 13 THEN ROUND(@Stoc * @PretCost,@setare351) ELSE ROUND((ValoareFaraTva/CantIesire)*@Stoc,6) END
2969 , ValoareTva=CASE WHEN @DocIdSync = 13 THEN ROUND(@Stoc * @PretCost * Tva * (1-ISNULL(TaxInv,0)) / 100.00,@Setare351) ELSE ROUND((ValoareTva/CantIesire)*@Stoc,6) END
2970 , DinJob=CASE WHEN ISNULL(DinJob,0) = 2 THEN 2 ELSE 1 END
2971 WHERE IesPozId = @IesPozId
2972 AND FirmaID = @FirmaID
2973 AND DivizieId = @DivizieID
2974
2975 IF(@sorderPozParentId is null)
2976 BEGIN
2977
2978 UPDATE inv.Stoc
2979 SET Stoc = 0
2980 WHERE IntrPozId = @IntrPozId
2981 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'')
2982 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
2983 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
2984 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
2985 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
2986 AND ((ISNULL(@Reev,0) = 0 AND ISNULL(PretAmanunt,0) = ISNULL(@PretAmanuntBuffer,0)) OR ISNULL(PretAmanunt,0) = ISNULL(@PretAmanuntStoc,0))
2987 AND FirmaID = @FirmaID
2988 AND DivizieID = @DivizieID
2989 AND ClientCustodieId IS NULL
2990 END
2991
2992 ELSE
2993
2994 BEGIN
2995
2996 UPDATE Sorderpoz
2997 set Cant=0
2998 WHERE SorderPozId=@SorderPozParentID
2999
3000 END
3001 --SELECT 'Pozitie reinserat in IesPoz:'
3002 --SELECT IesCapId
3003 -- , FirmaID
3004 -- , DivizieId
3005 -- , ProdusID
3006 -- , @IntrPozId
3007 -- , @IntrPozId
3008 -- , @CantIesire - @Stoc
3009 -- , @CantIesire - @Stoc
3010 -- , PretVanzare
3011 -- , PretVanzareRedus
3012 -- , Tva
3013 -- , GestiuneID
3014 -- , PretAmanunt
3015 -- , PretAmanuntRedus
3016 -- , RetailPozIesireID
3017 --FROM inv.IesPoz (NOLOCK)
3018 --WHERE IesPozId = @IesPozID
3019 -- AND FirmaID = @FirmaID
3020 -- AND DivizieID = @DivizieID
3021
3022 INSERT INTO inv.IesPoz
3023 (
3024 IesCapId
3025 ,ParentIesPozId
3026 ,FirmaID
3027 ,DivizieId
3028 ,ProdusID
3029 ,IntrPozId
3030 ,IntrPozOrigId
3031 ,CantIesire
3032 ,Cantitate2
3033 ,CantFacturata
3034 ,PretVanzare
3035 ,PretVanzareRedus
3036 ,Tva
3037 ,PretAmanunt
3038 ,PretAmanuntRedus
3039 ,RetailPozIesireID
3040 ,TaxCode
3041 ,SOrderPozId
3042 ,ValoareRedusaFaraTva
3043 ,ValoareRedusaTva
3044 ,ValoareFaraTva
3045 ,ValoareTva
3046 ,RetetaPozId
3047 ,DinJob
3048
3049 --,Amanunt
3050 )
3051 SELECT
3052 IP.IesCapId
3053 , IP.ParentIesPozId
3054 , IP.FirmaID
3055 , IP.DivizieId
3056 , IP.ProdusID
3057 , NULL
3058 , NULL
3059 , @CantIesire - @Stoc
3060 , ROUND(ISNULL((@CantIesire - @Stoc) * pr.GreutateNeta, CASE WHEN UPPER(um.Um) = 'KG' THEN @CantIesire - @Stoc END),3) AS Cantitate2
3061 , @CantIesire - @Stoc
3062 , CASE WHEN IC.DocId <> 13 THEN IP.PretVanzare END
3063 , CASE WHEN IC.DocId <> 13 THEN IP.PretVanzareRedus END
3064 , IP.Tva
3065 , IP.PretAmanunt
3066 , IP.PretAmanuntRedus
3067 , IP.RetailPozIesireID
3068 , IP.TaxCode
3069 , @SorderPozID
3070 , CASE WHEN IC.DocId <> 13 THEN ROUND((IP.ValoareRedusaFaraTva/IP.CantIesire)*( @CantIesire - @Stoc),6) END
3071 , CASE WHEN IC.DocId <> 13 THEN ROUND((IP.ValoareRedusaTva/IP.CantIesire)*( @CantIesire - @Stoc),6) END
3072 , CASE WHEN IC.DocId <> 13 THEN ROUND((IP.ValoareFaraTva/IP.CantIesire)*( @CantIesire - @Stoc),6) END
3073 , CASE WHEN IC.DocId <> 13 THEN ROUND((IP.ValoareTva/IP.CantIesire)*( @CantIesire - @Stoc),6) END
3074 , IP.RetetaPozId
3075 , IP.DinJob
3076 --, 1
3077 FROM inv.IesPoz IP (NOLOCK)
3078 inner join inv.IesCap IC (NOLOCK)
3079 ON IC.IesCapId = IP.IesCapId
3080 inner join dbo.tblproduse pr (NOLOCK)
3081 on pr.produsid = ip.ProdusId
3082 inner join dbo.tblum um (NOLOCK)
3083 on um.umid = pr.UmId
3084 WHERE IP.IesPozId = @IesPozID
3085 AND IP.FirmaID = @FirmaID
3086 AND IP.DivizieID = @DivizieID
3087
3088 DECLARE @IesPOzIdInserat INT
3089
3090 SET @IesPozIdInserat = SCOPE_IDENTITY()
3091
3092 PRINT '@IesPozIdInserat inserat pentru spargere:' + CAST(@IesPozIdInserat AS NVARCHAR)
3093
3094 INSERT INTO #TempSync
3095 (
3096 IesCapId
3097 , IesPozId
3098 , CantIesire
3099 , Cantitate2
3100 , Prelucrat
3101 , IntrPozOrigId
3102 , SerieIntrare
3103 , PretAmanunt
3104 , SOrderPozId
3105 , ProdusID
3106 , RetetaPozId
3107 , ParentIesPozId
3108 , DataIes
3109 , DocId
3110 )
3111 SELECT
3112 cap.IesCapId
3113 , poz.IesPozId
3114 , poz.CantIesire
3115 , poz.Cantitate2
3116 , 0
3117 , poz.IntrPozOrigId
3118 , poz.SerieIntrare
3119 , poz.PretAmanunt
3120 , poz.SorderPozId
3121 , poz.ProdusID
3122 , poz.RetetaPozId
3123 , poz.ParentIesPozId
3124 , cap.DataIes
3125 , cap.DocId
3126 FROM inv.IesPoz poz ( NOLOCK )
3127 INNER JOIN inv.Iescap cap ( NOLOCK )
3128 ON cap.IesCapId = poz.IesCapId
3129 WHERE cap.FirmaId = @FirmaID
3130 AND cap.DivizieId = @DivizieId
3131 AND poz.IntrPozId IS NULL
3132 --AND cap.DocId = @DocIdBon
3133 --AND ISNULL(cap.TipDocId,0) = @ConfigTipDocIdBonCuStoc
3134 --AND ISNULL(@TipDocStoc,0) = 1
3135 --AND cap.RetailCapIesireID IS NOT NULL
3136 --AND poz.RetailPozIesireID IS NOT NULL
3137 AND poz.IesPozId = @IesPozIdInserat
3138
3139 ORDER BY cap.DataIes
3140
3141 DECLARE @TempSyncIdInserat INT
3142
3143 SET @TempSyncIdInserat = SCOPE_IDENTITY()
3144
3145 PRINT '@TempSyncIdInserat inserat pentru spargere:' + CAST(@TempSyncIdInserat AS NVARCHAR)
3146
3147 END
3148
3149 --select @PretAmanuntBuffer,@PretAmanuntStoc
3150
3151 IF (ROUND(ISNULL(@PretAmanuntBuffer,0),@NrZecimaleAmanunt) <> ROUND(ISNULL(@PretAmanuntStoc,0),@NrZecimaleAmanunt)
3152 AND ISNULL(@GenerareReev,0) = 1
3153 )
3154 BEGIN
3155
3156 IF @ReevCapId IS NULL --inserare antet schimbare pret
3157 BEGIN
3158
3159 INSERT inv.ReevCap
3160 (
3161 FirmaId
3162 ,DivizieId
3163 ,DocId
3164 ,TipDocId
3165 ,PlajaId
3166 ,DataReev
3167 ,Observatii
3168 ,DataCreare
3169 ,UserCreare
3170 ,UserCreareId
3171 ,DataValidare
3172 ,UserValidare
3173 ,UserValidareId
3174 )
3175 SELECT TOP 1
3176 @FirmaId
3177 ,@DivizieID
3178 ,53
3179 ,@TipDocId
3180 ,@PlajaId
3181 ,CAST(@DataCrt AS DATE)
3182 ,NULL
3183 ,CAST(@DataCrt AS DATE)
3184 ,c.UserCreare
3185 ,c.UserCreareId
3186 ,CAST(@DataCrt AS DATE)
3187 ,ISNULL(c.UserValidare,c.UserCreare)
3188 ,ISNULL(c.UserValidareId,c.UserValidareId)
3189 FROM inv.IesPoz p (NOLOCK)
3190 INNER JOIN inv.IesCap c (NOLOCK)
3191 ON c.IesCapId = p.IesCapId
3192 WHERE p.IesPozId = @IesPozId
3193
3194 SET @ReevCapId = @@IDENTITY
3195
3196 UPDATE inv.reevcap
3197 SET NumarReev = CAST(@ReevCapId as nvarchar(100))
3198 WHERE ReevCapId = @ReevCapId
3199
3200 END
3201
3202 INSERT INTO inv.ReevPoz
3203 (
3204 ReevCapId
3205 ,ProdusId
3206 ,FirmaId
3207 ,DivizieId
3208 ,GestiuneId
3209 ,LotIntrare
3210 ,DataExpirare
3211 ,SerieIntrare
3212 ,DataFabricare
3213 ,IntrPozId
3214 ,FurnizorId
3215 ,FurnizorOriginalId
3216 ,NumarIntr
3217 ,DataIntr
3218 ,DocIntrId
3219 ,TipDocIntrId
3220 ,NumarFactIntr
3221 ,DataFactIntr
3222 ,PretIntrare
3223 ,PretIntrareRedus
3224 ,PretCostNew
3225 ,PretCostOld
3226 ,PretAmanuntNew
3227 ,PretAmanuntOld
3228 ,AssetId
3229 ,ClientCustodieId
3230 ,ExplicatiiIntr1
3231 ,ExplicatiiIntr2
3232 ,PretIntrareValuta
3233 ,ValutaIntrareId
3234 ,ValutaDefaultId
3235 ,CantReev
3236 ,ContractFurnizorPozId
3237 ,POrderPozId
3238 ,ValutaCmdFurnId
3239 ,PretCmdFurn
3240 ,PretCtrFurn
3241 ,ValutaCtrFurnId
3242 ,TVA
3243 ,TaxCode
3244 ,IdExternImport
3245 )
3246 SELECT @ReevCapId
3247 ,p.ProdusId
3248 ,p.FirmaId
3249 ,p.DivizieId
3250 ,p.GestiuneId
3251 ,p.LotIntrare
3252 ,p.DataExpirare
3253 ,p.SerieIntrare
3254 ,p.DataFabricare
3255 ,p.IntrPozId
3256 ,p.FurnizorId
3257 ,p.FurnizorOriginalId
3258 ,p.NumarIntr
3259 ,p.DataIntr
3260 ,p.DocIntrId
3261 ,p.TipDocIntrId
3262 ,p.NumarFactIntr
3263 ,p.DataFactIntr
3264 ,p.PretIntrare
3265 ,p.PretIntrareRedus
3266 ,p.PretCost
3267 ,p.PretCost
3268 ,ISNULL(p.PretAmanunt,0)
3269 ,ISNULL(@PretAmanuntStoc,0)
3270 ,p.AssetId
3271 ,p.ClientCustodieId
3272 ,p.ExplicatiiIntr1
3273 ,p.ExplicatiiIntr2
3274 ,p.PretIntrareValuta
3275 ,p.ValutaIntrareId
3276 ,p.ValutaIntrareDefaultId
3277 ,CASE WHEN @CantIesire <= @Stoc
3278 THEN @CantIesire
3279 ELSE @Stoc
3280 END
3281 ,p.ContractFurnizorPozId
3282 ,p.POrderPozId
3283 ,p.ValutaCmdFurnId
3284 ,p.PretCmdFurn
3285 ,p.PretCtrFurn
3286 ,p.ValutaCtrFurnId
3287 ,p.Tva
3288 ,@TaxCodeP
3289 ,p.RetailPozIesireId
3290 FROM inv.IesPoz p (NOLOCK)
3291 INNER JOIN inv.IesCap c (NOLOCK)
3292 ON p.IesCapId = c.IesCapId
3293 INNER JOIN dbo.tblGestiuni g (NOLOCK)
3294 on p.GestiuneId=g.GestiuneId
3295 WHERE p.IesPozId = @IesPozId
3296 and ISNULL(g.Amanunt,0)=1
3297 --AND c.IesCapFacturaId IS NULL--fara bonurile din factura
3298
3299 END
3300 END
3301
3302 UPDATE #TempSync
3303 SET Prelucrat = 1
3304 WHERE TempSyncId = @TempSyncId
3305
3306 PRINT '@TempSyncId prelucrat:' + CAST(@TempSyncId AS NVARCHAR)
3307
3308 IF XACT_STATE() = 1
3309 COMMIT TRANSACTION
3310
3311 END
3312
3313 END--end while
3314
3315 IF EXISTS (SELECT TOP 1 * FROM #TempSync WHERE ParentIesPozId IS NOT NULL)
3316 BEGIN -- propagare pret vanzare in detalii
3317
3318 UPDATE poz
3319 SET /*poz.PretVanzare = ROUND(ROUND(CASE WHEN pr.TipItem = 'S'
3320 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
3321 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3322 END,2) / (1 + poz.Tva / 100.00), @NrZecimalePretVanzare)
3323 ,*/poz.PretVanzareRedus = ROUND(ROUND(CASE WHEN pr.TipItem = 'S'
3324 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
3325 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3326 END,2) / (1 + poz.Tva / 100.00), @NrZecimalePretVanzare)
3327 /*,poz.PretAmanunt = ROUND(CASE WHEN pr.TipItem = 'S'
3328 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
3329 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3330 END,2)*/
3331 ,poz.PretAmanuntRedus = ROUND(CASE WHEN pr.TipItem = 'S'
3332 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
3333 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3334 END,2)
3335
3336 ,poz.ValoareRedusaFaraTva = ROUND(poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3337 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
3338 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3339 END - ROUND(((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3340 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
3341 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3342 END * poz.Tva) / (100.00 + poz.Tva)), 6), 6)
3343 ,poz.ValoareRedusaTva = ROUND(((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3344 THEN ISNULL(poz.PretAmanuntRedus,ISNULL(poz.PretVanzareRedus,poz.PretVanzare) * (1 + poz.Tva / 100.00))
3345 ELSE (ISNULL(poz.PretCost,0) * (1 + (poz.Tva / 100.00)) * ISNULL(det.ValAmanuntRedusParinte,det.ValAmanuntParinte) / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3346 END * poz.Tva) / (100.00 + poz.Tva)), 6)
3347 /*,poz.ValoareFaraTva = ROUND(poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3348 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
3349 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3350 END - ROUND((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3351 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
3352 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3353 END * poz.Tva) / (100.00 + poz.Tva), 6), 6)
3354 ,poz.ValoareTva = ROUND((poz.CantIesire * CASE WHEN pr.TipItem = 'S'
3355 THEN ISNULL(poz.PretAmanunt,poz.PretVanzare * (1 + poz.Tva / 100.00))
3356 ELSE (ISNULL(poz.PretCost,0) * (1 + poz.Tva / 100.00) * det.ValAmanuntParinte / ISNULL(NULLIF(det.ValoareCostCuTva,0),1))
3357 END * poz.Tva) / (100.00 + poz.Tva), 6)*/
3358 FROM inv.IesPoz poz
3359 INNER JOIN dbo.tblProduse pr (NOLOCK)
3360 ON pr.ProdusId = poz.ProdusId
3361 INNER JOIN dbo.tblTva t (NOLOCK)
3362 ON t.TvaId = pr.TvaId
3363 INNER JOIN (SELECT
3364 p.ParentIesPozId
3365 , SUM(CASE WHEN prod.TipItem = 'P' THEN p.CantIesire * ISNULL(p.PretCost,0) * (1 + p.Tva / 100.00)
3366 ELSE 0 END) AS ValoareCostCuTva
3367
3368 , MAX(pp.CantIesire * pp.PretAmanunt) AS ValAmanuntParinte
3369 , MAX(pp.CantIesire * ISNULL(pp.PretAmanuntRedus,pp.PretAmanunt)) AS ValAmanuntRedusParinte
3370
3371 , SUM(CASE WHEN prod.TipItem = 'S' THEN p.CantIesire * ISNULL(p.PretVanzare,0)
3372 ELSE 0 END) AS ValoareServicii
3373 , SUM(CASE WHEN prod.TipItem = 'S' THEN p.CantIesire * COALESCE(p.PretVanzareRedus,p.PretVanzare,0)
3374 ELSE 0 END) AS ValoareRedusaServicii
3375 FROM inv.IesPoz p (nolock)
3376 INNER JOIN inv.IesPoz (nolock) pp
3377 on pp.IesPozId = p.ParentIesPozId
3378 INNER JOIN dbo.tblProduse prod (NOLOCK)
3379 ON prod.ProdusId = p.ProdusId
3380 INNER JOIN (SELECT temp.ParentIesPozId as IesPozId
3381 FROM #TempSync temp
3382 GROUP BY temp.ParentIesPozId) temp
3383 ON temp.IesPozId = p.ParentIesPozId
3384 GROUP BY p.ParentIesPozId) det
3385 ON poz.ParentIesPozId = det.ParentIesPozId
3386
3387 UPDATE poz
3388 SET poz.PretCost = ROUND(ISNULL(det.ValoareCost,0) / ISNULL(NULLIF(poz.CantIesire,0),1),@NrZecimalePretIntrare)
3389 FROM inv.IesPoz poz
3390 INNER JOIN (SELECT p.ParentIesPozId
3391 , SUM(CASE WHEN prod.TipItem = 'P' THEN p.CantIesire * ISNULL(p.PretCost,0) ELSE 0 END) AS ValoareCost
3392 FROM inv.IesPoz p (NOLOCK)
3393 INNER JOIN dbo.tblProduse prod (NOLOCK)
3394 ON prod.ProdusId = p.ProdusId
3395 INNER JOIN (SELECT temp.ParentIesPozId as IesPozId
3396 FROM #TempSync temp
3397 GROUP BY temp.ParentIesPozId) temp
3398 ON temp.IesPozId = p.ParentIesPozId
3399 GROUP BY p.ParentIesPozId) det
3400 on poz.IesPozId = det.ParentIesPozId
3401 END
3402
3403 IF EXISTS (SELECT TOP 1 * FROM #TempSync WHERE DocId = 13)
3404 BEGIN
3405 DECLARE @CapDocIds NVARCHAR(MAX)
3406 , @DataDeLa DATE
3407 , @DataPanaLa DATE
3408
3409 SELECT @CapDocIds = ISNULL(@CapDocIds + ',','') + CAST(IesCapId AS NVARCHAR(MAX))
3410 , @DataDeLa = MIN(DataIes)
3411 , @DataPanaLa = MAX(DataIes)
3412 FROM #TempSync
3413 WHERE DocId = 13
3414 GROUP BY IesCapId
3415
3416 Update cap
3417 SET ValoareFaraTva = a.ValoareFaraTva
3418 , ValoareTva = a.ValoareTva
3419 , ValoareReducereFaraTva = a.ValoareReducereFaraTva
3420 , ValoareReducereTva = a.ValoareReducereTva
3421 , ValoareValuta = a.Valoarevaluta
3422 , ValoareValutaTva = a.ValoareValutaTva
3423 , ValoareValutaRedus = a.ValoareValutaredus
3424 , ValoareValutaTvaRedus=a.ValoareValutaTvaRedus
3425 , FaraTva = FaraTva --CASE WHEN @FaraTva = 1 THEN 1 ELSE 0 END
3426 , NrPozitii = a.NrPozitii
3427 , GreutateBrutaTotal = a.GreutateBrutaTotal
3428 , GreutateNetaTotal = a.GreutateNetaTotal
3429 , GreutateBrutaBax = a.GreutateBrutaBax
3430 , VolumBax = a.VolumBax
3431 FROM inv.IesCap cap
3432 INNER JOIN #TempSync tc
3433 ON tc.IescapId = cap.Iescapid
3434 AND tc.Docid = 13
3435 INNER JOIN (
3436 SELECT p.IesCapId
3437
3438 , SUM(ROUND(ISNULL(p.ValoareFaraTva,p.CantIesire * /*p.PretVanzare*/ p.PretCost),@setare351)) as ValoareFaraTva
3439 , SUM(ROUND(ISNULL(p.ValoareTva,p.CantIesire * /*p.PretVanzare*/ p.PretCost * p.Tva * (1-ISNULL(p.TaxInv, 0)) / 100.00),@setare351)) as ValoareTva
3440 , SUM(ROUND(ISNULL(p.ValoareRedusaFaraTva,p.CantIesire * /*ISNULL(p.PretVanzareRedus,p.PretVanzare)*/ p.PretCost),2)) as ValoareReducereFaraTva
3441 , SUM(ROUND(ISNULL(p.ValoareRedusaTva,p.CantIesire * /*ISNULL(p.PretVanzareRedus,p.PretVanzare)*/ p.PretCost * p.Tva * (1-ISNULL(p.TaxInv,0)) / 100.00),2)) as ValoareReducereTva
3442
3443 , SUM(ROUND(p.CantIesire * p.PretVanzareValuta,@setare351)) as ValoareValuta
3444 , SUM(ROUND(p.CantIesire * p.PretVanzareValuta * p.Tva * (1-ISNULL(p.TaxInv,0)) / 100.00,@setare351)) as ValoareValutaTva
3445 , SUM(ROUND(p.CantIesire * ISNULL(p.PretVanzareValutaRedus,p.PretVanzareValuta),@setare351) )as ValoareValutaRedus
3446 , SUM(ROUND(p.CantIesire * p.PretVanzareValutaRedus * p.Tva * (1-ISNULL(p.TaxInv,0)) / 100.00,@setare351)) as ValoareValutaTvaRedus
3447
3448 , COUNT(p.IesPozId) as NrPozitii
3449 , SUM(p.CantIesire * p.GreutateBruta) as GreutateBrutaTotal
3450 , SUM(p.CantIesire * p.GreutateNeta) as GreutateNetaTotal
3451 , SUM(p.GreutateBrutaBax) as GreutateBrutaBax
3452 , SUM(p.VolumBax) as VolumBax
3453
3454 FROM inv.Iespoz p (NOLOCK)
3455 INNER JOIN dbo.tf_IDs_from_a_string(@CapDocIds) x
3456 ON x.Numbers = p.IesCapId
3457 WHERE ISNULL(p.ParentIesPozId, 0) = 0
3458 AND p.IesPoz2Id IS NULL
3459 --AND p.IntrPozId IS NOT NULL
3460 GROUP BY p.IesCapId) a
3461 ON a.IesCapId = cap.IesCapId
3462 WHERE cap.FirmaId = @FirmaId
3463 AND cap.DivizieId = @DivizieId
3464
3465 EXEC dbo.Doc2Conta
3466 @DocIds = '13'
3467 , @CapId = @CapDocIds
3468 , @DeLa = @DataDeLa
3469 , @PanaLa = @DataPanaLa
3470 , @SYS_USERID = 0
3471 , @SYS_LANGID = 'RO'
3472 , @SYS_UNITID = @FirmaId
3473 , @SYS_DIVID = @DivizieID
3474 , @SYS_PARTID = @SYS_PARTID
3475
3476 END
3477
3478 if @Debug=1 /*d*/
3479 BEGIN
3480 SELECT 'End WHILE Bonuri'
3481 END
3482
3483 ------------------------------------------------------------------------------
3484
3485
3486 --SELECT 'Pozitii nesincronizate:'
3487
3488 --SELECT *
3489 --FROM inv.IesPoz poz (NOLOCK)
3490 --JOIN inv.IesCap cap (NOLOCK)
3491 -- ON poz.IesCapId = cap.IesCapId
3492 --WHERE cap.FirmaId = @FirmaId
3493 -- AND cap.DivizieId = @DivizieId
3494 -- AND cap.DocId = @DocIdBon
3495 -- AND poz.IntrPozId IS NULL
3496 -- AND cap.CodCalc IS NOT NULL
3497 -- AND cap.RetailCapIesireId IS NOT NULL
3498 -- AND poz.RetailPozIesireId IS NOT NULL
3499 -- AND ISNULL(cap.Retur, 0) <> 1
3500
3501 --------------------------------------------------------------------------------------------------------------
3502
3503
3504 --Legatura la stoc la pozitiile de retur (cant negativa)
3505
3506 PRINT 'Bonuri retur'
3507
3508 IF OBJECT_ID('tempdb..#TempSyncRetur') IS NOT NULL
3509 DROP TABLE #TempSyncRetur
3510
3511 CREATE TABLE #TempSyncRetur
3512 (
3513 TempSyncId INT IDENTITY(1,1)
3514 ,IesCapId INT
3515 ,IesPozId INT
3516 ,RetailPozIesireReturId INT
3517 ,IesPozRefDetId INT
3518 ,CantRetur DECIMAL(18,5)
3519 ,ProdusId INT
3520 ,Prelucrat BIT
3521 ,ParentIesPozId INT
3522 )
3523
3524 INSERT INTO #TempSyncRetur
3525 (
3526 IesCapId
3527 ,IesPozId
3528 ,RetailPozIesireReturId
3529 ,IesPozRefDetId
3530 ,CantRetur
3531 ,ProdusId
3532 ,Prelucrat
3533 ,ParentIesPozId
3534 )
3535 SELECT
3536 cap.IesCapId
3537 , poz.IesPozId
3538 , poz.RetailPozIesireReturId
3539 , CASE WHEN poz.RetailPozIesireReturId IS NULL THEN poz.IesPozRefId END
3540 , poz.CantIesire
3541 , poz.ProdusId
3542 , 0
3543 , poz.ParentIesPozId
3544 FROM inv.IesPoz poz ( NOLOCK )
3545 INNER JOIN inv.Iescap cap ( NOLOCK )
3546 ON cap.IesCapId = poz.IesCapId
3547 LEFT JOIN dbo.tblProduse p ( NOLOCK )
3548 ON poz.ProdusId = p.ProdusId
3549 WHERE cap.FirmaId = @FirmaID
3550 AND cap.DivizieId = @DivizieId
3551 AND cap.DataAnulare IS NULL
3552 --AND poz.IesPozRefId IS NULL-- nesincronizate
3553 AND poz.IntrPozId IS NULL
3554 AND ISNULL(poz.ContineDetalii,0) = 0
3555 AND ((cap.DocId = 13 AND cap.TipDocId = @ConfigTipDocBonConsum AND @TipDocBCStoc = 1)
3556 OR (cap.Docid = 21 AND cap.TipDocId = @ConfigTipDocIdBonCuStoc AND @TipDocStoc = 1))
3557 AND cap.RetailCapIesireID IS NOT NULL
3558 AND ((poz.RetailPozIesireID IS NOT NULL AND poz.RetailPozIesireReturID IS NOT NULL)
3559 OR poz.ParentIesPozId IS NOT NULL)
3560 AND cap.DocGeneratorId IS NULL
3561 AND cap.CapDocGeneratorId IS NULL
3562 AND ISNULL(cap.Retur,0) = 1
3563 AND ISNULL(p.TipItem,'P') <> 'S'
3564 --AND cap.IesCapFacturaId IS NULL--fara bonurile din factura
3565 --AND @Descarcare = 1
3566 --AND @GestiuniRetail IS NOT NULL
3567 ORDER BY cap.DataIes
3568
3569 if @Debug=1 /*d*/
3570 BEGIN
3571 SELECT '#TempSyncRetur:'
3572 SELECT * FROM #TempSyncRetur
3573 SELECT 'Begin WHILE Bonuri retur'
3574 END
3575
3576 WHILE EXISTS
3577 (
3578 SELECT TOP 1
3579 *
3580 FROM #TempSyncRetur
3581 WHERE ISNULL(Prelucrat,0) = 0
3582 )
3583 BEGIN
3584
3585 SELECT @TempSyncID = NULL
3586 , @IesCapId = NULL
3587 , @IesPozId = NULL
3588 , @CantRetur = NULL
3589 , @RetailPozIesireReturID = NULL
3590 , @IesPozRefDetId = NULL
3591 , @ProdusID = NULL
3592
3593 SELECT TOP 1
3594 @TempSyncId = TempSyncId
3595 , @IesCapId = IesCapId
3596 , @IesPozId = IesPozId
3597 , @CantRetur = CantRetur
3598 , @RetailPozIesireReturID = RetailPozIesireReturId
3599 , @IesPozRefDetId = IesPozRefDetId
3600 , @ProdusID = ProdusId
3601 FROM #TempSyncRetur
3602 WHERE ISNULL(Prelucrat,0) = 0
3603
3604 PRINT '@TempSyncId:' + CAST(@TempSyncId AS NVARCHAR)
3605 PRINT '@IesCapId:' + CAST(@IesCapId AS NVARCHAR)
3606 PRINT '@IesPozId:' + CAST(@IesPozId AS NVARCHAR)
3607 PRINT '@CantRetur:' + CAST(@CantRetur AS NVARCHAR)
3608 PRINT '@RetailPozIesireReturId:' + CAST(@RetailPozIesireReturId AS NVARCHAR)
3609 PRINT '@ProdusID:' + CAST(@ProdusID AS NVARCHAR)
3610
3611 --DECLARE @RetailCapIesireId INT
3612 --, @RetailPozIesireId INT --pozitia originala a bonului la care fac retur =
3613 --, @Identificator NVARCHAR(20)
3614
3615 --SELECT @RetailCapIesireId = CapIesireId
3616 -- , @RetailPozIesireId = PozIesireId
3617 -- , @Identificator = Identificator
3618 --FROM Retail.BufferPozitiiBon (NOLOCK)
3619 --WHERE FirmaId = @FirmaId
3620 -- AND DivizieId = @DivizieId
3621 -- AND PozIesireId = @RetailPozIesireReturID
3622
3623 --PRINT '@RetailCapIesireId:' + CAST(@RetailCapIesireId AS NVARCHAR(10))
3624 --PRINT '@RetailPozIesireId:' + CAST(@RetailPozIesireId AS NVARCHAR(10))
3625 --PRINT '@Identificator:' + CAST(@Identificator AS NVARCHAR(20))
3626
3627 --IF (
3628 -- ISNULL(@RetailCapIesireId, 0) > 0
3629 -- AND ISNULL(@RetailPozIesireId, 0) > 0
3630 -- )
3631 -- BEGIN
3632
3633 DECLARE @IesPozIdRef INT, @ContineDetalii BIT, @CantitatePF DECIMAL(22,8)
3634 DECLARE
3635 @IesCapIdREf INT
3636 , @CantReturRamasa DECIMAL(18,5)
3637 SET @IntrPozId = NULL
3638 SET @DataExpirare = NULL
3639 SET @GestiuneId = NULL
3640 SET @Amanunt = NULL
3641 SET @DataFabricare = NULL
3642 SET @SerieIntrare = NULL
3643 SET @LotIntrare = NULL
3644 SET @IntrPozOrigId = NULL
3645 SET @FurnizorID = NULL
3646 SET @FurnizorOriginalID = NULL
3647 SET @NumarIntr = NULL
3648 SET @DataIntr = NULL
3649 SET @DocIntrId = NULL
3650 SET @TipDocINtrID = NULL
3651 SET @DataScadenta = NULL
3652 SET @NumarFactIntr = NULL
3653 SET @DataFactIntr = NULL
3654 SET @PretIntrare = NULL
3655 SET @PretIntrareRedus = NULL
3656 SET @PretCost = NULL
3657 SET @GestiuneId = NULL
3658 SET @LotIntrare = NULL
3659 SET @DataExpirare = NULL
3660 SET @POOrderPozId = NULL
3661 SET @CantReturRamasa = NULL
3662 SET @ContineDetalii = NULL
3663 SET @CantitatePF = NULL
3664
3665
3666
3667 --aleg pozitia originala din inv.IesPoz la care MAI POT FACE retur
3668 SELECT TOP 1
3669 @IesPozIdRef = p.IesPozId
3670 , @IesCapIDRef = c.IesCapId
3671 , @IntrPozId = IntrPozId
3672 , @IntrPozOrigId = IntrPozOrigId
3673 , @DataExpirare = DataExpirare
3674 , @GestiuneId = GestiuneId
3675 , @DataFabricare = DataFabricare
3676 , @SerieIntrare = SerieIntrare
3677 , @LotIntrare = LotIntrare
3678 , @FurnizorID = FurnizorID
3679 , @FurnizorOriginalID = FurnizorOriginalID
3680 , @NumarIntr = NumarIntr
3681 , @DataIntr = DataIntr
3682 , @DocIntrId = DocIntrId
3683 , @TipDocINtrID = TipDocINtrID
3684 , @DataScadenta = DataScadenta
3685 , @NumarFactIntr = NumarFactIntr
3686 , @DataFactIntr = DataFactIntr
3687 , @PretIntrare = PretIntrare
3688 , @PretIntrareRedus = PretIntrareRedus
3689 , @PretCost = PretCost
3690 , @GestiuneId = GestiuneId
3691 , @Amanunt = ISNULL(Amanunt,0)
3692 , @CantReturRamasa = p.CantIesire - ISNULL(p.CantRetur,0)
3693 , @ContineDetalii = p.ContineDetalii
3694 , @CantitatePF = p.CantIesire
3695 FROM inv.IesPoz p ( NOLOCK )
3696 INNER JOIN inv.IesCap c ( NOLOCK )
3697 ON c.IesCapId = p.IesCapId
3698 WHERE c.FirmaID = @FirmaID
3699 AND c.DivizieID = @DivizieId
3700 AND c.DataAnulare IS NULL
3701 AND ISNULL(c.Retur,0) = 0
3702 --AND c.RetailCapIesireId = @RetailCapIesireId
3703 --AND c.CodCalc = @Identificator
3704 AND (p.RetailPozIesireId = @RetailPozIesireReturId OR IesPozId = @IesPozRefDetId)
3705 AND p.ProdusId = @ProdusID
3706 AND (p.IntrPozId IS NOT NULL OR p.ContineDetalii = 1) --sincronizat cu stocul sau contine detalii
3707 AND c.DocId IN (13,21)
3708 AND p.CantIesire - ISNULL(p.CantRetur,0) > 0
3709 --AND c.IesCapFacturaId IS NULL--fara bonurile din factura
3710 AND c.DocGeneratorId IS NULL
3711 AND c.CapDocGeneratorId IS NULL
3712 ORDER BY p.IesPozId DESC
3713
3714 PRINT '@IntrPozId:' + CAST(@IntrPozId AS NVARCHAR)
3715 PRINT '@IesPozIdRef:' + CAST(@IesPozIdRef AS NVARCHAR)
3716 PRINT '@IesCapIDRef:' + CAST(@IesCapIDRef AS NVARCHAR)
3717
3718 DECLARE @CantReturDeFacut DECIMAL(18,5) = 0
3719 SET @CantReturDeFacut = CASE WHEN ISNULL(( -1 ) * @CantRetur,0) > ISNULL(@CantReturRamasa,0)
3720 THEN ISNULL(@CantReturRamasa,0)
3721 ELSE ISNULL(( -1 ) * @CantRetur,0)
3722 END
3723
3724
3725 -- eroare daca nu am unicitate pe stoc
3726 IF ( ( SELECT
3727 COUNT(IntrPozId)
3728 FROM
3729 inv.Stoc (NOLOCK)
3730 WHERE
3731 IntrPozId = @IntrPozId
3732 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'') -- ISNULL
3733 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
3734 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
3735 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
3736 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
3737 AND FirmaID = @FirmaID
3738 AND DivizieID = @DivizieID
3739 ) > 1
3740 )
3741 BEGIN
3742 RAISERROR ('Nu exista unicitate pe inv.Stoc!', 16, 1)
3743 RETURN -1
3744 END
3745
3746 IF ( ISNULL(@CantReturDeFacut,0) > 0 )
3747 BEGIN
3748
3749 BEGIN TRAN
3750 IF @ContineDetalii = 1
3751 BEGIN
3752 INSERT inv.IesPoz
3753 (
3754 ParentIesPozId
3755 , IesCapId
3756 , IesPozRefId
3757 , FirmaId
3758 , DivizieId
3759 , ProdusId
3760 , CantIesire
3761 , PretVanzare
3762 , PretVanzareRedus
3763 , Tva
3764 , PretAmanunt
3765 , PretAmanuntRedus
3766 , CantFacturata
3767 , IntrPozId
3768 , FurnizorID
3769 , FurnizorOriginalID
3770 , NumarIntr
3771 , DataIntr
3772 , DocIntrId
3773 , TipDocINtrID
3774 , NumarFactIntr
3775 , DataFactIntr
3776 , PretIntrare
3777 , PretIntrareRedus
3778 , PretCost
3779 , GestiuneId
3780 , Amanunt
3781 , LotIntrare
3782 , DataExpirare
3783 , DataFabricare
3784 , SerieIntrare
3785 , IntrPozOrigId
3786 , POrderPozId
3787 , ValoareRedusaFaraTva
3788 , ValoareRedusaTva
3789 , ValoareFaraTva
3790 , ValoareTva
3791 )
3792 SELECT
3793 @IesPozId
3794 , @IesCapId
3795 , IesPozId
3796 , FirmaId
3797 , DivizieId
3798 , ProdusId
3799 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -CantIesire ELSE ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) END
3800 , PretVanzare
3801 , PretVanzareRedus
3802 , Tva
3803 , PretAmanunt
3804 , PretAmanuntRedus
3805 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -CantIesire ELSE ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) END
3806 , IntrPozId
3807 , FurnizorID
3808 , FurnizorOriginalID
3809 , NumarIntr
3810 , DataIntr
3811 , DocIntrId
3812 , TipDocINtrID
3813 , NumarFactIntr
3814 , DataFactIntr
3815 , PretIntrare
3816 , PretIntrareRedus
3817 , PretCost
3818 , GestiuneId
3819 , Amanunt
3820 , LotIntrare
3821 , DataExpirare
3822 , DataFabricare
3823 , SerieIntrare
3824 , IntrPozOrigId
3825 , POrderPozId
3826 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -ValoareRedusaFaraTva ELSE ROUND(ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) * PretVanzareRedus,2) END
3827 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -ValoareRedusaTva ELSE ROUND(ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) * PretVanzareRedus * Tva/100.00,2) END
3828 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -ValoareFaraTva ELSE ROUND(ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) * PretVanzare,@setare351) END
3829 , CASE WHEN @CantReturDeFacut = @CantitatePF THEN -ValoareTva ELSE ROUND(ROUND(-@CantReturDeFacut * CantIesire / @CantitatePF,@setare623) * PretVanzare * Tva/100.00,@setare351) END
3830 FROM inv.IesPoz (NOLOCK)
3831 WHERE ParentIesPozId = @IesPozIdRef
3832
3833 UPDATE inv.IesPoz
3834 SET ContineDetalii = 1
3835 WHERE IesPozId = @IesPozId
3836
3837 UPDATE inv.IesPoz
3838 SET CantRetur = ISNULL(CantRetur,0) + ISNULL(@CantReturDeFacut,0)
3839 WHERE IesPozId = @IesPozIdRef
3840
3841 INSERT INTO #TempSyncRetur
3842 (
3843 IesCapId
3844 ,IesPozId
3845 ,IesPozRefDetId
3846 ,CantRetur
3847 ,ProdusId
3848 ,Prelucrat
3849 ,ParentIesPozId
3850 )
3851 SELECT
3852 IesCapId
3853 , IesPozId
3854 , IesPozRefId
3855 , CantRetur
3856 , ProdusId
3857 , 0
3858 , ParentIesPozId
3859 FROM inv.IesPoz
3860 WHERE ParentIesPozId = @IesPozIdRef
3861
3862 END
3863
3864 ELSE
3865 BEGIN
3866
3867 -- daca am pe stoc "linia" din care s-a scazut bonul original, ii fac update; altfel , fac insert in stoc
3868 IF EXISTS
3869 ( SELECT TOP 1
3870 IntrPozId
3871 FROM inv.Stoc (NOLOCK)
3872 WHERE IntrPozId = @IntrPozId
3873 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'') -- ISNULL
3874 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
3875 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
3876 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
3877 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
3878 AND FirmaID = @FirmaID
3879 AND DivizieID = @DivizieID
3880 )
3881 BEGIN
3882
3883 PRINT '@Update stoc:'
3884
3885 UPDATE inv.Stoc
3886 SET Stoc = Stoc + @CantReturDeFacut
3887 WHERE IntrPozId = @IntrPozId
3888 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'') -- ISNULL
3889 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
3890 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
3891 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
3892 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
3893 AND FirmaID = @FirmaID
3894 AND DivizieID = @DivizieID
3895 AND ClientCustodieId IS NULL
3896
3897 END
3898
3899 ELSE -- nu am in stoc pozitia la care am facut retur
3900
3901 BEGIN
3902
3903 INSERT INTO inv.Stoc
3904 (
3905 IntrPozId
3906 ,FurnizorId
3907 ,FurnizorOriginalId
3908 ,NumarIntr
3909 ,DataIntr
3910 ,DocIntrId
3911 ,TipDocIntrId
3912 ,DataScadenta
3913 ,NumarFactIntr
3914 ,DataFactIntr
3915 ,FirmaId
3916 ,DivizieId
3917 ,IntrPozOrigId
3918 ,IntrPozRefId
3919 ,ProdusId
3920 ,Stoc
3921 ,PretIntrare
3922 ,PretIntrareRedus
3923 ,PretCost
3924 ,Tva
3925 ,PretAmanunt
3926 ,PretVanzare
3927 ,GestiuneId
3928 ,LotIntrare
3929 ,DataExpirare
3930 ,datafabricare
3931 ,SerieIntrare
3932 ,AssetId
3933 ,ContractFurnizorPozId
3934 ,POrderPozId
3935 ,ValutaCmdFurnId
3936 ,PretCmdFurn
3937 ,PretCtrFurn
3938 ,ValutaCtrFurnId
3939 )
3940 SELECT
3941 p.IntrPozId
3942 , p.FurnizorId
3943 , p.FurnizorOriginalId
3944 , p.NumarIntr
3945 , p.DataIntr
3946 , p.DocIntrId
3947 , p.TipDocIntrId
3948 , p.DataScadentaIntr
3949 , p.NumarFactIntr
3950 , p.DataFactIntr
3951 , p.FirmaId
3952 , p.DivizieId
3953 , p.IntrPozOrigId
3954 , p.IntrPozId AS IntrPozRefId
3955 , p.ProdusId
3956 , ISNULL(@CantReturDeFacut,0)
3957 , p.PretIntrare
3958 , p.PretIntrareRedus
3959 , p.PretCost
3960 , p.Tva
3961 , p.PretAmanunt
3962 , p.PretVanzare
3963 , p.GestiuneId
3964 , p.LotIntrare
3965 , p.DataExpirare
3966 , p.dataFabricare
3967 , p.SerieIntrare
3968 , p.AssetId
3969 , p.ContractFurnizorPozId
3970 , p.POrderPozId
3971 , p.ValutaCmdFurnId
3972 , p.PretCmdFurn
3973 , p.PretCtrFurn
3974 , p.ValutaCtrFurnId
3975 FROM inv.IesPoz p ( NOLOCK )
3976 WHERE p.iesPozId = @IesPozIdRef
3977 --p.IntrPozId = @IntrPozId
3978 -- AND ISNULL(p.DataExpirare, '') = ISNULL(@DataExpirare, '') -- ISNULL
3979 -- AND ISNULL(p.GestiuneId, 0) = ISNULL(@GestiuneId, 0)
3980 -- AND ISNULL(p.DataFabricare, '') = ISNULL(@DataFabricare, '')
3981 -- AND ISNULL(p.SerieIntrare, '') = ISNULL(@SerieIntrare, '')
3982 -- AND ISNULL(p.LotIntrare, '') = ISNULL(@LotIntrare, '')
3983 -- AND p.FirmaID = @FirmaID
3984 -- AND p.DivizieID = @DivizieID
3985
3986 END --de la ELSE
3987
3988 UPDATE inv.IesPoz
3989 SET CantRetur = ISNULL(CantRetur,0) + ISNULL(@CantReturDeFacut,0)
3990 WHERE IesPozId = @IesPozIdRef
3991
3992 UPDATE inv.IesPoz
3993 SET
3994 IesPozRefId = @IesPozIdRef
3995 , IntrPozOrigId = @IntrPozOrigId
3996 , IntrPozId = @IntrPozId
3997 , DataExpirare = @DataExpirare
3998 , GestiuneId = @GestiuneId
3999 , Amanunt = @Amanunt
4000 , DataFabricare = @DataFabricare
4001 , SerieIntrare = @SerieIntrare
4002 , LotIntrare = @LotIntrare
4003 , FurnizorID = @FurnizorID
4004 , FurnizorOriginalID = @FurnizorOriginalID
4005 , NumarIntr = @NumarIntr
4006 , DataIntr = @DataIntr
4007 , DocIntrId = @DocIntrId
4008 , TipDocINtrID = @TipDocINtrID
4009 , NumarFactIntr = @NumarFactIntr
4010 , DataFactIntr = @DataFactIntr
4011 , PretIntrare = @PretIntrare
4012 , PretIntrareRedus = @PretIntrareRedus
4013 , PretCost = @PretCost
4014 , CantIesire = ( -1 ) * @CantReturDeFacut
4015 , ValoareRedusaFaraTva=ROUND((-1) * @CantReturDeFacut * isnull(PretAmanuntRedus,PretAmanunt) - ROUND((((-1) * @CantReturDeFacut * isnull(PretAmanuntRedus,PretAmanunt)) * Tva / (100.00 + Tva)), 6), 6)
4016 --round(( -1 ) * @CantReturDeFacut*isnull(PretVanzareRedus,PretVanzare),6)
4017 , ValoareRedusaTva=ROUND((((-1)* @CantReturDeFacut * isnull(PretAmanuntRedus,PretAmanunt) * Tva) / (100.00 + Tva)), 6)
4018 --round(( -1 ) * @CantReturDeFacut*isnull(PretVanzareRedus,PretVanzare)*tva/100.00,6)
4019 , ValoareFaraTva=ROUND((-1) * @CantReturDeFacut * PretAmanunt - ROUND(((-1) * @CantReturDeFacut * PretAmanunt * Tva) / (100.00 + Tva), 6), 6)
4020 --round(( -1 ) * @CantReturDeFacut*PretVanzare,6)
4021 , ValoareTva= ROUND(((-1) * @CantReturDeFacut * PretAmanunt * Tva) / (100.00 + Tva), 6)
4022 --round(( -1 ) * @CantReturDeFacut*PretVanzare*tva/100.00,6)
4023 WHERE IesPozId = @IesPozId
4024 AND FirmaID = @FirmaID
4025 AND DivizieID = @DivizieId
4026 AND IesCapId = @IesCapId
4027
4028
4029 IF ( ISNULL(( -1 ) * @CantRetur,0) <> @CantReturDeFacut )
4030 BEGIN
4031
4032 INSERT INTO inv.IesPoz
4033 (
4034 IesCapId
4035 ,FirmaID
4036 ,DivizieId
4037 ,ProdusID
4038 ,CantIesire
4039 ,CantFacturata
4040 ,PretVanzare
4041 ,PretVanzareRedus
4042 ,Tva
4043 ,PretAmanunt
4044 ,PretAmanuntRedus
4045 ,RetailPozIesireID
4046 ,RetailPozIesireReturId
4047 ,TaxCode
4048 ,ValoareRedusaFaraTva
4049 ,ValoareRedusaTva
4050 ,ValoareFaraTva
4051 ,ValoareTva
4052 --,Amanunt
4053 )
4054 SELECT
4055 IesCapId
4056 , FirmaID
4057 , DivizieId
4058 , ProdusID
4059 , @CantRetur + @CantReturDeFacut
4060 , @CantRetur + @CantReturDeFacut
4061 , PretVanzare
4062 , PretVanzareRedus
4063 , Tva
4064 , PretAmanunt
4065 , PretAmanuntRedus
4066 , RetailPozIesireID
4067 , RetailPozIesireReturId
4068 , TaxCode
4069 , ROUND((@CantRetur + @CantReturDeFacut) * isnull(PretAmanuntRedus,PretAmanunt) - ROUND((((@CantRetur + @CantReturDeFacut) * isnull(PretAmanuntRedus, PretAmanunt) * Tva) / (100.00 + Tva)), 6), 6)
4070 --round(( @CantRetur + @CantReturDeFacut)*isnull(PretVanzareRedus,PretVanzare),6)
4071 , ROUND((((@CantRetur + @CantReturDeFacut) * isnull(PretAmanuntRedus,PretAmanunt) * Tva) / (100.00 + Tva)), 6)
4072 --round(( @CantRetur + @CantReturDeFacut)*isnull(PretVanzareRedus,PretVanzare)*tva/100.00,6)
4073 , ROUND((@CantRetur + @CantReturDeFacut) * PretAmanunt - ROUND(((@CantRetur + @CantReturDeFacut) * PretAmanunt * Tva) / (100.00 + Tva), 6), 6)
4074 --round(( @CantRetur + @CantReturDeFacut)*PretVanzare,6)
4075 , ROUND(((@CantRetur + @CantReturDeFacut) * PretAmanunt * Tva) / (100.00 + Tva), 6)
4076 --round(( @CantRetur + @CantReturDeFacut)*PretVanzare*tva/100.00,6)
4077
4078 --, 1
4079 FROM inv.IesPoz (NOLOCK)
4080 WHERE IesPozId = @IesPozID
4081 AND FirmaID = @FirmaID
4082 AND DivizieID = @DivizieID
4083
4084 DECLARE @IesPozIdReturInserat INT
4085
4086 SET @IesPozIdReturInserat = SCOPE_IDENTITY()
4087
4088 PRINT '@IesPozIdInserat inserat pentru spargere:' + CAST(@IesPozIdReturInserat AS NVARCHAR)
4089
4090 INSERT INTO #TempSyncRetur
4091 (
4092 IesCapId
4093 ,IesPozId
4094 ,CantRetur
4095 ,RetailPozIesireReturId
4096 ,Prelucrat
4097 )
4098 SELECT
4099 cap.IesCapId
4100 , poz.IesPozId
4101 , poz.CantIesire
4102 , RetailPozIesireReturId
4103 , 0
4104 FROM inv.IesPoz poz ( NOLOCK )
4105 INNER JOIN inv.Iescap cap ( NOLOCK )
4106 ON cap.IesCapId = poz.IesCapId
4107 WHERE cap.FirmaId = @FirmaID
4108 AND cap.DivizieId = @DivizieId
4109 AND poz.IntrPozId IS NULL
4110 AND ((cap.DocId = 13 AND cap.TipDocId = @ConfigTipDocBonConsum AND @TipDocBCStoc = 1)
4111 OR (cap.Docid = 21 AND cap.TipDocId = @ConfigTipDocIdBonCuStoc AND @TipDocStoc = 1))
4112 AND cap.RetailCapIesireID IS NOT NULL
4113 AND poz.RetailPozIesireID IS NOT NULL
4114 AND poz.IesPozId = @IesPozIdReturInserat
4115 ORDER BY cap.DataIes
4116
4117 --DECLARE @TempSyncIdInserat INT
4118 --SET @TempSyncIdInserat = SCOPE_IDENTITY()
4119 --PRINT '@TempSyncIdInserat inserat pentru spargere:' + CAST(@TempSyncIdInserat AS NVARCHAR(20))
4120
4121
4122 END
4123 END
4124 IF XACT_STATE() = 1
4125 COMMIT TRANSACTION
4126
4127 END
4128
4129 UPDATE #TempSyncRetur
4130 SET Prelucrat = 1
4131 WHERE TempSyncId = @TempSyncId
4132
4133 PRINT '@TempSyncId prelucrat:' + CAST(@TempSyncId AS NVARCHAR)
4134
4135 END--while
4136
4137 IF EXISTS (SELECT TOP 1 * FROM #TempSyncRetur WHERE ParentIesPozId IS NOT NULL)
4138 BEGIN
4139
4140 UPDATE poz
4141 SET poz.PretCost = ROUND(ISNULL(det.ValoareCost,0) / ISNULL(NULLIF(poz.CantIesire,0),1),@NrZecimalePretIntrare)
4142 FROM inv.IesPoz poz
4143 INNER JOIN (SELECT p.ParentIesPozId
4144 , SUM(CASE WHEN prod.TipItem = 'P' THEN p.CantIesire * ISNULL(p.PretCost,0) ELSE 0 END) AS ValoareCost
4145 FROM inv.IesPoz p (NOLOCK)
4146 INNER JOIN dbo.tblProduse prod (NOLOCK)
4147 ON prod.ProdusId = p.ProdusId
4148 INNER JOIN (SELECT temp.ParentIesPozId as IesPozId
4149 FROM #TempSyncRetur temp
4150 GROUP BY temp.ParentIesPozId) temp
4151 ON temp.IesPozId = p.ParentIesPozId
4152 GROUP BY p.ParentIesPozId) det
4153 on poz.IesPozId = det.ParentIesPozId
4154 END
4155
4156 if @Debug=1 /*d*/
4157 BEGIN
4158 SELECT 'End WHILE Bonuri retur'
4159 END
4160
4161 --SELECT 'Pozitii retur nesincronizate:'
4162 --SELECT *
4163 --FROM inv.IesPoz poz (NOLOCK)
4164 --JOIN inv.IesCap cap (NOLOCK)
4165 -- ON poz.IesCapId = cap.IesCapId
4166 --WHERE cap.FirmaId = @FirmaId
4167 -- AND cap.DivizieId = @DivizieId
4168 -- AND cap.DocId = @DocIdBon
4169 -- AND poz.IesPozRefId IS NULL
4170 -- AND cap.CodCalc IS NOT NULL
4171 -- AND cap.Retur = 1
4172 -- AND cap.RetailCapIesireId IS NOT NULL
4173 -- AND poz.RetailPozIesireId IS NOT NULL
4174
4175
4176 --SELECT 'Pozitii sincronizate:'
4177
4178 --SELECT *
4179 --FROM inv.IesPoz poz (NOLOCK)
4180 --JOIN inv.IesCap cap (NOLOCK)
4181 -- ON poz.IesCapId = cap.IesCapId
4182 --JOIN inv.Stoc
4183 -- ON stoc.IntrPozId = poz.IntrPozId
4184 -- AND ISNULL(Stoc.DataExpirare, '') = ISNULL(poz.DataExpirare, '') -- ISNULL
4185 -- AND ISNULL(Stoc.GestiuneId, 0) = ISNULL(poz.GestiuneId, 0)
4186 -- AND ISNULL(Stoc.DataFabricare, '') = ISNULL(poz.DataFabricare, '')
4187 -- AND ISNULL(Stoc.SerieIntrare, '') = ISNULL(poz.SerieIntrare, '')
4188 -- AND ISNULL(Stoc.LotIntrare, '') = ISNULL(poz.LotIntrare, '')
4189 --WHERE cap.FirmaId = @FirmaId
4190 -- AND cap.DivizieId = @DivizieId
4191 -- AND cap.DocId = @DocIdBon
4192 -- AND poz.IntrPozId IS NOT NULL
4193 -- AND cap.CodCalc IS NOT NULL
4194 -- AND cap.RetailCapIesireId IS NOT NULL
4195 -- AND poz.RetailPozIesireId IS NOT NULL
4196 -- AND ISNULL(cap.Retur, 0) <> 1
4197 --ORDER BY cap.IesCapID
4198 -- , poz.IesPozId
4199
4200 --SELECT 'Pozitii retur sincronizate:'
4201 --SELECT *
4202 --FROM inv.IesPoz poz (NOLOCK)
4203 --JOIN inv.IesCap cap (NOLOCK)
4204 -- ON poz.IesCapId = cap.IesCapId
4205 --LEFT JOIN inv.Stoc
4206 -- ON stoc.IntrPozId = poz.IntrPozId
4207 -- AND ISNULL(Stoc.DataExpirare, '') = ISNULL(poz.DataExpirare, '') -- ISNULL
4208 -- AND ISNULL(Stoc.GestiuneId, 0) = ISNULL(poz.GestiuneId, 0)
4209 -- AND ISNULL(Stoc.DataFabricare, '') = ISNULL(poz.DataFabricare, '')
4210 -- AND ISNULL(Stoc.SerieIntrare, '') = ISNULL(poz.SerieIntrare, '')
4211 -- AND ISNULL(Stoc.LotIntrare, '') = ISNULL(poz.LotIntrare, '')
4212 --WHERE cap.FirmaId = @FirmaId
4213 -- AND cap.DivizieId = @DivizieId
4214 -- AND cap.DocId = @DocIdBon
4215 -- AND poz.IesPozRefId IS NOT NULL
4216 -- AND cap.CodCalc IS NOT NULL
4217 -- AND cap.Retur = 1
4218 -- AND cap.RetailCapIesireId IS NOT NULL
4219 -- AND poz.RetailPozIesireId IS NOT NULL
4220
4221
4222
4223 PRINT 'Bonuri retur special'
4224 --bonuri de retur fara bifa @retur si referinta la iesire
4225
4226 IF OBJECT_ID('tempdb..#TempSyncReturSpecial') IS NOT NULL
4227 DROP TABLE #TempSyncReturSpecial
4228
4229 CREATE TABLE #TempSyncReturSpecial
4230 (
4231 TempSyncId INT IDENTITY(1,1)
4232 ,IesCapId INT
4233 ,IesPozId INT
4234 ,CantRetur DECIMAL(18,5)
4235 ,Prelucrat BIT
4236 ,ProdusId INT
4237 )
4238
4239 INSERT INTO #TempSyncReturSpecial
4240 (
4241 IesCapId
4242 ,IesPozId
4243 ,CantRetur
4244 ,Prelucrat
4245 ,ProdusId
4246 )
4247 SELECT
4248 cap.IesCapId
4249 , poz.IesPozId
4250 , poz.CantIesire
4251 , 0
4252 , poz.ProdusId
4253 FROM inv.IesPoz poz ( NOLOCK )
4254 INNER JOIN inv.Iescap cap ( NOLOCK )
4255 ON cap.IesCapId = poz.IesCapId
4256 LEFT JOIN dbo.tblProduse p ( NOLOCK )
4257 ON p.ProdusId = poz.ProdusId
4258 WHERE cap.FirmaId = @FirmaID
4259 AND cap.DivizieId = @DivizieId
4260 AND cap.DataAnulare IS NULL
4261 AND poz.IntrPozId IS NULL
4262 AND ((cap.DocId = 13 AND cap.TipDocId = @ConfigTipDocBonConsum AND @TipDocBCStoc = 1)
4263 OR (cap.Docid = 21 AND cap.TipDocId = @ConfigTipDocIdBonCuStoc AND @TipDocStoc = 1))
4264 AND cap.RetailCapIesireID IS NOT NULL
4265 AND poz.RetailPozIesireID IS NOT NULL
4266 AND poz.RetailPozIesireReturID IS NULL --nu am legatura cu pozitia de iesire
4267 AND poz.CantIesire < 0
4268 AND ISNULL(p.TipItem,'P') <> 'S'
4269 --AND cap.IesCapFacturaId IS NULL--fara bonurile din factura
4270 AND cap.DocGeneratorId IS NULL
4271 AND cap.CapDocGeneratorId IS NULL
4272 --AND @Descarcare = 1
4273 --AND @GestiuniRetail IS NOT NULL
4274 ORDER BY cap.DataIes
4275
4276 if @Debug=1 /*d*/
4277 BEGIN
4278 SELECT '#TempSyncReturSpecial:'
4279 SELECT * FROM #TempSyncReturSpecial
4280 SELECT 'Begin WHILE Bonuri retur special'
4281 END
4282
4283 WHILE EXISTS
4284 ( SELECT TOP 1
4285 *
4286 FROM #TempSyncReturSpecial
4287 WHERE ISNULL(Prelucrat,0) = 0
4288 )
4289 BEGIN
4290
4291 SELECT TOP 1
4292 @TempSyncId = TempSyncId
4293 , @IesCapId = IesCapId
4294 , @IesPozId = IesPozId
4295 , @CantRetur = CantRetur
4296 , @ProdusID = ProdusId
4297 FROM #TempSyncReturSpecial
4298 WHERE ISNULL(Prelucrat,0) = 0
4299
4300 PRINT '@TempSyncIdSpecial:' + CAST(@TempSyncId AS NVARCHAR)
4301 PRINT '@IesCapIdSpecial:' + CAST(@IesCapId AS NVARCHAR)
4302 PRINT '@IesPozIdSpecial:' + CAST(@IesPozId AS NVARCHAR)
4303 PRINT '@CantReturSpecial:' + CAST(@CantRetur AS NVARCHAR)
4304
4305 SET @FurnizorID = NULL
4306 SET @FurnizorOriginalID = NULL
4307 SET @NumarIntr = NULL
4308 SET @DataIntr = NULL
4309 SET @DocIntrId = NULL
4310 SET @TipDocINtrID = NULL
4311 SET @DataScadenta = NULL
4312 SET @NumarFactIntr = NULL
4313 SET @DataFactIntr = NULL
4314 SET @PretIntrare = NULL
4315 SET @PretIntrareRedus = NULL
4316 SET @PretCost = NULL
4317 SET @GestiuneId = NULL
4318 SET @Amanunt = NULL
4319 SET @LotIntrare = NULL
4320 SET @DataExpirare = NULL
4321 SET @DataFabricare = NULL
4322 SET @SerieIntrare = NULL
4323 SET @IntrPozOrigId = NULL
4324 SET @POOrderPozId = NULL
4325 SET @Stoc = NULL
4326 SET @IntrPozId = NULL
4327
4328 BEGIN TRAN
4329
4330 --iau prima pozitie din stoc din gestiunile de retail cu @produsid
4331 SELECT TOP 1
4332 @FurnizorID = stoc.FurnizorID
4333 , @FurnizorOriginalID = stoc.FurnizorOriginalID
4334 , @NumarIntr = stoc.NumarIntr
4335 , @DataIntr = stoc.DataIntr
4336 , @DocIntrId = stoc.DocIntrId
4337 , @TipDocINtrID = stoc.TipDocINtrID
4338 , @DataScadenta = stoc.DataScadenta
4339 , @NumarFactIntr = stoc.NumarFactIntr
4340 , @DataFactIntr = stoc.DataFactIntr
4341 , @PretIntrare = stoc.PretIntrare
4342 , @PretIntrareRedus = stoc.PretIntrareRedus
4343 , @PretCost = stoc.PretCost
4344 , @GestiuneId = stoc.GestiuneId
4345 , @Amanunt = ISNULL(g.Amanunt,0)
4346 , @LotIntrare = stoc.LotIntrare
4347 , @DataExpirare = stoc.DataExpirare
4348 , @DataFabricare = stoc.DataFabricare
4349 , @SerieIntrare = stoc.SerieIntrare
4350 , @IntrPozOrigId = stoc.IntrPozOrigId
4351 , @POOrderPozId = stoc.POrderPozId
4352 , @Stoc = stoc.Stoc
4353 , @IntrPozId = stoc.IntrPozId
4354 , @ProdusID = stoc.ProdusId
4355 FROM inv.Stoc stoc WITH ( ROWLOCK,UPDLOCK )
4356 INNER JOIN dbo.tblGestiuni G WITH ( NOLOCK )
4357 ON G.GestiuneId = stoc.GestiuneID
4358 --AND G.FirmaID = @FirmaID
4359 --AND G.DivizieId = @DivizieID
4360 INNER JOIN
4361 (
4362 SELECT
4363 val
4364 FROM dbo.SplitString(@ConfigGestiuni,',')
4365 WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
4366 ) CodGestSetare1
4367 ON G.CodGestiune = CodGestSetare1.val
4368 INNER JOIN inv.IesPoz poz ( NOLOCK )
4369 ON poz.ProdusId = stoc.ProdusId
4370 WHERE stoc.FirmaId = @FirmaId
4371 AND stoc.DivizieId = @DivizieId
4372 AND poz.IesPozId = @IesPozId
4373 AND stoc.ProdusId = @ProdusID
4374 ORDER BY stoc.DataIntr
4375 --ORDER BY DataExpirare
4376
4377 IF ( @IntrPozId IS NULL )
4378 BEGIN
4379
4380 --nu am inregistrare in stoc, caut ultima intrare pe firma,divizie a produsului
4381
4382 SELECT TOP 1 @IntrPozId = IntrPozId
4383 FROM inv.IntrPoz poz ( NOLOCK )
4384 INNER JOIN dbo.tblGestiuni G ( NOLOCK )
4385 ON G.GestiuneId = poz.GestiuneID
4386 INNER JOIN inv.IntrCap cap ( NOLOCK )
4387 ON cap.IntrCapId = poz.IntrCapId
4388 AND cap.DataAnulare IS NULL
4389 AND Cap.DataValidare IS NOT NULL
4390 INNER JOIN
4391 (
4392 SELECT
4393 val
4394 FROM dbo.SplitString(@ConfigGestiuni,',')
4395 WHERE LTRIM(RTRIM(ISNULL(val,''))) <> ''
4396 ) CodGestSetare1
4397 ON G.CodGestiune = CodGestSetare1.val
4398 WHERE ProdusId = @ProdusID
4399 AND poz.FirmaID = @FirmaID
4400 AND poz.DivizieId = @DivizieID
4401 AND poz.IntrPozAvizId IS NULL
4402 AND poz.IntrPozRefId IS NULL
4403 ORDER BY ISNULL(poz.DataIntr,cap.DataIntr) DESC
4404
4405 IF ( @IntrPozId IS NOT NULL )
4406 BEGIN
4407 -- am intrare pt produs
4408 -- insert in stoc
4409 SELECT TOP 1
4410 @FurnizorOriginalID = intr.FurnizorOriginalID
4411 , @FurnizorID = ISNULL(intr.FurnizorPozId,cap.FurnizorId)
4412 , @NumarIntr = ISNULL(intr.NumarIntr,cap.NumarIntr)
4413 , @DataIntr = ISNULL(intr.DataIntr,cap.DataIntr)
4414 , @NumarFactIntr = ISNULL(intr.NumarFactIntr,cap.NumarFactIntr)
4415 , @DataFactIntr = ISNULL(intr.DataFactIntr,cap.DataFactIntr)
4416 , @PretIntrare = intr.PretIntrare
4417 , @PretIntrareRedus = intr.PretIntrareRedus
4418 , @PretCost = intr.PretCost
4419 , @GestiuneId = intr.GestiuneId
4420 , @LotIntrare = intr.LotIntrare
4421 , @DataExpirare = intr.DataExpirare
4422 , @DataFabricare = intr.DataFabricare
4423 , @SerieIntrare = intr.SerieIntrare
4424 , @IntrPozOrigId = intr.IntrPozOrigId
4425 , @POOrderPozId = intr.POrderPozId
4426 , @DocIntrId = cap.DocId
4427 FROM inv.IntrPoz intr ( NOLOCK )
4428 INNER JOIN inv.IntrCap cap ( NOLOCK )
4429 ON intr.IntrCapId = cap.IntrCapId
4430 WHERE IntrPozId = @IntrPozId
4431
4432 INSERT INTO inv.Stoc
4433 (
4434 FurnizorId
4435 ,FurnizorOriginalID
4436 ,NumarIntr
4437 ,DataIntr
4438 ,NumarFactIntr
4439 ,DataFactIntr
4440 ,PretIntrare
4441 ,PretIntrareRedus
4442 ,PretCost
4443 ,GestiuneId
4444 ,LotIntrare
4445 ,DataExpirare
4446 ,SerieIntrare
4447 ,IntrPozOrigId
4448 ,POrderPozId
4449 ,DocIntrId
4450 ,Stoc
4451 ,ProdusId
4452 ,IntrPozId
4453 ,FirmaId
4454 ,DivizieId
4455 )
4456 VALUES
4457 (
4458 @FurnizorId
4459 ,@FurnizorOriginalID
4460 ,@NumarIntr
4461 ,@DataIntr
4462 ,@NumarFactIntr
4463 ,@DataFactIntr
4464 ,@PretIntrare
4465 ,@PretIntrareRedus
4466 ,@PretCost
4467 ,@GestiuneId
4468 ,@LotIntrare
4469 ,@DataExpirare
4470 ,@SerieIntrare
4471 ,@IntrPozOrigId
4472 ,@POOrderPozId
4473 ,@DocIntrId
4474 ,( -1 ) * @CantRetur
4475 ,@ProdusID
4476 ,@IntrPozId
4477 ,@FirmaId
4478 ,@DivizieID
4479
4480 )
4481
4482 --update pe inv.IesPoz
4483 UPDATE inv.IesPoz
4484 SET
4485 IesPozRefId = @IesPozIdRef
4486 , IntrPozOrigId = @IntrPozOrigId
4487 , IntrPozId = @IntrPozId
4488 , DataExpirare = @DataExpirare
4489 , GestiuneId = @GestiuneId
4490 , Amanunt = @Amanunt
4491 , DataFabricare = @DataFabricare
4492 , SerieIntrare = @SerieIntrare
4493 , LotIntrare = @LotIntrare
4494 , FurnizorID = @FurnizorID
4495 , FurnizorOriginalID = @FurnizorOriginalID
4496 , NumarIntr = @NumarIntr
4497 , DataIntr = @DataIntr
4498 , DocIntrId = @DocIntrId
4499 , TipDocINtrID = @TipDocINtrID
4500 , NumarFactIntr = @NumarFactIntr
4501 , DataFactIntr = @DataFactIntr
4502 , PretIntrare = @PretIntrare
4503 , PretIntrareRedus = @PretIntrareRedus
4504 , PretCost = @PretCost
4505 WHERE IesPozId = @IesPozId
4506 AND FirmaID = @FirmaID
4507 AND DivizieID = @DivizieId
4508 AND IesCapId = @IesCapId
4509 END
4510
4511 ELSE
4512
4513 BEGIN
4514 PRINT 'Nu exista inregistrari in stoc sau intrari pentru produsul cu ID:' + CAST(@ProdusID AS VARCHAR)
4515 END
4516 END
4517
4518 ELSE
4519
4520 BEGIN
4521 --am inregistrare in stoc
4522 PRINT '@Stoc:' + CAST(@Stoc AS NVARCHAR) + '- @CantitateReturSpecial:' + CAST(@CantRetur AS NVARCHAR)
4523
4524 UPDATE inv.IesPoz
4525 SET
4526 IntrPozId = @IntrPozId
4527 , FurnizorID = @FurnizorID
4528 , FurnizorOriginalID = @FurnizorOriginalID
4529 , NumarIntr = @NumarIntr
4530 , DataIntr = @DataIntr
4531 , DocIntrId = @DocIntrId
4532 , TipDocINtrID = @TipDocINtrID
4533 , NumarFactIntr = @NumarFactIntr
4534 , DataFactIntr = @DataFactIntr
4535 , PretIntrare = @PretIntrare
4536 , PretIntrareRedus = @PretIntrareRedus
4537 , PretCost = @PretCost
4538 , GestiuneId = @GestiuneId
4539 , Amanunt = @Amanunt
4540 , LotIntrare = @LotIntrare
4541 , DataExpirare = @DataExpirare
4542 , DataFabricare = @DataFabricare
4543 , SerieIntrare = @SerieIntrare
4544 , IntrPozOrigId = @IntrPozOrigId
4545 , POrderPozId = @POOrderPozId
4546 , ValoareRedusaFaraTva=ROUND(cantiesire*isnull(PretAmanuntRedus,PretAmanunt) - ROUND(((CantIesire*isnull(PretAmanuntRedus,PretAmanunt) * Tva) / (100.00 + Tva)), 6), 6)
4547 --round(cantiesire*isnull(PretVanzareRedus,PretVanzare),6)
4548 , ValoareRedusaTva=ROUND(((CantIesire*isnull(PretAmanuntRedus,PretAmanunt) * Tva) / (100.00 + Tva)), 6)
4549 --round(cantiesire*isnull(PretVanzareRedus,PretVanzare)*tva/100.00,6)
4550 , ValoareFaraTva=ROUND(CantIesire*PretAmanunt - ROUND((Cantiesire*PretAmanunt * Tva) / (100.00 + Tva), 6), 6)
4551 --round(cantiesire*PretVanzare,6)
4552 , ValoareTva=ROUND((Cantiesire*PretAmanunt * Tva) / (100.00 + Tva), 6)
4553 --round(cantiesire*PretVanzare*tva/100.00,6)
4554 WHERE IesPozId = @IesPozId
4555 AND FirmaID = @FirmaID
4556 AND DivizieId = @DivizieID
4557
4558 UPDATE inv.Stoc
4559 SET Stoc = Stoc - @CantRetur-- ( cantitate negativa)
4560 WHERE IntrPozId = @IntrPozId
4561 AND ISNULL(DataExpirare,'') = ISNULL(@DataExpirare,'')
4562 AND ISNULL(GestiuneId,0) = ISNULL(@GestiuneId,0)
4563 AND ISNULL(DataFabricare,'') = ISNULL(@DataFabricare,'')
4564 AND ISNULL(SerieIntrare,'') = ISNULL(@SerieIntrare,'')
4565 AND ISNULL(LotIntrare,'') = ISNULL(@LotIntrare,'')
4566 AND FirmaID = @FirmaID
4567 AND DivizieID = @DivizieID
4568 AND ClientCustodieId IS NULL
4569 END
4570
4571 UPDATE #TempSyncReturSpecial
4572 SET Prelucrat = 1
4573 WHERE TempSyncId = @TempSyncId
4574
4575 PRINT '@TempSyncIdSpecial prelucrat:' + CAST(@TempSyncId AS NVARCHAR)
4576
4577 IF XACT_STATE() = 1
4578 COMMIT TRANSACTION
4579
4580 END --end While
4581
4582 if @Debug=1 /*d*/
4583 BEGIN
4584 SELECT 'End WHILE Bonuri retur special'
4585 PRINT 'Medici/Pacienti'
4586 END
4587
4588 --=================================================== SFARSIT SINCRONIZARE CU STOC ==================================================================================
4589 /*
4590 DECLARE
4591 @MIdentificator VARCHAR(100)
4592 , @MCapIesireId INT
4593 , @MBufferPozitieBonID INT
4594 , @MClientDenumire VARCHAR(4000)
4595 , @MClientTip INT
4596 , @MClientAttrFiscal VARCHAR(100)
4597 , @MClientCNPCUI VARCHAR(100)
4598 , @MClientNrRegCom VARCHAR(100)
4599 , @MClientAdresa VARCHAR(4000)
4600 , @MClientID INT
4601 , @MClientCont VARCHAR(100)
4602 , @MClientBanca VARCHAR(4000)
4603 , @MValoarePlataUlterioara DECIMAL(18,2)
4604 , @MDiscountSuplimentar DECIMAL(18,2)
4605 , @MMotivDiscount VARCHAR(4000)
4606 , @MPacient VARCHAR(4000)
4607 , @MCodMedic VARCHAR(4000)
4608 , @MDenMedic VARCHAR(4000)
4609 , @MDataNastere DATETIME
4610
4611
4612 DECLARE CursorBonuriSpeciale CURSOR FOR
4613 SELECT
4614 Identificator
4615 , CapIesireId
4616 , BufferPozitieBonID
4617 , ClientDenumire
4618 , CASE WHEN ClientTip = 1 THEN 4
4619 ELSE 1
4620 END AS ClientTip
4621 , ClientAttrFiscal
4622 , ClientCNPCUI
4623 , ClientNrRegCom
4624 , ClientAdresa
4625 , ClientID
4626 , ClientCont
4627 , ClientBanca
4628 , ValoarePlataUlterioara
4629 , DiscountSuplimentar
4630 , MotivDiscount
4631 , Pacient
4632 , CodMedic
4633 , DenMedic
4634 , b.DataNastere
4635 FROM Retail.BufferPozitiiBon b ( NOLOCK )
4636 WHERE FirmaId = @FirmaId
4637 AND DivizieId = @DivizieId
4638 AND ISNULL(DocId,@DocIdBon) IN (13,21)
4639 AND ISNULL(Prelucrat2,0) = 0
4640 AND (
4641 CodMedic IS NOT NULL
4642 AND (
4643 ClientId IS NOT NULL
4644 OR ClientDenumire IS NOT NULL
4645 )
4646 )
4647 AND b.IdIesPoz IS NULL--fara bonurile din factura
4648 GROUP BY Identificator
4649 , CapIesireId
4650 , BufferPozitieBonID
4651 , ClientDenumire
4652 , ClientTip
4653 , ClientAttrFiscal
4654 , ClientCNPCUI
4655 , ClientNrRegCom
4656 , ClientAdresa
4657 , ClientID
4658 , ClientCont
4659 , ClientBanca
4660 , ValoarePlataUlterioara
4661 , DiscountSuplimentar
4662 , MotivDiscount
4663 , Pacient
4664 , CodMedic
4665 , DenMedic
4666 , b.DataNastere
4667
4668 OPEN CursorBonuriSpeciale
4669 FETCH NEXT FROM CursorBonuriSpeciale INTO @MIdentificator,@MCapIesireId,@MBufferPozitieBonID,@MClientDenumire,
4670 @MClientTip,@MClientAttrFiscal,@MClientCNPCUI,@MClientNrRegCom,@MClientAdresa,@MClientID,@MClientCont,
4671 @MClientBanca,@MValoarePlataUlterioara,@MDiscountSuplimentar,@MMotivDiscount,@MPacient,@MCodMedic,@MDenMedic,
4672 @MDataNastere
4673
4674 WHILE @@FETCH_STATUS = 0
4675 BEGIN
4676 BEGIN TRY
4677 BEGIN TRAN
4678
4679 DECLARE @TempIesCapId INT
4680
4681 SELECT @TempIesCapId = IesCapId
4682 FROM inv.IesCap c ( NOLOCK )
4683 WHERE RetailCapIesireId = @MCapIesireId
4684 AND c.CodCalc = @MIdentificator
4685 AND FirmaId = @FirmaId
4686 AND DivizieId = @DivizieID
4687 AND DocId IN (13,21)
4688
4689 IF ( @TempIesCapId IS NOT NULL )
4690 BEGIN
4691
4692 IF ( ISNULL(@MClientId,0) = 0 )
4693 BEGIN
4694 PRINT 'CUI: ' + @MClientCNPCUI
4695
4696 DECLARE
4697 @ClCUI VARCHAR(50) = [dbo].[ExtractInteger](@MClientCNPCUI)
4698 , @PartId INT = 0
4699 , @UnitId INT = 0
4700
4701 --daca nu am cui, caut client dupa denumire
4702
4703 IF ( LTRIM(RTRIM(ISNULL(@ClCUI,''))) = '' )
4704 BEGIN
4705
4706 --caut client dupa denumire
4707 SELECT TOP 1 @MClientId = C.ClientId
4708 FROM dbo.tblClienti C
4709 INNER JOIN dbo.vwUnitati U
4710 ON C.ClientId = U.UnitateId
4711 WHERE C.FirmaId = @FirmaId
4712 AND C.DivizieId = @DivizieId
4713 AND U.DenumireUnitate = @MClientDenumire
4714
4715 END
4716
4717 ELSE
4718
4719 BEGIN--am cui si caut client dupa cui
4720
4721 SELECT TOP 1 @MClientId = C.ClientId
4722 FROM dbo.tblClienti C
4723 INNER JOIN dbo.tblUnitate U
4724 ON C.ClientId = U.UnitateID
4725 INNER JOIN dbo.tblPartener P
4726 ON U.PartenerID = P.PartenerID
4727 WHERE C.FirmaId = @FirmaId
4728 AND C.DivizieId = @DivizieId
4729 AND P.CodFiscal = @ClCUI
4730
4731 END
4732
4733 --nu am gasit client dupa cui sau dupa nume, caut partener
4734 IF ( ISNULL(@MClientId,0) = 0 )
4735 BEGIN
4736
4737 --nu am cui, caut partener dupa denumire
4738 IF ( LTRIM(RTRIM(ISNULL(@ClCUI,''))) = '' )
4739 BEGIN
4740
4741 SELECT TOP 1 @PartId = P.PartenerID
4742 FROM dbo.tblPartener P
4743 WHERE P.DenumirePartener = @MClientDenumire
4744
4745 END
4746
4747 ELSE
4748
4749 BEGIN
4750 --caut partener dupa cui
4751
4752 SELECT TOP 1 @PartId = P.PartenerID
4753 FROM dbo.tblPartener P
4754 WHERE P.CodFiscal = @ClCUI
4755
4756 END
4757
4758
4759 --nu am partener, il inserez
4760 IF ( ISNULL(@PartId,0) = 0 )
4761 BEGIN
4762
4763 INSERT dbo.tblPartener
4764 (
4765 DenumirePartener
4766 ,TipFirmaId
4767 ,DataIntroducere
4768 ,CodFiscal
4769 ,NrRegComertului
4770 ,AtributFiscal
4771 ,ContFurn
4772 ,BancaFurn
4773 ,Strada
4774 )
4775 VALUES
4776 (
4777 @MClientDenumire
4778 ,@MClientTip
4779 ,GETDATE()
4780 ,@ClCUI
4781 ,@MClientNrRegCom
4782 ,LTRIM(RTRIM(REPLACE(@MClientCNPCUI,@ClCUI,'')))
4783 ,@MClientCont
4784 ,@MClientBanca
4785 ,@MClientAdresa
4786 )
4787 SELECT @PartId = @@IDENTITY
4788
4789 END
4790
4791 --inserez unitatea
4792 INSERT dbo.tblUnitate
4793 (
4794 PartenerId
4795 ,UnitateDefault
4796 ,DataIntroducere
4797 )
4798 VALUES
4799 (
4800 @PartId
4801 ,0
4802 ,GETDATE()
4803 )
4804
4805 SELECT @UnitId = @@IDENTITY
4806
4807 --inserez in client
4808 INSERT INTO dbo.tblClienti
4809 (
4810 ClientId
4811 ,DivizieId
4812 ,FirmaId
4813 ,Inactiv
4814 ,DataModificare
4815 ,DataIntroducere
4816 )
4817 VALUES
4818 (
4819 @UnitId
4820 ,@DivizieId
4821 ,@FirmaId
4822 ,0
4823 ,GETDATE()
4824 ,GETDATE()
4825 )
4826
4827 SELECT @MClientId = @UnitId
4828
4829 END
4830
4831 END
4832
4833
4834 DECLARE @MedicId INT
4835
4836 SELECT TOP 1 @MedicId = MedicId
4837 FROM tblMedici m ( NOLOCK )
4838 WHERE DivizieId = @DivizieID
4839 AND (
4840 (
4841 LTRIM(RTRIM(ISNULL(@MCodMedic,''))) <> ''
4842 AND m.ParafaMedic = @MCodMedic
4843 )
4844 OR ( LTRIM(RTRIM(m.DenumireMedic)) = LTRIM(RTRIM(@MDenMedic)) )
4845 )
4846
4847 IF ( ISNULL(@MedicId,0) = 0 )
4848 BEGIN
4849 INSERT INTO tblMedici
4850 (
4851 ParafaMedic
4852 ,DenumireMedic
4853 ,DataModificare
4854 ,DivizieId
4855 )
4856 VALUES
4857 (
4858 @MCodMedic
4859 ,@MDenMedic
4860 ,GETDATE()
4861 ,@DivizieID
4862 )
4863 END
4864
4865 IF (
4866 @MClientID IS NOT NULL
4867 OR @MedicId IS NOT NULL
4868 )
4869 BEGIN
4870
4871 UPDATE inv.IesCap
4872 SET ClientId = @MClientID
4873 , MedicId = @MedicId
4874 --, AdresaClient = @MClientAdresa
4875 , ClientAttrFiscal = @MClientAttrFiscal
4876 , ClientBanca = @MClientBanca
4877 , ClientCNPCUI = @MClientCNPCUI
4878 , ClientCont = @MClientCont
4879 , ClientDenumire = @MClientDenumire
4880 , ClientTip = @MClientTip
4881 , ClientNrRegCom = @MClientNrRegCom
4882 , ClientAdresa = @MClientAdresa
4883 , Pacient = @MPacient
4884 , ValoarePlataUlterioara = @MValoarePlataUlterioara
4885 , MotivDiscount = @MMotivDiscount
4886 , DataNastere = @MDataNastere
4887 WHERE IesCapId = @TempIesCapId
4888 AND RetailCapIesireId = @MCapIesireID
4889 AND CodCalc = @MIdentificator
4890 AND FirmaId = @FirmaId
4891 AND DivizieId = @DivizieID
4892
4893 UPDATE Retail.BufferPozitiiBon
4894 SET Prelucrat2 = 1
4895 WHERE BufferPozitieBonID = @MBufferPozitieBonId
4896 AND CapIesireID = @MCapIesireID
4897 AND Identificator = @MIdentificator
4898 AND FirmaId = @FirmaId
4899 AND DivizieID = @DivizieID
4900
4901 END
4902 END
4903 COMMIT TRAN
4904
4905 END TRY
4906 BEGIN CATCH
4907
4908 IF ( XACT_STATE() <> 0 )
4909 ROLLBACK TRANSACTION
4910 END CATCH
4911
4912
4913 FETCH NEXT FROM CursorBonuriSpeciale INTO @MIdentificator,@MCapIesireId,@MBufferPozitieBonID,
4914 @MClientDenumire,@MClientTip,@MClientAttrFiscal,@MClientCNPCUI,@MClientNrRegCom,@MClientAdresa,
4915 @MClientID,@MClientCont,@MClientBanca,@MValoarePlataUlterioara,@MDiscountSuplimentar,@MMotivDiscount,
4916 @MPacient,@MCodMedic,@MDenMedic,@MDataNastere
4917 END--cursor
4918
4919 CLOSE CursorBonuriSpeciale
4920 DEALLOCATE CursorBonuriSpeciale
4921
4922
4923
4924 PRINT 'END Medici/Pacienti'
4925 */
4926
4927 WHILE (EXISTS(SELECT TOP 1 SOrderCapId FROM @CmdFin))
4928 BEGIN
4929
4930 DECLARE @SorderCapComId INT,
4931 @SYS_UNITID INT,
4932 @SYS_DIVID INT,
4933 @SYS_USERID INT
4934
4935 SELECT TOP 1
4936 @SorderCapComId = SOrderCapId ,
4937 @SYS_UNITID = FirmaId,
4938 @SYS_DIVID = DivizieId,
4939 @SYS_USERID = UserId
4940 FROM @CmdFin
4941
4942 EXEC dbo.ComandaClient_Finalizare
4943 @SorderCapID = @SorderCapComId,
4944 @SYS_UNITID = @SYS_UNITID,
4945 @SYS_DIVID = @SYS_DIVID,
4946 @SYS_USERID = @SYS_USERID,
4947 @SYS_LANGID = 'RO'
4948
4949 DELETE FROM @CmdFin WHERE SOrderCapId = @SorderCapComId
4950
4951 END
4952
4953
4954 ------------------------------------------------------------------------------
4955 --Verificare existenta pozitii schimbare pret pentru validare doc sau stergere
4956 IF @ReevCapId IS NOT NULL
4957 BEGIN
4958
4959 IF NOT EXISTS
4960 (
4961 SELECT TOP 1 ReevPozId
4962 FROM inv.ReevPoz (NOLOCK)
4963 WHERE ReevCapId = @ReevCapId
4964 )
4965 BEGIN
4966
4967 DELETE FROM inv.ReevCap
4968 WHERE ReevCapId = @ReevCapId
4969
4970 END
4971
4972 ELSE
4973
4974 BEGIN
4975
4976 DECLARE @NrCifre int ,
4977 @Prefix nvarchar(50),
4978 @Curent nvarchar(20),
4979 @m nvarchar(100),
4980 @Setare247 int
4981
4982 SELECT @Setare247=dbo.Valoaresetare(247,@FirmaId,@DivizieID)
4983
4984 SELECT @NrCifre =isnull(NrCifre,0)
4985 FROM dbo.PlajaDoc (NOLOCK)
4986 WHERE PlajaId=@PlajaId
4987 and FirmaId=@FirmaId
4988 and DivizieId=@DivizieID
4989 and DocId=53
4990
4991 SELECT @Prefix=p.Prefix,
4992 @Curent= CAST(p.Curent as nvarchar(20))
4993 FROM dbo.Plaja p (NOLOCK)
4994 WHERE PlajaId=@PlajaId
4995
4996 WHILE (@NrCifre>0 and LEN(@Curent)<@NrCifre)
4997 BEGIN
4998 SET @Curent='0'+@Curent
4999 END
5000
5001 SELECT @m=@Prefix+@Curent
5002
5003 UPDATE cap
5004 SET cap.NrPozitii = poz.NrPoz
5005 , NumarReev = ISNULL(@m,CAST(cap.ReevCapId AS NVARCHAR(100)))
5006 FROM inv.ReevCap cap (NOLOCK)
5007 INNER JOIN
5008 (
5009 SELECT ReevCapId
5010 ,COUNT(*) as NrPoz
5011 FROM inv.ReevPoz (NOLOCK)
5012 WHERE ReevCapId = @ReevCapId
5013 GROUP BY ReevCapId
5014 ) poz
5015 ON cap.ReevCapId = poz.ReevCapId
5016
5017 UPDATE dbo.Plaja
5018 SET Curent = Curent + 1
5019 WHERE PlajaId = @PlajaId
5020
5021 IF EXISTS
5022 (
5023 SELECT TOP 1 cap.ReevCapId
5024 FROM dbo.ctbDoc cd ( NOLOCK )
5025 INNER JOIN inv.ReevCap cap ( NOLOCK )
5026 ON cap.ReevCapId = @ReevCapId
5027 AND cap.DocId = cd.DocId
5028 AND cap.FirmaId = cd.FirmaId
5029 AND cap.DivizieId = cd.DivizieId
5030 AND cd.Import = 1
5031 )
5032 BEGIN
5033
5034 SELECT @UserId = ISNULL(UserValidareId,UserCreareId)
5035 FROM inv.ReevCap
5036 WHERE ReevCapId = @ReevCapId
5037
5038 EXEC dbo.Doc2Conta_SchimbariPret
5039 @DocId = 53,
5040 @CapId = @ReevCapId,
5041 @DeLa = @DataCrt,
5042 @PanaLa = @DataCrt,
5043 @overwrite = 0,
5044 @sys_userId = @UserId,
5045 @sys_langID = 'RO',
5046 @sys_divId = @DivizieID,
5047 @sys_unitId = @FirmaId,
5048 @sys_partId = @sys_partId
5049
5050 --EXEC dbo.Doc2Conta
5051 -- @DocIds = '53',
5052 -- @CapId = @ReevCapId,
5053 -- @DeLa = @DataCrt,
5054 -- @PanaLa = @DataCrt,
5055 -- @overwrite = 0,
5056 -- @sys_userId = @UserId,
5057 -- @sys_langId = 'RO',
5058 -- @sys_unitId = @FirmaId,
5059 -- @sys_divId = @DivizieID,
5060 -- @sys_partID = @sys_partId
5061 END
5062
5063 END
5064
5065 END
5066 END
5067
5068 EXEC dbo.StopSemafor
5069 'POS_BONURI'
5070 , @FirmaId
5071 , @DivizieID
5072
5073 IF OBJECT_ID('tempdb..#TempSync') IS NOT NULL
5074 DROP TABLE #TempSync
5075 IF OBJECT_ID('tempdb..#TempSyncRetur') IS NOT NULL
5076 DROP TABLE #TempSyncRetur
5077 IF OBJECT_ID('tempdb..#TempSyncReturSpecial') IS NOT NULL
5078 DROP TABLE #TempSyncReturSpecial
5079 IF OBJECT_ID('tempdb..#PozitiiBuffer') IS NOT NULL
5080 DROP TABLE #PozitiiBuffer
5081 IF OBJECT_ID('tempdb..#IesCap') IS NOT NULL
5082 DROP TABLE #IesCap
5083
5084 END TRY
5085 BEGIN CATCH
5086
5087 -- 0 : no trans, -1: uncomittable, 1: active and valid
5088 -- PRINT XACT_STATE()
5089 -- IF (XACT_STATE() <> 0)
5090 -- ROLLBACK TRANSACTION T
5091
5092
5093 IF ( XACT_STATE() <> 0 )
5094 ROLLBACK TRANSACTION
5095
5096 IF OBJECT_ID('tempdb..#TempSync') IS NOT NULL
5097 DROP TABLE #TempSync
5098 IF OBJECT_ID('tempdb..#TempSyncRetur') IS NOT NULL
5099 DROP TABLE #TempSyncRetur
5100 IF OBJECT_ID('tempdb..#TempSyncReturSpecial') IS NOT NULL
5101 DROP TABLE #TempSyncReturSpecial
5102 IF OBJECT_ID('tempdb..#PozitiiBuffer') IS NOT NULL
5103 DROP TABLE #PozitiiBuffer
5104 IF OBJECT_ID('tempdb..#IesCap') IS NOT NULL
5105 DROP TABLE #IesCap
5106
5107 EXEC dbo.StopSemafor
5108 'POS_BONURI'
5109 , @FirmaId
5110 , @DivizieID
5111
5112
5113 SELECT
5114 @ErrorMessage = ERROR_MESSAGE()
5115 , @ErrorSeverity = ERROR_SEVERITY()
5116 , @ErrorState = ERROR_STATE();
5117
5118 RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
5119
5120
5121 END CATCH
5122
5123 END
5124
5125 ELSE
5126
5127 BEGIN
5128
5129 RAISERROR(N'JOB-ul este deja in rulare!', 16, 1)
5130
5131 END
5132
5133END
5134;
5135;
5136;
5137;