1 PACKAGE BODY WMS_OP_DEST_SYS_APIS AS
2 /* $Header: WMSOPDSB.pls 120.11 2005/12/28 11:49:17 stdavid ship $*/
3
4 --
5 -- File : WMSOPDSB.pls
6 -- Content : WMS_OP_DEST_SYS_APIS package body
7 -- Description : System seeded operation plan destination selection APIs.
8 -- Notes :
9 -- Modified : 10/01/2002 lezhang created
10
11
12 g_loc_type_packing_station NUMBER := inv_globals.g_loc_type_packing_station;
13 g_loc_type_storage_loc NUMBER := inv_globals.g_loc_type_storage_loc;
14 g_loc_type_consolidation NUMBER := inv_globals.g_loc_type_consolidation;
15 g_loc_type_staging_lane NUMBER := inv_globals.g_loc_type_staging_lane;
16 g_wms_task_type_pick NUMBER := wms_globals.g_wms_task_type_pick;
17 g_wms_task_type_stg_move NUMBER := wms_globals.g_wms_task_type_stg_move;
18
19 g_ret_sts_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
20 g_ret_sts_unexp_error VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
21 g_ret_sts_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
22
23
24 PROCEDURE print_debug(p_err_msg VARCHAR2,
25 p_level NUMBER)
26 IS
27 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
28 BEGIN
29
30 inv_mobile_helper_functions.tracelog
31 (p_err_msg => p_err_msg,
32 p_module => 'WMS_OP_Dest_Sys_APIs',
33 p_level => p_level);
34
35
36 -- dbms_output.put_line(p_err_msg);
37 END print_debug;
38
39
40 PROCEDURE create_pjm_locator(x_locator_id IN OUT nocopy NUMBER,
41 p_project_id IN NUMBER,
42 p_task_id IN NUMBER)
43 IS
44 PRAGMA AUTONOMOUS_TRANSACTION;
45
46 l_locator_id NUMBER;
47 l_locator_rec inv_validate.LOCATOR;
48 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
49 l_success NUMBER;
50 l_organization_id inv_validate.org;
51 l_subinventory_code inv_validate.sub;
52 BEGIN
53 IF (l_debug = 1) THEN
54 print_debug('Entering create_pjm_locator ', 1);
55 print_debug('x_Locator_Id : '|| x_Locator_Id, 4);
56 print_debug('p_project_id : '|| p_project_id, 4);
57 print_debug('p_task_id : '|| p_task_id, 4);
58
59 END IF;
60
61 SELECT *
62 INTO l_locator_rec
63 FROM
64 mtl_item_locations
65 WHERE inventory_location_id = x_locator_id;
66
67 IF l_locator_rec.project_id = p_project_id
68 AND l_locator_rec.task_id = p_task_id THEN
69 IF (l_debug = 1) THEN
70 print_debug('This locator itself is the logical locator for this project and task ', 4);
71 END IF;
72
73 RETURN;
74 END IF;
75
76
77 BEGIN
78 SELECT inventory_location_id
79 INTO l_locator_id
80 FROM
81 mtl_item_locations
82 WHERE physical_location_id = x_locator_id
83 AND project_id = p_project_id
84 AND task_id = p_task_id
85 AND ROWNUM < 2;
86 EXCEPTION
87 WHEN no_data_found THEN
88 l_locator_id := NULL;
89 END;
90
91
92 IF l_locator_id IS NOT NULL THEN
93 IF (l_debug = 1) THEN
94 print_debug('Locator ID '|| l_locator_id || 'is the logical locator for this project and task ', 4);
95 END IF;
96
97 x_locator_id := l_locator_id;
98
99 RETURN;
100 END IF;
101
102 l_locator_rec.inventory_location_id := NULL;
103 l_locator_rec.physical_location_id := x_locator_id;
104 l_locator_rec.project_id := p_project_id;
105 l_locator_rec.task_id := p_task_id;
106 l_locator_rec.segment19 := p_project_id;
107 l_locator_rec.segment20 := p_task_id;
108
109 print_debug('Before calling inv_validate.validatelocator', 4);
110
111 SELECT *
112 INTO l_organization_id
113 FROM mtl_parameters
114 WHERE organization_id = l_locator_rec.organization_id;
115
116 SELECT *
117 INTO l_subinventory_code
118 FROM mtl_secondary_inventories
119 WHERE secondary_inventory_name = l_locator_rec.subinventory_code
120 AND organization_id = l_locator_rec.organization_id;
121
122
123 l_success := inv_validate.validatelocator
124 (
125 p_locator => l_locator_rec
126 , p_org => l_organization_id
127 , p_sub => l_subinventory_code
128 , p_validation_mode => inv_validate.exists_or_create
129 , p_value_or_id => 'I'
130 );
131
132 print_debug('After calling inv_validate.validatelocator', 4);
133
134 COMMIT;
135
136 IF (l_success = inv_validate.t
137 AND fnd_flex_keyval.new_combination
138 ) THEN
139
140 print_debug('Created new logical locator ' || l_locator_rec.inventory_location_id, 4);
141
142 x_locator_id := l_locator_rec.inventory_location_id;
143
144 RETURN;
145 END IF;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 print_debug('Exception in create_pjm_locator.', 1);
150
151 END create_pjm_locator;
152
153
154 -- API name : Get_CONS_Loc_For_Delivery
155 -- Type : Public
156 -- Function :
157 -- Pre-reqs :
158 --
159 -- Parameters :
160 -- Output:
161 --
162 -- X_Return_status : API exeution status, differen meaning in different
163 -- call mode
164 -- For locator selection:
165 -- 'S' : Locator successfully returned.
166 -- 'E' : Locator is not returned because of application
167 -- error.
168 -- 'U' : Locator is not returned because of unexpected
169 -- error.
170 --
171 -- For locator validation:
172 -- 'S' : Locator is valid according to API logic.
173 -- 'W' : Locator is not valid, and user will be prompt for a warning
174 -- 'E' : Locator is not valid, and user should not be allowed to continue.
175 -- 'U' : API execution encountered unexpected error.
176 --
177 --
178 -- X_Message : Message corresponding to different statuses
179 -- and different call mode
180 -- For locator selection:
181 -- 'S' : Message that needs to displayed before
182 -- displaying the suggested locator.
183 -- 'E' : Reason why locator is not returned.
184 -- 'U' : Message for the unexpected error.
185 --
186 -- For locator validation:
187 -- 'S' : No message.
188 -- 'W' : Reason why locator is invalid.
189 -- 'E' : Reason why locator is invalid.
190 -- 'U' : Message for the unexpected error.
191 --
192 --
193 -- X_locator_ID : Locator returned according to API loc,
194 -- only apply to P_Call_Mode of locator selection.
195 --
196 -- X_Zone_ID : Zone returned according to API loc,
197 -- only apply to P_Call_Mode of locator selection.
198 --
199 -- X_Subinventory_Code : Subinventory code returned according to API loc
200 -- only apply to P_Call_Mode of locator selection.
201 --
202 --
203 -- Input:
204 --
205 -- P_Call_Mode : 1. Locator selection 2. Locator validation
206 --
207 -- P_Task_Type : Refer to lookup type WMS_TASK_TYPES
208 --
209 -- P_Task_ID : Primary key for the corresponding task type.
210 -- e.g. transaction_temp_id in MMTT for picking task type.
211 --
212 -- P_Locator_Id : The locator needs to be validated according to API logic,
213 -- only apply to P_Call_Mode of locator validation,
214 --
215 --
216 -- Version
217 -- Currently version is 1.0
218 --
219
220
221
222 PROCEDURE Get_CONS_Loc_For_Delivery
223 (
224 X_Return_status OUT nocopy VARCHAR2,
225 X_Message OUT nocopy VARCHAR2,
226 X_locator_ID OUT nocopy NUMBER,
227 X_Zone_ID OUT nocopy NUMBER,
228 X_Subinventory_Code OUT nocopy VARCHAR2,
229 P_Call_Mode IN NUMBER DEFAULT NULL,
230 P_Task_Type IN NUMBER DEFAULT NULL,
231 P_Task_ID IN NUMBER DEFAULT NULL,
232 P_Locator_Id IN NUMBER DEFAULT NULL
233 )
234 IS
235 l_progress VARCHAR2(10);
236
237 CURSOR l_current_task_curs
238 IS
239 SELECT mol.carton_grouping_id,
240 wda.delivery_id,
241 mmtt.transaction_temp_id,
242 mmtt.operation_plan_id,
243 mil.project_id,
244 mil.task_id
245 FROM
246 mtl_material_transactions_temp mmtt,
247 mtl_txn_request_lines mol,
248 wsh_delivery_details wdd,
249 wsh_delivery_assignments_v wda,
250 mtl_item_locations mil
251 WHERE
252 mmtt.transaction_temp_id = p_task_id AND
253 mmtt.move_order_line_id = mol.line_id AND
254 mmtt.move_order_line_id = wdd.move_order_line_id AND
255 mmtt.transfer_to_location = mil.inventory_location_id AND
256 mmtt.transfer_organization = mil.organization_id AND
257 wdd.released_status = 'S' AND
258 wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
259 p_call_mode <> 3
260 UNION ALL
261 SELECT mol.carton_grouping_id,
262 wda.delivery_id,
263 mmtt.transaction_temp_id,
264 mmtt.operation_plan_id,
265 mil.project_id,
266 mil.task_id
267 FROM
268 mtl_material_transactions_temp mmtt,
269 mtl_material_transactions_temp pmmtt,
270 mtl_txn_request_lines mol,
271 wsh_delivery_details wdd,
272 wsh_delivery_assignments_v wda,
273 mtl_item_locations mil
274 WHERE
275 mmtt.transaction_temp_id = p_task_id AND
276 mmtt.move_order_line_id = mol.line_id AND
277 mmtt.move_order_line_id = wdd.move_order_line_id AND
278 pmmtt.locator_id = mil.inventory_location_id AND
279 pmmtt.organization_id = mil.organization_id AND
280 wdd.released_status = 'S' AND
281 wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
282 pmmtt.transaction_temp_id = mmtt.parent_line_id AND
283 p_call_mode = 3
284 ;
285
286 CURSOR l_loc_with_same_del_curs
287 (v_delivery_id NUMBER, v_operation_plan_id NUMBER)
288 IS
289 SELECT wdd.subinventory del_subinventory,
290 wdd.locator_id del_locator_id,
291 nvl(mil.inventory_location_type, 3) del_locator_type,
292 wdth.creation_date
293 FROM wsh_delivery_details wdd,
294 wsh_delivery_assignments_v wda,
295 mtl_item_locations mil,
296 mtl_secondary_inventories msi,
297 wms_dispatched_tasks_history wdth
298 WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND
299 wdd.released_status = 'Y' AND
300 wdd.locator_id = mil.inventory_location_id AND
301 nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
302 nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
303 msi.secondary_inventory_name = mil.subinventory_code AND
304 msi.organization_id = mil.organization_id AND
305 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
306 wdd.move_order_line_id = wdth.move_order_line_id AND
307 wdth.operation_plan_id = v_operation_plan_id AND
308 wda.delivery_id = v_delivery_id
309 UNION ALL -- bug 4017457
310 SELECT mmtt.transfer_subinventory del_subinventory,
311 mmtt.transfer_to_location del_locator_id,
312 Nvl(mil.inventory_location_type, 3) del_locator_type,
313 Sysdate creation_date
314 FROM mtl_material_transactions_temp mmtt,
315 wsh_delivery_details wdd,
316 wsh_delivery_assignments_v wda,
317 mtl_item_locations mil,
318 mtl_secondary_inventories msi
319 WHERE mmtt.operation_plan_id = v_operation_plan_id AND
320 mmtt.move_order_line_id = wdd.move_order_line_id AND
321 wdd.delivery_detail_id = wda.delivery_detail_id AND
322 wdd.released_status = 'S' AND
323 wda.delivery_id = v_delivery_id AND
324 mmtt.transfer_to_location = mil.inventory_location_id AND
325 mmtt.transfer_organization = mil.organization_id AND
326 Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
327 Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
328 msi.secondary_inventory_name = mil.subinventory_code AND
329 msi.organization_id = mil.organization_id AND
330 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
331 mmtt.transfer_to_location IS NOT NULL AND
332 mmtt.transfer_subinventory IS NOT NULL AND
333 p_call_mode <> 3
334 UNION ALL -- bug 4017457
335 SELECT pmmtt.subinventory_code del_subinventory,
336 pmmtt.locator_id del_locator_id,
337 Nvl(mil.inventory_location_type, 3) del_locator_type,
338 Sysdate creation_date
339 FROM mtl_material_transactions_temp mmtt,
340 mtl_material_transactions_temp pmmtt,
341 wsh_delivery_details wdd,
342 wsh_delivery_assignments_v wda,
343 mtl_item_locations mil,
344 mtl_secondary_inventories msi
345 WHERE mmtt.operation_plan_id = v_operation_plan_id AND
346 mmtt.move_order_line_id = wdd.move_order_line_id AND
347 wdd.delivery_detail_id = wda.delivery_detail_id AND
348 wdd.released_status = 'S' AND
349 wda.delivery_id = v_delivery_id AND
350 pmmtt.locator_id = mil.inventory_location_id AND
351 pmmtt.organization_id = mil.organization_id AND
352 Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
353 Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
354 msi.secondary_inventory_name = mil.subinventory_code AND
355 msi.organization_id = mil.organization_id AND
356 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
357 pmmtt.locator_id IS NOT NULL AND
358 pmmtt.subinventory_code IS NOT NULL AND
359 pmmtt.transaction_temp_id = mmtt.parent_line_id AND
360 p_call_mode = 3
361 ORDER BY 4 DESC
362 ;
363
364 CURSOR l_loc_with_same_carton_group
365 (v_carton_grouping_id NUMBER, v_operation_plan_id NUMBER)
366 IS
367 SELECT wdd.subinventory mol_subinventory,
368 wdd.locator_id mol_locator_id,
369 nvl(mil.inventory_location_type, 3) mol_locator_type,
370 wdth.creation_date
371 FROM wsh_delivery_details wdd,
372 wsh_delivery_assignments_v wda,
373 mtl_txn_request_lines mol,
374 mtl_item_locations mil,
375 mtl_secondary_inventories msi,
376 wms_dispatched_tasks_history wdth
377 WHERE
378 mol.line_id = wdd.move_order_line_id AND
379 wdd.released_status = 'Y' AND
380 wdd.locator_id = mil.inventory_location_id AND
381 wda.delivery_detail_id = wdd.delivery_detail_id AND
382 wda.delivery_id IS NULL AND -- bug 2768678
383 nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
384 nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
385 msi.secondary_inventory_name = mil.subinventory_code AND
386 msi.organization_id = mil.organization_id AND
387 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
388 wdd.move_order_line_id = wdth.move_order_line_id AND
389 wdth.operation_plan_id = v_operation_plan_id AND
390 mol.carton_grouping_id = v_carton_grouping_id
391 UNION ALL -- bug 4017457
392 SELECT mmtt.transfer_subinventory mol_subinventory,
393 mmtt.transfer_to_location mol_locator_id,
394 Nvl(mil.inventory_location_type, 3) mol_locator_type,
395 Sysdate creation_date
396 FROM mtl_material_transactions_temp mmtt,
397 mtl_txn_request_lines mol,
398 wsh_delivery_details wdd,
399 wsh_delivery_assignments_v wda,
400 mtl_item_locations mil,
401 mtl_secondary_inventories msi
402 WHERE mmtt.operation_plan_id = v_operation_plan_id AND
403 mmtt.move_order_line_id = mol.line_id AND
404 mol.carton_grouping_id = v_carton_grouping_id AND
405 mmtt.move_order_line_id = wdd.move_order_line_id AND
406 wdd.delivery_detail_id = wda.delivery_detail_id AND
407 wda.delivery_id IS NULL AND
408 wdd.released_status = 'S' AND
409 mmtt.transfer_to_location = mil.inventory_location_id AND
410 mmtt.transfer_organization = mil.organization_id AND
411 Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
412 Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
413 msi.secondary_inventory_name = mil.subinventory_code AND
414 msi.organization_id = mil.organization_id AND
415 Nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
416 mmtt.transfer_to_location IS NOT NULL AND
417 mmtt.transfer_subinventory IS NOT NULL AND
418 p_call_mode <> 3
419 UNION ALL -- bug 4017457
420 SELECT pmmtt.subinventory_code mol_subinventory,
421 pmmtt.locator_id mol_locator_id,
422 Nvl(mil.inventory_location_type, 3) mol_locator_type,
423 Sysdate creation_date
424 FROM mtl_material_transactions_temp mmtt,
425 mtl_material_transactions_temp pmmtt,
426 mtl_txn_request_lines mol,
427 wsh_delivery_details wdd,
428 wsh_delivery_assignments_v wda,
429 mtl_item_locations mil,
430 mtl_secondary_inventories msi
431 WHERE mmtt.operation_plan_id = v_operation_plan_id AND
432 mmtt.move_order_line_id = mol.line_id AND
433 mol.carton_grouping_id = v_carton_grouping_id AND
434 mmtt.move_order_line_id = wdd.move_order_line_id AND
435 wdd.delivery_detail_id = wda.delivery_detail_id AND
436 wda.delivery_id IS NULL AND
437 wdd.released_status = 'S' AND
438 pmmtt.locator_id = mil.inventory_location_id AND
439 pmmtt.organization_id = mil.organization_id AND
440 Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
441 Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
442 msi.secondary_inventory_name = mil.subinventory_code AND
443 msi.organization_id = mil.organization_id AND
444 Nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
445 pmmtt.locator_id IS NOT NULL AND
446 pmmtt.subinventory_code IS NOT NULL AND
447 pmmtt.transaction_temp_id = mmtt.parent_line_id AND
448 p_call_mode = 3
449 ORDER BY 4 DESC
450 ;
451
452
453 l_current_task_rec l_current_task_curs%ROWTYPE;
454
455 l_loc_del_rec l_loc_with_same_del_curs%ROWTYPE;
456
457 l_loc_mol_rec l_loc_with_same_carton_group%ROWTYPE;
458
459
460 CURSOR l_empty_CONS_loc IS
461 SELECT mil.inventory_location_id locator_id,
462 mil.subinventory_code subinventory_code,
463 mil.dropping_order,
464 mil.picking_order
465 FROM mtl_item_locations mil,
466 mtl_secondary_inventories msi,
467 mtl_material_transactions_temp mmtt
468 WHERE mmtt.transaction_temp_id = p_task_id
469 AND mil.subinventory_code = mmtt.transfer_subinventory
470 AND mil.organization_id = mmtt.organization_id
471 AND nvl(mil.inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
472 AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
473 AND Nvl(mil.empty_flag, 'Y') = 'Y'
474 AND msi.secondary_inventory_name = mil.subinventory_code
475 AND msi.organization_id = mil.organization_id
476 AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
477 -- bug 4017457
478 AND NOT exists (SELECT 1
479 FROM mtl_material_transactions_temp mmtt2,
480 wsh_delivery_details wdd
481 WHERE mmtt2.move_order_line_id = wdd.move_order_line_id AND
482 wdd.released_status = 'S' AND
483 mmtt2.transfer_organization = mil.organization_id AND
484 mmtt2.transfer_subinventory = mil.subinventory_code AND
485 mmtt2.transfer_to_location = mil.inventory_location_id)
486 AND NOT exists (SELECT 1
487 FROM mtl_material_transactions_temp mmtt3,
488 mtl_material_transactions_temp pmmtt2,
489 wsh_delivery_details wdd
490 WHERE mmtt3.move_order_line_id = wdd.move_order_line_id AND
491 wdd.released_status = 'S' AND
492 pmmtt2.transaction_temp_id = mmtt3.parent_line_id AND
493 pmmtt2.organization_id = mil.organization_id AND
494 pmmtt2.subinventory_code = mil.subinventory_code AND
495 pmmtt2.locator_id = mil.inventory_location_id)
496 AND p_call_mode <> 3
497 UNION ALL
498 SELECT mil.inventory_location_id locator_id,
499 mil.subinventory_code subinventory_code,
500 mil.dropping_order,
501 mil.picking_order
502 FROM mtl_item_locations mil,
503 mtl_secondary_inventories msi,
504 mtl_material_transactions_temp mmtt,
505 mtl_material_transactions_temp pmmtt
506 WHERE mmtt.transaction_temp_id = p_task_id
507 AND mil.subinventory_code = pmmtt.subinventory_code
508 AND mil.organization_id = mmtt.organization_id
509 AND nvl(mil.inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
510 AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
511 AND Nvl(mil.empty_flag, 'Y') = 'Y'
512 AND msi.secondary_inventory_name = mil.subinventory_code
513 AND msi.organization_id = mil.organization_id
514 AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
515 AND pmmtt.transaction_temp_id = mmtt.parent_line_id
516 -- bug 4017457
517 AND NOT exists (SELECT 1
518 FROM mtl_material_transactions_temp mmtt2,
519 wsh_delivery_details wdd
520 WHERE mmtt2.move_order_line_id = wdd.move_order_line_id AND
521 wdd.released_status = 'S' AND
522 mmtt2.transfer_organization = mil.organization_id AND
523 mmtt2.transfer_subinventory = mil.subinventory_code AND
524 mmtt2.transfer_to_location = mil.inventory_location_id)
525 AND NOT exists (SELECT 1
526 FROM mtl_material_transactions_temp mmtt3,
527 mtl_material_transactions_temp pmmtt2,
528 wsh_delivery_details wdd
529 WHERE mmtt3.move_order_line_id = wdd.move_order_line_id AND
530 wdd.released_status = 'S' AND
531 pmmtt2.transaction_temp_id = mmtt3.parent_line_id AND
532 pmmtt2.organization_id = mil.organization_id AND
533 pmmtt2.subinventory_code = mil.subinventory_code AND
534 pmmtt2.locator_id = mil.inventory_location_id)
535 AND p_call_mode = 3
536 ORDER BY 3,4;
537
538 l_empty_CONS_loc_rec l_empty_CONS_loc%ROWTYPE;
539 l_empty_CONS_loc_count NUMBER := 0;
540
541 l_cons_loc_exists_flag NUMBER;
542 l_pick_release_subinventory VARCHAR2(30);
543 l_pick_release_locator_id NUMBER;
544 l_validate_loc_type NUMBER;
545 l_validate_loc_subinventory VARCHAR2(30);
546 l_validate_loc_empty_flag VARCHAR2(1);
547
548 l_loc_disable_date DATE;
549 l_sub_disable_date DATE;
550
551 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
552 BEGIN
553 x_return_status := G_RET_STS_SUCCESS;
554 l_progress := '10';
555
556 IF (l_debug = 1) THEN
557 print_debug('Enter Get_CONS_Loc_For_Delivery '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
558 END IF;
559
560
561 IF (l_debug = 1) THEN
562 print_debug('P_Call_Mode : '|| P_Call_Mode, 4);
563 print_debug('P_Task_Type : '|| P_Task_Type, 4);
564 print_debug('P_Task_ID : '|| P_Task_ID, 4);
565 print_debug('P_Locator_Id : '|| P_Locator_Id, 4);
566 END IF;
567
568
569
570 -- Input parameter validation
571
572 IF p_call_mode in (1,3) THEN -- locator selection
573 IF p_task_type IS NULL OR p_task_id IS NULL THEN
574 x_return_status := g_ret_sts_unexp_error;
575 IF (l_debug = 1) THEN
576 print_debug('Invalid input: For locator selection p_task_type and p_task_id cannot be NULL.', 4);
577 END IF;
578
579 RETURN;
580 END IF;
581
582 ELSIF p_call_mode = 2 THEN -- locator validation
583 IF p_task_type IS NULL OR
584 p_task_id IS NULL OR
585 p_locator_id IS NULL THEN
586 x_return_status := g_ret_sts_unexp_error;
587 IF (l_debug = 1) THEN
588 print_debug('Invalid input: For locator selection p_locator_id, p_task_type and p_task_id cannot be NULL.', 4);
589 END IF;
590 RETURN;
591
592 END IF;
593
594 ELSE -- invalid p_call_mode
595
596 x_return_status := g_ret_sts_unexp_error;
597 IF (l_debug = 1) THEN
598 print_debug('Invalid input: P_Call_Mode should be 1,2 or 3.', 4);
599 END IF;
600
601 RETURN;
602
603 END IF;
604
605
606 l_progress := '20';
607
608 IF p_task_type IN (g_wms_task_type_pick, g_wms_task_type_stg_move) THEN -- picking or staging move
609
610 -- Get a consolidation locator where same delivery (or carton_grouping_ID) with the same operation plan ID has been dropped to
611 OPEN l_current_task_curs;
612
613 l_progress := '30';
614
615 LOOP
616 -- this loop will only return one record, hense no performance concern
617 -- still used loop to conform to the standard way cursor is handled
618
619 FETCH l_current_task_curs INTO l_current_task_rec;
620 EXIT WHEN l_current_task_curs%notfound;
621
622 l_progress := '40';
623
624
625 IF (l_debug = 1) THEN
626 print_debug('Searching consolidation locators for this tasks whose ', 4);
627 print_debug('transaction_temp_id : ' ||l_current_task_rec.transaction_temp_id, 4);
628 print_debug('with operation_plan_id : '||l_current_task_rec.operation_plan_id, 4);
629 print_debug('and delivery ID: ' || l_current_task_rec.delivery_id, 4);
630 print_debug('or carton_grouping_ID: ' || l_current_task_rec.carton_grouping_ID, 4);
631 END IF;
632
633
634 IF l_current_task_rec.delivery_id IS NOT NULL THEN
635
636 IF (l_debug = 1) THEN
637 print_debug('Look for locator with same delivery.', 4);
638 END IF;
639
640
641 OPEN l_loc_with_same_del_curs
642 (l_current_task_rec.delivery_id,
643 l_current_task_rec.operation_plan_id);
644
645 LOOP
646 FETCH l_loc_with_same_del_curs INTO l_loc_del_rec;
647 EXIT WHEN l_loc_with_same_del_curs%notfound;
648
649 l_progress := '50';
650
651 IF p_call_mode IN (1,3) THEN --suggestion
652 IF (l_debug = 1) THEN
653 print_debug('Found one consolidation locator for the same delivery:', 4);
654 print_debug('subinventory : ' || l_loc_del_rec.del_subinventory, 4);
655 print_debug('locator ID : ' || l_loc_del_rec.del_locator_id, 4);
656 END IF;
657
658 x_zone_id := NULL;
659 x_subinventory_code := l_loc_del_rec.del_subinventory;
660 x_locator_id := l_loc_del_rec.del_locator_id;
661
662 IF l_current_task_rec.project_id IS NOT NULL THEN
663 create_pjm_locator(x_locator_id => x_locator_id,
664 p_project_id => l_current_task_rec.project_id,
665 p_task_id => l_current_task_rec.task_id);
666 END IF;
667
668 IF l_current_task_curs%isopen THEN
669 CLOSE l_current_task_curs;
670 END IF;
671
672 IF l_loc_with_same_del_curs%isopen THEN
673 CLOSE l_loc_with_same_del_curs;
674 END IF;
675
676 RETURN;
677
678 ELSIF p_call_mode = 2 THEN -- validation
679 IF l_loc_del_rec.del_locator_id = p_locator_id THEN
680 IF (l_debug = 1) THEN
681 print_debug('This is a valid consolidation locator with the same delivery.', 4);
682 END IF;
683
684
685 IF l_current_task_curs%isopen THEN
686 CLOSE l_current_task_curs;
687 END IF;
688
689 IF l_loc_with_same_del_curs%isopen THEN
690 CLOSE l_loc_with_same_del_curs;
691 END IF;
692
693 RETURN;
694 END IF;
695
696 END IF;
697
698
699 END LOOP; -- end l_loc_with_same_del_curs cursor loop
700
701
702 CLOSE l_loc_with_same_del_curs;
703
704 ELSIF l_current_task_rec.carton_grouping_id IS NOT NULL THEN
705
706 OPEN l_loc_with_same_carton_group
707 (l_current_task_rec.carton_grouping_ID,
708 l_current_task_rec.operation_plan_id);
709
710 LOOP
711 FETCH l_loc_with_same_carton_group INTO l_loc_mol_rec;
712 EXIT WHEN l_loc_with_same_carton_group%notfound;
713
714 l_progress := '60';
715
716 IF p_call_mode IN (1,3) THEN --suggestion
717
718 IF (l_debug = 1) THEN
719 print_debug('Found one consolidation locator for the same carton_grouping_ID:', 4);
720 print_debug('subinventory : ' || l_loc_mol_rec.mol_subinventory, 4);
721 print_debug('locator ID : ' || l_loc_mol_rec.mol_locator_id, 4);
722 END IF;
723
724
725 x_zone_id := NULL;
726 x_subinventory_code := l_loc_mol_rec.mol_subinventory;
727 x_locator_id := l_loc_mol_rec.mol_locator_id;
728
729 IF l_current_task_rec.project_id IS NOT NULL THEN
730 create_pjm_locator(x_locator_id => x_locator_id,
731 p_project_id => l_current_task_rec.project_id,
732 p_task_id => l_current_task_rec.task_id);
733 END IF;
734
735 IF l_current_task_curs%isopen THEN
736 CLOSE l_current_task_curs;
737 END IF;
738
739 IF l_loc_with_same_carton_group%isopen THEN
740 CLOSE l_loc_with_same_carton_group;
741 END IF;
742
743 RETURN;
744
745 ELSIF p_call_mode = 2 THEN -- validation
746 IF l_loc_mol_rec.mol_locator_id = p_locator_id THEN
747 IF (l_debug = 1) THEN
748 print_debug('This is a valid consolidation locator with the carton_grouping_ID.', 4);
749 END IF;
750
751
752 IF l_current_task_curs%isopen THEN
753 CLOSE l_current_task_curs;
754 END IF;
755
756 IF l_loc_with_same_carton_group%isopen THEN
757 CLOSE l_loc_with_same_carton_group;
758 END IF;
759
760 RETURN;
761 END IF;
762 END IF;
763
764 END LOOP; -- end l_loc_with_same_carton_group cursor loop
765
766 CLOSE l_loc_with_same_carton_group;
767
768 END IF;
769
770
771 END LOOP; -- end l_current_task_curs cursor loop
772
773
774 l_progress := '70';
775
776 CLOSE l_current_task_curs;
777
778 -- Get empty consolidation locator within the pick release subinventory
779
780 IF (l_debug = 1) THEN
781 print_debug('Searching for empty consolidation locator in staging subinventory for task ', 4);
782 print_debug('with transaction_temp_id : ' || p_task_id, 4);
783 END IF;
784
785
786 OPEN l_empty_CONS_loc;
787
788 l_progress := '80';
789
790 LOOP
791 FETCH l_empty_CONS_loc INTO l_empty_CONS_loc_rec;
792 EXIT WHEN l_empty_CONS_loc%notfound;
793
794 l_progress := '90';
795
796 IF p_call_mode IN (1,3) THEN --suggestion
797
798 IF (l_debug = 1) THEN
799 print_debug('Found one empty consolidation locator:', 4);
800 print_debug('subinventory : ' || l_empty_CONS_loc_rec.subinventory_code, 4);
801 print_debug('locator ID : ' || l_empty_CONS_loc_rec.locator_id, 4);
802 END IF;
803
804
805 x_zone_id := NULL;
806 x_subinventory_code := l_empty_CONS_loc_rec.subinventory_code;
807 x_locator_id := l_empty_CONS_loc_rec.locator_id;
808
809 IF l_current_task_rec.project_id IS NOT NULL THEN
810 create_pjm_locator(x_locator_id => x_locator_id,
811 p_project_id => l_current_task_rec.project_id,
812 p_task_id => l_current_task_rec.task_id);
813 END IF;
814
815 CLOSE l_empty_CONS_loc;
816 RETURN;
817
818 ELSIF p_call_mode = 2 THEN -- validation
819 IF l_empty_CONS_loc_rec.locator_id = p_locator_id THEN
820 IF (l_debug = 1) THEN
821 print_debug('This is a valid empty consolidation locator within the pick release subinventory.', 4);
822 END IF;
823
824 CLOSE l_empty_CONS_loc;
825 RETURN;
826 END IF;
827
828 l_empty_cons_loc_count := l_empty_cons_loc_count + 1;
829 END IF;
830
831
832 END LOOP;
833
834 CLOSE l_empty_CONS_loc;
835
836 l_progress := '100';
837
838
839 -- Return pick release locator and display proper message (No consolidation or consolidation full)
840 SELECT
841 mmtt.transfer_subinventory,
842 mmtt.transfer_to_location
843 INTO
844 l_pick_release_subinventory,
845 l_pick_release_locator_id
846 FROM
847 mtl_material_transactions_temp mmtt
848 WHERE mmtt.transaction_temp_id = p_task_id;
849
850 l_progress := '110';
851
852 BEGIN
853 IF (p_call_mode <> 3) THEN
854 SELECT 1
855 INTO l_cons_loc_exists_flag
856 FROM mtl_item_locations mil,
857 mtl_secondary_inventories msi,
858 mtl_material_transactions_temp mmtt
859 WHERE mmtt.transaction_temp_id = p_task_id
860 AND mil.subinventory_code = mmtt.transfer_subinventory
861 AND mil.organization_id = mmtt.organization_id
862 AND nvl(inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
863 AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
864 AND msi.secondary_inventory_name = mil.subinventory_code
865 AND msi.organization_id = mil.organization_id
866 AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
867 ;
868 ELSE
869 SELECT 1
870 INTO l_cons_loc_exists_flag
871 FROM mtl_item_locations mil,
872 mtl_secondary_inventories msi,
873 mtl_material_transactions_temp mmtt,
874 mtl_material_transactions_temp pmmtt
875 WHERE mmtt.transaction_temp_id = p_task_id
876 AND pmmtt.transaction_temp_id = mmtt.parent_line_id
877 AND mil.subinventory_code = pmmtt.subinventory_code
878 AND mil.organization_id = mmtt.organization_id
879 AND nvl(inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
880 AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
881 AND msi.secondary_inventory_name = mil.subinventory_code
882 AND msi.organization_id = mil.organization_id
883 AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
884 ;
885 END IF;
886 EXCEPTION
887 WHEN too_many_rows THEN
888 l_cons_loc_exists_flag := 1;
889 WHEN no_data_found THEN
890 l_cons_loc_exists_flag := 0;
891 END;
892
893 l_progress := '120';
894
895
896 IF p_call_mode IN (1,3) THEN -- suggestion
897
898 l_progress := '130';
899
900 x_zone_id := NULL;
901 x_subinventory_code := l_pick_release_subinventory;
902 x_locator_id := l_pick_release_locator_id;
903
904 IF l_current_task_rec.project_id IS NOT NULL THEN
905 create_pjm_locator(x_locator_id => x_locator_id,
906 p_project_id => l_current_task_rec.project_id,
907 p_task_id => l_current_task_rec.task_id);
908 END IF;
909
910 IF (l_debug = 1) THEN
911 print_debug('Return pick release sub : '|| x_subinventory_code ||' and locator ID : '||x_locator_id, 4);
912 print_debug('Exsiting flag for consolidation locators for the pick release staging sub : '||l_cons_loc_exists_flag, 4);
913 END IF;
914
915
916 IF l_cons_loc_exists_flag > 0 THEN
917 x_return_status := 'W';
918 x_message := fnd_message.get_string('WMS', 'WMS_CONS_LOC_FULL');
919 fnd_message.set_name('WMS', 'WMS_CONS_LOC_FULL');
920 FND_MSG_PUB.ADD;
921 IF (l_debug = 1) THEN
922 print_debug('All consolidation locators are full.', 4);
923 END IF;
924
925 ELSE
926 x_return_status := 'W';
927 x_message := fnd_message.get_string('WMS', 'WMS_CONS_LOC_NOT_DEFINED');
928 fnd_message.set_name('WMS', 'WMS_CONS_LOC_NOT_DEFINED');
929 FND_MSG_PUB.ADD;
930 IF (l_debug = 1) THEN
931 print_debug('Consolidation locators are not defined for staging sub.', 4);
932 END IF;
933
934 END IF;
935
936 RETURN;
937
938 END IF;
939
940
941 IF p_call_mode = 2 THEN
942
943 l_progress := '140';
944
945 SELECT Nvl(mil.inventory_location_type, 3),
946 mil.subinventory_code,
947 Nvl(mil.empty_flag, 'Y'),
948 Nvl(mil.disable_date, trunc(sysdate+1)),
949 Nvl(msi.disable_date, trunc(sysdate+1))
950 INTO l_validate_loc_type,
951 l_validate_loc_subinventory,
952 l_validate_loc_empty_flag,
953 l_loc_disable_date,
954 l_sub_disable_date
955 FROM mtl_item_locations mil,
956 mtl_secondary_inventories msi
957 WHERE mil.inventory_location_id = p_locator_id
958 AND msi.secondary_inventory_name = mil.subinventory_code
959 AND msi.organization_id = mil.organization_id
960 ;
961
962 l_progress := '150';
963
964 IF l_validate_loc_empty_flag = 'Y' AND
965 l_validate_loc_subinventory <> l_pick_release_subinventory AND
966 l_loc_disable_date > trunc(sysdate) AND
967 l_sub_disable_date > trunc(sysdate) AND
968 l_validate_loc_type = 4 THEN
969
970 IF (l_debug = 1) THEN
971 print_debug('This is a valid locator since it is an empty consolidation locator in a different subinventory.', 4);
972 END IF;
973
974 RETURN;
975
976 END IF;
977
978
979 -- Up to this point we know this locator is invalid
980 -- Need to perform invalid reason check
981
982 l_progress := '160';
983
984 IF l_validate_loc_type <> G_LOC_TYPE_CONSOLIDATION THEN -- This is not a consolidation locator
985
986 IF (l_debug = 1) THEN
987 print_debug('This locator is invalid since it is not a consolidation locator.', 4);
988 END IF;
989
990
991
992 x_message := fnd_message.get_string('WMS', 'WMS_NOT_A_CONS_LOC');
993 fnd_message.set_name('WMS', 'WMS_NOT_A_CONS_LOC');
994 FND_MSG_PUB.ADD;
995 x_return_status := 'W';
996
997 RETURN;
998
999 ELSIF l_validate_loc_empty_flag <> 'Y' THEN -- locator is not empty
1000
1001 IF (l_debug = 1) THEN
1002 print_debug('This locator is invalid since it contains other delivery.', 4);
1003 END IF;
1004
1005 IF l_empty_cons_loc_count > 0 THEN
1006 IF (l_debug = 1) THEN
1007 print_debug(' And there are other empty consolidation locators.', 4);
1008 END IF;
1009
1010 x_message := fnd_message.get_string('WMS', 'WMS_EMPTY_CONS_EXIST');
1011 fnd_message.set_name('WMS', 'WMS_EMPTY_CONS_EXIST');
1012 FND_MSG_PUB.ADD;
1013 x_return_status := g_ret_sts_error;
1014 ELSE
1015 IF (l_debug = 1) THEN
1016 print_debug(' But there are NOT any other empty consolidation locators.', 4);
1017 END IF;
1018
1019 fnd_message.set_name('WMS', 'WMS_LOC_CONTN_OTHER_DEL');
1020 FND_MSG_PUB.ADD;
1021 x_message := fnd_message.get_string('WMS', 'WMS_LOC_CONTN_OTHER_DEL');
1022 x_return_status := 'W';
1023 END IF;
1024
1025 RETURN;
1026
1027 ELSE
1028 x_message := fnd_message.get_string('INV', 'INV_INT_LOCCODE');
1029 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
1030 FND_MSG_PUB.ADD;
1031 x_return_status := 'W';
1032 RETURN;
1033
1034 END IF;
1035
1036
1037 END IF;
1038
1039 l_progress := '170';
1040
1041 ELSE -- invalid p_task_type
1042 x_return_status := g_ret_sts_unexp_error;
1043 IF (l_debug = 1) THEN
1044 print_debug('Invalid input: P_Task_Type of '||p_task_type||' is not supported. ', 4);
1045 END IF;
1046
1047
1048 RETURN;
1049 END IF;
1050
1051
1052
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 IF l_current_task_curs%isopen THEN
1057 CLOSE l_current_task_curs;
1058 END IF;
1059 IF l_loc_with_same_del_curs%isopen THEN
1060 CLOSE l_loc_with_same_del_curs;
1061 END IF;
1062 IF l_loc_with_same_carton_group%isopen THEN
1063 CLOSE l_loc_with_same_carton_group;
1064 END IF;
1065 IF l_empty_CONS_loc%isopen THEN
1066 CLOSE l_empty_CONS_loc;
1067 END IF;
1068
1069 IF (l_debug = 1) THEN
1070 print_debug('Other exception in Get_CONS_Loc_For_Delivery '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
1071 END IF;
1072
1073
1074 x_return_status := g_ret_sts_unexp_error;
1075
1076 IF SQLCODE IS NOT NULL THEN
1077 x_message := x_message || ' with SQL error: ' || SQLERRM(SQLCODE);
1078 IF (l_debug = 1) THEN
1079 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
1080 END IF;
1081
1082
1083 END IF;
1084 END Get_CONS_Loc_For_Delivery;
1085
1086
1087
1088
1089 -- API name : Get_Staging_Loc_For_Delivery
1090 -- Type : Public
1091 -- Function :
1092 -- Pre-reqs :
1093 --
1094 -- Parameters :
1095 -- Output:
1096 --
1097 -- X_Return_status : API exeution status, differen meaning in different
1098 -- call mode
1099 -- For locator selection:
1100 -- 'S' : Locator successfully returned.
1101 -- 'E' : Locator is not returned because of application
1102 -- error.
1103 -- 'U' : Locator is not returned because of unexpected
1104 -- error.
1105 --
1106 -- For locator validation:
1107 -- 'S' : Locator is valid according to API logic.
1108 -- 'W' : Locator is not valid, and user will be prompt for a warning
1109 -- 'E' : Locator is not valid, and user should not be allowed to continue.
1110 -- 'U' : API execution encountered unexpected error.
1111 --
1112 --
1113 -- X_Message : Message corresponding to different statuses
1114 -- and different call mode
1115 -- For locator selection:
1116 -- 'S' : Message that needs to displayed before
1117 -- displaying the suggested locator.
1118 -- 'E' : Reason why locator is not returned.
1119 -- 'U' : Message for the unexpected error.
1120 --
1121 -- For locator validation:
1122 -- 'S' : No message.
1123 -- 'W' : Reason why locator is invalid.
1124 -- 'E' : Reason why locator is invalid.
1125 -- 'U' : Message for the unexpected error.
1126 --
1127 --
1128 -- X_locator_ID : Locator returned according to API loc,
1129 -- only apply to P_Call_Mode of locator selection.
1130 --
1131 -- X_Zone_ID : Zone returned according to API loc,
1132 -- only apply to P_Call_Mode of locator selection.
1133 --
1134 -- X_Subinventory_Code : Subinventory code returned according to API loc
1135 -- only apply to P_Call_Mode of locator selection.
1136 --
1137 --
1138 -- Input:
1139 --
1140 -- P_Call_Mode : 1. Locator selection 2. Locator validation
1141 --
1142 -- P_Task_Type : Refer to lookup type WMS_TASK_TYPES
1143 --
1144 -- P_Task_ID : Primary key for the corresponding task type.
1145 -- e.g. transaction_temp_id in MMTT for picking task type.
1146 --
1147 -- P_Locator_Id : The locator needs to be validated according to API logic,
1148 -- only apply to P_Call_Mode of locator validation,
1149 --
1150 --
1151 -- Version
1152 -- Currently version is 1.0
1153 --
1154
1155 --{{
1156 -- Need to throughly test Get_Staging_Loc_For_Delivery.
1157 -- Delivery based consolidation at staging lane was never properly tested before.
1158 --}}
1159
1160 PROCEDURE Get_Staging_Loc_For_Delivery
1161 (
1162 X_Return_status OUT nocopy VARCHAR2,
1163 X_Message OUT nocopy VARCHAR2,
1164 X_locator_ID OUT nocopy NUMBER,
1165 X_Zone_ID OUT nocopy NUMBER,
1166 X_Subinventory_Code OUT nocopy VARCHAR2,
1167 P_Call_Mode IN NUMBER DEFAULT NULL,
1168 P_Task_Type IN NUMBER DEFAULT NULL,
1169 P_Task_ID IN NUMBER DEFAULT NULL,
1170 P_Locator_Id IN NUMBER DEFAULT NULL,
1171 p_mol_id IN NUMBER DEFAULT NULL
1172 )
1173 IS
1174 l_progress VARCHAR2(10);
1175
1176 CURSOR l_current_task_curs
1177 IS
1178 SELECT mol.carton_grouping_id,
1179 wda.delivery_id,
1180 mmtt.transaction_temp_id,
1181 -- mmtt.operation_plan_id,
1182 mil.project_id,
1183 mil.task_id,
1184 mil.organization_id
1185 FROM
1186 mtl_material_transactions_temp mmtt,
1187 mtl_txn_request_lines mol,
1188 wsh_delivery_details wdd1,
1189 wsh_delivery_details wdd2,
1190 wsh_delivery_assignments_v wda,
1191 mtl_item_locations mil
1192 WHERE
1193 mmtt.transaction_temp_id = p_task_id AND
1194 wdd1.move_order_line_id = mol.line_id AND
1195 mmtt.content_lpn_id = wdd2.lpn_id AND
1196 mmtt.transfer_to_location = mil.inventory_location_id AND
1197 mmtt.transfer_organization = mil.organization_id AND
1198 wdd1.released_status = 'Y' AND
1199 wdd1.delivery_detail_id = wda.delivery_detail_id AND
1200 wdd2.delivery_detail_id = wda.parent_delivery_detail_id AND
1201 p_call_mode <> 3 AND
1202 ROWNUM < 2
1203 UNION ALL
1204 SELECT mol.carton_grouping_id,
1205 wda.delivery_id,
1206 mmtt.transaction_temp_id,
1207 -- mmtt.operation_plan_id,
1208 mil.project_id,
1209 mil.task_id,
1210 mil.organization_id
1211 FROM
1212 mtl_material_transactions_temp mmtt,
1213 mtl_material_transactions_temp pmmtt,
1214 mtl_txn_request_lines mol,
1215 wsh_delivery_details wdd,
1216 wsh_delivery_assignments_v wda,
1217 mtl_item_locations mil
1218 WHERE
1219 mmtt.transaction_temp_id = p_task_id AND
1220 mmtt.move_order_line_id = mol.line_id AND
1221 mmtt.move_order_line_id = wdd.move_order_line_id AND
1222 pmmtt.locator_id = mil.inventory_location_id AND
1223 pmmtt.organization_id = mil.organization_id AND
1224 wdd.released_status = 'S' AND
1225 wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
1226 pmmtt.transaction_temp_id = mmtt.parent_line_id AND
1227 p_call_mode = 3 AND
1228 ROWNUM < 2
1229 ;
1230
1231
1232 CURSOR l_mol_curs
1233 IS
1234 SELECT mol.carton_grouping_id,
1235 wda.delivery_id,
1236 NULL,
1237 -- mmtt.operation_plan_id,
1238 mol.project_id,
1239 mol.task_id,
1240 mol.organization_id
1241 FROM
1242 mtl_txn_request_lines mol,
1243 wsh_delivery_details wdd,
1244 wsh_delivery_assignments_v wda
1245 WHERE
1246 mol.line_id = p_mol_id AND
1247 wdd.move_order_line_id = mol.line_id AND
1248 wdd.released_status = 'S' AND
1249 wdd.delivery_detail_id = wda.delivery_detail_id AND
1250 ROWNUM < 2
1251 ;
1252
1253 CURSOR l_loc_with_same_del_curs
1254 (v_delivery_id NUMBER)
1255 IS
1256 SELECT wdd.subinventory del_subinventory,
1257 wdd.locator_id del_locator_id,
1258 nvl(mil.inventory_location_type, 3) del_locator_type
1259 FROM wsh_delivery_details wdd,
1260 wsh_delivery_assignments_v wda,
1261 mtl_item_locations mil,
1262 mtl_secondary_inventories msi
1263 WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND
1264 wdd.released_status = 'Y' AND
1265 wdd.locator_id = mil.inventory_location_id AND
1266 (nvl(mil.inventory_location_type, g_loc_type_storage_loc) = G_LOC_TYPE_STAGING_LANE
1267 OR(nvl(mil.inventory_location_type, g_loc_type_storage_loc) IN (g_loc_type_staging_lane, g_loc_type_consolidation, g_loc_type_packing_station) AND
1268 p_call_mode = 2)
1269 ) AND
1270 -- wdd.move_order_line_id = wdth.move_order_line_id AND
1271 -- wdth.operation_plan_id = v_operation_plan_id AND
1272 nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
1273 msi.secondary_inventory_name = mil.subinventory_code AND
1274 msi.organization_id = mil.organization_id AND
1275 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
1276 wda.delivery_id = v_delivery_id
1277 ORDER BY wdd.last_update_date DESC
1278 ;
1279
1280 CURSOR l_loc_with_same_carton_group
1281 (v_carton_grouping_id NUMBER)
1282 IS
1283 SELECT wdd.subinventory mol_subinventory,
1284 wdd.locator_id mol_locator_id,
1285 nvl(mil.inventory_location_type, 3) mol_locator_type
1286 FROM wsh_delivery_details wdd,
1287 wsh_delivery_assignments_v wda,
1288 mtl_txn_request_lines mol,
1289 mtl_item_locations mil,
1290 mtl_secondary_inventories msi
1291 WHERE
1292 mol.line_id = wdd.move_order_line_id AND
1293 wdd.released_status = 'Y' AND
1294 wdd.locator_id = mil.inventory_location_id AND
1295 wda.delivery_detail_id = wdd.delivery_detail_id AND
1296 wda.delivery_id IS NULL AND -- bug 2768678
1297 (nvl(mil.inventory_location_type, g_loc_type_storage_loc) = G_LOC_TYPE_STAGING_LANE
1298 OR(nvl(mil.inventory_location_type, g_loc_type_storage_loc) IN (g_loc_type_staging_lane, g_loc_type_consolidation, g_loc_type_packing_station) AND
1299 p_call_mode = 2)
1300 ) AND
1301 -- wdd.move_order_line_id = wdth.move_order_line_id AND
1302 -- wdth.operation_plan_id = v_operation_plan_id AND
1303 nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
1304 msi.secondary_inventory_name = mil.subinventory_code AND
1305 msi.organization_id = mil.organization_id AND
1306 nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
1307 mol.carton_grouping_id = v_carton_grouping_id
1308 ORDER BY wdd.last_update_date DESC
1309 ;
1310
1311 --Cursor to get the Trip stop id associated with a delivery
1312 CURSOR l_del_trip_stop
1313 (v_del_id IN NUMBER)
1314 IS
1315 SELECT wts.STOP_ID
1316 FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl
1317 WHERE wdl.delivery_id = v_del_id AND
1318 wts.stop_id = wdl.pick_up_stop_id AND
1319 wts.trip_id = wt.trip_id;
1320
1321
1322
1323 l_current_task_rec l_current_task_curs%ROWTYPE;
1324
1325 l_loc_del_rec l_loc_with_same_del_curs%ROWTYPE;
1326
1327 l_loc_mol_rec l_loc_with_same_carton_group%ROWTYPE;
1328
1329 l_pick_release_subinventory VARCHAR2(30);
1330 l_pick_release_locator_id NUMBER;
1331 l_pick_release_locator_type NUMBER;
1332 l_validate_loc_type NUMBER;
1333 l_validate_loc_subinventory VARCHAR2(30);
1334 l_validate_loc_empty_flag VARCHAR2(1);
1335
1336 l_loc_disable_date DATE;
1337 l_sub_disable_date DATE;
1338
1339 l_stop_id NUMBER;
1340 l_api_return_status VARCHAR2(1);
1341 l_msg_count NUMBER;
1342 l_msg_data VARCHAR2(2000);
1343
1344
1345 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1346 BEGIN
1347 x_return_status := G_RET_STS_SUCCESS;
1348 l_progress := '10';
1349
1350 IF (l_debug = 1) THEN
1351 print_debug('Enter Get_Staging_Loc_For_Delivery '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1352 END IF;
1353
1354 IF (l_debug = 1) THEN
1355 print_debug('P_Call_Mode : '|| P_Call_Mode, 4);
1356 print_debug('P_Task_Type : '|| P_Task_Type, 4);
1357 print_debug('P_Task_ID : '|| P_Task_ID, 4);
1358 print_debug('P_Locator_Id : '|| P_Locator_Id, 4);
1359 END IF;
1360
1361
1362
1363 -- Input parameter validation
1364
1365 IF p_call_mode IN (1,3) THEN -- locator selection
1366 IF p_task_type IS NULL OR (p_task_id IS NULL AND p_mol_id IS NULL) THEN
1367 x_return_status := g_ret_sts_unexp_error;
1368 IF (l_debug = 1) THEN
1369 print_debug('Invalid input: For locator selection p_task_type and p_task_id cannot be NULL.', 4);
1370 END IF;
1371
1372 RETURN;
1373
1374 END IF;
1375
1376 ELSIF p_call_mode = 2 THEN -- locator validation
1377 IF p_task_type IS NULL OR
1378 p_task_id IS NULL OR
1379 p_locator_id IS NULL THEN
1380 x_return_status := g_ret_sts_unexp_error;
1381 IF (l_debug = 1) THEN
1382 print_debug('Invalid input: For locator selection p_locator_id, p_task_type and p_task_id cannot be NULL.', 4);
1383 END IF;
1384 RETURN;
1385
1386 END IF;
1387
1388 ELSE -- invalid p_call_mode
1389
1390 x_return_status := g_ret_sts_unexp_error;
1391 IF (l_debug = 1) THEN
1392 print_debug('Invalid input: P_Call_Mode should be 1,2 or 3.', 4);
1393 END IF;
1394
1395
1396 RETURN;
1397
1398 END IF;
1399
1400
1401 l_progress := '20';
1402
1403 IF p_task_type IN (g_wms_task_type_pick, g_wms_task_type_stg_move) THEN -- picking
1404
1405 -- Get a staging locator where same delivery (or carton_grouping_ID) with the same operation plan ID has been dropped to
1406
1407
1408 --
1409 -- {{
1410 -- Need to test following cases for delivery based staging lane suggestion when move order line ID is given:
1411 -- 1. MOL is lined to a WDD and its delivery trough crossdock
1412 -- 2. Project and task is enabled.
1413 -- }}
1414 --
1415 IF(p_mol_id IS NULL) THEN
1416 OPEN l_current_task_curs;
1417 ELSE
1418 OPEN l_mol_curs;
1419 END IF;
1420
1421 l_progress := '30';
1422
1423 LOOP
1424 -- this loop will only return one record, hense no performance concern
1425 -- still used loop to conform to the standard way cursor is handled
1426
1427 IF(p_mol_id IS NULL) THEN
1428 IF (l_debug = 1) THEN
1429 print_debug('Open l_current_task_curs cursor.', 4);
1430 END IF;
1431
1432 FETCH l_current_task_curs INTO l_current_task_rec;
1433 EXIT WHEN l_current_task_curs%notfound;
1434 ELSE
1435 IF (l_debug = 1) THEN
1436 print_debug('Open l_mol_curs cursor.', 4);
1437 END IF;
1438
1439 FETCH l_mol_curs INTO l_current_task_rec;
1440 EXIT WHEN l_mol_curs%notfound;
1441 END IF;
1442
1443
1444 l_progress := '40';
1445
1446
1447 IF (l_debug = 1) THEN
1448 print_debug('Searching staging locators for this tasks whose ', 4);
1449 print_debug('transaction_temp_id : ' ||l_current_task_rec.transaction_temp_id, 4);
1450 -- print_debug('with operation_plan_id : '||l_current_task_rec.operation_plan_id, 4);
1451 print_debug('and delivery ID: ' || l_current_task_rec.delivery_id, 4);
1452 print_debug('or carton_grouping_ID: ' || l_current_task_rec.carton_grouping_ID, 4);
1453 END IF;
1454
1455
1456 IF l_current_task_rec.delivery_id IS NOT NULL THEN
1457
1458 IF (l_debug = 1) THEN
1459 print_debug('Look for locator with same delivery.', 4);
1460 END IF;
1461
1462
1463 OPEN l_loc_with_same_del_curs
1464 (l_current_task_rec.delivery_id);
1465
1466 LOOP
1467 FETCH l_loc_with_same_del_curs INTO l_loc_del_rec;
1468 EXIT WHEN l_loc_with_same_del_curs%notfound;
1469
1470 l_progress := '50';
1471
1472 IF p_call_mode IN (1,3) THEN --suggestion
1473 IF (l_debug = 1) THEN
1474 print_debug('Found one staging locator for the same delivery:', 4);
1475 print_debug('subinventory : ' || l_loc_del_rec.del_subinventory, 4);
1476 print_debug('locator ID : ' || l_loc_del_rec.del_locator_id, 4);
1477 END IF;
1478
1479
1480 x_zone_id := NULL;
1481 x_subinventory_code := l_loc_del_rec.del_subinventory;
1482 x_locator_id := l_loc_del_rec.del_locator_id;
1483
1484 IF l_current_task_rec.project_id IS NOT NULL THEN
1485 create_pjm_locator(x_locator_id => x_locator_id,
1486 p_project_id => l_current_task_rec.project_id,
1487 p_task_id => l_current_task_rec.task_id);
1488 END IF;
1489
1490 IF l_current_task_curs%isopen THEN
1491 CLOSE l_current_task_curs;
1492 END IF;
1493
1494 IF l_mol_curs%isopen THEN
1495 CLOSE l_mol_curs;
1496 END IF;
1497
1498 IF l_loc_with_same_del_curs%isopen THEN
1499 CLOSE l_loc_with_same_del_curs;
1500 END IF;
1501
1502 RETURN;
1503
1504 ELSIF p_call_mode = 2 THEN -- validation
1505 IF l_loc_del_rec.del_locator_id = p_locator_id THEN
1506 IF (l_debug = 1) THEN
1507 print_debug('This is a valid staging locator with the same delivery.', 4);
1508 END IF;
1509
1510
1511 IF l_current_task_curs%isopen THEN
1512 CLOSE l_current_task_curs;
1513 END IF;
1514
1515 IF l_mol_curs%isopen THEN
1516 CLOSE l_mol_curs;
1517 END IF;
1518
1519 IF l_loc_with_same_del_curs%isopen THEN
1520 CLOSE l_loc_with_same_del_curs;
1521 END IF;
1522
1523 RETURN;
1524 END IF;
1525
1526 END IF;
1527
1528
1529 END LOOP; -- end l_loc_with_same_del_curs cursor loop
1530
1531
1532 CLOSE l_loc_with_same_del_curs;
1533
1534 -- Bug 4759446: find staging lane for dock appointment if it exists
1535 IF p_call_mode IN (1,3) THEN --suggestion
1536 -- {
1537 IF (l_debug = 1) THEN
1538 print_debug('Look for staging lane associated with a dock appt.', 4);
1539 END IF;
1540
1541 OPEN l_del_trip_stop (l_current_task_rec.delivery_id);
1542 FETCH l_del_trip_stop INTO l_stop_id;
1543 CLOSE l_del_trip_stop;
1544
1545 IF (l_debug = 1) THEN
1546 print_debug('Trip stop ID: ' || l_stop_id, 4);
1547 END IF;
1548
1549 IF l_stop_id IS NOT NULL THEN
1550 -- {
1551 l_api_return_status := fnd_api.g_ret_sts_success;
1552 WMS_TRIPSTOPS_STAGELANES_PUB.get_stgln_for_tripstop
1553 ( p_org_id => l_current_task_rec.organization_id
1554 , p_trip_stop => l_stop_id
1555 , x_stg_ln_id => x_locator_id
1556 , x_sub_code => x_subinventory_code
1557 , x_return_status => l_api_return_status
1558 , x_msg_count => l_msg_count
1559 , x_msg_data => l_msg_data
1560 );
1561
1562 IF l_api_return_status = fnd_api.g_ret_sts_success THEN
1563 x_zone_id := NULL;
1564 IF (l_debug = 1) THEN
1565 print_debug('Found staging sub for dock appt: '
1566 || x_subinventory_code, 4);
1567 print_debug('and staging lane: ' || x_locator_id, 4);
1568 END IF;
1569
1570 IF l_current_task_rec.project_id IS NOT NULL THEN
1571 create_pjm_locator
1572 ( x_locator_id => x_locator_id
1573 , p_project_id => l_current_task_rec.project_id
1574 , p_task_id => l_current_task_rec.task_id
1575 );
1576 END IF;
1577 RETURN;
1578 END IF;
1579 -- }
1580 END IF; -- end if stop ID defined
1581 -- }
1582 END IF; -- end if call mode 1 or 3 and delivery ID exists
1583
1584 ELSIF l_current_task_rec.carton_grouping_id IS NOT NULL THEN
1585
1586 OPEN l_loc_with_same_carton_group
1587 (l_current_task_rec.carton_grouping_id);
1588
1589 LOOP
1590 FETCH l_loc_with_same_carton_group INTO l_loc_mol_rec;
1591 EXIT WHEN l_loc_with_same_carton_group%notfound;
1592
1593 l_progress := '60';
1594
1595 IF p_call_mode IN (1,3) THEN --suggestion
1596
1597 IF (l_debug = 1) THEN
1598 print_debug('Found one staging locator for the same carton_grouping_ID:', 4);
1599 print_debug('subinventory : ' || l_loc_mol_rec.mol_subinventory, 4);
1600 print_debug('locator ID : ' || l_loc_mol_rec.mol_locator_id, 4);
1601 END IF;
1602
1603
1604 x_zone_id := NULL;
1605 x_subinventory_code := l_loc_mol_rec.mol_subinventory;
1606 x_locator_id := l_loc_mol_rec.mol_locator_id;
1607
1608 IF l_current_task_rec.project_id IS NOT NULL THEN
1609 create_pjm_locator(x_locator_id => x_locator_id,
1610 p_project_id => l_current_task_rec.project_id,
1611 p_task_id => l_current_task_rec.task_id);
1612 END IF;
1613
1614 IF l_current_task_curs%isopen THEN
1615 CLOSE l_current_task_curs;
1616 END IF;
1617
1618 IF l_mol_curs%isopen THEN
1619 CLOSE l_mol_curs;
1620 END IF;
1621
1622 IF l_loc_with_same_carton_group%isopen THEN
1623 CLOSE l_loc_with_same_carton_group;
1624 END IF;
1625
1626 RETURN;
1627
1628 ELSIF p_call_mode = 2 THEN -- validation
1629 IF l_loc_mol_rec.mol_locator_id = p_locator_id THEN
1630 IF (l_debug = 1) THEN
1631 print_debug('This is a valid staging locator with the carton_grouping_ID.', 4);
1632 END IF;
1633
1634
1635 IF l_current_task_curs%isopen THEN
1636 CLOSE l_current_task_curs;
1637 END IF;
1638
1639 IF l_mol_curs%isopen THEN
1640 CLOSE l_mol_curs;
1641 END IF;
1642
1643 IF l_loc_with_same_carton_group%isopen THEN
1644 CLOSE l_loc_with_same_carton_group;
1645 END IF;
1646
1647 RETURN;
1648 END IF;
1649 END IF;
1650
1651 END LOOP; -- end l_loc_with_same_carton_group cursor loop
1652
1653 CLOSE l_loc_with_same_carton_group;
1654
1655 END IF;
1656
1657
1658 END LOOP; -- end l_current_task_curs cursor loop
1659
1660
1661 l_progress := '70';
1662
1663 IF l_current_task_curs%isopen THEN
1664 CLOSE l_current_task_curs;
1665 END IF;
1666
1667
1668 IF l_mol_curs%isopen THEN
1669 CLOSE l_mol_curs;
1670 END IF;
1671
1672
1673
1674 l_progress := '100';
1675
1676
1677 -- Return pick release locator
1678
1679 IF(p_task_id IS NOT NULL) THEN
1680
1681 IF (l_debug = 1) THEN
1682 print_debug('Get pick release sub/loc from task.', 4);
1683 END IF;
1684
1685 BEGIN
1686 IF (p_call_mode <> 3) THEN
1687 SELECT
1688 mmtt.transfer_subinventory,
1689 mmtt.transfer_to_location,
1690 nvl(mil.inventory_location_type, 3)
1691 INTO
1692 l_pick_release_subinventory,
1693 l_pick_release_locator_id,
1694 l_pick_release_locator_type
1695 FROM
1696 mtl_material_transactions_temp mmtt,
1697 mtl_item_locations mil
1698 WHERE mmtt.transaction_temp_id = p_task_id
1699 AND mil.inventory_location_id = mmtt.transfer_to_location
1700 AND mil.organization_id = mmtt.organization_id
1701 ;
1702 ELSE
1703 SELECT
1704 pmmtt.subinventory_code,
1705 pmmtt.locator_id,
1706 nvl(mil.inventory_location_type, 3)
1707 INTO
1708 l_pick_release_subinventory,
1709 l_pick_release_locator_id,
1710 l_pick_release_locator_type
1711 FROM
1712 mtl_material_transactions_temp mmtt,
1713 mtl_material_transactions_temp pmmtt,
1714 mtl_item_locations mil
1715 WHERE mmtt.transaction_temp_id = p_task_id
1716 AND mmtt.parent_line_id = pmmtt.transaction_temp_id
1717 AND mil.inventory_location_id = pmmtt.locator_id
1718 AND mil.organization_id = pmmtt.organization_id
1719 ;
1720 END IF;
1721 EXCEPTION
1722 WHEN no_data_found THEN
1723 IF (l_debug = 1) THEN
1724 print_debug('Unable to determine default pick release sub',4);
1725 END IF;
1726 l_pick_release_subinventory := NULL;
1727 l_pick_release_locator_id := NULL;
1728 l_pick_release_locator_type := NULL;
1729 END;
1730 END IF;
1731
1732 l_progress := '120';
1733
1734 IF (l_debug = 1) THEN
1735 print_debug('Got pick release sub : '|| l_pick_release_subinventory ||' and locator ID : '||l_pick_release_locator_id, 4);
1736 END IF;
1737
1738
1739 IF p_call_mode in (1,3) AND p_task_id IS NOT NULL THEN -- suggestion
1740
1741 IF l_pick_release_locator_type = g_loc_type_staging_lane THEN
1742
1743 l_progress := '130';
1744
1745 x_zone_id := NULL;
1746 x_subinventory_code := l_pick_release_subinventory;
1747 x_locator_id := l_pick_release_locator_id;
1748
1749 IF l_current_task_rec.project_id IS NOT NULL THEN
1750 create_pjm_locator(x_locator_id => x_locator_id,
1751 p_project_id => l_current_task_rec.project_id,
1752 p_task_id => l_current_task_rec.task_id);
1753 END IF;
1754
1755 IF (l_debug = 1) THEN
1756 print_debug('Return pick release sub : '|| x_subinventory_code ||' and locator ID : '||x_locator_id, 4);
1757 END IF;
1758
1759
1760
1761 RETURN;
1762
1763 ELSE
1764 l_progress := '130';
1765 x_zone_id := NULL;
1766 x_subinventory_code := l_pick_release_subinventory;
1767 x_locator_id := l_pick_release_locator_id;
1768
1769 IF l_current_task_rec.project_id IS NOT NULL THEN
1770 create_pjm_locator(x_locator_id => x_locator_id,
1771 p_project_id => l_current_task_rec.project_id,
1772 p_task_id => l_current_task_rec.task_id);
1773 END IF;
1774
1775
1776 IF p_task_type = g_wms_task_type_stg_move THEN
1777
1778 IF (l_debug = 1) THEN
1779 print_debug('Cannot perform staging move to non-staging locator. Check pick release.', 4);
1780 END IF;
1781
1782 x_return_status := g_ret_sts_error;
1783 x_message := fnd_message.get_string('WMS', 'WMS_STG_MV_INVALID_LOC_TYPE');
1784 fnd_message.set_name('WMS', 'WMS_STG_MV_INVALID_LOC_TYPE');
1785 FND_MSG_PUB.ADD;
1786
1787 END IF;
1788
1789 RETURN;
1790
1791 END IF;
1792
1793 ELSIF p_call_mode = 2 THEN
1794
1795 l_progress := '140';
1796
1797 SELECT Nvl(mil.inventory_location_type, 3),
1798 mil.subinventory_code,
1799 Nvl(mil.empty_flag, 'Y'),
1800 Nvl(mil.disable_date, trunc(sysdate+1)),
1801 Nvl(msi.disable_date, trunc(sysdate+1))
1802 INTO l_validate_loc_type,
1803 l_validate_loc_subinventory,
1804 l_validate_loc_empty_flag,
1805 l_loc_disable_date,
1806 l_sub_disable_date
1807 FROM mtl_item_locations mil,
1808 mtl_secondary_inventories msi
1809 WHERE mil.inventory_location_id = p_locator_id
1810 AND msi.secondary_inventory_name = mil.subinventory_code
1811 AND msi.organization_id = mil.organization_id
1812 ;
1813
1814 l_progress := '150';
1815
1816
1817
1818 IF l_validate_loc_type NOT IN
1819 (g_loc_type_staging_lane, g_loc_type_consolidation, g_loc_type_packing_station)
1820 OR l_loc_disable_date <= trunc(sysdate)
1821 OR l_sub_disable_date <= trunc(sysdate)
1822 THEN
1823 -- Not a valid locator
1824 IF (l_debug = 1) THEN
1825 print_debug('This locator is invalid since it is not a staing, packing, or consolidation locator.', 4);
1826 END IF;
1827
1828
1829 x_message := fnd_message.get_string('WMS', 'WMS_NOT_A_STG_LOC'); -- Not a staging locator
1830 fnd_message.set_name('WMS', 'WMS_NOT_A_STG_LOC');
1831 FND_MSG_PUB.ADD;
1832 x_return_status := g_ret_sts_error;
1833
1834 RETURN;
1835
1836 END IF;
1837
1838
1839 END IF;
1840
1841 l_progress := '170';
1842
1843 ELSE -- invalid p_task_type
1844 x_return_status := g_ret_sts_unexp_error;
1845 IF (l_debug = 1) THEN
1846 print_debug('Invalid input: P_Task_Type of '||p_task_type||' is not supported. ', 4);
1847 END IF;
1848
1849
1850 RETURN;
1851 END IF;
1852
1853
1854
1855
1856 EXCEPTION
1857 WHEN OTHERS THEN
1858 IF l_current_task_curs%isopen THEN
1859 CLOSE l_current_task_curs;
1860 END IF;
1861 IF l_mol_curs%isopen THEN
1862 CLOSE l_mol_curs;
1863 END IF;
1864 IF l_loc_with_same_del_curs%isopen THEN
1865 CLOSE l_loc_with_same_del_curs;
1866 END IF;
1867 IF l_loc_with_same_carton_group%isopen THEN
1868 CLOSE l_loc_with_same_carton_group;
1869 END IF;
1870
1871 IF (l_debug = 1) THEN
1872 print_debug('Other exception in Get_Staging_Loc_For_Delivery '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
1873 END IF;
1874
1875
1876 x_return_status := g_ret_sts_unexp_error;
1877
1878 IF SQLCODE IS NOT NULL THEN
1879 IF (l_debug = 1) THEN
1880 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
1881 END IF;
1882
1883 END IF;
1884
1885
1886
1887 END Get_Staging_Loc_For_Delivery;
1888
1889
1890 PROCEDURE Get_LPN_For_Delivery
1891 (
1892 X_Return_status OUT nocopy VARCHAR2,
1893 X_Message OUT nocopy VARCHAR2,
1894 X_LPN_ID OUT nocopy NUMBER,
1895 P_Task_Type IN NUMBER DEFAULT NULL,
1896 P_Task_ID IN NUMBER DEFAULT NULL,
1897 p_sug_sub IN VARCHAR2 DEFAULT NULL,
1898 p_sug_loc IN NUMBER DEFAULT NULL
1899 )IS
1900 l_to_sub_code VARCHAR2(30);
1901 l_to_loc_id NUMBER;
1902 l_organization_id NUMBER;
1903 l_license_plan_number VARCHAR2(30);
1904 l_delivery_id NUMBER;
1905 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1906 l_progress VARCHAR2(10);
1907 BEGIN
1908 IF (l_debug = 1) THEN
1909 print_debug('Entered wms_op_dest_sys_apis.Get_LPN_For_Delivery ', 1);
1910 print_debug('P_Task_Type: ' ||P_Task_Type , 1);
1911 print_debug('P_Task_ID: ' || P_Task_ID, 1);
1912 print_debug('P_sug_sub: ' || p_sug_sub, 1);
1913 print_debug('p_sug_loc: ' || p_sug_loc, 1);
1914 END IF;
1915
1916 l_progress := '10';
1917
1918 SELECT Nvl(p_sug_sub,Nvl(mmtt.transfer_subinventory, mmtt.subinventory_code)),
1919 Nvl(p_sug_loc,Nvl(mmtt.transfer_to_location, mmtt.locator_id)),
1920 mmtt.organization_id,
1921 wda.delivery_id
1922 INTO l_to_sub_code,
1923 l_to_loc_id,
1924 l_organization_id,
1925 l_delivery_id
1926 FROM mtl_material_transactions_temp mmtt,
1927 wsh_delivery_details wdd,
1928 wsh_delivery_assignments_v wda
1929 WHERE mmtt.transaction_temp_id = p_task_id
1930 AND wdd.move_order_line_id = mmtt.move_order_line_id
1931 AND wdd.released_status = 'S'
1932 AND wdd.delivery_detail_id = wda.delivery_detail_id;
1933
1934
1935 IF (l_debug = 1) THEN
1936 print_debug('Before calling wms_pick_drop_pvt.get_default_drop_lpn ' , 1);
1937 print_debug('p_to_sub: ' ||l_to_sub_code , 1);
1938 print_debug('p_to_loc: ' || l_to_loc_id, 1);
1939 print_debug('p_delivery_id: ' || l_delivery_id, 1);
1940 END IF;
1941
1942 l_progress := '20';
1943
1944 wms_pick_drop_pvt.get_default_drop_lpn
1945 ( x_drop_lpn_num => l_license_plan_number
1946 , x_return_status => x_return_status
1947 , p_organization_id => l_organization_id
1948 , p_delivery_id => l_delivery_id
1949 , p_to_sub => l_to_sub_code
1950 , p_to_loc => l_to_loc_id
1951 );
1952
1953 print_debug('l_license_plate_number:'||l_license_plan_number,1);
1954 l_progress := '30';
1955
1956 IF(l_license_plan_number IS NOT NULL) THEN
1957 SELECT lpn_id
1958 INTO x_lpn_id
1959 FROM wms_license_plate_numbers
1960 WHERE license_plate_number = l_license_plan_number;
1961 END IF;
1962
1963 l_progress := '40';
1964
1965 IF (l_debug = 1) THEN
1966 print_debug('Exit wms_op_dest_sys_apis.Get_LPN_For_Delivery ', 1);
1967 print_debug('x_lpn_id: ' || x_lpn_id, 1);
1968 END IF;
1969
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 IF (l_debug = 1) THEN
1973 print_debug('Other exception in Get_LPN_For_Delivery '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS')|| ' after where l_progress = ' || l_progress, 1);
1974 END IF;
1975
1976
1977 x_return_status := g_ret_sts_unexp_error;
1978
1979 IF SQLCODE IS NOT NULL THEN
1980 IF (l_debug = 1) THEN
1981 print_debug(' With SQL error: ' || SQLERRM(SQLCODE), 1);
1982 END IF;
1983
1984 END IF;
1985
1986
1987 END Get_LPN_For_Delivery;
1988
1989
1990
1991 END wms_op_dest_sys_apis;
1992
[Home] [Help]