[Home] [Help]
PACKAGE BODY: APPS.INV_QUANTITY_TREE_PUB
Source
1 PACKAGE BODY inv_quantity_tree_pub as
2 /* $Header: INVPQTTB.pls 120.5 2006/10/11 23:53:04 cjandhya noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Quantity_Tree_PUB';
5
6 -- Procedure
7 -- clear_quantity_cache
8 -- Description
9 -- Delete all quantity trees in the memory. Should be called when you call
10 -- rollback. Otherwise the trees in memory may not be in sync with the data
11 -- in the corresponding database tables
12 PROCEDURE clear_quantity_cache
13 IS
14 BEGIN
15 inv_quantity_tree_grp.clear_quantity_cache;
16 END clear_quantity_cache;
17
18 -- Procedure
19 -- query_quantities
20 -- Description
21 -- Query quantities at a level
22 -- specified by the input
23 PROCEDURE query_quantities
24 ( p_api_version_number IN NUMBER
25 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
26 , x_return_status OUT NOCOPY VARCHAR2
27 , x_msg_count OUT NOCOPY NUMBER
28 , x_msg_data OUT NOCOPY VARCHAR2
29 , p_organization_id IN NUMBER
30 , p_inventory_item_id IN NUMBER
31 , p_tree_mode IN INTEGER
32 , p_is_revision_control IN BOOLEAN
33 , p_is_lot_control IN BOOLEAN
34 , p_is_serial_control IN BOOLEAN
35 , p_demand_source_type_id IN NUMBER DEFAULT -9999
36 , p_demand_source_header_id IN NUMBER DEFAULT -9999
37 , p_demand_source_line_id IN NUMBER DEFAULT -9999
38 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
39 , p_lot_expiration_date IN DATE DEFAULT NULL
40 , p_revision IN VARCHAR2
41 , p_lot_number IN VARCHAR2
42 , p_subinventory_code IN VARCHAR2
43 , p_locator_id IN NUMBER
44 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
45 , x_qoh OUT NOCOPY NUMBER
46 , x_rqoh OUT NOCOPY NUMBER
47 , x_qr OUT NOCOPY NUMBER
48 , x_qs OUT NOCOPY NUMBER
49 , x_att OUT NOCOPY NUMBER
50 , x_atr OUT NOCOPY NUMBER
51 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
52 , p_cost_group_id IN NUMBER DEFAULT NULL
53 , p_lpn_id IN NUMBER DEFAULT NULL
54 , p_transfer_locator_id IN NUMBER DEFAULT NULL
55 ) IS
56
57 l_grade_code VARCHAR2(150); -- invConv change
58 l_sqoh NUMBER; -- invConv change
59 l_srqoh NUMBER; -- invConv change
60 l_sqr NUMBER; -- invConv change
61 l_sqs NUMBER; -- invConv change
62 l_satt NUMBER; -- invConv change
63 l_satr NUMBER; -- invConv change
64
65 BEGIN
66 -- invConv changes begin:
67 -- calling the new API
68 query_quantities
69 ( p_api_version_number => p_api_version_number
70 , p_init_msg_lst => p_init_msg_lst
71 , x_return_status => x_return_status
72 , x_msg_count => x_msg_count
73 , x_msg_data => x_msg_data
74 , p_organization_id => p_organization_id
75 , p_inventory_item_id => p_inventory_item_id
76 , p_tree_mode => p_tree_mode
77 , p_is_revision_control => p_is_revision_control
78 , p_is_lot_control => p_is_lot_control
79 , p_is_serial_control => p_is_serial_control
80 , p_grade_code => l_grade_code
81 , p_demand_source_type_id => p_demand_source_type_id
82 , p_demand_source_header_id => p_demand_source_header_id
83 , p_demand_source_line_id => p_demand_source_line_id
84 , p_demand_source_name => p_demand_source_name
85 , p_lot_expiration_date => p_lot_expiration_date
86 , p_revision => p_revision
87 , p_lot_number => p_lot_number
88 , p_subinventory_code => p_subinventory_code
89 , p_locator_id => p_locator_id
90 , p_onhand_source => p_onhand_source
91 , x_qoh => x_qoh
92 , x_rqoh => x_rqoh
93 , x_qr => x_qr
94 , x_qs => x_qs
95 , x_att => x_att
96 , x_atr => x_atr
97 , x_sqoh => l_sqoh
98 , x_srqoh => l_srqoh
99 , x_sqr => l_sqr
100 , x_sqs => l_sqs
101 , x_satt => l_satt
102 , x_satr => l_satr
103 , p_transfer_subinventory_code => p_transfer_subinventory_code
104 , p_cost_group_id => p_cost_group_id
105 , p_lpn_id => p_lpn_id
106 , p_transfer_locator_id => p_transfer_locator_id);
107 -- invConv changes end.
108
109 END query_quantities;
110
111 -- invConv changes begin : overload
112 PROCEDURE query_quantities
113 ( p_api_version_number IN NUMBER
114 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
115 , x_return_status OUT NOCOPY VARCHAR2
116 , x_msg_count OUT NOCOPY NUMBER
117 , x_msg_data OUT NOCOPY VARCHAR2
118 , p_organization_id IN NUMBER
119 , p_inventory_item_id IN NUMBER
120 , p_tree_mode IN INTEGER
121 , p_is_revision_control IN BOOLEAN
122 , p_is_lot_control IN BOOLEAN
123 , p_is_serial_control IN BOOLEAN
124 , p_grade_code IN VARCHAR2 -- invConv change
125 , p_demand_source_type_id IN NUMBER DEFAULT -9999
126 , p_demand_source_header_id IN NUMBER DEFAULT -9999
127 , p_demand_source_line_id IN NUMBER DEFAULT -9999
128 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
129 , p_lot_expiration_date IN DATE DEFAULT NULL
130 , p_revision IN VARCHAR2
131 , p_lot_number IN VARCHAR2
132 , p_subinventory_code IN VARCHAR2
133 , p_locator_id IN NUMBER
134 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
135 , x_qoh OUT NOCOPY NUMBER
136 , x_rqoh OUT NOCOPY NUMBER
137 , x_qr OUT NOCOPY NUMBER
138 , x_qs OUT NOCOPY NUMBER
139 , x_att OUT NOCOPY NUMBER
140 , x_atr OUT NOCOPY NUMBER
141 , x_sqoh OUT NOCOPY NUMBER -- invConv change
142 , x_srqoh OUT NOCOPY NUMBER -- invConv change
143 , x_sqr OUT NOCOPY NUMBER -- invConv change
144 , x_sqs OUT NOCOPY NUMBER -- invConv change
145 , x_satt OUT NOCOPY NUMBER -- invConv change
146 , x_satr OUT NOCOPY NUMBER -- invConv change
147 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
148 , p_cost_group_id IN NUMBER DEFAULT NULL
149 , p_lpn_id IN NUMBER DEFAULT NULL
150 , p_transfer_locator_id IN NUMBER DEFAULT NULL
151 ) IS
152
153 l_api_version_number CONSTANT NUMBER := 1.0;
154 l_api_name CONSTANT VARCHAR2(30) := 'Query_Quantities';
155 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
156 l_tree_id INTEGER;
157
158 BEGIN
159
160 -- Standard call to check for call compatibility
161 IF NOT fnd_api.compatible_api_call(l_api_version_number
162 , p_api_version_number
163 , l_api_name
164 , G_PKG_NAME
165 ) THEN
166 RAISE fnd_api.g_exc_unexpected_error;
167 END IF;
168
169 -- Initialize message list.
170 IF fnd_api.to_boolean(p_init_msg_lst) THEN
171 fnd_msg_pub.initialize;
172 END IF;
173
174 inv_quantity_tree_grp.create_tree
175 (
176 p_api_version_number => 1.0
177 , p_init_msg_lst => p_init_msg_lst
178 , x_return_status => l_return_status
179 , x_msg_count => x_msg_count
180 , x_msg_data => x_msg_data
181 , p_organization_id => p_organization_id
182 , p_inventory_item_id => p_inventory_item_id
183 , p_tree_mode => p_tree_mode
184 , p_is_revision_control => p_is_revision_control
185 , p_is_lot_control => p_is_lot_control
186 , p_is_serial_control => p_is_serial_control
187 , p_grade_code => p_grade_code
188 , p_demand_source_type_id => p_demand_source_type_id
189 , p_demand_source_header_id => p_demand_source_header_id
190 , p_demand_source_line_id => p_demand_source_line_id
191 , p_demand_source_name => p_demand_source_name
192 , p_lot_expiration_date => p_lot_expiration_date
193 , p_onhand_source => p_onhand_source
194 , x_tree_id => l_tree_id
195 );
196
197 IF l_return_status = fnd_api.g_ret_sts_error THEN
198 RAISE fnd_api.g_exc_error;
199 END IF ;
200
201 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
202 RAISE fnd_api.g_exc_unexpected_error;
203 END IF;
204
205 inv_quantity_tree_grp.query_tree
206 (
207 p_api_version_number => 1.0
208 , p_init_msg_lst => p_init_msg_lst
209 , x_return_status => l_return_status
210 , x_msg_count => x_msg_count
211 , x_msg_data => x_msg_data
212 , p_tree_id => l_tree_id
213 , p_revision => p_revision
214 , p_lot_number => p_lot_number
215 , p_subinventory_code => p_subinventory_code
216 , p_locator_id => p_locator_id
217 , x_qoh => x_qoh
218 , x_rqoh => x_rqoh
219 , x_qr => x_qr
220 , x_qs => x_qs
221 , x_att => x_att
222 , x_atr => x_atr
223 , x_sqoh => x_sqoh -- invConv change
224 , x_srqoh => x_srqoh -- invConv change
225 , x_sqr => x_sqr -- invConv change
226 , x_sqs => x_sqs -- invConv change
227 , x_satt => x_satt -- invConv change
228 , x_satr => x_satr -- invConv change
229 , p_transfer_subinventory_code => p_transfer_subinventory_code
230 , p_cost_group_id => p_cost_group_id
231 , p_lpn_id => p_lpn_id
232 , p_transfer_locator_id => p_transfer_locator_id
233 );
234
235 IF l_return_status = fnd_api.g_ret_sts_error THEN
236 RAISE fnd_api.g_exc_error;
237 END IF ;
238
239 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
240 RAISE fnd_api.g_exc_unexpected_error;
241 END IF;
242
243 x_return_status := l_return_status;
244
245 EXCEPTION
246
247 WHEN fnd_api.g_exc_error THEN
248 x_return_status := fnd_api.g_ret_sts_error;
249
250 -- Get message count and data
251 fnd_msg_pub.count_and_get
252 ( p_count => x_msg_count
253 , p_data => x_msg_data
254 );
255
256 WHEN fnd_api.g_exc_unexpected_error THEN
257 x_return_status := fnd_api.g_ret_sts_unexp_error ;
258
259 -- Get message count and data
260 fnd_msg_pub.count_and_get
261 ( p_count => x_msg_count
262 , p_data => x_msg_data
263 );
264
265 WHEN OTHERS THEN
266 x_return_status := fnd_api.g_ret_sts_unexp_error ;
267
268 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
269 THEN
270 fnd_msg_pub.add_exc_msg
271 ( g_pkg_name
272 , l_api_name
273 );
274 END IF;
275
276 -- Get message count and data
277 fnd_msg_pub.count_and_get
278 ( p_count => x_msg_count
279 , p_data => x_msg_data
280 );
281
282 END query_quantities;
283
284 -- Procedure
285 -- update_quantities
286 -- Description
287 -- Update quantity at the level specified by the input and
288 -- return the quantities at the level after the update
289 PROCEDURE update_quantities
290 ( p_api_version_number IN NUMBER
291 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
292 , x_return_status OUT NOCOPY VARCHAR2
293 , x_msg_count OUT NOCOPY NUMBER
294 , x_msg_data OUT NOCOPY VARCHAR2
295 , p_organization_id IN NUMBER
296 , p_inventory_item_id IN NUMBER
297 , p_tree_mode IN INTEGER
298 , p_is_revision_control IN BOOLEAN
299 , p_is_lot_control IN BOOLEAN
300 , p_is_serial_control IN BOOLEAN
301 , p_demand_source_type_id IN NUMBER DEFAULT -9999
302 , p_demand_source_header_id IN NUMBER DEFAULT -9999
303 , p_demand_source_line_id IN NUMBER DEFAULT -9999
304 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
305 , p_lot_expiration_date IN DATE DEFAULT NULL
306 , p_revision IN VARCHAR2 DEFAULT NULL
307 , p_lot_number IN VARCHAR2 DEFAULT NULL
308 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
309 , p_locator_id IN NUMBER DEFAULT NULL
310 , p_primary_quantity IN NUMBER
311 , p_quantity_type IN INTEGER
312 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
313 , x_qoh OUT NOCOPY NUMBER
314 , x_rqoh OUT NOCOPY NUMBER
315 , x_qr OUT NOCOPY NUMBER
316 , x_qs OUT NOCOPY NUMBER
317 , x_att OUT NOCOPY NUMBER
318 , x_atr OUT NOCOPY NUMBER
319 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
320 , p_cost_group_id IN NUMBER DEFAULT NULL
321 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
322 , p_lpn_id IN NUMBER DEFAULT NULL
323 , p_transfer_locator_id IN NUMBER DEFAULT NULL
324 ) IS
325
326 l_sqoh NUMBER; -- invConv change
327 l_srqoh NUMBER; -- invConv change
328 l_sqr NUMBER; -- invConv change
329 l_sqs NUMBER; -- invConv change
330 l_satt NUMBER; -- invConv change
331 l_satr NUMBER; -- invConv change
332 l_secondary_quantity NUMBER; -- invConv change
333
334 BEGIN
335
336 -- invConv changes begin :
337 -- calling the new API
338 update_quantities
339 ( p_api_version_number => p_api_version_number
340 , p_init_msg_lst => p_init_msg_lst
341 , x_return_status => x_return_status
342 , x_msg_count => x_msg_count
343 , x_msg_data => x_msg_data
344 , p_organization_id => p_organization_id
345 , p_inventory_item_id => p_inventory_item_id
346 , p_tree_mode => p_tree_mode
347 , p_is_revision_control => p_is_revision_control
348 , p_is_lot_control => p_is_lot_control
349 , p_is_serial_control => p_is_serial_control
350 , p_demand_source_type_id => p_demand_source_type_id
351 , p_demand_source_header_id => p_demand_source_header_id
352 , p_demand_source_line_id => p_demand_source_line_id
353 , p_demand_source_name => p_demand_source_name
354 , p_lot_expiration_date => p_lot_expiration_date
355 , p_revision => p_revision
356 , p_lot_number => p_lot_number
357 , p_subinventory_code => p_subinventory_code
358 , p_locator_id => p_locator_id
359 , p_grade_code => NULL -- invConv change
360 , p_primary_quantity => p_primary_quantity
361 , p_quantity_type => p_quantity_type
362 , p_secondary_quantity => l_secondary_quantity -- invConv change
363 , p_onhand_source => p_onhand_source
364 , x_qoh => x_qoh
365 , x_rqoh => x_rqoh
366 , x_qr => x_qr
367 , x_qs => x_qs
368 , x_att => x_att
369 , x_atr => x_atr
370 , x_sqoh => l_sqoh -- invConv change
371 , x_srqoh => l_srqoh -- invConv change
372 , x_sqr => l_sqr -- invConv change
373 , x_sqs => l_sqs -- invConv change
374 , x_satt => l_satt -- invConv change
375 , x_satr => l_satr -- invConv change
376 , p_transfer_subinventory_code => p_transfer_subinventory_code
377 , p_cost_group_id => p_cost_group_id
378 , p_containerized => p_containerized
379 , p_lpn_id => p_lpn_id
380 , p_transfer_locator_id => p_transfer_locator_id);
381 -- invConv changes end.
382
383
384 END update_quantities;
385
386 -- invConv changes begin: overload
387 PROCEDURE update_quantities
388 ( p_api_version_number IN NUMBER
389 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
390 , x_return_status OUT NOCOPY VARCHAR2
391 , x_msg_count OUT NOCOPY NUMBER
392 , x_msg_data OUT NOCOPY VARCHAR2
393 , p_organization_id IN NUMBER
394 , p_inventory_item_id IN NUMBER
395 , p_tree_mode IN INTEGER
396 , p_is_revision_control IN BOOLEAN
397 , p_is_lot_control IN BOOLEAN
398 , p_is_serial_control IN BOOLEAN
399 , p_demand_source_type_id IN NUMBER DEFAULT -9999
400 , p_demand_source_header_id IN NUMBER DEFAULT -9999
401 , p_demand_source_line_id IN NUMBER DEFAULT -9999
402 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
403 , p_lot_expiration_date IN DATE DEFAULT NULL
404 , p_revision IN VARCHAR2 DEFAULT NULL
405 , p_lot_number IN VARCHAR2 DEFAULT NULL
406 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
407 , p_locator_id IN NUMBER DEFAULT NULL
408 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
409 , p_primary_quantity IN NUMBER
410 , p_quantity_type IN INTEGER
411 , p_secondary_quantity IN NUMBER -- invConv change
412 , p_onhand_source IN NUMBER DEFAULT inv_quantity_tree_pvt.g_all_subs
413 , x_qoh OUT NOCOPY NUMBER
414 , x_rqoh OUT NOCOPY NUMBER
415 , x_qr OUT NOCOPY NUMBER
416 , x_qs OUT NOCOPY NUMBER
417 , x_att OUT NOCOPY NUMBER
418 , x_atr OUT NOCOPY NUMBER
419 , x_sqoh OUT NOCOPY NUMBER -- invConv change
420 , x_srqoh OUT NOCOPY NUMBER -- invConv change
421 , x_sqr OUT NOCOPY NUMBER -- invConv change
422 , x_sqs OUT NOCOPY NUMBER -- invConv change
423 , x_satt OUT NOCOPY NUMBER -- invConv change
424 , x_satr OUT NOCOPY NUMBER -- invConv change
425 , p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
426 , p_cost_group_id IN NUMBER DEFAULT NULL
427 , p_containerized IN NUMBER DEFAULT inv_quantity_tree_pvt.g_containerized_false
428 , p_lpn_id IN NUMBER DEFAULT NULL
429 , p_transfer_locator_id IN NUMBER DEFAULT NULL
430 ) IS
431
432 l_api_version_number CONSTANT NUMBER := 1.0;
433 l_api_name CONSTANT VARCHAR2(30) := 'Update_Quantities';
434 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
435 l_tree_id INTEGER;
436 l_qoh NUMBER;
437 l_rqoh NUMBER;
438 l_qr NUMBER;
439 l_qs NUMBER;
440 l_att NUMBER;
441 l_atr NUMBER;
442 l_sqoh NUMBER; -- invConv change
443 l_srqoh NUMBER; -- invConv change
444 l_sqr NUMBER; -- invConv change
445 l_sqs NUMBER; -- invConv change
446 l_satt NUMBER; -- invConv change
447 l_satr NUMBER; -- invConv change
448 /*Bug:5209598. Following three variables have been added as part of this bug. */
449 l_override_neg_for_backflush NUMBER := 2;
450 l_neg_inv_rcpt NUMBER := 0;
451 BEGIN
452
453 -- Standard call to check for call compatibility
454 IF NOT fnd_api.compatible_api_call(l_api_version_number
455 , p_api_version_number
456 , l_api_name
457 , G_PKG_NAME
458 ) THEN
459 RAISE fnd_api.g_exc_unexpected_error;
460 END IF;
461
462 -- Initialize message list.
463 IF fnd_api.to_boolean(p_init_msg_lst) THEN
464 fnd_msg_pub.initialize;
465 END IF;
466
467 inv_quantity_tree_grp.create_tree
468 (
469 p_api_version_number => 1.0
470 , p_init_msg_lst => p_init_msg_lst
471 , x_return_status => l_return_status
472 , x_msg_count => x_msg_count
473 , x_msg_data => x_msg_data
474 , p_organization_id => p_organization_id
475 , p_inventory_item_id => p_inventory_item_id
476 , p_tree_mode => p_tree_mode
477 , p_is_revision_control => p_is_revision_control
478 , p_is_lot_control => p_is_lot_control
479 , p_is_serial_control => p_is_serial_control
480 , p_grade_code => p_grade_code
481 , p_demand_source_type_id => p_demand_source_type_id
482 , p_demand_source_header_id => p_demand_source_header_id
483 , p_demand_source_line_id => p_demand_source_line_id
484 , p_demand_source_name => p_demand_source_name
485 , p_lot_expiration_date => p_lot_expiration_date
486 , p_onhand_source => p_onhand_source
487 , x_tree_id => l_tree_id
488 );
489
490 IF l_return_status = fnd_api.g_ret_sts_error THEN
491 RAISE fnd_api.g_exc_error;
492 END IF ;
493
494 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
495 RAISE fnd_api.g_exc_unexpected_error;
496 END IF;
497
498 inv_quantity_tree_grp.query_tree
499 (
500 p_api_version_number => 1.0
501 , p_init_msg_lst => p_init_msg_lst
502 , x_return_status => l_return_status
503 , x_msg_count => x_msg_count
504 , x_msg_data => x_msg_data
505 , p_tree_id => l_tree_id
506 , p_revision => p_revision
507 , p_lot_number => p_lot_number
508 , p_subinventory_code => p_subinventory_code
509 , p_locator_id => p_locator_id
510 , x_qoh => l_qoh
511 , x_rqoh => l_rqoh
512 , x_qr => l_qr
513 , x_qs => l_qs
514 , x_att => l_att
515 , x_atr => l_atr
516 , x_sqoh => l_sqoh -- invConv change
517 , x_srqoh => l_srqoh -- invConv change
518 , x_sqr => l_sqr -- invConv change
519 , x_sqs => l_sqs -- invConv change
520 , x_satt => l_satt -- invConv change
521 , x_satr => l_satr -- invConv change
522 , p_transfer_subinventory_code => p_transfer_subinventory_code
523 , p_cost_group_id => p_cost_group_id
524 , p_lpn_id => p_lpn_id
525 , p_transfer_locator_id => p_transfer_locator_id
526 );
527
528 IF l_return_status = fnd_api.g_ret_sts_error THEN
529 RAISE fnd_api.g_exc_error;
530 END IF ;
531
532 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
533 RAISE fnd_api.g_exc_unexpected_error;
534 END IF;
535
536 /*Bug:5209598. Added the follwing code to get the profile values. */
537 IF (p_demand_source_type_id = inv_globals.G_SOURCETYPE_WIP) THEN
538 l_override_neg_for_backflush := NVL(fnd_profile.value('INV_OVERRIDE_NEG_FOR_BACKFLUSH'), 2);
539 END IF;
540
541 BEGIN
542 SELECT NEGATIVE_INV_RECEIPT_CODE
543 INTO l_neg_inv_rcpt
544 FROM MTL_PARAMETERS
545 WHERE ORGANIZATION_ID = p_organization_id;
546 EXCEPTION
547 WHEN OTHERS THEN
548 l_neg_inv_rcpt := 2;
549 END;
550
551
552 /*Bug:5209598. Following condition has been modified to not error out in the case where
553 we are trying to complete an assembly that has a component with
554 transaction_quantity(p_primary_quantity of the component) less than 0,
555 abs(transaction_quantity)< ATT and there exists a reservation for this component,
556 eventhough the profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' is set to 'YES'.
557 Now the following condition is modified to allow this transaction by checking the profile */
558
559 --Bug 1641063
560 --Check for sufficient qty when transaction mode was wrong
561 /*Bug 5451638. Modified the following condition to check only for profile
562 l_override_neg_for_backflush only ignoring l_override_rsv_for_backflush
563 for WIP transactions*/
564 IF (p_tree_mode = g_reservation_mode
565 AND p_primary_quantity > 0
566 AND p_primary_quantity > l_atr )
567 OR (p_tree_mode = g_transaction_mode
568 AND p_quantity_type = g_qoh
569 AND p_primary_quantity < 0
570 AND abs(p_primary_quantity) > l_att
571 AND NOT( ( p_demand_source_type_id = inv_globals.G_SOURCETYPE_WIP AND
572 l_override_neg_for_backflush = 1 )
573 OR
574 (l_neg_inv_rcpt = 1)
575 )
576 AND l_att > 0)
577 THEN
578 fnd_message.set_name('INV', 'INV_QTY_LESS_OR_EQUAL');
579 fnd_msg_pub.add;
580 RAISE fnd_api.g_exc_error;
581 END IF;
582
583 inv_quantity_tree_grp.update_quantities
584 (
585 p_api_version_number => 1.0
586 , p_init_msg_lst => p_init_msg_lst
587 , x_return_status => l_return_status
588 , x_msg_count => x_msg_count
589 , x_msg_data => x_msg_data
590 , p_tree_id => l_tree_id
591 , p_revision => p_revision
592 , p_lot_number => p_lot_number
593 , p_subinventory_code => p_subinventory_code
594 , p_locator_id => p_locator_id
595 , p_primary_quantity => p_primary_quantity
596 , p_quantity_type => p_quantity_type
597 , p_secondary_quantity => p_secondary_quantity -- invConv change
598 , x_qoh => l_qoh
599 , x_rqoh => l_rqoh
600 , x_qr => l_qr
601 , x_qs => l_qs
602 , x_att => l_att
603 , x_atr => l_atr
604 , x_sqoh => l_sqoh -- invConv change
605 , x_srqoh => l_srqoh -- invConv change
606 , x_sqr => l_sqr -- invConv change
607 , x_sqs => l_sqs -- invConv change
608 , x_satt => l_satt -- invConv change
609 , x_satr => l_satr -- invConv change
610 , p_transfer_subinventory_code => p_transfer_subinventory_code
611 , p_cost_group_id => p_cost_group_id
612 , p_containerized => p_containerized
613 , p_lpn_id => p_lpn_id
614 , p_transfer_locator_id => p_transfer_locator_id
615 );
616
617 IF l_return_status = fnd_api.g_ret_sts_error THEN
618 RAISE fnd_api.g_exc_error;
619 END IF ;
620
621 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
622 RAISE fnd_api.g_exc_unexpected_error;
623 END IF;
624
625 x_qoh := l_qoh;
626 x_rqoh := l_rqoh;
627 x_qr := l_qr;
628 x_qs := l_qs;
629 x_att := l_att;
630 x_atr := l_atr;
631 x_sqoh := l_sqoh;
632 x_srqoh := l_srqoh;
633 x_sqr := l_sqr;
634 x_sqs := l_sqs;
635 x_satt := l_satt;
636 x_satr := l_satr;
637 x_return_status := l_return_status;
638
639 EXCEPTION
640
641 WHEN fnd_api.g_exc_error THEN
642 x_return_status := fnd_api.g_ret_sts_error;
643
644 -- Get message count and data
645 fnd_msg_pub.count_and_get
646 ( p_count => x_msg_count
647 , p_data => x_msg_data
648 );
649
650 WHEN fnd_api.g_exc_unexpected_error THEN
651 x_return_status := fnd_api.g_ret_sts_unexp_error ;
652
653 -- Get message count and data
654 fnd_msg_pub.count_and_get
655 ( p_count => x_msg_count
656 , p_data => x_msg_data
657 );
658
659 WHEN OTHERS THEN
660 x_return_status := fnd_api.g_ret_sts_unexp_error ;
661
662 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
663 THEN
664 fnd_msg_pub.add_exc_msg
665 ( g_pkg_name
666 , l_api_name
667 );
668 END IF;
669
670 -- Get message count and data
671 fnd_msg_pub.count_and_get
672 ( p_count => x_msg_count
673 , p_data => x_msg_data
674 );
675
676 END update_quantities;
677
678 -- Procedure
679 -- do_check
680 -- Description
681 -- check whether the updates done in all trees so far are still valid
682 -- Return
683 -- x_no_violation = true if no violation has found
684 -- , otherwise = false
685 PROCEDURE do_check
686 ( p_api_version_number IN NUMBER
687 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
688 , x_return_status OUT NOCOPY VARCHAR2
689 , x_msg_count OUT NOCOPY NUMBER
690 , x_msg_data OUT NOCOPY VARCHAR2
691 , x_no_violation OUT NOCOPY BOOLEAN
692 ) IS
693 l_api_version_number CONSTANT NUMBER := 1.0;
694 l_api_name CONSTANT VARCHAR2(30) := 'Do_Check';
695 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
696 BEGIN
697 -- Standard call to check for call compatibility
698 IF NOT fnd_api.compatible_api_call(l_api_version_number
699 , p_api_version_number
700 , l_api_name
701 , G_PKG_NAME
702 ) THEN
703 RAISE fnd_api.g_exc_unexpected_error;
704 END IF;
705
706 -- Initialize message list.
707 IF fnd_api.to_boolean(p_init_msg_lst) THEN
708 fnd_msg_pub.initialize;
709 END IF;
710
711 inv_quantity_tree_grp.do_check
712 (
713 p_api_version_number => 1.0
714 , p_init_msg_lst => p_init_msg_lst
715 , x_return_status => l_return_status
716 , x_msg_count => x_msg_count
717 , x_msg_data => x_msg_data
718 , x_no_violation => x_no_violation
719 );
720
721 IF l_return_status = fnd_api.g_ret_sts_error THEN
722 RAISE fnd_api.g_exc_error;
723 END IF ;
724
725 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
726 RAISE fnd_api.g_exc_unexpected_error;
727 END IF;
728
729 x_return_status := l_return_status;
730
731 EXCEPTION
732
733 WHEN fnd_api.g_exc_error THEN
734 x_return_status := fnd_api.g_ret_sts_error;
735
736 -- Get message count and data
737 fnd_msg_pub.count_and_get
738 ( p_count => x_msg_count
739 , p_data => x_msg_data
740 );
741
742 WHEN fnd_api.g_exc_unexpected_error THEN
743 x_return_status := fnd_api.g_ret_sts_unexp_error ;
744
745 -- Get message count and data
746 fnd_msg_pub.count_and_get
747 ( p_count => x_msg_count
748 , p_data => x_msg_data
749 );
750
751 WHEN OTHERS THEN
752 x_return_status := fnd_api.g_ret_sts_unexp_error ;
753
754 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
755 THEN
756 fnd_msg_pub.add_exc_msg
757 ( g_pkg_name
758 , l_api_name
759 );
760 END IF;
761
762 -- Get message count and data
763 fnd_msg_pub.count_and_get
764 ( p_count => x_msg_count
765 , p_data => x_msg_data
766 );
767
768 END do_check;
769
770 END inv_quantity_tree_pub;