· 6 years ago · Sep 06, 2019, 07:26 AM
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]
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 SET @Warehouse = ISNULL(@Warehouse, '')
25 IF @Warehouse = ''
26 SET @Warehouse = '%'
27
28 SET @Warehouse = ISNULL(@Warehouse, '')
29 IF @Warehouse = ''
30 SET @Warehouse = '%'
31
32 DECLARE @t AS TABLE
33 (
34 item ItemType ,
35 item_desc DescriptionType ,
36 uom DescriptionType ,
37 whse WhseType ,
38 whse_desc DescriptionType ,
39 loc LocType ,
40 loc_desc DescriptionType ,
41 begin_qty AmountType DEFAULT 0 ,
42 begin_price AmountType DEFAULT 0 ,
43 begin_value AmountType DEFAULT 0 ,
44 in_qty AmountType DEFAULT 0 ,
45 in_price AmountType DEFAULT 0 ,
46 in_value AmountType DEFAULT 0 ,
47 out_qty AmountType DEFAULT 0 ,
48 out_price AmountType DEFAULT 0 ,
49 out_value AmountType DEFAULT 0 ,
50 end_qty AmountType DEFAULT 0 ,
51 end_price AmountType DEFAULT 0 ,
52 end_value AmountType DEFAULT 0
53 )
54
55 DECLARE @s AS TABLE
56 (
57 item ItemType ,
58 whse WhseType ,
59 loc LocType ,
60 qty QtyUnitType ,
61 value AmountType
62 )
63
64 --item
65 INSERT INTO @t
66 ( item ,
67 item_desc ,
68 uom ,
69 whse ,
70 whse_desc ,
71 loc ,
72 loc_desc
73 )
74 SELECT DISTINCT
75 i.item ,
76 i.[description] ,
77 u.[description] ,
78 m.whse ,
79 ISNULL(w.[name], '') ,
80 '',-- m.loc ,
81 ''--ISNULL(l.[description], '')
82 FROM matltran m
83 JOIN item i ON m.item = i.item
84 JOIN u_m u ON i.u_m = u.u_m
85 INNER JOIN location l ON m.loc = l.loc
86 INNER JOIN whse w ON w.whse = m.whse
87 WHERE ISNULL(@ProducCode, i.product_code) = i.product_code
88 AND ( m.whse LIKE @Warehouse
89 OR @Warehouse = ''
90 )
91 AND m.loc IS NOT NULL
92
93
94 --begin value
95 DELETE FROM @s
96 INSERT INTO @s
97 ( item ,
98 whse ,
99 loc ,
100 qty ,
101 value
102 )
103 SELECT item ,
104 whse ,
105 '' ,--loc ,
106 SUM(qty) ,
107 SUM(qty * cost)
108 FROM matltran i
109 WHERE whse IS NOT NULL
110 AND CAST(trans_date AS DATE) < CAST(@fromDate AS DATE)
111 AND ISNULL(@loc, loc) = loc
112 AND loc IS NOT NULL
113 AND EXISTS ( SELECT 1
114 FROM item
115 WHERE item = i.item
116 AND ISNULL(@ProducCode,
117 product_code) = product_code )
118 GROUP BY item ,
119 whse --,
120 --loc
121
122 UPDATE t
123 SET begin_qty = s.qty ,
124 begin_value = s.value
125 FROM @t t
126 INNER JOIN @s s ON t.item = s.item
127 AND t.whse = s.whse
128 --AND t.loc = s.loc
129
130 --in value
131 DELETE FROM @s
132 INSERT INTO @s
133 ( item ,
134 whse ,
135 loc ,
136 qty ,
137 value
138 )
139 SELECT item ,
140 whse ,
141 '',--loc ,
142 SUM(qty) ,
143 SUM(qty * cost)
144 FROM matltran i
145 WHERE EXISTS ( SELECT 1
146 FROM item
147 WHERE item = i.item
148 AND ISNULL(@ProducCode, product_code) = product_code )
149 AND whse IS NOT NULL
150 AND CAST(trans_date AS DATE) >= CAST(@fromDate AS DATE)
151 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
152 AND ISNULL(@loc, loc) = loc
153 AND loc IS NOT NULL
154 AND (trans_type = 'F'
155 OR qty > 0
156 --OR (qty < 0 AND trans_type = 'T' AND ISNULL(Uf_ttype, IIF(qty > 0, 0, 1)) = 0)
157 )
158 GROUP BY item ,
159 whse --,
160 -- loc
161
162 UPDATE t
163 SET in_qty = s.qty ,
164 in_value = s.value
165 FROM @t t
166 INNER JOIN @s s ON t.item = s.item
167 AND t.whse = s.whse
168 -- AND t.loc = s.loc
169
170 --out value
171 DELETE FROM @s
172 INSERT INTO @s
173 ( item ,
174 whse ,
175 loc ,
176 qty ,
177 value
178 )
179 SELECT item ,
180 whse ,
181 '',--loc ,
182 SUM(-qty) ,
183 SUM(-qty * cost)
184 FROM matltran i
185 WHERE EXISTS ( SELECT 1
186 FROM item
187 WHERE item = i.item
188 AND ISNULL(@ProducCode, product_code) = product_code )
189 AND whse IS NOT NULL
190 AND CAST(trans_date AS DATE) >= CAST(@fromDate AS DATE)
191 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
192 AND ISNULL(@loc, loc) = loc
193 AND loc IS NOT NULL
194 AND trans_type <> 'F'
195 AND qty < 0
196 -- AND (trans_type <> 'T'
197 --OR (trans_type = 'T' AND ISNULL(Uf_ttype, IIF(qty > 0, 0, 1)) = 1)
198 --)
199 GROUP BY item ,
200 whse --,
201 --loc
202
203 UPDATE t
204 SET out_qty = s.qty ,
205 out_value = s.value
206 FROM @t t
207 INNER JOIN @s s ON t.item = s.item
208 AND t.whse = s.whse
209 -- AND t.loc = s.loc
210
211 --end value
212 DELETE FROM @s
213 INSERT INTO @s
214 ( item ,
215 whse ,
216 loc ,
217 qty ,
218 value
219 )
220 SELECT item ,
221 whse ,
222 '',--loc ,
223 SUM(qty) ,
224 SUM(qty * cost)
225 FROM matltran i
226 WHERE EXISTS ( SELECT 1
227 FROM item
228 WHERE item = i.item
229 AND ISNULL(@ProducCode, product_code) = product_code )
230 AND whse IS NOT NULL
231 AND CAST(trans_date AS DATE) <= CAST(@toDate AS DATE)
232 AND ISNULL(@loc, loc) = loc
233 AND loc IS NOT NULL
234 GROUP BY item ,
235 whse --,
236 --loc
237
238 UPDATE t
239 SET end_qty = s.qty ,
240 end_value = s.value
241 FROM @t t
242 INNER JOIN @s s ON t.item = s.item
243 AND t.whse = s.whse
244 -- AND t.loc = s.loc
245
246 --price
247 UPDATE @t
248 SET begin_price = begin_value / begin_qty
249 WHERE begin_qty <> 0
250 AND begin_value <> 0
251
252 UPDATE @t
253 SET in_price = in_value / in_qty
254 WHERE in_qty <> 0
255 AND in_value <> 0
256
257 UPDATE @t
258 SET out_price = out_value / out_qty
259 WHERE out_qty <> 0
260 AND out_value <> 0
261
262 UPDATE @t
263 SET end_price = end_value / end_qty
264 WHERE end_qty <> 0
265 AND end_value <> 0
266
267 DELETE @t WHERE begin_qty = 0 AND in_qty = 0 AND out_qty = 0 AND end_qty = 0
268 UPDATE @t SET end_value = 0 WHERE end_qty = 0
269 --Result
270 SELECT *
271 FROM @t
272 WHERE begin_qty <> 0
273 OR begin_value <> 0
274 OR in_qty <> 0
275 OR in_value <> 0
276 OR out_qty <> 0
277 OR out_value <> 0
278 OR end_qty <> 0
279 OR end_value <> 0
280
281 END
282
283GO