Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [Adayroi_TMS]
- GO
- /****** Object: StoredProcedure [dbo].[APIMobileCOD_PO_UpdatePOStatusID_V01] Script Date: 7/27/2017 11:35:46 AM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Entity Name: APIMobileCOD_PO_UpdatePOStatusID_V01
- -- Author: Hoàng Anh Thủy
- -- Create date: 14/12/2014 09:44:33
- -- Description: Cap nhat trang thai danh sach pickup
- -- =============================================
- ALTER PROCEDURE [dbo].[APIMobileCOD_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 @PoIdNotUpdte AS TABLE (PoId bigint, ResultID INT, ResultMessage NVARCHAR(200))
- 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,
- IsSpecialMc bit,
- Latitude decimal(9, 6),
- Longitude decimal(9, 6),
- ClientMerchantWhId int,
- UpdateTime datetime,
- 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
- --, CASE WHEN r.ReasonName LIKE '%Lý do khác%' THEN S.ReasonName ELSE r.ReasonName END
- , po.DeliveryFlowId
- , sd.SOId
- , sd.Amount
- , sd.SellPrice
- , sd.SODetailStatusID
- , CASE WHEN ISNULL(cm.IsSuperMarket, 0) = 1 OR ISNULL(cm.ClientMerchantTypeId, 0) = 3 THEN 1 ELSE 0 END AS IsSpecialMc
- , S.Lat AS Latitude
- , S.Long AS Longitude
- , po.ClientMerchantWarehouseId AS ClientMerchantWhId
- , CONVERT(datetime, S.UpdateTime)
- , sd.ClientSoId
- , S.AgainFromTime
- , S.AgainToTime
- , 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(max) 'ReasonName',
- Lat decimal(9, 6) 'Lat',
- Long decimal(9, 6) 'Long',
- UpdateTime varchar(20) 'UpdateTime',
- AgainFromTime varchar(20) 'AgainFromTime',
- AgainToTime varchar(20) 'AgainToTime'
- ) 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
- LEFT JOIN dbo.Reason r WITH (NOLOCK) ON r.ReasonId = S.ReasonId
- INNER JOIN dbo.ClientMerchant cm WITH (NOLOCK) ON cm.ClientMerchantId = sd.ClientMerchantId
- 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
- /* ------------------------------------------------------- */
- INSERT INTO @PoIdNotUpdte
- (
- PoId,
- ResultID,
- ResultMessage
- )
- SELECT S.PoId, 1, N'PO hợp lệ'
- FROM openxml(@iDoc, '/ArrayOfPoConfirmPost/PoConfirmPost', 8) WITH
- (
- PoId BIGINT 'PoId',
- StatusId int 'StatusId',
- ReasonId int 'ReasonId'
- ) S
- UPDATE @PoIdNotUpdte
- SET
- ResultID = -1,
- ResultMessage = N'Đã cập nhật bởi phiên làm việc khác'
- WHERE [@PoIdNotUpdte].PoId IN
- (
- SELECT S.PoId
- FROM @SodetailTable AS S
- INNER JOIN PickUpDetail pud ON pud.PoId = S.PoId AND pud.PuId = S.PuId
- WHERE S.SODetailStatusID <> 2 OR pud.PuDetailStatus = 1
- )
- /* 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
- WHERE EXISTS (SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1) -- Khong insert nhung SoDetailId thuoc PO da cap nhat
- AND NOT EXISTS (SELECT 1 FROM dbo.SO_Detail_Serial sds WITH (NOLOCK) WHERE sds.SODetailID = S.SoDetailId AND sds.SerialNumber = S.ImeiSerial)
- EXEC sp_xml_removedocument @idoc;
- EXEC sp_xml_removedocument @iDocImei;
- /* -------------------------------------------------------- */
- DECLARE @UserStationId INT = 0
- SELECT @UserStationId = uu.SID FROM dbo.UM_Users uu WHERE uu.UserId = @userId
- END TRY
- BEGIN CATCH
- UPDATE @PoIdNotUpdte
- SET
- ResultID = -2,
- ResultMessage = ERROR_MESSAGE()
- SELECT * FROM @PoIdNotUpdte pinu
- RETURN
- END CATCH
- BEGIN TRAN T
- BEGIN TRY
- DECLARE @DeliveryTable TABLE (DeliverySlipsId int, StatusId int)
- DECLARE @DeliveryTableCash TABLE (DeliverySlipsId int)
- IF EXISTS (SELECT 1 FROM @SoDetailSerial S)
- 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
- --EXEC sp_xml_removedocument @iDocImei;
- --Update lại trạng thái cho PickUp
- UPDATE [dbo].[PickUp]
- SET [PuStatus] = 6, [FinishDate] = GETDATE()
- FROM dbo.PickUp pu WITH (NOLOCK)
- 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 WITH (NOLOCK) INNER JOIN [dbo].[PickUpDetail] PD WITH (NOLOCK) 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
- INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = st1.PoId AND pinu.ResultID = 1
- 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,
- dbo.PickUpDetail.DateSaveMobile = S.UpdateTime
- FROM dbo.PickUpDetail pud WITH (NOLOCK)
- INNER JOIN (SELECT DISTINCT st.PoId, st.PuId, st.PoStatus, st.ReasonId, st.ReasonName, st.UpdateTime FROM @SodetailTable st) AS S ON pud.PoId = S.PoId AND pud.PuId = S.PuId
- INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = S.PoId AND pinu.ResultID = 1
- --AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- -- 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
- INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = st.PoId
- WHERE st.PoStatus = 1 AND pinu.ResultID = 1
- ------------------------
- UPDATE PurchaseOrder 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 WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON P.PoId = S.PoId
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- 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 WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON PD.PoId = S.PoId
- INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = S.PoId AND pinu.ResultID = 1
- -- 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, CONCAT(N'Phiếu lấy hàng: ', S.PuId), @userId, GETDATE(), @userId, 2, S.SoDetailStatus, @UserStationId
- FROM @SodetailTable S
- WHERE EXISTS (SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- -- 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(), @userId, 2, S.PoStatus, @UserStationId
- FROM @SodetailTable S
- WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- -- Update trạng thái SoDetail
- UPDATE sd SET
- sd.[SODetailStatusID] = S.SoDetailStatus,
- sd.ReasonID = (CASE WHEN S.PoStatus = 5 THEN S.ReasonId ELSE NULL END),
- sd.[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 WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON S.SoDetailId = SD.SODetailID
- LEFT JOIN dbo.Reason r ON r.ReasonId = S.ReasonId
- WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- -- Update DeliveryDetail visible = 0 cho PO huy
- UPDATE dbo.DE_DeliverySlipsDetails
- SET dbo.DE_DeliverySlipsDetails.Visible = 0
- FROM dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK)
- 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)
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- IF EXISTS (SELECT 1 FROM @SodetailTable S
- WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- )
- 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 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- 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
- --Thêm dữ liệu ClearBin cho PO NCC đặc biệt hoặc siêu thị
- INSERT INTO dbo.OMS_Tracking_ClearBin
- (
- ClientPoId,
- IsCompleted,
- RetryCount,
- CreatedDate,
- LogDetails,
- CreatedBy,
- ResponseMessage,
- LastModifiedDate
- )
- SELECT DISTINCT st.PoId AS ClientPoId, 0, 0, GETDATE(), '' AS LogDetais, @userId, NULL, GETDATE()
- FROM @SodetailTable st
- WHERE st.IsSpecialMc = 1 AND st.PoStatus IN (3, 4) AND
- EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID=1)
- BEGIN TRY
- -- Thêm dữ liệu PickupLocation
- INSERT INTO dbo.PickupWithLocation
- (
- dbo.PickupWithLocation.PuId,
- dbo.PickupWithLocation.ClientMerchantWhId,
- dbo.PickupWithLocation.Latitude,
- dbo.PickupWithLocation.Longitude,
- dbo.PickupWithLocation.CreatedBy,
- dbo.PickupWithLocation.UpdateTime
- )
- SELECT DISTINCT st.PuId, st.ClientMerchantWhId, st.Latitude, st.Longitude, @userId, st.UpdateTime
- FROM @SodetailTable st
- WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
- --AND NOT EXISTS (SELECT 1 FROM dbo.PickupWithLocation pwl WHERE pwl.PuId = st.PuId AND pwl.ClientMerchantWhId = st.ClientMerchantWhId)
- END TRY
- BEGIN CATCH /**/ END CATCH
- 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 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID=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 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- /* Insert data vào table: SAP_CreateGoodsReceiptDO --------------------------------------------------------------*/
- DECLARE @tbl as SAP_TableGoodsReceivePO
- INSERT INTO @tbl
- SELECT
- sp.PoId
- , 315 AS ifcode
- FROM @SapPo sp
- WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = sp.PoId AND pinu.ResultID = 1)
- 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.StatusId,
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 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 || 8 || Huy ------------------------------------------------------------------*/
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
- 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 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
- /*---------------------------------------------------------------------------------------------------------*/
- /* 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 AND EXISTS (SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1))
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- --Ghi log
- INSERT INTO [WH_DeliverySlip_Logs] ([DeliverySlipsId], [WarehouseSlipId], [CreatedBy])
- SELECT ddsd.DeliverySlipsId, @warehouseSlipId, @userId
- FROM
- @SodetailTable S
- INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.SoDetailId = S.SoDetailId
- WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- GROUP BY DeliverySlipsId
- --======================================================
- --Sy them 2015-06-03======================================================
- --======================================================
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- --======================================================
- --Sy them 2015-06-03======================================================
- --======================================================
- END
- /*BEGIN ========== Cap nhat lai tien da tra cho cac PO huy - Chi cho hang giao thang ========================*/
- IF EXISTS(SELECT 1 FROM @SodetailTable S
- WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
- )
- 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
- AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)),
- 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 ON pd.SoDetailId = S.SODetailID
- WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
- 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 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 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
- -----------------------------------------------------------
- COMMIT TRAN T
- --Cap nhat gia tri cho ket qua
- UPDATE @PoIdNotUpdte
- SET
- ResultID = 2,
- ResultMessage = (
- CASE WHEN EXISTS (SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.ResultID=-1 )THEN N'Có PO đã được cập nhật bởi phiên làm việc khác!'
- ELSE N'Cập nhật thành công!'
- END
- )
- WHERE EXISTS(SELECT 1 FROM dbo.PurchaseOrder po WHERE po.PoId = [@PoIdNotUpdte].PoId AND po.POStatusId <>2)
- AND [@PoIdNotUpdte].ResultID =1
- END TRY
- BEGIN CATCH
- ROLLBACK TRAN T
- UPDATE @PoIdNotUpdte
- SET
- ResultID = -2,
- ResultMessage = ERROR_MESSAGE()
- --SET @Msg = ERROR_MESSAGE();
- END CATCH
- SELECT *
- FROM @PoIdNotUpdte pinu
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement