1 PACKAGE BODY WMS_OP_RUNTIME_PUB_APIS AS
2 /* $Header: WMSOPPBB.pls 120.5.12000000.2 2007/04/04 17:59:32 stdavid 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
172 LOOP
173
174 FETCH l_task_drop_loc_cur INTO l_task_drop_loc_rec;
175 EXIT WHEN l_task_drop_loc_cur%notfound;
176
177 l_progress := '20';
178
179 IF (l_debug = 1) THEN
180 print_debug('transaction_temp_id : '|| l_task_drop_loc_rec.transaction_temp_id, 4);
181 print_debug('wms_task_type : '|| l_task_drop_loc_rec.wms_task_type, 4);
182 print_debug('pick_release_subinventory : '|| l_task_drop_loc_rec.pick_release_subinventory, 4);
183 print_debug('pick_release_locator_id : '|| l_task_drop_loc_rec.pick_release_locator_id, 4);
184 print_debug('operation_plan_id : '|| l_task_drop_loc_rec.operation_plan_id, 4);
185 print_debug('dest_loc_sel_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria, 4);
186 print_debug('dest_loc_sel_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id, 4);
187 END IF;
188
189
190 l_temp_id_table(l_task_count) := l_task_drop_loc_rec.transaction_temp_id;
191 l_pick_rel_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
192 l_pick_rel_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
193
194 l_progress := '25';
195
196 l_sug_lpn_id_table(l_task_count) := NULL;
197
198 IF l_task_drop_loc_rec.wms_task_type = g_wms_task_type_stg_move THEN
199
200 --{{
201 -- For staging move
202 -- Two cases to handle when consolidation accross delivery
203 -- 1. Bulk picking. In this case, should call MDC API for each delivery within the LPN
204 -- 2. Regular LPN. In this case, only need to call MDC API for the entire LPN
205 -- The indicator for a regular LPN is that any line within this LPN has NULL parent_line_id
206 -- In fact, bulk picking case might not occur for staging move. But since there is no
207 -- performance concern, leave the check here just in case.
208 --}}
209 IF l_task_drop_loc_rec.consolidation_method_id = 1 THEN
210
211 IF(l_task_drop_loc_rec.parent_line_id IS NULL) THEN
212 l_progress := '25.10';
213
214 IF(l_task_count = 1) THEN
215 l_progress := '25.20';
216
217 IF (l_debug = 1) THEN
218 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
219 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
220 END IF;
221 wms_mdc_pvt.suggest_to_lpn
222 (p_lpn_id => p_transfer_lpn_id,
223 p_delivery_id => NULL,
224 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
225 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
226 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
227 x_return_status =>l_return_status,
228 x_msg_count =>l_msg_count,
229 x_msg_data =>l_message);
230
231 IF (l_debug = 1) THEN
232 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
233 print_debug('l_return_status : '|| l_return_status, 4);
234 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
235 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
236 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
237 END IF;
238
239 IF l_return_status <> g_ret_sts_success THEN
240 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
241 IF l_return_status = g_ret_sts_error THEN
242 RAISE fnd_api.g_exc_error;
243 ELSE
244 RAISE fnd_api.g_exc_unexpected_error;
245 END IF;
246 END IF;
247
248 ELSE
249 --{{
250 -- In the case of regular LPN, the rest of the tasks within the same LPN
251 -- should have the same LPN/sub/loc suggested as the first line
252 --}}
253 print_debug('Default sub/loc/LPN from the first task in this LPN.', 4);
254
255 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
256 l_sug_dest_loc_id_table(l_task_count) := l_sug_dest_loc_id_table(1);
257 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
258 END IF;
259 ELSE
260 -- first derive delivery
261 l_progress := '25.30';
262
263 BEGIN
264
265 SELECT wda.delivery_id
266 INTO l_delivery_id
267 FROM wsh_delivery_assignments_v wda,
268 wsh_delivery_details wdd
269 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
270 AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 NULL;
275 END;
276 --
277 IF l_delivery_id IS NOT NULL THEN
278 IF (l_debug = 1) THEN
279 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
280 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
281 print_debug('p_delivery_id: '|| l_delivery_id, 4);
282
283 END IF;
284 wms_mdc_pvt.suggest_to_lpn
285 (p_lpn_id => p_transfer_lpn_id,
286 p_delivery_id => l_delivery_id,
287 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
288 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
289 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
290 x_return_status =>l_return_status,
291 x_msg_count =>l_msg_count,
292 x_msg_data =>l_message);
293
294 IF (l_debug = 1) THEN
295 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
296 print_debug('l_return_status : '|| l_return_status, 4);
297 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
298 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
299 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
300 END IF;
301
302 IF l_return_status <> g_ret_sts_success THEN
303 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
304 IF l_return_status = g_ret_sts_error THEN
305 RAISE fnd_api.g_exc_error;
306 ELSE
307 RAISE fnd_api.g_exc_unexpected_error;
308 END IF;
309 END IF;
310 END IF;
311
312
313 END IF;
314
315 END IF;
316
317 IF(l_task_drop_loc_rec.consolidation_method_id = 2 OR
318 l_sug_lpn_id_table(l_task_count) IS NULL) THEN
319
320 --{{
321 -- If consolidation within deliveyr or MDC API does not return anything
322 -- fall back to what we have in 11.5.9
323 -- The latter case could happen to drop to WIP. i.e. no delivery for this task
324 -- wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery should resort to original suggestion.
325 --}}
326 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
327 (x_return_status => l_return_status,
328 x_message => l_message,
329 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
330 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
331 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
332 p_call_mode => 1, -- locator selection
333 p_task_type => g_wms_task_type_pick, -- picking
334 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
335 p_locator_id => NULL);
336
337 IF l_return_status <> g_ret_sts_success THEN
338 IF l_return_status = 'W' THEN
339 x_return_status := l_return_status;
340 x_message := l_message;
341 ELSE
342 RAISE fnd_api.g_exc_error;
343 END IF;
344
345 END IF;
346
347 END IF;
348
349
350 ELSE
351
352
353 IF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_sys_suggested THEN
354 l_progress := '30';
355 IF (l_debug = 1) THEN
356 print_debug('Return locator as system suggested.', 4);
357 END IF;
358
359
360 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
361 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
362
363 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_api THEN -- suggested by API
364 IF (l_debug = 1) THEN
365 print_debug('Return locator suggested by API.', 4);
366 END IF;
367
368 l_progress := '40';
369
370 IF l_task_drop_loc_rec.dest_loc_sel_api_id = 1 THEN
371 IF (l_debug = 1) THEN
372 print_debug('Calling WMS_OP_DEST_SYS_APIS.Get_CONS_Loc_For_Delivery. Leaving update_drop_locator_for_task ... ', 4);
373 END IF;
374
375 l_progress := '50';
376
377 wms_op_dest_sys_apis.Get_CONS_Loc_For_Delivery
378 (x_return_status => l_return_status,
379 x_message => l_message,
380 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
381 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
382 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
383 p_call_mode => 1, -- locator selection
384 p_task_type => g_wms_task_type_pick, -- picking
385 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
386 p_locator_id => NULL);
387
388 IF (l_debug = 1) THEN
389 print_debug('Back to update_drop_locator_for_task.', 4);
390 print_debug('x_return_status = '||l_return_status, 4);
391 print_debug('x_message = '||l_message, 4);
392 print_debug('x_locator_id = '||l_sug_dest_loc_id_table(l_task_count), 4);
393 print_debug('x_subinventory_code = '||l_sug_dest_sub_table(l_task_count), 4);
394 print_debug('x_zone_id = '||l_sug_dest_zone_id_table(l_task_count), 4);
395 END IF;
396
397 IF l_return_status <> g_ret_sts_success THEN
398 IF l_return_status = 'W' THEN
399 x_return_status := l_return_status;
400 x_message := l_message;
401 ELSE
402 RAISE fnd_api.g_exc_error;
403 END IF;
404 ELSIF l_task_drop_loc_rec.parent_line_id IS NOT NULL THEN
405 --
406 -- Bug 4884284: Bulk pick can have multiple deliveries
407 -- so update dest sub/loc immediately
408 --
409 UPDATE mtl_material_transactions_temp
410 SET transfer_subinventory
411 = l_sug_dest_sub_table(l_task_count)
412 , transfer_to_location
413 = l_sug_dest_loc_id_table(l_task_count)
414 WHERE transaction_temp_id
415 = l_task_drop_loc_rec.transaction_temp_id;
416 END IF;
417
418 l_progress := '60';
419
420
421 ELSIF l_task_drop_loc_rec.dest_loc_sel_api_id = 2 THEN
422
423
424
425 --{{
426 -- For pick drop
427 -- Two cases to handle when consolidation accross delivery
428 -- 1. Bulk picking. In this case, should call MDC API for each delivery within the LPN
429 -- 2. Regular LPN. In this case, only need to call MDC API for the entire LPN
430 -- The indicator for a regular LPN is that any line within this LPN has NULL parent_line_id
431 --}}
432
433 IF l_task_drop_loc_rec.consolidation_method_id = 1 THEN -- across delivery
434
435 IF(l_task_drop_loc_rec.parent_line_id IS NULL) THEN -- regular LPN, i.e. non-bulk
436 l_progress := '60.10';
437
438 IF(l_task_count = 1) THEN
439 l_progress := '60.20';
440
441 IF (l_debug = 1) THEN
442 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
443 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
444 END IF;
445
446 wms_mdc_pvt.suggest_to_lpn
447 (p_lpn_id => p_transfer_lpn_id,
448 p_delivery_id => NULL,
449 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
450 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
451 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
452 x_return_status =>l_return_status,
453 x_msg_count =>l_msg_count,
454 x_msg_data =>l_message);
455
456 IF (l_debug = 1) THEN
457 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
458 print_debug('l_return_status : '|| l_return_status, 4);
459 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
460 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
461 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
462 END IF;
463
464 IF l_return_status <> g_ret_sts_success THEN
465 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
466 IF l_return_status = g_ret_sts_error THEN
467 RAISE fnd_api.g_exc_error;
468 ELSE
469 RAISE fnd_api.g_exc_unexpected_error;
470 END IF;
471 END IF;
472
473 ELSE
474 --{{
475 -- In the case of regular LPN, the rest of the tasks within the same LPN
476 -- should have the same LPN/sub/loc suggested as the first line
477 --}}
478 print_debug('Default sub/loc/LPN from the first task in this LPN.', 4);
479
480 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
481 l_sug_dest_loc_id_table(l_task_count) := l_sug_dest_loc_id_table(1);
482 l_sug_lpn_id_table(l_task_count) := l_sug_lpn_id_table(1);
483 END IF;
484 ELSE -- now bulk picking
485 -- first derive delivery
486 l_progress := '60.30';
487
488 BEGIN
489
490 SELECT wda.delivery_id
491 INTO l_delivery_id
492 FROM wsh_delivery_assignments_v wda,
493 wsh_delivery_details wdd
494 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
495 AND wdd.move_order_line_id = l_task_drop_loc_rec.move_order_line_id;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 NULL;
500 END;
501 --
502 IF l_delivery_id IS NOT NULL THEN
503 IF (l_debug = 1) THEN
504 print_debug('Before calling wms_mdc_pvt.suggest_to_lpn with following parameters:', 4);
505 print_debug('p_transfer_lpn_id : '||p_transfer_lpn_id , 4);
506 print_debug('p_delivery_id: '|| l_delivery_id, 4);
507
508 END IF;
509 wms_mdc_pvt.suggest_to_lpn
510 (p_lpn_id => p_transfer_lpn_id,
511 p_delivery_id => l_delivery_id,
512 x_to_lpn_id =>l_sug_lpn_id_table(l_task_count),
513 x_to_subinventory_code=>l_sug_dest_sub_table(l_task_count),
514 x_to_locator_id =>l_sug_dest_loc_id_table(l_task_count),
515 x_return_status =>l_return_status,
516 x_msg_count =>l_msg_count,
517 x_msg_data =>l_message);
518
519 IF (l_debug = 1) THEN
520 print_debug('After calling wms_mdc_pvt.suggest_to_lpn:', 4);
521 print_debug('l_return_status : '|| l_return_status, 4);
522 print_debug('x_to_lpn_id : '|| l_sug_lpn_id_table(l_task_count), 4);
523 print_debug('x_to_subinventory_code : '|| l_sug_dest_sub_table(l_task_count), 4);
524 print_debug('x_to_locator_id : '||l_sug_dest_loc_id_table(l_task_count) , 4);
525 END IF;
526
527 IF l_return_status <> g_ret_sts_success THEN
528 print_debug('Failed calling wms_mdc_pvt.suggest_to_lpn.', 4);
529 IF l_return_status = g_ret_sts_error THEN
530 RAISE fnd_api.g_exc_error;
531 ELSE
532 RAISE fnd_api.g_exc_unexpected_error;
533 END IF;
534 END IF;
535 END IF; -- l_delivery_id IS NOT NULL
536
537 END IF; -- bulk picking if
538
539 END IF; -- l_task_drop_loc_rec.consolidation_method_id = 1
540
541 IF(l_task_drop_loc_rec.consolidation_method_id = 2 OR
542 l_sug_lpn_id_table(l_task_count) IS NULL) THEN
543
544 --{{
545 -- If consolidation within deliveyr or MDC API does not return anything
546 -- fall back to what we have in 11.5.9
547 -- The latter case could happen to drop to WIP. i.e. no delivery for this task
548 -- wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery should resort to original suggestion.
549 --}}
550 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
551 (x_return_status => l_return_status,
552 x_message => l_message,
553 x_locator_id => l_sug_dest_loc_id_table(l_task_count),
554 x_zone_id => l_sug_dest_zone_id_table(l_task_count),
555 x_subinventory_code => l_sug_dest_sub_table(l_task_count),
556 p_call_mode => 1, -- locator selection
557 p_task_type => g_wms_task_type_pick, -- picking
558 p_task_id => l_task_drop_loc_rec.transaction_temp_id,
559 p_locator_id => NULL);
560
561 IF l_return_status <> g_ret_sts_success THEN
562 IF l_return_status = 'W' THEN
563 x_return_status := l_return_status;
564 x_message := l_message;
565 ELSE
566 RAISE fnd_api.g_exc_error;
567 END IF;
568
569 END IF;
570
571 END IF;
572
573 l_progress := '80';
574
575 ELSE
576
577 IF (l_debug = 1) THEN
578 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);
579 END IF;
580
581
582 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
583 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
584 END IF;
585
586
587 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_pre_specified THEN
588 IF (l_debug = 1) THEN
589 print_debug('Return locator as pre-specified by user.', 4);
590 END IF;
591
592 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
593 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
594 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_rules_engine THEN
595 IF (l_debug = 1) THEN
596 print_debug('Return locator as suggested by rules engine.', 4);
597 END IF;
598
599 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
600 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
601 ELSE
602 IF (l_debug = 1) THEN
603 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);
604 END IF;
605
606 l_sug_dest_sub_table(l_task_count) := l_task_drop_loc_rec.pick_release_subinventory;
607 l_sug_dest_loc_id_table(l_task_count) := l_task_drop_loc_rec.pick_release_locator_id;
608
609 END IF;
610
611 END IF;
612
613
614 l_task_count := l_task_count + 1;
615
616 END LOOP;
617
618 -- In release I, tasks in one LPN have the same operation_plan_ID,
619 -- therefore use the drop_lpn_option of the last task for the LPN.
620
621 x_drop_lpn_option := Nvl(l_task_drop_loc_rec.drop_lpn_option, g_op_drop_lpn_optional);
622 IF (l_debug = 1) THEN
623 print_debug('drop_lpn_option : ' || x_drop_lpn_option, 4);
624 END IF;
625
626 l_progress := '90';
627
628 CLOSE l_task_drop_loc_cur;
629
630 l_progress := '100';
631
632 -- bulk update MMTT with new subinventory and locator
633 forall i IN 1..l_temp_id_table.COUNT
634 UPDATE mtl_material_transactions_temp
635 SET transfer_subinventory = l_sug_dest_sub_table(i),
636 transfer_to_location = l_sug_dest_loc_id_table(i)
637 --cartonization_id = l_sug_lpn_id_table(i) -- xdock MDC
638 -- mrana:bug5257431: the above update should not be committed
639 -- before the task is confirmed. If the drop is cancelled, we would
640 -- like to retain the original cartonization id
641 WHERE transaction_temp_id = l_temp_id_table(i);
642
643 l_progress := '105';
644
645 -- bulk update WDT with original subinventory and locator
646 -- Bug 4884372: moved WDT update to before the COMMIT
647 forall i IN 1..l_temp_id_table.COUNT
648 UPDATE wms_dispatched_tasks
649 SET suggested_dest_subinventory = l_pick_rel_sub_table(i),
650 suggested_dest_locator_id = l_pick_rel_loc_id_table(i)
651 WHERE transaction_temp_id = l_temp_id_table(i)
652 AND task_type IN (g_wms_task_type_pick, g_wms_task_type_stg_move);
653
654 --- bug fix 4017457 GXIAO 11/17/2004
655 --- need to commit after updating MMTT
656 IF (WMS_UI_TASKS_APIS.g_wms_patch_level >= WMS_UI_TASKS_APIS.g_patchset_j) THEN
657 COMMIT;
658 END IF;
659
660 l_progress := '110';
661
662 -- mrana:bug5257431: Bulk update MMTT with suggested MDC LPN
663 forall i IN 1..l_temp_id_table.COUNT
664 UPDATE mtl_material_transactions_temp
665 SET cartonization_id = l_sug_lpn_id_table(i) -- MDC
666 WHERE transaction_temp_id = l_temp_id_table(i);
667
668 l_progress := '120';
669
670 EXCEPTION
671
672 WHEN fnd_api.g_exc_error THEN
673 ROLLBACK TO update_drop_loc_sp;
674
675 x_return_status := g_ret_sts_error;
676 IF l_task_drop_loc_cur%isopen THEN
677 CLOSE l_task_drop_loc_cur;
678 END IF;
679
680
681 WHEN OTHERS THEN
682 ROLLBACK TO update_drop_loc_sp;
683
684 IF (l_debug = 1) THEN
685 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);
686 END IF;
687
688
689 x_return_status := g_ret_sts_unexp_error;
690
691 IF SQLCODE IS NOT NULL THEN
692 IF (l_debug = 1) THEN
693 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
694 END IF;
695
696 END IF;
697
698 IF l_task_drop_loc_cur%isopen THEN
699 CLOSE l_task_drop_loc_cur;
700 END IF;
701
702
703
704 END update_drop_locator_for_task;
705
706
707 PROCEDURE validate_pick_drop_Locator
708 (
709 X_Return_status OUT nocopy VARCHAR2,
710 X_Message OUT nocopy VARCHAR2,
711 P_Task_Type IN NUMBER,
712 P_Task_ID IN NUMBER,
713 P_Locator_Id IN NUMBER
714 )
715 IS
716 CURSOR l_task_drop_loc_cur
717 (v_operation_sequence_id NUMBER)IS
718 SELECT mmtt.transaction_temp_id transaction_temp_id,
719 mmtt.transfer_subinventory pick_release_subinventory,
720 mmtt.transfer_to_location pick_release_locator_id,
721 op.operation_plan_id operation_plan_id,
722 Nvl(op.loc_selection_criteria, g_op_dest_sys_suggested) dest_loc_sel_criteria,
723 op.loc_selection_api_id dest_loc_sel_api_id,
724 Nvl(op.drop_lpn_option, g_op_drop_lpn_optional) drop_lpn_option,
725 Nvl(consolidation_method_id, 2) consolidation_method_id
726 FROM mtl_material_transactions_temp mmtt,
727 (SELECT plan.operation_plan_id,
728 detail.loc_selection_criteria,
729 detail.loc_selection_api_id,
730 detail.drop_lpn_option,
731 detail.consolidation_method_id
732 FROM
733 wms_op_plans_b plan,
734 wms_op_plan_details detail
735 WHERE
736 plan.operation_plan_id = detail.operation_plan_id AND
737 detail.operation_sequence = v_operation_sequence_id
738 ) op
739 WHERE mmtt.transaction_temp_id = p_task_id AND
740 ((mmtt.transaction_source_type_id = g_sourcetype_salesorder
741 AND mmtt.transaction_action_id = g_action_stgxfr)
742 OR
743 (mmtt.transaction_source_type_id = g_sourcetype_intorder
744 AND mmtt.transaction_action_id = g_action_stgxfr)
745 OR
746 mmtt.wms_task_type = g_wms_task_type_stg_move
747 )AND
748 mmtt.operation_plan_id = op.operation_plan_id (+)
749 ;
750
751
752 l_task_drop_loc_rec l_task_drop_loc_cur%ROWTYPE;
753
754 l_operation_sequence_id NUMBER := 2;
755 l_progress VARCHAR2(10);
756
757 -- following three variables are dummy varaibles to hold output parameters
758 -- for validation API
759 l_subinventory_code VARCHAR2(30);
760 l_zone_id NUMBER;
761 l_locator_id NUMBER;
762
763 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
764 BEGIN
765 IF (l_debug = 1) THEN
766 print_debug('Enter validate_pick_drop_Locator', 1);
767 END IF;
768
769 IF (l_debug = 1) THEN
770 print_debug('p_task_type : '|| p_task_type, 4);
771 print_debug('p_task_id : '|| p_task_id, 4);
772 END IF;
773
774
775 l_progress := '10';
776
777 x_return_status := g_ret_sts_success;
778
779 fnd_message.clear;
780
781
782 IF p_task_type NOT IN (g_wms_task_type_pick, g_wms_task_type_stg_move) THEN
783 IF (l_debug = 1) THEN
784 print_debug('validate_pick_drop_Locator: Task type not picking or staging move, do not need to validate locator.', 4);
785 END IF;
786
787 RETURN;
788 END IF;
789
790 l_progress := '20';
791
792 OPEN l_task_drop_loc_cur(l_operation_sequence_id);
793
794 l_progress := '23';
795
796 FETCH l_task_drop_loc_cur INTO l_task_drop_loc_rec;
797
798 IF l_task_drop_loc_cur%notfound THEN
799 IF (l_debug = 1) THEN
800 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);
801 END IF;
802
803 IF l_task_drop_loc_cur%isopen THEN
804 CLOSE l_task_drop_loc_cur;
805 END IF;
806 RETURN;
807 END IF;
808
809 IF l_task_drop_loc_rec.operation_plan_id IS NULL
810 AND p_task_type <> g_wms_task_type_stg_move
811 THEN
812 IF (l_debug = 1) THEN
813 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);
814 END IF;
815
816 IF l_task_drop_loc_cur%isopen THEN
817 CLOSE l_task_drop_loc_cur;
818 END IF;
819 RETURN;
820 END IF;
821
822 l_progress := '25';
823
824 IF (l_debug = 1) THEN
825 print_debug('validate_pick_drop_Locator : transaction_temp_id : '|| l_task_drop_loc_rec.transaction_temp_id, 4);
826 print_debug('pick_release_subinventory : '|| l_task_drop_loc_rec.pick_release_subinventory, 4);
827 print_debug('pick_release_locator_id : '|| l_task_drop_loc_rec.pick_release_locator_id, 4);
828 print_debug('operation_plan_id : '|| l_task_drop_loc_rec.operation_plan_id, 4);
829 print_debug('dest_loc_sel_criteria : '|| l_task_drop_loc_rec.dest_loc_sel_criteria, 4);
830 print_debug('dest_loc_sel_api_id : '|| l_task_drop_loc_rec.dest_loc_sel_api_id, 4);
831 END IF;
832
833 IF p_task_type = g_wms_task_type_stg_move THEN
834
835 --{{
836 -- For staging move only do locator validation if consolidation within delivery
837 -- validation will be at LPN level if consolidation across delivery.
838 --}}
839
840 IF(l_task_drop_loc_rec.consolidation_method_id = 2) THEN
841 IF (l_debug = 1) THEN
842 print_debug('validate_pick_drop_Locator : About to call wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery', 4);
843 END IF;
844
845 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
846 (x_return_status => x_return_status,
847 x_message => x_message,
848 x_locator_id => l_locator_id,
849 x_zone_id => l_zone_id,
850 x_subinventory_code => l_subinventory_code,
851 p_call_mode => 2, -- locator validate
852 p_task_type => p_task_type,
853 p_task_id => p_task_id,
854 p_locator_id => p_locator_id);
855
856
857 IF (l_debug = 1) THEN
858 print_debug('Back to validate_pick_drop_Locator.', 4);
859 print_debug('x_return_status = '||x_return_status, 4);
860 print_debug('x_message = '||x_message, 4);
861 END IF;
862
863 END IF;
864
865 ELSE
866
867
868 IF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_sys_suggested THEN
869 l_progress := '30';
870 IF (l_debug = 1) THEN
871 print_debug('Return locator as system suggested, do not need to validate locator.', 4);
872 END IF;
873
874
875 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_api THEN
876 IF (l_debug = 1) THEN
877 print_debug('Return locator suggested by API.', 4);
878 END IF;
879
880 l_progress := '40';
881
882 IF l_task_drop_loc_rec.dest_loc_sel_api_id = 1 THEN
883 IF (l_debug = 1) THEN
884 print_debug('validate_pick_drop_Locator - Calling WMS_OP_DEST_SYS_APIS.Get_CONS_Loc_For_Delivery. Leaving validate_pick_drop_Locator ... ', 4);
885 END IF;
886
887 l_progress := '50';
888
889 wms_op_dest_sys_apis.Get_CONS_Loc_For_Delivery
890 (x_return_status => x_return_status,
891 x_message => x_message,
892 x_locator_id => l_locator_id,
893 x_zone_id => l_zone_id,
894 x_subinventory_code => l_subinventory_code,
895 p_call_mode => 2, -- locator validate
896 p_task_type => p_task_type,
897 p_task_id => p_task_id,
898 p_locator_id => p_locator_id);
899
900 IF (l_debug = 1) THEN
901 print_debug('Back to update_drop_locator_for_task.', 4);
902 print_debug('x_return_status = '||x_return_status, 4);
903 print_debug('x_message = '||x_message, 4);
904 END IF;
905
906
907
908 ELSIF l_task_drop_loc_rec.dest_loc_sel_api_id = 2 THEN
909 --{{
910 -- For staging move only do locator validation if consolidation within delivery
911 -- validation will be at LPN level if consolidation across delivery.
912 --}}
913
914 IF(l_task_drop_loc_rec.consolidation_method_id = 2) THEN
915 IF (l_debug = 1) THEN
916 print_debug('Calling WMS_OP_DEST_SYS_APIS.Get_Staging_Loc_For_Delivery. Leaving validate_pick_drop_Locator ... ', 4);
917 END IF;
918
919 l_progress := '70';
920
921 wms_op_dest_sys_apis.Get_Staging_Loc_For_Delivery
922 (x_return_status => x_return_status,
923 x_message => x_message,
924 x_locator_id => l_locator_id,
925 x_zone_id => l_zone_id,
926 x_subinventory_code => l_subinventory_code,
927 p_call_mode => 2, -- locator validate
928 p_task_type => p_task_type, -- picking
929 p_task_id => p_task_id,
930 p_locator_id => p_locator_id);
931
932
933 IF (l_debug = 1) THEN
934 print_debug('Back to update_drop_locator_for_task.', 4);
935 print_debug('x_return_status = '||x_return_status, 4);
936 print_debug('x_message = '||x_message, 4);
937 END IF;
938
939 END IF;
940
941
942
943 ELSE
944 IF (l_debug = 1) THEN
945 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);
946 END IF;
947
948 x_return_status := g_ret_sts_unexp_error;
949 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
950
951 END IF;
952
953
954 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_pre_specified THEN
955 IF (l_debug = 1) THEN
956 print_debug('Return locator as pre-specified by user. do not need to validate locator.', 4);
957 END IF;
958
959
960 ELSIF l_task_drop_loc_rec.dest_loc_sel_criteria = g_op_dest_rules_engine THEN
961 IF (l_debug = 1) THEN
962 print_debug('Return locator as suggested by rules engine. do not need to validate locator.', 4);
963 END IF;
964
965
966 ELSE
967 IF (l_debug = 1) THEN
968 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);
969 END IF;
970
971 x_return_status := g_ret_sts_unexp_error;
972 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
973
974 END IF;
975
976 END IF;
977
978 IF l_task_drop_loc_cur%isopen THEN
979 CLOSE l_task_drop_loc_cur;
980 END IF;
981
982
983 EXCEPTION
984
985 WHEN OTHERS THEN
986 IF (l_debug = 1) THEN
987 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);
988 END IF;
989
990 x_return_status := g_ret_sts_unexp_error;
991 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
992
993 IF SQLCODE IS NOT NULL THEN
994 IF (l_debug = 1) THEN
995 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
996 END IF;
997
998
999 END IF;
1000
1001
1002 IF l_task_drop_loc_cur%isopen THEN
1003 CLOSE l_task_drop_loc_cur;
1004 END IF;
1005
1006
1007 END validate_pick_drop_Locator;
1008
1009
1010 END WMS_OP_RUNTIME_PUB_APIS;
1011
[Home] [Help]