[Home] [Help]
PACKAGE BODY: APPS.INV_QUANTITY_TREE_UE
Source
1 PACKAGE BODY INV_QUANTITY_TREE_UE AS
2 /* $Header: INVQTUEB.pls 120.10.12020000.3 2012/11/01 12:10:19 ksivasa ship $*/
3
4
5 g_debug NUMBER := NULL;
6 PROCEDURE print_debug(p_message IN VARCHAR2, p_level IN NUMBER DEFAULT 14) IS
7 BEGIN
8 IF g_debug IS NULL THEN
9 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 END IF;
11
12 IF (g_debug = 1) THEN
13 inv_log_util.trace(p_message, 'INV_QUANTITY_TREE_UE', p_level);
14 END IF;
15 END;
16
17 -- bug 4104123 : replace p_demand_header_type default NULL by 0
18 FUNCTION create_tree(p_organization_id IN NUMBER,
19 p_inventory_item_id IN NUMBER,
20 p_revision_control IN NUMBER DEFAULT 1,
21 p_lot_control IN NUMBER DEFAULT 1,
22 p_serial_control IN NUMBER DEFAULT 1,
23 p_lot_active IN NUMBER DEFAULT 2,
24 p_demand_header_id IN NUMBER DEFAULT NULL,
25 p_demand_header_type IN NUMBER,
26 p_tree_mode IN NUMBER DEFAULT 3, --2 replaced by 3 for bug7038890
27 p_negative_inv_allowed IN NUMBER DEFAULT 0,
28 p_lot_expiration_date IN DATE DEFAULT NULL,
29 p_activate IN NUMBER DEFAULT 1,
30 p_uom_code IN VARCHAR2 DEFAULT NULL,
31 p_asset_subinventory_only IN NUMBER DEFAULT 0,
32 p_demand_source_name IN VARCHAR2 DEFAULT NULL,
33 p_demand_source_line_id IN NUMBER DEFAULT NULL,
34 p_demand_source_delivery IN NUMBER DEFAULT NULL,
35 p_rev_active IN NUMBER DEFAULT 2,
36 x_available_quantity OUT NOCOPY NUMBER,
37 x_onhand_quantity OUT NOCOPY NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_message_count OUT NOCOPY NUMBER,
40 x_message_data OUT NOCOPY VARCHAR2,
41 p_lpn_id IN NUMBER DEFAULT NULL, --added for bug7038890
42 p_wms_task_type IN NUMBER DEFAULT NULL) --added for bug12831673
43 RETURN NUMBER
44 IS
45
46 l_QTY NUMBER;
47 l_grade_code VARCHAR2(150) := NULL;
48 l_available_quantity2 NUMBER := NULL;
49 l_onhand_quantity2 NUMBER := NULL;
50 BEGIN
51 -- invConv changes begin : calling the overloaded function :
52 l_QTY := create_tree( p_organization_id => p_organization_id
53 , p_inventory_item_id => p_inventory_item_id
54 , p_revision_control => p_revision_control
55 , p_lot_control => p_lot_control
56 , p_serial_control => p_serial_control
57 , p_grade_code => l_grade_code
58 , p_lot_active => p_lot_active
59 , p_demand_header_id => p_demand_header_id
60 , p_demand_header_type => p_demand_header_type
61 , p_tree_mode => p_tree_mode
62 , p_negative_inv_allowed => p_negative_inv_allowed
63 , p_lot_expiration_date => p_lot_expiration_date
64 , p_activate => p_activate
65 , p_uom_code => p_uom_code
66 , p_asset_subinventory_only => p_asset_subinventory_only
67 , p_demand_source_name => p_demand_source_name
68 , p_demand_source_line_id => p_demand_source_line_id
69 , p_demand_source_delivery => p_demand_source_delivery
70 , p_rev_active => p_rev_active
71 , x_available_quantity => x_available_quantity
72 , x_available_quantity2 => l_available_quantity2
73 , x_onhand_quantity => x_onhand_quantity
74 , x_onhand_quantity2 => l_onhand_quantity2
75 , x_return_status => x_return_status
76 , x_message_count => x_message_count
77 , x_message_data => x_message_data
78 , p_lpn_id => p_lpn_id --added for bug7038890
79 , p_wms_task_type => p_wms_task_type); --added for bug12831673
80 -- invConv changes end.
81
82 RETURN l_QTY;
83
84 END create_tree;
85
86 -- invConv changes begin : overloaded version of create_tree:
87 -- bug 4104123 : replace p_demand_header_type default NULL by 0
88 FUNCTION create_tree( p_organization_id IN NUMBER
89 , p_inventory_item_id IN NUMBER
90 , p_revision_control IN NUMBER DEFAULT 1
91 , p_lot_control IN NUMBER DEFAULT 1
92 , p_serial_control IN NUMBER DEFAULT 1
93 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
94 , p_lot_active IN NUMBER DEFAULT 2
95 , p_demand_header_id IN NUMBER DEFAULT NULL
96 , p_demand_header_type IN NUMBER DEFAULT 0
97 , p_tree_mode IN NUMBER DEFAULT 3 --2 replaced by 3 for bug7038890
98 , p_negative_inv_allowed IN NUMBER DEFAULT 0
99 , p_lot_expiration_date IN DATE DEFAULT NULL
100 , p_activate IN NUMBER DEFAULT 1
101 , p_uom_code IN VARCHAR2 DEFAULT NULL
102 , p_asset_subinventory_only IN NUMBER DEFAULT 0
103 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
104 , p_demand_source_line_id IN NUMBER DEFAULT NULL
105 , p_demand_source_delivery IN NUMBER DEFAULT NULL
106 , p_rev_active IN NUMBER DEFAULT 2
107 , x_available_quantity OUT NOCOPY NUMBER
108 , x_available_quantity2 OUT NOCOPY NUMBER -- invConv change
109 , x_onhand_quantity OUT NOCOPY NUMBER
110 , x_onhand_quantity2 OUT NOCOPY NUMBER -- invConv change
111 , x_return_status OUT NOCOPY VARCHAR2
112 , x_message_count OUT NOCOPY NUMBER
113 , x_message_data OUT NOCOPY VARCHAR2
114 , p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
115 , p_wms_task_type IN NUMBER DEFAULT NULL) --added for bug12831673
116 RETURN NUMBER
117 IS
118 l_tree_id INTEGER := NULL;
119 l_tree_mode NUMBER := NULL;
120 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
121 l_msg_data VARCHAR2(2000) := NULL;
122 l_msg_count NUMBER := NULL;
123 l_is_revision_control BOOLEAN := FALSE;
124 l_is_lot_control BOOLEAN := FALSE;
125 l_is_serial_control BOOLEAN := FALSE;
126 l_asset_sub_only BOOLEAN := FALSE;
127 l_include_suggestion BOOLEAN := TRUE;
128 l_expiration_date DATE := NULL;
129 l_onhand_source NUMBER := NULL;
130 l_qoh NUMBER := NULL;
131 l_rqoh NUMBER := NULL;
132 l_qr NUMBER := NULL;
133 l_qs NUMBER := NULL;
134 l_att NUMBER := NULL;
135 l_atr NUMBER := NULL;
136 l_sqoh NUMBER := NULL; -- invConv change
137 l_srqoh NUMBER := NULL; -- invConv change
138 l_sqr NUMBER := NULL; -- invConv change
139 l_sqs NUMBER := NULL; -- invConv change
140 l_satt NUMBER := NULL; -- invConv change
141 l_satr NUMBER := NULL; -- invConv change
142 l_available_quantity NUMBER := NULL;
143 l_available_quantity2 NUMBER := NULL; -- invConv change
144 l_onhand_quantity NUMBER := NULL;
145 l_onhand_quantity2 NUMBER := NULL; -- invConv change
146 l_available_conv_quantity NUMBER := NULL;
147 l_onhand_conv_quantity NUMBER := NULL;
148 l_primary_uom_code VARCHAR2(3) := NULL;
149
150 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
151 l_demand_header_type NUMBER;
152 BEGIN
153
154 IF (l_debug = 1) then
155 print_debug('New profile value INV_MATERIAL_STATUS ='||NVL(FND_PROFILE.VALUE('INV_MATERIAL_STATUS'),'x') );
156 print_debug('Create_tree Inputs Org:'||p_organization_id||'itm:'||p_inventory_item_id||
157 'RevCtrl:'||p_revision_control||'LotCtrl:'||p_lot_control||
158 'SerCtrl:'||p_serial_control||'LotAct:'||p_lot_active);
159 print_debug('DHdrId:'||p_demand_header_id||'DHdrTyp:'||p_demand_header_type||
160 'TMode:'||p_tree_mode||'NegInv:'||p_negative_inv_allowed||
161 'LExpDate:'||p_lot_expiration_date||'Act:'||p_activate||
162 'UOM:'||p_uom_code||'AssetOnly:'||p_asset_subinventory_only);
163 print_debug('DSName:'||p_demand_source_name||'DSLine:'||p_demand_source_line_id||
164 'DSDel:'||p_demand_source_delivery||'RevAct:'||p_rev_active);
165 END IF;
166
167 x_return_status := fnd_api.g_ret_sts_success;
168
169 -- bug 4104123 : I don't know why the default of p_demand_header_type doesnt work.
170 l_demand_header_type := p_demand_header_type;
171 if p_demand_header_type IS NULL
172 THEN
173 IF (l_debug = 1) then
174 print_debug('... p_demand_header_type IS NULL... reset to 0');
175 END IF;
176 l_demand_header_type := 0;
177 ELSE
178 IF (l_debug = 1) then
179 print_debug('... p_demand_header_type IS NOT NULL... ');
180 END IF;
181 END IF;
182
183 --start changes for bug7038890
184 -- l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;--??????????????
185 IF ((p_lpn_id IS NOT NULL) OR (p_wms_task_type IS NOT NULL)) THEN -- 12831673
186 l_tree_mode := 2;
187 ELSE
188 l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;
189 END IF;
190 --end changes for bug7038890
191
192 IF p_activate <> 1 THEN
193 x_available_quantity := 0;
194 x_onhand_quantity := 0;
195 RETURN 1;
196 END IF;
197
198 IF p_lot_control NOT IN (g_lot_control,g_no_lot_control) THEN
199 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_LOT_CTRL_OPTION');
200 FND_MSG_PUB.ADD;
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203
204 -- invConv removed this test : serial_control can have other values.
205 -- IF p_serial_control NOT IN (g_serial_control,g_no_serial_control) THEN
206 -- FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SER_CTRL_OPTION');
207 -- FND_MSG_PUB.ADD;
208 -- RAISE FND_API.G_EXC_ERROR;
209 -- END IF;
210
211 IF p_rev_active = g_no_rev_ctrl_please THEN
212 l_is_revision_control := FALSE;
213 ELSIF p_rev_active = g_want_rev_ctrl then
214 l_is_revision_control := TRUE;
215 ELSE
216 IF p_revision_control = g_no_rev_control THEN
217 l_is_revision_control := FALSE;
218 ELSE
219 l_is_revision_control := TRUE;
220 END IF;
221 END IF;
222
223 IF p_lot_active = g_no_lot_ctrl_please THEN
224 l_is_lot_control := FALSE;
225 ELSIF p_lot_active = g_want_lot_ctrl then
226 l_is_lot_control := TRUE;
227 ELSE
228 IF p_lot_control = g_no_lot_control THEN
229 l_is_lot_control := FALSE;
230 ELSE
231 l_is_lot_control := TRUE;
232 END IF;
233 END IF;
234
235 IF p_asset_subinventory_only = g_asset_subinvs THEN
236 l_asset_sub_only := TRUE;
237 ELSE
238 l_asset_sub_only := FALSE;
239 END IF;
240
241 l_onhand_source := inv_quantity_tree_pvt.g_all_subs;
242
243 l_expiration_date := NULL;--???????
244
245 IF p_serial_control = g_no_serial_control THEN
246 l_is_serial_control := FALSE;
247 ELSE
248 l_is_serial_control := TRUE;
249 END IF;
250
251 l_include_suggestion := TRUE;
252
253 IF (l_debug = 1) then
254 print_debug('Calling inv_quantity_tree_pvt.create_tree');
255 END IF;
256
257 inv_quantity_tree_pvt.create_tree
258 (
259 p_api_version_number => 1.0,
260 p_init_msg_lst => fnd_api.g_true,
261 x_return_status => l_return_status,
262 x_msg_count => l_msg_count,
263 x_msg_data => l_msg_data,
264 p_organization_id => p_organization_id,
265 p_inventory_item_id => p_inventory_item_id,
266 p_tree_mode => l_tree_mode,
267 p_is_revision_control => l_is_revision_control,
268 p_is_lot_control => l_is_lot_control,
269 p_is_serial_control => l_is_serial_control,
270 p_grade_code => p_grade_code, -- invConv change
271 p_asset_sub_only => l_asset_sub_only,
272 p_include_suggestion => l_include_suggestion,
273 p_demand_source_type_id => l_demand_header_type,
274 p_demand_source_header_id => p_demand_header_id,
275 p_demand_source_line_id => p_demand_source_line_id,
276 p_demand_source_name => p_demand_source_name,
277 p_demand_source_delivery => p_demand_source_delivery,
278 p_lot_expiration_date => l_expiration_date,
279 p_onhand_source => l_onhand_source,
280 x_tree_id => l_tree_id
281 );
282
283 IF l_return_status <> fnd_api.g_ret_sts_success THEN
284 IF (l_debug = 1) then
285 print_debug('Error from inv_quantity_tree_pvt.create_tree');
286 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
287 END IF;
288 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_CREATE_TREE');
289 FND_MSG_PUB.ADD;
290 RAISE fnd_api.g_exc_error;
291 END IF;
292
293 IF (l_debug = 1) THEN
294 print_debug('Tree_id:'||l_tree_id);
295 print_debug('Calling inv_quantity_tree_pvt.query_tree');
296 END IF;
297
298 inv_quantity_tree_pvt.query_tree
299 (
300 p_api_version_number => 1.0,
301 p_init_msg_lst => fnd_api.g_true,
302 x_return_status => l_return_status,
303 x_msg_count => l_msg_count,
304 x_msg_data => l_msg_data,
305 p_tree_id => l_tree_id,
306 p_revision => NULL,
307 p_lot_number => NULL,
308 p_subinventory_code => NULL,
309 p_locator_id => NULL,
310 x_qoh => l_qoh,
311 x_rqoh => l_rqoh,
312 x_qr => l_qr,
313 x_qs => l_qs,
314 x_att => l_att,
315 x_atr => l_atr,
316 x_sqoh => l_sqoh, -- invConv change
317 x_srqoh => l_srqoh, -- invConv change
318 x_sqr => l_sqr, -- invConv change
319 x_sqs => l_sqs, -- invConv change
320 x_satt => l_satt, -- invConv change
321 x_satr => l_satr -- invConv change
322 );
323
324 IF l_return_status <> fnd_api.g_ret_sts_success THEN
325 IF (l_debug = 1) then
326 print_debug('Error from inv_quantity_tree_pvt.query_tree');
327 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
328 END IF;
329 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_QUERY_TREE');
330 FND_MSG_PUB.ADD;
331 RAISE fnd_api.g_exc_error;
332 END IF;
333
334 IF (l_debug = 1) THEN
335 print_debug('Primary Qties : l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
336 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
337 print_debug('Secondary Qties : l_sqoh:'||l_sqoh||'l_srqoh:'||l_srqoh||'l_sqr:'||l_sqr||
338 'l_sqs:'||l_sqs||'l_satt:'||l_satt||'l_satr:'||l_satr);
339 END IF;
340
341 IF l_tree_mode IN (inv_quantity_tree_pvt.g_transaction_mode,
342 inv_quantity_tree_pvt.g_loose_only_mode) THEN
343 l_available_quantity := l_att;
344 l_available_quantity2 := l_satt; -- invConv change
345 ELSE
346 l_available_quantity := l_atr;
347 l_available_quantity2 := l_satr; -- invConv change
348 END IF;
349
350 l_onhand_quantity := l_qoh;
351 l_onhand_quantity2 := l_sqoh; -- invConv change
352
353 --UOM Conversion
354 IF p_uom_code IS NOT NULL THEN
355 BEGIN
356 SELECT Primary_Uom_Code
357 INTO l_primary_uom_code
358 FROM MTL_SYSTEM_ITEMS
359 WHERE Organization_Id = p_organization_id
360 AND Inventory_Item_Id = p_inventory_item_id;
361 EXCEPTION
362 WHEN no_data_found THEN
363 IF (l_debug = 1) THEN
364 print_debug('Primary UOM not found');
365 END IF;
366 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PRIMARY_UOM');
367 FND_MSG_PUB.ADD;
368 RAISE FND_API.G_EXC_ERROR;
369 END;
370
371 IF (l_debug = 1) THEN
372 print_debug('l_primary_uom_code'||l_primary_uom_code);
373 END IF;
374
375 IF p_uom_code <> l_primary_uom_code THEN
376 l_available_conv_quantity := inv_convert.inv_um_convert
377 (item_id => p_inventory_item_id,
378 precision => NULL,
379 from_quantity => Abs(l_available_quantity),
380 from_unit => l_primary_uom_code,
381 to_unit => p_uom_code,
382 from_name => null,
383 to_name => NULL
384 );
385 IF l_available_conv_quantity < 0 THEN
386 IF (l_debug = 1) THEN
387 print_debug('Error converting l_available_quantity');
388 END IF;
389 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
390 FND_MSG_PUB.ADD;
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 IF l_available_quantity < 0 THEN
395 l_available_quantity := 0 - l_available_conv_quantity;
396 ELSE
397 l_available_quantity := l_available_conv_quantity;
398 END IF;
399 IF (l_debug = 1) then
400 print_debug('1 conversion result: qty='||l_available_quantity||', convQ='||l_available_conv_quantity);
401 END IF;
402
403 l_onhand_conv_quantity := inv_convert.inv_um_convert
404 (item_id => p_inventory_item_id,
405 precision => NULL,
406 from_quantity => Abs(l_onhand_quantity),
407 from_unit => l_primary_uom_code,
408 to_unit => p_uom_code,
409 from_name => null,
410 to_name => NULL
411 );
412 IF l_onhand_conv_quantity < 0 THEN
413 IF (l_debug = 1) THEN
414 print_debug('Error converting l_onhand_quantity');
415 END IF;
416 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
417 FND_MSG_PUB.ADD;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 IF l_onhand_quantity < 0 THEN
422 l_onhand_quantity := 0 - l_onhand_conv_quantity;
423 ELSE
424 l_onhand_quantity := l_onhand_conv_quantity;
425 END IF;
426 IF (l_debug = 1) then
427 print_debug('2 conversion result: qty='||l_onhand_quantity||', convQ='||l_onhand_conv_quantity);
428 END IF;
429 END IF;
430 END IF;
431
432 x_available_quantity := ROUND(l_available_quantity, 5);
433 x_available_quantity2 := ROUND(l_available_quantity2, 5); -- invConv change
434 x_onhand_quantity := ROUND(l_onhand_quantity, 5);
435 x_onhand_quantity2 := ROUND(l_onhand_quantity2, 5); -- invConv change
436
437 IF (l_debug = 1) THEN
438 print_debug('returning x_return_status:'||x_return_status||
439 'x_available_quantity='||x_available_quantity||
440 'x_onhand_quantity='||x_onhand_quantity||
441 'x_available_quantity2='||x_available_quantity2||
442 'x_onhand_quantity2='||x_onhand_quantity2);
443 END IF;
444
445 RETURN 1;
446
447 EXCEPTION
448 WHEN fnd_api.g_exc_error THEN
449
450 IF (l_debug = 1) THEN
451 print_debug('fnd_api.g_exc_error');
452 END IF;
453
454 x_return_status := fnd_api.g_ret_sts_error;
455 x_available_quantity := 0;
456 x_onhand_quantity := 0;
457
458 fnd_msg_pub.count_and_get
459 ( p_count => x_message_count
460 , p_data => x_message_data
461 );
462
463 IF (l_debug = 1) THEN
464 FOR i IN 1 .. x_message_count LOOP
465 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
466 END LOOP;
467 END IF;
468
469 RETURN 0;
470
471 WHEN OTHERS THEN
472 IF (l_debug = 1) THEN
473 print_debug('OTHERS error');
474 END IF;
475 x_return_status := fnd_api.g_ret_sts_unexp_error ;
476
477 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
478 THEN
479 fnd_msg_pub.add_exc_msg
480 ( 'INV_QUANTITY_TREE_UE'
481 ,'CREATE_TREE'
482 );
483 END IF;
484
485 x_available_quantity := 0;
486 x_onhand_quantity := 0;
487
488 fnd_msg_pub.count_and_get
489 ( p_count => x_message_count
490 , p_data => x_message_data
491 );
492 IF (l_debug = 1) THEN
493 FOR i IN 1 .. x_message_count LOOP
494 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
495 END LOOP;
496 END IF;
497
498 RETURN 0;
499 END create_tree;
500
501 -- bug 4104123 : replace p_demand_header_type default NULL by 0
502 FUNCTION query_tree(p_organization_id IN NUMBER,
503 p_inventory_item_id IN NUMBER,
504 p_revision_control IN NUMBER DEFAULT 1,
505 p_lot_control IN NUMBER DEFAULT 1,
506 p_serial_control IN NUMBER DEFAULT 1,
507 p_demand_header_id IN NUMBER default NULL,
508 p_demand_header_type IN NUMBER,
509 p_revision in varchar2 default NULL,
510 p_lot in varchar2 default NULL,
511 p_lot_expiration_date IN DATE default NULL,
512 p_subinventory IN varchar2 default NULL,
513 p_locator in NUMBER default NULL,
514 p_transfer_subinventory VARCHAR2 default NULL,
515 p_transaction_quantity in NUMBER default 0,
516 p_uom_code in varchar2 default NULL,
517 P_lot_active IN NUMBER default 2,
518 P_activate IN NUMBER default 1,
519 P_tree_mode In NUMBER Default 3, --2 replaced by 3 for bug7038890
520 P_demand_source_name IN varchar2 default NULL,
521 P_demand_source_line_id IN NUMBER default NULL,
522 P_demand_source_delivery in NUMBER default NULL,
523 P_rev_active in NUMBER default 2,
524 X_available_onhand out NOCOPY NUMBER,
525 X_available_quantity out NOCOPY NUMBER,
526 X_onhand_quantity out NOCOPY NUMBER,
527 X_return_status OUT NOCOPY VARCHAR2,
528 X_message_count OUT NOCOPY NUMBER,
529 X_message_data Out NOCOPY VARCHAR2,
530 P_lpn_id IN NUMBER DEFAULT NULL, --added for bug7038890
531 P_wms_task_type IN NUMBER DEFAULT NULL, --added for bug12831673
532 P_transfer_locator IN VARCHAR2 DEFAULT NULL -- Bug 14516283
533 ) RETURN NUMBER IS
534
535 l_QTY NUMBER;
536 l_available_onhand2 NUMBER;
537 l_available_quantity2 NUMBER;
538 l_onhand_quantity2 NUMBER;
539 l_transaction_quantity2 NUMBER;
540 BEGIN
541 -- invConv change : Calling the overloaded query_tree :
542 l_QTY := query_tree( p_organization_id => p_organization_id
543 , p_inventory_item_id => p_inventory_item_id
544 , p_revision_control => p_revision_control
545 , p_lot_control => p_lot_control
546 , p_serial_control => p_serial_control
547 , p_demand_header_id => P_demand_header_id
548 , p_demand_header_type => p_demand_header_type
549 , p_revision => P_revision
550 , p_lot => P_lot
551 , p_lot_expiration_date => P_lot_expiration_date
552 , p_subinventory => P_subinventory
553 , p_locator => P_locator
554 , p_transfer_subinventory => P_transfer_subinventory
555 , p_transaction_quantity => P_transaction_quantity
556 , p_uom_code => P_uom_code
557 , p_transaction_quantity2 => l_transaction_quantity2 -- invConv change.
558 , p_lot_active => P_lot_active
559 , p_activate => P_activate
560 , p_tree_mode => P_tree_mode
561 , P_demand_source_name => P_demand_source_name
562 , P_demand_source_line_id => P_demand_source_line_id
563 , P_demand_source_delivery => P_demand_source_delivery
564 , P_rev_active => P_rev_active
565 , X_available_onhand => X_available_onhand
566 , X_available_quantity => X_available_quantity
567 , X_onhand_quantity => X_onhand_quantity
568 , X_available_onhand2 => l_available_onhand2 -- invConv change
569 , X_available_quantity2 => l_available_quantity2 -- invConv change
570 , X_onhand_quantity2 => l_onhand_quantity2 -- invConv change
571 , X_return_status => X_return_status
572 , X_message_count => X_message_count
573 , X_message_data => X_message_data
574 , P_lpn_id => P_lpn_id --added for bug7038890
575 , P_wms_task_type => P_wms_task_type
576 , P_transfer_locator => P_transfer_locator ); --added for bug12831673
577
578 RETURN l_QTY;
579 -- invConv changes end.
580 END query_tree;
581
582 -- invConv changes begin : overloaded query_tree
583 -- bug 4104123 : replace p_demand_header_type default NULL by 0
584 FUNCTION query_tree( p_organization_id IN NUMBER
585 , p_inventory_item_id IN NUMBER
586 , p_revision_control IN NUMBER DEFAULT 1
587 , p_lot_control IN NUMBER DEFAULT 1
588 , p_serial_control IN NUMBER DEFAULT 1
589 , P_demand_header_id IN NUMBER DEFAULT NULL
590 , p_demand_header_type IN NUMBER DEFAULT 0
591 , P_revision in VARCHAR2 DEFAULT NULL
592 , P_lot in VARCHAR2 DEFAULT NULL
593 , P_lot_expiration_date IN DATE DEFAULT NULL
594 , P_subinventory IN VARCHAR2 DEFAULT NULL
595 , P_locator in NUMBER DEFAULT NULL
596 , P_transfer_subinventory IN VARCHAR2 DEFAULT NULL
597 , P_transaction_quantity IN NUMBER DEFAULT 0
598 , P_uom_code IN VARCHAR2 DEFAULT NULL
599 , P_transaction_quantity2 IN NUMBER DEFAULT NULL -- invConv change.
600 , P_lot_active IN NUMBER DEFAULT 2
601 , P_activate IN NUMBER DEFAULT 1
602 , P_tree_mode IN NUMBER DEFAULT 3 --2 replaced by 3 for bug7038890
603 , P_demand_source_name IN VARCHAR2 DEFAULT NULL
604 , P_demand_source_line_id IN NUMBER DEFAULT NULL
605 , P_demand_source_delivery IN NUMBER DEFAULT NULL
606 , P_rev_active IN NUMBER DEFAULT 2
607 , X_available_onhand OUT NOCOPY NUMBER
608 , X_available_quantity OUT NOCOPY NUMBER
609 , X_onhand_quantity OUT NOCOPY NUMBER
610 , X_available_onhand2 OUT NOCOPY NUMBER -- invConv change
611 , X_available_quantity2 OUT NOCOPY NUMBER -- invConv change
612 , X_onhand_quantity2 OUT NOCOPY NUMBER -- invConv change
613 , X_return_status OUT NOCOPY VARCHAR2
614 , X_message_count OUT NOCOPY NUMBER
615 , X_message_data OUT NOCOPY VARCHAR2
616 , P_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
617 , P_wms_task_type IN NUMBER DEFAULT NULL --added for bug12831673
618 , P_transfer_locator IN VARCHAR2 DEFAULT NULL -- Bug 14516283
619 ) RETURN NUMBER
620 IS
621 l_tree_id INTEGER := NULL;
622 l_tree_mode NUMBER := NULL;
623 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
624 l_msg_data VARCHAR2(2000) := NULL;
625 l_msg_count NUMBER := NULL;
626 l_is_revision_control BOOLEAN := FALSE;
627 l_is_lot_control BOOLEAN := FALSE;
628 l_is_serial_control BOOLEAN := FALSE;
629 l_asset_sub_only BOOLEAN := FALSE;
630 l_include_suggestion BOOLEAN := TRUE;
631 l_expiration_date DATE := NULL;
632 l_onhand_source NUMBER := NULL;
633 l_qoh NUMBER := NULL;
634 l_rqoh NUMBER := NULL;
635 l_qr NUMBER := NULL;
636 l_qs NUMBER := NULL;
637 l_att NUMBER := NULL;
638 l_atr NUMBER := NULL;
639 l_sqoh NUMBER := NULL; -- invConv change
640 l_srqoh NUMBER := NULL; -- invConv change
641 l_sqr NUMBER := NULL; -- invConv change
642 l_sqs NUMBER := NULL; -- invConv change
643 l_satt NUMBER := NULL; -- invConv change
644 l_satr NUMBER := NULL; -- invConv change
645
646 l_available_quantity NUMBER := NULL;
647 l_onhand_quantity NUMBER := NULL;
648 l_avail_qoh NUMBER := NULL;
649 l_available_quantity2 NUMBER := NULL; -- invConv change
650 l_onhand_quantity2 NUMBER := NULL; -- invConv change
651 l_avail_qoh2 NUMBER := NULL; -- invConv change
652 l_available_conv_quantity NUMBER := NULL;
653 l_onhand_conv_quantity NUMBER := NULL;
654 l_avail_qoh_conv_quantity NUMBER := NULL;
655 l_original_avail_qoh NUMBER := NULL;
656 l_original_avail_qoh2 NUMBER := NULL; -- invConv change
657 l_locator_id NUMBER := NULL;
658 l_dyn_loc BOOLEAN := FALSE;
659 l_tqoh NUMBER := NULL;
660 l_stqoh NUMBER := NULL; -- invConv change
661 l_transaction_quantity NUMBER := NULL;
662 l_transaction_quantity2 NUMBER := NULL; -- invConv change
663 l_transaction_conv_quantity NUMBER := NULL;
664 l_primary_uom_code VARCHAR2(3) := NULL;
665 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
666 l_transfer_locator_id NUMBER := NULL; --Bug 14516283
667
668 l_lot_control NUMBER;
669 CURSOR get_item_details( org_id IN NUMBER
670 , item_id IN NUMBER) IS
671 SELECT NVL(lot_control_code, 1)
672 FROM mtl_system_items
673 WHERE inventory_item_id = item_id
674 AND organization_id = org_id;
675
676 l_demand_header_type NUMBER;
677 BEGIN
678
679 IF (l_debug = 1) then
680 print_debug('query_tree Inputs Org:'||p_organization_id||'itm:'||p_inventory_item_id||
681 'RevCtrl:'||p_revision_control||'LotCtrl:'||p_lot_control||
682 'SerCtrl:'||p_serial_control||'LotAct:'||p_lot_active);
683 print_debug('DHdrId:'||p_demand_header_id||'DHdrTyp:'||p_demand_header_type||
684 'TMode:'||p_tree_mode||'Rev:'||p_revision||'Lot:'||p_lot||
685 'LExpDate:'||p_lot_expiration_date||'Act:'||p_activate);
686 print_debug('UOM:'||p_uom_code||'sub:'||p_subinventory||'Loc:'||p_locator||
687 'XSub:'||p_transfer_subinventory||'TxnQty:'||p_transaction_quantity||
688 'DSName:'||p_demand_source_name||'DSLine:'||p_demand_source_line_id||
689 'DSDel:'||p_demand_source_delivery||'RevAct:'||p_rev_active);
690 END IF;
691
692 -- bug 4104123 : I don't know why the default of p_demand_header_type doesnt work.
693 l_demand_header_type := p_demand_header_type;
694 if p_demand_header_type IS NULL
695 THEN
696 IF (l_debug = 1) then
697 print_debug('... p_demand_header_type IS NULL... reset to 0');
698 END IF;
699 l_demand_header_type := 0;
700 ELSE
701 IF (l_debug = 1) then
702 print_debug('... p_demand_header_type IS NOT NULL... ');
703 END IF;
704 END IF;
705
706 x_return_status := fnd_api.g_ret_sts_success;
707
708 IF p_activate <> 1 THEN
709 x_available_quantity := 0;
710 x_available_onhand := 0;
711 x_onhand_quantity := 0;
712 RETURN 1;
713 END IF;
714
715 --start changes for bug7038890
716 -- l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;--??????????????
717 IF ((p_lpn_id IS NOT NULL) OR (p_wms_task_type IS NOT NULL)) THEN -- 12831673
718 l_tree_mode := 2;
719 else
720 l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;
721 END IF ;
722 --end changes for bug7038890
723
724 IF p_locator = -1 THEN
725 l_locator_id := NULL;
726 l_dyn_loc := TRUE;
727 ELSE
728 l_locator_id := p_locator;
729 END IF;
730
731 --Bug 14516283
732 IF p_transfer_locator = -1 THEN
733 l_transfer_locator_id := 0;
734 ELSE
735 l_transfer_locator_id :=p_transfer_locator;
736 END IF;
737
738 IF p_rev_active = g_no_rev_ctrl_please THEN
739 l_is_revision_control := FALSE;
740 ELSIF p_rev_active = g_want_rev_ctrl then
741 l_is_revision_control := TRUE;
742 ELSE
743 IF p_revision_control = g_no_rev_control THEN
744 l_is_revision_control := FALSE;
745 ELSE
746 l_is_revision_control := TRUE;
747 END IF;
748 END IF;
749
750 IF p_lot_active = g_no_lot_ctrl_please THEN
751 l_is_lot_control := FALSE;
752 ELSIF p_lot_active = g_want_lot_ctrl then
753 l_is_lot_control := TRUE;
754 ELSE
755 IF p_lot_control = g_no_lot_control THEN
756 l_is_lot_control := FALSE;
757 ELSE
758 l_is_lot_control := TRUE;
759 END IF;
760 END IF;
761 -- invConv changes begin :
762 -- Because of Material Status : Need to know whether the item is lot_control : MANDATORY.
763 IF (l_debug = 1) then
764 print_debug('... g_is_mat_status_used='||INV_QUANTITY_TREE_PVT.g_is_mat_status_used);
765 END IF;
766 IF (l_is_lot_control = FALSE
767 AND INV_QUANTITY_TREE_PVT.g_is_mat_status_used = 1)
768 THEN
769 -- Get Item Details:
770 OPEN get_item_details(p_organization_id, p_inventory_item_id);
771 FETCH get_item_details
772 INTO l_lot_control;
773
774 IF (get_item_details%NOTFOUND)
775 THEN
776 CLOSE get_item_details;
777 -- The item doesn't exist under this organization.
778 FND_MESSAGE.SET_NAME('INV', 'ITEM_NOTFOUND');
779 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inventory_item_id);
780 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID', p_organization_id);
781 FND_MSG_PUB.ADD;
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784 CLOSE get_item_details;
785 END IF;
786 -- invConv changes end.
787
788 --?????????????????????
789 l_asset_sub_only := FALSE;
790 --IF p_asset_subinventory_only = g_asset_subinvs THEN
791 -- l_asset_sub_only := TRUE;
792 -- ELSE
793 -- l_asset_sub_only := FALSE;
794 -- END IF;
795
796 l_onhand_source := inv_quantity_tree_pvt.g_all_subs;
797
798 l_expiration_date := NULL;--???????
799
800 IF p_serial_control = g_no_serial_control THEN
801 l_is_serial_control := FALSE;
802 ELSE
803 l_is_serial_control := TRUE;
804 END IF;
805
806 l_include_suggestion := TRUE;
807
808 IF (l_debug = 1) THEN
809 print_debug('calling inv_quantity_tree_pvt.find_rootinfo.');
810 END IF;
811
812 l_tree_id :=
813 inv_quantity_tree_pvt.find_rootinfo
814 (
815 x_return_status => l_return_status,
816 p_organization_id => p_Organization_id,
817 p_inventory_item_id => p_Inventory_item_id,
818 p_tree_mode => l_Tree_Mode,
819 p_is_revision_control => l_is_revision_control,
820 p_is_lot_control => l_is_lot_control,
821 p_is_serial_control => l_is_serial_control,
822 p_asset_sub_only => l_asset_sub_only,
823 p_include_suggestion => TRUE,
824 p_demand_source_type_id => l_demand_header_type,
825 p_demand_source_header_id => p_demand_header_id,
826 p_demand_source_line_id => p_demand_source_line_id,
827 p_demand_source_name => p_demand_source_name,
828 p_demand_source_delivery => p_demand_source_Delivery,
829 p_lot_expiration_date => NULL,
830 p_onhand_source => l_onhand_source
831 );
832
833 IF l_return_status <> fnd_api.g_ret_sts_success THEN
834 IF (l_debug = 1) then
835 print_debug('Error from inv_quantity_tree_pvt.find_rootinfo');
836 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
837 END IF;
838 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_FIND_ROOTINFO');
839 FND_MSG_PUB.ADD;
840 RAISE fnd_api.g_exc_error;
841 END IF;
842
843 IF (l_debug = 1) then
844 print_debug('After inv_quantity_tree_pvt.find_rootinfo tree_id:'||l_tree_id);
845 print_debug('calling inv_quantity_tree_pvt.query_tree');
846 END IF;
847
848
849 inv_quantity_tree_pvt.query_tree
850 (
851 p_api_version_number => 1.0,
852 p_init_msg_lst => fnd_api.g_true,
853 x_return_status => l_return_status,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data,
856 p_tree_id => l_tree_id,
857 p_revision => p_Revision,
858 p_lot_number => p_Lot,
859 p_subinventory_code => p_Subinventory,
860 p_locator_id => l_locator_id,
861 x_qoh => l_qoh,
862 x_rqoh => l_rqoh,
863 x_qr => l_qr,
864 x_qs => l_qs,
865 x_att => l_att,
866 x_atr => l_atr,
867 x_sqoh => l_sqoh, -- invConv change
868 x_srqoh => l_srqoh, -- invConv change
869 x_sqr => l_sqr, -- invConv change
870 x_sqs => l_sqs, -- invConv change
871 x_satt => l_satt, -- invConv change
872 x_satr => l_satr, -- invConv change
873 p_transfer_subinventory_code => p_Transfer_Subinventory,
874 p_lpn_id => p_lpn_id, --added for bug7038890
875 p_transfer_locator_id => l_transfer_locator_id --Bug 14516283
876 );
877
878 IF l_return_status <> fnd_api.g_ret_sts_success THEN
879 IF (l_debug = 1) then
880 print_debug('Error from inv_quantity_tree_pvt.query_tree');
881 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
882 END IF;
883 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_QUERY_TREE');
884 FND_MSG_PUB.ADD;
885 RAISE fnd_api.g_exc_error;
886 END IF;
887
888 IF (l_debug = 1) THEN
889 print_debug('Primaries : l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
890 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
891 print_debug('Secondaries : l_sqoh:'||l_sqoh||'l_srqoh:'||l_srqoh||'l_sqr:'||l_sqr||
892 'l_sqs:'||l_sqs||'l_satt:'||l_satt||'l_satr:'||l_satr);
893 print_debug('Calling inv_quantity_tree_pvt.get_total_qoh');
894 END IF;
895
896 IF p_tree_mode IN (inv_quantity_tree_pvt.g_transaction_mode,
897 inv_quantity_tree_pvt.g_loose_only_mode) THEN
898 l_available_quantity := l_att;
899 l_available_quantity2 := l_satt; -- invConv change
900 ELSE
901 l_available_quantity := l_atr;
902 l_available_quantity2 := l_satr; -- invConv change
903 END IF;
904
905 l_onhand_quantity := l_qoh;
906 l_onhand_quantity2 := l_sqoh; -- invConv change
907 l_original_avail_qoh := l_onhand_quantity;
908 l_original_avail_qoh2 := l_onhand_quantity2; -- invConv change
909 IF (l_debug = 1) then
910 print_debug(' odab l_original_avail_qoh='||l_original_avail_qoh||', l_original_avail_qoh2='||l_original_avail_qoh2);
911 END IF;
912
913 inv_quantity_tree_pvt.get_total_qoh
914 (x_return_status => l_return_status,
915 x_msg_count => l_msg_count,
916 x_msg_data => l_msg_data,
917 p_tree_id => l_tree_id,
918 p_revision => p_Revision,
919 p_lot_number => p_Lot,
920 p_subinventory_code => p_Subinventory,
921 p_locator_id => l_locator_id,
922 x_tqoh => l_tqoh,
923 x_stqoh => l_stqoh -- invConv change
924 );
925
926 IF l_return_status <> fnd_api.g_ret_sts_success THEN
927 IF (l_debug = 1) then
928 print_debug('Error from inv_quantity_tree_pvt.get_total_qoh');
929 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
930 END IF;
931 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_TOTAL_QOH');
932 FND_MSG_PUB.ADD;
933 RAISE fnd_api.g_exc_error;
934 END IF;
935
936 IF (l_debug = 1) THEN
937 print_debug('l_tqoh:'||l_tqoh||', l_stqoh='||l_stqoh);
938 END IF;
939
940
941 l_avail_qoh := l_tqoh;
942 l_avail_qoh2 := l_stqoh; -- invConv change
943
944 IF p_uom_code IS NOT NULL THEN
945 BEGIN
946 SELECT Primary_Uom_Code
947 INTO l_primary_uom_code
948 FROM MTL_SYSTEM_ITEMS
949 WHERE Organization_Id = p_organization_id
950 AND Inventory_Item_Id = p_inventory_item_id;
951 EXCEPTION
952 WHEN no_data_found THEN
953 IF (l_debug = 1) then
954 print_debug('Cannot Find primary UOM');
955 END IF;
956 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PRIMARY_UOM');
957 FND_MSG_PUB.ADD;
958 RAISE FND_API.G_EXC_ERROR;
959 END;
960 END IF;
961
962 IF (l_debug = 1) then
963 print_debug('odab transaction_qty='||p_transaction_quantity||', transaction_qty2='||p_transaction_quantity2||'.');
964 END IF;
965 l_transaction_quantity := p_transaction_quantity;
966 l_transaction_quantity2 := NVL(p_transaction_quantity2, 0);
967
968 IF l_transaction_quantity <> 0 AND
969 p_uom_code <> l_primary_uom_code THEN
970 l_transaction_conv_quantity := inv_convert.inv_um_convert
971 (item_id => p_inventory_item_id,
972 organization_id => p_organization_id, /*Bug#10035564 */
973 lot_number => P_lot,
974 precision => NULL,
975 from_quantity => Abs(l_transaction_quantity),
976 from_unit => p_uom_code,
977 to_unit => l_primary_uom_code,
978 from_name => null,
979 to_name => NULL
980 );
981 IF l_transaction_conv_quantity < 0 THEN
982 IF (l_debug = 1) THEN
983 print_debug('Error converting l_transaction_quantity');
984 END IF;
985 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
986 FND_MSG_PUB.ADD;
987 RAISE FND_API.G_EXC_ERROR;
988 END IF;
989
990 -- Bug 4094112 : Added the ELSE clause to the test to convert the QTY when >0
991 IF l_transaction_quantity < 0 THEN
992 l_transaction_quantity := l_transaction_conv_quantity * (-1);
993 ELSE
994 l_transaction_quantity := l_transaction_conv_quantity;
995 END IF;
996
997 IF (l_debug = 1) THEN
998 print_debug('3 conversion result: qty='||l_transaction_quantity||', convQ='||l_transaction_conv_quantity);
999 END IF;
1000 END IF; -- IF l_transaction_quantity <> 0
1001
1002 --??????????????????
1003 IF l_dyn_loc THEN
1004 IF l_available_quantity > 0 THEN
1005 l_available_quantity := 0;
1006 l_available_quantity2 := 0; -- invConv change
1007 l_avail_qoh := 0;
1008 l_avail_qoh2 := 0; -- invConv change
1009 ELSE
1010 l_available_quantity := l_available_quantity;
1011 l_available_quantity2 := l_available_quantity2; -- invConv change
1012 l_avail_qoh := l_available_quantity;
1013 l_avail_qoh2 := l_available_quantity2; -- invConv change
1014 END IF;
1015 END IF;
1016 --????????????
1017
1018 l_avail_qoh := l_avail_qoh - l_transaction_quantity;
1019 l_avail_qoh2 := l_avail_qoh2 - l_transaction_quantity2; -- invConv change
1020 l_available_quantity := l_available_quantity - l_transaction_quantity;
1021 l_available_quantity2 := l_available_quantity2 - l_transaction_quantity2; -- invConv change
1022
1023 IF l_dyn_loc THEN
1024 l_transaction_quantity := 0 - l_transaction_quantity;
1025 l_transaction_quantity2 := 0 - l_transaction_quantity2; -- invConv change
1026 else
1027 l_transaction_quantity := l_original_avail_qoh - l_transaction_quantity;
1028 l_transaction_quantity2 := l_original_avail_qoh2 - l_transaction_quantity2; -- invConv change
1029 END IF;
1030
1031 IF p_uom_code <> l_primary_uom_code THEN
1032
1033 l_available_conv_quantity := inv_convert.inv_um_convert
1034 (item_id => p_inventory_item_id,
1035 organization_id => p_organization_id, /*Bug#10035564 */
1036 lot_number => P_lot,
1037 precision => NULL,
1038 from_quantity => Abs(l_available_quantity),
1039 from_unit => l_primary_uom_code,
1040 to_unit => p_uom_code,
1041 from_name => null,
1042 to_name => NULL
1043 );
1044 IF l_available_conv_quantity < 0 THEN
1045 IF (l_debug = 1) THEN
1046 print_debug('Error converting l_available_quantity');
1047 END IF;
1048 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1049 FND_MSG_PUB.ADD;
1050 RAISE FND_API.G_EXC_ERROR;
1051 END IF;
1052
1053 IF l_available_quantity < 0 THEN
1054 l_available_quantity := l_available_conv_quantity * (-1);
1055 ELSE
1056 l_available_quantity := l_available_conv_quantity;
1057 END IF;
1058 IF (l_debug = 1) then
1059 print_debug('4 conversion result: qty='||l_available_quantity||', convQ='||l_available_conv_quantity);
1060 END IF;
1061
1062 l_avail_qoh_conv_quantity := inv_convert.inv_um_convert
1063 (item_id => p_inventory_item_id,
1064 organization_id => p_organization_id, /*Bug#10035564 */
1065 lot_number => P_lot,
1066 precision => NULL,
1067 from_quantity => Abs(l_avail_qoh),
1068 from_unit => l_primary_uom_code,
1069 to_unit => p_uom_code,
1070 from_name => null,
1071 to_name => NULL
1072 );
1073 IF l_avail_qoh_conv_quantity < 0 THEN
1074 IF (l_debug = 1) THEN
1075 print_debug('Error converting l_avail_qoh');
1076 END IF;
1077 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1078 FND_MSG_PUB.ADD;
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081
1082 IF l_avail_qoh < 0 THEN
1083 l_avail_qoh := l_avail_qoh_conv_quantity * (-1);
1084 ELSE
1085 l_avail_qoh := l_avail_qoh_conv_quantity;
1086 END IF;
1087 IF (l_debug = 1) then
1088 print_debug('5 conversion result: qty='||l_avail_qoh||', convQ='||l_avail_qoh_conv_quantity);
1089 END IF;
1090
1091 l_transaction_conv_quantity := inv_convert.inv_um_convert
1092 (item_id => p_inventory_item_id,
1093 organization_id => p_organization_id, /*Bug#10035564 */
1094 lot_number => P_lot,
1095 precision => NULL,
1096 from_quantity => Abs(l_transaction_quantity),
1097 from_unit => l_primary_uom_code,
1098 to_unit => p_uom_code,
1099 from_name => null,
1100 to_name => NULL
1101 );
1102 IF l_transaction_conv_quantity < 0 THEN
1103 IF (l_debug = 1) THEN
1104 print_debug('Error converting l_transaction_quantity');
1105 END IF;
1106 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1107 FND_MSG_PUB.ADD;
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110
1111 IF l_transaction_quantity < 0 THEN
1112 l_transaction_quantity := l_transaction_conv_quantity * (-1);
1113 ELSE
1114 l_transaction_quantity := l_transaction_conv_quantity;
1115 END IF;
1116 IF (l_debug = 1) then
1117 print_debug('6 conversion result: qty='||l_transaction_quantity||', convQ='||l_transaction_conv_quantity);
1118 END IF;
1119 END IF; -- IF p_uom_code <> l_primary_uom_code THEN
1120
1121 x_available_quantity := ROUND(l_available_quantity, 5);
1122 x_available_onhand := ROUND(l_avail_qoh, 5);
1123 x_onhand_quantity := ROUND(l_transaction_quantity, 5);
1124 x_available_quantity2 := ROUND(l_available_quantity2, 5);
1125 x_available_onhand2 := ROUND(l_avail_qoh2, 5);
1126 x_onhand_quantity2 := ROUND(l_transaction_quantity2, 5);
1127
1128
1129 IF (l_debug = 1) THEN
1130 print_debug('returning x_return_status:'||x_return_status||
1131 'x_available_quantity:'||x_available_quantity||
1132 'x_available_onhand :'||x_available_onhand||
1133 'x_onhand_quantity:'||x_onhand_quantity);
1134 print_debug('Secondaries : '||
1135 'x_available_quantity2='||x_available_quantity2||
1136 'x_available_onhand2='||x_available_onhand2||
1137 'x_onhand_quantity2='||x_onhand_quantity2);
1138 END IF;
1139 --?????????????????
1140 --Investigate why the l_transaction_quantity is used for onhand_quantity
1141 IF (l_debug = 1) then
1142 print_debug(' odab returning 1');
1143 END IF;
1144 RETURN 1;
1145 EXCEPTION
1146 WHEN fnd_api.g_exc_error THEN
1147 IF (l_debug = 1) THEN
1148 print_debug('fnd_api.g_exc_error');
1149 END IF;
1150 x_return_status := fnd_api.g_ret_sts_error;
1151 x_available_quantity := 0;
1152 x_available_onhand := 0;
1153 x_onhand_quantity := 0;
1154
1155 fnd_msg_pub.count_and_get
1156 ( p_count => x_message_count
1157 , p_data => x_message_data
1158 );
1159
1160 IF (l_debug = 1) THEN
1161 FOR i IN 1 .. x_message_count LOOP
1162 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
1163 END LOOP;
1164 END IF;
1165 RETURN 0;
1166 WHEN OTHERS THEN
1167 IF (l_debug = 1) THEN
1168 print_debug('Others error');
1169 END IF;
1170 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1171
1172 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1173 THEN
1174 fnd_msg_pub.add_exc_msg
1175 ( 'INV_QUANTITY_TREE_UE'
1176 ,'QUERY_TREE'
1177 );
1178 END IF;
1179
1180 x_available_quantity := 0;
1181 x_available_onhand := 0;
1182 x_onhand_quantity := 0;
1183
1184 fnd_msg_pub.count_and_get
1185 ( p_count => x_message_count
1186 , p_data => x_message_data
1187 );
1188 IF (l_debug = 1) THEN
1189 FOR i IN 1 .. x_message_count LOOP
1190 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
1191 END LOOP;
1192 END IF;
1193 RETURN 0;
1194 END query_tree;
1195
1196 -- bug 4104123 : replace p_demand_header_type default NULL by 0
1197 FUNCTION xact_qty(p_organization_id IN NUMBER,
1198 p_inventory_item_id IN NUMBER,
1199 p_demand_header_id IN NUMBER default NULL,
1200 p_demand_header_type IN NUMBER,
1201 p_revision_control IN NUMBER default 1,
1202 p_lot_control IN NUMBER default 1,
1203 p_serial_control IN NUMBER default 1,
1204 p_revision in varchar2 default NULL,
1205 p_lot in varchar2 default NULL,
1206 p_lot_expiration_date IN DATE default NULL,
1207 p_subinventory IN varchar2 default NULL,
1208 p_locator in NUMBER default NULL,
1209 p_xact_mode In NUMBER Default 2,
1210 p_transfer_subinventory IN VARCHAR2 default NULL,
1211 p_transfer_locator in NUMBER default NULL,
1212 p_transaction_quantity in NUMBER default NULL,
1213 p_uom_code in varchar2 default NULL,
1214 p_lot_active IN NUMBER default 2,
1215 p_activate IN NUMBER default 1,
1216 p_demand_source_name IN varchar2 default NULL,
1217 p_demand_source_line_id IN NUMBER default NULL,
1218 p_demand_source_delivery in NUMBER default NULL,
1219 p_rev_active in NUMBER default 2,
1220 x_available_onhand out NOCOPY NUMBER,
1221 x_available_quantity out NOCOPY NUMBER,
1222 x_onhand_quantity out NOCOPY NUMBER,
1223 x_return_status OUT NOCOPY VARCHAR2,
1224 x_message_count OUT NOCOPY NUMBER,
1225 x_message_data Out NOCOPY VARCHAR2,
1226 p_tree_mode IN NUMBER DEFAULT 3, --added for bug7038890
1227 p_lpn_id IN NUMBER DEFAULT NULL, --added for bug7038890
1228 p_wms_task_type IN NUMBER DEFAULT NULL --added for bug12831673
1229 ) RETURN NUMBER
1230 IS
1231
1232 l_QTY NUMBER := NULL;
1233 l_transaction_quantity2 NUMBER := NULL;
1234 l_available_onhand2 NUMBER := NULL;
1235 l_available_quantity2 NUMBER := NULL;
1236 l_onhand_quantity2 NUMBER := NULL;
1237 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1238
1239 BEGIN
1240 -- invConv change : callng the new signature xact_qty :
1241 IF (l_debug = 1) then
1242 print_debug(' in old call of xact_qty...');
1243 END IF;
1244 l_QTY := xact_qty( P_organization_id => P_organization_id
1245 , P_inventory_item_id => P_inventory_item_id
1246 , P_demand_header_id => P_demand_header_id
1247 , p_demand_header_type => p_demand_header_type
1248 , P_revision_control => P_revision_control
1249 , P_lot_control => P_lot_control
1250 , P_serial_control => P_serial_control
1251 , P_revision => P_revision
1252 , P_lot => P_lot
1253 , P_lot_expiration_date => P_lot_expiration_date
1254 , P_subinventory => P_subinventory
1255 , P_locator => P_locator
1256 , P_xact_mode => P_xact_mode
1257 , P_transfer_subinventory => P_transfer_subinventory
1258 , P_transfer_locator => P_transfer_locator
1259 , P_transaction_quantity => P_transaction_quantity
1260 , P_uom_code => P_uom_code
1261 , P_transaction_quantity2 => l_transaction_quantity2
1262 , P_lot_active => P_lot_active
1263 , P_activate => P_activate
1264 , P_demand_source_name => P_demand_source_name
1265 , P_demand_source_line_id => P_demand_source_line_id
1266 , P_demand_source_delivery => P_demand_source_delivery
1267 , P_rev_active => P_rev_active
1268 , X_available_onhand => X_available_onhand
1269 , X_available_quantity => X_available_quantity
1270 , X_onhand_quantity => X_onhand_quantity
1271 , X_available_onhand2 => l_available_onhand2
1272 , X_available_quantity2 => l_available_quantity2
1273 , X_onhand_quantity2 => l_onhand_quantity2
1274 , X_return_status => X_return_status
1275 , X_message_count => X_message_count
1276 , X_message_data => X_message_data
1277 , P_tree_mode => p_tree_mode --added for bug7038890
1278 , P_lpn_id => p_lpn_id --added for bug7038890
1279 , P_wms_task_type => p_wms_task_type --added for bug12831673
1280 );
1281
1282 RETURN l_QTY;
1283 -- invConv changes end.
1284
1285
1286 END xact_qty;
1287
1288 -- invConv changes begin :Overloaded version of xact_qty :
1289 -- bug 4104123 : replace p_demand_header_type default NULL by 0
1290 FUNCTION xact_qty( P_organization_id IN NUMBER
1291 , P_inventory_item_id IN NUMBER
1292 , P_demand_header_id IN NUMBER DEFAULT NULL
1293 , p_demand_header_type IN NUMBER DEFAULT 0
1294 , P_revision_control IN NUMBER DEFAULT 1
1295 , P_lot_control IN NUMBER DEFAULT 1
1296 , P_serial_control IN NUMBER DEFAULT 1
1297 , P_revision IN VARCHAR2 DEFAULT NULL
1298 , P_lot IN VARCHAR2 DEFAULT NULL
1299 , P_lot_expiration_date IN DATE DEFAULT NULL
1300 , P_subinventory IN VARCHAR2 DEFAULT NULL
1301 , P_locator IN NUMBER DEFAULT NULL
1302 , P_xact_mode IN NUMBER DEFAULT 2
1303 , P_transfer_subinventory IN VARCHAR2 DEFAULT NULL
1304 , P_transfer_locator IN NUMBER DEFAULT NULL
1305 , P_transaction_quantity IN NUMBER DEFAULT NULL
1306 , P_uom_code IN VARCHAR2 DEFAULT NULL
1307 , P_transaction_quantity2 IN NUMBER DEFAULT NULL
1308 , P_lot_active IN NUMBER DEFAULT 2
1309 , P_activate IN NUMBER DEFAULT 1
1310 , P_demand_source_name IN VARCHAR2 DEFAULT NULL
1311 , P_demand_source_line_id IN NUMBER DEFAULT NULL
1312 , P_demand_source_delivery IN NUMBER DEFAULT NULL
1313 , P_rev_active IN NUMBER DEFAULT 2
1314 , X_available_onhand OUT NOCOPY NUMBER
1315 , X_available_quantity OUT NOCOPY NUMBER
1316 , X_onhand_quantity OUT NOCOPY NUMBER
1317 , X_available_onhand2 OUT NOCOPY NUMBER
1318 , X_available_quantity2 OUT NOCOPY NUMBER
1319 , X_onhand_quantity2 OUT NOCOPY NUMBER
1320 , X_return_status OUT NOCOPY VARCHAR2
1321 , X_message_count OUT NOCOPY NUMBER
1322 , X_message_data OUT NOCOPY VARCHAR2
1323 , P_tree_mode IN NUMBER DEFAULT 3 --added for bug7038890
1324 , P_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
1325 , P_wms_task_type IN NUMBER DEFAULT NULL --added for bug12831673
1326 ) RETURN NUMBER
1327 IS
1328
1329 l_tree_id INTEGER := NULL;
1330 l_tree_mode NUMBER := NULL;
1331 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1332 l_msg_data VARCHAR2(2000) := NULL;
1333 l_msg_count NUMBER := NULL;
1334 l_is_revision_control BOOLEAN := FALSE;
1335 l_is_lot_control BOOLEAN := FALSE;
1336 l_is_serial_control BOOLEAN := FALSE;
1337 l_asset_sub_only BOOLEAN := FALSE;
1338 l_include_suggestion BOOLEAN := TRUE;
1339 l_expiration_date DATE := NULL;
1340 l_onhand_source NUMBER := NULL;
1341 l_qoh NUMBER := NULL;
1342 l_rqoh NUMBER := NULL;
1343 l_qr NUMBER := NULL;
1344 l_qs NUMBER := NULL;
1345 l_att NUMBER := NULL;
1346 l_atr NUMBER := NULL;
1347 l_sqoh NUMBER := NULL; -- invConv change
1348 l_srqoh NUMBER := NULL; -- invConv change
1349 l_sqr NUMBER := NULL; -- invConv change
1350 l_sqs NUMBER := NULL; -- invConv change
1351 l_satt NUMBER := NULL; -- invConv change
1352 l_satr NUMBER := NULL; -- invConv change
1353
1354 l_available_quantity NUMBER := NULL;
1355 l_available_quantity2 NUMBER := NULL; -- invconv change
1356 l_onhand_quantity NUMBER := NULL;
1357 l_onhand_quantity2 NUMBER := NULL; -- invconv change
1358 l_avail_qoh NUMBER := NULL;
1359 l_avail_qoh2 NUMBER := NULL; -- invconv change
1360 l_available_conv_quantity NUMBER := NULL;
1361 l_onhand_conv_quantity NUMBER := NULL;
1362 l_avail_qoh_conv_quantity NUMBER := NULL;
1363 l_original_avail_qoh NUMBER := NULL;
1364 l_locator_id NUMBER := NULL;
1365 l_transfer_locator_id NUMBER := NULL;
1366 l_dyn_loc BOOLEAN := FALSE;
1367 l_tqoh NUMBER := NULL;
1368 l_stqoh NUMBER := NULL; -- invConv change
1369 l_transaction_quantity NUMBER := NULL;
1370 l_transaction_quantity2 NUMBER := NULL; -- invConv change
1371 l_transaction_conv_quantity NUMBER := NULL;
1372 l_primary_uom_code VARCHAR2(3) := NULL;
1373 l_xact_mode NUMBER := NULL;
1374 l_process_type NUMBER := NULL;
1375 l_temp_trx_quantity NUMBER := NULL;
1376 l_temp_trx_quantity2 NUMBER := NULL;
1377
1378 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1379
1380 l_lot_control NUMBER;
1381 CURSOR get_item_details( org_id IN NUMBER
1382 , item_id IN NUMBER) IS
1383 SELECT NVL(lot_control_code, 1)
1384 FROM mtl_system_items
1385 WHERE inventory_item_id = item_id
1386 AND organization_id = org_id;
1387
1388 l_demand_header_type NUMBER;
1389 BEGIN
1390 x_return_status := fnd_api.g_ret_sts_success;
1391
1392 IF (l_debug = 1) then
1393 print_debug('Xact_qty Inputs Org='||p_organization_id||' itm='||p_inventory_item_id||
1394 ' RevCtrl='||p_revision_control||' LotCtrl='||p_lot_control||
1395 ' SerCtrl='||p_serial_control||' LotAct='||p_lot_active);
1396 print_debug(' DHdrId='||p_demand_header_id||' DHdrTyp='||p_demand_header_type||
1397 ' XactMode='||p_Xact_mode||' Rev='||p_revision||' Lot='||p_lot||
1398 ' LExpDate='||p_lot_expiration_date||' Act='||p_activate);
1399 print_debug(' UOM='||p_uom_code||' sub='||p_subinventory||' Loc='||p_locator||
1400 ' XSub='||p_transfer_subinventory||' Xloc='||p_transfer_locator||
1401 ' TxnQty='||p_transaction_quantity||' '||P_uom_code||' TxnQty2='||p_transaction_quantity2||
1402 ' DSName='||p_demand_source_name||
1403 ' DSLine='||p_demand_source_line_id||
1404 ' DSDel='||p_demand_source_delivery||' RevAct:'||p_rev_active);
1405 END IF;
1406
1407 -- bug 4104123 : I don't know why the default of p_demand_header_type doesnt work.
1408 l_demand_header_type := p_demand_header_type;
1409 if p_demand_header_type IS NULL
1410 THEN
1411 IF (l_debug = 1) then
1412 print_debug('... p_demand_header_type IS NULL... reset to 0');
1413 END IF;
1414 l_demand_header_type := 0;
1415 ELSE
1416 IF (l_debug = 1) then
1417 print_debug('... p_demand_header_type IS NOT NULL... ');
1418 END IF;
1419 END IF;
1420
1421 IF p_activate <> 1 THEN
1422 x_available_quantity := 0;
1423 x_available_onhand := 0;
1424 x_onhand_quantity := 0;
1425 IF p_transaction_quantity2 IS NOT NULL
1426 THEN
1427 x_available_onhand2 := 0;
1428 x_available_quantity2 := 0;
1429 x_onhand_quantity2 := 0;
1430 END IF;
1431 RETURN 1;
1432 END IF;
1433
1434 --start changes for bug7038890
1435 --l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;--??????????????
1436 IF ((p_lpn_id IS NOT NULL) OR (p_wms_task_type IS NOT NULL)) THEN -- 12831673
1437 l_tree_mode :=2;
1438 ELSE
1439 l_tree_mode := inv_quantity_tree_pvt.g_loose_only_mode;
1440 END IF;
1441 --end changes for bug7038890
1442
1443 IF p_rev_active = g_no_rev_ctrl_please THEN
1444 l_is_revision_control := FALSE;
1445 ELSIF p_rev_active = g_want_rev_ctrl then
1446 l_is_revision_control := TRUE;
1447 ELSE
1448 IF p_revision_control = g_no_rev_control THEN
1449 l_is_revision_control := FALSE;
1450 ELSE
1451 l_is_revision_control := TRUE;
1452 END IF;
1453 END IF;
1454
1455 IF p_lot_active = g_no_lot_ctrl_please THEN
1456 l_is_lot_control := FALSE;
1457 ELSIF p_lot_active = g_want_lot_ctrl then
1458 l_is_lot_control := TRUE;
1459 ELSE
1460 IF p_lot_control = g_no_lot_control THEN
1461 l_is_lot_control := FALSE;
1462 ELSE
1463 l_is_lot_control := TRUE;
1464 END IF;
1465 END IF;
1466 -- invConv changes begin :
1467 -- Because of Material Status : Need to know whether the item is lot_control : MANDATORY.
1468 IF (l_debug = 1) then
1469 print_debug('+++ g_is_mat_status_used='||INV_QUANTITY_TREE_PVT.g_is_mat_status_used);
1470 END IF;
1471 IF (l_is_lot_control = FALSE
1472 AND INV_QUANTITY_TREE_PVT.g_is_mat_status_used = 1)
1473 THEN
1474 -- Get Item Details:
1475 OPEN get_item_details(p_organization_id, p_inventory_item_id);
1476 FETCH get_item_details
1477 INTO l_lot_control;
1478
1479 IF (get_item_details%NOTFOUND)
1480 THEN
1481 CLOSE get_item_details;
1482 -- The item doesn't exist under this organization.
1483 FND_MESSAGE.SET_NAME('INV', 'ITEM_NOTFOUND');
1484 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', p_inventory_item_id);
1485 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID', p_organization_id);
1486 FND_MSG_PUB.ADD;
1487 RAISE FND_API.G_EXC_ERROR;
1488 END IF;
1489 CLOSE get_item_details;
1490 END IF;
1491 -- invConv changes end.
1492
1493 IF p_locator = -1 THEN
1494 l_locator_id := 0;
1495 ELSE
1496 l_locator_id := p_locator;
1497 END IF;
1498
1499 IF p_transfer_locator = -1 THEN
1500 l_transfer_locator_id := 0;
1501 ELSE
1502 l_transfer_locator_id :=p_transfer_locator;
1503 END IF;
1504
1505 if (p_Xact_Mode = g_TRX_TEMP) THEN
1506 l_Xact_Mode := g_ONHAND;
1507 ELSE
1508 l_Xact_Mode := p_Xact_Mode;
1509 END IF;
1510
1511 IF p_uom_code IS NOT NULL THEN
1512 BEGIN
1513 SELECT Primary_Uom_Code
1514 INTO l_primary_uom_code
1515 FROM MTL_SYSTEM_ITEMS
1516 WHERE Organization_Id = p_organization_id
1517 AND Inventory_Item_Id = p_inventory_item_id;
1518 EXCEPTION
1519 WHEN no_data_found THEN
1520 IF (l_debug = 1) then
1521 print_debug('Cannot Find primary UOM');
1522 END IF;
1523 FND_MESSAGE.SET_NAME('INV', 'INV_NO_PRIMARY_UOM');
1524 FND_MSG_PUB.ADD;
1525 RAISE FND_API.G_EXC_ERROR;
1526 END;
1527 END IF;
1528
1529
1530 l_transaction_quantity := p_transaction_quantity;
1531 l_transaction_quantity2 := p_transaction_quantity2;
1532
1533 IF l_transaction_quantity <> 0 AND
1534 p_uom_code <> l_primary_uom_code THEN
1535 l_transaction_conv_quantity := inv_convert.inv_um_convert
1536 (item_id => p_inventory_item_id,
1537 organization_id => p_organization_id, /*Bug#10035564 */
1538 lot_number => P_lot,
1539 precision => NULL,
1540 from_quantity => Abs(l_transaction_quantity),
1541 from_unit => p_uom_code,
1542 to_unit => l_primary_uom_code,
1543 from_name => null,
1544 to_name => NULL
1545 );
1546 IF l_transaction_conv_quantity < 0 THEN
1547 IF (l_debug = 1) THEN
1548 print_debug('Error converting l_transaction_quantity');
1549 END IF;
1550 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1551 FND_MSG_PUB.ADD;
1552 RAISE FND_API.G_EXC_ERROR;
1553 END IF;
1554
1555 -- Bug 4094112 : Added the ELSE clause in the test, in order to convert QTY when >0
1556 IF l_transaction_quantity < 0 THEN
1557 l_transaction_quantity := l_transaction_conv_quantity * (-1);
1558 ELSE
1559 l_transaction_quantity := l_transaction_conv_quantity;
1560 END IF;
1561
1562 IF (l_debug = 1) THEN
1563 print_debug('7 conversion result: qty='||l_transaction_quantity||', convQ='||l_transaction_conv_quantity);
1564 END IF;
1565 END IF;
1566
1567 --?????????????????????
1568 l_asset_sub_only := FALSE;
1569 --IF p_asset_subinventory_only = g_asset_subinvs THEN
1570 -- l_asset_sub_only := TRUE;
1571 -- ELSE
1572 -- l_asset_sub_only := FALSE;
1573 -- END IF;
1574
1575 l_onhand_source := inv_quantity_tree_pvt.g_all_subs;
1576
1577 l_expiration_date := NULL;--???????
1578
1579 IF p_serial_control = g_no_serial_control THEN
1580 l_is_serial_control := FALSE;
1581 ELSE
1582 l_is_serial_control := TRUE;
1583 END IF;
1584
1585 l_include_suggestion := TRUE;
1586
1587 IF (l_debug = 1) THEN
1588 print_debug('calling inv_quantity_tree_pvt.find_rootinfo');
1589 END IF;
1590
1591 l_tree_id :=
1592 inv_quantity_tree_pvt.find_rootinfo
1593 ( x_return_status => l_return_status,
1594 p_organization_id => p_organization_id,
1595 p_inventory_item_id => p_inventory_item_id,
1596 p_tree_mode => l_tree_Mode,
1597 p_is_revision_control => l_is_revision_control,
1598 p_is_lot_control => l_is_lot_control,
1599 p_is_serial_control => l_is_serial_control,
1600 p_asset_sub_only => l_asset_sub_only,
1601 p_include_suggestion => TRUE,
1602 p_demand_source_type_id => l_demand_header_type,
1603 p_demand_source_header_id => p_demand_header_id,
1604 p_demand_source_line_id => p_demand_source_line_id,
1605 p_demand_source_name => p_demand_source_name,
1606 p_demand_source_delivery => p_demand_source_Delivery,
1607 p_lot_expiration_date => NULL,
1608 p_onhand_source => l_onhand_source
1609 );
1610
1611 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1612 IF (l_debug = 1) then
1613 print_debug('Error from inv_quantity_tree_pvt.find_rootinfo');
1614 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1615 END IF;
1616 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_FIND_ROOTINFO');
1617 FND_MSG_PUB.ADD;
1618 RAISE fnd_api.g_exc_error;
1619 END IF;
1620
1621 IF (l_debug = 1) then
1622 print_debug('After inv_quantity_tree_pvt.find_rootinfo tree_id:'||l_tree_id);
1623 print_debug('Will call update_qties for xact_mode='||l_xact_mode||', g_qs_txn='||g_qs_txn||', subinv='||p_transfer_subinventory);
1624 END IF;
1625
1626 IF p_transfer_subinventory IS NOT NULL AND
1627 l_xact_mode <> g_qs_txn THEN
1628
1629 IF (l_debug = 1) THEN
1630 print_debug('Calling update_quantities_for_form for xact_mode='||l_xact_mode||', trx_qty='||l_transaction_quantity||', trx_qty2='||p_transaction_quantity2||'.');
1631 END IF;
1632
1633 inv_quantity_tree_pvt.update_quantities_for_form
1634 ( p_api_version_number => 1.0,
1635 p_init_msg_lst => fnd_api.g_true,
1636 x_return_status => l_return_status,
1637 x_msg_count => l_msg_count,
1638 x_msg_data => l_msg_data,
1639 p_tree_id => l_tree_id,
1640 p_revision => p_Revision,
1641 p_lot_number => p_Lot,
1642 p_subinventory_code => p_transfer_Subinventory,
1643 p_locator_id => l_transfer_Locator_id,
1644 p_primary_quantity => l_transaction_quantity,
1645 p_secondary_quantity => p_transaction_quantity2, -- invConv change
1646 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
1647 x_qoh => l_qoh,
1648 x_rqoh => l_rqoh,
1649 x_qr => l_qr,
1650 x_qs => l_qs,
1651 x_att => l_att,
1652 x_atr => l_atr,
1653 x_sqoh => l_sqoh, -- invConv change
1654 x_srqoh => l_srqoh, -- invConv change
1655 x_sqr => l_sqr, -- invConv change
1656 x_sqs => l_sqs, -- invConv change
1657 x_satt => l_satt, -- invConv change
1658 x_satr => l_satr, -- invConv change
1659 p_call_for_form => fnd_api.g_true,
1660 p_lpn_id =>p_lpn_id --added for bug7038890
1661 );
1662
1663 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1664 IF (l_debug = 1) then
1665 print_debug('Error from inv_quantity_tree_pvt.update_quantities_for_form 1');
1666 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1667 END IF;
1668 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_UPDATE_QUANTITIES');
1669 FND_MSG_PUB.ADD;
1670 RAISE fnd_api.g_exc_error;
1671 END IF;
1672
1673 IF (l_debug = 1) THEN
1674 print_debug('Primaries l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
1675 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
1676 print_debug('Secondaries l_sqoh:'||l_sqoh||'l_srqoh:'||l_srqoh||'l_sqr:'||l_qr||
1677 'l_sqs:'||l_sqs||'l_satt:'||l_satt||'l_satr:'||l_satr);
1678 END IF;
1679
1680 IF (l_debug = 1) THEN
1681 print_debug('Calling update_quantities_for_form for trx_qty='||(0 - l_transaction_quantity)||', trx_qty2='||(0 - p_transaction_quantity2)||'.');
1682 END IF;
1683
1684 inv_quantity_tree_pvt.update_quantities_for_form
1685 ( p_api_version_number => 1.0,
1686 p_init_msg_lst => fnd_api.g_true,
1687 x_return_status => l_return_status,
1688 x_msg_count => l_msg_count,
1689 x_msg_data => l_msg_data,
1690 p_tree_id => l_tree_id,
1691 p_revision => p_Revision,
1692 p_lot_number => p_Lot,
1693 p_subinventory_code => p_Subinventory,
1694 p_locator_id => l_Locator_id,
1695 p_primary_quantity => (0 - l_transaction_quantity),
1696 p_secondary_quantity => (0 - p_transaction_quantity2),
1697 p_quantity_type => inv_quantity_tree_pvt.g_qoh,
1698 x_qoh => l_qoh,
1699 x_rqoh => l_rqoh,
1700 x_qr => l_qr,
1701 x_qs => l_qs,
1702 x_att => l_att,
1703 x_atr => l_atr,
1704 x_sqoh => l_sqoh, -- invConv change
1705 x_srqoh => l_srqoh, -- invConv change
1706 x_sqr => l_sqr, -- invConv change
1707 x_sqs => l_sqs, -- invConv change
1708 x_satt => l_satt, -- invConv change
1709 x_satr => l_satr, -- invConv change
1710 p_call_for_form => fnd_api.g_true,
1711 p_lpn_id =>p_lpn_id --added for bug7038890
1712 );
1713
1714 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1715 IF (l_debug = 1) then
1716 print_debug('Error from inv_quantity_tree_pvt.update_quantities_for_form 2');
1717 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1718 END IF;
1719 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_UPDATE_QUANTITIES');
1720 FND_MSG_PUB.ADD;
1721 RAISE fnd_api.g_exc_error;
1722 END IF;
1723
1724 IF (l_debug = 1) THEN
1725 print_debug('Primaries l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
1726 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
1727 print_debug('Secondaries l_sqoh:'||l_sqoh||'l_srqoh:'||l_srqoh||'l_sqr:'||l_sqr||
1728 'l_sqs:'||l_sqs||'l_satt:'||l_satt||'l_satr:'||l_satr);
1729 END IF;
1730
1731 IF (l_debug = 1) THEN
1732 print_debug('calling inv_quantity_tree_pvt.query_tree');
1733 END IF;
1734
1735 inv_quantity_tree_pvt.query_tree
1736 (p_api_version_number => 1.0,
1737 p_init_msg_lst => fnd_api.g_true,
1738 x_return_status => l_return_status,
1739 x_msg_count => l_msg_count,
1740 x_msg_data => l_msg_data,
1741 p_tree_id => l_tree_id,
1742 p_revision => p_Revision,
1743 p_lot_number => p_Lot,
1744 p_subinventory_code => p_Subinventory,
1745 p_locator_id => l_locator_id,
1746 x_qoh => l_qoh,
1747 x_rqoh => l_rqoh,
1748 x_qr => l_qr,
1749 x_qs => l_qs,
1750 x_att => l_att,
1751 x_atr => l_atr,
1752 x_sqoh => l_sqoh, -- invConv change
1753 x_srqoh => l_srqoh, -- invConv change
1754 x_sqr => l_sqr, -- invConv change
1755 x_sqs => l_sqs, -- invConv change
1756 x_satt => l_satt, -- invConv change
1757 x_satr => l_satr, -- invConv change
1758 p_transfer_subinventory_code => p_Transfer_Subinventory,
1759 p_lpn_id => p_lpn_id --added for bug7038890
1760 );
1761
1762 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1763 IF (l_debug = 1) then
1764 print_debug('Error from inv_quantity_tree_pvt.query_tree');
1765 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1766 END IF;
1767 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_QUERY_TREE');
1768 FND_MSG_PUB.ADD;
1769 RAISE fnd_api.g_exc_error;
1770 END IF;
1771
1772 IF (l_debug = 1) THEN
1773 print_debug('l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
1774 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
1775 END IF;
1776
1777 ELSE
1778 IF l_xact_mode IN (g_reservation, g_qs_txn) THEN
1779 l_temp_trx_quantity := l_transaction_quantity;
1780 l_temp_trx_quantity2 := p_transaction_quantity2; -- invConv change
1781 ELSE
1782 l_temp_trx_quantity := 0 - l_transaction_quantity;
1783 l_temp_trx_quantity2 := 0 - p_transaction_quantity2; -- invConv change
1784 END IF;
1785
1786 IF (l_debug = 1) THEN
1787 print_debug('Calling update_quantities_for_form for temp_trx_qty='||l_temp_trx_quantity||', temp_trx_qty2='||l_temp_trx_quantity2||'.');
1788 END IF;
1789
1790 inv_quantity_tree_pvt.update_quantities_for_form
1791 ( p_api_version_number => 1.0,
1792 p_init_msg_lst => fnd_api.g_true,
1793 x_return_status => l_return_status,
1794 x_msg_count => l_msg_count,
1795 x_msg_data => l_msg_data,
1796 p_tree_id => l_tree_id,
1797 p_revision => p_Revision,
1798 p_lot_number => p_Lot,
1799 p_subinventory_code => p_Subinventory,
1800 p_locator_id => l_Locator_id,
1801 p_primary_quantity => l_temp_trx_quantity,
1802 p_secondary_quantity => l_temp_trx_quantity2, -- invConv change
1803 p_quantity_type => l_xact_mode,
1804 x_qoh => l_qoh,
1805 x_rqoh => l_rqoh,
1806 x_qr => l_qr,
1807 x_qs => l_qs,
1808 x_att => l_att,
1809 x_atr => l_atr,
1810 x_sqoh => l_sqoh, -- invConv change
1811 x_srqoh => l_srqoh, -- invConv change
1812 x_sqr => l_sqr, -- invConv change
1813 x_sqs => l_sqs, -- invConv change
1814 x_satt => l_satt, -- invConv change
1815 x_satr => l_satr, -- invConv change
1816 p_call_for_form => fnd_api.g_true,
1817 p_lpn_id => p_lpn_id --added for bug7038890
1818 );
1819
1820 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1821 IF (l_debug = 1) then
1822 print_debug('Error from inv_quantity_tree_pvt.update_quantities_for_form 3');
1823 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1824 END IF;
1825 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_UPDATE_QUANTITIES');
1826 FND_MSG_PUB.ADD;
1827 RAISE fnd_api.g_exc_error;
1828 END IF;
1829
1830 IF (l_debug = 1) THEN
1831 print_debug('Primaries l_qoh:'||l_qoh||'l_rqoh:'||l_rqoh||'l_qr:'||l_qr||
1832 'l_qs:'||l_qs||'l_att:'||l_att||'l_atr:'||l_atr);
1833 print_debug('Secondaries l_sqoh:'||l_sqoh||'l_srqoh:'||l_srqoh||'l_sqr:'||l_sqr||
1834 'l_sqs:'||l_sqs||'l_satt:'||l_satt||'l_satr:'||l_satr);
1835 END IF;
1836 END IF;
1837
1838 IF l_tree_mode IN (inv_quantity_tree_pvt.g_transaction_mode,
1839 inv_quantity_tree_pvt.g_loose_only_mode) THEN
1840 l_available_quantity := l_att;
1841 l_available_quantity2 := l_satt; -- invConv change
1842 ELSE
1843 l_available_quantity := l_atr;
1844 l_available_quantity2 := l_satr; -- invConv change
1845 END IF;
1846
1847 l_onhand_quantity := l_qoh;
1848 l_onhand_quantity2 := l_sqoh; -- invConv change
1849
1850 l_transaction_quantity := l_onhand_quantity;
1851 l_transaction_quantity2 := l_onhand_quantity2; -- invconv change
1852 IF (l_debug = 1) then
1853 print_debug(' odab before get_total_qoh l_original_avail_qoh='||l_onhand_quantity||', l_original_avail_qoh2='||l_onhand_quantity2);
1854 END IF;
1855
1856 IF (l_debug = 1) THEN
1857 print_debug('Calling inv_quantity_tree_pvt.get_total_qoh');
1858 END IF;
1859
1860 inv_quantity_tree_pvt.get_total_qoh
1861 (x_return_status => l_return_status,
1862 x_msg_count => l_msg_count,
1863 x_msg_data => l_msg_data,
1864 p_tree_id => l_tree_id,
1865 p_revision => p_Revision,
1866 p_lot_number => p_Lot,
1867 p_subinventory_code => p_Subinventory,
1868 p_locator_id => l_locator_id,
1869 x_tqoh => l_tqoh,
1870 x_stqoh => l_stqoh, -- invConv change
1871 p_lpn_id => p_lpn_id --added for bug7038890
1872 );
1873
1874 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1875 IF (l_debug = 1) then
1876 print_debug('Error from inv_quantity_tree_pvt.get_total_qoh');
1877 print_debug('l_return_status:'||l_return_status||'l_msg_data:'||l_msg_data);
1878 END IF;
1879 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_TOTAL_QOH');
1880 FND_MSG_PUB.ADD;
1881 RAISE fnd_api.g_exc_error;
1882 END IF;
1883
1884 IF (l_debug = 1) THEN
1885 print_debug('l_tqoh:'||l_tqoh);
1886 END IF;
1887
1888 l_avail_qoh := l_tqoh;
1889 l_avail_qoh2 := l_stqoh; -- invConv change
1890
1891 IF p_uom_code <> l_primary_uom_code THEN
1892
1893 l_available_conv_quantity := inv_convert.inv_um_convert
1894 (item_id => p_inventory_item_id,
1895 organization_id => p_organization_id, /*Bug#10035564 */
1896 lot_number => P_lot,
1897 precision => NULL,
1898 from_quantity => Abs(l_available_quantity),
1899 from_unit => l_primary_uom_code,
1900 to_unit => p_uom_code,
1901 from_name => null,
1902 to_name => NULL
1903 );
1904 IF l_available_conv_quantity < 0 THEN
1905 IF (l_debug = 1) THEN
1906 print_debug('Error converting l_available_quantity');
1907 END IF;
1908 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1909 FND_MSG_PUB.ADD;
1910 RAISE FND_API.G_EXC_ERROR;
1911 END IF;
1912
1913 IF l_available_quantity < 0 THEN
1914 l_available_quantity := l_available_conv_quantity * (-1);
1915 ELSE
1916 l_available_quantity := l_available_conv_quantity;
1917 END IF;
1918 IF (l_debug = 1) then
1919 print_debug('8 conversion result: qty='||l_available_quantity||', convQ='||l_available_conv_quantity);
1920 END IF;
1921
1922 l_avail_qoh_conv_quantity := inv_convert.inv_um_convert
1923 (item_id => p_inventory_item_id,
1924 organization_id => p_organization_id, /*Bug#10035564 */
1925 lot_number => P_lot,
1926 precision => NULL,
1927 from_quantity => Abs(l_avail_qoh),
1928 from_unit => l_primary_uom_code,
1929 to_unit => p_uom_code,
1930 from_name => null,
1931 to_name => NULL
1932 );
1933 IF l_avail_qoh_conv_quantity < 0 THEN
1934 IF (l_debug = 1) THEN
1935 print_debug('Error converting l_avail_qoh');
1936 END IF;
1937 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1938 FND_MSG_PUB.ADD;
1939 RAISE FND_API.G_EXC_ERROR;
1940 END IF;
1941
1942 IF l_avail_qoh < 0 THEN
1943 l_avail_qoh := l_avail_qoh_conv_quantity * (-1);
1944 ELSE
1945 l_avail_qoh := l_avail_qoh_conv_quantity;
1946 END IF;
1947 IF (l_debug = 1) then
1948 print_debug('9 conversion result: qty='||l_avail_qoh||', convQ='||l_avail_qoh_conv_quantity);
1949 END IF;
1950
1951 l_transaction_conv_quantity := inv_convert.inv_um_convert
1952 (item_id => p_inventory_item_id,
1953 organization_id => p_organization_id, /*Bug#10035564 */
1954 lot_number => P_lot,
1955 precision => NULL,
1956 from_quantity => Abs(l_transaction_quantity),
1957 from_unit => l_primary_uom_code,
1958 to_unit => p_uom_code,
1959 from_name => null,
1960 to_name => NULL
1961 );
1962 IF l_transaction_conv_quantity < 0 THEN
1963 IF (l_debug = 1) THEN
1964 print_debug('Error converting l_transaction_quantity');
1965 END IF;
1966 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_CANNOT_CONVERT');
1967 FND_MSG_PUB.ADD;
1968 RAISE FND_API.G_EXC_ERROR;
1969 END IF;
1970
1971 IF l_transaction_quantity < 0 THEN
1972 l_transaction_quantity := l_transaction_conv_quantity * (-1);
1973 ELSE
1974 l_transaction_quantity := l_transaction_conv_quantity;
1975 END IF;
1976 IF (l_debug = 1) then
1977 print_debug('0 conversion result: qty='||l_transaction_quantity||', convQ='||l_transaction_conv_quantity);
1978 END IF;
1979 END IF;
1980 x_available_quantity := ROUND(l_available_quantity, 5);
1981 x_available_onhand := ROUND(l_avail_qoh, 5);
1982 x_onhand_quantity := ROUND(l_transaction_quantity, 5);
1983 x_available_quantity2 := ROUND(l_available_quantity2, 5); -- invConv change
1984 x_available_onhand2 := ROUND(l_avail_qoh2, 5); -- invConv change
1985 x_onhand_quantity2 := ROUND(l_transaction_quantity2, 5); -- invConv change
1986
1987 IF (l_debug = 1) THEN
1988 print_debug('xact_tree returning x_return_status:'||x_return_status||
1989 ' x_available_quantity:'||x_available_quantity||
1990 ' x_available_onhand:'||x_available_onhand||
1991 ' x_onhand_quantity:'||x_onhand_quantity||
1992 ' x_available_quantity2:'||x_available_quantity2||
1993 ' x_available_onhand2:'||x_available_onhand2||
1994 ' x_onhand_quantity2:'||x_onhand_quantity2);
1995 END IF;
1996
1997 RETURN 1;
1998
1999 EXCEPTION
2000 WHEN fnd_api.g_exc_error THEN
2001 IF (l_debug = 1) THEN
2002 print_debug('fnd_api.g_exc_error');
2003 END IF;
2004 x_return_status := fnd_api.g_ret_sts_error;
2005 x_available_quantity := 0;
2006 x_available_onhand := 0;
2007 x_onhand_quantity := 0;
2008
2009 -- invConv changes begin :
2010 IF p_transaction_quantity2 IS NOT NULL
2011 THEN
2012 x_available_quantity2 := 0;
2013 x_available_onhand2 := 0;
2014 x_onhand_quantity2 := 0;
2015 END IF;
2016 -- invConv changes end.
2017
2018 fnd_msg_pub.count_and_get
2019 ( p_count => x_message_count
2020 , p_data => x_message_data
2021 );
2022
2023 IF (l_debug = 1) THEN
2024 FOR i IN 1 .. x_message_count LOOP
2025 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
2026 END LOOP;
2027 END IF;
2028
2029 RETURN 0;
2030 WHEN OTHERS THEN
2031 IF (l_debug = 1) THEN
2032 print_debug('Others error');
2033 END IF;
2034 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2035
2036 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2037 THEN
2038 fnd_msg_pub.add_exc_msg
2039 ( 'INV_QUANTITY_TREE_UE'
2040 ,'XACT_QTY'
2041 );
2042 END IF;
2043
2044 x_available_quantity := 0;
2045 x_available_onhand := 0;
2046 x_onhand_quantity := 0;
2047 -- invConv changes begin :
2048 IF p_transaction_quantity2 IS NOT NULL
2049 THEN
2050 x_available_quantity2 := 0;
2051 x_available_onhand2 := 0;
2052 x_onhand_quantity2 := 0;
2053 END IF;
2054 -- invConv changes end.
2055
2056 fnd_msg_pub.count_and_get
2057 ( p_count => x_message_count
2058 , p_data => x_message_data
2059 );
2060
2061 IF (l_debug = 1) THEN
2062 FOR i IN 1 .. x_message_count LOOP
2063 print_debug(fnd_msg_pub.get(x_message_count - i + 1, 'F'));
2064 END LOOP;
2065 END IF;
2066
2067 RETURN 0;
2068 END xact_qty;
2069
2070 END INV_QUANTITY_TREE_UE;