qBGySAQh

1USE [Adayroi_TMS]
2GO
3/****** Object:  StoredProcedure [dbo].[APIClient_PurchaseOrder_ChangeOrderType_V02]    Script Date: 12/12/2017 2:54:26 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8--====================================================================================
9-- Entity Name:	[APIClient_PurchaseOrder_ChangeOrderType_V02]
10-- Author:	TRẦN AN BÌNH
11-- Create date:	15/12/2014 8:32:27 PM
12-- Description:	ĐỔI LUỒN GIAO HÀNG CỦA ĐƠN HÀNG
13-- ================================================================================
14ALTER PROCEDURE [dbo].[APIClient_PurchaseOrder_ChangeOrderType_V02]
15(
16	@Poid nvarchar(max),
17	@userId int,
18	@sessionkey nvarchar(200),
19	@currentstatus int,
20	@newStatus int,
21	@opptionChange int
22)
23AS
24BEGIN
25	DECLARE @result BIT = 1;
26	DECLARE @ResponseMessage NVARCHAR(MAX) = N'';
27	--DECLARE	@PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)	
28	--EXEC		[dbo].[CHECK_SESSION_AND_PERMISSION] @userId, @sessionkey, @PROC_NAME
29	--ktra diwu kien chuyem
30	DECLARE @PoTableSC TABLE
31	   (
32		PoId bigint,
33		DeliveryFlowId int,
34		Soid bigint,
35		regionTally	int,
36		regionDestination int,
37		statlly int,
38		sdes	int,
39		stationTypeId int,
40		ScoverId int,
41		HangTraiTinhKhongcoSc bit
42	   )
43	  DECLARE @PoTable TABLE
44	   (
45		PoId bigint,
46		DeliveryFlowId int,
47		Soid bigint,
48		typeChange int,
49		merchantID int,
50		TallyRouts int,
51		DesRouts int,
52		ProductTypeId int,
53		requireInstall bit,
54		HangTraiTinhKhongcoSc bit
55	   )
56	   INSERT INTO @PoTable 
57			(	PoId,
58				DeliveryFlowId,
59				Soid,
60				typeChange,
61				merchantID,
62				TallyRouts,
63				DesRouts,
64				ProductTypeId,
65				requireInstall,
66				HangTraiTinhKhongcoSc
67			)
68	   SELECT	DISTINCT
69				[value] ,
70				po.DeliveryFlowId,
71				po.SoId,
72				CASE 
73						WHEN	cm.MerchantId =2282 AND (SELECT	Count(*) FROM dbo.PurchaseOrder po2 WITH(NOLOCK)	WHERE po2.SoId	= po.SoId) = 1 THEN	1
74						--WHEN	cm.MerchantId =2282 AND (SELECT	Count(*) FROM dbo.PurchaseOrder po2 WITH(NOLOCK)	WHERE po2.SoId	= po.SoId) > 1 THEN	2
75				ELSE	0
76				END,
77				cm.MerchantId,
78				[dbo].[GetRouts](cmw.WardId,po.SID_Tally,1),
79				[dbo].[GetRouts](s.DeliveryWardId, CASE WHEN @currentstatus in (3,4) then po.SID_Tally
80														ELSE  	 ISNULL(sw.SID,0) 	 END,2),
81				po.ProductTypeId,
82				CASE WHEN EXISTS(SELECT 1 FROM SO_Detail pod WITH(NOLOCK) where pod.RequireInstall = 1 AND pod.Poid = po.PoId) THEN 1 
83				ELSE 0 
84				END,
85				CASE WHEN EXISTS (SELECT TOP 1 1 FROM Stations st with(nolock) where st.cityID=s.DeliveryCityId and st.STID = 2)  
86							AND EXISTS (SELECT TOP 1 1 FROM Stations st with(nolock) where st.cityID=cmw.CityId and st.STID = 2)
87						THEN 1 
88						ELSE 0 END
89	   FROM  SplitListToBIGINT(@Poid, ',')	AS Poid
90	   INNER JOIN	dbo.PurchaseOrder					po	WITH(NOLOCK)	ON	po.PoId	= PoId.[value]
91	   INNER JOIN	dbo.ClientMerchantWarehouse			cmw WITH(NOLOCK)	ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
92	   INNER JOIN	dbo.ClientMerchant					cm	WITH(NOLOCK)	ON cm.ClientMerchantId = cmw.ClientMerchantId	
93	   INNER JOIN	SO									s	WITH(NOLOCK)	ON s.SOID = PO.SoId
94	   LEFT JOIN	dbo.Station_Ward					sw	WITH(NOLOCK)	ON  sw.WardId = s.DeliveryWardId AND sw.TypeId = 2 --tram giao hàng
95	  -- LEFT JOIN	dbo.Station_Ward					st	WITH(NOLOCK)	ON  st.SID = sw.SID AND st.STID IN(1,2)
96
97	DECLARE	@UserName nvarchar(250);
98	Declare @StationId int;  
99	SELECT	@UserName = us.FullName,
100			@StationId = us.SID
101	FROM UM_Users us WITH(NOLOCK) WHERE us.UserId = @userId
102	--ktra có trạm giao theo dia chỉ khách hàng ko?
103	DECLARE @RowCountTempPO int;
104	SELECT @RowCountTempPO = count(*) FROM @PoTable
105	Declare @tempStationtypeId int = 0;
106	Declare @tempScCover int = 0;
107	Declare @temsdes int = 0;
108	--SELECT	* FROM @PoTable pt	
109	--Return;
110    IF EXISTS (SELECT 1 FROM dbo.SO_Detail sd	 WITH(NOLOCK) 	
111		  INNER JOIN @PoTable rs ON rs.PoId = sd.ClientPoId  
112		  WHERE sd.SODetailStatusID  > 1 )
113		  BEGIN
114			 SET @ResponseMessage = N'Đơn hàng đã được xử lý hoặc đã nhập kho.'; 
115			 SET @result = 0;
116
117			 SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
118			 RETURN
119		  END
120	   IF EXISTS (SELECT ClientPoId FROM PurchaseOrder po WITH(NOLOCK) 	
121		  INNER JOIN @PoTable rs ON rs.PoId = po.ClientPoId  
122	   WHERE po.POStatusId	= 1  AND UID_COD IS NOT NULL )
123		  BEGIN   
124				SET @result = 0;
125				SET @ResponseMessage = N'Đơn hàng đã được xử lý hoặc được nhập kho.'; 
126				SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
127				RETURN
128		  END  
129	   IF(@RowCountTempPO = 0)
130			BEGIN
131				SET	@result = 0;
132				Set @ResponseMessage =N'Trạm giao hàng chưa xác định, vui lòng cấu hình trạm cho địa chỉ giao hàng.'
133				RETURN
134			END
135		IF( EXISTS (SELECT 1 FROM  @PoTable rs
136					  INNER JOIN	dbo.SO_Detail sd  WITH(NOLOCK) 		ON sd.ClientPoId = rs.PoId
137					  WHERE (RTRIM(LTRIM(ISNULL(sd.CrCode,''))) <> '' OR RTRIM(LTRIM(ISNULL(sd.CrCodeApply,'')))  <> '')))
138			BEGIN	
139				SET @result = 0;
140				SET @ResponseMessage = N'Đơn hàng Cart rule không cho chuyển luồng.'; 
141				SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
142				RETURN
143			END	
144			--PO đã được chuyển luồng đi thẳng
145		IF( EXISTS (SELECT 1 FROM  @PoTable rs
146					  INNER JOIN	PurchaseOrder po  WITH(NOLOCK)		ON rs.PoId = po.PoId
147					  WHERE (UID_COD IS NOT NULL AND UID_Operator IS NOT NULL ) and po.DeliveryFlowId = @newStatus))
148			BEGIN	
149				SET @result = 0;
150				SET @ResponseMessage = N'Có PO đã thao tác phân không, không cho chuyển luồng.'; 
151				SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
152				RETURN
153			END	
154		IF( EXISTS (SELECT 1 FROM  @PoTable rs
155					  INNER JOIN	PurchaseOrder po  WITH(NOLOCK)		ON rs.PoId = po.PoId
156					   Inner join	Stations	  st	WITH(NOLOCK)		ON po.SID_Tally = st.SID
157					  WHERE (UID_COD IS NULL AND UID_Operator IS NULL ) and po.DeliveryFlowId = @newStatus AND st.STID IN (1,2)))
158			BEGIN	
159				SET @result = 0;
160				SET @ResponseMessage = N'Có PO đã thao tác chuyển luồng, cần refesh lại dữ liệu.'; 
161				SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
162				RETURN
163			END	
164		IF(EXISTS (SELECT 1 FROM  @PoTable rs
165					  INNER JOIN	PurchaseOrder po	WITH(NOLOCK)		ON rs.PoId = po.PoId
166					  Inner join	Stations	  st	WITH(NOLOCK)		ON po.SID_Tally = st.SID 
167					  WHERE (UID_COD IS NULL AND UID_Operator IS NULL ) and po.DeliveryFlowId = @currentstatus AND st.STID NOT IN (1,2)))
168			BEGIN
169				SET @result = 0;
170				SET @ResponseMessage = N'Có PO đã thao tác chuyển luồng, cần refesh lại dữ liệu.'; 
171				SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
172				RETURN
173			END
174		if(@opptionChange = 1)
175			BEGIN
176				BEGIN TRAN T	
177			--	Select * from @PoTable
178				If(@currentstatus in(2,3) AND EXISTS (SELECT 1 FROM  @PoTable rs
179						  INNER JOIN	PurchaseOrder po	WITH(NOLOCK)		ON rs.PoId = po.PoId
180						  where not exists (
181								Select 1 from ClientMerchantWarehouse cmw
182								 INNER JOIN	dbo.Station_Ward			sw		WITH(NOLOCK)	ON  sw.WardId = cmw.WardId AND sw.TypeId = 1  --tram lấy hàng
183								 INNER JOIN	Stations	  st	WITH(NOLOCK)		ON sw.SID = st.SID and st.STID IN (1,2)
184								 where cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
185							)
186							AND (po.UID_COD IS NULL AND po.UID_Operator IS NULL ) and po.DeliveryFlowId = @currentstatus)) -- kiểm tra cấu hình trạm lấy hàng
187					BEGIN
188						SET @result = 0;
189						SET @ResponseMessage = N'Có PO không cấu hình trạm cho địa chỉ lấy hàng.'; 
190						SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
191						RETURN
192					END
193				IF(@currentstatus = 3 AND EXISTS (SELECT 1 FROM  @PoTable rs
194						  INNER JOIN	PurchaseOrder po	WITH(NOLOCK)		ON rs.PoId = po.PoId
195						  INNER JOIN	dbo.ClientMerchantWarehouse	cmw WITH(NOLOCK)		ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
196						  INNER JOIN	dbo.Station_Ward			sw		WITH(NOLOCK)	ON  sw.WardId = cmw.WardId AND sw.TypeId = 1 --tram lấy hàng
197						  INNER JOIN	Stations					st	WITH(NOLOCK)		ON sw.SID = po.SID_Tally AND st.STID IN (1,2)
198						  WHERE (UID_COD IS NULL AND UID_Operator IS NULL ) and po.DeliveryFlowId = 2)) -- kiểm tra đơn vi hành chính trạm lấy 
199					BEGIN
200						SET @result = 0;
201						SET @ResponseMessage = N'Có PO đã được đổi luồng, vui lòng refesh data.'; 
202						SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
203						RETURN
204					END
205				IF(@currentstatus = 2 AND EXISTS (SELECT 1 FROM  @PoTable rs
206						  INNER JOIN	PurchaseOrder po	WITH(NOLOCK)		ON rs.PoId = po.PoId
207						  INNER JOIN	dbo.ClientMerchantWarehouse	cmw WITH(NOLOCK)		ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
208						  INNER JOIN	dbo.Station_Ward			sw		WITH(NOLOCK)	ON  sw.WardId = cmw.WardId AND sw.TypeId = 1 --tram lấy hàng
209						  Inner join	Stations					st	WITH(NOLOCK)		ON sw.SID = po.SID_Tally AND st.STID IN (1,2)
210						  WHERE (UID_COD IS NULL AND UID_Operator IS NULL ) and po.DeliveryFlowId = 3)) -- kiểm tra đơn vi hành chính trạm lấy 
211					BEGIN
212						SET @result = 0;
213						SET @ResponseMessage = N'Có PO đã được đổi luồng, vui lòng refesh data.'; 
214						SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
215						RETURN
216					END
217				IF(@currentstatus = 2 AND  EXISTS (SELECT 1 FROM  @PoTable rs
218						  INNER JOIN	PurchaseOrder po	WITH(NOLOCK)		ON rs.PoId = po.PoId
219						  where not exists (
220								Select 1 from SO s
221								 INNER JOIN	dbo.Station_Ward			sw		WITH(NOLOCK)	ON  sw.WardId = s.DeliveryWardId AND sw.TypeId = 2  --tram giao hàng
222								 INNER JOIN	Stations	  st	WITH(NOLOCK)		ON sw.SID = st.SID and st.STID IN (1,2)
223								 where s.soid = po.soid
224							)
225							AND (po.UID_COD IS NULL AND po.UID_Operator IS NULL ) and po.DeliveryFlowId = 3)) -- kiểm tra đơn vị hành chính trạm giao
226					BEGIN
227						SET @result = 0;
228						SET @ResponseMessage = N'Có PO không cấu hình trạm cho địa chỉ giao hàng.'; 
229						SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
230						RETURN
231					END
232				UPDATE PurchaseOrder 
233			 SET 
234						DeliveryFlowId =	CASE  
235											WHEN PO.DeliveryFlowId  = 4 THEN	1
236											WHEN PO.DeliveryFlowId  = 1 THEN	4
237											WHEN PO.DeliveryFlowId	= 2	THEN	3
238											WHEN PO.DeliveryFlowId	= 3	THEN	2
239											END,
240						SID_Tally		= ISNULL(swtally.SID,0),
241						SID_Destination =	CASE  
242												WHEN PO.DeliveryFlowId  = 4		THEN	PO.SID_Tally
243												WHEN PO.DeliveryFlowId  = 1	and lpo.HangTraiTinhKhongcoSc = 1	THEN	ISNULL(sw.SID,0)
244												WHEN PO.DeliveryFlowId  = 1	and lpo.HangTraiTinhKhongcoSc = 0	THEN	s.SID_Destination
245												WHEN PO.DeliveryFlowId	= 2		AND s.DeliveryCityId <> cmw.CityId AND lpo.ProductTypeId	= 3 AND lpo.requireInstall = 0	THEN stall.ScCover -- trường hợp CK ko lắp đặt 	
246												WHEN PO.DeliveryFlowId	=2		AND ISNULL(stall.Is3PlConfig,0) = 1 AND s.DeliveryCityId = cmw.CityId AND lpo.ProductTypeId	= 1 THEN PO.SID_Tally
247												WHEN PO.DeliveryFlowId	= 2		and lpo.HangTraiTinhKhongcoSc = 1 THEN	ISNULL(sw.SID,0)
248												WHEN PO.DeliveryFlowId	= 2	and lpo.HangTraiTinhKhongcoSc = 0	THEN	s.SID_Destination
249												WHEN PO.DeliveryFlowId	= 3		THEN	PO.SID_Tally
250											END,
251						RouteId			= ISNULL(lpo.TallyRouts	,0),
252						DeliveryRoutsId =  CASE WHEN PO.DeliveryFlowId	= 2	AND s.DeliveryCityId <> cmw.CityId AND lpo.ProductTypeId	= 3 AND lpo.requireInstall = 0 THEN [dbo].[GetRouts](s.DeliveryWardId,  stall.ScCover,2)
253										   ELSE  ISNULL(lpo.DesRouts,0)
254										   END
255			 --select CM.IsSuperMarket , PO.DeliveryFlowId
256						OUTPUT	INSERTED.PoId, INSERTED.DeliveryFlowId,inserted.SoId, city.RegionId,ISNULL(Descity.RegionId,0), INSERTED.SID_Tally, inserted.SID_Destination, st.STID, st.ScCover
257						INTO	@PoTableSC	(PoId, DeliveryFlowId,Soid,regionTally,regionDestination,statlly,sdes,stationTypeId,ScoverId)  
258			 FROM  PurchaseOrder PO
259				INNER JOIN @PoTable						lpo						ON lpo.PoId = PO.PoId
260				INNER JOIN SO							s		WITH(NOLOCK)	ON s.SOID = PO.SoId
261				LEFT JOIN	dbo.Station_Ward			sw		WITH(NOLOCK)	ON  sw.WardId = s.DeliveryWardId AND sw.TypeId = 2 --tram giao hàng
262				INNER JOIN	dbo.ClientMerchantWarehouse	cmw		WITH(NOLOCK)	ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
263				LEFT JOIN	AD_City						city	with(nolock)	ON	city.CityId = cmw.CityId
264				INNER JOIN	AD_City						Descity	with(nolock)	ON	Descity.CityId = s.DeliveryCityId		
265				LEFT JOIN	Stations					st		WITH(NOLOCK)	ON	st.SID		= sw.SID and st.STID In (1,2) --xác dinh chỉ SC và TGN bình thường
266				INNER JOIN	Stations					stall	WITH(NOLOCK)	ON	stall.SID= PO.SID_Tally and stall.STID in (1,2)
267				LEFT JOIN	dbo.Station_Ward			swtally	WITH(NOLOCK)	ON  swtally.WardId = cmw.WardId AND swtally.TypeId = 1 --tram lấy hàng
268				INNER JOIN	Stations					stallsw	WITH(NOLOCK)	ON	stallsw.SID= swtally.SID and stallsw.STID in (1,2)
269			 WHERE	
270						PO.POStatusId = 1
271				    AND PO.DeliveryFlowId = @currentstatus
272					AND ISNULL(st.Visible,1) = 1
273			if(@@ROWCOUNT = 0)
274						BEGIN	
275							SET @result = 0;
276							SET @ResponseMessage = N'Update row = 0'; 
277							SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
278							ROLLBACK TRAN T
279							RETURN
280						END	
281			--IF(@currentstatus = 2) -- trường hợp chuyển về trạm NCC xử lý
282			--	BEGIN
283			--		Select
284			--		@tempStationtypeId = ISNULL(stationTypeId,0),
285			--		@tempScCover = ISNULL(ScoverId,0),
286			--		@temsdes = isnull(sdes,0)
287			--		FROM @PoTableSC
288
289			--		If((@tempStationtypeId = 0 AND @tempScCover =0) OR @temsdes = 0)		
290			--		BEGIN
291			--		SET	@result = 0;
292			--		Set @ResponseMessage =N'Trạm giao hàng chưa xác định, vui lòng cấu hình trạm cho địa chỉ giao hàng.'
293			--		ROLLBACK TRAN T
294			--		SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
295			--		RETURN
296			--		END
297			--	END
298			--xử lý trường hợp chưa có trạm giao Cover
299		
300			--- cập nhập loại PO
301			 UPDATE PurchaseOrder 
302			 SET
303				DeliveryCaseId = 	CASE
304										WHEN ((sta.CityID = sde.CityID) AND (sta.SID = sde.SID)) AND po.DeliveryFlowId IN (3,4) THEN 3
305										WHEN ((sta.CityID = sde.CityID) AND (sta.SID = sde.SID)) AND NOT po.DeliveryFlowId IN (3,4) THEN po.DeliveryFlowId
306										WHEN ((sta.CityID = sde.CityID) AND (sta.SID <> sde.SID)) THEN 4 -- Trung chuyển trạm giao nhận nội vùng
307										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
308									ELSE	
309											0	
310									END	,
311				SID_Destination = CASE WHEN  po.DeliveryFlowId IN (3,4) and posc.regionTally <> posc.regionDestination and posc.stationTypeId = 2 THEN sta.ScCover
312									   WHEN  po.DeliveryFlowId IN (3,4) and ISNULL(SC.IsSortingMaster,0) = 1 and posc.stationTypeId = 2 THEN SC.SID
313									   WHEN po.DeliveryFlowId IN (3,4) and posc.stationTypeId = 2 THEN 
314										ISNULL((Select top 1 s.SID from Stations	s with(nolock) 
315											INNER JOIN	AD_City				Descity	with(nolock)	ON	s.CityId = Descity.CityId		
316											where s.STID =2
317											and s.Visible = 1
318											and Descity.RegionId = posc.regionTally 
319										),SID_Destination)	-- lấy trạm master của vùng miền
320								   ELSE
321										SID_Destination
322								   END --trường hợp cho đổi luồng về SC
323				
324			  FROM  PurchaseOrder				PO	WITH(NOLOCK)
325					--INNER JOIN @PoTable			lpo					ON	lpo.PoId			=	PO.PoId
326					INNER JOIN @PoTableSC		posc						ON	posc.PoId					=	po.PoId
327					INNER JOIN	dbo.Stations	sta			WITH(NOLOCK)	ON	posc.statlly				=	sta.SID AND sta.Visible = 1 and sta.STID in (1,2)
328					INNER JOIN	dbo.Stations	sde			WITH(Nolock)	ON	posc.sdes					=	sde.SID AND sde.Visible = 1 and sde.STID in (1,2)
329					LEFT  JOIN	dbo.Stations	SC			WITH(NOLOCK)	ON	ISNULL(sta.ScCover,0)		=	SC.SID AND SC.Visible = 1 and SC.STID in (1,2)
330					LEFT  JOIN	dbo.Stations	SCMaster	WITH(NOLOCK)	ON	ISNULL(SCMaster.SID,0)		=	PO.SID_Destination and SCMaster.STID in (1,2)
331				IF(@@ROWCOUNT = 0)
332						BEGIN	
333							SET @result = 0;
334							SET @ResponseMessage = N'Địa chỉ giao hàng chưa được cấu hình trạm.'; 
335							SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
336							ROLLBACK TRAN T
337							RETURN
338						END	
339			 SET @result = 1;
340			 SET @ResponseMessage = N'Đổi thành công luồng đơn hàng.';
341			 --Insert Log_Information ActionType PO
342			 INSERT INTO [dbo].[Log_Information]
343							 ([ObjectTypeId]
344							 ,[ObjectId]
345							 ,[ActionId]
346							 ,[LogDetails]
347							 ,[CreatedBy]
348							 ,[CreatedDate]
349							 ,[UID_AssignTo]
350							 ,[OldStatusId]
351							 ,[NewStatusId]
352							 ,[StationId]
353							 )
354			 SELECT DISTINCT 3, sd.SODetailID, 22,
355				CASE
356				WHEN @newStatus = 2 THEN N'Đổi thành công luồng đơn hàng thẳng thường.' 
357				WHEN @newStatus = 3 THEN N'Đổi thành công luồng đơn hàng nhập trạm.'
358				END,
359				@userId, GETDATE(), 0, 0,0,@StationId
360				 
361			 FROM @PoTable sltb
362			 INNER JOIN dbo.SO_Detail	sd		WITH(NOLOCK)	ON sltb.PoId = sd.PoId
363
364			 --thêm ghi chu đặt biệt
365			  INSERT	INTO dbo.SpecialNotes
366				 (
367				     --Id - this column value is auto-generated
368				     CreatedBy,
369				     FullName,
370				     DepartmentName,
371				     CreatedDate,
372				     Note,
373				     ClientSoId,
374				     SoId
375				 )
376				SELECT	DISTINCT
377						@userId,
378						@UserName,
379						N'TMS', -- DepartmentName - nvarchar
380						Getdate(), -- CreatedDate - datetime
381						CASE
382						WHEN @newStatus = 2 THEN N'Đổi thành công luồng đơn hàng thẳng thường.' 
383						WHEN @newStatus = 3 THEN N'Đổi thành công luồng đơn hàng nhập trạm.'
384						END, -- Note - nvarchar
385						s.ClientSoId, -- ClientSoId - bigint
386						s.SOID -- SoId - bigint
387				FROM @PoTable sltb
388				INNER JOIN	SO s				WITH(NOLOCK)    ON sltb.SoId =s.SOID
389				COMMIT TRAN T
390			 SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage;
391			END	
392		ELSE
393			BEGIN
394				--lấy 2 trạm vinpro	
395				UPDATE PO 
396				 SET 
397						DeliveryFlowId				= @newStatus,
398						PO.SID_Tally				= ISNULL(sm.StationId,[dbo].[GetStationNotVinpro](cmw.ClientMerchantId,cmw.ClientMerchantWarehouseId, '1'
399														, po.ProductTypeId, 1, s.DeliveryCityId)),
400						PO.SID_Destination			= ISNULL(sm.StationId,[dbo].[GetStationNotVinpro](cmw.ClientMerchantId,cmw.ClientMerchantWarehouseId, '1'
401														, po.ProductTypeId, 1, s.DeliveryCityId)),
402						PO.RouteId					=[dbo].[GetRouts](cmw.WardId,ISNULL(sm.StationId,[dbo].[GetStationNotVinpro](cmw.ClientMerchantId,cmw.ClientMerchantWarehouseId, '1'
403														, po.ProductTypeId, 1, s.DeliveryCityId)),1),
404						PO.DeliveryRoutsId			= [dbo].[GetRouts](s.DeliveryWardId,ISNULL(sm.StationId,[dbo].[GetStationNotVinpro](cmw.ClientMerchantId,cmw.ClientMerchantWarehouseId, '1'
405														, po.ProductTypeId, 1, s.DeliveryCityId)),2)
406				 --select CM.IsSuperMarket , PO.DeliveryFlowId  
407				 FROM  PurchaseOrder PO							WITH(NOLOCK)
408					INNER JOIN @PoTable					lpo						ON lpo.PoId = PO.PoId
409					INNER JOIN ClientMerchantWarehouse	cmw		WITH(NOLOCK)	ON PO.ClientMerchantWarehouseId= cmw.ClientMerchantWarehouseId
410					INNER JOIN SO						s		WITH(NOLOCK)	ON s.SOID = PO.SoId
411					INNER JOIN dbo.ClientMerchant		cm		WITH(NOLOCK)	ON cm.ClientMerchantId = cmw.ClientMerchantId
412					LEFT JOIN dbo.Station_Merchant		sm		WITH(NOLOCK)	ON sm.ClientMerchantId = cm.ClientMerchantId	AND sm.ClientMerchantWarehouseId = cmw.ClientMerchantWarehouseId		
413					--LEFT JOIN   Routs					rTally	WITH(NOLOCK)	ON	rTally.SId =sm.StationId	 AND rTally.RouteTypeId = 1 -- tuyến lấy
414					--LEFT JOIN	RoutDetail				rdTally	WITH(NOLOCK)	ON	rdTally.WardId = cmw.WardId  AND rdTally.RoutId = rTally.RoutId
415					--LEFT JOIN	Routs					rDes	WITH(NOLOCK)	ON	rDes.SId	=sm.StationId	 AND rDes.RouteTypeId = 2 -- tuyến giao
416					--LEFT JOIN	RoutDetail				rdDes	WITH(NOLOCK)	ON	rDes.RoutId = rdDes.RoutId	 AND rdDes.WardId = s.DeliveryWardId
417				 WHERE	
418							PO.POStatusId = 1
419						AND PO.DeliveryFlowId = @currentstatus 
420
421				if(@@ROWCOUNT = 0)
422						BEGIN	
423							SET @result = 0;
424							SET @ResponseMessage = N'Đổi luồng thất bại -kiểm tra cấu hình trạm NCC.'; 
425							SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage
426							RETURN
427						END	
428					 SET @ResponseMessage = N'Đổi thành công luồng đơn hàng.';
429				 --Insert Log_Information ActionType PO
430				 INSERT INTO [dbo].[Log_Information]
431								 ([ObjectTypeId]
432								 ,[ObjectId]
433								 ,[ActionId]
434								 ,[LogDetails]
435								 ,[CreatedBy]
436								 ,[CreatedDate]
437								 ,[UID_AssignTo]
438								 ,[OldStatusId]
439								 ,[NewStatusId]
440								 ,[StationId]
441								 )
442				 SELECT DISTINCT 3, sd.SODetailID, 22,
443					CASE
444					WHEN @newStatus = 2 THEN N'Đổi thành công luồng đơn hàng thẳng thường và PO về trạm NCC xử lý.' 
445					WHEN @newStatus = 3 THEN N'Đổi thành công luồng đơn hàng nhập trạm.'
446					END,
447					@userId, GETDATE(), 0, 0,0,@StationId
448				 FROM @PoTable sltb
449				 INNER JOIN dbo.SO_Detail sd WITH(NOLOCK) ON sltb.PoId = sd.PoId
450
451				 --thêm ghi chu đặt biệt
452				  INSERT	INTO dbo.SpecialNotes
453					 (
454						 --Id - this column value is auto-generated
455						 CreatedBy,
456						 FullName,
457						 DepartmentName,
458						 CreatedDate,
459						 Note,
460						 ClientSoId,
461						 SoId
462					 )
463					SELECT	DISTINCT
464							@userId,
465							@UserName,
466							N'TMS', -- DepartmentName - nvarchar
467							Getdate(), -- CreatedDate - datetime
468							CASE
469							WHEN @newStatus = 2 THEN N'Đổi thành công luồng đơn hàng thẳng thường và PO về trạm NCC xử lý.' 
470							WHEN @newStatus = 3 THEN N'Đổi thành công luồng đơn hàng nhập trạm.'
471							END, -- Note - nvarchar
472							s.ClientSoId, -- ClientSoId - bigint
473							s.SOID -- SoId - bigint
474					FROM @PoTable sltb
475						INNER JOIN	SO s				WITH(NOLOCK)    ON sltb.SoId =s.SOID
476				 SELECT @result AS IsSuccess, @ResponseMessage AS ResponseMessage;
477				END	
478			 
479	
480END