· 8 years ago · Nov 20, 2017, 09:10 AM
1USE [Adayroi_TMS]
2GO
3/****** Object: StoredProcedure [dbo].[APIClient_PODetail_GetListPoDetailToCollect_V01] Script Date: 11/20/2017 3:50:36 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Entity Name: APIClient_PODetail_GetListPoDetailToCollect_V01
11-- Author: SyHT
12-- Create date: 28/12/2015 09:44:33
13-- Description: Lấy toà n bộ PO chi tiết, viet lai store
14-- =============================================
15ALTER PROCEDURE [dbo].[APIClient_PODetail_GetListPoDetailToCollect_V01] -- [APIClient_PODetail_GetListPoDetailToCollect_V01] '4966381433131257105'
16(
17 @ListPoId NVARCHAR(MAX),
18 @puId INT,
19 @CurrentUser INT,
20 @SesstionKey VARCHAR(200)
21)
22AS
23BEGIN
24DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
25 BEGIN TRY
26 EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
27
28 IF @puId = 0
29 BEGIN
30 ;WITH PoCbmKg AS
31 (
32 SELECT
33 pod.PoId,
34 SUM(pod.Amount * cp.CBM) AS PoCbm,
35 SUM(pod.Amount * cp.Weight) AS PoWeight
36 FROM dbo.SplitListToBIGINT(@ListPoId, ',') lpo
37 INNER JOIN dbo.PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.PoId = lpo.[Value]
38 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = pod.ClientProductId
39 GROUP BY pod.PoId
40 )
41 SELECT
42 pod.PODetailID
43 , pod.LogisticSKULabel
44 , cp.ClientSKU
45 , cp.ProductName
46 , pod.Amount
47 , pod.SellPrice
48 , po.PoId
49 , pod.PODetailStatusID
50 , CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
51 , pod.Priority
52 , pod.TotalSKU
53 , cp.ProductUnit
54 , CASE WHEN cmw.HybrisMerchantWarehouseId IS NOT NULL AND cmw.SourceClientId = 3 THEN CONCAT(cmw.HybrisMerchantWarehouseId, ' - ') ELSE N'' END + CASE WHEN pommw.ReverseCode IS NOT NULL THEN s3.FullAddress ELSE cmw.FullAddress END AS FullAddress
55 , cm.MerchantName
56 , CASE WHEN pommw.ReverseCode IS NOT NULL THEN s3.Phone ELSE cmw.PhoneN0 END AS PhoneN0
57 , CAST(cm.MerchantId AS varchar) AS MerchantCode
58 , CONCAT(cm.MerchantName, ' - ', cmw.Name, ' - ', cm.MerchantId, N' - ÄC: ', cmw.FullAddress) AS PoDetailGroup
59 , '' AS PoDetailStatusName
60 , CONCAT('2', FORMAT(cm.SourceClientId, '00'), po.PoId) AS PoGroup
61 , po.UID_COD
62 , pod.SODetailID
63 , s.ClientSoId AS ClientSoIdBigInt
64 , CASE WHEN s.SourceClientId = 2 THEN cp.HybrisProductItemId ELSE CAST(cp.ProductItemId AS nvarchar) END AS ProductItemId
65 , po.ProductTypeId
66 --, CONCAT('2', FORMAT(cm.SourceClientId, '00'), po.PoId) AS PoLabel
67 , CONCAT(N'- TGN Ä‘Ãch: ', s2.SName) AS PoLabel
68 , po.RouteId
69 , pck.PoCbm
70 , pck.PoWeight
71 , r.RouteName
72 , cp.MerchantProductName
73 , cm.TradingAddress AS MerchantAddress
74 , cm.PhoneN0 AS MerchantPhone
75 , pod.Note
76 , cp.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
77 , sd.SAPReservationNumber
78 , sd.SAPPONumberInterim
79 , sd.SAPReservationLineItem
80 , 0 AS IsSelect
81 , '' AS SAPInternalMerchantSONumber
82 , pommw.ReverseCode
83 , s.SourceClientId
84 , cmw.ClientMerchantWarehouseTypeId
85 FROM
86 PoCbmKg pck
87 INNER JOIN PurchaseOrder po WITH (NOLOCK) ON pck.PoId = po.PoId
88 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.PoId = PO.PoId
89 INNER JOIN PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND pod.PoId = po.PoId
90 --INNER JOIN dbo.PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.PoId = po.PoId
91 INNER JOIN dbo.SO s WITH (NOLOCK) ON s.SOID = po.SoId
92 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = pod.ClientProductId
93 INNER JOIN dbo.ClientMerchantWarehouse cmw WITH (NOLOCK) ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
94 INNER JOIN dbo.ClientMerchant cm WITH (NOLOCK) ON cm.ClientMerchantId = sd.ClientMerchantId
95 LEFT JOIN dbo.Routs r WITH (NOLOCK) ON r.RoutId = po.RouteId
96 INNER JOIN dbo.Stations s2 WITH (NOLOCK) ON po.SID_Destination = s2.SID
97 LEFT JOIN dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = po.ClientPoId AND pommw.IsComplete = 1
98 INNER JOIN dbo.Stations s3 WITH (NOLOCK) ON po.SID_Tally = s3.SID
99 WHERE sd.ParentSoDetailId IS NULL
100
101 END
102 ELSE
103 BEGIN
104 ;WITH PoCbmKg AS
105 (
106 SELECT
107 pod.PoId,
108 SUM(pod.Amount * cp.CBM) AS PoCbm,
109 SUM(pod.Amount * cp.Weight) AS PoWeight
110 FROM dbo.SplitListToBIGINT(@ListPoId, ',') lpo
111 INNER JOIN dbo.PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.PoId = lpo.[Value]
112 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = pod.ClientProductId
113 GROUP BY pod.PoId
114 )
115 SELECT
116 pod.PODetailID
117 , pod.LogisticSKULabel
118 , cp.ClientSKU
119 , cp.ProductName
120 , pod.Amount
121 , pod.SellPrice
122 , po.PoId
123 , pod.PODetailStatusID
124 , CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
125 , pod.Priority
126 , pod.TotalSKU
127 , cp.ProductUnit
128 , CASE WHEN cmw.HybrisMerchantWarehouseId IS NOT NULL AND cmw.SourceClientId = 3 THEN CONCAT(cmw.HybrisMerchantWarehouseId, ' - ') ELSE N'' END + CASE WHEN pommw.ReverseCode IS NOT NULL THEN s3.FullAddress ELSE cmw.FullAddress END AS FullAddress
129 , cm.MerchantName
130 , CASE WHEN pommw.ReverseCode IS NOT NULL THEN s3.Phone ELSE cmw.PhoneN0 END AS PhoneN0
131 , CAST(cm.MerchantId AS varchar) AS MerchantCode
132 , CONCAT(cm.MerchantName, ' - ', cmw.Name, ' - ', cm.MerchantId, N' - ÄC: ', cmw.FullAddress) AS PoDetailGroup
133 , '' AS PoDetailStatusName
134 , CONCAT('2', FORMAT(cm.SourceClientId, '00'), po.PoId) AS PoGroup
135 , po.UID_COD
136 , pod.SODetailID
137 , s.ClientSoId AS ClientSoIdBigInt
138 , CASE WHEN s.SourceClientId = 2 THEN cp.HybrisProductItemId ELSE CAST(cp.ProductItemId AS nvarchar) END AS ProductItemId
139 , po.ProductTypeId
140 --, CONCAT('2', FORMAT(cm.SourceClientId, '00'), po.PoId) AS PoLabel
141 , CONCAT(N'- TGN Ä‘Ãch: ', s2.SName) AS PoLabel
142 , po.RouteId
143 , pck.PoCbm
144 , pck.PoWeight
145 , r.RouteName
146 , cp.MerchantProductName
147 , cm.TradingAddress AS MerchantAddress
148 , cm.PhoneN0 AS MerchantPhone
149 , CASE WHEN pud.IsAgain = 1 THEN N'Hẹn lại' ELSE pod.Note END AS Note
150 , cp.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
151 , sd.SAPReservationNumber
152 , sd.SAPPONumberInterim
153 , sd.SAPReservationLineItem
154 , 0 AS IsSelect
155 , '' AS SAPInternalMerchantSONumber
156 , pommw.ReverseCode
157 , s.SourceClientId
158 , cmw.ClientMerchantWarehouseTypeId
159 FROM
160 PoCbmKg pck
161 INNER JOIN PurchaseOrder po WITH (NOLOCK) ON pck.PoId = po.PoId
162 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.PoId = PO.PoId
163 INNER JOIN PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND pod.PoId = po.PoId
164 --INNER JOIN dbo.PurchaseOrder_Detail pod WITH (NOLOCK) ON pod.PoId = po.PoId
165 INNER JOIN dbo.SO s WITH (NOLOCK) ON s.SOID = po.SoId
166 INNER JOIN dbo.PickUpDetail pud WITH (NOLOCK) ON pud.PoId = po.PoId
167 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = pod.ClientProductId
168 INNER JOIN dbo.ClientMerchantWarehouse cmw WITH (NOLOCK) ON cmw.ClientMerchantWarehouseId = po.ClientMerchantWarehouseId
169 INNER JOIN dbo.ClientMerchant cm WITH (NOLOCK) ON cm.ClientMerchantId = sd.ClientMerchantId
170 LEFT JOIN dbo.Routs r WITH (NOLOCK) ON r.RoutId = po.RouteId
171 INNER JOIN dbo.Stations s2 WITH (NOLOCK) ON po.SID_Destination = s2.SID
172 LEFT JOIN dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = po.ClientPoId --AND pommw.IsComplete = 1
173 INNER JOIN dbo.Stations s3 WITH (NOLOCK) ON po.SID_Tally = s3.SID
174 WHERE pud.PuId = @puId AND sd.ParentSoDetailId IS NULL
175 END
176 END TRY
177 BEGIN CATCH
178 THROW;
179 END CATCH
180END
181
182
183----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
184-- =============================================
185-- Entity Name: [APIClient_PODetail_GetListPoDetailByDeliveryId_V01]
186-- Author: Hà Trá»ng Sỹ
187-- Create date: 03/01/2014 09:44:33
188-- Description: Lấy toà n bộ PO chi tiết cho 1 Delivery
189-- =============================================
190ALTER PROCEDURE [dbo].[APIClient_PODetail_GetListPoDetailByDeliveryId_V01]
191(
192 @DeliverySlipsId int,
193 @CurrentUser INT,
194 @SesstionKey VARCHAR(200)
195)
196AS
197BEGIN
198DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
199 BEGIN TRY
200 EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
201 SELECT PODetailID
202 ,sd.LogisticSKULabel
203 ,ClientSKU
204 ,ProductName
205 ,sd.[Amount]
206 ,sd.SellPrice * sd.[Amount] AS UnitBuyPrice
207 ,PO.PoId
208 ,[PODetailStatusID]
209 ,CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
210 ,PO.[Priority]
211 ,PO.SKUAmount AS [TotalSKU]
212 ,CP.ProductUnit
213 ,CASE WHEN MW.HybrisMerchantWarehouseId IS NOT NULL AND MW.SourceClientId = 3 THEN CONCAT(MW.HybrisMerchantWarehouseId, ' - ') ELSE N'' END + CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.FullAddress ELSE MW.FullAddress END AS FullAddress
214 , M.MerchantName
215 , CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.Phone ELSE MW.PhoneN0 END AS PhoneN0
216 ,CAST(M.MerchantId AS NVARCHAR) AS MerchantCode
217 ,CASE WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.SID IS NOT NULL THEN s2.SName
218 WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.SID IS NULL THEN N'Không tìm thấy địa chỉ PIC'
219 ELSE ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.ClientMerchantWarehouseId, '000000') + ' - ' + MW.FullAddress
220 END AS NccGroup
221 ,UID_COD
222 ,sd.SODetailID
223 ,SCM.ClientSoId
224 ,CASE WHEN SCM.SourceClientId = 2 THEN CP.HybrisProductItemId ELSE CAST(CP.ProductItemId AS nvarchar) END AS ProductItemId
225 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(PO.PoId AS NVARCHAR) AS PoLabel
226 ,SCM.SourceClientId
227 ,SCM.TotalPo
228 ,PO.RouteId
229 ,RD.RouteName
230 ,ST.SName
231 ,SCM.DeliveryFullAddress
232 ,SCM.CustomerName
233 ,SCM.CustomerPhone
234 ,r.RoutId AS RouteDest
235 ,POD.Priority AS PodPriority
236 ,CP.MerchantProductName
237 ,'1' + FORMAT(SCM.SourceClientId,'00') + CAST(SCM.ClientSoId AS NVARCHAR) AS ClientSoIdView
238 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(PO.PoId AS NVARCHAR) AS PoIdView
239 ,CASE WHEN pud.IsAgain = 1 THEN N'Hẹn lại' ELSE sd.Note + SCM.Note END AS Note
240 ,PO.DeliveryDateTo AS SoCreateDateTime
241 ,r.RouteName AS SoRouteName
242 ,CASE WHEN ISNULL(p.OldTotalPaid, 0) + ISNULL(p.OldTotalVinIdMoney, 0) > 0 AND ISNULL(p.TotalMoney, 0) + ISNULL(p.OldDeliveryFee, 0) + ISNULL(p.OldTotalPoFee, 0) > ISNULL(p.OldTotalPaid, 0) + ISNULL(p.OldTotalVinIdMoney, 0)THEN N'COD + Online'
243 WHEN ISNULL(p.OldTotalPaid, 0) + ISNULL(p.OldTotalVinIdMoney, 0) > 0 AND ISNULL(p.TotalMoney, 0) + ISNULL(p.OldDeliveryFee, 0) + ISNULL(p.OldTotalPoFee, 0) <= ISNULL(p.OldTotalPaid, 0) + ISNULL(p.OldTotalVinIdMoney, 0) THEN 'Online'
244 ELSE 'COD' END AS CashType
245 ,DATEDIFF(MINUTE, GETDATE(), CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN po.TempDeliveryDateFrom ELSE po.DeliveryDateFrom END) AS RemainTime
246 ,sd.SODetailStatusID
247 ,M.TradingAddress AS MerchantAddress
248 ,M.PhoneN0 AS MerchantPhone
249 ,CASE WHEN pud.PuId IS NOT NULL THEN CONCAT(pud.PuId, '_', FORMAT(pud.IndexInPu, '00')) ELSE '' END AS PuId
250 ,CP.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
251 ,sd.SAPReservationNumber
252 ,sd.SAPPONumberInterim
253 ,sd.SAPReservationLineItem
254 ,0 AS IsSelect
255 ,CASE WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 THEN 2 ELSE SCM.OrderType END AS OrderType
256 ,pommw.ReverseCode
257 ,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) < 2 THEN Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByPolicy
258 ,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN Format(po.TempDeliveryDateFrom, 'HH:mm') + ' - ' + Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByCustomer
259 ,Format(po.MerchantPickupDateFrom, 'dd/MM/yyyy') AS DatePickupAgain -- Ngay hen lay
260 ,Format(PO.DeliveryDateTo, 'dd/MM/yyyy') AS DateDelivery
261 ,sd.ClientMerchantId
262 ,pt.ProductTypeName
263 ,sc.Name AS SourceClientName
264 ,PO.ClientMerchantWarehouseId
265 FROM
266 dbo.DE_DeliverySlips dds WITH ( NOLOCK )
267 INNER JOIN dbo.DE_DeliverySlipsDetails ddsd WITH ( NOLOCK ) ON dds.DeliverySlipsId = ddsd.DeliverySlipsId
268 INNER JOIN dbo.SO_Detail sd WITH ( NOLOCK ) ON sd.SODetailID = ddsd.SoDetailId
269 LEFT JOIN dbo.PickUpDetail pud WITH ( NOLOCK ) ON pud.PuId = dds.PuId AND pud.PoId = sd.PoId
270 INNER JOIN PurchaseOrder_Detail POD WITH ( NOLOCK ) ON POD.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND POD.PoId = sd.PoId
271 INNER JOIN PurchaseOrder PO WITH ( NOLOCK ) ON POD.PoId = PO.PoId
272 INNER JOIN ClientProduct CP WITH ( NOLOCK ) ON CP.ClientProductId = sd.ClientProductId
273 INNER JOIN ClientMerchantWarehouse MW WITH ( NOLOCK ) ON MW.ClientMerchantWarehouseId = PO.ClientMerchantWarehouseId
274 INNER JOIN ClientMerchant M WITH ( NOLOCK ) ON M.ClientMerchantId = sd.ClientMerchantId
275 INNER JOIN dbo.SO SCM WITH ( NOLOCK ) ON SCM.SOID = PO.SoId
276 LEFT JOIN dbo.Packing_Detail pd WITH ( NOLOCK ) ON pd.PackingDetailId = ddsd.PackingDetailId
277 LEFT JOIN dbo.Packing p WITH ( NOLOCK ) ON p.PackingID = pd.PackingID
278 LEFT JOIN dbo.Routs RD WITH ( NOLOCK ) ON RD.RoutId = PO.RouteId
279 LEFT JOIN dbo.Routs r WITH ( NOLOCK ) ON PO.DeliveryRoutsId = r.RoutId
280 LEFT JOIN dbo.Stations ST WITH ( NOLOCK ) ON ST.SID = PO.SID_Tally
281 LEFT JOIN dbo.Stations s2 WITH (NOLOCK) ON SCM.DeliveryCityId = s2.CityID AND SCM.DeliveryDistId = s2.DistID AND SCM.DeliveryWardId = s2.WardID AND SCM.DeliveryStreetId = s2.StreetID AND SCM.DeliveryHouseN0 = s2.HouseN0 AND ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.Visible = 1
282 LEFT JOIN dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = PO.ClientPoId --AND pommw.IsComplete = 1
283 INNER JOIN dbo.ProductType pt WITH (NOLOCK) ON pt.ProductTypeID = po.ProductTypeId
284 INNER JOIN dbo.SourceClient sc WITH (NOLOCK) ON sc.SourceClientId = SCM.SourceClientId
285 WHERE
286 dds.DeliverySlipsId = @DeliverySlipsId
287
288 END TRY
289 BEGIN CATCH
290 THROW;
291 END CATCH
292END
293
294----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
295-- =============================================
296-- Entity Name: [APIClient_PODetail_GetListPoDetailByPoIds_V01]
297-- Author: Hà Trá»ng Sỹ
298-- Create date: 03/01/2014 09:44:33
299-- Description: Lấy toà n bộ PO chi tiết
300-- =============================================
301ALTER PROCEDURE [dbo].[APIClient_PODetail_GetListPoDetailByPoIds_V01]
302(
303 @ListPoId NVARCHAR(MAX),
304 @CurrentStatus INT,
305 @CurrentUser INT,
306 @SesstionKey VARCHAR(200)
307)
308AS
309BEGIN
310DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
311 BEGIN TRY
312 EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
313
314 DECLARE @PoSelected TABLE (PoId bigint PRIMARY KEY (PoId))
315 INSERT INTO @PoSelected
316 SELECT sltb.[Value] FROM dbo.SplitListToBIGINT(@ListPoId, ',') sltb
317
318 SELECT PODetailID
319 ,sd.LogisticSKULabel
320 ,ClientSKU
321 ,ProductName
322 ,sd.[Amount]
323 ,sd.SellPrice * sd.[Amount] AS UnitBuyPrice
324 ,sd.PoId
325 ,[PODetailStatusID]
326 ,CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
327 ,PO.[Priority]
328 ,POD.[TotalSKU]
329 ,CP.ProductUnit
330 ,CASE WHEN MW.HybrisMerchantWarehouseId IS NOT NULL AND MW.SourceClientId = 3 THEN CONCAT(MW.HybrisMerchantWarehouseId, ' - ') ELSE N'' END + CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.FullAddress ELSE MW.FullAddress END AS FullAddress
331 , M.MerchantName
332 , CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.Phone ELSE MW.PhoneN0 END AS PhoneN0
333 ,CAST(M.MerchantId AS NVARCHAR) AS MerchantCode
334 ,CASE WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.SID IS NOT NULL THEN s2.SName
335 WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.SID IS NULL THEN N'Không tìm thấy địa chỉ PIC'
336 ELSE ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.ClientMerchantWarehouseId, '000000') + ' - ' + MW.FullAddress END AS NccGroup
337 ,UID_COD
338 ,sd.SODetailID
339 ,SCM.ClientSoId
340 ,CASE WHEN SCM.SourceClientId = 2 THEN CP.HybrisProductItemId ELSE CAST([ProductItemId] AS nvarchar) END AS ProductItemId
341 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoLabel
342 ,SCM.SourceClientId
343 ,SCM.TotalPo
344 ,PO.RouteId
345 ,RD.RouteName
346 ,ST.SName
347 ,SCM.DeliveryFullAddress
348 ,SCM.CustomerName
349 ,SCM.CustomerPhone
350 ,r.RoutId AS RouteDest
351 ,POD.Priority AS PodPriority
352 ,CP.MerchantProductName
353 ,'1' + FORMAT(SCM.SourceClientId,'00') + CAST(SCM.ClientSoId AS NVARCHAR) AS ClientSoIdView
354 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoIdView
355 ,sd.Note + SCM.Note AS Note
356 ,PO.DeliveryDateTo AS SoCreateDateTime
357 ,r.RouteName AS SoRouteName
358 ,CASE WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) = 0 THEN 'COD'
359 WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) > 0
360 AND SCM.RemainingAmount > 0 THEN N'COD + Online'
361 ELSE 'Online' END AS CashType
362 ,DATEDIFF(MINUTE, GETDATE(), CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN po.TempDeliveryDateFrom ELSE po.DeliveryDateFrom END) AS RemainTime
363 ,sd.SODetailStatusID
364 ,M.TradingAddress AS MerchantAddress
365 ,M.PhoneN0 AS MerchantPhone
366 ,CASE WHEN sd.SODetailStatusID = 1 THEN ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.ClientMerchantWarehouseId, '000000') + ' - ' + MW.FullAddress ELSE '' END AS PuId
367 ,CP.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
368 ,sd.SAPReservationNumber
369 ,sd.SAPPONumberInterim
370 ,sd.SAPReservationLineItem
371 ,0 AS IsSelect
372 ,CASE WHEN ISNULL(SCM.IsDeliveryPiC, 0) = 1 THEN 2 ELSE SCM.OrderType END AS OrderType
373 ,pommw.ReverseCode
374 ,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) < 2 THEN Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByPolicy
375 ,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN Format(po.TempDeliveryDateFrom, 'HH:mm') + ' - ' + Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByCustomer
376 ,pt.ProductTypeName
377 FROM @PoSelected ps
378 INNER JOIN PurchaseOrder PO WITH (NOLOCK) ON ps.PoId = PO.PoId
379 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.PoId = PO.PoId
380 INNER JOIN PurchaseOrder_Detail POD WITH (NOLOCK) ON POD.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND POD.PoId = PO.PoId
381 INNER JOIN ClientProduct CP WITH (NOLOCK) ON CP.ClientProductId = POD.ClientProductId
382 INNER JOIN ClientMerchantWarehouse MW WITH (NOLOCK) ON MW.ClientMerchantWarehouseId = PO.ClientMerchantWarehouseId
383 INNER JOIN ClientMerchant M WITH (NOLOCK) ON M.ClientMerchantId = sd.ClientMerchantId
384 INNER JOIN dbo.SO SCM WITH (NOLOCK) ON SCM.SOID = PO.SoId
385 LEFT JOIN dbo.Routs RD WITH (NOLOCK) ON RD.RoutId = PO.RouteId
386 LEFT JOIN dbo.Routs r WITH (NOLOCK) ON PO.DeliveryRoutsId = r.RoutId
387 LEFT JOIN dbo.Stations ST WITH (NOLOCK) ON ST.SID = PO.SID_Tally
388 LEFT JOIN dbo.Stations s2 WITH (NOLOCK) ON SCM.DeliveryCityId = s2.CityID AND SCM.DeliveryDistId = s2.DistID AND SCM.DeliveryWardId = s2.WardID AND SCM.DeliveryStreetId = s2.StreetID AND SCM.DeliveryHouseN0 = s2.HouseN0 AND ISNULL(SCM.IsDeliveryPiC, 0) = 1 AND s2.Visible = 1
389 LEFT JOIN dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = PO.ClientPoId --AND pommw.IsComplete = 1
390 INNER JOIN dbo.ProductType pt WITH (NOLOCK) ON pt.ProductTypeID = po.ProductTypeId
391 WHERE
392 (sd.SODetailStatusID = 1 OR sd.SODetailStatusID = 4)
393 AND sd.Visible = 1
394 AND POD.Visible = 1
395
396 END TRY
397 BEGIN CATCH
398 THROW;
399 END CATCH
400END
401
402----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
403-- =============================================
404-- Entity Name: APIClient_PO_GetListSettupPoDetailToDelivery_V01
405-- Author: Hà Trá»ng Sỹ
406-- Create date: 24/05/2017 09:44:33
407-- Description: Lấy toà n bộ PO chi tiết
408-- =============================================
409ALTER PROCEDURE [dbo].[APIClient_PO_GetListSettupPoDetailToDelivery_V01]
410(
411 @CurrentUser INT,
412 @SesstionKey VARCHAR(200),
413 @StationId INT,
414 @SourceClientId INT
415)
416AS
417BEGIN
418DECLARE @PROC_NAME VARCHAR(250) = OBJECT_NAME(@@PROCID)
419 BEGIN TRY
420 EXEC [dbo].[CHECK_SESSION_AND_PERMISSION] @CurrentUser, @SesstionKey, @PROC_NAME
421
422 DECLARE @VinmartId TABLE (VinmartId int)
423 DECLARE @VinmartConfig varchar(max) = ''
424 SELECT @VinmartConfig = sc.[Value] FROM dbo.SystemConfigs sc WHERE sc.Name = 'VinmartMerchantId'
425 INSERT INTO @VinmartId SELECT slti.[Value] FROM dbo.SplitListToINT(@VinmartConfig, ',') slti
426
427 SELECT PODetailID
428 ,sd.LogisticSKULabel
429 ,ClientSKU
430 ,ProductName
431 ,sd.[Amount]
432 ,sd.SellPrice * sd.[Amount] AS UnitBuyPrice
433 ,sd.PoId
434 ,[PODetailStatusID]
435 ,CASE WHEN cp.MerchantSku IS NULL THEN cp.ProductItemIdSap ELSE cp.MerchantSku END AS MerchantSku
436 ,PO.[Priority]
437 ,POD.[TotalSKU]
438 ,CP.ProductUnit
439 ,CASE WHEN MW.HybrisMerchantWarehouseId IS NOT NULL AND MW.SourceClientId = 3 THEN CONCAT(MW.HybrisMerchantWarehouseId, ' - ') ELSE N'' END + CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.FullAddress ELSE MW.FullAddress END AS FullAddress
440 , M.MerchantName
441 , CASE WHEN pommw.ReverseCode IS NOT NULL THEN ST.Phone ELSE MW.PhoneN0 END AS PhoneN0
442 ,CAST(M.MerchantId AS NVARCHAR) AS MerchantCode
443 ,ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.ClientMerchantWarehouseId, '000000') + ' - ' + MW.FullAddress AS NccGroup
444 ,po.UID_COD
445 ,sd.SODetailID
446 ,SCM.ClientSoId
447 ,CASE WHEN SCM.SourceClientId = 2 THEN CP.HybrisProductItemId ELSE CAST([ProductItemId] AS nvarchar) END AS ProductItemId
448 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoLabel
449 ,SCM.SourceClientId
450 ,SCM.TotalPo
451 ,PO.RouteId
452 ,RD.RouteName
453 ,ST.SName
454 ,SCM.DeliveryFullAddress
455 ,SCM.CustomerName
456 ,SCM.CustomerPhone
457 ,r.RoutId AS RouteDest
458 ,POD.Priority AS PodPriority
459 ,CP.MerchantProductName
460 ,'1' + FORMAT(SCM.SourceClientId,'00') + CAST(SCM.ClientSoId AS NVARCHAR) AS ClientSoIdView
461 ,'2' + FORMAT(SCM.SourceClientId,'00') + CAST(POD.PoId AS NVARCHAR) AS PoIdView
462 ,ISNULL(sd.Note,N'') + ISNULL(SCM.Note,N'') AS Note
463 ,SCM.CreateDatetime AS SoCreateDateTime
464 ,r.RouteName AS SoRouteName
465 ,CASE WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) <= 0 THEN 'COD'
466 WHEN (ISNULL(SCM.TotalPaidOnline, 0) - ISNULL(SCM.TotalPaidUsed, 0)) + (ISNULL(SCM.TotalPaidVinId, 0) - ISNULL(SCM.TotalPaidVinIdUsed, 0)) > 0 AND SCM.RemainingAmount > 0 THEN N'COD + Online'
467 ELSE 'Online' END AS CashType
468 --,DATEDIFF(MINUTE, GETDATE(), CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN po.TempDeliveryDateFrom ELSE po.DeliveryDateFrom END) AS RemainTime
469 ,DATEDIFF(MINUTE, GETDATE(), PO.TempDeliveryDateFrom) AS RemainTime
470 ,sd.SODetailStatusID
471 ,M.TradingAddress AS MerchantAddress
472 ,M.PhoneN0 AS MerchantPhone
473 ,CASE WHEN sd.SODetailStatusID = 1 THEN ISNULL(M.[MerchantName], '') + ' - ' + ISNULL(MW.Name, '') + N' - ' + FORMAT(MW.MerchantWarehouseId, '000000') + ' - ' + MW.FullAddress ELSE '' END AS PuId
474 ,CP.ManufactureBarcode + CASE WHEN ISNULL(sd.SAPInternalMerchantSONumber, '') <> '' THEN CONCAT('(SO:', sd.SAPInternalMerchantSONumber, ')') ELSE '' END AS ManufactureBarcode
475 ,sd.SAPReservationNumber
476 ,sd.SAPPONumberInterim
477 ,sd.SAPReservationLineItem
478 ,0 AS IsSelect
479 ,CASE WHEN EXISTS (SELECT 1 FROM @VinmartId vi WHERE vi.VinmartId = m.MerchantId) THEN 1 ELSE 0 END AS OrderType
480 ,pommw.ReverseCode
481 --,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) < 2 THEN Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByPolicy
482 --,CASE WHEN ISNULL(po.DeliveryPolicyType, 0) = 2 THEN Format(po.TempDeliveryDateFrom, 'HH:mm') + ' - ' + Format(po.DeliveryDateFrom, 'HH:mm') ELSE '' END AS DeliveryTimeByCustomer
483 ,Format(po.MerchantPickupDateFrom, 'dd/MM/yyyy') AS DatePickupAgain -- Ngay hen lay
484 ,Format(PO.TempDeliveryDateFrom, 'dd/MM/yyyy') AS DateDelivery
485 ,sd.ClientMerchantId
486 ,pt.ProductTypeName
487 ,sc.Name AS SourceClientName
488 ,PO.ClientMerchantWarehouseId
489 ,CASE WHEN vlr.ClientRPO IS NOT NULL THEN 1 ELSE 0 END AS IsCreatedRpo
490 FROM
491 PurchaseOrder PO WITH (NOLOCK)
492 INNER JOIN dbo.SO_Detail sd WITH (NOLOCK) ON sd.PoId = PO.PoId
493 INNER JOIN PurchaseOrder_Detail POD WITH (NOLOCK) ON POD.SODetailID = ISNULL(sd.SoDetailIdRoot, sd.SODetailID) AND POD.PoId = PO.PoId
494 INNER JOIN ClientProduct CP WITH (NOLOCK) ON CP.ClientProductId = POD.ClientProductId
495 INNER JOIN ClientMerchantWarehouse MW WITH (NOLOCK) ON MW.ClientMerchantWarehouseId = PO.ClientMerchantWarehouseId
496 INNER JOIN ClientMerchant M WITH (NOLOCK) ON M.ClientMerchantId = sd.ClientMerchantId
497 INNER JOIN dbo.SO SCM WITH (NOLOCK) ON SCM.SOID = PO.SoId
498 LEFT JOIN dbo.Routs RD WITH (NOLOCK) ON RD.RoutId = PO.RouteId
499 LEFT JOIN dbo.Routs r WITH (NOLOCK) ON PO.DeliveryRoutsId = r.RoutId
500 LEFT JOIN dbo.Stations ST WITH (NOLOCK) ON ST.SID = PO.SID_Tally
501 --LEFT JOIN dbo.Stations s2 WITH (NOLOCK) ON SCM.DeliveryCityId = s2.CityID AND SCM.DeliveryDistId = s2.DistID AND SCM.DeliveryWardId = s2.WardID AND SCM.DeliveryStreetId = s2.StreetID AND SCM.DeliveryHouseN0 = s2.HouseN0
502 LEFT JOIN dbo.PurchaseOrder_MappingMiniWarehourse pommw WITH (NOLOCK) ON pommw.ClientPoId = PO.ClientPoId --AND pommw.IsComplete = 1
503 INNER JOIN dbo.ProductType pt WITH (NOLOCK) ON pt.ProductTypeID = po.ProductTypeId
504 INNER JOIN dbo.SourceClient sc WITH (NOLOCK) ON sc.SourceClientId = SCM.SourceClientId
505 LEFT JOIN dbo.Vinpro_LogRPO vlr WITH (NOLOCK) ON sd.ClientSoId = vlr.ClientSoid
506 WHERE
507 (
508 (po.POStatusId = 1 AND sd.SODetailStatusID = 1) OR
509 (po.POStatusId = 4 AND sd.SODetailStatusID = 4)
510 )
511 AND SID_Tally = @StationId
512 AND SCM.SourceClientId = @SourceClientId
513 AND po.ProductTypeId < 4
514 AND po.Visible = 1
515 AND po.DeliveryFlowId = 2
516 AND SCM.UnitTransportId = 1
517 AND ISNULL(po.IsDropOff, 0) = 0
518 AND po.SID_Destination != 0
519 AND ISNULL(scm.IsDeliveryPiC, 0) = 0
520 AND ISNULL(po.IsOnhold, 0) = 0
521 AND ISNULL(scm.OrderType, 0) = 1
522 AND sd.Visible = 1
523
524 END TRY
525 BEGIN CATCH
526 THROW;
527 END CATCH
528END