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