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