USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_DE_UpdateDeliverySlip_V01]    Script Date: 1/9/2016 12:41:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Hoàng Đình Khiêm
-- Create date: 13/05/2015
-- Description:	Cập nhật trạng thái giao hàng
-- =============================================
ALTER PROCEDURE [dbo].[APIClient_DE_UpdateDeliverySlip_V01]
	@CodId	INT,
	@StationId INT,
	@CurrentUser INT,
	@SesstionKey VARCHAR(250),
	@DESDetail NVARCHAR(MAX),
	@IsState INT
AS
BEGIN
	DECLARE	@PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
	--Tạo Table temp
	CREATE TABLE #TableOpenXml
	(
		DeliverySlipsId			INT,
		DeliverySlipsDetailId	BIGINT,
		QuantityCompleted		INT,
		QuantityCanceled		INT,
		QuantityAgain			INT,
		QuantityFailing			INT,
		ReasonAgain				INT,
		ReasonCanceled			INT,
		IsCompleted				BIT,
		IsAgain					BIT,
		IsCanceled				BIT	,
		DateAgain				DATETIME,
		DateAgainTo				DATETIME,
		[Status]				INT,
		ReasonAgainOrther		NVARCHAR(250),
		ReasonCanceledOrther	NVARCHAR(250),
		SoDetailId				BIGINT,
		DeliveryFlowId			INT,
		CodId					INT,
		ClientSoId				BIGINT,
		SoId					BIGINT,
		Note					NVARCHAR(500)
	)
	BEGIN TRY
		EXEC	[dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
		--DECLARE @RetryCounter INT
		--SET @RetryCounter = 1
		--RETRY: -- Label RETRY
		BEGIN TRAN T
			BEGIN TRY-------------------------------------------------------------------------------------BEGIN TRY-------------------------------------------------->
				--Deadlock------------------------------------------
				UPDATE LockTableSoDetail SET COUNT=1 WHERE ID=1 --
				----------------------------------------------------
				BEGIN
					DECLARE @DeliverySlipsId INT
					DECLARE @CashId INT
					DECLARE @CodDe INT
					DECLARE @idoc int
					DECLARE @Counter INT
					EXEC sp_xml_preparedocument @idoc OUTPUT, @DESDetail

					--INSERT TO TABLE TEMP---------------------------------------------------------------BEGIN------------------------------------------------------->
					INSERT INTO #TableOpenXml (DeliverySlipsId, DeliverySlipsDetailId,QuantityCompleted,QuantityCanceled,QuantityAgain,QuantityFailing,ReasonAgain,ReasonCanceled,
												IsCompleted,IsAgain,IsCanceled,DateAgain,DateAgainTo,[Status],ReasonAgainOrther,ReasonCanceledOrther,SoDetailId,DeliveryFlowId,CodId,ClientSoId,SoId, Note)
					SELECT	DeliverySlipsId, DeliverySlipsDetailId,QuantityCompleted,QuantityCanceled,QuantityAgain,QuantityFailing,ReasonAgain,ReasonCanceled,
							IsCompleted,IsAgain,IsCanceled,DateAgain,DateAgainTo,[Status],ReasonAgainOrther,ReasonCanceledOrther,SoDetailId,DeliveryFlowId,CodId,ClientSoId,SoId, Note
					FROM	OPENXML (@idoc, '/ArrayOfDeSlipsBo/DeSlipsBo', 8)
							WITH
								(
									DeliverySlipsId			INT		'DeliverySlipsId',
									DeliverySlipsDetailId	BIGINT	'DeliverySlipsDetailId',
									QuantityCompleted		INT		'QuantityCompleted',
									QuantityCanceled		INT		'QuantityCanceled',
									QuantityAgain			INT		'QuantityAgain',
									QuantityFailing			INT		'QuantityFailing',
									ReasonAgain				INT		'ReasonAgain',
									ReasonCanceled			INT		'ReasonCanceled',
									IsCompleted				BIT		'IsCompleted',
									IsAgain					BIT		'IsAgain',
									IsCanceled				BIT		'IsCanceled',
									DateAgain				DATETIME 'DateAgain',
									DateAgainTo				DATETIME 'DateAgainTo',
									[Status]				INT		'Status',
									ReasonAgainOrther		NVARCHAR(250) 'ReasonAgainOrther',
									ReasonCanceledOrther	NVARCHAR(250) 'ReasonCanceledOrther',
									SoDetailId				BIGINT	'SoDetailId',
									DeliveryFlowId			INT		'DeliveryFlowId',
									CodId					INT		'CodId',
									ClientSoId				BIGINT	'ClientSoId',
									SoId					BIGINT	'SoId',
									Note					NVARCHAR(500) 'Note'
								)
					-------------------------------------------------------------------------------------END--------------------------------------------------------->
					IF (@IsState = 2)		-->LƯU HOÀN TẤT
					BEGIN--------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						--CHECK DSGH ĐÃ ĐƯỢC LƯU Ở SECTION KHÁC HAY CHƯA
						SELECT	@Counter = COUNT(sd.ClientSoId)
						FROM	#TableOpenXml tox INNER JOIN dbo.SO_Detail sd ON tox.SoDetailId = sd.SoDetailId
						WHERE	sd.SODetailStatusID >= 13

						IF (@Counter = 0)
						BEGIN
						--1. CẬP NHẬT TRẠNG THÁI CHI TIẾT PHIẾU: DE_DeliverySlipsDetails
						UPDATE	DE_DeliverySlipsDetails
						SET
								IsCompleted			=	D.IsCompleted,
								IsAgain				=	D.IsAgain,
								IsCanceled			=	D.IsCanceled,
								DateCompleted		=	CASE WHEN D.IsCompleted	= 1 THEN GETDATE()				ELSE NULL	END,
								DateAgain			=	CASE WHEN D.IsAgain		= 1 THEN D.DateAgain			ELSE NULL	END,
								DateAgainTo			=	CASE WHEN D.IsAgain		= 1 THEN D.DateAgainTo			ELSE NULL	END,
								DateCanceled		=	CASE WHEN D.IsCanceled	= 1 THEN GETDATE()				ELSE NULL	END,
								QuantityCompleted	=	CASE WHEN D.IsCompleted	= 1 THEN D.QuantityCompleted	ELSE 0		END,
								QuantityAgain		=	CASE WHEN D.IsAgain		= 1 THEN D.QuantityAgain		ELSE 0		END,
								QuantityCanceled	=	CASE WHEN D.IsCanceled	= 1 THEN D.QuantityCanceled		ELSE 0		END,
								QuantityFailing		=	D.QuantityFailing,
								ReasonAgain			=	CASE WHEN D.IsAgain		= 1 THEN D.ReasonAgain			ELSE 0		END,
								ReasonCanceled		=	CASE WHEN D.IsCanceled	= 1 THEN D.ReasonCanceled		ELSE 0		END,
								ModifiedBy			=	@CurrentUser,
								DateModified		=	GETDATE(),
								[Status]			=	29,
								ReasonAgainOrther	=	CASE WHEN D.IsAgain		= 1 THEN D.ReasonAgainOrther	ELSE NULL	END,
								ReasonCanceledOrther=	CASE WHEN D.IsCanceled	= 1 THEN D.ReasonCanceledOrther ELSE NULL	END,
								DateSaveTemp		=	CASE WHEN DED.DateSaveTemp IS NULL THEN GETDATE() ELSE DED.DateSaveTemp END,
								Note				=	D.Note
						FROM
								DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--2. TRƯỜNG HỢP HOÀN THÀNH HẾT
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF(EXISTS(SELECT 1 FROM  #TableOpenXml 	WHERE IsCompleted =	1	AND IsAgain =	0 AND IsCanceled =	0))
						BEGIN
							--UPDATE SO_Detail	-----------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
							UPDATE	SO_Detail
							SET		SODetailStatusID	=	13,	--> Đã giao cho khách hàng
									Visible				=	1,
									SoDetailIdRoot		=	CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
									SoDetailStatusLastUpdateTime=GETDATE()
							FROM	SO_Detail sd WITH (NOLOCK) INNER JOIN #TableOpenXml D ON sd.SODetailID=D.SoDetailId
							WHERE
										D.IsCompleted	=	1	AND
										D.IsAgain		=	0	AND
										D.IsCanceled	=	0
							--GHI LOG
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,D.SoDetailId,
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Cập nhật : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
							FROM	#TableOpenXml D
							WHERE
									D.IsCompleted		=	1	AND
									D.IsAgain			=	0	AND
									D.IsCanceled		=	0
							--HÀNG BÌNH THƯỜNG DeliveryFlow = 3 (Giao phải nhập trạm):
							--UPDATE So_Detail_Serial: StatusId = 62 trong trường hợp sản phẩm có quản lý số serial và sản phẩm đó hoàn tất hêt
							;WITH SoDetailWithSerial AS
							(
								SELECT	sd.SoDetailId, sd.ParentSoDetailId
								FROM	SO_Detail sd WITH (NOLOCK)
										INNER JOIN	#TableOpenXml AS s ON sd.SODetailID = s.SoDetailId
								WHERE
										s.IsCompleted	=	1	AND		-->Trường hợp hoàn thành hết
										s.IsAgain		=	0	AND
										s.IsCanceled	=	0
								UNION ALL
								SELECT	 sd.SoDetailId,  sd.ParentSoDetailId
								FROM	dbo.SO_Detail sd WITH (NOLOCK)
										INNER JOIN SoDetailWithSerial sdr ON sdr.ParentSoDetailId = sd.SoDetailId
							), SoDetail AS
							(
								SELECT	sds.SoDetailId,sds.SDSId
								FROM SoDetailWithSerial s INNER JOIN dbo.SO_Detail_Serial sds ON s.SoDetailId=sds.SODetailID
							)
							UPDATE	SO_Detail_Serial
							SET		StatusId = 62, -- ĐÃ GIAO CHO KHÁCH HÀNG
									RollBackSerial = 0
							FROM	SO_Detail_Serial sds
									INNER JOIN SoDetail sd WITH (NOLOCK) ON sds.SDSId = sd.SDSId
							WHERE	IsNull(sds.StatusId,60) IN  (59,60)

							--UPDATE	SO_Detail_Serial
							--SET		StatusId = 62 -- ĐÃ GIAO CHO KHÁCH HÀNG
							--FROM	#TableOpenXml tox	INNER JOIN SO_Detail sd			ON sd.SODetailID=tox.SoDetailId
							--							INNER JOIN SO_Detail_Serial sds ON sds.SODetailID=sd.SoDetailIdRoot
							--WHERE	IsNull(sds.StatusId,60) IN  (59,60)
							-------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--3. TRƯỜNG HỢP HẸN LẠI HẾT
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	0	AND IsAgain =	1 AND IsCanceled =	0))
						BEGIN
							--Update note vào SoDetails
							UPDATE	SO_Detail
							SET		IsDeliveryAgain		=	1,
									IsDeliveryDateAgain	=	1,
									UserModify			=	@CurrentUser,
									IsPushCS			=	CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
									Note				=	CASE WHEN sd.Note IS NOT NULL THEN N'Hẹn lại. ' + sd.Note ELSE N'Hẹn lại. ' END,
									DeliveryDateFrom	=	D.DateAgain,
									DeliveryDateTo		=	D.DateAgainTo,
									Visible				=	1,
									SoDetailIdRoot		=	CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
									SoDetailStatusLastUpdateTime=GETDATE(),
									DeliveryPeriodTime	=	CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime)  END,
									DeliveryFromTimeUpdateTime = GETDATE()

							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
													LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
							WHERE
									D.IsCompleted	=	0	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	0
							-- =>GHI LOG
							INSERT INTO dbo.Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,D.SoDetailId,
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Hẹn lại : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
							FROM	#TableOpenXml D
							WHERE
									D.IsCompleted		=	0	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	0
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--4. TRƯỜNG HỢP HỦY HẾT
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	0	AND	IsAgain	 =	0	AND	IsCanceled = 1))
						BEGIN
							--UPDATE SO_Detail	-----------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
							UPDATE	SO_Detail
							SET		SODetailStatusID	=	16,		--==> Hủy
									ReasonID			=	D.ReasonCanceled,
									ReasonCanceledOrther=	D.ReasonCanceledOrther,
									Visible				=	1,
									SoDetailIdRoot		=	CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,
									SoDetailStatusLastUpdateTime=GETDATE()
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
							WHERE
								D.IsCompleted		=	0	AND
								D.IsAgain			=	0	AND
								D.IsCanceled		=	1
							--GHI LOG
							INSERT INTO dbo.Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,D.SoDetailId,
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Cập nhật : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
							FROM	#TableOpenXml D
							WHERE	D.IsCompleted		=	0	AND
									D.IsAgain			=	0	AND
									D.IsCanceled		=	1
							-------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--5. TRƯỜNG HỢP HOÀN THÀNH + HẸN LẠI + HỦY
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	1	AND	IsAgain	=	1	AND IsCanceled	=	1))
						BEGIN
							--UPDATE SO_Detail
							UPDATE	SO_Detail
							SET		Visible	=	0,
									SoDetailStatusLastUpdateTime=GETDATE()
							WHERE	SoDetailId IN	(SELECT DS.SoDetailId
													 FROM	#TableOpenXml DS INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = DS.DeliverySlipsDetailId)
													 WHERE	DS.IsCompleted	=	1 AND
															DS.IsAgain		=	1 AND
															DS.IsCanceled	=	1)
							--INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot,SoDetailStatusLastUpdateTime,
								CustomerRequestTime,CommitTime,FreshFoodTypeId
							)
							SELECT
								sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoàn thành.',13,
								0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
								1,sd.RequireSerial,
								0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
								0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
								D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
								sd.CustomerRequestTime,sd.CommitTime,sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
							WHERE	D.IsCompleted	=	1	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	1
							--Ghi log HOÀN THÀNH
							INSERT INTO dbo.Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
							WHERE
									D.IsCompleted			=	1	AND
									D.IsAgain				=	1	AND
									D.IsCanceled			=	1	AND
									sd.SODetailStatusID		=	13
							--INSERT SO_Detail TRƯỜNG HỢP HẸN LẠI
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify,IsDeliveryDateAgain
							)
							SELECT
								sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
								D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
								2,sd.RequireSerial,
								0,
								0,--D.QuantityAgain * sd.SellPrice,
								D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
								sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
								CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime)  END,
								GETDATE(),
								CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
								@CurrentUser, 1
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
													LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
							WHERE	D.IsCompleted	=	1	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	1
							--Ghi log HẸN LẠI
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
							WHERE
									D.IsCompleted		=	1	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	7
							--INSERT SO_Detail TRƯỜNG HỢP HỦY
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId
							)
							SELECT
								sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
								D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
								3,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
								sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
							WHERE	D.IsCompleted	=	1	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	1
							-- Ghi log HỦY
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
							WHERE	D.IsCompleted		=	1	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	16
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--6. TRƯỜNG HỢP HOÀN THÀNH + HẸN LẠI
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	1	AND IsAgain	 =	1	AND IsCanceled	=	0))
						BEGIN
							--UPDATE SO_Detail
							UPDATE	SO_Detail
							SET		Visible	=	0,
									SoDetailStatusLastUpdateTime=GETDATE()
							WHERE	SoDetailId IN (	SELECT	D.SoDetailId
													FROM	#TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
													WHERE	D.IsCompleted	=	1 AND
															D.IsAgain		=	1 AND
															D.IsCanceled	=	0 )
							--INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId
							)
							SELECT
								sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoàn thành.',13,
								0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
								1,sd.RequireSerial,
								0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
								0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
								D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
								sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
							WHERE	D.IsCompleted	=	1	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	0
							--Ghi log HOÀN THÀNH
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
							WHERE	D.IsCompleted		=	1	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	0	AND
									sd.SODetailStatusID	=	13
							--HẸN LẠI
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify, IsDeliveryDateAgain
							)
							SELECT
								sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
								D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
								2,sd.RequireSerial,
								0,
								0,--D.QuantityAgain * sd.SellPrice,
								D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
								sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
								CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime)  END,
								GETDATE(),
								CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
								@CurrentUser, 1
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
													LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
							WHERE	D.IsCompleted	=	1	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	0
							--Ghi log HẸN LẠI
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
							WHERE
									D.IsCompleted		=	1	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	0	AND
									sd.SODetailStatusID	=	7
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--7. TRƯỜNG HỢP HOÀN THÀNH + HỦY
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	1	AND	IsAgain	=	0	AND	IsCanceled	= 1))
						BEGIN
							--UPDATE SO_Detail
							UPDATE	SO_Detail
							SET		Visible	=	0,
									SoDetailStatusLastUpdateTime=GETDATE()
							WHERE	SoDetailId IN (	SELECT	D.SoDetailId
													FROM	#TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
													WHERE	D.IsCompleted	=	1 AND
															D.IsAgain		=	0 AND
															D.IsCanceled	=	1 )
							--INSERT SO_Detail TRƯỜNG HỢP HOÀN THÀNH
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId
							)
							SELECT	sd.LogisticSKULabel, ISNULL(D.QuantityCompleted,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hoàn thành.',13,
									0,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
									1,sd.RequireSerial,
									0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN sd.TotalPaid ELSE D.QuantityCompleted*sd.SellPrice END,
									0,--CASE WHEN sd.TotalPaid < D.QuantityCompleted*sd.SellPrice THEN D.QuantityCompleted*sd.SellPrice - sd.TotalPaid ELSE 0 END,
									D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
									sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
							WHERE	D.IsCompleted		=	1	AND
									D.IsAgain			=	0	AND
									D.IsCanceled		=	1
							--Ghi log HOÀN THÀNH
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,13,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
							WHERE	D.IsCompleted		=	1	AND
									D.IsAgain			=	0	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	13
							--INSERT SO_Detail TRƯỜNG HỢP HỦY
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId
							)
							SELECT	sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
									D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
									2,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
									sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = D.SoDetailId
							WHERE
									D.IsCompleted	=	1	AND
									D.IsAgain		=	0	AND
									D.IsCanceled	=	1
							--Ghi log HỦY
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
							FROM	#TableOpenXml D INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId = D.SoDetailId
							WHERE
									D.IsCompleted		=	1	AND
									D.IsAgain			=	0	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	16
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--8. TRƯỜNG HỢP HẸN LẠI + HỦY
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						IF (EXISTS(SELECT 1 FROM #TableOpenXml WHERE IsCompleted =	0	AND	IsAgain	=	1	AND	IsCanceled	=	1))
						BEGIN
							--UPDATE SO_Detail	---------------------------------------------------------BEGIN UPDATE SO_Detail-------------------------------------->
							UPDATE	SO_Detail
							SET		Visible	=	0,
									SoDetailStatusLastUpdateTime=GETDATE()
							WHERE	SoDetailId IN (	SELECT	D.SoDetailId
													FROM	#TableOpenXml D INNER JOIN DE_DeliverySlipsDetails DED ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
													WHERE	D.IsCompleted	=	0	AND
															D.IsAgain		=	1	AND
															D.IsCanceled	=	1	)
							--INSERT SO_Detail TRƯỜNG HỢP HẸN LẠI
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,IsDeliveryAgain,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId,DeliveryPeriodTime,DeliveryFromTimeUpdateTime,IsPushCS,UserModify, IsDeliveryDateAgain
							)
							SELECT	sd.LogisticSKULabel, ISNULL(D.QuantityAgain,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hẹn lại.',7,
									D.ReasonAgain,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,D.DateAgain,D.DateAgainTo,1,
									1,sd.RequireSerial,
									0,
									0,--D.QuantityAgain * sd.SellPrice,
									D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,1,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
									sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId,
									CASE WHEN D.IsAgain=1 THEN (SELECT TOP 1 dpt.Name FROM DeliveryPeriodTime dpt WHERE CONVERT(CHAR(8), D.DateAgain, 108) > dpt.FromTime AND CONVERT(CHAR(8), D.DateAgain, 108) <= dpt.ToTime)  END,
									GETDATE(),
									CASE WHEN ddsd.IsCallCustomerFail = 1 THEN 1 ELSE 0 END,
									@CurrentUser, 1
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK)	ON sd.SODetailID = D.SoDetailId
													LEFT JOIN DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON (ddsd.SoDetailId = sd.SODetailID AND ddsd.DeliverySlipsId = D.DeliverySlipsId)
							WHERE	D.IsCompleted	=	0	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	1
							--Ghi log HẸN LẠI
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,7,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=D.SoDetailId
							WHERE	D.IsCompleted		=	0	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	7
							--INSERT SO_Detail TRƯỜNG HỢP HỦY
							INSERT INTO SO_Detail
							(
								LogisticSKULabel, Amount,SellPrice,UnitPriceExclVat,AdrVat,DiscountPercent,UnitPriceExclVatDiscount,SOId,Note,SODetailStatusID,
								ReasonID,ManufactureBarcode,ClientMerchantId,ClientMerchantWarehouseId,ClientProductCateId,ClientProductId,DeliveryDateFrom,DeliveryDateTo,Visible,
								[Priority],RequireSerial,TotalPaid,RemainingAmount,ParentSoDetailId,ClientPoId,ClientSoId,PoId,MTID,ProductTypeID,UnitBuyPrice,SID_CurrentStation,ReasonCanceledOrther,SoDetailIdRoot, SoDetailStatusLastUpdateTime,
								CustomerRequestTime, CommitTime, FreshFoodTypeId
							)
							SELECT	sd.LogisticSKULabel, ISNULL(D.QuantityCanceled,0),sd.SellPrice,sd.UnitPriceExclVat,sd.AdrVat,sd.DiscountPercent,sd.UnitPriceExclVatDiscount,sd.SOId,N'Hủy.',16,
									D.ReasonCanceled,sd.ManufactureBarcode,sd.ClientMerchantId,sd.ClientMerchantWarehouseId,sd.ClientProductCateId,sd.ClientProductId,sd.DeliveryDateFrom,sd.DeliveryDateTo,1,
									1,sd.RequireSerial,0,0,D.SoDetailId,sd.ClientPoId,sd.ClientSoId,sd.PoId,sd.MTID,sd.ProductTypeID,sd.UnitBuyPrice,sd.SID_CurrentStation,D.ReasonCanceledOrther,CASE WHEN sd.SoDetailIdRoot IS NULL THEN sd.SODetailID ELSE sd.SoDetailIdRoot END,GETDATE(),
									sd.CustomerRequestTime, sd.CommitTime, sd.FreshFoodTypeId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.SODetailID=D.SoDetailId
							WHERE	D.IsCompleted	=	0	AND
									D.IsAgain		=	1	AND
									D.IsCanceled	=	1
							-- Ghi log HỦY
							INSERT INTO Log_Information
							(
								ObjectTypeId,ObjectId,ActionId,LogDetails,CreatedBy,CreatedDate,UID_AssignTo,OldStatusId,NewStatusId,StationId
							)
							SELECT	3,ISNULL(sd.SODetailID,0),
									CASE
										WHEN D.DeliveryFlowId = 1 THEN 15
										WHEN D.DeliveryFlowId = 2 THEN 23
										WHEN D.DeliveryFlowId = 3 THEN 14
										WHEN D.DeliveryFlowId = 4 THEN 25
									END,
									N'Sinh ra từ SoDetailId : ' + CAST(D.SoDetailId AS nvarchar(50)),@CurrentUser,GETDATE(),D.CodId,7,16,@StationId
							FROM	#TableOpenXml D INNER JOIN SO_Detail sd WITH (NOLOCK) ON sd.ParentSoDetailId=	D.SoDetailId
							WHERE
									D.IsCompleted		=	0	AND
									D.IsAgain			=	1	AND
									D.IsCanceled		=	1	AND
									sd.SODetailStatusID	=	16
							-----------------------------------------------------------------------------END UPDATE SO_Detail-----------------------------------------
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--9. XUẤT KHO ẢO ĐỐI VỚI HÀNH CK, CẬP NHẬT TRẠNG THÁI SO, CẬP NHẬT TRẠNG THÁI PHIẾU + TÍNH TIỀN + CẬP NHẬT LẠI TIỀN ĐÃ TRẢ
						--9.1 TRƯỜNG HỢP LÀ HÀNG CỒNG KỀNH (DeliveryFlowId = 2)
						IF(EXISTS(SELECT 1 FROM dbo.#TableOpenXml  WHERE	DeliveryFlowId	=	2 ))
						BEGIN
							--=>XUẤT KHO CHO CÁC SẢN PHẨM HOÀN THÀNH
							DECLARE @warehouseSlipId	BIGINT	= 0
							DECLARE @stationWHId		INT		= 0
							SELECT  @stationWHId = COALESCE([StationWHId],0) FROM [Station_WHType] WHERE [SID]= @StationId AND [SWHTID] = 1
									-- Tạo phiếu xuất kho
									DECLARE @CodViewByDe INT=0
									SELECT  @CodViewByDe = SOD.CodId	FROM dbo.#TableOpenXml SOD
																		WHERE SOD.DeliveryFlowId=2
																		GROUP BY SOD.DeliverySlipsId,SOD.CodId
									INSERT INTO WH_WarehouseSlips
									(
										UserIdRecipient,
										UserIdDelivery,
										StationWHId,
										ActionTypeId,
										DestinationStationId,
										IsCompleted,
										DateCompleted,
										CompletedBy
									)
									SELECT	@CurrentUser, @CodViewByDe, @stationWHId, 5, null, 1, GETDATE() , @CurrentUser
									SET @warehouseSlipId = @@IDENTITY
									-- Tạo chi tiết của phiếu xuất kho
									INSERT INTO [WH_WarehouseSlip_Details]
									(
										WarehouseSlipId,
										SoDetailId,
										Quantity,
										QuantityReceived
									)
									SELECT	@warehouseSlipId, SOD.SoDetailId, SOD.Quantity, SOD.Quantity
									FROM	OPENXML (@idoc, '/ArrayOfDeSlipsBo/DeSlipsBo', 8)
											WITH
											(
												SoDetailId			BIGINT	'SoDetailId',
												DeliverySlipsId		INT		'DeliverySlipsId',
												QuantityCompleted	INT		'QuantityCompleted',
												Quantity			INT		'Quantity',
												DeliveryFlowId		INT		'DeliveryFlowId'
											) SOD
									WHERE
											SOD.DeliveryFlowId		=	2
									--Ghi log
									INSERT INTO WH_DeliverySlip_Logs
									(
										DeliverySlipsId,
										WarehouseSlipId,
										CreatedBy
									)
									SELECT	DED.DeliverySlipsId,@warehouseSlipId, @CurrentUser
									FROM	dbo.#TableOpenXml DED
									WHERE	DED.DeliveryFlowId		=	2
									GROUP BY DED.DeliverySlipsId
									--INSERT WH_ImportSource
									INSERT INTO WH_ImportSource
									(
										ImportSourceId,
										WhSlipId,
										DateCreated,
										WhImportSourceTypeId
									)
									SELECT	SOD.DeliverySlipsId,@warehouseSlipId,GETDATE(),4
									FROM	dbo.#TableOpenXml SOD
									WHERE	SOD.DeliveryFlowId		=	2
									GROUP BY SOD.DeliverySlipsId
						END
						--9.2 CẬP NHẬT TRẠNG THÁI SO
						--Update SO
						UPDATE	dbo.SO
						SET		dbo.SO.SOStatusId = (SELECT st.Id FROM dbo.SOStatus st INNER JOIN (SELECT  MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
					   	FROM	dbo.SO s INNER JOIN #TableOpenXml AS Sml ON Sml.ClientSoId = s.ClientSoId
						--Ghi LOG
						--INSERT INTO [dbo].[Log_Information]
						--		([ObjectTypeId]
						--		,[ObjectId]
						--		,[ActionId]
						--		,[LogDetails]
						--		,[CreatedBy]
						--		,[CreatedDate]
						--		,[UID_AssignTo]
						--		,[OldStatusId]
						--		,[NewStatusId])
						--SELECT	1,
						--		s.ClientSoId,
						--		CASE
						--			WHEN Sml.DeliveryFlowId = 1 THEN 15
						--			WHEN Sml.DeliveryFlowId = 2 THEN 23
						--			WHEN Sml.DeliveryFlowId = 3 THEN 14
						--			WHEN Sml.DeliveryFlowId = 4 THEN 25
						--		END,
						--		NULL,
						--		@CurrentUser,
						--		GETDATE(),
						--		NULL,
						--		s.SOStatusId,
						--		(SELECT st.Id FROM dbo.SOStatus st WITH (NOLOCK) INNER JOIN (SELECT  MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 WITH (NOLOCK) ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)
						-- FROM	dbo.SO s WITH (NOLOCK) INNER JOIN #TableOpenXml AS Sml ON Sml.ClientSoId = s.ClientSoId
						-- WHERE	s.SOStatusId < (SELECT st.Id FROM dbo.SOStatus st WITH (NOLOCK) INNER JOIN (SELECT  MIN(st1.Priority) AS MinPriority FROM dbo.SO_Detail sd WITH (NOLOCK) INNER JOIN dbo.SOStatus st1 WITH (NOLOCK) ON sd.SODetailStatusID = st1.Id WHERE sd.ClientSoId = s.ClientSoId AND sd.Visible = 1) ts ON st.Priority = ts.MinPriority)

						--9.3 KIỂM TRA CẬP NHẬT PHIẾU GIAO HÀNG
						--Update DE_DeliverySlips--------------------------------------------------------BEGIN------------------------------------------------------->
						UPDATE
							DE_DeliverySlips
						SET
							[StatusId]			=	CASE WHEN EXISTS(SELECT 1 FROM DE_DeliverySlipsDetails WHERE (IsCanceled = 1 OR IsAgain = 1) AND DeliverySlipsId = D.DeliverySlipsId) THEN 28 ELSE 29  END,
							[DateCompleted]		=	GETDATE(),
							[DateModified]		=	GETDATE(),
							[ModifiedBy]		=	@CurrentUser
						FROM
							DE_DeliverySlips de INNER JOIN #TableOpenXml D ON (de.DeliverySlipsId = D.DeliverySlipsId)
						WHERE
							NOT EXISTS (
								SELECT 1
								FROM
									DE_DeliverySlipsDetails ddsd
								WHERE
										ddsd.Visible			=	1
									AND ddsd.DeliverySlipsId	=	de.DeliverySlipsId
									AND (isnull(ddsd.Status,0) <> 29))
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--9.4 CẬP NHẬT LẠI TIỀN ĐÃ TRẢ
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						--@DESDetail
						IF EXISTS (SELECT 1 FROM #TableOpenXml D WHERE D.IsAgain = 1 OR D.IsCanceled = 1) AND
							NOT EXISTS (SELECT 1 FROM #TableOpenXml D INNER JOIN dbo.UM_Users uu ON D.CodId = uu.UserId WHERE isnull(uu.UnitTransportId,0) > 2 AND  D.IsAgain = 1)
						BEGIN
							EXEC APIClient_DE_RollBackTotalPaidOfPacking
							@DeliverySlipsId	= 0,
							@ListSoDetails		= @DESDetail,
							@CurrentUser		= @CurrentUser,
							@SesstionKey		= @SesstionKey
						END

						--DECLARE @DeliverySlipsIdCurrent INT
						--DECLARE @ListSoDetails NVARCHAR(MAX)
						--DECLARE DeCursor CURSOR FOR
						--SELECT  D.DeliverySlipsId
						--FROM	#TableOpenXml D
						--GROUP BY D.DeliverySlipsId
						--OPEN DeCursor
						--FETCH NEXT FROM DeCursor
						--	INTO @DeliverySlipsIdCurrent
						--WHILE @@FETCH_STATUS = 0
						--BEGIN
						--	SELECT @ListSoDetails = LEFT(SoDetailId, LEN(SoDetailId) - 1)
						--	FROM (
						--		SELECT CAST(SoDetailId AS nvarchar) + ','
						--		FROM #TableOpenXml
						--		WHERE DeliverySlipsId = @DeliverySlipsIdCurrent
						--		FOR XML PATH ('')
						--		) c (SoDetailId)

						--	EXEC APIClient_DE_RollBackTotalPaidOfPacking
						--	@DeliverySlipsId	= @DeliverySlipsIdCurrent,
						--	@ListSoDetails		= @ListSoDetails,
						--	@CurrentUser		= @CurrentUser,
						--	@SesstionKey		= @SesstionKey
						--	FETCH NEXT FROM DeCursor
						--	INTO @DeliverySlipsIdCurrent
						--END
						--CLOSE DeCursor;
						--DEALLOCATE DeCursor;
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--9.5 TẠO PHIẾU THU TIỀN
						---------------------------------------------------------------------------------BEGIN------------------------------------------------------->
						--KIỂM TRA NẾU CodId KHÁC 3PL THÌ MỚI TẠO PHIẾU THU TIỀN
						IF EXISTS (SELECT 1 FROM #TableOpenXml D INNER JOIN dbo.UM_Users uu ON D.CodId = uu.UserId WHERE isnull(uu.UnitTransportId,0) < 2)
						BEGIN
							--CHECK XEM 1 HAY NHIỀU DANH SÁCH GIAO HÀNG
							DECLARE @CountDeSlip int
							SELECT @CountDeSlip = COUNT(DISTINCT tox.DeliverySlipsId) FROM dbo.#TableOpenXml tox

							IF (@CountDeSlip=1)
							BEGIN
								DECLARE @DeSlipId int
								DECLARE @CodByDeSlip int
								SELECT DISTINCT @DeSlipId = tox.DeliverySlipsId FROM dbo.#TableOpenXml tox INNER JOIN dbo.DE_DeliverySlips dds ON dds.DeliverySlipsId=tox.DeliverySlipsId WHERE dds.StatusId IN (28,29)
								SELECT @CodByDeSlip = tox.CodId FROM dbo.#TableOpenXml tox WHERE tox.DeliverySlipsId=@DeSlipId
								IF EXISTS (	SELECT	1
											FROM DE_DeliverySlipsDetails D INNER JOIN SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = D.SoDetailId or sd.SoDetailId = D.SoDetailId)
											WHERE	sd.SODetailStatusID	= 13	AND
													sd.Visible			= 1		AND
													D.IsCompleted		= 1		AND
													D.DeliverySlipsId	= @DeSlipId)
								BEGIN

									--INSERT CA_CashDelivery
									INSERT INTO CA_CashDelivery
									(
										[UserIdDelivery],
										[Visible],
										[IsCompleted],
										[DateCompleted],
										[CreatedBy],
										[TotalMoney],
										[DeliverySlipsId]
									)
									SELECT @CodByDeSlip,1,0,GETDATE(),@CurrentUser,0, @DeSlipId
									SET @CashId = @@IDENTITY
									--INSERT CA_CashDeliveryDetails
									INSERT INTO CA_CashDeliveryDetails
									(
										[CashId],
										[SoDetailId],
										[Quantity]
									)
									SELECT	@CashId, sd.SoDetailId , DESD.QuantityCompleted
									FROM	DE_DeliverySlipsDetails DESD
											INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = DESD.SoDetailId or sd.SoDetailId = DESD.SoDetailId)
									WHERE 	sd.SODetailStatusID		= 13	AND
											sd.Visible				= 1		AND
											DESD.IsCompleted		= 1		AND
											DESD.DeliverySlipsId	= @DeSlipId
									--INSERT CA_CashDeliverySlip_Logs
									INSERT INTO CA_CashDeliverySlip_Logs
									(
										[CashId],
										[DeliverySlipsId],
										[CreatedBy]
									)
									SELECT @CashId, @DeSlipId, @CurrentUser
								END
							END
							ELSE
																																																																			BEGIN
								DECLARE @iDeCodId int = 0;
								DECLARE curSoId CURSOR FOR
								SELECT	DISTINCT	de.DeliverySlipsId, de.CodId
								FROM
										DE_DeliverySlips de INNER JOIN #TableOpenXml as [des] ON  [des].DeliverySlipsId = de.DeliverySlipsId
								WHERE
										de.[StatusId] IN (29,28)
								OPEN curSoId
								FETCH NEXT FROM curSoId INTO @DeliverySlipsId , @iDeCodId
								WHILE @@FETCH_STATUS = 0
								BEGIN
								IF EXISTS (SELECT 1 FROM DE_DeliverySlipsDetails D
									INNER JOIN SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = D.SoDetailId or sd.SoDetailId = D.SoDetailId)
									WHERE	sd.SODetailStatusID	= 13	AND
											sd.Visible			= 1		AND
											D.IsCompleted		= 1		AND
											D.DeliverySlipsId	= @DeliverySlipsId)
								BEGIN
									--INSERT CA_CashDelivery
									INSERT INTO CA_CashDelivery
									(
										[UserIdDelivery],
										[Visible],
										[IsCompleted],
										[DateCompleted],
										[CreatedBy],
										[TotalMoney],
										[DeliverySlipsId]
									)
									SELECT @iDeCodId,1,0,GETDATE(),@CurrentUser,0, @DeliverySlipsId
									SET @CashId = @@IDENTITY
									--INSERT CA_CashDeliveryDetails
									INSERT INTO CA_CashDeliveryDetails
									(
										[CashId],
										[SoDetailId],
										[Quantity]
									)
									SELECT	@CashId, sd.SoDetailId , DESD.QuantityCompleted
									FROM	DE_DeliverySlipsDetails DESD
											INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = DESD.SoDetailId or sd.SoDetailId = DESD.SoDetailId)
									WHERE 	sd.SODetailStatusID		= 13	AND
											sd.Visible				= 1		AND
											DESD.IsCompleted		= 1		AND
											DESD.DeliverySlipsId	= @DeliverySlipsId
									--INSERT CA_CashDeliverySlip_Logs
									INSERT INTO CA_CashDeliverySlip_Logs
									(
										[CashId],
										[DeliverySlipsId],
										[CreatedBy]
									)
									SELECT @CashId,@DeliverySlipsId,@CurrentUser
								END
								-----------------------------------------------------------------------------------------------------
								FETCH NEXT FROM curSoId INTO @DeliverySlipsId , @iDeCodId
							END
								CLOSE curSoId
								DEALLOCATE curSoId
						END
						END
						---------------------------------------------------------------------------------END----------------------------------------------------------
						--9.6 LƯU GHI CHÚ ĐẶC BIỆC
						DECLARE @FullName nvarchar(50)
						DECLARE @DepartmentName nvarchar(50)
						SELECT @FullName = uu.FullName FROM dbo.UM_Users uu WHERE uu.UserId=@CurrentUser
						SELECT @DepartmentName = ud.DepartmentName FROM dbo.UM_Users uu INNER JOIN dbo.UM_Departments ud ON ud.DepartmentId = uu.DepartmentId WHERE uu.UserId=@CurrentUser

						INSERT INTO dbo.SpecialNotes
						(
							CreatedBy,
							FullName,
							DepartmentName,
							CreatedDate,
							Note,
							ClientSoId,
							SoId
						)
						SELECT DISTINCT
							@CurrentUser,
							@FullName,
							@DepartmentName,
							GETDATE(),
							CASE WHEN r.ReasonName	LIKE N'%Lý do khác%'	THEN cast(tox.ReasonAgainOrther AS nvarchar(250)) + N'(Thời gian hẹn lại: ' + convert(varchar(5), tox.DateAgain, 108) + ' -> ' + convert(varchar(5), tox.DateAgainTo,108) + ' ' + convert(varchar(10), tox.DateAgain, 103) + ')'
																			ELSE cast(r.ReasonName AS nvarchar(250))		  + N'(Thời gian hẹn lại: ' + convert(varchar(5), tox.DateAgain, 108) + ' -> ' + convert(varchar(5), tox.DateAgainTo,108) + ' ' + convert(varchar(10), tox.DateAgain, 103) + ')'
							END,
							tox.ClientSoId,
							tox.SoId
						FROM dbo.#TableOpenXml tox	LEFT JOIN dbo.Reason r ON r.ReasonId=tox.ReasonAgain
						WHERE tox.IsAgain = 1 --AND NOT tox.ReasonAgainOrther IS NULL

						--9.7 KIỂM TRA NẾU LÀ ĐƠN HÀNG MÀ 3PL GIAO THÌ CẬP NHẬT SANG UnitTransport_Log_CreateShippingOrder
						UPDATE	UnitTransport_Log_CreateShippingOrder
						SET		StatusId =	CASE
												WHEN tox.IsCompleted	= 1 THEN 7	--> Đã giao hàng
												WHEN tox.IsAgain		= 1 THEN 9	--> Hẹn lại
												WHEN tox.IsCanceled		= 1 THEN 8	--> Hủy
											END,
								LastModifiedDate = GETDATE(),
								IsCheckSave	= 1
						FROM	UnitTransport_Log_CreateShippingOrder ulc INNER JOIN dbo.Packing_Detail pd ON pd.PackingID = ulc.ReferenceId
																			INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.PackingDetailId = pd.PackingDetailId
																			INNER JOIN #TableOpenXml tox ON tox.DeliverySlipsDetailId = ddsd.DeliverySlipsDetailId
						WHERE	isnull(ulc.UnitTransportId,0) > 1
							AND	ulc.MethodId = 1
							AND ulc.StatusId NOT IN (7,8,10,11,12)

						END

					END
					ELSE IF (@IsState = 1)	-->LƯU TẠM
					BEGIN
						--1. CẬP NHẬT TRẠNG THÁI CHI TIẾT PHIẾU: DE_DeliverySlipsDetails
						UPDATE	DE_DeliverySlipsDetails
						SET
								IsCompleted			=	D.IsCompleted,
								IsAgain				=	D.IsAgain,
								IsCanceled			=	D.IsCanceled,
								DateCompleted		=	CASE WHEN D.IsCompleted	= 1 THEN GETDATE()				ELSE NULL	END,
								DateAgain			=	CASE WHEN D.IsAgain		= 1 THEN D.DateAgain			ELSE NULL	END,
								DateAgainTo			=	CASE WHEN D.IsAgain		= 1 THEN D.DateAgainTo			ELSE NULL	END,
								DateCanceled		=	CASE WHEN D.IsCanceled	= 1 THEN GETDATE()				ELSE NULL	END,
								QuantityCompleted	=	CASE WHEN D.IsCompleted	= 1 THEN D.QuantityCompleted	ELSE 0		END,
								QuantityAgain		=	CASE WHEN D.IsAgain		= 1 THEN D.QuantityAgain		ELSE 0		END,
								QuantityCanceled	=	CASE WHEN D.IsCanceled	= 1 THEN D.QuantityCanceled		ELSE 0		END,
								QuantityFailing		=	D.QuantityFailing,
								ReasonAgain			=	CASE WHEN D.IsAgain		= 1 THEN D.ReasonAgain			ELSE 0		END,
								ReasonCanceled		=	CASE WHEN D.IsCanceled	= 1 THEN D.ReasonCanceled		ELSE 0		END,
								ModifiedBy			=	@CurrentUser,
								DateModified		=	CASE WHEN (D.IsCompleted <> DED.IsCompleted OR D.IsAgain<>DED.IsAgain OR D.IsCanceled<>DED.IsCanceled) THEN GETDATE() ELSE DED.DateModified END,
								[Status]			=	CASE WHEN (D.IsCompleted = 1 OR D.IsAgain = 1 OR D.IsCanceled = 1) THEN 28 ELSE NULL END,
								ReasonAgainOrther	=	CASE WHEN D.IsAgain		 = 1 THEN D.ReasonAgainOrther	ELSE NULL	END,
								ReasonCanceledOrther=	CASE WHEN D.IsCanceled	 = 1 THEN D.ReasonCanceledOrther ELSE NULL	END,
								DateSaveTemp		=	CASE WHEN ((D.IsCompleted <> DED.IsCompleted OR D.IsAgain<>DED.IsAgain OR D.IsCanceled<>DED.IsCanceled) AND DED.IsCallCustomerFail = 0) THEN GETDATE() ELSE DED.DateSaveTemp END,
								Note				=	D.Note
						FROM
								DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
								INNER JOIN dbo.SO_Detail sd ON sd.SODetailID=DED.SoDetailId
						WHERE	sd.SODetailStatusID=7
					END
					ELSE IF (@IsState = 3)	-->LƯU TRẠNG THÁI KHÔNG LH ĐƯỢC VỚI KH
					BEGIN
						--Insert
						INSERT INTO dbo.SMS_Customer_Tracking
						(
							ClientSoId,
							CreatedBy,
							CreatedAt,
							FullName,
							PhoneDelivery,
							PhoneReceive,
							ReasonName
						)
						SELECT DISTINCT	D.ClientSoId,
								@CurrentUser,
								GETDATE(),
								uu.FullName,
								uu.PhoneNumber,
								s.CustomerPhone,
								N'Không liên hệ được với khách hàng.'
						FROM
								DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
															INNER JOIN dbo.SO s ON s.ClientSoId = D.ClientSoId
															LEFT JOIN dbo.UM_Users uu ON uu.UserId = D.CodId
						WHERE	isnull(DED.IsCallCustomerFail,0)	= 0
							AND (DED.IsCompleted = 0 OR DED.IsAgain	= 0 OR DED.IsCanceled = 0)


						--Update
						UPDATE	DE_DeliverySlipsDetails
						SET
								IsCallCustomerFail	= 1,
								DateSaveTemp		= CASE WHEN DED.DateSaveTemp IS NULL THEN GETDATE() ELSE DED.DateSaveTemp END

						FROM
								DE_DeliverySlipsDetails DED INNER JOIN #TableOpenXml D ON (DED.DeliverySlipsDetailId = D.DeliverySlipsDetailId)
						WHERE	isnull(DED.IsCallCustomerFail,0)	= 0
							AND (DED.IsCompleted = 0 OR DED.IsAgain	= 0 OR DED.IsCanceled = 0)

						--Update SoDetails
						UPDATE	SO_Detail
						SET		SO_Detail.IsPushCS	= 1
						FROM	dbo.SO_Detail sd INNER JOIN #TableOpenXml tox ON tox.SoDetailId = sd.SODetailID

					END
				-----------------------------------------------------------------------------------------END----------------------------------------------------------
				END
				--XÓA TABLE TEMP
				EXEC sp_xml_removedocument @idoc

			COMMIT TRAN T
			IF (@Counter = 0)
			BEGIN
				SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
			END
			ELSE IF (@Counter > 0)
			BEGIN
				SELECT 0 AS IsSuccess, N'Fail' AS ResponseMessage;
			END
			ELSE IF (@IsState = 1)
			BEGIN
				SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
			END
			ELSE IF (@IsState = 3)
			BEGIN
				SELECT 1 AS IsSuccess, 'Successful' AS ResponseMessage
			END

		END TRY---------------------------------------------------------------------------------------END TRY---------------------------------------------------------
		BEGIN CATCH
			SELECT 0 AS IsSuccess, N'Lỗi rồi.' AS ResponseMessage;
			THROW;
		END CATCH
	END TRY
	BEGIN CATCH
		SELECT 0 AS IsSuccess, N'Lỗi rồi.' AS ResponseMessage;
		THROW;
	END CATCH
	DROP TABLE #TableOpenXml
END