· 9 years ago · Nov 01, 2016, 10:24 AM
1USE [Adayroi_TMS]
2GO
3/****** Object: StoredProcedure [dbo].[APIClient_PurchaseOrder_AddFromOrder_V05] Script Date: 11/1/2016 5:22:36 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8--====================================================================================
9-- Entity Name: APIClient_PurchaseOrder_AddFromOrder_V05
10-- Author: Trần An Bình
11-- Create date:31/03/2015 09:44:33
12-- Description: Thêm mới PO từ Order
13-- ================================================================================
14ALTER PROC [dbo].[APIClient_PurchaseOrder_AddFromOrder_V05]
15(
16 @ListSo NVARCHAR(max),
17 --@ListPo NVARCHAR(MAX),
18 --@SoClientMapping NVARCHAR(MAX),
19 @SourceClient INT,
20 @ClientSoId BIGINT,
21 @SosStatus bit
22
23)
24AS
25BEGIN
26 DECLARE @result BIT = 0;
27 DECLARE @ResponseMessage NVARCHAR(MAX) = N'Chưa chạy và o Store';
28 DECLARE @Starttime datetime = getdate();
29 DECLARE @RowCoutPoFee int;
30 IF NOT EXISTS (SELECT 1 FROM dbo.SoClientMapping scm WHERE scm.ClientSoIdBigInt = @ClientSoId)
31 BEGIN
32 --cần don hà ng thì báºt dòng nà y lên
33 --insert into Log_SOPO (CreateDate,so,po,SoClientMapping,ClientSoId) Values (getdate(),@ListSo,'','',@ClientSoId)
34 DECLARE @RetryCounter INT
35 SET @RetryCounter = 1
36 RETRY: -- Label RETRY
37 BEGIN TRY
38 BEGIN TRAN T
39 DECLARE @TempSoDetail TABLE
40 (
41 SodetailId bigint,
42 SellPrice decimal (18,4),
43 LogisticSKULabel bigint ,
44 PoId bigint ,
45 ClientPoId bigint ,
46 SOId bigint,
47 ClientMerchant bigint,
48 ClientMerchantWarehourse bigint,
49 DeliveryDateFrom datetime,
50 DeliveryDateTo datetime,
51 DeliveryPeriodTime nvarchar(50)
52 --PRIMARY KEY (SodetailId)
53 )
54
55 DECLARE @tempSo TABLE
56 (
57 Soid bigint,
58 SID_Destination int,
59 City int,
60 CustomerRequestDeliveryType int,
61 CreateDatetime datetime
62 --PRIMARY KEY (Soid)
63 )
64
65 DECLARE @tempPo TABLE
66 (
67 Poid bigint,
68 SID_Tally int,
69 SID_Destination int,
70 RoutsId int
71 --PRIMARY KEY (Poid)
72 )
73
74 DECLARE @tempPOFee TABLE
75 (
76 PofeeId int,
77 PoFeeName nvarchar(200),
78 PoFee [decimal](18,0),
79 ClientPo bigint,
80 ProductItemId bigint
81 )
82 DECLARE @iDoc INT;
83 EXEC sp_xml_preparedocument @iDoc out, @ListSo;
84
85 --insert dữ liệu và o bản PO phà tạm
86 INSERT @tempPOFee
87 (
88 PofeeId,
89 PoFeeName,
90 PoFee,
91 ClientPo,
92 ProductItemId
93 )
94 SELECT
95 PofeeId,
96 PoFeeName,
97 PoFee,
98 ClientPo,
99 ProductItemId
100 FROM openxml(@iDoc,'/SoBoV4/ListPoFee/PoFeeObject', 0) WITH
101 (
102 PofeeId int 'PofeeId',
103 PoFeeName nvarchar(200) 'PoFeeName',
104 PoFee decimal(18,0) 'PoFee',
105 ClientPo bigint 'ClientPo',
106 ProductItemId bigint 'ProductItemId'
107 )
108 SET @RowCoutPoFee = @@ROWCOUNT;
109 -- INSERT TO SO
110 DECLARE @SoId bigint;
111 INSERT INTO [dbo].[SO]
112 ([SourceClientId]
113 ,[SID_Destination]
114 ,[UID_Create]
115 ,[CreateDatetime]
116 ,[SOStatusId]
117 ,[Note]
118 ,[MTID]
119 ,[DeliveryDateFrom]
120 ,[DeliveryDateTo]
121 ,[Visible]
122 ,[CustomerId]
123 ,[CustomerName]
124 ,[CustomerPhone]
125 ,[DeliveryCityId]
126 ,[DeliveryDistId]
127 ,[DeliveryWardId]
128 ,[DeliveryStreetId]
129 ,[DeliveryHouseN0]
130 ,[AddressTypeId]
131 ,[TotalPaid]
132 ,[RemainingAmount]
133 ,[DeliveryFullAddress]
134 ,[TotalPo]
135 ,[RouteId]
136 ,[DeliveryFee]
137 ,[DeliveryFeeSmallSoValue]
138 ,[ClientSoId]
139 ,[UnitTransportId]
140 ,OrderDate
141 ,CustomerRequestDeliveryType
142 ,CustomerRequestTime
143 ,CommitTime
144 ,DeliveryPeriodTime
145 ,Latitude
146 ,Longitude
147 ) OUTPUT INSERTED.SOID, INSERTED.SID_Destination, INSERTED.DeliveryCityId,INSERTED.CustomerRequestDeliveryType, INSERTED.CreateDatetime
148 INTO @tempSo (Soid, SID_Destination,City,CustomerRequestDeliveryType,CreateDatetime)
149 SELECT [SourceClientId]
150 --,[SID_Destination]
151 ,S.[SID_Destination]
152 ,[UID_Create]
153 ,GETDATE()
154 ,[SOStatusId]
155 --,[dbo].[CheckVipCustomer](CustomerPhone,DeliveryWardId)
156 --+
157 , CASE WHEN RTRIM(LTRIM(S.[Note]))='.' THEN ''
158 WHEN LEFT(LTRIM(S.[Note]), 1) = '.' THEN RIGHT(S.[Note], LEN(S.[Note]) - 1)
159 ELSE S.[Note] END
160 ,[MTID]
161 ,[DeliveryDateFrom]
162 ,[DeliveryDateTo]
163 ,1
164 ,[CustomerId]
165 ,[CustomerName]
166 ,[CustomerPhone]
167 ,[DeliveryCityId]
168 ,[DeliveryDistId]
169 ,[DeliveryWardId]
170 ,[DeliveryStreetId]
171 ,[DeliveryHouseN0]
172 ,[AddressTypeId]
173 ,[TotalPaid]
174 ,[RemainingAmount]
175 ,[DeliveryFullAddress]
176 ,[TotalPo]
177 ,[Routs]
178 ,[DeliveryFee]
179 ,[DeliveryFeeSmallSoValue]
180 ,[ClientSoId]
181 ,S.[UnitTransportId]
182 ,S.OrderDate
183 ,S.CustomerDeliveryType
184 ,S.CustomerRequestTime
185 ,S.CommitTime
186 ,s.DeliveryPeriodTime
187 ,s.Latitude
188 ,s.Longitude
189 FROM openxml(@iDoc, '/SoBoV4', 0) WITH
190 (
191 SourceClientId int 'SourceClientId',
192 SID_Destination int 'SID_Destination',
193 UID_Create int 'UID_Create',
194 SOStatusId int 'SOStatusId',
195 Note nvarchar(500) 'Note',
196 MTID int 'MTID',
197 DeliveryDateFrom datetime 'DeliveryDateFrom',
198 DeliveryDateTo datetime 'DeliveryDateTo',
199 CustomerId int 'CustomerId',
200 CustomerName nvarchar(50) 'CustomerName',
201 CustomerPhone varchar(50) 'CustomerPhone',
202 DeliveryCityId int 'DeliveryCityId',
203 DeliveryDistId int 'DeliveryDistId',
204 DeliveryWardId int 'DeliveryWardId',
205 DeliveryStreetId bigint 'DeliveryStreetId',
206 DeliveryHouseN0 nvarchar(200) 'DeliveryHouseN0',
207 AddressTypeId int 'AddressTypeId',
208 TotalPaid decimal(18, 4) 'TotalPaid',
209 RemainingAmount decimal(18, 4) 'RemainingAmount',
210 DeliveryFullAddress nvarchar(500) 'DeliveryFullAddress',
211 TotalPo int 'TotalPo',
212 RouteId int 'RouteId',
213 DeliveryFee decimal(18,0) 'DeliveryFee',
214 DeliveryFeeSmallSoValue decimal(18,0) 'DeliveryFeeSmallSoValue',
215 ClientSoId bigint 'ClientSoId',
216 UnitTransportId int 'UnitTransportId',
217 OrderDate datetime 'OrderDate',
218 CustomerDeliveryType int 'CustomerDeliveryType',
219 CustomerRequestTime datetime 'CustomerRequestTime',
220 CommitTime datetime 'CommitTime',
221 Routs int 'Routs',
222 DeliveryPeriodTime nvarchar(50)'DeliveryPeriodTime',
223 Latitude float 'Latitude',
224 Longitude float 'Longitude'
225 ) AS S
226 set @SoId = @@identity;
227 -- INSERT TO SoClientMapping
228 INSERT INTO [dbo].[SoClientMapping]
229 ([SoId]
230 ,[SourceClientId]
231 ,[ClientSoIdGuid]
232 ,[ClientSoIdBigInt])
233 VALUES
234 (
235 @SoId,
236 1,
237 null,
238 @ClientSoId
239 )
240
241 --INSERT TO SoDetail
242 INSERT INTO [dbo].[SO_Detail]
243 (
244 [LogisticSKULabel]
245 ,[Amount]
246 ,[SellPrice]
247 ,[UnitPriceExclVat]
248 ,[AdrVat]
249 ,[DiscountPercent]
250 ,[UnitPriceExclVatDiscount]
251 ,[SOId]
252 ,[SODetailStatusID]
253 ,[ManufactureBarcode]
254 ,[ClientMerchantId]
255 ,[ClientMerchantWarehouseId]
256 ,[ClientProductCateId]
257 ,[ClientProductId]
258 ,[DeliveryDateFrom]
259 ,[DeliveryDateTo]
260 ,[Visible]
261 ,[Priority]
262 ,[RequireSerial]
263 ,[TotalPaid]
264 ,[RemainingAmount]
265 ,[ClientPoId]
266 ,[ClientSoId]
267 ,[PoId]
268 ,[UnitBuyPrice]
269 ,[ProductTypeID]
270 ,[MTID]
271 ,[CustomerRequestTime]
272 ,[CommitTime]
273 ,[FreshFoodTypeId]
274 ,[DeliveryPeriodTime]
275 ,[CrCode]
276 ,[CrCodeApply]
277 ,[CrCodeNote]
278 ,[RequireInstall]
279 ,[SAPReservationNumber]
280 ,[SAPPONumberInterim]
281 ,[SAPReservationLineItem]
282 ,[CheckGiftProducts]
283 ) OUTPUT INSERTED.SODetailID,INSERTED.SellPrice,INSERTED.LogisticSKULabel,INSERTED.PoId, INSERTED.ClientPoId ,INSERTED.SOId, INSERTED.ClientMerchantId, INSERTED.ClientMerchantWarehouseId, INSERTED.DeliveryDateFrom, INSERTED.DeliveryDateTo, INSERTED.DeliveryPeriodTime
284 INTO @TempSoDetail( SodetailId, SellPrice,LogisticSKULabel,PoId,ClientPoId, SOId,ClientMerchant, ClientMerchantWarehourse,DeliveryDateFrom,DeliveryDateTo,DeliveryPeriodTime)
285 SELECT
286 [LogisticSKULabel]
287 ,[Amount]
288 ,[SellPrice]
289 ,[UnitPriceExclVat]
290 ,[AdrVat]
291 ,[DiscountPercent]
292 ,[UnitPriceExclVatDiscount]
293 ,@SoId
294 ,[SODetailStatusID]
295 ,[ManufactureBarcode]
296 ,[ClientMerchantId]
297 ,[ClientMerchantWarehouseId]
298 ,[ClientProductCateId]
299 ,[ClientProductId]
300 ,[DeliveryDateFrom]
301 ,[DeliveryDateTo]
302 ,1
303 ,[Priority]
304 ,[RequireSerial]
305 ,[TotalPaid]
306 ,[RemainingAmount]
307 ,[ClientPoId]
308 ,[ClientSoId]
309 ,[PoId]
310 ,[UnitBuyPrice]
311 ,SD.[ProductTypeID]
312 ,[MTID]
313 ,[CustomerRequestTime]
314 ,[CommitTime]
315 ,[FreshFoodTypeId]
316 ,DeliveryPeriodTime
317 ,CrCode
318 ,CrCodeApply
319 ,CrCodeNote
320 ,RequireInstall
321 ,SAPReservationNumber
322 ,SAPPONumberInterim
323 ,SAPReservationLineItem
324 ,CheckGiftProducts
325 FROM openxml(@idoc, '/SoBoV4/SoDetailBos/SoDetailBoV4', 0) WITH
326 (
327 LogisticSKULabel bigint 'LogisticSKULabel',
328 Amount int 'Amount',
329 SellPrice decimal(18, 4) 'SellPrice',
330 UnitPriceExclVat decimal(18, 4) 'UnitPriceExclVat',
331 AdrVat decimal(18, 4) 'AdrVat',
332 DiscountPercent decimal(7, 4) 'DiscountPercent',
333 UnitPriceExclVatDiscount decimal(18, 4) 'UnitPriceExclVatDiscount',
334 SODetailStatusID int 'SODetailStatusID',
335 ManufactureBarcode varchar(50) 'ManufactureBarcode',
336 ClientMerchantId int 'ClientMerchantId',
337 ClientMerchantWarehouseId int 'ClientMerchantWarehouseId',
338 ClientProductCateId int 'ClientProductCateId',
339 ClientProductId int 'ClientProductId',
340 DeliveryDateFrom datetime 'DeliveryDateFrom',
341 DeliveryDateTo datetime 'DeliveryDateTo',
342 [Priority] int 'Priority',
343 RequireSerial bit 'RequireSerial',
344 TotalPaid decimal(18, 4) 'TotalPaid',
345 RemainingAmount decimal(18, 4) 'RemainingAmount',
346 ClientPoId bigint 'ClientPoId',
347 ClientSoId bigint 'ClientSoId',
348 PoId bigint 'PoId',
349 UnitBuyPrice decimal(18, 4) 'UnitBuyPrice',
350 ProductTypeID int 'ProductTypeID',
351 MTID int 'MTID',
352 CustomerRequestTime datetime 'CustomerRequestTime',
353 CommitTime datetime 'CommitTime',
354 FreshFoodTypeId int 'FreshFoodTypeId',
355 DeliveryPeriodTime nvarchar(50) 'DeliveryPeriodTime',
356 CrCode nvarchar(50) 'CrCode',
357 CrCodeApply nvarchar(50) 'CrCodeApply',
358 CrCodeNote nvarchar(500) 'CrCodeNote',
359 RequireInstall bit 'RequireInstall',
360 SAPReservationNumber nvarchar(100) 'SAPReservationNumber',
361 SAPPONumberInterim varchar(100) 'SAPPONumberInterim',
362 SAPReservationLineItem nvarchar(200) 'SAPReservationLineItem',
363 CheckGiftProducts varchar(100) 'CheckGiftProducts'
364 ) as SD
365
366 DECLARE @CurrentDateTime datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
367 INSERT INTO [dbo].[PurchaseOrder]
368 ([PoId]
369 ,[SoId]
370 ,[ClientPoId]
371 ,[SID_Tally]
372 ,[SID_Destination]
373 ,[UID_Create]
374 ,[ClientMerchantWarehouseId]
375 ,[SKUAmount]
376 ,[TotalItemAmount]
377 ,[AmountExclVat]
378 ,[DiscountPercent]
379 ,[DiscountAmount]
380 ,[AmountInclVat]
381 ,[CreateDatetime]
382 ,[POStatusId]
383 ,[MTID]
384 ,[ProductTypeId]
385 ,[DeliveryDateFrom]
386 ,[DeliveryDateTo]
387 ,[TotalWeight]
388 ,[TotalSkuCbm]
389 ,[Visible]
390 ,[Priority]
391 ,[RouteId]
392 ,[UnitTransportId]
393 ,[DeliveryFlowId]
394 ,[CustomerRequestTime]
395 ,[CommitTime]
396 ,[FreshFoodTypeId]
397 ,[DatePickup]
398 ,[DeliveryPeriodTime]
399 ,[TempTallyStation]
400 ,[TempDestinationStation]
401 ,[Sos]
402 ,[IsDropOff]
403 ,[InitDeliveryFlowId]
404 ,[StartLeadtime]
405 ,[SIDChangeSC]
406 ) OUTPUT INSERTED.PoId, INSERTED.SID_Tally, INSERTED.SID_Destination, INSERTED.RouteId
407 INTO @tempPo (Poid,SID_Tally,SID_Destination,RoutsId)
408 SELECT P.[PoId]
409 ,@SoId
410 ,P.[ClientPoId]
411 ,P.SID_Tally
412 ,P.SID_Destination
413 ,[UID_Create]
414 ,P.[ClientMerchantWarehouseId]
415 ,[SKUAmount]
416 ,[TotalItemAmount]
417 ,[AmountExclVat]
418 ,[DiscountPercent]
419 ,[DiscountAmount]
420 ,[AmountInclVat]
421 ,GETDATE()
422 ,[POStatusId]
423 ,[MTID]
424 ,P.[ProductTypeId]
425 ,P.[DeliveryDateFrom]
426 ,P.[DeliveryDateTo]
427 ,[TotalWeight]
428 ,[TotalSkuCbm]
429 ,1
430 ,P.[Priority]
431 ,[RouteId]
432 ,P.[UnitTransportId]
433 ,ISNULL(P.DeliveryFlowId,0)
434 ,[CustomerRequestTime]
435 ,[CommitTime]
436 ,[FreshFoodTypeId]
437 ,[DatePickup]
438 ,P.[DeliveryPeriodTime]
439 ,[TempTallyStation]
440 ,[TempDestinationStation]
441 ,[Sos]
442 ,P.[IsDropOff]
443 ,[InitDeliveryFlowId]
444 ,CASE
445 WHEN cm.IsDropOff = 1 THEN DATEADD(DAY,ct.DateNumber,DATEADD(HOUR, DATEPART(HOUR,cm.DateStartLeadTime),DATEADD(MINUTE, DATEPART(MINUTE,cm.DateStartLeadTime),@CurrentDateTime)))
446 ELSE StartLeadtime
447 END AS [StartLeadtime],
448 [SIDChangeSC]
449 FROM openxml(@iDoc, '/SoBoV4/ClientPo/ClientPurchaseOrderBoV2', 0) WITH
450 (
451 PoId bigint 'PoId',
452 ClientPoId bigint 'ClientPoId',
453 SID_Tally int 'SID_Tally',
454 SID_Destination int 'SID_Destination',
455 UID_Create int 'UID_Create',
456 ClientMerchantWarehouseId int 'ClientMerchantWarehouseId',
457 SKUAmount int 'SKUAmount',
458 TotalItemAmount int 'TotalItemAmount',
459 AmountExclVat decimal(18, 4) 'AmountExclVat',
460 DiscountPercent decimal(7, 4) 'DiscountPercent',
461 DiscountAmount decimal(18, 4) 'DiscountAmount',
462 AmountInclVat decimal(18, 4) 'AmountInclVat',
463 POStatusId int 'POStatusId',
464 MTID int 'MTID',
465 ProductTypeId int 'ProductTypeId',
466 DeliveryDateFrom datetime 'DeliveryDateFrom',
467 DeliveryDateTo datetime 'DeliveryDateTo',
468 TotalWeight decimal(18, 0) 'TotalWeight',
469 TotalSkuCbm decimal(18, 0) 'TotalSkuCbm',
470 [Priority] int 'Priority',
471 RouteId int 'RouteId',
472 UnitTransportId int 'UnitTransportId',
473 WardId INT 'WardId',
474 StreetId INT 'StreetId',
475 DeliveryFlowId INT 'DeliveryFlowId',
476 CustomerRequestTime datetime 'CustomerRequestTime',
477 CommitTime datetime 'CommitTime',
478 FreshFoodTypeId int 'FreshFoodTypeId',
479 DatePickup int 'DatePickup',
480 DeliveryPeriodTime nvarchar(50)'DeliveryPeriodTime',
481 TempTallyStation int 'TempTallyStation',
482 TempDestinationStation int 'TempDestinationStation',
483 Sos bit 'Sos',
484 IsDropOff bit 'IsDropOff',
485 InitDeliveryFlowId int 'InitDeliveryFlowId',
486 StartLeadtime datetime 'StartLeadtime',
487 [SIDChangeSC] int 'SIDChangeSC'
488 ) AS P
489 LEFT JOIN dbo.ClientMerchantWarehouse cmw ON cmw.ClientMerchantWarehouseId = P.ClientMerchantWarehouseId
490 LEFT JOIN dbo.ClientMerchant cm WITH(NOLOCK) ON cm.ClientMerchantId = cmw.ClientMerchantId
491 LEFT JOIN CutOffTimeType ct WITH(NOLOCK) ON ct.COTTypeId = cm.COTTypeId
492 --INNER JOIN @tempSo ts ON ts.Soid =tsd.SOId
493 --tinh giá» server
494
495
496 -- INSERT TO PURCHASEORDERDETAIL
497 INSERT INTO [dbo].[PurchaseOrder_Detail]
498 (
499 [SODetailID]
500 ,[LogisticSKULabel]
501 ,[ClientProductId]
502 ,[Amount]
503 ,[SellPrice]
504 ,[UnitPriceExclVat]
505 ,[AdrVat]
506 ,[McVat]
507 ,[DiscountPercent]
508 ,[UnitPriceExclVatDiscount]
509 ,[UnitBuyPrice]
510 ,[PoId]
511 ,[PODetailStatusID]
512 ,[ClientProductCateId]
513 ,[CBM]
514 ,[Weight]
515 ,[Visible]
516 ,[Priority]
517 ,[TotalSKU]
518 ,[RequireSerial]
519 ,[ClientPoId]
520 ,[ComissionFee]
521 ,[CheckGiftProducts]
522 )
523 SELECT SOD.[SODetailID]
524 ,POD.[LogisticSKULabel]
525 ,POD.[ClientProductId]
526 ,POD.[Amount]
527 ,POD.[SellPrice]
528 ,POD.[UnitPriceExclVat]
529 ,POD.[AdrVat]
530 ,POD.[McVat]
531 ,POD.[DiscountPercent]
532 ,POD.[UnitPriceExclVatDiscount]
533 ,POD.[UnitBuyPrice]
534 ,POD.[PoId]
535 ,POD.[PODetailStatusID]
536 ,POD.[ClientProductCateId]
537 ,[CBM]
538 ,[Weight]
539 ,1
540 ,POD.[Priority]
541 ,TotalSKU
542 ,POD.[RequireSerial]
543 ,Isnull(SOD.ClientPoId,POD.PoId) as [ClientPoId]
544 ,POD.[CommissionFee]
545 ,[CheckGiftProducts]
546 FROM openxml(@iDoc, '/SoBoV4/ClientPo/ClientPurchaseOrderBoV2/ClientPoDetails/ClientPurchaseOrderDetailBo', 0) WITH
547 (
548 SODetailID bigint 'SODetailID',
549 LogisticSKULabel bigint 'LogisticSKULabel',
550 ClientProductId int 'ClientProductId',
551 Amount int 'Amount',
552 SellPrice decimal(18, 4) 'SellPrice',
553 UnitPriceExclVat decimal(18, 4) 'UnitPriceExclVat',
554 AdrVat decimal(18, 4) 'AdrVat',
555 McVat decimal(18, 4) 'McVat',
556 DiscountPercent decimal(7, 4) 'DiscountPercent',
557 UnitPriceExclVatDiscount decimal(18, 4) 'UnitPriceExclVatDiscount',
558 UnitBuyPrice decimal(18, 4) 'UnitBuyPrice',
559 PoId bigint 'PoId',
560 PODetailStatusID int 'PODetailStatusID',
561 ClientProductCateId int 'ClientProductCateId',
562 CBM bigint 'CBM',
563 [Weight] decimal(18, 0) 'Weight',
564 [Priority] int 'Priority',
565 TotalSKU int 'TotalSKU',
566 RequireSerial bit 'RequireSerial',
567 CommissionFee decimal(18,4) 'CommissionFee',
568 CheckGiftProducts varchar(100) 'CheckGiftProducts'
569 ) as POD
570 INNER JOIN @TempSoDetail as SOD
571 on (POD.LogisticSKULabel = SOD.LogisticSKULabel AND SOD.PoId = POD.PoId AND SOD.SellPrice =POD.SellPrice and SOD.SOId= @SoId)
572
573
574 -- insert phà PO
575 if(@RowCoutPoFee > 0)
576 BEGIN
577 --SELECT @currentName = PoFeeName,@tmsPoFeeId=id FROM PurchaseOrder_Fee WITH(NOLOCK) WHERE PoFeeId = @PoFeeId;
578 INSERT dbo.PurchaseOrder_Fee_Map
579 (
580 PoId,
581 PoFeeId,
582 ClientPoId,
583 ProductItemId,
584 Fee
585 )
586 SELECT
587 po.PoId,
588 pof.PoFeeId,
589 tp.ClientPo,
590 tp.ProductItemId, -- tuong duong ProductItemId
591 tp.PoFee
592 FROM @tempPOFee tp
593 INNER JOIN dbo.PurchaseOrder_Fee pof WITH(NOLOCK) ON tp.PofeeId = pof.PoFeeId
594 INNER JOIN dbo.PurchaseOrder po WITH(NOLOCK) ON po.ClientPoId = tp.ClientPo
595 END
596 ---
597 --check special Note
598 IF EXISTS (SELECT ClientSoId FROM SpecialNotes WHERE ClientSoId= @ClientSoId)
599 BEGIN
600 UPDATE SpecialNotes
601 SET SoId = @SoId
602 WHERE ClientSoId = @ClientSoId
603
604 UPDATE SO
605 SET SpecialNoteStatus = 1
606 WHERE SoId = @SoId
607 END
608 --------------------------------------
609 ----Update lại sodetailRootParent
610 -- IF EXISTS (SELECT [SODetailID] from @TempSoDetail)
611 -- BEGIN
612 -- UPDATE dbo.SO_Detail
613 -- SET
614 -- SO_Detail.SoDetailIdRoot = tsd.SodetailId
615 -- FROM dbo.SO_Detail sd
616 -- INNER JOIN @TempSoDetail tsd ON tsd.SodetailId =sd.SODetailID
617 -- END
618 ----------------------------------
619 UPDATE dbo.PurchaseOrder
620 SET
621 DeliveryDateFrom =tsd.DeliveryDateFrom,
622 DeliveryDateTo =tsd.DeliveryDateTo,
623 DeliveryPeriodTime = tsd.DeliveryPeriodTime,
624 DeliveryCaseId = CASE
625 WHEN ((sta.CityID = sde.CityID) AND (sta.SID = sde.SID)) AND po.DeliveryFlowId IN (3,4) THEN 3
626 WHEN ((sta.CityID = sde.CityID) AND (sta.SID = sde.SID)) AND NOT po.DeliveryFlowId IN (3,4) THEN po.DeliveryFlowId
627 WHEN ((sta.CityID = sde.CityID) AND (sta.SID <> sde.SID)) THEN 4 -- Trung chuyển trạm giao nháºn ná»™i vùng
628 WHEN ((sta.CityID <> sde.CityID) AND (sta.SID <> sde.SID)) THEN 6 -- Trung chuyển đến trung tâm giao nháºn trái vùng
629 ELSE
630 0
631 END
632 FROM dbo.PurchaseOrder po
633 INNER JOIN dbo.Stations sta WITH(NOLOCK) ON po.SID_Tally = sta.SID
634 INNER JOIN dbo.Stations sde WITH(Nolock) ON po.SID_Destination = sde.SID
635 INNER JOIN @TempSoDetail tsd ON tsd.ClientPoId = po.ClientPoId OR tsd.PoId= po.ClientPoId
636 INNER JOIN @tempSo ts ON ts.Soid = tsd.SOId
637 --WHERE ts.CustomerRequestDeliveryType =2
638 ------ --AND po.SoId =91795
639
640 --check Tuyên lây hà ng
641 if(EXISTS(SELECT tp.RoutsId FROM @tempPo tp WHERE tp.RoutsId =0))
642 BEGIN
643 UPDATE dbo.PurchaseOrder
644 SET
645 PurchaseOrder.RouteId = isnull(rd.RoutId,0)
646 FROM dbo.PurchaseOrder po WITH(NOLOCK)
647 INNER JOIN dbo.ClientMerchantWarehouse cmw WITH(NOLOCK) ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
648 INNER JOIN dbo.AD_Ward_Street aws WITH(NOLOCK) ON aws.WardId = cmw.WardId AND aws.StreetId = cmw.StreetId
649 INNER JOIN dbo.RoutDetail rd WITH(NOLOCK) ON aws.WardId = rd.WardId AND aws.WSId = rd.StreetId
650 INNER JOIN @tempPo tp ON tp.Poid= po.PoId
651 END
652
653 EXEC sp_xml_removedocument @iDoc
654
655 DECLARE @end datetime = getdate() - @Starttime;
656 SET @result=1;
657 SET @ResponseMessage = 'Star: ' + convert(varchar(50), @Starttime , 21) + ' @end: ' + convert(varchar(50),@end , 21) ;
658 INSERT dbo.Log_SOPO
659 (
660 --id - this column value is auto-generated
661 CreateDate,
662 ClientSoId,
663 type,
664 LogXml,
665 ClientRPO,
666 ErrorMessage
667 )
668 VALUES
669 (
670 -- id - bigint
671 Getdate(), -- CreateDate - datetime
672 @SoId, -- ClientSoId - bigint
673 3, -- type - int
674 N'', -- LogXml - nvarchar
675 0, -- ClientRPO - bigint
676 @ResponseMessage-- ErrorMessage - nvarchar
677 )
678 COMMIT TRAN T
679 END TRY
680 BEGIN CATCH
681 ROLLBACK TRAN T
682 DECLARE @DoRetry bit; -- Whether to Retry transaction or not
683 DECLARE @ErrorMessage varchar(500)
684 SET @ResponseMessage = ERROR_MESSAGE();
685 SET @result=0
686 SET @doRetry = 0;
687
688 IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
689 BEGIN
690 SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
691 END
692 IF @DoRetry = 1
693 BEGIN
694 SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
695 IF (@RetryCounter >=1) -- Check whether Retry Counter reached to 3
696 BEGIN
697 SET @ErrorMessage = N'Deadlock.'
698 END
699 ELSE
700 BEGIN
701 WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
702 SET @ErrorMessage = ERROR_MESSAGE();
703 GOTO RETRY -- Go to Label RETRY
704 END
705 END
706 ELSE
707 BEGIN
708 RAISERROR(@ErrorMessage, 18, 1)
709 END
710 END CATCH
711 END
712 ELSE
713 BEGIN
714 SET @result = 0;
715 SET @ResponseMessage = N'ÄÆ¡n hà ng nà y đã có trong hệ thống rồi!';
716 END
717 SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage;
718END