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