· 6 years ago · Sep 25, 2019, 02:28 PM
1USE [reporting]
2GO
3/****** Object: StoredProcedure [dbo].[Report_OrderTransfer_3663_0001] Script Date: 09/25/2019 15:23:54 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9ALTER PROCEDURE [dbo].[Report_OrderTransfer_3663_0001]
10
11 @StartDate DateTime,
12 @EndDate DateTime,
13 @StartTime VarChar(5),
14 @EndTime VarChar(5)
15
16AS
17
18--DECLARE
19-- @StartDate DateTime,
20-- @EndDate DateTime,
21-- @StartTime VarChar(5),
22-- @EndTime VarChar(5)
23
24--SELECT
25-- @StartDate = '01 Dec 2013'
26-- ,@EndDate = '16 Dec 2013'
27-- ,@StartTime = '00:00'
28-- ,@EndTime = '23:59'
29
30
31 CREATE TABLE #RawData
32 (
33 SourceType VarChar(3),
34 RunDate VarChar(8),
35 RunTime VarChar(6),
36 DocumentNo VarChar(20),
37 ImportDate DateTime,
38 HeaderID Int,
39 LineID Int,
40 ExpectedOrders Int,
41 ExpectedLines Int,
42 ExpectedItems Int,
43 ActualLines Int,
44 ActualItems Int
45 )
46
47 CREATE TABLE #Output
48 (
49 [Format] VarChar(200),
50 TimeSeq VarChar(20),
51 RunDate VarChar(100),
52 RunTime VarChar(100),
53 [Status] VarChar(100),
54 [Error] VarChar(100),
55 OrdersActual VarChar(100),
56 OrdersExpected VarChar(100),
57 OrdersDiff VarChar(100),
58 LinesActual VarChar(100),
59 LinesExpected VarChar(100),
60 LinesDiff VarChar(100),
61 ItemsActual VarChar(100),
62 ItemsExpected VarChar(100),
63 ItemsDiff VarChar(100)
64 )
65
66 SELECT
67 @StartDate = DATEADD(hour, CONVERT(Int, LEFT(@StartTime, 2)), DATEADD(minute, CONVERT(Int, RIGHT(@StartTime, 2)), @StartDate)),
68 @EndDate = DATEADD(hour, CONVERT(Int, LEFT(@EndTime, 2)), DATEADD(minute, CONVERT(Int, RIGHT(@EndTime, 2)), @EndDate))
69
70 INSERT INTO #RawData(SourceType, RunDate, RunTime, DocumentNo, HeaderID, LineID, ImportDate, ExpectedOrders, ExpectedLines, ExpectedItems, ActualLines, ActualItems)
71 SELECT 'PBO'
72 ,LEFT(soh.UserDef8, 8)
73 ,LEFT(RIGHT(soh.UserDef8, 15),6)
74 ,soh.Documentno
75 ,soh.SalesHeaderID
76 ,NULL
77 ,soh.Entrydate
78 ,CONVERT(Int, soh.UserDef5)
79 ,CONVERT(Int, soh.UserDef6)
80 ,CONVERT(Int, soh.UserDef7)
81 ,NULL
82 ,NULL
83 FROM Pickmanager_dbo_SalesOrderHeaders soh
84 WHERE soh.Entrydate BETWEEN @StartDate AND @EndDate
85 UNION ALL
86 SELECT 'PBL'
87 ,LEFT(lol.UserDef8, 8)
88 ,LEFT(RIGHT(lol.UserDef8, 15),6)
89 ,loh.DocumentNo
90 ,loh.HeaderID
91 ,lol.LineID
92 ,loh.EntryDate
93 ,CONVERT(Int, lol.UserDef5)
94 ,CONVERT(Int, lol.UserDef6)
95 ,CONVERT(Int, lol.UserDef7)
96 ,1
97 ,lol.QtyOrdered
98 FROM Pickmanager_dbo_PickByLineOrderLines lol
99 JOIN Pickmanager_dbo_PickByLineOrderHeaders loh ON lol.HeaderID = loh.HeaderID
100 WHERE loh.EntryDate BETWEEN @StartDate AND @EndDate
101
102 -- Get PBO data
103 UPDATE dat
104 SET dat.ActualLines = sol.LineCount
105 ,dat.ActualItems = sol.ItemCount
106 FROM #RawData dat
107 JOIN (SELECT InitialSalesHeaderID, COUNT(*) AS LineCount, SUM(QtyOrdered) AS ItemCount
108 FROM PickManager.dbo.SalesOrderLines GROUP BY InitialSalesHeaderID) AS sol
109 ON dat.HeaderID = sol.InitialSalesHeaderID
110 WHERE dat.SourceType = 'PBO'
111
112 IF NOT EXISTS (SELECT 1 FROM #RawData)
113 BEGIN
114 SELECT
115 [0000LN&101||Heading1] = 'Location: ' + (SELECT TextValue FROM Parameters_ic WHERE ParameterName = 'DefaultCustomerName') +
116 ' - Date: ' + CONVERT(VarChar, GETDATE(), 106) + ' - Time: ' + LEFT(CONVERT(VarChar, GETDATE(), 108), 5),
117 [AAA0LN|| ] = 'There is no data for this date range.'
118 END
119 ELSE
120 BEGIN
121 INSERT INTO #Output([Format], TimeSeq, RunDate, RunTime, [Status], [Error], OrdersActual, OrdersExpected, OrdersDiff, LinesActual, LinesExpected, LinesDiff, ItemsActual, ItemsExpected, ItemsDiff)
122 SELECT
123 '', --[Format]
124 RunDate + RunTime,
125 CONVERT(VarChar, CAST(RunDate AS DateTime), 106), --RunDate
126 LEFT(RunTime, 2) + ':' + LEFT(RIGHT(RunTime, 4), 2) + ':' + RIGHT(RunTime, 2), --RunTime
127 CASE DATEDIFF(minute, MAX(ImportDate), GETDATE()) WHEN 0 THEN 'In Progress' ELSE 'Complete' END, --[Status]
128 CASE DATEDIFF(minute, MAX(ImportDate), GETDATE()) WHEN 0 THEN '' ELSE 'OK' END, --[Error]
129 COUNT(DISTINCT DocumentNo), --OrdersActual
130 ExpectedOrders, --OrdersExpected
131 ExpectedOrders - COUNT(DISTINCT DocumentNo), --OrdersDiff
132 SUM(ActualLines), --LinesActual
133 ExpectedLines, --LinesExpected
134 ExpectedLines - SUM(ActualLines), --LinesDiff
135 SUM(ActualItems), --ItemsActual
136 ExpectedItems, --ItemsExpected
137 ExpectedItems - SUM(ActualItems) --ItemsDiff
138 FROM #RawData
139 GROUP BY RunDate, RunTime, ExpectedOrders, ExpectedLines, ExpectedItems
140
141 --update error
142 UPDATE #Output
143 SET Error = 'ERROR',
144 [Format] = '[Bold]'
145 WHERE Error = 'OK'
146 AND NOT (OrdersDiff = 0 AND LinesDiff = 0 AND ItemsDiff = 0)
147
148 -- Output
149 SELECT
150 [0000LN&101||Heading1] = 'Location: ' + (SELECT TextValue FROM Parameters_ic WHERE ParameterName = 'DefaultCustomerName') +
151 ' - Date: ' + CONVERT(VarChar, GETDATE(), 106) + ' - Time: ' + LEFT(CONVERT(VarChar, GETDATE(), 108), 5),
152 [0000LN$MR||Format] = [Format],
153 [AAA0LN||Run Date] = RunDate,
154 [AAA0LN||Run Time] = RunTime,
155 [AAA0LN||Status] = [Status],
156 [AAA0LN||Error] = [Error],
157 [AAA0CN||Actual Orders] = OrdersActual,
158 [AAA0CN||Expected Orders] = OrdersExpected,
159 [AAA0CN||Difference] = OrdersDiff,
160 [AAA0CN||Actual Lines] = LinesActual,
161 [AAA0CN||Expected Lines] = LinesExpected,
162 [AAA0CN||Difference ] = LinesDiff,
163 [AAA0CN||Actual Items] = ItemsActual,
164 [AAA0CN||Expected Items] = ItemsExpected,
165 [AAA0CN||Difference ] = ItemsDiff
166 FROM #Output
167 ORDER BY TimeSeq
168 END
169
170 DROP TABLE #RawData
171 DROP TABLE #Output