· 6 years ago · May 20, 2019, 10:24 AM
1USE [BELDMS]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_RPT_UncompletedTransaction] Script Date: 5/20/2019 5:20:46 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Nguyen Phuong Hoai Thu
10-- Create date: 2018/06/01
11-- Description:
12
13-- Edited: Nguyen Phuong Hoai Thu
14-- Edited date: 2018/06/01
15-- Description: Turning
16-- =============================================
17-- EXEC [sp_DMS_RPT_UncompletedTransaction] 3, 219, 'admin', '2018/09/27', '2018/09/27', null, 219
18ALTER PROCEDURE [dbo].[sp_DMS_RPT_UncompletedTransaction]
19 @CompanyID INT = NULL, --
20 @DistributorID INT = NULL, --
21 @LoginID VARCHAR(50) = NULL, --
22 @FromDate DATETIME = NULL, --
23 @ToDate DATETIME = NULL, --
24 @ChannelID INT = NULL, --
25 @SalesAreaID INT = NULL,--
26 @SelectedDistributorID INT = NULL,--
27 @RouteCD VARCHAR(15) = NULL,--
28 @CustomerID INT = NULL --
29AS
30BEGIN
31 SET NOCOUNT ON;
32 SET FMTONLY OFF;
33 SET NO_BROWSETABLE OFF;
34
35 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
36 DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
37 DECLARE @_FromDate DATETIME = @FromDate;
38 DECLARE @_ToDate DATETIME = @ToDate;
39 DECLARE @_LoginID VARCHAR(50) = @LoginID;
40 DECLARE @_TerritoryType CHAR(1);
41 DECLARE @_SalesAreaID INT = NULLIF(@SalesAreaID, 0);
42 DECLARE @_SelectedDistributorID INT = NULLIF(@SelectedDistributorID, 0);
43 DECLARE @_RouteCD VARCHAR(15) = NULLIF(@RouteCD, '');
44 DECLARE @_CustomerID INT = NULLIF(@CustomerID, 0);
45
46 SELECT CompanyID,
47 SiteID
48 INTO #tmpSiteAccess
49 FROM dbo.DMSViewSiteByUser
50 WHERE CompanyID = @CompanyID
51 AND Username = @LoginID;
52
53 CREATE TABLE #tmp (CompanyID INT,
54 DistributorID INT,
55 DistributorLocationID INT,
56 SalesOrgID INT,
57 SalesOrg_0_ValueID INT,
58 SalesOrg_0_ValueCD VARCHAR(15),
59 SalesOrg_0_ValueName NVARCHAR(50),
60 SalesForce_0_Name NVARCHAR(50),
61 SalesOrg_1_ValueID INT,
62 SalesOrg_1_ValueCD VARCHAR(15),
63 SalesOrg_1_ValueName NVARCHAR(50),
64 SalesForce_1_Name NVARCHAR(50),
65 SalesOrg_2_ValueID INT,
66 SalesOrg_2_ValueCD VARCHAR(15),
67 SalesOrg_2_ValueName NVARCHAR(50),
68 SalesForce_2_Name NVARCHAR(50),
69 SalesOrg_3_ValueID INT,
70 SalesOrg_3_ValueCD VARCHAR(15),
71 SalesOrg_3_ValueName NVARCHAR(50),
72 SalesForce_3_Name NVARCHAR(50),
73 SalesOrg_4_ValueID INT,
74 SalesOrg_4_ValueCD VARCHAR(15),
75 SalesOrg_4_ValueName NVARCHAR(50),
76 SalesForce_4_Name NVARCHAR(50),
77 SalesOrg_5_ValueID INT,
78 SalesOrg_5_ValueCD VARCHAR(15),
79 SalesOrg_5_ValueName NVARCHAR(50),
80 SalesForce_5_Name NVARCHAR(50),
81 SalesOrg_6_ValueID INT,
82 SalesOrg_6_ValueCD VARCHAR(15),
83 SalesOrg_6_ValueName NVARCHAR(50),
84 SalesForce_6_Name NVARCHAR(50),
85 SalesOrg_7_ValueID INT,
86 SalesOrg_7_ValueCD VARCHAR(15),
87 SalesOrg_7_ValueName NVARCHAR(50),
88 SalesForce_7_Name NVARCHAR(50),
89 SalesOrg_8_ValueID INT,
90 SalesOrg_8_ValueCD VARCHAR(15),
91 SalesOrg_8_ValueName NVARCHAR(50),
92 SalesForce_8_Name NVARCHAR(50),
93 SalesOrg_9_ValueID INT,
94 SalesOrg_9_ValueCD VARCHAR(15),
95 SalesOrg_9_ValueName NVARCHAR(50),
96 SalesForce_9_Name NVARCHAR(50),
97 ParentID INT,
98 ParentLevel INT);
99
100 ---- Lấy thông tin permission theo user đăng nháºp
101 SELECT *
102 INTO #tmpPermission
103 FROM dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID, @_LoginID);
104
105 ---- Group by các dữ liệu permission
106 SELECT DistributorID
107 INTO #tmpPrmDistributor
108 FROM #tmpPermission
109 GROUP BY DistributorID;
110
111 ---- Lấy working position của user đăng nháºp
112 ---- Äấy là báo cáo NPP nên nếu territory type khác SS thì set kiểu view 1 NPP
113 SELECT @_TerritoryType = (SELECT TOP 1 TerritoryType FROM #tmpPermission);
114 IF @_TerritoryType IS NULL
115 INSERT INTO #tmpPrmDistributor
116 VALUES (@_DistributorID);
117
118 ---- Lấy thông tin NPP với cây sales org tương ứng
119 INSERT INTO #tmp
120 EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;
121
122 ---- Lấy thông tin đơn bán hà ng
123 SELECT soo.CustomerID,
124 soo.CompanyID,
125 soo.BranchID,
126 soo.UsrRoute,
127 soo.OrderNbr,
128 OrderType = CASE CASE soo.OrderType
129 WHEN 'SO' THEN 'IN'
130 ELSE soo.OrderType END
131 WHEN 'IN' THEN N'Bán hà ng'
132 WHEN 'CM' THEN N'Trả hà ng' END,
133 soo.OrderTotal,
134 CASE soo.UsrPDAOrderType
135 WHEN 'Pre' THEN N'Äặt trÆ°á»›c giao sau'
136 WHEN 'Van' THEN N'Bán hà ng trực tiếp' END OrigOrderType,
137 soo.OrderDate,
138 ModuleID = 'SO',
139 ModuleDesc = 'Bán hà ng',
140 ScreenDesc = CASE
141 WHEN sos.ShipmentNbr IS NULL
142 AND soo.UsrIsPrinted = 1 THEN N'XỠlý hoà n tất đơn hà ng'
143 WHEN sos.ShipmentNbr IS NULL THEN N'XỠlý hoà n tất đơn hà ng'
144 WHEN sos.ShipmentNbr IS NOT NULL
145 AND sos.InvtRefNbr IS NULL THEN N'Hóa đơn'
146 WHEN sos.ShipmentNbr IS NOT NULL
147 AND sos.InvtRefNbr IS NOT NULL
148 AND intr.Released = 0 THEN N'Xác nháºn chứng từ'
149 ELSE '' END,
150 Status = CASE
151 WHEN sos.ShipmentNbr IS NULL
152 AND soo.UsrIsPrinted = 1 THEN N'ChÆ°a hoà n tất giao hà ng (Äã in Ä‘Æ¡n hà ng)'
153 WHEN sos.ShipmentNbr IS NULL THEN N'Chưa in đơn hà ng'
154 WHEN sos.ShipmentNbr IS NOT NULL
155 AND sos.InvtRefNbr IS NULL THEN N'Chưa hoà n tất hóa đơn'
156 WHEN sos.ShipmentNbr IS NOT NULL
157 AND sos.InvtRefNbr IS NOT NULL
158 AND intr.Released = 0 THEN N'ChÆ°a hoà n tất giao hà ng (Äã in Ä‘Æ¡n hà ng)'
159 ELSE '' END,
160 Action = CASE
161 WHEN sos.ShipmentNbr IS NULL
162 AND soo.UsrIsPrinted = 1 THEN N'Hoà n tất giao hà ng'
163 WHEN sos.ShipmentNbr IS NULL THEN N'In và hoà n tất giao hà ng'
164 WHEN sos.ShipmentNbr IS NOT NULL
165 AND sos.InvtRefNbr IS NULL THEN N'XỠlý đơn hà ng'
166 WHEN sos.ShipmentNbr IS NOT NULL
167 AND sos.InvtRefNbr IS NOT NULL
168 AND intr.Released = 0 THEN N'Hoà n tất giao hà ng'
169 ELSE '' END,
170 TransactionGroup = CASE
171 WHEN sos.ShipmentNbr IS NULL
172 AND soo.UsrIsPrinted = 1 THEN
173 N'Chưa hoà n tất giao hà ng (Tự động hoà n tất khi baseline)'
174 WHEN sos.ShipmentNbr IS NULL THEN N'Chưa in đơn hà ng (Sẽ hủy khi baseline)'
175 WHEN sos.ShipmentNbr IS NOT NULL
176 AND sos.InvtRefNbr IS NULL THEN
177 N'Chưa hoà n tất đơn hà ng (Tự động hoà n tất khi baseline)'
178 WHEN sos.ShipmentNbr IS NOT NULL
179 AND sos.InvtRefNbr IS NOT NULL
180 AND intr.Released = 0 THEN
181 N'Chưa hoà n tất giao hà ng (Tự động hoà n tất khi baseline)'
182 ELSE '' END
183 INTO #tmpSOOrder
184 FROM dbo.SOOrder soo WITH (NOLOCK)
185 LEFT JOIN dbo.SOOrderShipment sos WITH (NOLOCK)
186 ON sos.CompanyID = soo.CompanyID
187 AND soo.OrderNbr = sos.OrderNbr
188 AND soo.OrderType = sos.OrderType
189 LEFT JOIN dbo.INRegister intr WITH (NOLOCK)
190 ON intr.CompanyID = sos.CompanyID
191 AND intr.RefNbr = sos.InvtRefNbr
192 AND intr.DocType = sos.InvtDocType
193 WHERE soo.Status <> 'C'
194 AND soo.Status <> 'L'
195 AND soo.Status <> 'I'
196 AND soo.OrderDate BETWEEN @_FromDate AND @_ToDate
197 AND ( @_RouteCD IS NULL
198 OR soo.UsrRoute = @_RouteCD)
199 AND ( @_CustomerID IS NULL
200 OR soo.CustomerID = @_CustomerID)
201 AND EXISTS (SELECT * FROM #tmpPrmDistributor p WHERE soo.BranchID = p.DistributorID)
202 AND ( @_SelectedDistributorID IS NULL
203 OR soo.BranchID = @_SelectedDistributorID);
204
205 ---- Lấy thông tin đơn mua hà ng
206 SELECT poo.CompanyID,
207 poo.BranchID,
208 poo.OrderNbr,
209 OrderType = 'Mua hà ng', --'PO',
210 poo.OrderTotal,
211 poo.OrderDate,
212 ModuleID = 'PO',
213 ModuleDesc = 'Mua hà ng',
214 ScreenDesc = CASE poo.Status
215 WHEN 'N' THEN N'ÄÆ¡n mua hà ng'
216 WHEN 'D' THEN N'Nháºp hà ng'
217 WHEN 'H' THEN N'Xá» lý phiếu nháºp hà ng từ SAP'
218 ELSE '' END,
219 Status = CASE poo.Status
220 WHEN 'N' THEN N'Chưa in đơn hà ng'
221 WHEN 'D' THEN N'Chưa xỠlý đơn mua hà ng'
222 WHEN 'H' THEN N'Chưa xỠlý đơn mua hà ng'
223 ELSE '' END,
224 Action = CASE poo.Status
225 WHEN 'N' THEN N'XỠlý đơn hà ng'
226 WHEN 'D' THEN N'XỠlý đơn mua hà ng'
227 WHEN 'H' THEN N'XỠlý đơn mua hà ng'
228 ELSE '' END,
229 TransactionGroup = ''
230 INTO #tmpPOOrder
231 FROM dbo.POOrder poo WITH (NOLOCK)
232 WHERE Status <> 'C'
233 AND poo.OrderDate BETWEEN @_FromDate AND @_ToDate
234 AND EXISTS (SELECT * FROM #tmpPrmDistributor p WHERE poo.BranchID = p.DistributorID)
235 AND ( @_SelectedDistributorID IS NULL
236 OR poo.BranchID = @_SelectedDistributorID);
237
238 ---- Lấy thông tin đơn đặt hà ng PDA
239 SELECT soo.CustomerID,
240 soo.CompanyID,
241 soo.BranchID,
242 soo.Route,
243 soo.OrderNbr,
244 OrderType = CASE CASE soo.OrderType
245 WHEN 'SO' THEN 'IN'
246 ELSE soo.OrderType END
247 WHEN 'IN' THEN N'Bán hà ng'
248 WHEN 'CM' THEN N'Trả hà ng' END,
249 OrigOrderType = CASE soo.OrderType
250 WHEN 'SO' THEN N'Äặt trÆ°á»›c giao sau' --'Pre'
251 WHEN 'IN' THEN N'Bán hà ng trực tiếp' --'Van'
252 ELSE NULL END,
253 soo.OrderTotal,
254 soo.OrderDate,
255 ModuleID = 'SO',
256 ModuleDesc = 'Bán hà ng',
257 ScreenDesc = N'XỠlý đơn hà ng từ PDA',
258 Status = CASE soo.Status
259 WHEN 'U' THEN N'Chưa in đơn hà ng'
260 WHEN 'F' THEN N'ÄÆ¡n thiếu hà ng'
261 WHEN 'P' THEN N'Chưa in đơn hà ng'
262 ELSE '' END,
263 Action = N'XỠlý đơn hà ng',
264 TransactionGroup = CASE soo.Status
265 WHEN 'U' THEN N'Chưa in đơn hà ng (Sẽ hủy khi baseline)'
266 WHEN 'F' THEN N'ÄÆ¡n thiếu hà ng (Sẽ hủy khi baseline)'
267 WHEN 'P' THEN N'Chưa in đơn hà ng (Sẽ hủy khi baseline)'
268 ELSE '' END
269 INTO #tmpPDASOOrder
270 FROM dbo.DMSPDAOrder soo WITH (NOLOCK)
271 JOIN dbo.DMSViewCustomerLocationWithoutAttribute vcl WITH (NOLOCK)
272 ON vcl.CompanyID = soo.CompanyID
273 AND vcl.CustomerCD = soo.CustomerID
274 AND vcl.LocationID = soo.CustomerLocation
275 WHERE soo.Status <> 'I'
276 AND soo.Status <> 'C'
277 AND soo.OrderDate BETWEEN @_FromDate AND @_ToDate
278 AND ( @_RouteCD IS NULL
279 OR soo.Route = @_RouteCD)
280 AND ( @_CustomerID IS NULL
281 OR vcl.CustomerID = @_CustomerID)
282 AND EXISTS (SELECT * FROM #tmpPrmDistributor p WHERE soo.BranchID = p.DistributorID)
283 AND ( @_SelectedDistributorID IS NULL
284 OR soo.BranchID = @_SelectedDistributorID);
285
286 ---- Lấy thông tin đơn hà ng RPO
287 SELECT rpo.CompanyID,
288 rpo.BranchID,
289 rpo.RPONbr,
290 RPOType = N'Äặt hà ng', --RPO',
291 rpo.OrderTotal,
292 rpo.OrderDate,
293 ModuleID = 'PO',
294 ModuleDesc = 'Mua hà ng',
295 ScreenDesc = N'XỠlý đơn mua hà ng từ SAP',
296 Status = N'Chưa xỠlý đơn mua hà ng',
297 Action = N'XỠlý đơn mua hà ng',
298 TransactionGroup = ''
299 INTO #tmpRPOOrder
300 FROM dbo.DMSRPOOrder rpo WITH (NOLOCK)
301 WHERE rpo.Status <> 'C'
302 AND rpo.Status <> 'L'
303 AND rpo.OrderDate BETWEEN @_FromDate AND @_ToDate
304 AND EXISTS (SELECT * FROM #tmpPrmDistributor p WHERE rpo.BranchID = p.DistributorID)
305 AND ( @_SelectedDistributorID IS NULL
306 OR rpo.BranchID = @_SelectedDistributorID);
307
308 ---- Lấy thông tin giao dịch kho
309 SELECT DISTINCT inr.BranchID,
310 inr.CompanyID,
311 inr.RefNbr,
312 --inr.DocType,
313 DocType = CASE
314 WHEN inr.DocType = 'R'
315 AND intr.InvtMult = 1 THEN N'Nháºp hà ng' -- N'Receipt'
316 WHEN inr.DocType = 'I' THEN N'Xuất hà ng' -- N'Issue'
317 WHEN inr.DocType = 'T' THEN N'Chuyển kho' -- N'Transfer'
318 WHEN inr.DocType = 'A' THEN N'Äiá»u chỉnh kho' -- N'Adjustment'
319 ELSE inr.DocType END,
320 inr.TotalAmount,
321 inr.TranDate,
322 ModuleID = 'IN',
323 ModuleDesc = 'Kho bãi',
324 ScreenDesc = CASE
325 WHEN inr.DocType = 'R'
326 AND intr.InvtMult = 1 THEN N'Phiếu nháºp kho'
327 WHEN inr.DocType = 'I' THEN N'Phiếu xuất kho'
328 WHEN inr.DocType = 'T' THEN N'Phiếu chuyển kho'
329 WHEN inr.DocType = 'A' THEN N'Phiếu Ä‘iá»u chỉnh' END,
330 Status = N'Chưa xỠlý hoà n tất chứng từ',
331 Action = N'XỠlý hoà n tất chứng từ',
332 TransactionGroup = ''
333 INTO #tmpInvTransaction
334 FROM dbo.INRegister inr WITH (NOLOCK)
335 JOIN dbo.INTran intr WITH (NOLOCK)
336 ON intr.CompanyID = inr.CompanyID
337 AND intr.RefNbr = inr.RefNbr
338 AND intr.DocType = inr.DocType
339 WHERE inr.Released <> 1
340 AND inr.OrigModule <> 'SO'
341 AND inr.OrigModule <> 'PO'
342 AND inr.TranDate BETWEEN @_FromDate AND @_ToDate
343 AND EXISTS (SELECT * FROM #tmpPrmDistributor p WHERE inr.BranchID = p.DistributorID)
344 AND ( @_SelectedDistributorID IS NULL
345 OR inr.BranchID = @_SelectedDistributorID);
346
347 SELECT *
348 INTO #tmpTransaction
349 FROM ( SELECT so.CompanyID,
350 so.BranchID DistributorID,
351 TranType = 'IN',
352 so.OrigOrderType,
353 so.OrderType,
354 so.OrderNbr,
355 so.OrderDate,
356 so.ModuleID,
357 so.ModuleDesc,
358 so.ScreenDesc,
359 so.Status,
360 so.Action,
361 so.OrderTotal,
362 so.TransactionGroup
363 FROM #tmpSOOrder so
364 UNION ALL
365 SELECT so.CompanyID,
366 so.BranchID DistributorID,
367 TranType = 'IN',
368 OrigOrderType,
369 so.OrderType,
370 so.OrderNbr,
371 so.OrderDate,
372 so.ModuleID,
373 so.ModuleDesc,
374 so.ScreenDesc,
375 so.Status,
376 so.Action,
377 so.OrderTotal,
378 so.TransactionGroup
379 FROM #tmpPDASOOrder so
380 UNION ALL
381 SELECT so.CompanyID,
382 DistributorID = so.BranchID,
383 TranType = 'IN',
384 OrigOrderType = '',
385 so.OrderType,
386 so.OrderNbr,
387 so.OrderDate,
388 so.ModuleID,
389 so.ModuleDesc,
390 so.ScreenDesc,
391 so.Status,
392 so.Action,
393 so.OrderTotal,
394 so.TransactionGroup
395 FROM #tmpPOOrder so
396 UNION ALL
397 SELECT so.CompanyID,
398 DistributorID = so.BranchID,
399 TranType = 'IN',
400 OrigOrderType = '',
401 OrderType = so.RPOType,
402 OrderNbr = so.RPONbr,
403 so.OrderDate,
404 so.ModuleID,
405 so.ModuleDesc,
406 so.ScreenDesc,
407 so.Status,
408 so.Action,
409 so.OrderTotal,
410 so.TransactionGroup
411 FROM #tmpRPOOrder so
412 UNION ALL
413 SELECT so.CompanyID,
414 DistributorID = so.BranchID,
415 TranType = 'IN',
416 OrigOrderType = '',
417 OrderType = so.DocType,
418 OrderNbr = so.RefNbr,
419 so.TranDate,
420 so.ModuleID,
421 so.ModuleDesc,
422 so.ScreenDesc,
423 so.Status,
424 so.Action,
425 so.TotalAmount,
426 so.TransactionGroup
427 FROM #tmpInvTransaction so) r;
428
429 SELECT DISTINCT tmp.SalesOrg_0_ValueCD SalesOrg_0_ValueName,
430 tmp.SalesOrg_1_ValueCD SalesOrg_1_ValueName,
431 tmp.SalesOrg_2_ValueCD SalesOrg_2_ValueName,
432 tmp.SalesOrg_3_ValueCD SalesOrg_3_ValueName,
433 tmp.SalesOrg_4_ValueCD SalesOrg_4_ValueName,
434 tmp.SalesOrg_5_ValueCD SalesOrg_5_ValueName,
435 tmp.SalesOrg_6_ValueCD SalesOrg_6_ValueName,
436 tmp.SalesOrg_7_ValueCD SalesOrg_7_ValueName,
437 tmp.SalesOrg_8_ValueCD SalesOrg_8_ValueName,
438 tmp.SalesOrg_9_ValueCD SalesOrg_9_ValueName,
439 dl.DistributorCD,
440 dl.DistributorName,
441 tr.CompanyID,
442 tr.DistributorID,
443 tr.TranType,
444 tr.OrderType,
445 tr.OrigOrderType,
446 tr.OrderNbr,
447 tr.OrderDate,
448 tr.ModuleID,
449 tr.ModuleDesc,
450 tr.ScreenDesc,
451 [Status] TranStatus,
452 [Action],
453 tr.OrderTotal,
454 tr.TransactionGroup
455 FROM #tmpTransaction tr
456 JOIN #tmp tmp
457 ON tmp.CompanyID = tr.CompanyID
458 AND tmp.DistributorID = tr.DistributorID
459 JOIN dbo.DMSViewDistributorLocation dl
460 ON dl.CompanyID = tmp.CompanyID
461 AND dl.DistributorID = tmp.DistributorID
462 WHERE ( @_SelectedDistributorID IS NULL
463 OR tr.DistributorID = @_SelectedDistributorID)
464 AND EXISTS ( SELECT *
465 FROM #tmpPrmDistributor p
466 WHERE p.DistributorID = tr.DistributorID)
467 AND ( @_SalesAreaID IS NULL
468 OR tmp.SalesOrg_0_ValueID = @_SalesAreaID
469 OR tmp.SalesOrg_1_ValueID = @_SalesAreaID
470 OR tmp.SalesOrg_2_ValueID = @_SalesAreaID
471 OR tmp.SalesOrg_3_ValueID = @_SalesAreaID
472 OR tmp.SalesOrg_4_ValueID = @_SalesAreaID
473 OR tmp.SalesOrg_5_ValueID = @_SalesAreaID
474 OR tmp.SalesOrg_6_ValueID = @_SalesAreaID
475 OR tmp.SalesOrg_7_ValueID = @_SalesAreaID
476 OR tmp.SalesOrg_8_ValueID = @_SalesAreaID
477 OR tmp.SalesOrg_9_ValueID = @_SalesAreaID);
478
479END;