1 PACKAGE BODY INV_QTY_MANAGEMENT AS
2 /* $Header: INVQTYMB.pls 120.1 2005/06/11 12:33:17 appldev $ */
3
4 PROCEDURE when_txn_qty_entered(p_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
5 p_txn_uom_code IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6 p_txn_unit_of_measure IN VARCHAR2,
7 p_prev_txn_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
8 p_serial_control_code IN NUMBER,
9 p_lot_control_code IN NUMBER,
10 p_primary_uom_code IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
11 p_organization_id IN NUMBER,
12 p_inventory_item_id IN NUMBER,
13 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
14 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
15 x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
16 x_error_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
17 x_error_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
18 p_txn_action_id IN NUMBER)
19 AS
20 l_rate NUMBER; -- gets the rate of conversion from txn_uom to primary
21 l_txn_qty NUMBER; -- stores the txn_qty in the primary UOM
22 BEGIN
23
24 if nvl(p_prev_txn_qty,-1) = -1 THEN
25 p_total_lot_qty := 0;
26 p_total_serial_qty := 0;
27 end if;
28
29 x_done := 'F';
30
31
32 IF p_primary_uom_code IS NULL THEN
33 BEGIN
34 SELECT primary_uom_code
35 INTO p_primary_uom_code
36 FROM mtl_system_items
37 WHERE organization_id = p_organization_id
38 AND inventory_item_id = p_inventory_item_id;
39 EXCEPTION
40 WHEN OTHERS THEN
41 x_error_code := 'E';
42 x_error_message := 'Could not find primary UOM';
43 RETURN;
44 END;
45 END IF;
46
47 IF p_txn_uom_code IS NULL THEN
48 BEGIN
49 SELECT uom_code
50 INTO p_txn_uom_code
51 FROM mtl_item_uoms_view
52 WHERE organization_id = p_organization_id
53 AND inventory_item_id = p_inventory_item_id
54 AND unit_of_measure = p_txn_unit_of_measure;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 x_error_code := 'E';
59 x_error_message := 'Could not find txn UOM Code';
60 RETURN;
61 END;
62 END IF;
63
64 inv_convert.inv_um_conversion(p_txn_uom_code,
65 p_primary_uom_code,
66 p_inventory_item_id,
67 l_rate);
68
69 l_txn_qty := p_txn_qty * l_rate;
70
71 --If transaction is an issue or a transfer, and the item is serial controlled
72 --then the quantity needs not even look at the lot qty and the behavior will
73 --be that of a receiving transaction that is serial controlled.
74
75 IF ((p_txn_action_id = 1 or p_txn_action_id = 2 or p_txn_action_id =3) AND
76 (p_serial_control_code<>1 AND p_serial_control_code <>6)) THEN
77
78 p_total_lot_qty := 0;
79 IF l_txn_qty < p_total_serial_qty THEN
80 p_txn_qty := p_prev_txn_qty;
81 x_error_code := 'E';
82 x_error_message := 'Txn qty cannot be less the total serial qty = ' || p_total_serial_qty;
83 RETURN;
84 ELSE
85 p_prev_txn_qty := p_txn_qty;
86 IF p_txn_qty = p_total_serial_qty THEN
87 x_done := 'T';
88 END IF;
89 x_error_code := 'C';
90 x_error_message := ' ';
91 RETURN;
92 END IF;
93
94
95 -- if item not lot controlled and not serial controlled just update the
96 -- the p_prev_txn_qty to the p_txn_qty and keep p_total_serial_qty
97 -- and p_total_lot_qty both as zero.
98
99 ELSIF p_lot_control_code = 1
100 AND (p_serial_control_code = 1 or
101 p_serial_control_code = 6) THEN
102 p_prev_txn_qty := p_txn_qty;
103 p_total_lot_qty := 0;
104 p_total_serial_qty := 0;
105 x_error_code := 'C';
106 x_error_message := ' ';
107 x_done := 'T';
108 RETURN;
109 -- if the item is lot controlled but not serial controlled then
110 -- make the p_total_serial_qty equal to zero, if the p_txn_qty
111 -- is less than the p_total_lot_qty then error OUT NOCOPY /* file.sql.39 change */ and set the
112 -- p_txn_qty equal to the p_prev_txn_qty and leave other values
113 -- same. Otherwise everything is fine, update the p_prev_txn_qty
114 -- to the new p_txn_qty, p_total_serial_qty to zero.
115 ELSIF p_lot_control_code = 2
116 AND (p_serial_control_code = 1 or
117 p_serial_control_code = 6) THEN
118 p_total_serial_qty := 0;
119 IF l_txn_qty < p_total_lot_qty THEN
120 p_txn_qty := p_prev_txn_qty;
121 x_error_code := 'E';
122 x_error_message := 'Txn qty cannot be less than the total lot qty = '||p_total_lot_qty;
123 RETURN;
124 ELSE
125 p_prev_txn_qty := p_txn_qty;
126 IF p_txn_qty = p_total_lot_qty THEN
127 x_done := 'T';
128 END IF;
129 x_error_code := 'C';
130 x_error_message := ' ';
131 RETURN;
132 END IF;
133 -- if the item is not lot controlled but serial controlled
134 -- make the p_total_lot_qty to zero, if the p_txn_qty is less
135 -- than the the p_total_serial_qty then error OUT NOCOPY /* file.sql.39 change */ and set the
136 -- p_txn_qty to the previous value and leave other values same
137 -- Otherwise everything is fine and update the p_prev_txn_qty
138 -- to the new p_txn_qty.
139 ELSIF p_lot_control_code = 1
140 AND (p_serial_control_code <> 1 and
141 p_serial_control_code <> 6) THEN
142 p_total_lot_qty := 0;
143 IF l_txn_qty < p_total_serial_qty THEN
144 p_txn_qty := p_prev_txn_qty;
145 x_error_code := 'E';
146 x_error_message := 'Txn qty cannot be less the total serial qty = ' || p_total_serial_qty;
147 RETURN;
148 ELSE
149 p_prev_txn_qty := p_txn_qty;
150 IF p_txn_qty = p_total_serial_qty THEN
151 x_done := 'T';
152 END IF;
153 x_error_code := 'C';
154 x_error_message := ' ';
155 RETURN;
156 END IF;
157 ELSE
158 -- item is both lot and serial controlled
159 -- he can only change this if he has entered serial numbers for the
160 -- current lot which match the lot quantity and thus
161 -- p_total_lot_qty would be equal to p_total_serial_qty.
162 -- We cannot reset the p_total_serial_qty to zero in this case
163 -- it should be reset to zero when the new lot number is entered.
164 IF l_txn_qty < p_total_lot_qty THEN
165 p_txn_qty := p_prev_txn_qty;
166 x_error_code := 'E';
167 x_error_message := 'Txn qty cannot be less than the total lot qty = '||p_total_lot_qty;
168 RETURN;
169 ELSE
170 p_prev_txn_qty := p_txn_qty;
171 IF p_txn_qty = p_total_lot_qty THEN
172 x_done := 'T';
173 END IF;
174 x_error_code := 'C';
175 x_error_message := ' ';
176 RETURN;
177 END IF;
178 END IF;
179
180 END;
181
182
183 PROCEDURE when_lot_qty_entered(p_txn_qty IN NUMBER,
184 p_txn_uom_code IN VARCHAR2,
185 p_primary_uom_code IN VARCHAR2,
186 p_inventory_item_id IN NUMBER,
187 p_lot_control_code IN NUMBER,
188 p_serial_control_code IN NUMBER,
189 p_current_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
190 p_prev_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
191 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
192 p_total_serial_qty IN NUMBER,
193 x_done IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
194 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
195 x_error_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
196 x_error_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
197
198 AS
199 l_rate NUMBER; -- gets the rate of conversion from txn_uom to primary
200 l_txn_qty NUMBER; -- stores the txn_qty in the primary UOM
201 l_allowed_lot_qty NUMBER;
202
203
204 BEGIN
205
206 x_lot_done := 'F';
207 x_done := 'F';
208
209 x_error_message := ':txn qty:'||p_txn_qty||
210 ':txn uom:'||p_txn_uom_code||
211 ':primary uom:'||p_primary_uom_code||
212 ':item id:'||p_inventory_item_id||
213 ':lot code:'||p_lot_control_code||
214 ':serial code:'||p_serial_control_code||
215 ':current lot:'||p_current_lot_qty||
216 ':prev lot:'||p_prev_lot_qty||
217 ':total lot:'||p_total_lot_qty||
218 ':total srl:'||p_total_serial_qty||
219 ':x done:'||x_done;
220
221
222 inv_convert.inv_um_conversion(p_txn_uom_code,
223 p_primary_uom_code,
224 p_inventory_item_id,
225 l_rate);
226
227 l_txn_qty := p_txn_qty * l_rate;
228
229 IF p_lot_control_code = 1 THEN
230 x_error_code := 'E';
231 x_error_message := 'You should not have come here';
232 RETURN;
233 END IF;
234
235 -- calculates the maximum value the value can be changed to.
236 l_allowed_lot_qty := (p_txn_qty - (p_total_lot_qty/l_rate));
237
238 -- if item is not serial controlled then allow to change the lot
239 -- quantity to a value equal to p_txn_qty - p_total_lot_qty and zero
240 -- on the lower end.
241 IF (p_serial_control_code = 1 or
242 p_serial_control_code = 6) THEN
243 IF p_current_lot_qty <= 0 OR
244 p_current_lot_qty > l_allowed_lot_qty THEN
245 p_current_lot_qty := p_prev_lot_qty;
246 x_error_code := 'E';
247 x_error_message := 'Lot quantity should be between 1 and '||l_allowed_lot_qty;
248 RETURN;
249 ELSE
250 p_prev_lot_qty := p_current_lot_qty;
251 p_total_lot_qty := p_total_lot_qty + (p_current_lot_qty * l_rate);
252 IF p_total_lot_qty = p_txn_qty THEN
253 x_done := 'T';
254 END IF;
255 x_lot_done := 'T';
256 x_error_code := 'C';
257 x_error_message := x_error_message||':finish:'||
258 ':current lot:'||p_current_lot_qty||
259 ':prev lot:'||p_prev_lot_qty||
260 ':total lot:'||p_total_lot_qty||
261 ':total srl:'||p_total_serial_qty||
262 ':x done:'||x_done;
263
264 RETURN;
265 END IF;
266 ELSE
267 -- item is also serial controlled.
268 -- fine if the current lot quantity is more than the serial numbers
269 -- already entered for the lot and it is less than the transaction
270 -- quantity minus the lot quantity entered.
271 -- the total lot quantity is modified with as the serial numbers are
272 -- entered for a lot and serial controlled item.
273 IF p_current_lot_qty < (p_total_serial_qty/l_rate) OR
274 p_current_lot_qty > l_allowed_lot_qty THEN
275 p_current_lot_qty := p_prev_lot_qty;
276 x_error_code := 'E';
277 x_error_message := 'Lot quantity should be between '||(p_total_serial_qty/l_rate)||' and '||l_allowed_lot_qty;
278 RETURN;
279 ELSE
280 p_prev_lot_qty := p_current_lot_qty;
281 IF p_current_lot_qty = (p_total_serial_qty/l_rate) THEN
282 x_lot_done := 'T';
283 END IF;
284 x_error_code := 'C';
285 x_error_message := ' ';
286 RETURN;
287 END IF;
288 END IF;
289 END;
290
291
292
293 PROCEDURE when_lot_num_entered(p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
294 p_serial_number_control_code IN NUMBER)
295 IS
296 BEGIN
297 IF p_serial_number_control_code <> 1 AND p_serial_number_control_code <> 6 THEN
298 p_total_serial_qty := 0;
299 END IF;
300 END when_lot_num_entered;
301
302 PROCEDURE when_srl_num_entered(p_txn_qty IN NUMBER,
303 p_txn_uom_code IN VARCHAR2,
304 p_primary_uom_code IN VARCHAR2,
305 p_inventory_item_id IN NUMBER,
306 p_current_lot_qty IN NUMBER,
307 p_lot_control_code IN NUMBER,
308 p_serial_control_code IN NUMBER,
309 p_from_serial IN VARCHAR2,
310 p_to_serial IN VARCHAR2,
311 p_total_lot_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
312 p_total_serial_qty IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
313 x_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
314 x_lot_done OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
315 x_error_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
316 x_error_message OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
317 p_txn_action_id IN NUMBER)
318
319 AS
320 l_rate NUMBER; -- gets the rate of conversion from txn_uom to primary
321 l_txn_qty NUMBER; -- stores the txn_qty in the primary UOM
322 l_num_serials NUMBER; -- stores the number of serials entered
323 l_from_serial_num NUMBER;
324 l_to_serial_num NUMBER;
325 l_prefix VARCHAR2(30);
326
327 BEGIN
328
329 x_done := 'F';
330 x_lot_done := 'F';
331
332 inv_convert.inv_um_conversion(p_txn_uom_code,
333 p_primary_uom_code,
334 p_inventory_item_id,
335 l_rate);
336
337 l_txn_qty := p_txn_qty * l_rate;
338
339 IF (p_serial_control_code = 1 or
340 p_serial_control_code = 6) THEN
341 x_error_code := 'E';
342 x_error_message := 'You should not have come here';
343 RETURN;
344 END IF;
345
346 IF p_to_serial IS NULL THEN
347 l_num_serials := 1;
348 ELSE
349 inv_validate.number_from_sequence(p_from_serial,
350 l_prefix,
351 l_from_serial_num);
352 inv_validate.number_from_sequence(p_to_serial,
353 l_prefix,
354 l_to_serial_num);
355 l_num_serials := l_to_serial_num - l_from_serial_num + 1;
356 END IF;
357
358 IF p_from_serial IS NULL THEN
359 x_error_code := 'E';
360 x_error_message := 'From serial cannot be NULL';
361 RETURN;
362 END IF;
363
364 --If transaction is an issue or a transfer, and the item is serial controlled
365 --then the quantity needs not even look at the lot qty and the behavior will be
366 --that of a receiving transaction that is serial controlled.
367 IF (p_txn_action_id = 1 or p_txn_action_id = 2 or p_txn_action_id =3) AND
368 (p_serial_control_code<>1 AND p_serial_control_code <>6) THEN
369 IF (p_total_serial_qty + l_num_serials) > (p_txn_qty * l_rate) THEN
370 x_error_code := 'E';
371 x_error_message := 'Max serials you can specify are '||((p_txn_qty*l_rate)-p_total_serial_qty);
372 RETURN;
373 ELSE
374 p_total_serial_qty := p_total_serial_qty + l_num_serials;
375 IF p_total_serial_qty = p_txn_qty THEN
376 x_done := 'T';
377 END IF;
378 x_error_code := 'C';
379 x_error_message := ' ';
380 RETURN;
381 END IF;
382
383 -- if item is not lot controlled then just update the p_total_serial_qty
384 -- by the number of serial numbers entered.
385 ELSIF p_lot_control_code = 1 THEN
386 IF (p_total_serial_qty + l_num_serials) > (p_txn_qty * l_rate) THEN
387 x_error_code := 'E';
388 x_error_message := 'Max serials you can specify are '||((p_txn_qty*l_rate)-p_total_serial_qty);
389 RETURN;
390 ELSE
391 p_total_serial_qty := p_total_serial_qty + l_num_serials;
392 IF p_total_serial_qty = p_txn_qty THEN
393 x_done := 'T';
394 END IF;
395 x_error_code := 'C';
396 x_error_message := ' ';
397 RETURN;
398 END IF;
399
400 -- item is lot and serial controlled
401 -- number of serials that can be entered are < p_current_lot_qty
402 -- minus the p_total_serial_qty
403 ELSIF (p_total_serial_qty + l_num_serials) > (p_current_lot_qty *
404 l_rate)
405 THEN
406 x_error_code := 'E';
407 x_error_message := 'Max serials you can specify are '||((p_current_lot_qty*l_rate)-p_total_serial_qty);
408 RETURN;
409 ELSE
410 p_total_serial_qty := p_total_serial_qty + l_num_serials;
411 p_total_lot_qty := p_total_lot_qty + l_num_serials;
412 IF p_total_lot_qty = p_txn_qty THEN
413 x_done := 'T';
414 x_lot_done := 'T';
415 END IF;
416 IF p_total_lot_qty = p_total_serial_qty THEN
417 x_lot_done := 'T';
418 END IF;
419 x_error_code := 'C';
420 x_error_message := ' ';
421 END IF;
422
423 END;
424
425 END inv_qty_management;
[Home] [Help]