1 PACKAGE BODY WMS_OP_RUNTIME_PUB_APIS AS
2 /* $Header: WMSOPPBB.pls 120.9.12020000.3 2013/03/10 13:28:25 srsomasu ship $*/
3
4 --
5 -- File : WMSOPPBB.pls
6 -- Content : WMS_OP_RUNTIME_PUB_APIS package Body
7 -- Description : WMS Operation Plan Run-time APIs
8 -- Notes :
9 -- Modified : 10/21/2002 lezhang created
10
11 --g_txn_type_so_stg_xfr NUMBER := inv_globals.g_type_transfer_order_stgxfr;
12 g_sourcetype_salesorder NUMBER := inv_globals.g_sourcetype_salesorder;
13 g_action_stgxfr NUMBER := inv_globals.g_action_stgxfr;
14 g_sourcetype_intorder NUMBER := inv_globals.g_sourcetype_intorder;
15 g_op_dest_sys_suggested NUMBER := wms_globals.g_op_dest_sys_suggested;
16 g_op_dest_api NUMBER := wms_globals.g_op_dest_api;
17 g_op_dest_pre_specified NUMBER := wms_globals.g_op_dest_pre_specified;
18 g_op_dest_rules_engine NUMBER := wms_globals.g_op_dest_rules_engine;
19 g_wms_task_type_pick NUMBER := wms_globals.g_wms_task_type_pick;
20 g_wms_task_type_stg_move NUMBER := wms_globals.g_wms_task_type_stg_move;
21 g_op_drop_lpn_no_lpn NUMBER := wms_globals.g_op_drop_lpn_no_lpn;
22 g_op_drop_lpn_optional NUMBER := wms_globals.g_op_drop_lpn_optional;
23
24 g_ret_sts_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
25 g_ret_sts_unexp_error VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
26 g_ret_sts_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
27
28 PROCEDURE print_debug(p_err_msg VARCHAR2,
29 p_level NUMBER)
30 IS
31 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
32 BEGIN
33
34 inv_mobile_helper_functions.tracelog
35 (p_err_msg => p_err_msg,
36 p_module => 'WMS_OP_RUNTIME_PUB_APIS',
37 p_level => p_level);
38
39 --dbms_output.put_line(p_err_msg);
40 END print_debug;
41
42
43
44 -- API name :
45 -- Type : Public
46 -- Function :
47 -- Pre-reqs :
48 --
49 --
50 -- Parameters :
51 -- Output:
52 --
53 -- Input:
54 --
55 --
56 -- Version
57 -- Currently version is 1.0
58 --
59
60
61 PROCEDURE update_drop_locator_for_task
62 (x_return_status OUT nocopy VARCHAR2,
63 x_message OUT nocopy VARCHAR2,
64 x_drop_lpn_option OUT nocopy NUMBER,
65 p_transfer_lpn_id IN NUMBER
66 )
67
68 IS
69
70 TYPE TransactionTempIDTable IS TABLE OF NUMBER
71 INDEX BY BINARY_INTEGER;
72 TYPE PickRelDestZoneIDTable IS TABLE OF NUMBER
73 INDEX BY BINARY_INTEGER;
74 TYPE PickRelDestSubCodeTable IS TABLE OF VARCHAR2(30)
75 INDEX BY BINARY_INTEGER;
76 TYPE PickRelDestLocIDTable IS TABLE OF NUMBER
77 INDEX BY BINARY_INTEGER;
78 TYPE SugDestZoneIDTable IS TABLE OF NUMBER
79 INDEX BY BINARY_INTEGER;
80 TYPE SugDestSubCodeTable IS TABLE OF VARCHAR2(30)
81 INDEX BY BINARY_INTEGER;
82 TYPE SugDestLocIDTable IS TABLE OF VARCHAR2(30)
83 INDEX BY BINARY_INTEGER;
84 TYPE SugLPNIDTable IS TABLE OF NUMBER -- xdock MDC
85 INDEX BY BINARY_INTEGER;
86
87 l_temp_id_table TransactionTempIDTable;
88 l_pick_rel_zone_id_table PickRelDestZoneIDTable;
89 l_pick_rel_sub_table PickRelDestSubCodeTable;
90 l_pick_rel_loc_id_table PickRelDestLocIDTable;
91 l_sug_dest_zone_id_table SugDestZoneIDTable;
92 l_sug_dest_sub_table SugDestSubCodeTable;
93 l_sug_dest_loc_id_table SugDestLocIDTable;
94 l_sug_lpn_id_table SugLPNIDTable;
95
96
97 CURSOR l_task_drop_loc_cur
98 (v_operation_sequence_id NUMBER)IS
99 SELECT mmtt.transaction_temp_id transaction_temp_id,
100 mmtt.transfer_subinventory pick_release_subinventory,
101 mmtt.transfer_to_location pick_release_locator_id,
102 mmtt.wms_task_type,
103 mmtt.parent_line_id parent_line_id,
104 mmtt.move_order_line_id move_order_line_id,
105 op.operation_plan_id operation_plan_id,
106 Nvl(op.loc_selection_criteria, g_op_dest_sys_suggested) dest_loc_sel_criteria,
107 op.loc_selection_api_id dest_loc_sel_api_id,
108 Nvl(op.drop_lpn_option, g_op_drop_lpn_optional) drop_lpn_option,
109 Nvl(op.consolidation_method_id, 2) consolidation_method_id -- xdock MDC default to within delivery
110 FROM mtl_material_transactions_temp mmtt,
111 (SELECT plan.operation_plan_id,
112 detail.loc_selection_criteria,
113 detail.loc_selection_api_id,
114 detail.drop_lpn_option,
115 detail.consolidation_method_id
116 FROM
117 wms_op_plans_b plan,
118 wms_op_plan_details detail
119 WHERE
120 plan.operation_plan_id = detail.operation_plan_id AND
121 detail.operation_sequence = v_operation_sequence_id
122 ) op
123 WHERE mmtt.transfer_lpn_id = p_transfer_lpn_id AND
124 (mmtt.parent_line_id IS NULL -- xdock MDC non bulk line or child line for bulk task
125 OR mmtt.parent_line_id <> mmtt.transaction_temp_id) AND
126 ((mmtt.transaction_source_type_id = g_sourcetype_salesorder
127 AND mmtt.transaction_action_id = g_action_stgxfr)
128 OR
129 (mmtt.transaction_source_type_id = g_sourcetype_intorder
130 AND mmtt.transaction_action_id = g_action_stgxfr)
131 OR
132 mmtt.wms_task_type = g_wms_task_type_stg_move
133 )AND
134 mmtt.operation_plan_id = op.operation_plan_id (+)
135 ;
136
137 l_task_drop_loc_rec l_task_drop_loc_cur%ROWTYPE;
138
139 l_operation_sequence_id NUMBER := 2;
140 l_delivery_id NUMBER;
141 l_task_count NUMBER := 1;
142
143 l_return_status VARCHAR2(1);
144 l_message VARCHAR2(400);
145 l_progress VARCHAR2(10);
146
147 l_msg_count NUMBER;
148 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
149 BEGIN
150
151 SAVEPOINT update_drop_loc_sp;
152
153
154
155 IF (l_debug = 1) THEN
156 print_debug('Enter update_drop_locator_for_task', 1);
157 END IF;
158
159 IF (l_debug = 1) THEN
160 print_debug('p_transfer_lpn_id : '|| p_transfer_lpn_id, 4);
161 END IF;
162
163
164 l_progress := '10';
165
166 x_return_status := g_ret_sts_success;
167
168 fnd_message.clear;
169
170 OPEN l_task_drop_loc_cur(l_operation_sequence_id);
171 wms_op_dest_sys_apis.g_sug_dest_sub_loc_table.DELETE; -- 16371756 FP of Bug 16344535
172
173 LOOP
174
175 FETCH l_task_drop_loc_cur INTO l_task_drop_loc_rec;
176 EXIT WHEN l_task_drop_loc_cur%notfound;
177
178 l_progress := '20';
179
180 IF (l_debug = 1) THEN
181 print_debug('transaction_temp_id : '|| l_task_drop_loc_rec.transaction_temp_id, 4);
182 print_debug('wms_task_type : '|| l_task_drop_loc_rec.wms_task_type, 4);
183 print_debug('pick_release_subinventory : '|| l_task_drop_loc_rec.pick_release_subinventory, 4);
184 print_debug('pick_release_locator_id : '|| l_task_drop_loc_rec.pick_release_locator_id, 4);
185 print_debug('operation_plan_id : '|| l_task_drop_loc_rec.operation_plan_id, 4);
186 print_debug('dest_loc_sel_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria, 4);
187 print_debug('dest_loc_sel_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id, 4);
188 END IF;
189
190
191 l_temp_id_table(l_task_count) := l_task_drop_loc_rec.transaction_temp_id;
192 l_pick_rel_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
193 l_pick_rel_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
194
195 l_progress := '25';
196
197 l_sug_lpn_id_table(l_task_count) := NULL;
198
199 IF l_task_drop_loc_rec.wms_task_type = g_wms_task_type_stg_move THEN
200
201 --{{
202 -- For staging move
203 -- Two cases to handle when consolidation accross delivery
204 -- 1. Bulk picking. In this case, should call MDC API for each delivery within the LPN
205 -- 2. Regular LPN. In this case, only need to call MDC API for the entire LPN
206 -- The indicator for a regular LPN is that any line within this LPN has NULL parent_line_id
207 -- In fact, bulk picking case might not occur for staging move. But since there is no
208 -- performance concern, leave the check here just in case.
209 --}}
210 IF l_task_drop_loc_rec.consolidation_method_id = 1 THEN
211
212 IF(l_task_drop_loc_rec.parent_line_id IS NULL) THEN
213 l_progress := '25.10';
214
215 IF(l_task_count = 1) THEN
216 l_progress := '25.20';
217
218 IF (l_debug = 1) THEN
219 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
220 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
221 END IF;
222 wms_mdc_pvt.suggest_to_lpn
223 (p_lpn_id => p_transfer_lpn_id,
224 p_delivery_id => NULL,
225 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
226 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
227 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
228 x_return_status =>l_return_status,
229 x_msg_count =>l_msg_count,
230 x_msg_data =>l_message);
231
232 IF (l_debug = 1) THEN
233 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
234 print_debug('l_return_status : '|| l_return_status, 4);
235 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
236 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
237 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
238 END IF;
239
240 IF l_return_status <> g_ret_sts_success THEN
241 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
242 IF l_return_status = g_ret_sts_error THEN
243 RAISE fnd_api.g_exc_error;
244 ELSE
245 RAISE fnd_api.g_exc_unexpected_error;
246 END IF;
247 END IF;
248
249 ELSE
250 --{{
251 -- In the case of regular LPN, the rest of the tasks within the same LPN
252 -- should have the same LPN/sub/loc suggested as the first line
253 --}}
254 print_debug('Default sub/loc/LPN from the first task in this LPN.', 4);
255
256 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
257 l_sug_dest_loc_id_table(l_task_count) := l_sug_dest_loc_id_table(1);
258 l_sug_dest_sub_table(l_task_count) := l_sug_dest_sub_table(1); --Bug#9929125
259 END IF;
260 ELSE
261 -- first derive delivery
262 l_progress := '25.30';
263
264 BEGIN
265
266 SELECT wda.delivery_id
267 INTO l_delivery_id
268 FROM wsh_delivery_assignments_v wda,
269 wsh_delivery_details wdd
270 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
271 AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 NULL;
276 END;
277 --
278 IF l_delivery_id IS NOT NULL THEN
279 IF (l_debug = 1) THEN
280 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
281 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
282 print_debug('p_delivery_id: '|| l_delivery_id, 4);
283
284 END IF;
285 wms_mdc_pvt.suggest_to_lpn
286 (p_lpn_id => p_transfer_lpn_id,
287 p_delivery_id => l_delivery_id,
288 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
289 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
290 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
291 x_return_status =>l_return_status,
292 x_msg_count =>l_msg_count,
293 x_msg_data =>l_message);
294
295 IF (l_debug = 1) THEN
296 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
297 print_debug('l_return_status : '|| l_return_status, 4);
298 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
299 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
300 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
301 END IF;
302
303 IF l_return_status <> g_ret_sts_success THEN
304 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
305 IF l_return_status = g_ret_sts_error THEN
306 RAISE fnd_api.g_exc_error;
307 ELSE
308 RAISE fnd_api.g_exc_unexpected_error;
309 END IF;
310 END IF;
311 END IF;
312
313
314 END IF;
315
316 END IF;
317
318 IF(l_task_drop_loc_rec.consolidation_method_id = 2 OR
319 l_sug_lpn_id_table(l_task_count) IS NULL) THEN
320
321 --{{
322 -- If consolidation within deliveyr or MDC API does not return anything
323 -- fall back to what we have in 11.5.9
324 -- The latter case could happen to drop to WIP. i.e. no delivery for this task
325 -- wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery should resort to original suggestion.
326 --}}
327 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
328 (x_return_status => l_return_status,
329 x_message => l_message,
330 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
331 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
332 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
333 p_call_mode => 1, -- locator selection
334 p_task_type => g_wms_task_type_pick, -- picking
335 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
336 p_locator_id => NULL);
337
338 IF l_return_status <> g_ret_sts_success THEN
339 IF l_return_status = 'W' THEN
340 x_return_status := l_return_status;
341 x_message := l_message;
342 ELSE
343 RAISE fnd_api.g_exc_error;
344 END IF;
345
346 END IF;
347
348 END IF;
349
350
351 ELSE
352
353
354 IF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_sys_suggested THEN
355 l_progress := '30';
356 IF (l_debug = 1) THEN
357 print_debug('Return locator as system suggested.', 4);
358 END IF;
359
360
361 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
362 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
363
364 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_api THEN -- suggested by API
365 IF (l_debug = 1) THEN
366 print_debug('Return locator suggested by API.', 4);
367 END IF;
368
369 l_progress := '40';
370
371 IF l_task_drop_loc_rec.dest_loc_sel_api_id = 1 THEN
372 IF (l_debug = 1) THEN
373 print_debug('Calling WMS_OP_DEST_SYS_APIS.Get_CONS_Loc_For_Delivery. Leaving update_drop_locator_for_task ... ', 4);
374 END IF;
375
376 l_progress := '50';
377
378 wms_op_dest_sys_apis.Get_CONS_Loc_For_Delivery
379 (x_return_status => l_return_status,
380 x_message => l_message,
381 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
382 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
383 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
384 p_call_mode => 1, -- locator selection
385 p_task_type => g_wms_task_type_pick, -- picking
386 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
387 p_locator_id => NULL);
388
389 IF (l_debug = 1) THEN
390 print_debug('Back to update_drop_locator_for_task.', 4);
391 print_debug('x_return_status = '||l_return_status, 4);
392 print_debug('x_message = '||l_message, 4);
393 print_debug('x_locator_id = '||l_sug_dest_loc_id_table(l_task_count), 4);
394 print_debug('x_subinventory_code = '||l_sug_dest_sub_table(l_task_count), 4);
395 print_debug('x_zone_id = '||l_sug_dest_zone_id_table(l_task_count), 4);
396 END IF;
397
398 IF l_return_status <> g_ret_sts_success THEN
399 IF l_return_status = 'W' THEN
400 x_return_status := l_return_status;
401 x_message := l_message;
402 ELSE
403 RAISE fnd_api.g_exc_error;
404 END IF;
405 /*ELSIF l_task_drop_loc_rec.parent_line_id IS NOT NULL THEN
406 --
407 -- Bug 4884284: Bulk pick can have multiple deliveries
408 -- so update dest sub/loc immediately
409 --
410 UPDATE mtl_material_transactions_temp
411 SET transfer_subinventory
412 = l_sug_dest_sub_table(l_task_count)
413 , transfer_to_location
414 = l_sug_dest_loc_id_table(l_task_count)
415 WHERE transaction_temp_id
416 = l_task_drop_loc_rec.transaction_temp_id;*/ -- 12800191
417 END IF;
418
419 l_progress := '60';
420
421
422 ELSIF l_task_drop_loc_rec.dest_loc_sel_api_id = 2 THEN
423
424
425
426 --{{
427 -- For pick drop
428 -- Two cases to handle when consolidation accross delivery
429 -- 1. Bulk picking. In this case, should call MDC API for each delivery within the LPN
430 -- 2. Regular LPN. In this case, only need to call MDC API for the entire LPN
431 -- The indicator for a regular LPN is that any line within this LPN has NULL parent_line_id
432 --}}
433
434 IF l_task_drop_loc_rec.consolidation_method_id = 1 THEN -- across delivery
435
436 IF(l_task_drop_loc_rec.parent_line_id IS NULL) THEN -- regular LPN, i.e. non-bulk
437 l_progress := '60.10';
438
439 IF(l_task_count = 1) THEN
440 l_progress := '60.20';
441
442 IF (l_debug = 1) THEN
443 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
444 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
445 END IF;
446
447 wms_mdc_pvt.suggest_to_lpn
448 (p_lpn_id => p_transfer_lpn_id,
449 p_delivery_id => NULL,
450 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
451 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
452 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
453 x_return_status =>l_return_status,
454 x_msg_count =>l_msg_count,
455 x_msg_data =>l_message);
456
457 IF (l_debug = 1) THEN
458 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
459 print_debug('l_return_status : '|| l_return_status, 4);
460 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
461 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
462 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
463 END IF;
464
465 IF l_return_status <> g_ret_sts_success THEN
466 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
467 IF l_return_status = g_ret_sts_error THEN
468 RAISE fnd_api.g_exc_error;
469 ELSE
470 RAISE fnd_api.g_exc_unexpected_error;
471 END IF;
472 END IF;
473
474 ELSE
475 --{{
476 -- In the case of regular LPN, the rest of the tasks within the same LPN
477 -- should have the same LPN/sub/loc suggested as the first line
478 --}}
479 print_debug('Default sub/loc/LPN from the first task in this LPN.', 4);
480
481 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
482 l_sug_dest_loc_id_table(l_task_count) := l_sug_dest_loc_id_table(1);
483 l_sug_dest_sub_table(l_task_count) := l_sug_dest_sub_table(1); --Bug#9929125
484 END IF;
485 ELSE -- now bulk picking
486 -- first derive delivery
487 l_progress := '60.30';
488
489 BEGIN
490
491 SELECT wda.delivery_id
492 INTO l_delivery_id
493 FROM wsh_delivery_assignments_v wda,
494 wsh_delivery_details wdd
495 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
496 AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
497
498 EXCEPTION
499 WHEN OTHERS THEN
500 NULL;
501 END;
502 --
503 IF l_delivery_id IS NOT NULL THEN
504 IF (l_debug = 1) THEN
505 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
506 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
507 print_debug('p_delivery_id: '|| l_delivery_id, 4);
508
509 END IF;
510 wms_mdc_pvt.suggest_to_lpn
511 (p_lpn_id => p_transfer_lpn_id,
512 p_delivery_id => l_delivery_id,
513 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
514 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
515 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
516 x_return_status =>l_return_status,
517 x_msg_count =>l_msg_count,
518 x_msg_data =>l_message);
519
520 IF (l_debug = 1) THEN
521 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
522 print_debug('l_return_status : '|| l_return_status, 4);
523 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
524 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
525 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
526 END IF;
527
528 IF l_return_status <> g_ret_sts_success THEN
529 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
530 IF l_return_status = g_ret_sts_error THEN
531 RAISE fnd_api.g_exc_error;
532 ELSE
533 RAISE fnd_api.g_exc_unexpected_error;
534 END IF;
535 END IF;
536 END IF; -- l_delivery_id IS NOT NULL
537
538 END IF; -- bulk picking if
539
540 END IF; -- l_task_drop_loc_rec.consolidation_method_id = 1
541
542 IF(l_task_drop_loc_rec.consolidation_method_id = 2 OR
543 l_sug_lpn_id_table(l_task_count) IS NULL) THEN
544
545 --{{
546 -- If consolidation within deliveyr or MDC API does not return anything
547 -- fall back to what we have in 11.5.9
548 -- The latter case could happen to drop to WIP. i.e. no delivery for this task
549 -- wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery should resort to original suggestion.
550 --}}
551 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
552 (x_return_status => l_return_status,
553 x_message => l_message,
554 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
555 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
556 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
557 p_call_mode => 1, -- locator selection
558 p_task_type => g_wms_task_type_pick, -- picking
559 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
560 p_locator_id => NULL);
561
562 IF l_return_status <> g_ret_sts_success THEN
563 IF l_return_status = 'W' THEN
564 x_return_status := l_return_status;
565 x_message := l_message;
566 ELSE
567 RAISE fnd_api.g_exc_error;
568 END IF;
569
570 END IF;
571
572 END IF;
573
574 l_progress := '80';
575
576 ELSE
577
578 IF (l_debug = 1) THEN
579 print_debug('Invalid loc_selection_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
580 END IF;
581
582
583 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
584 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
585 END IF;
586
587
588 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_pre_specified THEN
589 IF (l_debug = 1) THEN
590 print_debug('Return locator as pre-specified by user.', 4);
591 END IF;
592
593 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
594 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
595 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_rules_engine THEN
596 IF (l_debug = 1) THEN
597 print_debug('Return locator as suggested by rules engine.', 4);
598 END IF;
599
600 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
601 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
602 ELSE
603 IF (l_debug = 1) THEN
604 print_debug('Invalid loc_selection_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
605 END IF;
606
607 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
608 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
609
610 END IF;
611
612 END IF;
613
614 UPDATE mtl_material_transactions_temp
615 SET transfer_subinventory
616 = l_sug_dest_sub_table(l_task_count)
617 , transfer_to_location
618 = l_sug_dest_loc_id_table(l_task_count)
619 WHERE transaction_temp_id
620 = l_task_drop_loc_rec.transaction_temp_id; -- 12800191
621
622 l_task_count := l_task_count + 1;
623
624 END LOOP;
625 wms_op_dest_sys_apis.g_sug_dest_sub_loc_table.DELETE; -- 16371756 FP of Bug 16344535
626 -- In release I, tasks in one LPN have the same operation_plan_ID,
627 -- therefore use the drop_lpn_option of the last task for the LPN.
628
629 x_drop_lpn_option := Nvl(l_task_drop_loc_rec.drop_lpn_option, g_op_drop_lpn_optional);
630 IF (l_debug = 1) THEN
631 print_debug('drop_lpn_option : ' || x_drop_lpn_option, 4);
632 END IF;
633
634 l_progress := '90';
635
636 CLOSE l_task_drop_loc_cur;
637
638 l_progress := '100';
639
640 -- bulk update MMTT with new subinventory and locator
641 /* forall i IN 1..l_temp_id_table.COUNT
642 UPDATE mtl_material_transactions_temp
643 SET transfer_subinventory = l_sug_dest_sub_table(i),
644 transfer_to_location = l_sug_dest_loc_id_table(i)
645 --cartonization_id = l_sug_lpn_id_table(i) -- xdock MDC
646 -- mrana:bug5257431: the above update should not be committed
647 -- before the task is confirmed. If the drop is cancelled, we would
648 -- like to retain the original cartonization id
649 WHERE transaction_temp_id = l_temp_id_table(i);*/ -- 12800191
650
651 l_progress := '105';
652
653 -- bulk update WDT with original subinventory and locator
654 -- Bug 4884372: moved WDT update to before the COMMIT
655 forall i IN 1..l_temp_id_table.COUNT
656 UPDATE wms_dispatched_tasks
657 SET suggested_dest_subinventory = l_pick_rel_sub_table(i),
658 suggested_dest_locator_id = l_pick_rel_loc_id_table(i)
659 WHERE transaction_temp_id = l_temp_id_table(i)
660 AND task_type IN (g_wms_task_type_pick, g_wms_task_type_stg_move);
661
662 --- bug fix 4017457 GXIAO 11/17/2004
663 --- need to commit after updating MMTT
664 IF (WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) THEN
665 COMMIT;
666 END IF;
667
668 l_progress := '110';
669
670 -- mrana:bug5257431: Bulk update MMTT with suggested MDC LPN
671 forall i IN 1..l_temp_id_table.COUNT
672 UPDATE mtl_material_transactions_temp
673 SET cartonization_id = l_sug_lpn_id_table(i) -- MDC
674 WHERE transaction_temp_id = l_temp_id_table(i);
675
676 l_progress := '120';
677
678 EXCEPTION
679
680 WHEN fnd_api.g_exc_error THEN
681 ROLLBACK TO update_drop_loc_sp;
682
683 x_return_status := g_ret_sts_error;
684 IF l_task_drop_loc_cur%isopen THEN
685 CLOSE l_task_drop_loc_cur;
686 END IF;
687
688
689 WHEN OTHERS THEN
690 ROLLBACK TO update_drop_loc_sp;
691
692 IF (l_debug = 1) THEN
693 print_debug('Other exception in update_drop_locator_for_task '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
694 END IF;
695
696
697 x_return_status := g_ret_sts_unexp_error;
698
699 IF SQLCODE IS NOT NULL THEN
700 IF (l_debug = 1) THEN
701 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
702 END IF;
703
704 END IF;
705
706 IF l_task_drop_loc_cur%isopen THEN
707 CLOSE l_task_drop_loc_cur;
708 END IF;
709
710
711
712 END update_drop_locator_for_task;
713
714
715 PROCEDURE validate_pick_drop_Locator
716 (
717 X_Return_status OUT nocopy VARCHAR2,
718 X_Message OUT nocopy VARCHAR2,
719 P_Task_Type IN NUMBER,
720 P_Task_ID IN NUMBER,
721 P_Locator_Id IN NUMBER
722 )
723 IS
724 CURSOR l_task_drop_loc_cur
725 (v_operation_sequence_id NUMBER)IS
726 SELECT mmtt.transaction_temp_id transaction_temp_id,
727 mmtt.transfer_subinventory pick_release_subinventory,
728 mmtt.transfer_to_location pick_release_locator_id,
729 op.operation_plan_id operation_plan_id,
730 Nvl(op.loc_selection_criteria, g_op_dest_sys_suggested) dest_loc_sel_criteria,
731 op.loc_selection_api_id dest_loc_sel_api_id,
732 Nvl(op.drop_lpn_option, g_op_drop_lpn_optional) drop_lpn_option,
733 Nvl(consolidation_method_id, 2) consolidation_method_id,
734 mmtt.organization_id organization_id --BUG13839132
735 FROM mtl_material_transactions_temp mmtt,
736 (SELECT plan.operation_plan_id,
737 detail.loc_selection_criteria,
738 detail.loc_selection_api_id,
739 detail.drop_lpn_option,
740 detail.consolidation_method_id
741 FROM
742 wms_op_plans_b plan,
743 wms_op_plan_details detail
744 WHERE
745 plan.operation_plan_id = detail.operation_plan_id AND
746 detail.operation_sequence = v_operation_sequence_id
747 ) op
748 WHERE mmtt.transaction_temp_id = p_task_id AND
749 ((mmtt.transaction_source_type_id = g_sourcetype_salesorder
750 AND mmtt.transaction_action_id = g_action_stgxfr)
751 OR
752 (mmtt.transaction_source_type_id = g_sourcetype_intorder
753 AND mmtt.transaction_action_id = g_action_stgxfr)
754 OR
755 mmtt.wms_task_type = g_wms_task_type_stg_move
756 )AND
757 mmtt.operation_plan_id = op.operation_plan_id (+)
758 ;
759
760
761 l_task_drop_loc_rec l_task_drop_loc_cur%ROWTYPE;
762
763 l_operation_sequence_id NUMBER := 2;
764 l_progress VARCHAR2(10);
765
766 -- following three variables are dummy varaibles to hold output parameters
767 -- for validation API
768 l_subinventory_code VARCHAR2(30);
769 l_zone_id NUMBER;
770 l_locator_id NUMBER;
771
772 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
773 BEGIN
774 IF (l_debug = 1) THEN
775 print_debug('Enter validate_pick_drop_Locator', 1);
776 END IF;
777
778 IF (l_debug = 1) THEN
779 print_debug('p_task_type : '|| p_task_type, 4);
780 print_debug('p_task_id : '|| p_task_id, 4);
781 END IF;
782
783
784 l_progress := '10';
785
786 x_return_status := g_ret_sts_success;
787
788 fnd_message.clear;
789
790
791 IF p_task_type NOT IN (g_wms_task_type_pick, g_wms_task_type_stg_move) THEN
792 IF (l_debug = 1) THEN
793 print_debug('validate_pick_drop_Locator: Task type not picking or staging move, do not need to validate locator.', 4);
794 END IF;
795
796 RETURN;
797 END IF;
798
799 l_progress := '20';
800
801 OPEN l_task_drop_loc_cur(l_operation_sequence_id);
802
803 l_progress := '23';
804
805 FETCH l_task_drop_loc_cur INTO l_task_drop_loc_rec;
806
807 IF l_task_drop_loc_cur%notfound THEN
808 IF (l_debug = 1) THEN
809 print_debug('validate_pick_drop_Locator: This is not a sales order staging transfer or a staging move, do not need to validate locator for consolidation.', 4);
810 END IF;
811
812 IF l_task_drop_loc_cur%isopen THEN
813 CLOSE l_task_drop_loc_cur;
814 END IF;
815 RETURN;
816 END IF;
817
818 --BUG13839132
819 IF inv_cache.set_org_rec(l_task_drop_loc_rec.organization_id) THEN
820 IF (l_debug = 1) THEN
821 print_debug('validate_pick_drop_Locator: Setting the Inv cache for the org rec', 4);
822 END IF;
823 ELSE
824 IF (l_debug = 1) THEN
825 print_debug('validate_pick_drop_Locator: No org data found in cache so returing', 4);
826 END IF;
827 x_return_status := g_ret_sts_unexp_error;
828 RETURN;
829 END IF;
830 --BUG13839132
831
832 --IF l_task_drop_loc_rec.operation_plan_id IS NULL --commented by fixing bug 13839132, start
833 IF (l_task_drop_loc_rec.operation_plan_id IS NULL OR
834 l_task_drop_loc_rec.operation_plan_id = 1 and inv_cache.org_rec.default_pick_op_plan_id IS NULL ) -- bug 13839132, end.
835 AND p_task_type <> g_wms_task_type_stg_move
836 THEN
837 IF (l_debug = 1) THEN
838 print_debug('validate_pick_drop_Locator : This mmtt record does not have an operation_plan_id, do not need to validate locator for consolidation.', 4);
839 END IF;
840
841 IF l_task_drop_loc_cur%isopen THEN
842 CLOSE l_task_drop_loc_cur;
843 END IF;
844 RETURN;
845 END IF;
846
847 l_progress := '25';
848
849 IF (l_debug = 1) THEN
850 print_debug('validate_pick_drop_Locator : transaction_temp_id : '|| l_task_drop_loc_rec.transaction_temp_id, 4);
851 print_debug('pick_release_subinventory : '|| l_task_drop_loc_rec.pick_release_subinventory, 4);
852 print_debug('pick_release_locator_id : '|| l_task_drop_loc_rec.pick_release_locator_id, 4);
853 print_debug('operation_plan_id : '|| l_task_drop_loc_rec.operation_plan_id, 4);
854 print_debug('dest_loc_sel_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria, 4);
855 print_debug('dest_loc_sel_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id, 4);
856 END IF;
857
858 IF p_task_type = g_wms_task_type_stg_move THEN
859
860 --{{
861 -- For staging move only do locator validation if consolidation within delivery
862 -- validation will be at LPN level if consolidation across delivery.
863 --}}
864
865 IF(l_task_drop_loc_rec.consolidation_method_id = 2) THEN
866 IF (l_debug = 1) THEN
867 print_debug('validate_pick_drop_Locator : About to call wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery', 4);
868 END IF;
869
870 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
871 (x_return_status => x_return_status,
872 x_message => x_message,
873 x_locator_id => l_locator_id,
874 x_zone_id => l_zone_id,
875 x_subinventory_code => l_subinventory_code,
876 p_call_mode => 2, -- locator validate
877 p_task_type => p_task_type,
878 p_task_id => p_task_id,
879 p_locator_id => p_locator_id);
880
881
882 IF (l_debug = 1) THEN
883 print_debug('Back to validate_pick_drop_Locator.', 4);
884 print_debug('x_return_status = '||x_return_status, 4);
885 print_debug('x_message = '||x_message, 4);
886 END IF;
887
888 END IF;
889
890 ELSE
891
892
893 IF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_sys_suggested THEN
894 l_progress := '30';
895 IF (l_debug = 1) THEN
896 print_debug('Return locator as system suggested, do not need to validate locator.', 4);
897 END IF;
898
899
900 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_api THEN
901 IF (l_debug = 1) THEN
902 print_debug('Return locator suggested by API.', 4);
903 END IF;
904
905 l_progress := '40';
906
907 IF l_task_drop_loc_rec.dest_loc_sel_api_id = 1 THEN
908 IF (l_debug = 1) THEN
909 print_debug('validate_pick_drop_Locator - Calling WMS_OP_DEST_SYS_APIS.Get_CONS_Loc_For_Delivery. Leaving validate_pick_drop_Locator ... ', 4);
910 END IF;
911
912 l_progress := '50';
913
914 wms_op_dest_sys_apis.Get_CONS_Loc_For_Delivery
915 (x_return_status => x_return_status,
916 x_message => x_message,
917 x_locator_id => l_locator_id,
918 x_zone_id => l_zone_id,
919 x_subinventory_code => l_subinventory_code,
920 p_call_mode => 2, -- locator validate
921 p_task_type => p_task_type,
922 p_task_id => p_task_id,
923 p_locator_id => p_locator_id);
924
925 IF (l_debug = 1) THEN
926 print_debug('Back to update_drop_locator_for_task.', 4);
927 print_debug('x_return_status = '||x_return_status, 4);
928 print_debug('x_message = '||x_message, 4);
929 END IF;
930
931
932
933 ELSIF l_task_drop_loc_rec.dest_loc_sel_api_id = 2 THEN
934 --{{
935 -- For staging move only do locator validation if consolidation within delivery
936 -- validation will be at LPN level if consolidation across delivery.
937 --}}
938
939 IF(l_task_drop_loc_rec.consolidation_method_id = 2) THEN
940 IF (l_debug = 1) THEN
941 print_debug('Calling WMS_OP_DEST_SYS_APIS.Get_Staging_Loc_For_Delivery. Leaving validate_pick_drop_Locator ... ', 4);
942 END IF;
943
944 l_progress := '70';
945
946 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
947 (x_return_status => x_return_status,
948 x_message => x_message,
949 x_locator_id => l_locator_id,
950 x_zone_id => l_zone_id,
951 x_subinventory_code => l_subinventory_code,
952 p_call_mode => 2, -- locator validate
953 p_task_type => p_task_type, -- picking
954 p_task_id => p_task_id,
955 p_locator_id => p_locator_id);
956
957
958 IF (l_debug = 1) THEN
959 print_debug('Back to update_drop_locator_for_task.', 4);
960 print_debug('x_return_status = '||x_return_status, 4);
961 print_debug('x_message = '||x_message, 4);
962 END IF;
963
964 END IF;
965
966
967
968 ELSE
969 IF (l_debug = 1) THEN
970 print_debug('Invalid loc_selection_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
971 END IF;
972
973 x_return_status := g_ret_sts_unexp_error;
974 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
975
976 END IF;
977
978
979 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_pre_specified THEN
980 IF (l_debug = 1) THEN
981 print_debug('Return locator as pre-specified by user. do not need to validate locator.', 4);
982 END IF;
983
984
985 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_rules_engine THEN
986 IF (l_debug = 1) THEN
987 print_debug('Return locator as suggested by rules engine. do not need to validate locator.', 4);
988 END IF;
989
990
991 ELSE
992 IF (l_debug = 1) THEN
993 print_debug('Invalid loc_selection_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria ||'for operation_plan_id : '||l_operation_sequence_id||' and operation_sequence :'||l_operation_sequence_id, 4);
994 END IF;
995
996 x_return_status := g_ret_sts_unexp_error;
997 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
998
999 END IF;
1000
1001 END IF;
1002
1003 IF l_task_drop_loc_cur%isopen THEN
1004 CLOSE l_task_drop_loc_cur;
1005 END IF;
1006
1007
1008 EXCEPTION
1009
1010 WHEN OTHERS THEN
1011 IF (l_debug = 1) THEN
1012 print_debug('Other exception in validate_pick_drop_Locator '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
1013 END IF;
1014
1015 x_return_status := g_ret_sts_unexp_error;
1016 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
1017
1018 IF SQLCODE IS NOT NULL THEN
1019 IF (l_debug = 1) THEN
1020 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
1021 END IF;
1022
1023
1024 END IF;
1025
1026
1027 IF l_task_drop_loc_cur%isopen THEN
1028 CLOSE l_task_drop_loc_cur;
1029 END IF;
1030
1031
1032 END validate_pick_drop_Locator;
1033
1034 /*9929125,adding a dummy procedure to resolve dependancy
1035 created by fix 7445908 */
1036 PROCEDURE lock_org_proc
1037 (
1038 p_type_name IN VARCHAR2,
1039 x_err_code OUT NOCOPY NUMBER,
1040 x_err_msg OUT NOCOPY VARCHAR2
1041 ) IS
1042 BEGIN
1043 null;
1044 END lock_org_proc;
1045
1046
1047 END WMS_OP_RUNTIME_PUB_APIS;
1048
[Home] [Help]