[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_EXTN_PVT
Source
1 PACKAGE BODY wms_rule_extn_pvt AS
2 /* $Header: WMSVRXTB.pls 120.5.12010000.5 2008/11/06 11:01:36 avuppala ship $ */
3 --
4 -- File : WMSVPPTB.pls
5 -- Content : WMS_Test_Pub package body
6 -- Description : wms rules engine private API's
7 -- Notes :
8 -- Modified : 05/18/05 rambrose created orginal file
9 --
10 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_rule_extn_PVT';
11 g_debug NUMBER;
12
13
14 TYPE numtbltype IS TABLE OF NUMBER
15 INDEX BY BINARY_INTEGER;
16
17 --Procedures for logging messages
18 PROCEDURE log_event(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
19 l_module VARCHAR2(255);
20 BEGIN
21 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
22 inv_log_util.trace(p_message, l_module, 9);
23 END log_event;
24
25 PROCEDURE log_error(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
26 l_module VARCHAR2(255);
27 BEGIN
28 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
29 inv_log_util.trace(p_message, l_module, 9);
30 END log_error;
31
32 PROCEDURE log_error_msg(p_api_name VARCHAR2, p_label VARCHAR2) IS
33 l_module VARCHAR2(255);
34 BEGIN
35 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
36 inv_log_util.trace('err:', l_module, 9);
37 END log_error_msg;
38
39 PROCEDURE log_procedure(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
40 l_module VARCHAR2(255);
41 BEGIN
42 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
43 inv_log_util.trace(p_message, l_module, 9);
44 END log_procedure;
45
46 PROCEDURE log_statement(p_api_name VARCHAR2, p_label VARCHAR2, p_message VARCHAR2) IS
47 l_module VARCHAR2(255);
48 BEGIN
49 l_module := 'wms.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
50 inv_log_util.trace(p_message, l_module, 9);
51 END log_statement;
52
53 -- Start of comments
54 -- Name : InitQtyTree
55 -- Function : Initializes quantity tree for picking and returns tree id.
56 -- Pre-reqs : none
57 -- Parameters :
58 -- x_return_status out varchar2(1)
59 -- x_msg_count out number
60 -- x_msg_data out varchar2(2000)
61 -- p_organization_id in number required
62 -- p_inventory_item_id in number required
63 -- p_transaction_source_type_id in number required
64 -- p_transaction_source_id in number required
65 -- p_trx_source_line_id in number required
66 -- p_trx_source_delivery_id in number required
67 -- p_transaction_source_name in varchar2 required
68 -- p_tree_mode in number required
69 -- x_tree_id out number
70 -- Notes : privat procedure for internal use only
71 -- End of comments
72
73 procedure InitQtyTree (
74 x_return_status out nocopy varchar2
75 ,x_msg_count out nocopy number
76 ,x_msg_data out nocopy varchar2
77 ,p_organization_id in number
78 ,p_inventory_item_id in number
79 ,p_transaction_source_type_id in number
80 ,p_transaction_source_id in number
81 ,p_trx_source_line_id in number
82 ,p_trx_source_delivery_id in number
83 ,p_transaction_source_name in varchar2
84 ,p_tree_mode in number
85 ,x_tree_id out nocopy number
86 ) is
87
88 l_api_name VARCHAR2(30) := 'InitQtyTree';
89 l_rev_control_code MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE%type;
90 l_lot_control_code MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE%type;
91 l_ser_control_code MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE%type;
92 l_is_revision_control boolean;
93 l_is_lot_control boolean;
94 l_is_serial_control boolean;
95 l_msg_data VARCHAR2(240);
96 l_transaction_source_id NUMBER;
97 l_trx_source_line_id NUMBER;
98 l_debug NUMBER;
99 cursor iteminfo is
100 select nvl(msi.REVISION_QTY_CONTROL_CODE,1)
101 ,nvl(msi.LOT_CONTROL_CODE,1)
102 ,nvl(msi.SERIAL_NUMBER_CONTROL_CODE,1)
103 from MTL_SYSTEM_ITEMS msi
104 where ORGANIZATION_ID = p_organization_id
105 and INVENTORY_ITEM_ID = p_inventory_item_id
106 ;
107 begin
108
109 IF (g_debug IS NULL) THEN
110 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
111 END IF;
112 l_debug := g_debug;
113 If (l_debug = 1) then
114 log_procedure(l_api_name, 'start', 'Start InitQtyTree');
115 End if;
116 /*--
117 -- debugging portion
118 -- can be commented ut for final code
119 IF inv_pp_debug.is_debug_mode THEN
120 inv_pp_debug.send_message_to_pipe('enter '||g_pkg_name||'.'||l_api_name);
121 END IF;
122 -- end of debugging section
123 -- */
124 open iteminfo;
125 fetch iteminfo into l_rev_control_code
126 ,l_lot_control_code
127 ,l_ser_control_code;
128 if iteminfo%notfound then
129 close iteminfo;
130 raise no_data_found;
131 end if;
132 close iteminfo;
133
134 if l_rev_control_code = 1 then
135 l_is_revision_control := false;
136 else
137 l_is_revision_control := true;
138 end if;
139 if l_lot_control_code = 1 then
140 l_is_lot_control := false;
141 else
142 l_is_lot_control := true;
143 end if;
144 if l_ser_control_code = 1 then
145 l_is_serial_control := false;
146 else
147 l_is_serial_control := true;
148 end if;
149
150 -- bug 2398927
151 --if source type id is 13 (inventory), don't pass in the demand
152 --source line and header info. This info was causing LPN putaway
153 -- to fall for unit effective items.
154 IF p_transaction_source_type_id IN (4,13) THEN
155 l_transaction_source_id := -9999;
156 l_trx_source_line_id := -9999;
157 ELSE l_transaction_source_id := p_transaction_source_id;
158 l_trx_source_line_id := p_trx_source_line_id;
159 END IF;
160
161 If (l_debug = 1) then
162 log_event(l_api_name, 'create_tree',
163 'Trying to create quantity tree in exclusive mode');
164 End if;
165
166 INV_Quantity_Tree_PVT.Create_Tree
167 (
168 p_api_version_number => 1.0
169 --,p_init_msg_list => fnd_api.g_false
170 ,x_return_status => x_return_status
171 ,x_msg_count => x_msg_count
172 ,x_msg_data => x_msg_data
173 ,p_organization_id => p_organization_id
174 ,p_inventory_item_id => p_inventory_item_id
175 ,p_tree_mode => p_tree_mode
176 ,p_is_revision_control => l_is_revision_control
177 ,p_is_lot_control => l_is_lot_control
178 ,p_is_serial_control => l_is_serial_control
179 ,p_asset_sub_only => FALSE
180 ,p_include_suggestion => TRUE
181 ,p_demand_source_type_id => p_transaction_source_type_id
182 ,p_demand_source_header_id => l_transaction_source_id
183 ,p_demand_source_line_id => l_trx_source_line_id
184 ,p_demand_source_name => p_transaction_source_name
185 ,p_demand_source_delivery => p_trx_source_delivery_id
186 ,p_lot_expiration_date => sysdate
187 ,p_onhand_source => inv_quantity_tree_pvt.g_all_subs
188 ,p_exclusive => inv_quantity_tree_pvt.g_exclusive
189 ,p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
190 ,x_tree_id => x_tree_id
191 );
192 --
193 If (l_debug = 1) then
194 log_event(l_api_name, 'create_tree_finished',
195 'Created quantity tree in exclusive mode');
196 End if;
197 /* -- debugging portion
198 -- can be commented ut for final code
199 IF inv_pp_debug.is_debug_mode THEN
200 inv_pp_debug.send_message_to_pipe('exit '||g_pkg_name||'.'||l_api_name);
201 END IF;
202 -- end of debugging section */
203 If (l_debug = 1) then
204 log_procedure(l_api_name, 'end', 'End InitQtyTree');
205 End if;
206 --
207 exception
208 when others then
209 if iteminfo%isopen then
210 close iteminfo;
211 end if;
212 x_return_status := fnd_api.g_ret_sts_unexp_error;
213 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
214 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
215 end if;
216 fnd_msg_pub.count_and_get( p_count => x_msg_count
217 ,p_data => x_msg_data );
218 If (l_debug = 1) then
219 log_error(l_api_name, 'error', 'Error in InitQtyTree - ' || x_msg_data);
220 End if;
221 end InitQtyTree;
222
223
224 PROCEDURE suggest_reservations(
225 p_api_version IN NUMBER
226 , p_init_msg_list IN VARCHAR2
227 , p_commit IN VARCHAR2
228 , p_validation_level IN NUMBER
229 , x_return_status OUT NOCOPY VARCHAR2
230 , x_msg_count OUT NOCOPY NUMBER
231 , x_msg_data OUT NOCOPY VARCHAR2
232 , p_transaction_temp_id IN NUMBER
233 , p_allow_partial_pick IN VARCHAR2
234 , p_suggest_serial IN VARCHAR2
235 , p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
236 , p_demand_source_type IN NUMBER
237 , p_demand_source_header_id IN NUMBER
238 , p_demand_source_line_id IN NUMBER
239 , p_demand_source_detail IN NUMBER DEFAULT NULL
240 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
241 , p_requirement_date IN DATE DEFAULT NULL
242 , p_suggestions OUT NOCOPY g_suggestion_list_rec_type
243 ) IS
244 l_api_version CONSTANT NUMBER := 1.0;
245 l_api_name VARCHAR2(30) := 'Suggest_Reservations';
246 l_qry_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
247 l_new_reservation inv_reservation_global.mtl_reservation_rec_type;
248 l_orig_reservation inv_reservation_global.mtl_reservation_rec_type;
249 l_last_reservation inv_reservation_global.mtl_reservation_rec_type;
250 -- Record for querying up matching reservations for the move order line
251 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
252 l_demand_reservations inv_reservation_global.mtl_reservation_tbl_type;
253 l_rsv_qty_available NUMBER;
254
255 l_rsv_qty2_available NUMBER; --BUG#7377744 Added a secondary quantity available to reserve to make it consistent with process_reservations call
256 l_new_reservation_id NUMBER;
257 l_qty_succ_reserved NUMBER;
258 l_rsv_index NUMTBLTYPE;
259 l_demand_info wsh_inv_delivery_details_v%ROWTYPE;
260 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
261 l_reserved_serials inv_reservation_global.serial_number_tbl_type;
262 l_suggested_serials inv_reservation_global.serial_number_tbl_type;
263 l_reservation_count_by_id NUMBER;
264 l_requirement_date DATE;
265 l_primary_uom_code VARCHAR2(10) ;
266 l_simulation_mode NUMBER;
267 l_simulation_id NUMBER;
268 l_api_error_code VARCHAR2(10);
269 l_return_value BOOLEAN;
270 l_message VARCHAR2(200);
271 l_reservable_type NUMBER;
272 i NUMBER;
273
274 first_pass BOOLEAN;
275 l_tree_id NUMBER;
276 l_qoh NUMBER;
277 l_rqoh NUMBER;
278 l_qr NUMBER;
279 l_qs NUMBER;
280 l_att NUMBER;
281 l_atr NUMBER;
282 l_allocation_quantity NUMBER;
283 l_sqoh NUMBER;
284 l_srqoh NUMBER;
285 l_sqr NUMBER;
286 l_sqs NUMBER;
287 l_satt NUMBER;
288 l_satr NUMBER;
289
290 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
291
292 l_last_sugg_str VARCHAR2(120);
293
294
295 CURSOR c_sugg_grp IS
296 SELECT from_organization_id
297 , lot_number
298 , revision
299 , from_subinventory_code
300 , from_locator_id
301 , lpn_id
302 , reservation_id
303 , sum(primary_quantity) primary_quantity
304 , sum(transaction_quantity) transaction_quantity
305 , sum(secondary_quantity) secondary_quantity
306 , revision || ' - ' || lot_number || ' - ' || from_subinventory_code || ' - ' || from_locator_id || ' - ' || lpn_id as sugg_str
307 FROM wms_transactions_temp
308 WHERE line_type_code = 2
309 GROUP BY from_organization_id,
310 lot_number, from_subinventory_code, revision,
311 from_locator_id, lpn_id, reservation_id
312 ORDER BY sugg_str, reservation_id;
313
314 CURSOR c_sugg_serials(lc_from_org NUMBER
315 , lc_from_sub VARCHAR2
316 , lc_from_loc NUMBER
317 , lc_from_rev VARCHAR2
318 , lc_lot_num VARCHAR2
319 , lc_lpn_id NUMBER
320 , lc_res_id NUMBER) IS
321 SELECT serial_number
322 FROM wms_transactions_temp
323 WHERE line_type_code = 2
324 AND from_organization_id = lc_from_org
325 AND from_subinventory_code = lc_from_sub
326 AND nvl(from_locator_id,-888) = nvl(lc_from_loc,-888)
327 AND nvl(revision,'@@@') = nvl(lc_from_rev,'@@@')
328 AND nvl(lot_number,'@@@') = nvl(lc_lot_num,'@@@')
329 AND nvl(lpn_id,-888) = nvl(lc_lpn_id, -888)
330 AND nvl(reservation_id,-888) = nvl(lc_res_id, -888);
331
332
333 CURSOR c_suggestions IS
334 SELECT from_organization_id
335 , to_organization_id
336 , revision
337 , lot_number
338 , lot_expiration_date
339 , from_subinventory_code
340 , to_subinventory_code
341 , from_locator_id
342 , to_locator_id
343 , lpn_id
344 , reservation_id
345 , serial_number
346 , grade_code
347 , from_cost_group_id
348 , to_cost_group_id
349 , sum(primary_quantity) primary_quantity
350 , sum(transaction_quantity) transaction_quantity
351 , sum(secondary_quantity) secondary_quantity
352 FROM wms_transactions_temp
353 WHERE line_type_code = 2
354 GROUP BY from_organization_id, to_organization_id, revision,
355 lot_number, lot_expiration_date, from_subinventory_code,
356 to_subinventory_code, from_locator_id, to_locator_id, lpn_id, reservation_id,
357 serial_number, grade_code, from_cost_group_id, to_cost_group_id;
358 BEGIN
359
360 g_debug := l_debug;
361
362 If l_debug = 1 THEN
363 log_procedure(l_api_name, 'start', 'Start suggest_reservations');
364 log_event(
365 l_api_name
366 , 'start_detail'
367 , 'Starting the WMS Rules engine Extention to create Rules Based reservations: '
368 || p_transaction_temp_id
369 );
370 End if;
371
372 -- Standard start of API savepoint
373 SAVEPOINT suggest_reservations_sa;
374
375 --
376 -- Standard Call to check for call compatibility
377 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
378 RAISE fnd_api.g_exc_unexpected_error;
379 END IF;
380
381 --
382 -- Initialize message list if p_init_msg_list is set to true
383 IF fnd_api.to_boolean(p_init_msg_list) THEN
384 fnd_msg_pub.initialize;
385 END IF;
386
387 log_procedure(l_api_name, 'start', 'Start suggest_reservations');
388 --
389 -- Initialisize API return status to access
390 x_return_status := fnd_api.g_ret_sts_success;
391 --
392
393 l_return_value := INV_CACHE.set_item_rec(p_mo_line_rec.organization_id,
394 p_mo_line_rec.inventory_item_id);
395 l_reservable_type:= INV_CACHE.item_rec.reservable_type;
396
397 IF l_reservable_type = 2 THEN
398 IF (l_debug = 1) THEN
399 log_error(l_api_name, 'Process_Reservations','Error - Item is not reservable');
400 END IF;
401
402 RAISE fnd_api.g_exc_error;
403 END IF;
404
405 /* Set Demand Info Record */
406 l_demand_info.oe_line_id := p_demand_source_line_id;
407
408 /* Call Process Reservations */
409 --Bug#7377744 : included secondary quantity available to reserve in the parameters
410 inv_pick_release_pvt.process_reservations(
411 x_return_status => x_return_status
412 , x_msg_count => x_msg_count
413 , x_msg_data => x_msg_data
414 , p_demand_info => l_demand_info
415 , p_mo_line_rec => p_mo_line_rec
416 , p_mso_line_id => p_demand_source_header_id
417 , p_demand_source_type => p_demand_source_type
418 , p_demand_source_name => p_demand_source_name
419 , p_allow_partial_pick => p_allow_partial_pick
420 , x_demand_rsvs_ordered => l_demand_rsvs_ordered
421 , x_rsv_qty_available => l_rsv_qty_available
422 ,x_rsv_qty2_available => l_rsv_qty2_available);
423
424
425 -- Return an error if the query reservations call failed
426 IF x_return_status <> fnd_api.g_ret_sts_success THEN
427 IF ( l_debug = 1 ) THEN
428 log_error(l_api_name, 'Suggest Reservations', 'l_return_status = '|| x_return_status);
429 log_error(l_api_name, 'Suggest Reservations', 'Process Reservations Failed ' || x_msg_data);
430 END IF;
431 RAISE fnd_api.g_exc_unexpected_error;
432 END IF;
433 /* Place reservation IDs into a table for easy access when creating new reservations */
434 IF l_demand_rsvs_ordered.count > 0 THEN
435 log_event(l_api_name, 'Suggest Reservations','# Reservations returned from Process Reservation : ' || l_demand_rsvs_ordered.count);
436 FOR i in l_demand_rsvs_ordered.First..l_demand_rsvs_ordered.Last LOOP
437 inv_reservation_pvt.print_rsv_rec(l_demand_rsvs_ordered (i));
438 l_rsv_index(l_demand_rsvs_ordered(i).reservation_id) := i;
439 END LOOP;
440 END IF;
441
442 DELETE FROM WMS_TRANSACTIONS_TEMP WHERE line_type_code = 2;
443
444 /* Call create suggestions */
445 wms_engine_pvt.create_suggestions(
446 p_api_version => 1.0
447 , p_init_msg_list => fnd_api.g_true
448 , p_commit => fnd_api.g_false
449 , p_validation_level => NULL
450 , x_return_status => x_return_status
451 , x_msg_count => x_msg_count
452 , x_msg_data => x_msg_data
453 , p_transaction_temp_id => p_mo_line_rec.line_id
454 , p_reservations => l_demand_rsvs_ordered
455 , p_suggest_serial => p_suggest_serial
456 , p_simulation_mode => wms_engine_pvt.g_pick_full_mode
457 , p_simulation_id => NULL
458 , p_plan_tasks => FALSE
459 , p_quick_pick_flag => 'N'
460 );
461 IF x_return_status <> fnd_api.g_ret_sts_success THEN
462 IF ( l_debug = 1 ) THEN
463 log_error(l_api_name, 'Suggest Reservations', 'l_return_status = '|| x_return_status);
464 log_error(l_api_name, 'Suggest Reservations', 'Detailing Failed ');
465 END IF;
466 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => l_message, p_encoded => 'F');
467
468 IF (x_msg_count = 0) THEN
469 IF ( l_debug = 1) THEN
470 log_error(l_api_name, 'Suggest Reservations', 'no message from detailing engine');
471 END IF;
472 ELSIF (x_msg_count = 1) THEN
473 IF ( l_debug = 1 ) THEN
474 log_error(l_api_name, 'Suggest_Reservations', l_message);
475 END IF;
476 ELSE
477 FOR i IN 1 .. x_msg_count LOOP
478 l_message := fnd_msg_pub.get(i, 'F');
479 IF ( l_debug = 1) THEN
480 log_error(l_api_name, 'Suggest_Reservations', l_message);
481 END IF;
482 END LOOP;
483
484 fnd_msg_pub.delete_msg();
485 END IF;
486
487 ROLLBACK TO suggest_reservations_sa;
488
489 fnd_message.set_name('INV', 'INV_DETAILING_FAILED');
490 fnd_message.set_token('LINE_NUM', TO_CHAR(p_mo_line_rec.line_number));
491 fnd_msg_pub.ADD;
492 x_msg_count := 1;
493 RAISE fnd_api.g_exc_unexpected_error;
494 END IF;
495
496 l_suggested_serials := l_dummy_sn;
497 l_requirement_date := nvl(p_requirement_date, sysdate);
498
499 first_pass := TRUE;
500 FOR l_grp_sugg_rec in c_sugg_grp LOOP
501
502 IF first_pass THEN
503 InitQtyTree ( x_return_status
504 ,x_msg_count
505 ,x_msg_data
506 ,p_mo_line_rec.organization_id
507 ,p_mo_line_rec.inventory_item_id
508 ,p_demand_source_type
509 ,p_demand_source_header_id
510 ,p_demand_source_line_id
511 ,p_demand_source_detail
512 ,p_demand_source_name
513 ,INV_Quantity_Tree_PVT.g_transaction_mode
514 ,l_tree_id
515 );
516 if x_return_status = fnd_api.g_ret_sts_unexp_error then
517 raise fnd_api.g_exc_unexpected_error;
518 elsif x_return_status = fnd_api.g_ret_sts_error then
519 raise fnd_api.g_exc_error;
520 end if;
521 first_pass := FALSE;
522 END IF;
523
524 -- Update quantity tree for this suggested quantity
525 IF l_debug = 1 THEN
526 log_statement(l_api_name, 'update_tree', 'Updating qty tree');
527 END IF;
528
529 inv_quantity_tree_pvt.update_quantities
530 (
531 p_api_version_number => 1.0
532 , p_init_msg_lst => fnd_api.g_false
533 , x_return_status => x_return_status
534 , x_msg_count => x_msg_count
535 , x_msg_data => x_msg_data
536 , p_tree_id => l_tree_id
537 , p_revision => l_grp_sugg_rec.revision
538 , p_lot_number => l_grp_sugg_rec.lot_number
539 , p_subinventory_code => l_grp_sugg_rec.from_subinventory_code
540 , p_locator_id => l_grp_sugg_rec.from_locator_id
541 , p_primary_quantity => -1 * l_grp_sugg_rec.primary_quantity
542 , p_secondary_quantity => -1 * l_grp_sugg_rec.secondary_quantity -- INVCONV
543 , p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
544 , x_qoh => l_qoh
545 , x_rqoh => l_rqoh
546 , x_qr => l_qr
547 , x_qs => l_qs
548 , x_att => l_att
549 , x_atr => l_atr
550 , x_sqoh => l_sqoh -- INVCONV
551 , x_srqoh => l_srqoh -- INVCONV
552 , x_sqr => l_sqr -- INVCONV
553 , x_sqs => l_sqs -- INVCONV
554 , x_satt => l_satt -- INVCONV
555 , x_satr => l_satr -- INVCONV
556 , p_transfer_subinventory_code => null
557 , p_cost_group_id => null
558 , p_lpn_id => l_grp_sugg_rec.lpn_id
559 );
560
561 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
562 IF l_debug = 1 THEN
563 log_statement(l_api_name, 'uerr_update_qty', 'Unexpected error in inv_quantity_tree_pvt.update_quantities');
564 END IF;
565
566 RAISE fnd_api.g_exc_unexpected_error;
567 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
568 IF l_debug = 1 THEN
569 log_statement(l_api_name, 'err_update_qty', 'Error in inv_quantity_tree_pvt.update_quantities');
570 END IF;
571 RAISE fnd_api.g_exc_error;
572 END IF;
573
574 /* Get Original reservation for which these grouped suggestions were created */
575 -- {{ Test Case # UTK- REALLOC
576 -- Description : API called with either a) No existing Reservation or
577 -- b) A single existing Reservation or c) Multiple existing Reservations }}
578 IF l_grp_sugg_rec.reservation_id IS NOT NULL THEN
579 log_event(l_api_name, 'Suggest Reservations','Sugg Res : ' || l_grp_sugg_rec.reservation_id);
580 l_orig_reservation := l_demand_rsvs_ordered(l_rsv_index(l_grp_sugg_rec.reservation_id));
581 l_primary_uom_code := l_orig_reservation.primary_uom_code;
582 l_new_reservation := l_orig_reservation;
583 END IF;
584
585 IF l_debug = 1 THEN
586 log_event(l_api_name, 'Suggest Reservations', 'Suggested lot_number : ' || l_grp_sugg_rec.lot_number);
587 log_event(l_api_name, 'Suggest Reservations', 'Suggested subinventory_code : ' || l_grp_sugg_rec.from_subinventory_code);
588 log_event(l_api_name, 'Suggest Reservations', 'Suggested locator id : ' || l_grp_sugg_rec.from_locator_id);
589 log_event(l_api_name, 'Suggest Reservations', 'Suggested lpn_id : ' || l_grp_sugg_rec.lpn_id);
590 log_event(l_api_name, 'Suggest Reservations', 'Suggested pri quantity : ' || l_grp_sugg_rec.primary_quantity);
591 log_event(l_api_name, 'Suggest Reservations', 'Suggested sec quantity : ' || l_grp_sugg_rec.secondary_quantity);
592 END IF;
593
594 /* Set new_rsv record from the grouped suggestion record */
595 l_new_reservation.organization_id := p_mo_line_rec.organization_id;
596 l_new_reservation.inventory_item_id := p_mo_line_rec.inventory_item_id;
597 l_new_reservation.supply_source_type_id := inv_reservation_global.g_source_type_inv;
598 l_new_reservation.revision := l_grp_sugg_rec.revision;
599 l_new_reservation.lot_number := l_grp_sugg_rec.lot_number;
600 l_new_reservation.subinventory_code := l_grp_sugg_rec.from_subinventory_code;
601 l_new_reservation.locator_id := l_grp_sugg_rec.from_locator_id;
602 l_new_reservation.lpn_id := l_grp_sugg_rec.lpn_id;
603
604 l_new_reservation.primary_uom_code := l_primary_uom_code;
605 l_new_reservation.reservation_uom_code := p_mo_line_rec.uom_code;
606 l_new_reservation.secondary_uom_code := p_mo_line_rec.secondary_uom;
607
608 l_new_reservation.primary_reservation_quantity := l_grp_sugg_rec.primary_quantity;
609 l_new_reservation.secondary_reservation_quantity := l_grp_sugg_rec.secondary_quantity;
610 l_new_reservation.demand_source_type_id := p_demand_source_type;
611 l_new_reservation.demand_source_header_id := p_demand_source_header_id;
612 l_new_reservation.demand_source_line_id := p_demand_source_line_id;
613 l_new_reservation.demand_source_name := p_demand_source_name;
614 l_new_reservation.requirement_date := l_requirement_date;
615
616 IF p_suggest_serial = 'Y' THEN
617 l_suggested_serials := l_dummy_sn;
618 IF ( l_debug = 1 ) THEN
619 log_event(l_api_name,'Suggest_Reservations','Get Serials Suggested for this Reservation');
620 END IF;
621 For l_ser_rec in c_sugg_serials(l_new_reservation.organization_id, l_new_reservation.subinventory_code,
622 l_new_reservation.locator_id, l_new_reservation.revision, l_new_reservation.lot_number,
623 l_new_reservation.lpn_id, l_grp_sugg_rec.reservation_id
624 ) LOOP
625 l_suggested_serials(i).inventory_item_id := p_mo_line_rec.inventory_item_id;
626 l_suggested_serials(i).serial_number := l_ser_rec.serial_number;
627 END LOOP;
628 END IF;
629
630 IF l_grp_sugg_rec.reservation_id IS NOT NULL THEN
631 IF l_debug = 1 THEN
632 log_event(l_api_name, 'Suggest Reservations', 'Original revision : ' || l_orig_reservation.revision);
633 log_event(l_api_name, 'Suggest Reservations', 'Original lot_number : ' || l_orig_reservation.lot_number);
634 log_event(l_api_name, 'Suggest Reservations', 'Original subinventory_code : ' || l_orig_reservation.subinventory_code);
635 log_event(l_api_name, 'Suggest Reservations', 'Original locator id : ' || l_orig_reservation.locator_id);
636 log_event(l_api_name, 'Suggest Reservations', 'Original lpn_id : ' || l_orig_reservation.lpn_id);
637 log_event(l_api_name, 'Suggest Reservations', 'Original pri quantity : ' || l_orig_reservation.primary_reservation_quantity);
638 log_event(l_api_name, 'Suggest Reservations', 'Original sec quantity : ' || l_orig_reservation.secondary_reservation_quantity);
639 END IF;
640
641 l_last_sugg_str := l_grp_sugg_rec.sugg_str;
642 l_last_reservation := l_new_reservation;
643
644 /* Check whether original reservation is equal to the allocated record */
645 /* If not equal to the original reservation the transfer the allocated quantity to the new reservation */
646 IF ((nvl(l_orig_reservation.lot_number,'-999') <> nvl(l_new_reservation.lot_number, '-999')) OR
647 (nvl(l_orig_reservation.revision,'-999') <> nvl(l_new_reservation.revision, '-999')) OR
648 (nvl(l_orig_reservation.subinventory_code,'-999') <> nvl(l_new_reservation.subinventory_code, '-999')) OR
649 (nvl(l_orig_reservation.locator_id,'-999') <> nvl(l_new_reservation.locator_id, '-999')) OR
650 (nvl(l_orig_reservation.lpn_id,'-999') <> nvl(l_new_reservation.lpn_id, '-999'))) THEN
651
652 -- Setting this to null will allow the reservation to be added to other reservations
653 -- with the same controls that may have been created during this process
654 l_new_reservation.reservation_id := NULL;
655
656 inv_reservation_pvt.Transfer_Reservation (
657 p_api_version_number => 1.0
658 , p_init_msg_lst => fnd_api.g_true
659 , x_return_status => x_return_status
660 , x_msg_count => x_msg_count
661 , x_msg_data => x_msg_data
662 , p_original_rsv_rec => l_orig_reservation
663 , p_to_rsv_rec => l_new_reservation
664 , p_original_serial_number => l_dummy_sn
665 , p_validation_flag => fnd_api.g_false
666 , x_reservation_id => l_new_reservation_id
667 );
668
669 -- Bug 6719290 Return an error if the transfer reservation call failed
670 IF x_return_status = fnd_api.g_ret_sts_error THEN
671 IF (l_debug = 1) THEN
672 log_error(l_api_name, 'Suggest_Reservations','expected error in transfer reservation');
673 END IF;
674 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
675 fnd_msg_pub.ADD;
676 RAISE fnd_api.g_exc_error;
677 END IF;
678
679 -- Return an error if the transfer reservation call failed
680 IF x_return_status <> fnd_api.g_ret_sts_success THEN
681 IF (l_debug = 1) THEN
682 log_error(l_api_name, 'Suggest_Reservations','error in transfer reservation');
683 END IF;
684 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
685 fnd_msg_pub.ADD;
686 RAISE fnd_api.g_exc_unexpected_error;
687 END IF;
688 IF l_debug = 1 THEN
689 log_event(l_api_name, 'Suggest Reservations', 'After calling transfer from ' || l_orig_reservation.reservation_id || ' to ' || l_new_reservation_id);
690 END IF;
691 ELSE
692 IF l_debug = 1 THEN
693 log_event(l_api_name, 'Suggest Reservations', 'Reservation already Exists and is Detailed: ID = ' || l_grp_sugg_rec.reservation_id);
694 END IF;
695 END IF;
696 -- ELSE reservation ID is null AND not the same inventory controls
697 ELSIF l_grp_sugg_rec.sugg_str = nvl(l_last_sugg_str,'@@@') THEN
698 /* Update the current reservation with the quantities from the new reservation */
699 l_new_reservation.primary_reservation_quantity := l_last_reservation.primary_reservation_quantity + l_new_reservation.primary_reservation_quantity;
700 l_new_reservation.secondary_reservation_quantity := l_last_reservation.secondary_reservation_quantity + l_new_reservation.secondary_reservation_quantity;
701
702 inv_reservation_pvt.update_reservation(
703 p_api_version_number => 1.0
704 , p_init_msg_lst => fnd_api.g_false
705 , x_return_status => x_return_status
706 , x_msg_count => x_msg_count
707 , x_msg_data => x_msg_data
708 , p_original_rsv_rec => l_last_reservation
709 , p_to_rsv_rec => l_new_reservation
710 , p_original_serial_number => l_dummy_sn
711 , p_to_serial_number => l_reserved_serials
712 , p_validation_flag => 'Q'
713 , p_check_availability => fnd_api.g_false
714 );
715
716
717 -- Return an error if the update reservation call failed
718 IF x_return_status <> fnd_api.g_ret_sts_success THEN
719 IF (l_debug = 1) THEN
720 log_error(l_api_name, 'Suggest_Reservations','error in update reservation');
721 END IF;
722 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
723 fnd_msg_pub.ADD;
724 RAISE fnd_api.g_exc_unexpected_error;
725 END IF;
726
727 ELSE -- reservtion ID is null
728 /* Create new reservation and set as current reservation */
729 IF l_debug = 1 THEN
730 log_event(l_api_name, 'Suggest Reservations', 'Defaulting vales to create the reservations');
731 END IF;
732
733 l_new_reservation.reservation_id := NULL; -- cannot know
734 l_new_reservation.demand_source_delivery := NULL;
735 l_new_reservation.primary_uom_id := NULL;
736 l_new_reservation.secondary_uom_id := NULL;
737 l_new_reservation.reservation_uom_code := NULL;
738 l_new_reservation.reservation_uom_id := NULL;
739 l_new_reservation.reservation_quantity := NULL;
740 l_new_reservation.autodetail_group_id := NULL;
741 l_new_reservation.external_source_code := NULL;
742 l_new_reservation.external_source_line_id := NULL;
743 l_new_reservation.supply_source_header_id := NULL;
744 l_new_reservation.supply_source_line_id := NULL;
745 l_new_reservation.supply_source_name := NULL;
746 l_new_reservation.supply_source_line_detail := NULL;
747 l_new_reservation.subinventory_id := NULL;
748 l_new_reservation.lot_number_id := NULL;
749 l_new_reservation.pick_slip_number := NULL;
750 l_new_reservation.attribute_category := NULL;
751 l_new_reservation.attribute1 := NULL;
752 l_new_reservation.attribute2 := NULL;
753 l_new_reservation.attribute3 := NULL;
754 l_new_reservation.attribute4 := NULL;
755 l_new_reservation.attribute5 := NULL;
756 l_new_reservation.attribute6 := NULL;
757 l_new_reservation.attribute7 := NULL;
758 l_new_reservation.attribute8 := NULL;
759 l_new_reservation.attribute9 := NULL;
760 l_new_reservation.attribute10 := NULL;
761 l_new_reservation.attribute11 := NULL;
762 l_new_reservation.attribute12 := NULL;
763 l_new_reservation.attribute13 := NULL;
764 l_new_reservation.attribute14 := NULL;
765 l_new_reservation.attribute15 := NULL;
766 l_new_reservation.ship_ready_flag := NULL;
767 l_new_reservation.detailed_quantity := 0;
768
769 inv_reservation_pub.create_reservation(
770 p_api_version_number => 1.0
771 , p_init_msg_lst => fnd_api.g_false
772 , x_return_status => x_return_status
773 , x_msg_count => x_msg_count
774 , x_msg_data => x_msg_data
775 , p_rsv_rec => l_new_reservation
776 , p_serial_number => l_suggested_serials
777 , x_serial_number => l_reserved_serials
778 , p_partial_reservation_flag => fnd_api.g_true
779 , p_force_reservation_flag => fnd_api.g_false
780 , p_validation_flag => 'Q'
781 , x_quantity_reserved => l_qty_succ_reserved
782 , x_reservation_id => l_new_reservation_id
783 );
784
785 IF l_debug = 1 THEN
786 log_event(l_api_name, 'Suggest Reservations', 'After creating the reservations: status =' || x_return_status);
787 log_event(l_api_name, 'Suggest Reservations', 'After creating the reservations: Reservation ID =' || l_new_reservation_id);
788 END IF;
789 -- Return an error if the create reservation call failed
790 IF x_return_status <> fnd_api.g_ret_sts_success THEN
791 IF (l_debug = 1) THEN
792 log_error(l_api_name, 'Process_Reservations','error in create reservation');
793 END IF;
794 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
795 fnd_msg_pub.ADD;
796 RAISE fnd_api.g_exc_unexpected_error;
797 END IF;
798 END IF;
799 END LOOP;
800
801 -- Return the suggestions
802 OPEN c_suggestions;
803 FETCH c_suggestions
804 BULK COLLECT INTO
805 p_suggestions.from_organization_id
806 , p_suggestions.to_organization_id
807 , p_suggestions.revision
808 , p_suggestions.lot_number
809 , p_suggestions.lot_expiration_date
810 , p_suggestions.from_subinventory_code
811 , p_suggestions.to_subinventory_code
812 , p_suggestions.from_locator_id
813 , p_suggestions.to_locator_id
814 , p_suggestions.lpn_id
815 , p_suggestions.reservation_id
816 , p_suggestions.serial_number
817 , p_suggestions.grade_code
818 , p_suggestions.from_cost_group_id
819 , p_suggestions.to_cost_group_id
820 , p_suggestions.primary_quantity
821 , p_suggestions.transaction_quantity
822 , p_suggestions.secondary_quantity;
823 CLOSE c_suggestions;
824
825 EXCEPTION
826 WHEN fnd_api.g_exc_error THEN
827 --ROLLBACK TO suggest_reservations_sa;
828 x_return_status := fnd_api.g_ret_sts_error;
829 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
830 IF l_debug = 1 THEN
831 log_error(l_api_name, 'error', 'Error in suggest_reservations - ' || x_msg_data);
832 END IF ;
833 --
834 WHEN fnd_api.g_exc_unexpected_error THEN
835 --ROLLBACK TO suggest_reservations_sa;
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
838 IF l_debug = 1 THEN
839 log_error(l_api_name, 'unexp_error', 'Unexpected error ' || 'in suggest_reservations - ' || x_msg_data);
840 END IF;
841 --
842 WHEN OTHERS THEN
843 ROLLBACK TO suggest_reservations_sa;
844 x_return_status := fnd_api.g_ret_sts_unexp_error;
845
846 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
847 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
848 END IF;
849 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
850 IF l_debug = 1 THEN
851 log_error(l_api_name, 'other_error', 'Other error ' || 'in suggest_reservations - ' || x_msg_data);
852 log_error(l_api_name, 'other_error', 'SQL Error ' || SQLERRM);
853 END IF;
854 END suggest_reservations;
855
856 END wms_rule_extn_pvt;