Advertisement
Guest User

Untitled

a guest
Jul 26th, 2017
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 39.75 KB | None | 0 0
  1. USE [Adayroi_TMS]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[APIMobileCOD_PO_UpdatePOStatusID_V01] Script Date: 7/27/2017 11:35:46 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Entity Name: APIMobileCOD_PO_UpdatePOStatusID_V01
  10. -- Author: Hoàng Anh Thủy
  11. -- Create date: 14/12/2014 09:44:33
  12. -- Description: Cap nhat trang thai danh sach pickup
  13. -- =============================================
  14. ALTER PROCEDURE [dbo].[APIMobileCOD_PO_UpdatePOStatusID_V01]
  15. (
  16. @ListPoIds NVARCHAR(MAX),
  17. @StatusId INT,
  18. @IsOk bit = 1,
  19. @ListSoDetailSerials NVARCHAR(MAX),
  20. @userId int,
  21. @sessionkey varchar(255)
  22. )
  23. AS
  24. BEGIN
  25.  
  26. DECLARE @result BIT
  27. DECLARE @Msg NVARCHAR(MAX)
  28. DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
  29. BEGIN TRY
  30. EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @userId, @sessionkey, @PROC_NAME
  31.  
  32. DECLARE @iDoc INT
  33. DECLARE @iDocImei INT;
  34. EXEC sp_xml_preparedocument @iDoc out, @ListPoIds
  35. EXEC sp_xml_preparedocument @iDocImei out, @ListSoDetailSerials;
  36.  
  37. DECLARE @PoIdNotUpdte AS TABLE (PoId bigint, ResultID INT, ResultMessage NVARCHAR(200))
  38.  
  39. DECLARE @SodetailTable TABLE (
  40. SoDetailId bigint,
  41. PoId bigint,
  42. SoDetailStatus int,
  43. PoStatus int,
  44. PuId int,
  45. ReasonId int,
  46. ReasonName nvarchar(250),
  47. DeliveryFlowId int,
  48. SOId bigint,
  49. Amount int,
  50. SellPrice decimal(18,4),
  51. SODetailStatusID int,
  52. IsSpecialMc bit,
  53. Latitude decimal(9, 6),
  54. Longitude decimal(9, 6),
  55. ClientMerchantWhId int,
  56. UpdateTime datetime,
  57. ClientSoId bigint,
  58. MerchantDatePickupFrom varchar(20),
  59. MerchantDatePickupTo varchar(20),
  60. IsUsedVinId bit,
  61. SourceClientId int,
  62. MTID int,
  63. IsVinIdCard bit,
  64. ProductItemId int,
  65. HybrisProductItemId varchar(30),
  66. SAPReservationNumber nvarchar(100),
  67. SAPPONumberInterim nvarchar(100),
  68. SAPReservationLineItem nvarchar(200)
  69. PRIMARY KEY (SoDetailId, PoId, PuId)
  70. )
  71.  
  72. INSERT INTO @SodetailTable
  73. SELECT sd.SODetailID
  74. , po.PoId
  75. , CASE WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 AND ISNULL(s2.IsDeliveryPiC, 0) = 0 THEN 7
  76. WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 AND ISNULL(s2.IsDeliveryPiC, 0) = 1 THEN 18
  77. WHEN S.StatusId = 5 THEN 16 ELSE S.StatusId END
  78. , CASE WHEN S.StatusId = 3 AND po.DeliveryFlowId = 2 THEN 4 ELSE S.StatusId END
  79. , S.PuId
  80. , S.ReasonId
  81. , S.ReasonName
  82. --, CASE WHEN r.ReasonName LIKE '%Lý do khác%' THEN S.ReasonName ELSE r.ReasonName END
  83. , po.DeliveryFlowId
  84. , sd.SOId
  85. , sd.Amount
  86. , sd.SellPrice
  87. , sd.SODetailStatusID
  88. , CASE WHEN ISNULL(cm.IsSuperMarket, 0) = 1 OR ISNULL(cm.ClientMerchantTypeId, 0) = 3 THEN 1 ELSE 0 END AS IsSpecialMc
  89. , S.Lat AS Latitude
  90. , S.Long AS Longitude
  91. , po.ClientMerchantWarehouseId AS ClientMerchantWhId
  92. , CONVERT(datetime, S.UpdateTime)
  93. , sd.ClientSoId
  94. , S.AgainFromTime
  95. , S.AgainToTime
  96. , ISNULL(po.IsUsedVinId, 0)
  97. , s2.SourceClientId
  98. , po.MTID
  99. , sd.IsVinIdCard
  100. , cp.ProductItemId
  101. , cp.HybrisProductItemId
  102. , sd.SAPReservationNumber
  103. , sd.SAPPONumberInterim
  104. , sd.SAPReservationLineItem
  105.  
  106. FROM OPENXML(@iDoc, '/ArrayOfPoConfirmPost/PoConfirmPost', 8) WITH
  107. (
  108. PoId BIGINT 'PoId',
  109. StatusId int 'StatusId',
  110. ReasonId int 'ReasonId',
  111. PuId int 'PuId',
  112. ReasonName nvarchar(max) 'ReasonName',
  113. Lat decimal(9, 6) 'Lat',
  114. Long decimal(9, 6) 'Long',
  115. UpdateTime varchar(20) 'UpdateTime',
  116. AgainFromTime varchar(20) 'AgainFromTime',
  117. AgainToTime varchar(20) 'AgainToTime'
  118. ) AS S
  119. INNER JOIN dbo.PurchaseOrder po WITH (NOLOCK) ON po.PoId = S.PoId
  120. INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.ClientPoId = po.ClientPoId
  121. LEFT JOIN dbo.Reason r WITH (NOLOCK) ON r.ReasonId = S.ReasonId
  122. INNER JOIN dbo.ClientMerchant cm WITH (NOLOCK) ON cm.ClientMerchantId = sd.ClientMerchantId
  123. INNER JOIN dbo.SO s2 WITH (NOLOCK) ON s2.SOID = sd.SOId
  124. INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
  125. WHERE sd.Visible = 1 AND S.StatusId <> 2
  126.  
  127.  
  128.  
  129. /*------------------------------------------------------*/
  130. -- Thong tin phi PO
  131. DECLARE @PoFeeInfo TABLE (
  132. ClientSoId bigint,
  133. ClientPoId bigint,
  134. ProductItemId int,
  135. Fee decimal(18,0)
  136. )
  137.  
  138. INSERT INTO @PoFeeInfo
  139. SELECT DISTINCT
  140. sdit.ClientSoId,
  141. sdit.PoId,
  142. pofm.ProductItemId,
  143. pofm.Fee
  144. FROM @SodetailTable sdit
  145. INNER JOIN dbo.PurchaseOrder_Fee_Map pofm WITH (NOLOCK) ON sdit.PoId = pofm.ClientPoId
  146. WHERE sdit.SourceClientId = 1 AND pofm.IsActiveFee = 1 AND pofm.Visible = 1 AND pofm.Status = 1
  147. /*--------------------------------------------------------*/
  148.  
  149. /* Sap Po ------------------------------------------------*/
  150. DECLARE @SapPo TABLE (
  151. PoId bigint
  152. )
  153.  
  154. INSERT INTO @SapPo
  155. SELECT DISTINCT st.PoId
  156. FROM @SodetailTable st
  157. WHERE st.PoStatus IN (3,4) AND st.SourceClientId = 2 AND st.MTID IN (2,3,4,5)
  158. GROUP BY st.PoId
  159. /* ------------------------------------------------------- */
  160.  
  161. INSERT INTO @PoIdNotUpdte
  162. (
  163. PoId,
  164. ResultID,
  165. ResultMessage
  166. )
  167. SELECT S.PoId, 1, N'PO hợp lệ'
  168. FROM openxml(@iDoc, '/ArrayOfPoConfirmPost/PoConfirmPost', 8) WITH
  169. (
  170. PoId BIGINT 'PoId',
  171. StatusId int 'StatusId',
  172. ReasonId int 'ReasonId'
  173. ) S
  174.  
  175. UPDATE @PoIdNotUpdte
  176. SET
  177. ResultID = -1,
  178. ResultMessage = N'Đã cập nhật bởi phiên làm việc khác'
  179. WHERE [@PoIdNotUpdte].PoId IN
  180. (
  181. SELECT S.PoId
  182. FROM @SodetailTable AS S
  183. INNER JOIN PickUpDetail pud ON pud.PoId = S.PoId AND pud.PuId = S.PuId
  184. WHERE S.SODetailStatusID <> 2 OR pud.PuDetailStatus = 1
  185. )
  186.  
  187. /* Input SoDetail Serial----------------------------------- */
  188. DECLARE @SoDetailSerial TABLE (
  189. SoDetailId bigint,
  190. ImeiSerial VARCHAR(250),
  191. IsVinIdCard bit
  192. )
  193.  
  194. INSERT INTO @SoDetailSerial
  195. SELECT S.SoDetailId,
  196. S.ImeiSerial,
  197. st.IsVinIdCard
  198. FROM OPENXML(@iDocImei, '/ArrayOfImeiProductBulkyGoods/ImeiProductBulkyGoods',8)
  199. WITH (
  200. SoDetailId BIGINT 'SoDetailId',
  201. ImeiSerial VARCHAR(250) 'ImeiSerial'
  202. ) S
  203. INNER JOIN @SodetailTable st ON st.SoDetailId = S.SoDetailId
  204. 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
  205. AND NOT EXISTS (SELECT 1 FROM dbo.SO_Detail_Serial sds WITH (NOLOCK) WHERE sds.SODetailID = S.SoDetailId AND sds.SerialNumber = S.ImeiSerial)
  206.  
  207. EXEC sp_xml_removedocument @idoc;
  208. EXEC sp_xml_removedocument @iDocImei;
  209. /* -------------------------------------------------------- */
  210.  
  211. DECLARE @UserStationId INT = 0
  212. SELECT @UserStationId = uu.SID FROM dbo.UM_Users uu WHERE uu.UserId = @userId
  213. END TRY
  214. BEGIN CATCH
  215. UPDATE @PoIdNotUpdte
  216. SET
  217. ResultID = -2,
  218. ResultMessage = ERROR_MESSAGE()
  219.  
  220. SELECT * FROM @PoIdNotUpdte pinu
  221. RETURN
  222. END CATCH
  223.  
  224. BEGIN TRAN T
  225. BEGIN TRY
  226. DECLARE @DeliveryTable TABLE (DeliverySlipsId int, StatusId int)
  227. DECLARE @DeliveryTableCash TABLE (DeliverySlipsId int)
  228.  
  229. IF EXISTS (SELECT 1 FROM @SoDetailSerial S)
  230. BEGIN
  231. -- Insert into [dbo].[SO_Detail_Serial]
  232. INSERT INTO [dbo].[SO_Detail_Serial]
  233. ([SODetailID]
  234. ,[SerialNumber]
  235. ,[ExpireDate]
  236. ,[CreatedDate]
  237. ,[CreatedBy]
  238. ,[StatusId])
  239. SELECT S.SoDetailId, S.ImeiSerial, NULL, GETDATE(), @userId, CASE WHEN st.DeliveryFlowId = 2 THEN 59 ELSE 67 END
  240. FROM
  241. @SoDetailSerial S
  242. INNER JOIN @SodetailTable st ON st.SoDetailId = S.SoDetailId
  243. END
  244.  
  245. --EXEC sp_xml_removedocument @iDocImei;
  246.  
  247. --Update lại trạng thái cho PickUp
  248. UPDATE [dbo].[PickUp]
  249. SET [PuStatus] = 6, [FinishDate] = GETDATE()
  250. FROM dbo.PickUp pu WITH (NOLOCK)
  251. INNER JOIN (SELECT DISTINCT st.PuId FROM @SodetailTable st) AS S ON S.PuId = pu.PuId
  252. WHERE pu.PuStatus = 5 AND
  253. NOT EXISTS(SELECT 1
  254. FROM [dbo].[PurchaseOrder] PO WITH (NOLOCK) INNER JOIN [dbo].[PickUpDetail] PD WITH (NOLOCK) ON PO.PoId = PD.[PoId]
  255. WHERE PD.[PuId] = pu.PuId AND PO.POStatusId = 2 AND ISNULL(PD.PuDetailStatus, 0) = 0
  256. AND NOT EXISTS(
  257. SELECT 1 FROM @SodetailTable st1
  258. INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = st1.PoId AND pinu.ResultID = 1
  259. WHERE st1.PuId = PD.PuId AND st1.PoId = PD.PoId))
  260.  
  261. UPDATE dbo.PickUpDetail
  262. SET
  263. dbo.PickUpDetail.IsComplete = CASE WHEN S.PoStatus IN (3, 4) THEN 1 ELSE 0 END, -- bit
  264. dbo.PickUpDetail.IsAgain = CASE WHEN S.PoStatus = 1 THEN 1 ELSE 0 END, -- bit
  265. dbo.PickUpDetail.IsCancel = CASE WHEN S.PoStatus = 5 THEN 1 ELSE 0 END, -- bit
  266. dbo.PickUpDetail.ReasonId = S.ReasonId, -- int
  267. dbo.PickUpDetail.ReasonDescription = S.ReasonName,
  268. dbo.PickUpDetail.ModifyDate = GETDATE(), -- datetime
  269. dbo.PickUpDetail.ModifyBy = @userId, -- int
  270. dbo.PickUpDetail.PuDetailStatus = 1,
  271. dbo.PickUpDetail.DateSaveMobile = S.UpdateTime
  272. FROM dbo.PickUpDetail pud WITH (NOLOCK)
  273. 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
  274. INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = S.PoId AND pinu.ResultID = 1
  275. --AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  276.  
  277. -- Them log SpecialNotes
  278. DECLARE @UserName nvarchar(150)
  279. SELECT @UserName = FullName FROM dbo.UM_Users uu WHERE uu.UserId = @userId
  280. INSERT INTO dbo.SpecialNotes
  281. (
  282. --Id - this column value is auto-generated
  283. dbo.SpecialNotes.CreatedBy,
  284. dbo.SpecialNotes.FullName,
  285. dbo.SpecialNotes.DepartmentName,
  286. dbo.SpecialNotes.CreatedDate,
  287. dbo.SpecialNotes.Note,
  288. dbo.SpecialNotes.ClientSoId,
  289. dbo.SpecialNotes.SoId
  290. )
  291. SELECT DISTINCT
  292. @userId,
  293. @UserName,
  294. 'TMS',
  295. GETDATE(),
  296. CONCAT(N'PO ', st.PoId, N' hẹn lại: ', st.ReasonName), -- , FORMAT(st.SourceClientId, N'00'),
  297. st.ClientSoId,
  298. st.SOId
  299. FROM @SodetailTable st
  300. INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = st.PoId
  301. WHERE st.PoStatus = 1 AND pinu.ResultID = 1
  302. ------------------------
  303.  
  304. UPDATE PurchaseOrder SET POStatusId = S.PoStatus,
  305. CompleteDatetime = CASE WHEN S.PoStatus > 2 THEN GETDATE() ELSE NULL END,
  306. UID_COD = CASE WHEN S.PoStatus = 1 THEN NULL ELSE UID_COD END,
  307. MerchantPickupDateFrom = CASE WHEN S.PoStatus = 1 THEN CONVERT(Datetime, S.MerchantDatePickupFrom, 120) ELSE P.MerchantPickupDateFrom END,
  308. MerchantPickupDateTo = CASE WHEN S.PoStatus = 1 THEN CONVERT(Datetime, S.MerchantDatePickupTo, 120) ELSE P.MerchantPickupDateTo END
  309. FROM PurchaseOrder P WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON P.PoId = S.PoId
  310. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
  311.  
  312. UPDATE [dbo].[PurchaseOrder_Detail]
  313. SET [PODetailStatusID] = S.PoStatus,
  314. [ReasonID] = (CASE WHEN S.PoStatus = 5 THEN S.ReasonId ELSE NULL END),
  315. [Note] = CASE WHEN S.PoStatus = 1 THEN S.ReasonName ELSE PD.Note END
  316. FROM [PurchaseOrder_Detail] PD WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON PD.PoId = S.PoId
  317. INNER JOIN @PoIdNotUpdte pinu ON pinu.PoId = S.PoId AND pinu.ResultID = 1
  318.  
  319. -- Insert into Log_Information action type SoDetail
  320. INSERT INTO [dbo].[Log_Information]
  321. ([ObjectTypeId]
  322. ,[ObjectId]
  323. ,[ActionId]
  324. ,[LogDetails]
  325. ,[CreatedBy]
  326. ,[CreatedDate]
  327. ,[UID_AssignTo]
  328. ,[OldStatusId]
  329. ,[NewStatusId]
  330. ,StationId)
  331. SELECT 3, S.SoDetailId, 3, CONCAT(N'Phiếu lấy hàng: ', S.PuId), @userId, GETDATE(), @userId, 2, S.SoDetailStatus, @UserStationId
  332. FROM @SodetailTable S
  333. WHERE EXISTS (SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
  334.  
  335. -- Insert into Log_Information action type PurchaseOrder
  336. INSERT INTO [dbo].[Log_Information]( [ObjectTypeId],
  337. [ObjectId],
  338. [ActionId],
  339. [LogDetails],
  340. [CreatedBy],
  341. [CreatedDate],
  342. [UID_AssignTo],
  343. [OldStatusId],
  344. [NewStatusId],
  345. StationId )
  346. SELECT DISTINCT 2, S.PoId, 3, NULL, @userId, GETDATE(), @userId, 2, S.PoStatus, @UserStationId
  347. FROM @SodetailTable S
  348. WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
  349.  
  350. -- Update trạng thái SoDetail
  351. UPDATE sd SET
  352. sd.[SODetailStatusID] = S.SoDetailStatus,
  353. sd.ReasonID = (CASE WHEN S.PoStatus = 5 THEN S.ReasonId ELSE NULL END),
  354. sd.[ReasonCanceledOrther] = CASE WHEN S.PoStatus = 5 AND r.ReasonName LIKE N'%Lý do khác%' THEN S.ReasonName ELSE N'' END,
  355. SoDetailStatusLastUpdateTime = GETDATE()
  356. FROM SO_Detail SD WITH (NOLOCK) INNER JOIN @SodetailTable AS S ON S.SoDetailId = SD.SODetailID
  357. LEFT JOIN dbo.Reason r ON r.ReasonId = S.ReasonId
  358. WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
  359.  
  360. -- Update DeliveryDetail visible = 0 cho PO huy
  361. UPDATE dbo.DE_DeliverySlipsDetails
  362. SET dbo.DE_DeliverySlipsDetails.Visible = 0
  363. FROM dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK)
  364. INNER JOIN @SodetailTable AS S ON S.SoDetailId = ddsd.SoDetailId
  365. WHERE ddsd.Visible = 1 AND S.DeliveryFlowId = 2 AND (S.PoStatus = 5 OR S.PoStatus = 1)
  366. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)
  367.  
  368. IF EXISTS (SELECT 1 FROM @SodetailTable S
  369. 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)
  370. )
  371. BEGIN
  372. INSERT INTO @DeliveryTable
  373. SELECT c.DeliverySlipsId,
  374. CASE WHEN EXISTS (
  375. SELECT 1 FROM dbo.DE_DeliverySlipsDetails ddsd1 WITH (NOLOCK)
  376. WHERE ddsd1.DeliverySlipsId = c.DeliverySlipsId AND ddsd1.Visible = 1 AND (ISNULL(ddsd1.IsAgain, 0) = 1 OR ISNULL(ddsd1.IsCanceled, 0) = 1))
  377. THEN 28
  378. ELSE 29 END
  379. FROM (SELECT DISTINCT MAX(ddsd.DeliverySlipsId) AS DeliverySlipsId--po.PoId, SD.SOId
  380. FROM @SodetailTable S
  381. INNER JOIN dbo.Packing_Detail pd WITH (NOLOCK) ON pd.SoDetailId = S.SODetailID
  382. INNER JOIN dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK) ON ddsd.PackingDetailId = pd.PackingDetailId
  383. 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)
  384. GROUP BY S.SODetailID) c
  385. WHERE NOT EXISTS (
  386. SELECT 1
  387. FROM dbo.DE_DeliverySlipsDetails ddsd WITH (NOLOCK)
  388. INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.SODetailID = ddsd.SoDetailId
  389. WHERE ddsd.DeliverySlipsId = c.DeliverySlipsId AND sd.Visible = 1 AND ddsd.Visible = 1 AND ISNULL(ddsd.Status, 0) IN (0, 28))
  390.  
  391. INSERT INTO @DeliveryTableCash
  392. SELECT dt.DeliverySlipsId
  393. FROM @DeliveryTable dt
  394. WHERE EXISTS (
  395. SELECT 1
  396. FROM DE_DeliverySlipsDetails DESD WITH (NOLOCK)
  397. INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON (sd.ParentSoDetailId = DESD.SoDetailId or sd.SoDetailId = DESD.SoDetailId)
  398. WHERE
  399. sd.SODetailStatusID = 13 AND
  400. sd.Visible = 1 AND
  401. DESD.IsCompleted = 1 AND
  402. DESD.DeliverySlipsId = dt.DeliverySlipsId)
  403. END
  404.  
  405. --Thêm dữ liệu ClearBin cho PO NCC đặc biệt hoặc siêu thị
  406. INSERT INTO dbo.OMS_Tracking_ClearBin
  407. (
  408. ClientPoId,
  409. IsCompleted,
  410. RetryCount,
  411. CreatedDate,
  412. LogDetails,
  413. CreatedBy,
  414. ResponseMessage,
  415. LastModifiedDate
  416. )
  417. SELECT DISTINCT st.PoId AS ClientPoId, 0, 0, GETDATE(), '' AS LogDetais, @userId, NULL, GETDATE()
  418. FROM @SodetailTable st
  419. WHERE st.IsSpecialMc = 1 AND st.PoStatus IN (3, 4) AND
  420. EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID=1)
  421.  
  422. BEGIN TRY
  423. -- Thêm dữ liệu PickupLocation
  424. INSERT INTO dbo.PickupWithLocation
  425. (
  426. dbo.PickupWithLocation.PuId,
  427. dbo.PickupWithLocation.ClientMerchantWhId,
  428. dbo.PickupWithLocation.Latitude,
  429. dbo.PickupWithLocation.Longitude,
  430. dbo.PickupWithLocation.CreatedBy,
  431. dbo.PickupWithLocation.UpdateTime
  432. )
  433. SELECT DISTINCT st.PuId, st.ClientMerchantWhId, st.Latitude, st.Longitude, @userId, st.UpdateTime
  434. FROM @SodetailTable st
  435. WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  436. --AND NOT EXISTS (SELECT 1 FROM dbo.PickupWithLocation pwl WHERE pwl.PuId = st.PuId AND pwl.ClientMerchantWhId = st.ClientMerchantWhId)
  437. END TRY
  438. BEGIN CATCH /**/ END CATCH
  439.  
  440. INSERT INTO dbo.PurchaseOrder_MerchantDate
  441. (
  442. --Id - this column value is auto-generated
  443. dbo.PurchaseOrder_MerchantDate.PoId,
  444. dbo.PurchaseOrder_MerchantDate.DateFrom,
  445. dbo.PurchaseOrder_MerchantDate.DateTo,
  446. dbo.PurchaseOrder_MerchantDate.ReasonName,
  447. dbo.PurchaseOrder_MerchantDate.UserCreate,
  448. dbo.PurchaseOrder_MerchantDate.ImportSource
  449. )
  450. SELECT DISTINCT
  451. st.PoId,
  452. CONVERT(Datetime, st.MerchantDatePickupFrom, 120),
  453. CONVERT(Datetime, st.MerchantDatePickupTo, 120),
  454. N'TMS' + st.ReasonName,
  455. @userId,
  456. N'Logistics'
  457. FROM @SodetailTable st
  458. WHERE st.PoStatus = 1 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID=1)
  459.  
  460. INSERT INTO dbo.SAP_LogDateImportPoid
  461. (
  462. dbo.SAP_LogDateImportPoid.POID,
  463. dbo.SAP_LogDateImportPoid.ImportDate,
  464. dbo.SAP_LogDateImportPoid.CreatedDate
  465. )
  466. SELECT DISTINCT S.PoId, GETDATE(), GETDATE()
  467. FROM @SodetailTable AS S
  468. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  469.  
  470. /* Insert data vào table: SAP_CreateGoodsReceiptDO --------------------------------------------------------------*/
  471. DECLARE @tbl as SAP_TableGoodsReceivePO
  472. INSERT INTO @tbl
  473. SELECT
  474. sp.PoId
  475. , 315 AS ifcode
  476. FROM @SapPo sp
  477. WHERE EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = sp.PoId AND pinu.ResultID = 1)
  478. EXEC [APIClient_SAP_CreateGoodsReceiptDO_InsertLogToSync_Sale_V01] @tbl
  479. /*---------------------------------------------------------------------------------------------------------------*/
  480.  
  481. /* ------------------ Hybris status sync ------------------------------------------------------------------*/
  482. DECLARE @StatusUpdateInserted TABLE (
  483. Log_UpdateDeliveryId bigint,
  484. ClientSoId bigint,
  485. ClientPoId bigint
  486. )
  487.  
  488. INSERT INTO dbo.Log_UpdateDeliveryStatus
  489. (
  490. --Log_UpdateDeliveryId - this column value is auto-generated
  491. --dbo.Log_UpdateDeliveryStatus.StatusId,
  492. dbo.Log_UpdateDeliveryStatus.SourceClientId,
  493. dbo.Log_UpdateDeliveryStatus.ClientSoId,
  494. dbo.Log_UpdateDeliveryStatus.ClientPoId,
  495. dbo.Log_UpdateDeliveryStatus.CreatedBy,
  496. dbo.Log_UpdateDeliveryStatus.CreatedDatetime,
  497. dbo.Log_UpdateDeliveryStatus.Visible,
  498. dbo.Log_UpdateDeliveryStatus.RetryCount
  499. )
  500. OUTPUT INSERTED.Log_UpdateDeliveryId, INSERTED.ClientSoId, INSERTED.ClientPoId INTO @StatusUpdateInserted
  501. SELECT DISTINCT st.SourceClientId,
  502. st.ClientSoId,
  503. st.PoId,
  504. @userId,
  505. GETDATE(),
  506. 1,
  507. 0
  508. FROM @SodetailTable st
  509. WHERE st.SourceClientId = 2 AND st.SoDetailStatus <> 1
  510. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  511.  
  512. INSERT INTO dbo.Log_UpdateDeliveryStatus_Detail
  513. (
  514. --Log_UpdateDeliveryDetailId - this column value is auto-generated
  515. dbo.Log_UpdateDeliveryStatus_Detail.Log_UpdateDeliveryId,
  516. dbo.Log_UpdateDeliveryStatus_Detail.So_DetailID,
  517. dbo.Log_UpdateDeliveryStatus_Detail.OldStatus,
  518. dbo.Log_UpdateDeliveryStatus_Detail.CurrentStatus,
  519. dbo.Log_UpdateDeliveryStatus_Detail.Amount,
  520. dbo.Log_UpdateDeliveryStatus_Detail.SellPrice,
  521. dbo.Log_UpdateDeliveryStatus_Detail.HybrisProductItemId,
  522. dbo.Log_UpdateDeliveryStatus_Detail.SAPReservationNumber,
  523. dbo.Log_UpdateDeliveryStatus_Detail.SAPPONumberInterim,
  524. dbo.Log_UpdateDeliveryStatus_Detail.SAPReservationLineItem
  525. )
  526. SELECT DISTINCT sui.Log_UpdateDeliveryId,
  527. st.SoDetailId,
  528. 64,
  529. CASE WHEN st.SoDetailStatus = 1 THEN 8
  530. WHEN st.SoDetailStatus = 16 THEN 16777216
  531. ELSE 128 END,
  532. st.Amount,
  533. st.SellPrice,
  534. st.HybrisProductItemId,
  535. st.SAPReservationNumber,
  536. st.SAPPONumberInterim,
  537. st.SAPReservationLineItem
  538. FROM @SodetailTable st INNER JOIN @StatusUpdateInserted sui ON st.ClientSoId = sui.ClientSoId AND st.PoId = sui.ClientPoId
  539.  
  540. INSERT INTO dbo.Log_UpdateDeliveryStatus_Serial
  541. (
  542. --Log_UpdateStatusSerialId - this column value is auto-generated
  543. dbo.Log_UpdateDeliveryStatus_Serial.Log_UpdateDeliveryDetailId,
  544. dbo.Log_UpdateDeliveryStatus_Serial.SerialNumber
  545. )
  546. SELECT ludsd.Log_UpdateDeliveryDetailId,
  547. sds.ImeiSerial
  548. FROM dbo.Log_UpdateDeliveryStatus_Detail ludsd
  549. INNER JOIN @SoDetailSerial sds ON sds.SoDetailId = ludsd.So_DetailID
  550. INNER JOIN @StatusUpdateInserted sui ON sui.Log_UpdateDeliveryId = ludsd.Log_UpdateDeliveryId
  551. WHERE sds.IsVinIdCard = 0
  552. GROUP BY ludsd.Log_UpdateDeliveryDetailId, sds.ImeiSerial
  553. /*---------------------------------------------------------------------------------------------------------*/
  554.  
  555. /* ------------------ OMS status sync 64 -> 128 || 8 || Huy ------------------------------------------------------------------*/
  556. DECLARE @TblOms AS Oms_UpdateSoDetailStatus
  557.  
  558. INSERT INTO @TblOms -- Product Item
  559. (
  560. SOID,
  561. PO,
  562. ProductItemId,
  563. Amount,
  564. CurrentStatus,
  565. DeliveryReasonId,
  566. DeliveryNote,
  567. OldStatus,
  568. PriceCustomer,
  569. SAPReservationNumber,
  570. SAPReservationLineItem,
  571. SAPPONumberInterim,
  572. Serials
  573. )
  574. SELECT
  575. st.ClientSoId,
  576. st.PoId,
  577. st.ProductItemId,
  578. st.Amount,
  579. CASE WHEN st.PoStatus = 1 THEN 8 ELSE 128 END,
  580. 0,
  581. '',
  582. 64,
  583. st.SellPrice,
  584. st.SAPReservationNumber,
  585. st.SAPReservationLineItem,
  586. st.SAPPONumberInterim,
  587. (
  588. SELECT sds.ImeiSerial AS string
  589. FROM @SoDetailSerial sds
  590. WHERE sds.SoDetailId = st.SoDetailId AND sds.IsVinIdCard = 0
  591. FOR XML PATH ('')
  592. )
  593. FROM @SodetailTable st
  594. WHERE st.SourceClientId = 1 AND st.PoStatus <> 5
  595. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  596. GROUP BY st.ClientSoId,
  597. st.PoId,
  598. st.ProductItemId,
  599. st.Amount,
  600. st.SellPrice,
  601. st.SAPReservationNumber,
  602. st.SAPReservationLineItem,
  603. st.SAPPONumberInterim,
  604. st.SoDetailId,
  605. st.PoStatus
  606.  
  607. INSERT INTO @TblOms -- Po Fee
  608. (
  609. SOID,
  610. PO,
  611. ProductItemId,
  612. Amount,
  613. CurrentStatus,
  614. DeliveryReasonId,
  615. DeliveryNote,
  616. OldStatus,
  617. PriceCustomer,
  618. SAPReservationNumber,
  619. SAPReservationLineItem,
  620. SAPPONumberInterim,
  621. Serials
  622. )
  623. SELECT DISTINCT
  624. pfi.ClientSoId,
  625. pfi.ClientPoId,
  626. pfi.ProductItemId,
  627. 1,
  628. CASE WHEN st.PoStatus = 1 THEN 8 ELSE 128 END,
  629. 0,
  630. '',
  631. 64,
  632. pfi.Fee,
  633. NULL,
  634. NULL,
  635. NULL,
  636. ''
  637. FROM @PoFeeInfo pfi
  638. INNER JOIN @SodetailTable st ON pfi.ClientPoId = st.PoId
  639. WHERE st.SourceClientId = 1 AND st.PoStatus <> 5
  640. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  641.  
  642. EXEC dbo.APIClient_OMS_CreateLogUpdateSoDetails_V01 @CurrentUser = @userId, @SessionKey = @sessionkey, @TblInput = @TblOms
  643.  
  644. -- Huy PO
  645. DECLARE @TblOmsCancelPo as OMS_CancelPo
  646.  
  647. INSERT INTO @TblOmsCancelPo
  648. (
  649. ClientSoId,
  650. PoId,
  651. DeliveryReasonId,
  652. DeliveryNote,
  653. UserId,
  654. CreateRpo
  655. )
  656. SELECT DISTINCT
  657. st.ClientSoId,
  658. st.PoId,
  659. st.ReasonId,
  660. st.ReasonName,
  661. @userId,
  662. 0
  663. FROM @SodetailTable st
  664. WHERE st.SourceClientId = 1 AND st.PoStatus = 5
  665. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  666.  
  667. EXEC dbo.APIClient_OMS_CreateLogCancelPo_V01 @CurrentUser = 12345, @SessionKey = '12345', @TblInput = @TblOmsCancelPo
  668. /*---------------------------------------------------------------------------------------------------------*/
  669.  
  670. /* ------------------ Vinpro status sync ------------------------------------------------------------------*/
  671. INSERT INTO dbo.Vinpro_Sync_Status
  672. (
  673. --SyncId - this column value is auto-generated
  674. dbo.Vinpro_Sync_Status.OrderNo,
  675. dbo.Vinpro_Sync_Status.ItemNo,
  676. dbo.Vinpro_Sync_Status.Quantity,
  677. dbo.Vinpro_Sync_Status.Status,
  678. dbo.Vinpro_Sync_Status.TMSCode,
  679. dbo.Vinpro_Sync_Status.CreatedDate,
  680. dbo.Vinpro_Sync_Status.IsSynced,
  681. dbo.Vinpro_Sync_Status.RetryCount,
  682. dbo.Vinpro_Sync_Status.CreatedBy
  683. )
  684. SELECT DISTINCT
  685. st.ClientSoId,
  686. st.HybrisProductItemId,
  687. st.Amount,
  688. 0,
  689. st.SoDetailId,
  690. GETDATE(),
  691. 0,
  692. 0,
  693. @userId
  694. FROM @SodetailTable st
  695. WHERE st.SourceClientId = 3 AND st.SoDetailStatus = 16 AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = st.PoId AND pinu.ResultID = 1)
  696. /*---------------------------------------------------------------------------------------------------------*/
  697.  
  698. /* Nhập kho cho các sản phẩm hoàn thành */
  699. 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))
  700. BEGIN
  701. -- Tạo phiếu nhập kho
  702. DECLARE @warehouseSlipId bigint = 0
  703. DECLARE @warehouseSlipIdX bigint = 0
  704. DECLARE @stationWHId int = 0
  705. DECLARE @codId int = 0
  706. DECLARE @stationId int = 0
  707.  
  708. 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
  709. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
  710. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  711.  
  712. SELECT @stationWHId = COALESCE([StationWHId],0)
  713. FROM [Station_WHType]
  714. WHERE [SID]= @stationId
  715. AND [SWHTID] = 1
  716.  
  717. INSERT INTO [WH_WarehouseSlips]
  718. (
  719. [UserIdRecipient],
  720. [UserIdDelivery],
  721. [StationWHId],
  722. [ActionTypeId],
  723. [DestinationStationId],
  724. [IsCompleted],
  725. [DateCompleted],
  726. [CompletedBy]
  727. )
  728. SELECT @userId, @codId, @stationWHId, 1, null, 1, GETDATE() , @userId
  729.  
  730. SET @warehouseSlipId = @@IDENTITY
  731.  
  732. -- Xóa chi tiết phiếu nhập kho
  733. --DELETE FROM [dbo].[WH_WarehouseSlip_Details] WHERE [WarehouseSlipId] = @warehouseSlipId
  734.  
  735. -- Tạo chi tiết của phiếu nhập kho
  736. INSERT INTO [WH_WarehouseSlip_Details] ([WarehouseSlipId], [SoDetailId], [Quantity], [QuantityReceived])
  737. SELECT @warehouseSlipId, S.SoDetailId, S.Amount, S.Amount
  738. FROM @SodetailTable S
  739. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
  740. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  741.  
  742. --Ghi log
  743. INSERT INTO [WH_DeliverySlip_Logs] ([DeliverySlipsId], [WarehouseSlipId], [CreatedBy])
  744. SELECT ddsd.DeliverySlipsId, @warehouseSlipId, @userId
  745. FROM
  746. @SodetailTable S
  747. INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.SoDetailId = S.SoDetailId
  748. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
  749. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  750. GROUP BY DeliverySlipsId
  751.  
  752. --======================================================
  753. --Sy them 2015-06-03======================================================
  754. --======================================================
  755.  
  756. INSERT INTO dbo.WH_ImportSource
  757. (
  758. --WhImportSourceId - this column value is auto-generated
  759. dbo.WH_ImportSource.ImportSourceId,
  760. dbo.WH_ImportSource.WhSlipId,
  761. dbo.WH_ImportSource.DateCreated,
  762. dbo.WH_ImportSource.WhImportSourceTypeId
  763. )
  764. SELECT S.PuId, @warehouseSlipId, GETDATE(), 1
  765. FROM
  766. @SodetailTable S
  767. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
  768. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  769. GROUP BY S.PuId
  770.  
  771. INSERT INTO [WH_PO_Inbound_Map] ([PoId], [WarehouseSlipsId], [DateCreated])
  772. SELECT DISTINCT S.PoId, @warehouseSlipId, GETDATE()
  773. FROM
  774. @SodetailTable S
  775. WHERE S.PoStatus = 4 AND S.DeliveryFlowId = 2
  776. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID=1)
  777.  
  778. --======================================================
  779. --Sy them 2015-06-03======================================================
  780. --======================================================
  781.  
  782. END
  783.  
  784.  
  785. /*BEGIN ========== Cap nhat lai tien da tra cho cac PO huy - Chi cho hang giao thang ========================*/
  786. IF EXISTS(SELECT 1 FROM @SodetailTable S
  787. 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)
  788. )
  789. BEGIN
  790. DECLARE @RollBackSO TABLE
  791. (
  792. PackingId bigint,
  793. SoId bigint,
  794. TotalPaid decimal(18,4),
  795. TotalPaidVinId decimal(18,4),
  796. TotalMoney decimal(18,4),
  797. TotalMoneyComplete decimal(18,4),
  798. TotalFee decimal(18,4),
  799. DeliveryFee decimal(18,4),
  800. IsRollBackFee bit,
  801. TotalPaidRollBack decimal(18,4),
  802. TotalPaidVinIdRollBack decimal(18,4),
  803. TotalPoFeeRollBack decimal(18,4),
  804. TotalPoFee decimal(18,4),
  805. IsUsedVinId bit
  806. PRIMARY KEY (PackingId, SoId))
  807.  
  808. INSERT INTO @RollBackSO
  809. SELECT p.PackingID,
  810. s.SOId,
  811. p.TotalPaid,
  812. ISNULL(p.TotalVinIdMoney, 0),
  813. p.TotalMoney - (SELECT SUM(S.Amount * S.SellPrice)
  814. FROM dbo.Packing_Detail pd1
  815. INNER JOIN @SodetailTable S ON S.SoDetailId = pd1.SoDetailId
  816. WHERE pd1.PackingID = p.PackingID AND S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2
  817. AND EXISTS(SELECT 1 FROM @PoIdNotUpdte pinu WHERE pinu.PoId = S.PoId AND pinu.ResultID = 1)),
  818. 0,
  819. 0,
  820. p.DeliveryFee,
  821. CASE WHEN EXISTS(SELECT 1
  822. FROM dbo.Packing_Detail pd2
  823. INNER JOIN dbo.SO_Detail sd ON sd.SODetailID = pd2.SoDetailId
  824. INNER JOIN dbo.DE_DeliverySlipsDetails ddsd ON ddsd.PackingDetailId = pd2.PackingDetailId
  825. WHERE sd.SODetailStatusID < 16 AND sd.SODetailStatusID > 1 AND pd2.PackingID = p.PackingID AND ddsd.Visible = 1)
  826. THEN 0 ELSE 1 END,
  827. 0,
  828. 0,
  829. ISNULL((SELECT SUM(pdsf.Fee)
  830. FROM (SELECT DISTINCT S.PoId FROM @SodetailTable S WHERE S.PoStatus IN (1, 5) AND S.DeliveryFlowId = 2) st
  831. INNER JOIN dbo.Packing_DeliverySlips_Fee pdsf ON st.PoId = pdsf.ClientPoId
  832. WHERE pdsf.PackingID = p.PackingID
  833. GROUP BY pdsf.PackingId), 0),
  834. ISNULL(p.TotalPoFee, 0),
  835. ps.IsUsedVinId
  836. FROM (SELECT DISTINCT pst.PackingID, pst.IsUsedVinId FROM (SELECT DISTINCT MAX(pd.PackingID) AS PackingID, S.SODetailID, S.IsUsedVinId--po.PoId, SD.SOId
  837. FROM @SodetailTable S
  838. INNER JOIN dbo.Packing_Detail pd ON pd.SoDetailId = S.SODetailID
  839. 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)
  840. GROUP BY S.SODetailID, S.IsUsedVinId) pst) ps
  841. INNER JOIN dbo.Packing p WITH (NOLOCK) ON p.PackingID = ps.PackingID
  842. INNER JOIN dbo.SO s WITH (NOLOCK) ON s.ClientSoId = p.ClientSoId
  843.  
  844. UPDATE @RollBackSO
  845. SET TotalMoneyComplete = CASE WHEN TotalMoney > 0 THEN TotalMoney + DeliveryFee + TotalPoFee - TotalPoFeeRollBack ELSE 0 END,
  846. TotalFee = CASE WHEN TotalMoney > 0 THEN DeliveryFee + TotalPoFee - TotalPoFeeRollBack ELSE 0 END
  847.  
  848. UPDATE @RollBackSO
  849. SET TotalPaidRollBack = CASE WHEN IsUsedVinId = 0 AND TotalPaidVinId < TotalFee AND TotalPaid > TotalMoneyComplete - TotalPaidVinId THEN TotalPaid + TotalPaidVinId - TotalMoneyComplete
  850. WHEN IsUsedVinId = 0 AND TotalPaidVinId >= TotalFee AND TotalPaid > TotalMoneyComplete - TotalFee THEN TotalPaid + TotalFee - TotalMoneyComplete
  851. WHEN IsUsedVinId = 1 AND TotalPaidVinId >= TotalMoneyComplete THEN TotalPaid
  852. WHEN IsUsedVinId = 1 AND TotalPaidVinId < TotalMoneyComplete AND TotalPaid + TotalPaidVinId >= TotalMoneyComplete THEN TotalPaid + TotalPaidVinId - TotalMoneyComplete
  853. ELSE 0 END,
  854. TotalPaidVinIdRollBack = CASE WHEN IsUsedVinId = 0 AND TotalPaidVinId >= TotalFee THEN TotalPaidVinId - TotalFee
  855. WHEN IsUsedVinId = 1 AND TotalPaidVinId >= TotalMoneyComplete THEN TotalPaidVinId - TotalMoneyComplete
  856. ELSE 0 END
  857.  
  858. UPDATE dbo.SO
  859. SET dbo.SO.TotalPaidUsed = dbo.SO.TotalPaidUsed - pst.TotalPaidRollBack,
  860. dbo.SO.TotalPaidRemaining = dbo.SO.TotalPaidRemaining + pst.TotalPaidRollBack,
  861. dbo.SO.TotalPaidVinIdUsed = dbo.SO.TotalPaidVinIdUsed - pst.TotalPaidVinIdRollBack,
  862. dbo.SO.TotalPaidVinIdRemaining = dbo.SO.TotalPaidVinIdRemaining + pst.TotalPaidVinIdRollBack,
  863. dbo.SO.IsPaidFee = CASE WHEN pst.IsPaidFee > 0 THEN 0 ELSE dbo.SO.IsPaidFee END
  864. FROM (SELECT
  865. rb.SoId,
  866. SUM(rb.TotalPaidRollBack) AS TotalPaidRollBack,
  867. SUM(rb.TotalPaidVinIdRollBack) AS TotalPaidVinIdRollBack,
  868. SUM(CASE WHEN rb.IsRollBackFee = 1 AND rb.DeliveryFee > 0 THEN 1 ELSE 0 END) AS IsPaidFee
  869. FROM @RollBackSO rb
  870. WHERE (rb.TotalPaidRollBack + rb.TotalPaidVinIdRollBack > 0 OR rb.DeliveryFee > 0)
  871. OR (rb.TotalMoneyComplete = 0 AND rb.DeliveryFee > 0)
  872. GROUP BY rb.SoId) pst
  873. WHERE dbo.SO.SOID = pst.SoId
  874.  
  875. UPDATE dbo.Packing
  876. SET dbo.Packing.TotalPaid = dbo.Packing.TotalPaid - rbs.TotalPaidRollBack,
  877. dbo.Packing.TotalVinIdMoney = dbo.Packing.TotalVinIdMoney - rbs.TotalPaidVinIdRollBack,
  878. dbo.Packing.TotalMoney = rbs.TotalMoney,
  879. dbo.Packing.TotalPoFee = dbo.Packing.TotalPoFee - rbs.TotalPoFeeRollBack
  880. FROM @RollBackSO rbs
  881. WHERE dbo.Packing.PackingID = rbs.PackingId
  882.  
  883. UPDATE dbo.Packing_DeliverySlips_Fee
  884. SET
  885. dbo.Packing_DeliverySlips_Fee.Status = 2,
  886. dbo.Packing_DeliverySlips_Fee.UpdateBy = @userId, -- int
  887. dbo.Packing_DeliverySlips_Fee.UpdateDate = GETDATE()
  888. FROM dbo.Packing_DeliverySlips_Fee pdsf
  889. INNER JOIN @SodetailTable st ON st.PoId = pdsf.ClientPoId
  890. INNER JOIN @RollBackSO rso ON rso.PackingId = pdsf.PackingId
  891. WHERE st.PoStatus IN (1, 5) AND st.DeliveryFlowId = 2
  892. ---------------------------------
  893. /*-- 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 */
  894. UPDATE
  895. DE_DeliverySlips
  896. SET
  897. [StatusId] = dt.StatusId,
  898. [DateModified] = GETDATE(),
  899. [ModifiedBy] = @userId,
  900. [IsConfirmed] = 1
  901. FROM @DeliveryTable dt
  902. WHERE dbo.DE_DeliverySlips.DeliverySlipsId = dt.DeliverySlipsId
  903.  
  904. DECLARE @LogInsertCash TABLE (CashId int, DeliverySlipsId int)
  905.  
  906. INSERT INTO CA_CashDelivery([UserIdDelivery],[Visible],[IsCompleted],[DateCompleted],[CreatedBy],[TotalMoney], [DeliverySlipsId])
  907. OUTPUT INSERTED.CashId, INSERTED.DeliverySlipsId INTO @LogInsertCash(CashId, DeliverySlipsId)
  908. SELECT dds.CodId, 1, 0, GETDATE(), @userId, 0, dds.DeliverySlipsId
  909. FROM @DeliveryTableCash dtc INNER JOIN dbo.DE_DeliverySlips dds ON dds.DeliverySlipsId = dtc.DeliverySlipsId
  910.  
  911. --INSERT CA_CashDeliveryDetails------------------------------------------------------------------------------------------------------->
  912. INSERT INTO CA_CashDeliveryDetails([CashId],[SoDetailId],[Quantity])
  913. SELECT
  914. lic.CashId, sd.SoDetailId , ddsd.QuantityCompleted
  915. FROM @LogInsertCash lic
  916. INNER JOIN DE_DeliverySlipsDetails ddsd ON ddsd.DeliverySlipsId = lic.DeliverySlipsId
  917. INNER JOIN dbo.SO_Detail sd ON (sd.ParentSoDetailId = ddsd.SoDetailId or sd.SoDetailId = ddsd.SoDetailId)
  918. WHERE
  919. sd.SODetailStatusID = 13 AND
  920. sd.Visible = 1 AND
  921. ddsd.IsCompleted = 1
  922.  
  923. --INSERT CA_CashDeliverySlip_Logs------------------------------------------------------------------------------------------------------->
  924. INSERT INTO CA_CashDeliverySlip_Logs([CashId],[DeliverySlipsId],[CreatedBy])
  925. SELECT lic.CashId, lic.DeliverySlipsId, @userId
  926. FROM @LogInsertCash lic
  927.  
  928. END
  929. /*END ============ Cap nhat lai tien da tra cho cac PO huy ========================*/
  930.  
  931. ---------- Update SMS logtoPic ----------------------------
  932. DECLARE @SoDetailFull TABLE (
  933. ClientSoId bigint,
  934. SoDetailId bigint,
  935. StatusId int,
  936. SID_Current int,
  937. SidPic int
  938. )
  939.  
  940. DECLARE @SoFull TABLE (
  941. ClientSoId bigint,
  942. SidPic int
  943. )
  944.  
  945. INSERT INTO @SoDetailFull
  946. SELECT
  947. sd.ClientSoId,
  948. sd.SODetailID,
  949. sd.SODetailStatusID,
  950. sd.SID_CurrentStation,
  951. d.SID
  952. FROM dbo.SO_Detail sd WITH (NOLOCK)
  953. INNER JOIN (SELECT DISTINCT s.ClientSoId, s2.SID
  954. FROM @SodetailTable st
  955. INNER JOIN dbo.SO s WITH (NOLOCK) ON s.ClientSoId = st.ClientSoId
  956. 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
  957. 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))
  958. AS d ON d.ClientSoId = sd.ClientSoId
  959.  
  960. INSERT INTO @SoFull
  961. SELECT DISTINCT sdf.ClientSoId, sdf.SidPic
  962. FROM @SoDetailFull sdf
  963. WHERE sdf.SID_Current = sdf.SidPic
  964. AND sdf.StatusId = 7
  965. AND NOT EXISTS (
  966. SELECT 1 FROM @SoDetailFull sdf2
  967. WHERE sdf2.ClientSoId = sdf.ClientSoId
  968. AND (sdf2.StatusId NOT IN (7,13,14,15,16) OR sdf2.SID_Current <> sdf.SidPic))
  969.  
  970. IF EXISTS(SELECT 1 FROM @SoFull sf)
  971. BEGIN
  972. -- INSERT TO CS SMS
  973. BEGIN TRY
  974. INSERT INTO dbo.Log_PickupPointToCs
  975. (
  976. dbo.Log_PickupPointToCs.SoId,
  977. dbo.Log_PickupPointToCs.ClientSoId,
  978. dbo.Log_PickupPointToCs.CustomerName,
  979. dbo.Log_PickupPointToCs.CustomerId,
  980. dbo.Log_PickupPointToCs.StreetName,
  981. dbo.Log_PickupPointToCs.StreetId,
  982. dbo.Log_PickupPointToCs.DistrictName,
  983. dbo.Log_PickupPointToCs.DistrictId,
  984. dbo.Log_PickupPointToCs.CityName,
  985. dbo.Log_PickupPointToCs.CityId,
  986. dbo.Log_PickupPointToCs.WardId,
  987. dbo.Log_PickupPointToCs.WardName,
  988. dbo.Log_PickupPointToCs.PromiseDate,
  989. dbo.Log_PickupPointToCs.UserId,
  990. dbo.Log_PickupPointToCs.UserName,
  991. dbo.Log_PickupPointToCs.CreateDate,
  992. dbo.Log_PickupPointToCs.IsComplete,
  993. dbo.Log_PickupPointToCs.NumCount
  994. )
  995. SELECT DISTINCT
  996. s.SOID,
  997. s.ClientSoId,
  998. s.CustomerName,
  999. s.CustomerId,
  1000. [as].StreetName,
  1001. s.DeliveryStreetId,
  1002. ad.DistrictName,
  1003. s.DeliveryDistId,
  1004. ac.CityName,
  1005. s.DeliveryCityId,
  1006. s.DeliveryWardId,
  1007. aw.WardName,
  1008. GETDATE(),
  1009. @userId,
  1010. (SELECT uu.FullName FROM dbo.UM_Users uu WITH (NOLOCK) WHERE uu.UserId = @userId),
  1011. GETDATE(),
  1012. 0,
  1013. 0
  1014. FROM dbo.SO s WITH (NOLOCK)
  1015. INNER JOIN @SoFull sf ON sf.ClientSoId = s.ClientSoId
  1016. LEFT JOIN dbo.AD_Street [as] WITH (NOLOCK) ON [as].StreetId = s.DeliveryStreetId
  1017. LEFT JOIN dbo.AD_District ad WITH (NOLOCK) ON ad.DistrictId = s.DeliveryDistId
  1018. LEFT JOIN dbo.AD_City ac WITH (NOLOCK) ON ac.CityId = s.DeliveryCityId
  1019. LEFT JOIN dbo.AD_Ward aw WITH (NOLOCK) ON aw.WardId = s.DeliveryWardId
  1020.  
  1021. END TRY
  1022. BEGIN CATCH /**/ END CATCH
  1023. END
  1024. -----------------------------------------------------------
  1025.  
  1026. COMMIT TRAN T
  1027.  
  1028. --Cap nhat gia tri cho ket qua
  1029. UPDATE @PoIdNotUpdte
  1030. SET
  1031. ResultID = 2,
  1032. ResultMessage = (
  1033. 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!'
  1034. ELSE N'Cập nhật thành công!'
  1035. END
  1036. )
  1037.  
  1038. WHERE EXISTS(SELECT 1 FROM dbo.PurchaseOrder po WHERE po.PoId = [@PoIdNotUpdte].PoId AND po.POStatusId <>2)
  1039. AND [@PoIdNotUpdte].ResultID =1
  1040.  
  1041. END TRY
  1042. BEGIN CATCH
  1043. ROLLBACK TRAN T
  1044. UPDATE @PoIdNotUpdte
  1045. SET
  1046. ResultID = -2,
  1047. ResultMessage = ERROR_MESSAGE()
  1048. --SET @Msg = ERROR_MESSAGE();
  1049. END CATCH
  1050.  
  1051. SELECT *
  1052. FROM @PoIdNotUpdte pinu
  1053.  
  1054. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement