· 6 years ago · Aug 28, 2019, 12:22 PM
1SET QUOTED_IDENTIFIER ON
2SET ANSI_NULLS ON
3GO
4-- =============================================
5-- Author: thanh.nguyen
6-- Create date: 2016-04-22
7-- Description: Bao cao nhap, xuat, ton Vat tu
8-- =============================================
9CREATE PROCEDURE [dbo].[FAB_Rpt_MaterialBalance_SX17_04]
10 @fromDate DATE ,
11 @toDate DATE ,
12 @Warehouse WhseType ,
13 @loc LocType ,
14 @ProducCode ProductCodeType ,
15 @Site SiteType
16AS
17 BEGIN
18 SET NOCOUNT ON;
19 EXEC dbo.SetSiteSp @Site = @Site, -- SiteType
20 @Infobar = NULL -- InfobarType
21 IF ISNULL(@loc, '') = ''
22 SET @loc = NULL
23
24 DECLARE @YearFrom INT, @MonthFrom INT, @YearTo INT, @MonthTo INT
25
26 SELECT @YearFrom = YEAR(@fromDate), @MonthFrom = MONTH(@fromDate), @YearTo = YEAR(@toDate), @MonthTo = MONTH(@toDate)
27
28 SET @Warehouse = ISNULL(@Warehouse, '')
29 IF @Warehouse = ''
30 SET @Warehouse = '%'
31
32 SET @Warehouse = ISNULL(@Warehouse, '')
33 IF @Warehouse = ''
34 SET @Warehouse = '%'
35
36 DECLARE @t AS TABLE
37 (
38 item ItemType ,
39 item_desc DescriptionType ,
40 uom DescriptionType ,
41 whse WhseType ,
42 whse_desc DescriptionType ,
43 loc LocType ,
44 loc_desc DescriptionType ,
45 begin_qty AmountType DEFAULT 0 ,
46 begin_price AmountType DEFAULT 0 ,
47 begin_value AmountType DEFAULT 0 ,
48 in_qty AmountType DEFAULT 0 ,
49 in_price AmountType DEFAULT 0 ,
50 in_value AmountType DEFAULT 0 ,
51 out_qty AmountType DEFAULT 0 ,
52 out_price AmountType DEFAULT 0 ,
53 out_value AmountType DEFAULT 0 ,
54 end_qty AmountType DEFAULT 0 ,
55 end_price AmountType DEFAULT 0 ,
56 end_value AmountType DEFAULT 0
57 )
58
59 DECLARE @s AS TABLE
60 (
61 item ItemType ,
62 whse WhseType ,
63 loc LocType ,
64 qty QtyUnitType DEFAULT(0),
65 value AmountType DEFAULT(0)
66 )
67 DECLARE @s_temp AS TABLE
68 (
69 item ItemType ,
70 whse WhseType ,
71 loc LocType ,
72 qty QtyUnitType DEFAULT(0),
73 value AmountType DEFAULT(0)
74 )
75
76 --item
77 INSERT INTO @t
78 ( item ,
79 item_desc ,
80 uom ,
81 whse ,
82 whse_desc ,
83 loc ,
84 loc_desc
85 )
86 SELECT DISTINCT
87 i.item ,
88 i.[description] ,
89 u.[description] ,
90 m.whse ,
91 ISNULL(w.[name], '') ,
92 '',-- m.loc ,
93 ''--ISNULL(l.[description], '')
94 FROM matltran m
95 JOIN item i ON m.item = i.item
96 JOIN u_m u ON i.u_m = u.u_m
97 INNER JOIN location l ON m.loc = l.loc
98 INNER JOIN whse w ON w.whse = m.whse
99 WHERE ISNULL(@ProducCode, i.product_code) = i.product_code
100 AND ( m.whse LIKE @Warehouse
101 OR @Warehouse = ''
102 )
103 AND m.loc IS NOT NULL
104
105
106 --begin value
107 DELETE FROM @s
108 INSERT INTO @s
109 ( item ,
110 whse ,
111 loc ,
112 qty ,
113 value
114 )
115 SELECT B.item,
116 B.whse,
117 '',
118 0,
119 B.Uf_StockCost-- * B.Uf_StockAmount
120
121 FROM dbo.matltran B
122 JOIN
123 (
124 SELECT MAX(i.Uf_StockDate) Uf_StockDate,
125 i.item,
126 i.whse
127 FROM matltran i
128 WHERE YEAR(trans_date) * 100 + MONTH(trans_date) < @YearFrom * 100 + @MonthFrom
129 AND i.whse IS NOT NULL
130 AND i.loc IS NOT NULL
131 AND ISNULL(@loc, loc) = loc
132 AND EXISTS ( SELECT 1
133 FROM item
134 WHERE item = i.item
135 AND ISNULL(@ProducCode,
136 product_code) = product_code )
137 GROUP BY
138 i.item,
139 i.whse
140 ) A ON A.Uf_StockDate = B.Uf_StockDate AND A.whse = B.whse AND A.item = B.item
141 WHERE ISNULL(b.Uf_StockCost, 0) <> 0
142 ---lay so luong
143 INSERT INTO @s_temp
144 ( item ,
145 whse ,
146 loc ,
147 qty ,
148 value
149 )
150 SELECT A.item,
151 A.whse,
152 '',
153 A.qty,
154 0
155 FROM
156 (
157 SELECT item ,
158 whse ,
159 SUM(qty) qty
160 --SUM(qty * cost) amount
161 FROM matltran i
162 WHERE whse IS NOT NULL
163 AND CAST(trans_date AS DATE) < CAST(@fromDate AS DATE)
164 AND ISNULL(@loc, loc) = loc
165 AND loc IS NOT NULL
166 AND EXISTS ( SELECT 1
167 FROM item
168 WHERE item = i.item
169 AND ISNULL(@ProducCode,
170 product_code) = product_code )
171 GROUP BY item ,
172 whse
173 ) AS A WHERE (ISNULL(qty,0 ) <> 0 OR qty IS NOT NULL)
174
175
176 UPDATE t
177 SET begin_qty = s.qty
178 --begin_value = s.value
179 FROM @t t
180 INNER JOIN @s_temp s ON t.item = s.item
181 AND t.whse = s.whse
182 --AND t.loc = s.loc
183
184 UPDATE t
185 SET --begin_qty = s.qty ,
186 begin_value = (s.value * t.begin_qty)
187 FROM @t t
188 INNER JOIN @s s ON t.item = s.item
189 AND t.whse = s.whse
190 --AND t.loc = s.loc
191 WHERE (qty <> 0 OR qty IS NOT NULL)
192
193
194 --in value
195 DELETE FROM @s
196 INSERT INTO @s
197 ( item ,
198 whse ,
199 loc ,
200 qty ,
201 value
202 )
203 SELECT item ,
204 whse ,
205 '',--loc ,
206 SUM(qty) ,
207 SUM(qty * cost)
208 FROM matltran i
209 WHERE EXISTS ( SELECT 1
210 FROM item
211 WHERE item = i.item
212 AND ISNULL(@ProducCode, product_code) = product_code )
213 AND whse IS NOT NULL
214 AND CAST(trans_date AS DATE) >= CAST(@fromDate AS DATE)
215 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
216 AND ISNULL(@loc, loc) = loc
217 AND loc IS NOT NULL
218 AND (trans_type = 'F'
219 OR qty > 0
220 --OR (qty < 0 AND trans_type = 'T' AND ISNULL(Uf_ttype, IIF(qty > 0, 0, 1)) = 0)
221 )
222 GROUP BY item ,
223 whse --,
224 -- loc
225
226 UPDATE t
227 SET in_qty = s.qty ,
228 in_value = s.value
229 FROM @t t
230 INNER JOIN @s s ON t.item = s.item
231 AND t.whse = s.whse
232 -- AND t.loc = s.loc
233
234 --out value
235 DELETE FROM @s
236 INSERT INTO @s
237 ( item ,
238 whse ,
239 loc ,
240 qty ,
241 value
242 )
243 SELECT item ,
244 whse ,
245 '',--loc ,
246 SUM(-qty) ,
247 SUM(-qty * cost)
248 FROM matltran i
249 WHERE EXISTS ( SELECT 1
250 FROM item
251 WHERE item = i.item
252 AND ISNULL(@ProducCode, product_code) = product_code )
253 AND whse IS NOT NULL
254 AND CAST(trans_date AS DATE) >= CAST(@fromDate AS DATE)
255 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
256 AND ISNULL(@loc, loc) = loc
257 AND loc IS NOT NULL
258 AND trans_type <> 'F'
259 AND qty < 0
260 -- AND (trans_type <> 'T'
261 --OR (trans_type = 'T' AND ISNULL(Uf_ttype, IIF(qty > 0, 0, 1)) = 1)
262 --)
263 GROUP BY item ,
264 whse --,
265 --loc
266
267 UPDATE t
268 SET out_qty = s.qty ,
269 out_value = s.value
270 FROM @t t
271 INNER JOIN @s s ON t.item = s.item
272 AND t.whse = s.whse
273 -- AND t.loc = s.loc
274
275 --end value
276 DELETE FROM @s
277 DELETE FROM @s_temp
278 INSERT INTO @s
279 ( item ,
280 whse ,
281 loc ,
282 qty ,
283 value
284 )
285 SELECT B.item,
286 B.whse,
287 '',
288 0,--B.Uf_StockAmount,
289 B.Uf_StockCost --* B.Uf_StockAmount
290
291 FROM dbo.matltran B
292 JOIN
293 (
294 SELECT MAX(i.Uf_StockDate) Uf_StockDate,
295 i.item,
296 i.whse
297 FROM matltran i
298 WHERE YEAR(trans_date) * 100 + MONTH(trans_date) <= @YearTo * 100 + @MonthTo
299 AND i.whse IS NOT NULL
300 AND i.loc IS NOT NULL
301 AND ISNULL(@loc, loc) = loc
302 AND EXISTS ( SELECT 1
303 FROM item
304 WHERE item = i.item
305 AND ISNULL(@ProducCode,
306 product_code) = product_code )
307 GROUP BY
308 i.item,
309 i.whse
310 ) A ON A.Uf_StockDate = B.Uf_StockDate AND A.whse = B.whse AND A.item = B.item
311 WHERE ISNULL(b.Uf_StockCost, 0) <> 0
312 --tinh so luong
313 INSERT INTO @s_temp
314 ( item ,
315 whse ,
316 loc ,
317 qty ,
318 value
319 )
320 SELECT A.item,
321 A.whse,
322 '',
323 A.qty,
324 0
325 FROM
326 (
327 SELECT item ,
328 whse ,
329 SUM(qty) qty
330 --SUM(qty * cost) amount
331 FROM matltran i
332 WHERE EXISTS ( SELECT 1
333 FROM item
334 WHERE item = i.item
335 AND ISNULL(@ProducCode, product_code) = product_code )
336 AND whse IS NOT NULL
337 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
338 AND ISNULL(@loc, loc) = loc
339 AND loc IS NOT NULL
340 GROUP BY item ,
341 whse
342 ) AS A WHERE (ISNULL(A.qty,0) <> 0 OR A.qty IS NOT NULL)
343
344 --SELECT item ,
345 -- whse ,
346 -- '',--loc ,
347 -- SUM(qty) ,
348 -- SUM(qty * cost)
349 --FROM matltran i
350 --WHERE EXISTS ( SELECT 1
351 -- FROM item
352 -- WHERE item = i.item
353 -- AND ISNULL(@ProducCode, product_code) = product_code )
354 -- AND whse IS NOT NULL
355 -- AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
356 -- AND ISNULL(@loc, loc) = loc
357 -- AND loc IS NOT NULL
358 --GROUP BY item ,
359 -- whse --,
360 -- --loc
361
362 UPDATE t
363 SET end_qty = s.qty
364 --end_value = s.value
365 FROM @t t
366 INNER JOIN @s_temp s ON t.item = s.item
367 AND t.whse = s.whse
368 -- AND t.loc = s.loc
369
370 UPDATE t
371 SET --end_qty = s.qty ,
372 end_value = (s.value * t.end_qty)
373 FROM @t t
374 INNER JOIN @s s ON t.item = s.item
375 AND t.whse = s.whse
376 -- AND t.loc = s.loc
377
378 --price
379 UPDATE @t
380 SET begin_price = begin_value / begin_qty
381 WHERE begin_qty <> 0
382 AND begin_value <> 0
383
384 UPDATE @t
385 SET in_price = in_value / in_qty
386 WHERE in_qty <> 0
387 AND in_value <> 0
388
389 UPDATE @t
390 SET out_price = out_value / out_qty
391 WHERE out_qty <> 0
392 AND out_value <> 0
393
394 UPDATE @t
395 SET end_price = end_value / end_qty
396 WHERE end_qty <> 0
397 AND end_value <> 0
398
399 DELETE @t WHERE begin_qty = 0 AND in_qty = 0 AND out_qty = 0 AND end_qty = 0
400 UPDATE @t SET end_value = 0 WHERE end_qty = 0
401 --Result
402 SELECT *
403 FROM @t
404 WHERE begin_qty <> 0
405 OR begin_value <> 0
406 OR in_qty <> 0
407 OR in_value <> 0
408 OR out_qty <> 0
409 OR out_value <> 0
410 OR end_qty <> 0
411 OR end_value <> 0
412
413 END
414
415GO