[Home] [Help]
PACKAGE BODY: APPS.INV_QUANTITY_TREE_PUB
Source
1 PACKAGE BODY inv_quantity_tree_pub as
2 /* $Header: INVPQTTB.pls 120.5.12010000.2 2009/06/12 06:07:46 ksivasa ship $*/
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 --Bug 8571657
542 l_override_neg_for_backflush := NVL(fnd_profile.value('INV_OVERRIDE_NEG_FOR_BACKFLUSH'), 2);
543 BEGIN
544 SELECT NEGATIVE_INV_RECEIPT_CODE
545 INTO l_neg_inv_rcpt
546 FROM MTL_PARAMETERS
547 WHERE ORGANIZATION_ID = p_organization_id;
548 EXCEPTION
549 WHEN OTHERS THEN
550 l_neg_inv_rcpt := 2;
551 END;
552
553
554 /*Bug:5209598. Following condition has been modified to not error out in the case where
555 we are trying to complete an assembly that has a component with
556 transaction_quantity(p_primary_quantity of the component) less than 0,
557 abs(transaction_quantity)< ATT and there exists a reservation for this component,
558 eventhough the profile 'INV_OVERRIDE_RSV_FOR_BACKFLUSH' is set to 'YES'.
559 Now the following condition is modified to allow this transaction by checking the profile */
560
561 --Bug 1641063
562 --Check for sufficient qty when transaction mode was wrong
563 /*Bug 5451638. Modified the following condition to check only for profile
564 l_override_neg_for_backflush only ignoring l_override_rsv_for_backflush
565 for WIP transactions*/
566 IF (p_tree_mode = g_reservation_mode
567 AND p_primary_quantity > 0
568 AND p_primary_quantity > l_atr )
569 OR (p_tree_mode = g_transaction_mode
570 AND p_quantity_type = g_qoh
571 AND p_primary_quantity < 0
572 AND abs(p_primary_quantity) > l_att
573 AND NOT( ( p_demand_source_type_id = inv_globals.G_SOURCETYPE_WIP AND
574 l_override_neg_for_backflush = 1 )
575 OR
576 (l_neg_inv_rcpt = 1)
577 )
578 AND l_att > 0)
579 THEN
580 fnd_message.set_name('INV', 'INV_QTY_LESS_OR_EQUAL');
581 fnd_msg_pub.add;
582 RAISE fnd_api.g_exc_error;
583 END IF;
584
585 inv_quantity_tree_grp.update_quantities
586 (
587 p_api_version_number => 1.0
588 , p_init_msg_lst => p_init_msg_lst
589 , x_return_status => l_return_status
590 , x_msg_count => x_msg_count
591 , x_msg_data => x_msg_data
592 , p_tree_id => l_tree_id
593 , p_revision => p_revision
594 , p_lot_number => p_lot_number
595 , p_subinventory_code => p_subinventory_code
596 , p_locator_id => p_locator_id
597 , p_primary_quantity => p_primary_quantity
598 , p_quantity_type => p_quantity_type
599 , p_secondary_quantity => p_secondary_quantity -- invConv change
600 , x_qoh => l_qoh
601 , x_rqoh => l_rqoh
602 , x_qr => l_qr
603 , x_qs => l_qs
604 , x_att => l_att
605 , x_atr => l_atr
606 , x_sqoh => l_sqoh -- invConv change
607 , x_srqoh => l_srqoh -- invConv change
608 , x_sqr => l_sqr -- invConv change
609 , x_sqs => l_sqs -- invConv change
610 , x_satt => l_satt -- invConv change
611 , x_satr => l_satr -- invConv change
612 , p_transfer_subinventory_code => p_transfer_subinventory_code
613 , p_cost_group_id => p_cost_group_id
614 , p_containerized => p_containerized
615 , p_lpn_id => p_lpn_id
616 , p_transfer_locator_id => p_transfer_locator_id
617 );
618
619 IF l_return_status = fnd_api.g_ret_sts_error THEN
620 RAISE fnd_api.g_exc_error;
621 END IF ;
622
623 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
624 RAISE fnd_api.g_exc_unexpected_error;
625 END IF;
626
627 x_qoh := l_qoh;
628 x_rqoh := l_rqoh;
629 x_qr := l_qr;
630 x_qs := l_qs;
631 x_att := l_att;
632 x_atr := l_atr;
633 x_sqoh := l_sqoh;
634 x_srqoh := l_srqoh;
635 x_sqr := l_sqr;
636 x_sqs := l_sqs;
637 x_satt := l_satt;
638 x_satr := l_satr;
639 x_return_status := l_return_status;
640
641 EXCEPTION
642
643 WHEN fnd_api.g_exc_error THEN
644 x_return_status := fnd_api.g_ret_sts_error;
645
646 -- Get message count and data
647 fnd_msg_pub.count_and_get
648 ( p_count => x_msg_count
649 , p_data => x_msg_data
650 );
651
652 WHEN fnd_api.g_exc_unexpected_error THEN
653 x_return_status := fnd_api.g_ret_sts_unexp_error ;
654
655 -- Get message count and data
656 fnd_msg_pub.count_and_get
657 ( p_count => x_msg_count
658 , p_data => x_msg_data
659 );
660
661 WHEN OTHERS THEN
662 x_return_status := fnd_api.g_ret_sts_unexp_error ;
663
664 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
665 THEN
666 fnd_msg_pub.add_exc_msg
667 ( g_pkg_name
668 , l_api_name
669 );
670 END IF;
671
672 -- Get message count and data
673 fnd_msg_pub.count_and_get
674 ( p_count => x_msg_count
675 , p_data => x_msg_data
676 );
677
678 END update_quantities;
679
680 -- Procedure
681 -- do_check
682 -- Description
683 -- check whether the updates done in all trees so far are still valid
684 -- Return
685 -- x_no_violation = true if no violation has found
686 -- , otherwise = false
687 PROCEDURE do_check
688 ( p_api_version_number IN NUMBER
689 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
690 , x_return_status OUT NOCOPY VARCHAR2
691 , x_msg_count OUT NOCOPY NUMBER
692 , x_msg_data OUT NOCOPY VARCHAR2
693 , x_no_violation OUT NOCOPY BOOLEAN
694 ) IS
695 l_api_version_number CONSTANT NUMBER := 1.0;
696 l_api_name CONSTANT VARCHAR2(30) := 'Do_Check';
697 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
698 BEGIN
699 -- Standard call to check for call compatibility
700 IF NOT fnd_api.compatible_api_call(l_api_version_number
701 , p_api_version_number
702 , l_api_name
703 , G_PKG_NAME
704 ) THEN
705 RAISE fnd_api.g_exc_unexpected_error;
706 END IF;
707
708 -- Initialize message list.
709 IF fnd_api.to_boolean(p_init_msg_lst) THEN
710 fnd_msg_pub.initialize;
711 END IF;
712
713 inv_quantity_tree_grp.do_check
714 (
715 p_api_version_number => 1.0
716 , p_init_msg_lst => p_init_msg_lst
717 , x_return_status => l_return_status
718 , x_msg_count => x_msg_count
719 , x_msg_data => x_msg_data
720 , x_no_violation => x_no_violation
721 );
722
723 IF l_return_status = fnd_api.g_ret_sts_error THEN
724 RAISE fnd_api.g_exc_error;
725 END IF ;
726
727 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
728 RAISE fnd_api.g_exc_unexpected_error;
729 END IF;
730
731 x_return_status := l_return_status;
732
733 EXCEPTION
734
735 WHEN fnd_api.g_exc_error THEN
736 x_return_status := fnd_api.g_ret_sts_error;
737
738 -- Get message count and data
739 fnd_msg_pub.count_and_get
740 ( p_count => x_msg_count
741 , p_data => x_msg_data
742 );
743
744 WHEN fnd_api.g_exc_unexpected_error THEN
745 x_return_status := fnd_api.g_ret_sts_unexp_error ;
746
747 -- Get message count and data
748 fnd_msg_pub.count_and_get
749 ( p_count => x_msg_count
750 , p_data => x_msg_data
751 );
752
753 WHEN OTHERS THEN
754 x_return_status := fnd_api.g_ret_sts_unexp_error ;
755
756 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
757 THEN
758 fnd_msg_pub.add_exc_msg
759 ( g_pkg_name
760 , l_api_name
761 );
762 END IF;
763
764 -- Get message count and data
765 fnd_msg_pub.count_and_get
766 ( p_count => x_msg_count
767 , p_data => x_msg_data
768 );
769
770 END do_check;
771
772 END inv_quantity_tree_pub;