USE [Adayroi_TMS]
GO
/****** Object:  StoredProcedure [dbo].[APIClient_PO_UpdatePOStatusID_V01]    Script Date: 7/27/2017 11:23:33 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Entity Name:	APIClient_PO_UpdatePOStatusID_V01
-- Author:		Hoàng Anh Thủy
-- Create date:	14/12/2014 09:44:33
-- Description:	Lấy chi tiết của 1 pickup
-- =============================================
ALTER PROCEDURE [dbo].[APIClient_PO_UpdatePOStatusID_V01]
(
	@ListPoIds NVARCHAR(MAX),
	@StatusId INT,
	@IsOk bit = 1,
	@ListSoDetailSerials NVARCHAR(MAX),
	@userId int,
	@sessionkey varchar(255)
)
AS
BEGIN
	DECLARE @result BIT
	DECLARE @Msg NVARCHAR(MAX)
	DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
	BEGIN TRY
		EXEC		[dbo].[CHECK_SESSION_AND_PERMISSION] @userId, @sessionkey, @PROC_NAME

		DECLARE @iDoc INT
		DECLARE @iDocImei INT;
		EXEC sp_xml_preparedocument @iDoc out, @ListPoIds
		EXEC sp_xml_preparedocument @iDocImei out, @ListSoDetailSerials;

		DECLARE @SodetailTable TABLE (
			SoDetailId bigint,
			PoId bigint,
			SoDetailStatus int,
			PoStatus int,
			PuId int,
			ReasonId int,
			ReasonName nvarchar(250),
			DeliveryFlowId int,
			SOId bigint,
			Amount int,
			SellPrice decimal(18,4),
			SODetailStatusID int,
			ClientSoId bigint,
			MerchantDatePickupFrom varchar(20),
			MerchantDatePickupTo varchar(20),
			IsUsedVinId bit,
			SourceClientId int,
			MTID int,
			IsVinIdCard bit,
			ProductItemId int,
			HybrisProductItemId varchar(30),
			SAPReservationNumber nvarchar(100),
			SAPPONumberInterim nvarchar(100),
			SAPReservationLineItem nvarchar(200)
			PRIMARY KEY (SoDetailId, PoId, PuId)
		)

		INSERT INTO @SodetailTable
		SELECT sd.SODetailID
			, po.PoId
			, CASE WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 AND ISNULL(s2.IsDeliveryPiC, 0) = 0 THEN 7
				WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 AND ISNULL(s2.IsDeliveryPiC, 0) = 1 THEN 18
				WHEN S.StatusId = 5 THEN 16 ELSE S.StatusId END
			, CASE WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 THEN 4 ELSE S.StatusId END
			, S.PuId
			, S.ReasonId
			, S.ReasonName
			, po.DeliveryFlowId
			, sd.SOId
			, sd.Amount
			, sd.SellPrice
			, sd.SODetailStatusID
			, sd.ClientSoId
			, S.MerchantDatePickupFrom
			, S.MerchantDatePickupTo
			, ISNULL(po.IsUsedVinId, 0)
			, s2.SourceClientId
			, po.MTID
			, sd.IsVinIdCard
			, cp.ProductItemId
			, cp.HybrisProductItemId
			, sd.SAPReservationNumber
			, sd.SAPPONumberInterim
			, sd.SAPReservationLineItem
		FROM OPENXML(@iDoc, '/ArrayOfPoConfirmPost/PoConfirmPost', 8) WITH
			(
				PoId	BIGINT 'PoId',
				StatusId	int 'StatusId',
				ReasonId	int 'ReasonId',
				PuId	int 'PuId',
				ReasonName	nvarchar(250) 'ReasonName',
				MerchantDatePickupFrom	varchar(20) 'MerchantDatePickupFrom',
				MerchantDatePickupTo	varchar(20) 'MerchantDatePickupTo'
			) AS S
			INNER JOIN dbo.PurchaseOrder po WITH (NOLOCK) ON po.PoId = S.PoId
			INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.ClientPoId = po.ClientPoId
			INNER JOIN dbo.SO s2 WITH (NOLOCK) ON s2.SOID = sd.SOId
			INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
		WHERE sd.Visible = 1 AND S.StatusId <> 2

		/*------------------------------*/
		-- Thong tin phi PO
		DECLARE @PoFeeInfo TABLE (
			ClientSoId bigint,
			ClientPoId bigint,
			ProductItemId int,
			Fee decimal(18,0)
		)

		INSERT INTO @PoFeeInfo
		SELECT DISTINCT
			sdit.ClientSoId,
			sdit.PoId,
			pofm.ProductItemId,
			pofm.Fee
		FROM @SodetailTable sdit
			INNER JOIN dbo.PurchaseOrder_Fee_Map pofm WITH (NOLOCK) ON sdit.PoId = pofm.ClientPoId
		WHERE sdit.SourceClientId = 1 AND pofm.IsActiveFee = 1 AND pofm.Visible = 1 AND pofm.Status = 1
		/*------------------------------*/

		/* Sap Po ------------------------------------------------*/
		DECLARE @SapPo TABLE (
			PoId bigint
		)

		INSERT INTO @SapPo
		SELECT DISTINCT st.PoId
		FROM @SodetailTable st
		WHERE st.PoStatus IN (3,4) AND st.SourceClientId = 2 AND st.MTID IN (2,3,4,5)
		GROUP BY st.PoId
		/* -------------------------------------------------------- */

		/* Input SoDetail Serial----------------------------------- */
		DECLARE @SoDetailSerial TABLE (
			SoDetailId bigint,
			ImeiSerial VARCHAR(250),
			IsVinIdCard bit
		)

		INSERT INTO @SoDetailSerial
		SELECT S.SoDetailId,
			S.ImeiSerial,
			st.IsVinIdCard
		FROM OPENXML(@iDocImei, '/ArrayOfImeiProductBulkyGoods/ImeiProductBulkyGoods',8)
			WITH(
				SoDetailId BIGINT 'SoDetailId',
				ImeiSerial VARCHAR(250) 'ImeiSerial'
			) S
			INNER JOIN @SodetailTable st ON st.SoDetailId = S.SoDetailId

		EXEC sp_xml_removedocument @idoc;
		EXEC sp_xml_removedocument @iDocImei;
		/* -------------------------------------------------------- */

		IF EXISTS (SELECT 1 FROM @SodetailTable AS S
			INNER JOIN PickUpDetail pud WITH (NOLOCK) ON pud.PoId = S.PoId AND pud.PuId = S.PuId
		WHERE pud.PuDetailStatus = 1 OR S.SODetailStatusID <> 2)
		BEGIN
			SELECT 0 AS IsSuccess, N'Có PO đã được cập nhật! Xin hãy làm mới dữ liệu!' AS MessageError
			RETURN
		END

		DECLARE @UserStationId INT = 0
		SELECT @UserStationId = uu.SID FROM dbo.UM_Users uu WITH (NOLOCK) WHERE uu.UserId = @userId

		DECLARE @UserCodId INT = 0
		SELECT @UserCodId = pu.CodId FROM @SodetailTable st INNER JOIN dbo.PickUp pu WITH (NOLOCK) ON pu.PuId = st.PuId
	END TRY
	BEGIN CATCH
		SELECT 0 AS IsSuccess, N'Có lỗi phát sinh trong quá trình xử lý dữ liệu đầu vào!' AS MessageError
		RETURN
	END CATCH

	DECLARE @RetryCounter INT
	SET @RetryCounter = 1
	RETRY: -- Label RETRY

	BEGIN TRAN T
	BEGIN TRY
		UPDATE LockTablePO SET COUNT=1 WHERE ID=1
		UPDATE LockTableSoDetail SET COUNT=1 WHERE ID = 1

		DECLARE @DeliveryTable TABLE (DeliverySlipsId int, StatusId int)
		DECLARE @DeliveryTableCash TABLE (DeliverySlipsId int)

		IF EXISTS (SELECT 1 FROM @SoDetailSerial sds)
		BEGIN
			-- Insert into [dbo].[SO_Detail_Serial]
			INSERT INTO [dbo].[SO_Detail_Serial]
					([SODetailID]
					,[SerialNumber]
					,[ExpireDate]
					,[CreatedDate]
					,[CreatedBy]
					,[StatusId])
			SELECT s.SoDetailId, s.ImeiSerial, NULL, GETDATE(), @userId , CASE WHEN st.DeliveryFlowId = 2 THEN 59 ELSE 67 END
			FROM @SoDetailSerial s
				INNER JOIN @SodetailTable st ON st.SoDetailId = s.SoDetailId
		END

		--Update lại trạng thái cho PickUp
		UPDATE [dbo].[PickUp]
		SET [PuStatus] = 6, [FinishDate] = GETDATE()
		FROM dbo.PickUp pu
			INNER JOIN (SELECT DISTINCT st.PuId FROM @SodetailTable st) AS S ON S.PuId = pu.PuId
		WHERE pu.PuStatus = 5 AND
			NOT EXISTS(SELECT 1
				FROM [dbo].[PurchaseOrder] PO INNER JOIN [dbo].[PickUpDetail] PD ON PO.PoId = PD.[PoId]
				WHERE PD.[PuId] = pu.PuId AND PO.POStatusId = 2 AND ISNULL(PD.PuDetailStatus, 0) = 0
					AND NOT EXISTS(SELECT 1 FROM @SodetailTable st1 WHERE st1.PuId = PD.PuId AND st1.PoId = PD.PoId))

		UPDATE dbo.PickUpDetail
		    SET
		        dbo.PickUpDetail.IsComplete = CASE WHEN S.PoStatus IN (3, 4) THEN 1 ELSE 0 END, -- bit
		        dbo.PickUpDetail.IsAgain = CASE WHEN S.PoStatus = 1 THEN 1 ELSE 0 END, -- bit
		        dbo.PickUpDetail.IsCancel = CASE WHEN S.PoStatus = 5 THEN 1 ELSE 0 END, -- bit
		        dbo.PickUpDetail.ReasonId = S.ReasonId, -- int
			    dbo.PickUpDetail.ReasonDescription = S.ReasonName,
		        dbo.PickUpDetail.ModifyDate = GETDATE(), -- datetime
		        dbo.PickUpDetail.ModifyBy = @userId, -- int
				dbo.PickUpDetail.PuDetailStatus = 1
		FROM dbo.PickUpDetail pud INNER JOIN (SELECT DISTINCT st.PoId, st.PuId, st.PoStatus, st.ReasonId, st.ReasonName FROM @SodetailTable st) AS S ON pud.PoId = S.PoId AND pud.PuId = S.PuId

		-- Them log SpecialNotes
		DECLARE @UserName nvarchar(150)
		SELECT @UserName = FullName FROM dbo.UM_Users uu WHERE uu.UserId = @userId
		INSERT INTO dbo.SpecialNotes
		(
		    --Id - this column value is auto-generated
		    dbo.SpecialNotes.CreatedBy,
		    dbo.SpecialNotes.FullName,
		    dbo.SpecialNotes.DepartmentName,
		    dbo.SpecialNotes.CreatedDate,
		    dbo.SpecialNotes.Note,
		    dbo.SpecialNotes.ClientSoId,
		    dbo.SpecialNotes.SoId
		)
		SELECT DISTINCT
			@userId,
			@UserName,
			'TMS',
			GETDATE(),
			CONCAT(N'PO ', st.PoId, N' hẹn lại: ', st.ReasonName), -- , FORMAT(st.SourceClientId, N'00'),
			st.ClientSoId,
			st.SOId
		FROM @SodetailTable st
		WHERE st.PoStatus = 1
		------------------------

		UPDATE P SET POStatusId = S.PoStatus,
			CompleteDatetime = CASE WHEN S.PoStatus > 2 THEN GETDATE() ELSE NULL END,
			UID_COD = CASE WHEN S.PoStatus = 1 THEN NULL ELSE UID_COD END,
			MerchantPickupDateFrom = CASE WHEN S.PoStatus = 1 THEN CONVERT(Datetime, S.MerchantDatePickupFrom, 120) ELSE P.MerchantPickupDateFrom END,
			MerchantPickupDateTo = CASE WHEN S.PoStatus = 1 THEN CONVERT(Datetime, S.MerchantDatePickupTo, 120) ELSE P.MerchantPickupDateTo END
		FROM PurchaseOrder P INNER JOIN @SodetailTable AS S ON P.PoId = S.PoId

		UPDATE [dbo].[PurchaseOrder_Detail]
		SET  [PODetailStatusID] = S.PoStatus,
			[ReasonID] = (CASE WHEN S.PoStatus = 5 THEN S.ReasonId ELSE NULL END),
			[Note] = CASE WHEN S.PoStatus = 1 THEN S.ReasonName ELSE PD.Note END
		FROM [PurchaseOrder_Detail] PD INNER JOIN @SodetailTable AS S ON PD.PoId = S.PoId

		-- Insert into Log_Information action type SoDetail
		INSERT INTO [dbo].[Log_Information]
						([ObjectTypeId]
						,[ObjectId]
						,[ActionId]
						,[LogDetails]
						,[CreatedBy]
						,[CreatedDate]
						,[UID_AssignTo]
						,[OldStatusId]
						,[NewStatusId]
						,StationId)
			SELECT 3, S.SoDetailId, 3, NULL, @userId, GETDATE(), @UserCodId, 2, S.SoDetailStatus, @UserStationId
			FROM @SodetailTable S

		-- Insert into Log_Information action type PurchaseOrder
		INSERT INTO [dbo].[Log_Information]( [ObjectTypeId],
                                    [ObjectId],
                                    [ActionId],
                                    [LogDetails],
                                    [CreatedBy],
                                    [CreatedDate],
                                    [UID_AssignTo],
                                    [OldStatusId],
                                    [NewStatusId],
									StationId )
			SELECT DISTINCT 2, S.PoId, 3, NULL, @userId, GETDATE(), @UserCodId, 2, S.PoStatus, @UserStationId
			FROM @SodetailTable S

		-- Update trạng thái SoDetail
		UPDATE [dbo].[SO_Detail] SET
			[SODetailStatusID] = S.SoDetailStatus,
			ReasonID = (CASE WHEN S.PoStatus = 5 THEN S.ReasonId ELSE NULL END),
			[ReasonCanceledOrther] = CASE WHEN S.PoStatus = 5 AND r.ReasonName LIKE N'Lý do khác' THEN S.ReasonName ELSE N'' END,
			SoDetailStatusLastUpdateTime = GETDATE()
		FROM SO_Detail SD INNER JOIN @SodetailTable AS S ON S.SoDetailId = SD.SODetailID
			LEFT JOIN dbo.Reason r ON r.ReasonId = S.ReasonId

		-- Update DeliveryDetail visible = 0 cho PO huy
		UPDATE dbo.DE_DeliverySlipsDetails
		SET dbo.DE_DeliverySlipsDetails.Visible = 0
		FROM dbo.DE_DeliverySlipsDetails ddsd
			INNER JOIN @SodetailTable AS S ON S.SoDetailId = ddsd.SoDetailId
		WHERE ddsd.Visible = 1 AND S.DeliveryFlowId = 2 AND (S.PoStatus = 5 OR S.PoStatus = 1)

		-- Delivery need to Update status
		IF EXISTS(SELECT 1 FROM @SodetailTable S
			WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2
		)
		BEGIN
			INSERT INTO @DeliveryTable
			SELECT c.DeliverySlipsId,
				CASE WHEN EXISTS (
						SELECT 1 FROM dbo.DE_DeliverySlipsDetails ddsd1 WITH (NOLOCK)
						WHERE ddsd1.DeliverySlipsId = c.DeliverySlipsId AND ddsd1.Visible = 1 AND (ISNULL(ddsd1.IsAgain, 0) = 1 OR ISNULL(ddsd1.IsCanceled, 0) = 1))
					THEN 28
				ELSE 29 END
			FROM (SELECT DISTINCT MAX(ddsd.DeliverySlipsId) AS DeliverySlipsId--po.PoId, SD.SOId
				FROM @SodetailTable S
					INNER JOIN dbo.Packing_Detail pd WITH (NOLOCK) ON pd.SoDetailId = S.SODetailID
					INNER JOIN dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON ddsd.PackingDetailId = pd.PackingDetailId
				WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2
				GROUP BY S.SODetailID) c
			WHERE NOT EXISTS (
				SELECT 1
				FROM dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK)
					INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = ddsd.SoDetailId
				WHERE ddsd.DeliverySlipsId = c.DeliverySlipsId AND sd.Visible = 1 AND ddsd.Visible = 1 AND ISNULL(ddsd.Status, 0) IN (0, 28))

			INSERT INTO @DeliveryTableCash
			SELECT dt.DeliverySlipsId
			FROM @DeliveryTable dt
			WHERE EXISTS (
				SELECT 1
				FROM DE_DeliverySlipsDetails DESD WITH (NOLOCK)
					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	= dt.DeliverySlipsId)
		END

		INSERT INTO dbo.PurchaseOrder_MerchantDate
		(
			--Id - this column value is auto-generated
			dbo.PurchaseOrder_MerchantDate.PoId,
			dbo.PurchaseOrder_MerchantDate.DateFrom,
			dbo.PurchaseOrder_MerchantDate.DateTo,
			dbo.PurchaseOrder_MerchantDate.ReasonName,
			dbo.PurchaseOrder_MerchantDate.UserCreate,
			dbo.PurchaseOrder_MerchantDate.ImportSource
		)
		SELECT DISTINCT
			st.PoId,
			CONVERT(Datetime, st.MerchantDatePickupFrom, 120),
			CONVERT(Datetime, st.MerchantDatePickupTo, 120),
			N'TMS' + st.ReasonName,
			@userId,
			N'Logistics'
		FROM @SodetailTable st
		WHERE st.PoStatus = 1

		INSERT INTO dbo.SAP_LogDateImportPoid
		(
		    dbo.SAP_LogDateImportPoid.POID,
		    dbo.SAP_LogDateImportPoid.ImportDate,
		    dbo.SAP_LogDateImportPoid.CreatedDate
		)
		SELECT DISTINCT S.PoId, GETDATE(), GETDATE()
		FROM @SodetailTable AS S WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2

		DECLARE @tbl as SAP_TableGoodsReceivePO
		INSERT INTO @tbl
		SELECT
			  sp.PoId
			, 315 AS ifcode
		FROM @SapPo sp
		EXEC [APIClient_SAP_CreateGoodsReceiptDO_InsertLogToSync_Sale_V01] @tbl
		/*---------------------------------------------------------------------------------------------------------------*/

		/* ------------------ Hybris status sync ------------------------------------------------------------------*/
		DECLARE @StatusUpdateInserted TABLE (
			Log_UpdateDeliveryId bigint,
			ClientSoId bigint,
			ClientPoId bigint
		)

		INSERT INTO dbo.Log_UpdateDeliveryStatus
		(
			--Log_UpdateDeliveryId - this column value is auto-generated
			dbo.Log_UpdateDeliveryStatus.SourceClientId,
			dbo.Log_UpdateDeliveryStatus.ClientSoId,
			dbo.Log_UpdateDeliveryStatus.ClientPoId,
			dbo.Log_UpdateDeliveryStatus.CreatedBy,
			dbo.Log_UpdateDeliveryStatus.CreatedDatetime,
			dbo.Log_UpdateDeliveryStatus.Visible,
			dbo.Log_UpdateDeliveryStatus.RetryCount
		)
		OUTPUT INSERTED.Log_UpdateDeliveryId, INSERTED.ClientSoId, INSERTED.ClientPoId INTO @StatusUpdateInserted
		SELECT DISTINCT st.SourceClientId,
			st.ClientSoId,
			st.PoId,
			@userId,
			GETDATE(),
			1,
			0
		FROM @SodetailTable st
		WHERE st.SourceClientId = 2 AND st.SoDetailStatus <> 1

		INSERT INTO dbo.Log_UpdateDeliveryStatus_Detail
		(
			--Log_UpdateDeliveryDetailId - this column value is auto-generated
			dbo.Log_UpdateDeliveryStatus_Detail.Log_UpdateDeliveryId,
			dbo.Log_UpdateDeliveryStatus_Detail.So_DetailID,
			dbo.Log_UpdateDeliveryStatus_Detail.OldStatus,
			dbo.Log_UpdateDeliveryStatus_Detail.CurrentStatus,
			dbo.Log_UpdateDeliveryStatus_Detail.Amount,
			dbo.Log_UpdateDeliveryStatus_Detail.SellPrice,
			dbo.Log_UpdateDeliveryStatus_Detail.HybrisProductItemId,
			dbo.Log_UpdateDeliveryStatus_Detail.SAPReservationNumber,
			dbo.Log_UpdateDeliveryStatus_Detail.SAPPONumberInterim,
			dbo.Log_UpdateDeliveryStatus_Detail.SAPReservationLineItem
		)
		SELECT DISTINCT sui.Log_UpdateDeliveryId,
			st.SoDetailId,
			64,
			CASE WHEN st.SoDetailStatus = 1 THEN 8
				WHEN st.SoDetailStatus = 16 THEN 16777216
				ELSE 128 END,
			st.Amount,
			st.SellPrice,
			st.HybrisProductItemId,
			st.SAPReservationNumber,
			st.SAPPONumberInterim,
			st.SAPReservationLineItem
		FROM @SodetailTable st INNER JOIN @StatusUpdateInserted sui ON st.ClientSoId = sui.ClientSoId AND st.PoId = sui.ClientPoId

		INSERT INTO dbo.Log_UpdateDeliveryStatus_Serial
		(
		    --Log_UpdateStatusSerialId - this column value is auto-generated
		    dbo.Log_UpdateDeliveryStatus_Serial.Log_UpdateDeliveryDetailId,
		    dbo.Log_UpdateDeliveryStatus_Serial.SerialNumber
		)
		SELECT ludsd.Log_UpdateDeliveryDetailId,
			sds.ImeiSerial
		FROM dbo.Log_UpdateDeliveryStatus_Detail ludsd
			INNER JOIN @SoDetailSerial sds ON sds.SoDetailId = ludsd.So_DetailID
			INNER JOIN @StatusUpdateInserted sui ON sui.Log_UpdateDeliveryId = ludsd.Log_UpdateDeliveryId
		WHERE sds.IsVinIdCard = 0
		GROUP BY ludsd.Log_UpdateDeliveryDetailId, sds.ImeiSerial
		/*---------------------------------------------------------------------------------------------------------*/

		/* ------------------ OMS status sync 64 -> 128 ------------------------------------------------------------------*/
		DECLARE @TblOms AS Oms_UpdateSoDetailStatus

		INSERT INTO @TblOms -- Product Item
		(
			SOID,
			PO,
			ProductItemId,
			Amount,
			CurrentStatus,
			DeliveryReasonId,
			DeliveryNote,
			OldStatus,
			PriceCustomer,
			SAPReservationNumber,
			SAPReservationLineItem,
			SAPPONumberInterim,
			Serials
		)
		SELECT
			st.ClientSoId,
			st.PoId,
			st.ProductItemId,
			st.Amount,
			CASE WHEN st.PoStatus = 1 THEN 8 ELSE 128 END,
			0,
			'',
			64,
			st.SellPrice,
			st.SAPReservationNumber,
			st.SAPReservationLineItem,
			st.SAPPONumberInterim,
			(
				SELECT sds.ImeiSerial AS string
				FROM @SoDetailSerial sds
				WHERE sds.SoDetailId = st.SoDetailId AND sds.IsVinIdCard = 0
				FOR XML PATH ('')
			)
		FROM @SodetailTable st
		WHERE st.SourceClientId = 1 AND st.PoStatus <> 5
		GROUP BY st.ClientSoId,
			st.PoId,
			st.ProductItemId,
			st.Amount,
			st.SellPrice,
			st.SAPReservationNumber,
			st.SAPReservationLineItem,
			st.SAPPONumberInterim,
			st.SoDetailId,
			st.PoStatus

		INSERT INTO @TblOms -- Po Fee
		(
			SOID,
			PO,
			ProductItemId,
			Amount,
			CurrentStatus,
			DeliveryReasonId,
			DeliveryNote,
			OldStatus,
			PriceCustomer,
			SAPReservationNumber,
			SAPReservationLineItem,
			SAPPONumberInterim,
			Serials
		)
		SELECT DISTINCT
			pfi.ClientSoId,
			pfi.ClientPoId,
			pfi.ProductItemId,
			1,
			CASE WHEN st.PoStatus = 1 THEN 8 ELSE 128 END,
			0,
			'',
			64,
			pfi.Fee,
			NULL,
			NULL,
			NULL,
			''
		FROM @PoFeeInfo pfi
			INNER JOIN @SodetailTable st ON pfi.ClientPoId = st.PoId
		WHERE st.SourceClientId = 1 AND st.PoStatus <> 5

		EXEC dbo.APIClient_OMS_CreateLogUpdateSoDetails_V01 @CurrentUser = @userId, @SessionKey = @sessionkey, @TblInput = @TblOms

		-- Huy PO
		DECLARE @TblOmsCancelPo as OMS_CancelPo

		INSERT INTO @TblOmsCancelPo
		(
			ClientSoId,
			PoId,
			DeliveryReasonId,
			DeliveryNote,
			UserId,
			CreateRpo
		)
		SELECT DISTINCT
			st.ClientSoId,
			st.PoId,
			st.ReasonId,
			st.ReasonName,
			@userId,
			0
		FROM @SodetailTable st
		WHERE st.SourceClientId = 1 AND st.PoStatus = 5

		EXEC dbo.APIClient_OMS_CreateLogCancelPo_V01 @CurrentUser = 12345, @SessionKey = '12345', @TblInput = @TblOmsCancelPo
		/*---------------------------------------------------------------------------------------------------------*/

		/* ------------------ Vinpro status sync ------------------------------------------------------------------*/
		INSERT INTO dbo.Vinpro_Sync_Status
		(
		    --SyncId - this column value is auto-generated
		    dbo.Vinpro_Sync_Status.OrderNo,
		    dbo.Vinpro_Sync_Status.ItemNo,
		    dbo.Vinpro_Sync_Status.Quantity,
		    dbo.Vinpro_Sync_Status.Status,
		    dbo.Vinpro_Sync_Status.TMSCode,
		    dbo.Vinpro_Sync_Status.CreatedDate,
		    dbo.Vinpro_Sync_Status.IsSynced,
		    dbo.Vinpro_Sync_Status.RetryCount,
			dbo.Vinpro_Sync_Status.CreatedBy
		)
		SELECT DISTINCT
			st.ClientSoId,
			st.HybrisProductItemId,
			st.Amount,
			0,
			st.SoDetailId,
			GETDATE(),
			0,
			0,
			@userId
		FROM @SodetailTable st
		WHERE st.SourceClientId = 3 AND st.SoDetailStatus = 16
		/*---------------------------------------------------------------------------------------------------------*/

		/* Nhập kho cho các sản phẩm hoàn thành */
		IF(EXISTS(SELECT 1 FROM @SodetailTable AS S WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2))
		BEGIN
			-- Tạo phiếu nhập kho
			DECLARE @warehouseSlipId bigint = 0
			DECLARE @warehouseSlipIdX bigint = 0
			DECLARE @stationWHId int = 0
			DECLARE @codId int = 0
			DECLARE @stationId int = 0

			SELECT @stationId = P.SID_Tally, @codId = P.UID_COD FROM PurchaseOrder P INNER JOIN (SELECT DISTINCT st.PoId, st.PoStatus, st.DeliveryFlowId FROM @SodetailTable st) AS S ON P.PoId = S.PoId
			WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2

			SELECT @stationWHId = COALESCE([StationWHId],0) FROM [Station_WHType]
			WHERE [SID]= @stationId AND [SWHTID] = 1

			INSERT INTO [WH_WarehouseSlips]([UserIdRecipient], [UserIdDelivery], [StationWHId], [ActionTypeId], [DestinationStationId], [IsCompleted], [DateCompleted], [CompletedBy] )
			SELECT	@userId, @codId, @stationWHId, 1, null, 1, GETDATE() , @userId
			SET @warehouseSlipId = @@IDENTITY
			-- Xóa chi tiết phiếu nhập kho
			--DELETE FROM [dbo].[WH_WarehouseSlip_Details] WHERE [WarehouseSlipId] = @warehouseSlipId
			-- Tạo chi tiết của phiếu nhập kho
			INSERT INTO [WH_WarehouseSlip_Details] ([WarehouseSlipId], [SoDetailId], [Quantity], [QuantityReceived])
			SELECT	@warehouseSlipId, S.SoDetailId, S.Amount, S.Amount
			FROM	@SodetailTable S
			WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
			--Ghi log
			INSERT INTO [WH_DeliverySlip_Logs]	([DeliverySlipsId], [WarehouseSlipId], [CreatedBy])
			SELECT ddsd.DeliverySlipsId, @warehouseSlipId, @userId
			FROM
				SO_Detail SD INNER JOIN @SodetailTable S ON S.SoDetailId = SD.SODetailID
				INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.SoDetailId = SD.SODetailID
			WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
			GROUP BY DeliverySlipsId

			INSERT INTO dbo.WH_ImportSource
			(
				--WhImportSourceId - this column value is auto-generated
				dbo.WH_ImportSource.ImportSourceId,
				dbo.WH_ImportSource.WhSlipId,
				dbo.WH_ImportSource.DateCreated,
				dbo.WH_ImportSource.WhImportSourceTypeId
			)
			SELECT S.PuId, @warehouseSlipId, GETDATE(), 1
			FROM
				@SodetailTable S
			WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
			GROUP BY S.PuId

			INSERT INTO [WH_PO_Inbound_Map] ([PoId], [WarehouseSlipsId], [DateCreated])
			SELECT DISTINCT S.PoId, @warehouseSlipId, GETDATE()
			FROM
				@SodetailTable S
			WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
		END


		/*BEGIN ========== Cap nhat lai tien da tra cho cac PO huy - hen lai - Chi cho hang giao thang ========================*/
		IF EXISTS(SELECT 1 FROM @SodetailTable S
			WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2
		)
		BEGIN
			DECLARE @RollBackSO TABLE
			(
				PackingId bigint,
				SoId bigint,
				TotalPaid decimal(18,4),
				TotalPaidVinId decimal(18,4),
				TotalMoney decimal(18,4),
				TotalMoneyComplete decimal(18,4),
				TotalFee decimal(18,4),
				DeliveryFee decimal(18,4),
				IsRollBackFee bit,
				TotalPaidRollBack decimal(18,4),
				TotalPaidVinIdRollBack decimal(18,4),
				TotalPoFeeRollBack decimal(18,4),
				TotalPoFee decimal(18,4),
				IsUsedVinId bit
				PRIMARY KEY (PackingId, SoId))

			INSERT INTO @RollBackSO
			SELECT p.PackingID,
				s.SOId,
				p.TotalPaid,
				ISNULL(p.TotalVinIdMoney, 0),
				p.TotalMoney - (SELECT SUM(S.Amount * S.SellPrice)
					FROM dbo.Packing_Detail pd1
						INNER JOIN @SodetailTable S ON S.SoDetailId = pd1.SoDetailId
					WHERE pd1.PackingID = p.PackingID AND S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2),
				0,
				0,
				p.DeliveryFee,
				CASE WHEN EXISTS(SELECT 1
					FROM dbo.Packing_Detail pd2
						INNER JOIN dbo.SO_Detail sd ON sd.SODetailID = pd2.SoDetailId
						INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.PackingDetailId = pd2.PackingDetailId
					WHERE sd.SODetailStatusID < 16 AND sd.SODetailStatusID > 1 AND pd2.PackingID = p.PackingID AND ddsd.Visible = 1)
					THEN 0 ELSE 1 END,
				0,
				0,
				ISNULL((SELECT SUM(pdsf.Fee)
					FROM (SELECT DISTINCT S.PoId FROM @SodetailTable S WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2) st
						INNER JOIN dbo.Packing_DeliverySlips_Fee pdsf ON st.PoId = pdsf.ClientPoId
					WHERE pdsf.PackingID = p.PackingID
					GROUP BY pdsf.PackingId), 0),
				ISNULL(p.TotalPoFee, 0),
				ps.IsUsedVinId
			FROM (SELECT DISTINCT pst.PackingID, pst.IsUsedVinId FROM (SELECT DISTINCT MAX(pd.PackingID) AS PackingID, S.SODetailID, S.IsUsedVinId--po.PoId, SD.SOId
				FROM @SodetailTable S
					INNER JOIN dbo.Packing_Detail pd WITH (NOLOCK) ON pd.SoDetailId = S.SODetailID
				WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2
				GROUP BY S.SODetailID, S.IsUsedVinId) pst) ps
				INNER JOIN dbo.Packing p WITH (NOLOCK) ON p.PackingID = ps.PackingID
				INNER JOIN dbo.SO s WITH (NOLOCK) ON s.ClientSoId = p.ClientSoId

			UPDATE @RollBackSO
			SET TotalMoneyComplete = CASE WHEN TotalMoney > 0 THEN TotalMoney + DeliveryFee + TotalPoFee - TotalPoFeeRollBack ELSE 0 END,
				TotalFee = CASE WHEN TotalMoney > 0 THEN DeliveryFee + TotalPoFee - TotalPoFeeRollBack ELSE 0 END

			UPDATE @RollBackSO
			SET TotalPaidRollBack = CASE WHEN IsUsedVinId = 0 AND TotalPaidVinId < TotalFee AND TotalPaid > TotalMoneyComplete - TotalPaidVinId THEN TotalPaid + TotalPaidVinId - TotalMoneyComplete
									WHEN IsUsedVinId = 0 AND TotalPaidVinId >= TotalFee AND TotalPaid > TotalMoneyComplete - TotalFee THEN TotalPaid + TotalFee - TotalMoneyComplete
									WHEN IsUsedVinId = 1 AND TotalPaidVinId >= TotalMoneyComplete THEN TotalPaid
									WHEN IsUsedVinId = 1 AND TotalPaidVinId < TotalMoneyComplete AND TotalPaid + TotalPaidVinId >= TotalMoneyComplete THEN TotalPaid + TotalPaidVinId - TotalMoneyComplete
									ELSE 0 END,
				TotalPaidVinIdRollBack = CASE WHEN IsUsedVinId = 0 AND TotalPaidVinId >= TotalFee THEN TotalPaidVinId - TotalFee
									WHEN IsUsedVinId = 1 AND TotalPaidVinId >= TotalMoneyComplete THEN TotalPaidVinId - TotalMoneyComplete
									ELSE 0 END

			UPDATE dbo.SO
			SET dbo.SO.TotalPaidUsed = dbo.SO.TotalPaidUsed - pst.TotalPaidRollBack,
				dbo.SO.TotalPaidRemaining = dbo.SO.TotalPaidRemaining + pst.TotalPaidRollBack,
				dbo.SO.TotalPaidVinIdUsed = dbo.SO.TotalPaidVinIdUsed - pst.TotalPaidVinIdRollBack,
				dbo.SO.TotalPaidVinIdRemaining = dbo.SO.TotalPaidVinIdRemaining + pst.TotalPaidVinIdRollBack,
				dbo.SO.IsPaidFee = CASE WHEN pst.IsPaidFee > 0 THEN 0 ELSE dbo.SO.IsPaidFee END
			FROM (SELECT
					rb.SoId,
					SUM(rb.TotalPaidRollBack) AS TotalPaidRollBack,
					SUM(rb.TotalPaidVinIdRollBack) AS TotalPaidVinIdRollBack,
					SUM(CASE WHEN rb.IsRollBackFee = 1 AND rb.DeliveryFee > 0 THEN 1 ELSE 0 END) AS IsPaidFee
				FROM @RollBackSO rb
				WHERE (rb.TotalPaidRollBack + rb.TotalPaidVinIdRollBack > 0 OR rb.DeliveryFee > 0)
					OR (rb.TotalMoneyComplete = 0 AND rb.DeliveryFee > 0)
				GROUP BY rb.SoId) pst
			WHERE dbo.SO.SOID = pst.SoId

			UPDATE dbo.Packing
			SET dbo.Packing.TotalPaid = dbo.Packing.TotalPaid - rbs.TotalPaidRollBack,
				dbo.Packing.TotalVinIdMoney = dbo.Packing.TotalVinIdMoney - rbs.TotalPaidVinIdRollBack,
				dbo.Packing.TotalMoney = rbs.TotalMoney,
				dbo.Packing.TotalPoFee = dbo.Packing.TotalPoFee - rbs.TotalPoFeeRollBack
			FROM @RollBackSO rbs
			WHERE dbo.Packing.PackingID = rbs.PackingId

			UPDATE dbo.Packing_DeliverySlips_Fee
			SET
			    dbo.Packing_DeliverySlips_Fee.Status = 2,
			    dbo.Packing_DeliverySlips_Fee.UpdateBy = @userId, -- int
			    dbo.Packing_DeliverySlips_Fee.UpdateDate = GETDATE()
			FROM dbo.Packing_DeliverySlips_Fee pdsf
				INNER JOIN @SodetailTable st ON st.PoId = pdsf.ClientPoId
				INNER JOIN @RollBackSO rso ON rso.PackingId = pdsf.PackingId
			WHERE st.PoStatus IN (1, 5) AND st.DeliveryFlowId = 2

			/*-- Cập nhật trạng thái của phiếu PGH(DE_DeliverySlip) là: 29 cho hàng giao thẳng không nhập trạm */
			UPDATE
				DE_DeliverySlips
			SET
				[StatusId]	   =	dt.StatusId,
				[DateModified]  =	GETDATE(),
				[ModifiedBy]	   =	@userId,
				[IsConfirmed]   =	1
			FROM @DeliveryTable dt
			WHERE dbo.DE_DeliverySlips.DeliverySlipsId = dt.DeliverySlipsId

			DECLARE @LogInsertCash TABLE (CashId int, DeliverySlipsId int)

			INSERT INTO CA_CashDelivery([UserIdDelivery],[Visible],[IsCompleted],[DateCompleted],[CreatedBy],[TotalMoney], [DeliverySlipsId])
			OUTPUT INSERTED.CashId, INSERTED.DeliverySlipsId INTO @LogInsertCash(CashId, DeliverySlipsId)
			SELECT dds.CodId, 1, 0, GETDATE(), @userId, 0, dds.DeliverySlipsId
			FROM @DeliveryTableCash dtc INNER JOIN dbo.DE_DeliverySlips dds ON dds.DeliverySlipsId = dtc.DeliverySlipsId

			--INSERT CA_CashDeliveryDetails------------------------------------------------------------------------------------------------------->
			INSERT INTO CA_CashDeliveryDetails([CashId],[SoDetailId],[Quantity])
			SELECT
				lic.CashId, sd.SoDetailId , ddsd.QuantityCompleted
			FROM @LogInsertCash lic
				INNER JOIN DE_DeliverySlipsDetails ddsd ON ddsd.DeliverySlipsId = lic.DeliverySlipsId
				INNER JOIN dbo.SO_Detail sd ON (sd.ParentSoDetailId = ddsd.SoDetailId or sd.SoDetailId = ddsd.SoDetailId)
			WHERE
				sd.SODetailStatusID	    =	13	AND
				sd.Visible		    =	1	AND
				ddsd.IsCompleted	    =	1

			--INSERT CA_CashDeliverySlip_Logs------------------------------------------------------------------------------------------------------->
			INSERT INTO CA_CashDeliverySlip_Logs([CashId],[DeliverySlipsId],[CreatedBy])
			SELECT lic.CashId, lic.DeliverySlipsId, @userId
			FROM @LogInsertCash lic

		END
		/*END ============ Cap nhat lai tien da tra cho cac PO huy ========================*/

		---------- Update SMS logtoPic ----------------------------
		DECLARE @SoDetailFull TABLE (
			ClientSoId bigint,
			SoDetailId bigint,
			StatusId int,
			SID_Current int,
			SidPic int
		)

		DECLARE @SoFull TABLE (
			ClientSoId bigint,
			SidPic int
		)

		INSERT INTO @SoDetailFull
		SELECT
			sd.ClientSoId,
			sd.SODetailID,
			sd.SODetailStatusID,
			sd.SID_CurrentStation,
			d.SID
		FROM dbo.SO_Detail sd WITH (NOLOCK)
			INNER JOIN (SELECT DISTINCT s.ClientSoId, s2.SID
						FROM @SodetailTable st
							INNER JOIN dbo.SO s WITH (NOLOCK) ON s.ClientSoId = st.ClientSoId
							INNER JOIN dbo.Stations s2 WITH (NOLOCK) ON s.DeliveryCityId = s2.CityID AND s.DeliveryDistId = s2.DistID AND s.DeliveryWardId = s2.WardID AND s.DeliveryStreetId = s2.StreetID AND s.DeliveryHouseN0 = s2.HouseN0
						WHERE st.PoStatus = 5 AND ISNULL(s.IsDeliveryPiC, 0) = 1) AS d ON d.ClientSoId = sd.ClientSoId

		INSERT INTO @SoFull
		SELECT DISTINCT sdf.ClientSoId, sdf.SidPic
		FROM @SoDetailFull sdf
		WHERE sdf.SID_Current = sdf.SidPic
			AND sdf.StatusId = 7
			AND NOT EXISTS (
				SELECT 1 FROM @SoDetailFull sdf2
				WHERE sdf2.ClientSoId = sdf.ClientSoId
					AND (sdf2.StatusId NOT IN (7,13,14,15,16) OR sdf2.SID_Current <> sdf.SidPic))

		IF EXISTS(SELECT 1 FROM @SoFull sf)
		BEGIN
			-- INSERT TO CS SMS
			BEGIN TRY
			INSERT INTO dbo.Log_PickupPointToCs
			(
				dbo.Log_PickupPointToCs.SoId,
				dbo.Log_PickupPointToCs.ClientSoId,
				dbo.Log_PickupPointToCs.CustomerName,
				dbo.Log_PickupPointToCs.CustomerId,
				dbo.Log_PickupPointToCs.StreetName,
				dbo.Log_PickupPointToCs.StreetId,
				dbo.Log_PickupPointToCs.DistrictName,
				dbo.Log_PickupPointToCs.DistrictId,
				dbo.Log_PickupPointToCs.CityName,
				dbo.Log_PickupPointToCs.CityId,
				dbo.Log_PickupPointToCs.WardId,
				dbo.Log_PickupPointToCs.WardName,
				dbo.Log_PickupPointToCs.PromiseDate,
				dbo.Log_PickupPointToCs.UserId,
				dbo.Log_PickupPointToCs.UserName,
				dbo.Log_PickupPointToCs.CreateDate,
				dbo.Log_PickupPointToCs.IsComplete,
				dbo.Log_PickupPointToCs.NumCount
			)
			SELECT DISTINCT
				s.SOID,
				s.ClientSoId,
				s.CustomerName,
				s.CustomerId,
				[as].StreetName,
				s.DeliveryStreetId,
				ad.DistrictName,
				s.DeliveryDistId,
				ac.CityName,
				s.DeliveryCityId,
				s.DeliveryWardId,
				aw.WardName,
				GETDATE(),
				@userId,
				(SELECT uu.FullName FROM dbo.UM_Users uu WITH (NOLOCK) WHERE uu.UserId = @userId),
				GETDATE(),
				0,
				0
			FROM dbo.SO s WITH (NOLOCK)
				INNER JOIN @SoFull sf ON sf.ClientSoId = s.ClientSoId
				LEFT JOIN dbo.AD_Street [as] WITH (NOLOCK) ON [as].StreetId = s.DeliveryStreetId
				LEFT JOIN dbo.AD_District ad WITH (NOLOCK) ON ad.DistrictId = s.DeliveryDistId
				LEFT JOIN dbo.AD_City ac WITH (NOLOCK) ON ac.CityId = s.DeliveryCityId
				LEFT JOIN dbo.AD_Ward aw WITH (NOLOCK) ON aw.WardId = s.DeliveryWardId

			END TRY
			BEGIN CATCH /**/ END CATCH
		END
		-----------------------------------------------------------

		SET @Result = 1;
		SELECT @result AS IsSuccess, '' AS MessageError
	COMMIT TRAN T
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN T
		SET @result=0;
		SET @Msg = ERROR_MESSAGE();

		IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
		BEGIN
			SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
			IF (@RetryCounter >=1) -- Check whether Retry Counter reached to 3
			BEGIN
				SET @Msg = N'Hệ thống xử lý phát sinh lỗi! Xin hãy thử lại sau vài giây!';
			END
			ELSE
			BEGIN
				WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
				GOTO RETRY	-- Go to Label RETRY
			END
		END

		SELECT @result AS IsSuccess, @Msg AS MessageError
	END CATCH

END