1 PACKAGE BODY wms_mdc_pvt AS
2 /* $Header: WMSVMDCB.pls 120.21 2011/05/04 09:37:57 abasheer ship $ */
3
4 g_debug NUMBER := 1; -- NVL(fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
5
6 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2) IS
7 BEGIN
8 inv_log_util.TRACE(p_message, p_module);
9 -- dbms_output.put_line(p_module || ': ' || p_message);
10 END debug;
11
12 FUNCTION get_consol_delivery_id(p_lpn_id IN NUMBER) RETURN NUMBER IS
13 l_delivery_id NUMBER; -- Consol Delivery ID for the LPN
14 BEGIN
15 IF g_debug = 1 THEN
16 debug('In get_consol_delivery_id: P_LPN ID: ' || p_lpn_id,
17 'wms_mdc_pvt.get_consol_delivery_id');
18 END IF;
19
20 SELECT wda.delivery_id
21 INTO l_delivery_id
22 FROM wsh_delivery_assignments wda,
23 wsh_delivery_details_ob_grp_v wdd,
24 wsh_new_deliveries_ob_grp_v wnd
25 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
26 AND wnd.delivery_id = wda.delivery_id
27 AND wnd.delivery_type = 'CONSOLIDATION'
28 AND wdd.lpn_id = p_lpn_id
29 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
30 AND ROWNUM = 1;
31
32 IF g_debug = 1 THEN
33 debug('Consol Delivery ID: ' || l_delivery_id,
34 'wms_mdc_pvt.get_consol_delivery_id');
35 END IF;
36
37 RETURN l_delivery_id;
38
39 EXCEPTION
40 WHEN no_data_found THEN
41 IF g_debug = 1 THEN
42 debug('No Consol Delivery', 'wms_mdc_pvt.get_consol_delivery_id');
43 --{{No Consol Delivery found for the given delivery id}}
44 END IF;
45 RETURN NULL;
46 END get_consol_delivery_id;
47
48 FUNCTION get_delivery_type(p_delivery_id IN NUMBER) RETURN VARCHAR2 IS
49 l_delivery_type VARCHAR2(30); -- Consol Delivery ID for the LPN
50 BEGIN
51 IF g_debug = 1 THEN
52 debug('Inside get_delivery_type (2) : p_delivery_id : ' || p_delivery_id,
53 'wms_mdc_pvt.get_delivery_type');
54 END IF;
55
56 SELECT wnd.delivery_type
57 INTO l_delivery_type
58 FROM wsh_new_deliveries_ob_grp_v wnd
59 WHERE wnd.delivery_id = p_delivery_id ;
60
61 IF g_debug = 1 THEN
62 debug('Consol l_delivery_type : ' || l_delivery_type,
63 'wms_mdc_pvt.get_delivery_type');
64 END IF;
65
66 IF l_delivery_type = 'CONSOLIDATION' THEN
67 RETURN l_delivery_type;
68 END IF;
69 RETURN l_delivery_type;
70
71 EXCEPTION
72 WHEN no_data_found THEN
73 IF g_debug = 1 THEN
74 debug('No Delivery found in wsh_new_deliveries', 'wms_mdc_pvt.get_delivery_type');
75 --{{No Delivery found in wsh_new_deliveries whle tryiong to find delivery type }}
76 END IF;
77 RETURN NULL;
78 END get_delivery_type;
79
80 FUNCTION get_delivery_id(p_lpn_id IN NUMBER) RETURN NUMBER IS
81 l_lpn_context NUMBER; -- LPN context for LPN
82 l_delivery_id NUMBER; -- Delivery ID for the LPN
83 l_no_of_deliveries NUMBER;--added for bug 10139672
84 BEGIN
85 IF g_debug = 1 THEN
86 debug('inside get_delivery_id : p_lpn_id : ' || p_lpn_id,
87 'wms_mdc_pvt.get_delivery_id');
88 END IF;
89 SELECT lpn_context
90 INTO l_lpn_context
91 FROM wms_license_plate_numbers
92 WHERE lpn_id = p_lpn_id;
93
94 IF g_debug = 1 THEN
95 debug('LPN Context : ' || l_lpn_context, 'wms_mdc_pvt.get_delivery_id');
96 END IF;
97
98 IF l_lpn_context = 8 THEN -- Packing Context: LPN1 has been loaded
99 SELECT wda.delivery_id
100 INTO l_delivery_id
101 FROM mtl_material_transactions_temp mmtt,
102 wsh_delivery_details_ob_grp_v wdd,
103 wsh_delivery_assignments_v wda
104 WHERE mmtt.transfer_lpn_id = p_lpn_id
105 AND mmtt.transaction_action_id = 28
106 AND mmtt.transaction_source_type_id IN (2, 8)
107 AND mmtt.move_order_line_id = wdd.move_order_line_id
108 AND wdd.released_status = 'S'
109 AND wdd.delivery_detail_id = wda.delivery_detail_id
110 AND ROWNUM =1;
111
112 ---- *** MRANA : it can return multiple rows ..using rownum=1 expecting that all
113 --lines have the same delivery id..no cms
114 -- {{ - LPNs that are pick loaded(context 8) with record in MMTT
115 -- may belong to a cancelled MOL/SOL and }}
116 -- {{ thus the join to WDD with status 'Released to Warehouse'
117 -- might return no_data_found }}
118 ELSIF l_lpn_context = 5 THEN -- Prepacked LPN, does not have a delivery
119 --START--Added for bug 10139672
120 --In case of bulk pick with multiple deliveries, the MMTT record will be stamped with "To LPN" whose context
121 --will be 5 and it will be linked with a delivery
122 select count(wda.delivery_id) into l_no_of_deliveries
123 FROM mtl_material_transactions_temp mmtt,
124 wsh_delivery_details_ob_grp_v wdd,
125 wsh_delivery_assignments_v wda
126 WHERE mmtt.transfer_lpn_id = p_lpn_id
127 AND mmtt.transaction_action_id = 28
128 AND mmtt.transaction_source_type_id IN (2, 8)
129 AND mmtt.move_order_line_id = wdd.move_order_line_id
130 AND wdd.released_status = 'S'
131 AND wdd.delivery_detail_id = wda.delivery_detail_id;
132
133 IF g_debug = 1 THEN
134 debug('No of delvieries for this LPN with context 5: ' || l_no_of_deliveries, 'wms_mdc_pvt.get_delivery_id');
135 END IF;
136
137 IF l_no_of_deliveries > 0 THEN
138
139 SELECT wda.delivery_id
140 INTO l_delivery_id
141 FROM mtl_material_transactions_temp mmtt,
142 wsh_delivery_details_ob_grp_v wdd,
143 wsh_delivery_assignments_v wda
144 WHERE mmtt.transfer_lpn_id = p_lpn_id
145 AND mmtt.transaction_action_id = 28
146 AND mmtt.transaction_source_type_id IN (2, 8)
147 AND mmtt.move_order_line_id = wdd.move_order_line_id
148 AND wdd.released_status = 'S'
149 AND wdd.delivery_detail_id = wda.delivery_detail_id
150 AND ROWNUM =1;
151
152 ELSE
153 RETURN 0;
154 END IF;
155 --End-Added for bug 10139672-Before this fix, only 'RETURN 0' was present
156 ELSIF l_lpn_context IN (12, 11) THEN -- LPN1 has been staged
157 -- 12:Loaded to stage move Context
158 SELECT wda.delivery_id
159 INTO l_delivery_id
160 FROM wsh_delivery_assignments_v wda,
161 wsh_delivery_details_ob_grp_v wdd
162 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
163 AND wdd.lpn_id = p_lpn_id
164 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
165 AND ROWNUM =1;
166 ---- *** MRANA : it can return multiple rows ..using rownum=1 expecting that all
167 --lines have the same delivery id..no cms
168 ELSIF l_lpn_context = 3 THEN
169 --There should only be 1 MOL in this LPN
170 BEGIN
171 SELECT wda.delivery_id
172 INTO l_delivery_id
173 FROM mtl_txn_request_lines mtrl,
174 wsh_delivery_assignments_v wda
175 WHERE mtrl.lpn_id IN (SELECT wlpn.lpn_id
176 FROM wms_license_plate_numbers wlpn
177 START WITH wlpn.lpn_id = p_lpn_id
178 CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id)
179 AND wda.delivery_detail_id = mtrl.backorder_delivery_detail_id;
180 EXCEPTION
181 WHEN too_many_rows THEN
182 IF g_debug = 1 THEN
183 debug('More than 1 MOL. Should not come here!', 'wms_mdc_pvt.get_delivery_id');
184 END IF;
185 RETURN NULL;
186 WHEN OTHERS THEN
187 IF g_debug = 1 THEN
188 debug('no MOL found!', 'wms_mdc_pvt.get_delivery_id');
189 END IF;
190 RETURN NULL;
191 END;
192 END IF;
193
194 IF g_debug = 1 THEN
195 debug('Delivery ID: ' || l_delivery_id, 'wms_mdc_pvt.get_delivery_id');
196 END IF;
197
198 RETURN l_delivery_id;
199
200 EXCEPTION
201 WHEN no_data_found THEN
202 IF g_debug = 1 THEN
203 debug('SQL Error: ' || sqlerrm, 'wms_mdc_pvt.get_delivery_id');
204 END IF;
205 RETURN NULL;
206 END get_delivery_id;
207
208 -- API to check if the LPN is tied to an operation plan that specifies
209 -- consolidation across deliveries
210 FUNCTION is_across_delivery(p_lpn_id IN NUMBER,
211 p_lpn_context IN NUMBER := NULL) RETURN BOOLEAN IS
212 l_lpn_context NUMBER; -- LPN context for LPN
213 l_consolidation_method_id NUMBER; -- Consolidation method
214 BEGIN
215 IF g_debug = 1 THEN
216 debug('Entered ..is_across_delivery : ' , 'wms_mdc_pvt.is_across_delivery');
217 debug('p_lpn_id : ' || p_lpn_id, 'wms_mdc_pvt.is_across_delivery');
218 debug('p_lpn_context : ' || p_lpn_context, 'wms_mdc_pvt.is_across_delivery');
219 END IF;
220
221 IF p_lpn_context = 8 THEN -- Packing Context: LPN1 has been loaded
222 BEGIN
223 SELECT 2 -- 1=AD, 2=WD
224 INTO l_consolidation_method_id -- 1=AD, 2=WD
225 FROM wms_op_plan_details wopd, mtl_material_transactions_temp mmtt
226 WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
227 AND mmtt.transaction_action_id = 28
228 AND mmtt.transaction_source_type_id IN (2, 8)
229 AND mmtt.operation_plan_id = wopd.operation_plan_id
230 AND wopd.consolidation_method_id = 2 --WD
231 AND wopd.operation_type = 2 -- Drop
232 AND ROWNUM = 1;
233 IF g_debug = 1 THEN
234 debug('Consolidation Method: context 8' || l_consolidation_method_id,
235 'wms_mdc_pvt.is_across_delivery');
236 END IF;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 l_consolidation_method_id := 1;
240 END ;
241 ELSIF p_lpn_context = 5 THEN -- Prepacked LPN, can be treated as across delivery
242 l_consolidation_method_id := 1;
243 RETURN TRUE;
244 ELSIF p_lpn_context IN (12, 11) THEN -- LPN1 has been staged
245 -- 12:Loaded to stage move Context
246 BEGIN
247 SELECT 2 -- 1=AD, 2=WD
248 INTO l_consolidation_method_id -- 1=AD, 2=WD
249 FROM wms_op_plan_details wopd, wms_dispatched_tasks_history wdth
250 WHERE wdth.transfer_lpn_id = p_lpn_id
251 AND wdth.operation_plan_id = wopd.operation_plan_id
252 AND wopd.consolidation_method_id = 2 --WD
253 AND wopd.operation_type = 2 -- Drop
254 AND ROWNUM = 1;
255 IF g_debug = 1 THEN
256 debug('Consolidation Method: context11: ' || l_consolidation_method_id,
257 'wms_mdc_pvt.is_across_delivery');
258 END IF;
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 l_consolidation_method_id := 1;
262 END ;
263 ELSIF p_lpn_context = 3 THEN
264 BEGIN
265 SELECT 2
266 INTO l_consolidation_method_id
267 FROM wms_op_plan_details wopd,
268 mtl_material_transactions_temp mmtt,
269 mtl_txn_request_lines mtrl
270 WHERE mtrl.lpn_id IN (SELECT wlpn.lpn_id
271 FROM wms_license_plate_numbers wlpn
272 START WITH wlpn.lpn_id = p_lpn_id
273 CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id)
274 AND mtrl.line_status = 7
275 AND mtrl.line_id = mmtt.move_order_line_id
276 AND mmtt.operation_plan_id = wopd.operation_plan_id
277 AND wopd.consolidation_method_id = 2 --WD
278 AND wopd.operation_type = 2;
279 EXCEPTION
280 WHEN too_many_rows THEN
281 IF (g_debug = 1) THEN
282 debug('Too many rows!', 'wms_mdc_pvt.is_across_delivery');
283 END IF;
284 l_consolidation_method_id := 2;
285 WHEN NO_DATA_FOUND THEN
286 IF (g_debug = 1) THEN
287 debug('No data found!', 'wms_mdc_pvt.is_across_delivery');
288 END IF;
289 l_consolidation_method_id := 1;
290 WHEN OTHERS THEN
291 IF (g_debug = 1) THEN
292 debug('Other exception!', 'wms_mdc_pvt.is_across_delivery');
293 END IF;
294 l_consolidation_method_id := 2;
295
296 END ;
297 END IF;
298
299 IF g_debug = 1 THEN
300 debug('Consolidation Method: ' || l_consolidation_method_id,
301 'wms_mdc_pvt.is_across_delivery');
302 END IF;
303
304 IF Nvl(l_consolidation_method_id, 0) = 1 THEN
305 RETURN TRUE;
306 ELSE
307 RETURN FALSE;
308 END IF;
309
310 EXCEPTION
311 WHEN Others THEN
312 IF g_debug = 1 THEN
313 debug('Unexpected error: ' || sqlerrm, 'wms_mdc_pvt.is_across_delivery');
314 END IF;
315 RETURN FALSE;
316 END is_across_delivery;
317
318
319 -- API to check if an LPN is a consolidated LPN
320 FUNCTION is_mdc_lpn(p_lpn_id IN NUMBER) RETURN BOOLEAN IS
321
322 CURSOR lpn_cursor IS
323 SELECT wlpn.lpn_id, lpn_context
324 FROM wms_license_plate_numbers wlpn
325 WHERE wlpn.outermost_lpn_id = p_lpn_id;
326
327 --l_lpn_context NUMBER;
328 l_loose_exists VARCHAR2(1);
329 BEGIN
330 IF g_debug = 1 THEN
331 debug('Entered ..is_mdc_lpn : ' , 'wms_mdc_pvt.is_mdc_lpn');
332 debug('p_lpn_id : ' || p_lpn_id, 'wms_mdc_pvt.is_mdc_lpn');
333 END IF;
334 FOR rec_lpn IN lpn_cursor LOOP
335 -- Call IS_ACROSS_DELIVERY foreach of the LPNs that have material. If this LPN is tied
336 -- to an operation plan that specifies packing Within Deliveries,
337 -- then return false and exit the loop.
338
339 IF g_debug = 1 THEN
340 debug('rec_lpn.lpn_id:rec_lpn.lpn_context ' || rec_lpn.lpn_id || ':' || rec_lpn.lpn_context
341 , 'wms_mdc_pvt.is_mdc_lpn');
342 END IF;
343 IF NOT is_across_delivery(rec_lpn.lpn_id, rec_lpn.lpn_context) THEN
344 IF g_debug = 1 THEN
345 debug('Exiting ..is_mdc_lpn : FALSE ' , 'wms_mdc_pvt.is_mdc_lpn');
346 END IF;
347 RETURN FALSE;
348 END IF;
349 END LOOP;
350
351 IF g_debug = 1 THEN
352 debug('Exiting ..is_mdc_lpn : TRUE ' , 'wms_mdc_pvt.is_mdc_lpn');
353 END IF;
354
355 RETURN TRUE;
356
357 END is_mdc_lpn;
358
359 -- API to check if an LPN1 (delivery D1) can be packed into another LPN2
360 -- p_local_caller IN VARCHAR2 DEFAULT 'N', It will be passed as 'Y' when called from the overloaded
361 -- validate_to_lpn (used for mass_move functionality ). No one else should ever use it
362 PROCEDURE validate_to_lpn
363 (p_from_lpn_id IN NUMBER, -- LPN1
364 p_from_delivery_id IN NUMBER DEFAULT NULL, -- delivery ID for material in LPN1
365 p_to_lpn_id IN NUMBER, -- LPN2
366 p_is_from_to_delivery_same IN VARCHAR2, -- Y,N,U
367 p_is_from_lpn_mdc IN VARCHAR2 DEFAULT 'U',
368 p_is_to_lpn_mdc IN VARCHAR2 DEFAULT 'U',
369 p_to_sub IN VARCHAR2 DEFAULT NULL,
370 p_to_locator_id IN NUMBER DEFAULT NULL,
371 p_local_caller IN VARCHAR2 DEFAULT 'N',
372 x_allow_packing OUT nocopy VARCHAR2, -- Y,N,C,L
373 x_return_status OUT nocopy VARCHAR2,
374 x_msg_count OUT nocopy NUMBER,
375 x_msg_data OUT nocopy VARCHAR2) IS
376
377 l_from_lpn_mdc BOOLEAN; -- Is the LPN1 an MDC LPN?
378 l_to_lpn_mdc BOOLEAN; -- Is the LPN2 an MDC LPN?
379 l_loose_exists VARCHAR2(1); -- Does there exist any loose material in an LPN
380 l_in_staging VARCHAR2(1); -- Does the LPN reside in a non staging locator
381 l_is_from_to_delivery_same VARCHAR2(1); -- Do LPN1 and LPN2 have material for the same delivery?
382 l_from_delivery_id NUMBER; -- Delivery ID for material in LPN1
383 l_to_delivery_id NUMBER; -- Delivery ID for material in LPN2
384 l_deconsolidation_location NUMBER;
385 l_deliveries wsh_util_core.id_tab_type;
386 l_to_lpn_context NUMBER; -- LPN context
387 l_allow_packing VARCHAR2(1); -- Allow packing LPN1 into LPN2?
388 l_from_delivery_type VARCHAR2(30);
389 l_to_delivery_type VARCHAR2(30);
390 l_to_lpn_organization_id NUMBER;
391 l_outermost_lpn_id NUMBER; -- To check if the TO LPn is the outermost or not
392 BEGIN
393 x_return_status := 'S';
394
395 IF g_debug = 1 THEN
396 debug('Entered.. wms_mdc_pvt.validate_to_lpn(single): ' , 'wms_mdc_pvt.validate_to_lpn');
397 debug('p_from_lpn_id: ' || p_from_lpn_id, 'wms_mdc_pvt.validate_to_lpn');
398 debug('p_from_delivery_id: ' || p_from_delivery_id, 'wms_mdc_pvt.validate_to_lpn');
399 debug('p_to_lpn_id: ' || p_to_lpn_id, 'wms_mdc_pvt.validate_to_lpn');
400 debug('p_is_from_to_delivery_same : ' || p_is_from_to_delivery_same, 'wms_mdc_pvt.validate_to_lpn');
401 debug('p_is_from_lpn_mdc : ' || p_is_from_lpn_mdc, 'wms_mdc_pvt.validate_to_lpn');
402 debug('p_is_to_lpn_mdc : ' || p_is_to_lpn_mdc , 'wms_mdc_pvt.validate_to_lpn');
403 debug('p_to_sub : ' || p_to_sub, 'wms_mdc_pvt.validate_to_lpn');
404 debug('p_to_locator_id : ' || p_to_locator_id, 'wms_mdc_pvt.validate_to_lpn');
405 debug('p_local_caller : ' || p_local_caller , 'wms_mdc_pvt.validate_to_lpn');
406 END IF;
407
408 l_is_from_to_delivery_same := p_is_from_to_delivery_same;
409 IF p_is_from_to_delivery_same IS NULL THEN
410 l_is_from_to_delivery_same := 'U';
411 END IF;
412
413 --{{ From LPN Should have NOT NULL Delivery }}
414 --{{ From LPN Should have only 1 Delivery }}
415 --{{ From LPN's Delivery should not be a consolidated Delivery }}
416 IF l_is_from_to_delivery_same = 'N' AND p_local_caller = 'Y' THEN
417 null; -- No need to derive delivery_ids
418 ELSE
419 l_from_delivery_id := p_from_delivery_id;
420 IF l_from_delivery_id IS NULL OR l_from_delivery_id = 0 THEN
421 l_from_delivery_id := get_delivery_id(p_from_lpn_id);
422 END IF;
423
424 IF g_debug = 1 THEN
425 debug('l_from_delivery_id: ' || l_from_delivery_id, 'wms_mdc_pvt.validate_to_lpn');
426 END IF;
427
428 IF l_from_delivery_id is NULL AND NOT (p_to_lpn_id = 0 OR p_to_lpn_id IS NULL) THEN
429 x_allow_packing := 'N' ; -- U gets used in WMSPKDPB.pls
430 IF g_debug = 1 THEN
431 debug('WMS_FROM_LPN_NO_DELIVERY : from lpn has no delivery : ' ,
432 'wms_mdc_pvt.validate_to_lpn');
433 END IF;
434 fnd_message.set_name('WMS', 'WMS_FROM_LPN_NO_DELIVERY');
435 fnd_msg_pub.ADD;
436 -- Check the to_lpn before raising it
437 -- RAISE fnd_api.g_exc_error;
438 END IF;
439
440 IF l_from_delivery_id IS NOT NULL THEN
441 l_from_delivery_type := get_delivery_type (p_delivery_id => l_from_delivery_id);
442 END IF;
443
444 IF g_debug = 1 THEN
445 debug('l_from_delivery_type: ' || l_from_delivery_type, 'wms_mdc_pvt.validate_to_lpn');
446 END IF;
447
448 IF l_from_delivery_type = 'CONSOLIDATION' THEN
449 x_allow_packing := 'C' ;
450 -- further checks in the caller: pick_drop and staging_move and mass_move
451 IF g_debug = 1 THEN
452 debug('WMS_FROM_LPN_CONSOL : from lpn is a consol LPN: ' ,
453 'wms_mdc_pvt.validate_to_lpn');
454 END IF;
455 IF P_TO_LPN_ID = 0 OR p_to_lpn_id IS NULL THEN
456 IF g_debug = 1 THEN
457 debug('WMS_FROM_LPN_CONSOL : from lpn is a consol LPN: ' ,
458 'wms_mdc_pvt.validate_to_lpns');
459 END IF;
460 fnd_message.set_name('WMS', 'WMS_FROM_LPN_CONSOL');
461 fnd_msg_pub.ADD;
462 --RAISE fnd_api.g_exc_error; No need to raise it ..check other conditions first
463 ELSE
464 IF g_debug = 1 THEN
465 debug('WMS_CONSOL_LPN_NESTING_NOTALLOWED : From LPNs is ' ||
466 'a Consol LPN, No further nesting is allowed ' ,
467 'wms_mdc_pvt.validate_to_lpns');
468 --{{- From LPNs is a Consol LPN, No further nesting is allowed }}
469 END IF;
470 fnd_message.set_name('WMS', 'WMS_CONSOL_NESTING_NOTALLOWED');
471 fnd_msg_pub.ADD;
472 x_allow_packing := 'N' ;
473 RAISE fnd_api.g_exc_error;
474 END IF;
475 END IF;
476 END IF;
477
478
479 IF (p_to_lpn_id = 0 OR p_to_lpn_id IS NULL) THEN
480 null;
481 ELSE
482 SELECT lpn_context , organization_id, outermost_lpn_id
483 INTO l_to_lpn_context , l_to_lpn_organization_id, l_outermost_lpn_id
484 FROM wms_license_plate_numbers
485 WHERE lpn_id = p_to_lpn_id;
486 IF g_debug = 1 THEN
487 debug('l_to_lpn_context for p_to_lpn_id : ' || l_to_lpn_context
488 || ': '
489 || p_to_lpn_id
490 || ':in org : '
491 || l_to_lpn_organization_id ,
492 'wms_mdc_pvt.validate_to_lpn');
493 debug('l_outermost_lpn_id: ' || l_outermost_lpn_id ,
494 'wms_mdc_pvt.validate_to_lpn');
495 END IF;
496 l_in_staging := 'N';
497 l_loose_exists := 'N';
498 l_allow_packing := 'N';
499
500 IF l_to_lpn_context = 5 AND p_local_caller = 'N' THEN
501 -- means only 1 from LPN
502 l_is_from_to_delivery_same := 'Y';
503 l_allow_packing := 'Y';
504 ELSE
505 IF g_debug = 1 THEN
506 debug('Check is the to_locator is a staging locator ', 'wms_mdc_pvt.validate_to_lpn');
507 END IF;
508 BEGIN
509 SELECT 'Y'
510 INTO l_in_staging
511 FROM mtl_item_locations mil
512 WHERE mil.organization_id = l_to_lpn_organization_id
513 AND mil.subinventory_code = p_to_sub
514 AND mil.inventory_location_id = p_to_locator_id
515 AND mil.inventory_location_type = 2;
516 EXCEPTION
517 WHEN no_data_found THEN
518 l_in_staging := 'N';
519 END;
520 IF g_debug = 1 THEN
521 debug('l_in_staging : ' || l_in_staging , 'wms_mdc_pvt.validate_to_lpn');
522 END IF;
523
524 IF l_from_delivery_type = 'CONSOLIDATION' THEN
525 IF l_in_staging = 'N' THEN
526 x_allow_packing := 'N' ;
527 IF g_debug = 1 THEN
528 debug('WMS_MDC_IN_STAGING_ONLY : The new TO LPN is not in staging locator. Consolidation ' ||
529 ' Across Delivery is allowed in staging locator only: ' , 'wms_mdc_pvt.validate_to_lpn');
530 --{{- TO LPN must be in staging locator. Consolidation Across Delivery is allowed }}
531 --{{ in staging locator only }}
532 END IF;
533 fnd_message.set_name('WMS', 'WMS_MDC_IN_STAGING_ONLY');
534 fnd_msg_pub.ADD;
535 RAISE fnd_api.g_exc_error;
536 ELSE -- not a consol delivery of from LPN
537 x_allow_packing := 'Y' ;
538 Return;
539 END IF;
540 --{{- Newly generated TOLPN (context - defined but not used) should allow packing if }}
541 --{{ from LPN is a consol LPN and TLPN is in Staging locator}}
542 --{{- Newly generated TOLPN (context - defined but not used) should allow packing in }}
543 --{{ any locatory type, if fromLPN is NOT a consolLPN }}
544 END IF ;
545 IF g_debug = 1 THEN
546 debug('l_allow_packing: ' || l_allow_packing , 'wms_mdc_pvt.validate_to_lpn'); END IF;
547 END IF ;
548
549 /* Bug: 5478071
550 * Added the foll. for better readability. Once the 3 conditions are met for
551 * to_lpn, we need can just return with allow_packing with V (further validation
552 * required) */
553 IF l_to_lpn_context = 5 AND p_local_caller = 'Y' AND l_in_staging = 'Y'
554 THEN
555 l_allow_packing := 'V';
556 return;
557 END IF;
558
559 IF l_to_lpn_context = 5 THEN
560 NULL;
561 ELSE
562 IF l_is_from_to_delivery_same = 'U' OR l_is_from_to_delivery_same IS NULL THEN
563 -- we have already derived from delivery_id ;
564 IF l_to_delivery_id IS NULL THEN
565 l_to_delivery_id := get_delivery_id(p_to_lpn_id);
566 END IF;
567
568 IF g_debug = 1 THEN
569 debug('l_to_delivery_id: ' || l_to_delivery_id, 'wms_mdc_pvt.validate_to_lpn');
570 END IF;
571 -- the following will not be executed for context 5 toLPNs
572
573 --{{ To LPN Should have NOT NULL Delivery ..}}
574 --The following is to find is one of them is null, then what shld be
575 --the order of erro messages
576 IF NOT (l_to_delivery_id is NOT NULL AND l_from_delivery_id IS NOT NULL) THEN
577 IF (l_to_delivery_id is NULL ) THEN
578 x_allow_packing := 'N' ; -- U gets used in WMSPKDPB.pls
579 IF g_debug = 1 THEN
580 debug('WMS_TO_LPN_NO_DELIVERY : TO lpn has no delivery : ' ,
581 'wms_mdc_pvt.validate_to_lpn');
582 END IF;
583 fnd_message.set_name('WMS', 'WMS_TO_LPN_NO_DELIVERY');
584 fnd_msg_pub.ADD;
585 RAISE fnd_api.g_exc_error;
586 ELSE -- TODEL is not null and FROMDEL is null
587 RAISE fnd_api.g_exc_error;
588 END IF;
589 END IF;
590
591 IF l_to_delivery_id = l_from_delivery_id THEN
592 l_is_from_to_delivery_same := 'Y';
593 ELSE
594 l_is_from_to_delivery_same := 'N';
595 END IF;
596 END IF;
597 END IF;
598 IF g_debug = 1 THEN
599 debug('l_is_from_to_delivery_same : '|| l_is_from_to_delivery_same
600 , 'wms_mdc_pvt.validate_to_lpn');
601 END IF;
602 IF l_is_from_to_delivery_same = 'Y' THEN
603 x_allow_packing := 'Y';
604 return;
605 END IF ;
606 IF l_to_lpn_context = 5 THEN
607 null;
608 ELSE
609 IF g_debug = 1 THEN
610 debug('l_is_from_to_delivery_same is NO ' , 'wms_mdc_pvt.validate_to_lpn');
611 END IF;
612 IF l_outermost_lpn_id <> p_to_lpn_id THEN
613 x_allow_packing := 'N' ;
614 IF g_debug = 1 THEN
615 debug('WMS_CANNOT_CONSOL_INNERLPN : TOLPN is an inner LPN. Cannot consolidate : '
616 , 'wms_mdc_pvt.validate_to_lpn');
617 --{{Cannot comingle AD/WD material in TO LPN }}
618 END IF;
619 fnd_message.set_name('WMS', 'WMS_CANNOT_CONSOL_INNERLPN');
620 fnd_msg_pub.ADD;
621 RAISE fnd_api.g_exc_error;
622 END IF;
623 BEGIN
624 SELECT 'Y'
625 INTO l_in_staging
626 FROM mtl_item_locations mil, wms_license_plate_numbers wlpn
627 WHERE wlpn.lpn_id = p_to_lpn_id
628 AND wlpn.locator_id = mil.inventory_location_id
629 AND mil.inventory_location_type = 2;
630 EXCEPTION
631 WHEN no_data_found THEN
632 l_in_staging := 'N';
633 END;
634 IF g_debug = 1 THEN
635 debug('l_in_staging : ' || l_in_staging , 'wms_mdc_pvt.validate_to_lpn');
636 END IF;
637 END IF;
638
639 IF l_to_lpn_context = 5 AND p_local_caller = 'N' THEN
640 null;
641 ELSE
642 IF l_in_staging = 'Y' THEN
643 BEGIN
644 SELECT 'Y'
645 INTO l_loose_exists
646 FROM wms_lpn_contents wlc
647 WHERE wlc.parent_lpn_id = p_to_lpn_id
648 AND ROWNUM =1;
649 EXCEPTION
650 WHEN no_data_found THEN
651 l_loose_exists := 'N';
652 END;
653
654 IF g_debug = 1 THEN
655 debug('Loose Exists: ' || l_loose_exists, 'wms_mdc_pvt.validate_to_lpn');
656 END IF;
657 IF l_loose_exists = 'N' THEN
658 IF p_local_caller = 'N' THEN
659 IF p_is_from_lpn_mdc = 'Y' THEN
660 l_from_lpn_mdc := TRUE;
661 ELSIF p_is_from_lpn_mdc = 'N' THEN
662 l_from_lpn_mdc := FALSE;
663 ELSE
664 l_from_lpn_mdc := is_mdc_lpn(p_from_lpn_id);
665 END IF;
666 IF g_debug = 1 THEN
667 IF l_from_lpn_mdc THEN
668 debug('l_from_lpn_mdc : TRUE ' , 'wms_mdc_pvt.validate_to_lpn');
669 ELSE
670 debug('l_from_lpn_mdc : FALSE ' , 'wms_mdc_pvt.validate_to_lpn');
671 END IF;
672 END IF;
673
674 IF p_is_to_lpn_mdc = 'Y' THEN
675 l_to_lpn_mdc := TRUE;
676 ELSIF p_is_to_lpn_mdc = 'N' THEN
677 l_to_lpn_mdc := FALSE;
678 ELSE
679 l_to_lpn_mdc := is_mdc_lpn(p_to_lpn_id);
680 END IF;
681
682 IF g_debug = 1 THEN
683 IF l_to_lpn_mdc THEN
684 debug('l_to_lpn_mdc : TRUE ' , 'wms_mdc_pvt.validate_to_lpn');
685 ELSE
686 debug('l_to_lpn_mdc : FALSE ' , 'wms_mdc_pvt.validate_to_lpn');
687 END IF;
688 END IF;
689
690 IF l_from_lpn_mdc AND l_to_lpn_mdc THEN
691 l_deliveries(1) := l_from_delivery_id;
692
693 IF get_delivery_type (p_delivery_id => l_to_delivery_id) = 'CONSOLIDATION' THEN
694 Null;
695 ELSE
696 l_deliveries(2) := l_to_delivery_id;
697 IF g_debug = 1 THEN
698 debug('p_input_delivery_id_tab : ' || l_deliveries(2),
699 'wms_mdc_pvt.validate_to_lpn');
700 END IF;
701 END IF;
702
703 IF g_debug = 1 THEN
704 debug('wsh_fte_comp_constraint_grp.is_valid_consol called: ' ,
705 'wms_mdc_pvt.validate_to_lpn');
706 debug('p_input_delivery_id_tab : ' || l_deliveries(1) ,
707 'wms_mdc_pvt.validate_to_lpn');
708 debug('p_caller = WMS: ', 'wms_mdc_pvt.validate_to_lpn');
709 END IF;
710 --Call shipping API to validate
711 WSH_WMS_LPN_GRP.is_valid_consol
712 (p_init_msg_list => NULL,
713 p_input_delivery_id_tab => l_deliveries,
714 p_target_consol_delivery_id => get_consol_delivery_id(p_lpn_id => p_to_lpn_id),
715 p_caller => 'WMS',
716 x_deconsolidation_location => l_deconsolidation_location,
717 x_return_status => x_return_status,
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data);
720
721 IF g_debug = 1 THEN
722 debug ('x_return_status : ' || x_return_status , 'wms_mdc_pvt.validate_to_lpn');
723 debug ('x_msg_data : ' || x_msg_data, 'wms_mdc_pvt.validate_to_lpn');
724 debug ('x_msg_count : ' || x_msg_count, 'wms_mdc_pvt.validate_to_lpn');
725 debug ('x_deconsolidation_location : ' || l_deconsolidation_location,
726 'wms_mdc_pvt.validate_to_lpn');
727 END IF;
728 IF x_return_status <> 'S' THEN
729 IF g_debug = 1 THEN
730 debug ('Error from wsh_fte_comp_constraint_grp.is_valid_consol: ' || x_return_status,
731 'wms_mdc_pvt.validate_to_lpn');
732 debug ('x_msg_data : ' || x_msg_data, 'wms_mdc_pvt.validate_to_lpn');
733 END IF;
734
735 IF x_return_status = 'E' THEN
736 RAISE fnd_api.g_exc_error;
737 ELSE
738 RAISE fnd_api.g_exc_unexpected_error;
739 END IF;
740 ELSE
741 IF l_deconsolidation_location IS NOT NULL THEN
742 l_allow_packing := 'Y';
743 ELSE
744 l_allow_packing := 'N';
745 END IF;
746 END IF;
747 ELSE -- NOT l_from_lpn_mdc and/OR NOT l_to_lpn_mdc
748 x_allow_packing := 'N' ;
749 IF g_debug = 1 THEN
750 debug('WMS_CANNOT_COMMINGLE_ADWD : cannto comingle AD/WD material: ' ,
751 'wms_mdc_pvt.validate_to_lpn');
752 --{{Cannot comingle AD/WD material in TO LPN }}
753 END IF;
754 fnd_message.set_name('WMS', 'WMS_CANNOT_COMMINGLE_ADWD');
755 fnd_msg_pub.ADD;
756 RAISE fnd_api.g_exc_error;
757 END IF;
758 ELSE
759 l_allow_packing := 'V';
760 END IF ; --p_local_caller = 'N' THEN
761 ELSE -- l_loose_exists = 'Y' THEN
762 x_allow_packing := 'N' ;
763 IF g_debug = 1 THEN
764 debug('WMS_LOOSE_TO_LPN : Loose material exist in TO LPN' , 'wms_mdc_pvt.validate_to_lpn');
765 --{{Cannot Pack into TO LPN that has loose material }}
766 END IF;
767 fnd_message.set_name('WMS', 'WMS_LOOSE_TO_LPN');
768 fnd_msg_pub.ADD;
769 RAISE fnd_api.g_exc_error;
770 END IF ;
771 ELSE -- l_in_staging = 'N' THEN
772 x_allow_packing := 'N' ;
773 IF g_debug = 1 THEN
774 debug('WMS_MDC_IN_STAGING_ONLY : TO LPN is not in staging locator. Consolidation ' ||
775 ' Across Delivery is allowed in staging locator only: ' , 'wms_mdc_pvt.validate_to_lpn');
776 --{{TO LPN must be in staging locator. Consolidation Across Delivery is allowed
777 --in staging locator only }}
778 END IF;
779 fnd_message.set_name('WMS', 'WMS_MDC_IN_STAGING_ONLY');
780 fnd_msg_pub.ADD;
781 RAISE fnd_api.g_exc_error;
782 END IF;
783 END IF; -- l_to_lpn_context = 5 AND p_local_caller = 'N' THEN
784
785 END IF; -- to_lpn_id is not null
786
787 IF g_debug = 1 THEN
788 debug ('l_allow_packing : ' || l_allow_packing, 'wms_mdc_pvt.validate_to_lpn');
789 END IF;
790 x_allow_packing := l_allow_packing;
791 x_return_status := 'S';
792
793 EXCEPTION
794 WHEN fnd_api.g_exc_error THEN
795 x_return_status := 'E';
796 IF g_debug = 1 THEN
797 debug('Error', 'wms_mdc_pvt.validate_to_lpn');
798 END IF;
799
800 WHEN fnd_api.g_exc_unexpected_error THEN
801 x_return_status := 'U';
802 IF g_debug = 1 THEN
803 debug('Unexpected Error', 'wms_mdc_pvt.validate_to_lpn');
804 END IF;
805 WHEN OTHERS THEN
806 x_return_status := 'U';
807 x_msg_data := SQLERRM;
808 IF g_debug = 1 THEN
809 debug('SQL error: ' || SQLERRM, 'wms_mdc_pvt.validate_to_lpn');
810 END IF;
811 END validate_to_lpn;
812
813
814 -- API to suggest drop LPN, Subinventory and Locator
815 PROCEDURE suggest_to_lpn(p_lpn_id IN NUMBER, -- The LPN that is being dropped (from LPN)
816 p_delivery_id IN NUMBER, -- The delivery associated with the LPN
817 x_to_lpn_id OUT nocopy NUMBER, -- The LPN that is being dropped
818 x_to_subinventory_code OUT nocopy VARCHAR2, -- The subinventory of the suggested LPN
819 x_to_locator_id OUT nocopy NUMBER, -- The locator of the suggested LPN
820 x_return_status OUT nocopy VARCHAR2,
821 x_msg_count OUT nocopy NUMBER,
822 x_msg_data OUT nocopy VARCHAR2) IS
823
824 l_from_delivery_id NUMBER; -- The delivery for which material is packed in from LPN
825 l_allow_packing VARCHAR2(1); -- Allow packing from LPN into the suggested LPN
826 BEGIN
827 x_return_status := 'S';
828
829 IF g_debug = 1 THEN
830 debug('p_lpn_id: ' || p_lpn_id, 'wms_mdc_pvt.suggest_to_lpn');
831 debug('p_delivery_id: ' || p_delivery_id, 'wms_mdc_pvt.suggest_to_lpn');
832 END IF;
833
834 -- Algorithm
835 -- If the Consolidation Method in the operation plan is set to
836 -- Across Deliveries in staging lane, then the following algorithm
837 -- should be used to suggest the Drop LPN
838 -- * If another LPN belonging to the same delivery is staged,
839 -- divert LPN to the locator where the first LPN was staged.
840 -- If there are multiple LPNs for that delivery that have been staged,
841 -- then use the last LPN (time-stamp). Divert to the outer LPN of the
842 -- above line (if display LPN is opted in the operation plan). This
843 -- simply means that nesting/pallet building is desired. Allow user to
844 -- override and drop LPN as is or into a different Outer LPN.
845 -- * Use parent delivery or trip stop to make suggestions is the above
846 -- query does not return any suggestion
847 -- * If this is the first LPN to be dropped, divert to the staging lane
848 -- suggested by pick/cross-dock release (suggest nothing)
849
850 --{{ From LPN is a Consol LPN..no further checking}}
851 --{{ From LPN with delivery D1 is MDC }}
852 --{{ From LPN with delivery D1 is not MDC}}
853
854 IF get_consol_delivery_id(p_lpn_id => p_lpn_id) IS NOT NULL THEN
855 IF g_debug = 1 THEN
856 debug('From LPN is a consol delivery LPN : ' , 'wms_mdc_pvt.validate_to_lpns');
857 END IF;
858 ELSE
859
860
861 IF is_mdc_lpn(p_lpn_id) THEN -- If the LPN is an MDC LPN
862
863 l_from_delivery_id := p_delivery_id;
864 IF Nvl(l_from_delivery_id, 0) = 0 THEN
865 --Find the Delivery for the From LPN
866 l_from_delivery_id := get_delivery_id(p_lpn_id);
867 END IF;
868
869 IF g_debug = 1 THEN
870 debug('From Delivery ID: ' || l_from_delivery_id, 'wms_mdc_pvt.suggest_to_lpn');
871 END IF;
872
873 IF Nvl(l_from_delivery_id, 0) <> 0 THEN
874 --{{There is another staged LPN2 with material for D1, LPN2 has loose material }}
875 --{{There is another staged LPN2 with material for D1, LPN2 has no loose material }}
876 --{{There is another staged LPN2 with material for D2, LPN2 has no loose material }}
877 -- Find if another LPN with material for the same delivery is staged in a staging locator
878 BEGIN
879 SELECT outermost_lpn_id, subinventory_code, locator_id
880 INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
881 FROM
882 (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
883 FROM wsh_delivery_assignments wda
884 , wsh_delivery_details_ob_grp_v wdd
885 , mtl_item_locations mil
886 , wms_license_plate_numbers wlpn
887 , wms_dispatched_tasks_history wdth
888 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
889 AND wdd.lpn_id = wlpn.lpn_id
890 AND wlpn.lpn_id <> p_lpn_id
891 AND wlpn.outermost_lpn_id <> p_lpn_id
892 AND wlpn.LPN_CONTEXT = 11
893 AND wlpn.locator_id = mil.inventory_location_id
894 AND wlpn.organization_id = mil.organization_id
895 AND mil.inventory_location_type = 2 -- Staging
896 AND wdth.transfer_lpn_id = wdd.lpn_id
897 AND NOT exists (SELECT 1
898 FROM wms_lpn_contents wlc
899 WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
900 -- above is to check that the outermost lpn being suggested
901 -- does nto have Looase material
902 AND wda.delivery_id = l_from_delivery_id
903 ORDER BY wdth.creation_date DESC)
904 WHERE ROWNUM = 1;
905 IF g_debug = 1 THEN
906 debug('Cursor 1: same Delivery: ' , 'wms_mdc_pvt.suggest_to_lpn');
907 END IF;
908 EXCEPTION
909 WHEN no_data_found THEN
910 IF g_debug = 1 THEN
911 debug('No staged material with the same delivery found in staging locators',
912 'wms_mdc_pvt.suggest_to_lpn');
913 END IF;
914 END;
915
916 --{{There is another staged LPN2 with material for same parent delivery, no staged LPN
917 -- has material for D1 }}
918 -- Find if another LPN with material for the same parent delivery is staged in a staging locator
919 IF Nvl(x_to_lpn_id, 0) = 0 THEN
920 BEGIN
921 SELECT outermost_lpn_id, subinventory_code, locator_id
922 INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
923 FROM
924 (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
925 FROM wsh_delivery_assignments wda
926 , wsh_delivery_details_ob_grp_v wdd
927 , mtl_item_locations mil
928 , wms_license_plate_numbers wlpn
929 , wms_dispatched_tasks_history wdth
930 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
931 AND wdd.lpn_id = wlpn.lpn_id
932 AND wlpn.lpn_id <> p_lpn_id
933 AND wlpn.outermost_lpn_id <> p_lpn_id
934 AND wlpn.LPN_CONTEXT = 11
935 AND wlpn.locator_id = mil.inventory_location_id
936 AND wlpn.organization_id = mil.organization_id
937 AND mil.inventory_location_type = 2
938 AND wdth.transfer_lpn_id = wdd.lpn_id
939 AND NOT exists (SELECT 1
940 FROM wms_lpn_contents wlc
941 WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
942 AND wda.delivery_id IN (SELECT l2.delivery_id
943 FROM wsh_delivery_legs l1, --_ob_grp_v l1,
944 wsh_delivery_legs l2 --_ob_grp_v l2
945 WHERE l1.delivery_id = l_from_delivery_id
946 AND l1.parent_delivery_leg_id = l2.parent_delivery_leg_id)
947 --- above sub query: that this del is a prt of a consol
948 ORDER BY wdth.creation_date DESC)
949 WHERE ROWNUM = 1;
950 IF g_debug = 1 THEN
951 debug('Cursor 2: same Delivery in a consol: ' , 'wms_mdc_pvt.suggest_to_lpn');
952 END IF;
953 EXCEPTION
954 WHEN no_data_found THEN
955 IF g_debug = 1 THEN
956 debug('No staged material with the same parent delivery found in staging locators',
957 'wms_mdc_pvt.suggest_to_lpn');
958 END IF;
959 END;
960 END IF;
961
962 --{{There is another staged LPN2 with material for same trip, no staged LPN has material for
963 -- D1, no staged LPN has material FOR the same parent delivery}}
964 -- Find if another LPN with material for deliveries of a delivery that
965 -- share the same trip from initial pickup and dropoff
966 IF Nvl(x_to_lpn_id, 0) = 0 THEN
967 BEGIN
968 SELECT outermost_lpn_id, subinventory_code, locator_id
969 INTO x_to_lpn_id, x_to_subinventory_code, x_to_locator_id
970 FROM
971 (SELECT wlpn.outermost_lpn_id, wlpn.subinventory_code, wlpn.locator_id
972 FROM wsh_delivery_assignments wda
973 , wsh_delivery_details_ob_grp_v wdd
974 , mtl_item_locations mil
975 , wms_license_plate_numbers wlpn
976 , wms_dispatched_tasks_history wdth
977 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
978 AND wdd.lpn_id = wlpn.lpn_id
979 AND wlpn.lpn_id <> p_lpn_id
980 AND wlpn.outermost_lpn_id <> p_lpn_id
981 AND wlpn.LPN_CONTEXT = 11
982 AND wlpn.locator_id = mil.inventory_location_id
983 AND wlpn.organization_id = mil.organization_id
984 AND mil.inventory_location_type = 2
985 AND wdth.transfer_lpn_id = wdd.lpn_id
986 AND NOT exists (SELECT 1
987 FROM wms_lpn_contents wlc
988 WHERE wlc.parent_lpn_id = wlpn.outermost_lpn_id)
989 AND wda.delivery_id IN (SELECT l2.delivery_id
990 FROM wsh_delivery_legs_ob_grp_v l1
991 , wsh_delivery_legs_ob_grp_v l2
992 , wsh_trip_stops_ob_grp_v s
993 , wsh_new_deliveries_ob_grp_v d
994 WHERE d.delivery_id = l_from_delivery_id
995 AND d.initial_pickup_location_id = s.stop_location_id
996 AND d.delivery_id = l1.delivery_id
997 AND s.stop_id = l1.pick_up_stop_id
998 AND l1.pick_up_stop_id = l2.pick_up_stop_id)
999 --01/02/07:5475113 AND l1.drop_off_stop_id = l2.drop_off_stop_id)
1000 -- above subquery: that this delvery is a part of Trip
1001 ORDER BY wdth.creation_date DESC)
1002 WHERE ROWNUM = 1;
1003 IF g_debug = 1 THEN
1004 debug('Cursor 3: same Delivery in a consol: ' , 'wms_mdc_pvt.suggest_to_lpn');
1005 END IF;
1006 EXCEPTION
1007 WHEN no_data_found THEN
1008 IF g_debug = 1 THEN
1009 debug('No staged material with the same trip found in staging locators',
1010 'wms_mdc_pvt.suggest_to_lpn');
1011 END IF;
1012 END;
1013 END IF;
1014
1015 IF g_debug = 1 THEN
1016 debug('To LPN ID: ' || x_to_lpn_id, 'wms_mdc_pvt.suggest_to_lpn');
1017 debug('To Subinventory: ' || x_to_subinventory_code, 'wms_mdc_pvt.suggest_to_lpn');
1018 debug('To Locator ID: ' || x_to_locator_id, 'wms_mdc_pvt.suggest_to_lpn');
1019 END IF;
1020
1021 -- Validate that the From LPN can be packed into the To LPN.
1022 IF Nvl(x_to_lpn_id, 0) <> 0 THEN
1023 validate_to_lpn(p_from_lpn_id => p_lpn_id,
1024 p_from_delivery_id => l_from_delivery_id,
1025 p_to_lpn_id => x_to_lpn_id,
1026 p_is_from_to_delivery_same => 'U',
1027 p_to_sub => NULL,
1028 p_to_locator_id => NULL,
1029 x_allow_packing => l_allow_packing,
1030 x_return_status => x_return_status,
1031 x_msg_count => x_msg_count,
1032 x_msg_data => x_msg_data);
1033
1034 IF x_return_status <> 'S' THEN
1035 IF g_debug = 1 THEN
1036 debug ('Error from validate_to_lpn: ' || x_return_status, 'wms_mdc_pvt.suggest_to_lpn');
1037 debug ('x_msg_data : ' || x_msg_data, 'wms_mdc_pvt.suggest_to_lpn');
1038 END IF;
1039
1040 /*-- MRANA: 3/26/07: it is OK, not to find any valid LPN to
1041 -- suggest, therefore there is no need to raise an error.
1042 IF x_return_status = 'E' THEN
1043 RAISE fnd_api.g_exc_error;
1044 ELSE
1045 RAISE fnd_api.g_exc_unexpected_error;
1046 END IF; */
1047 x_return_status := 'S';
1048 l_allow_packing := 'N';
1049 x_to_lpn_id := NULL;
1050 x_to_subinventory_code := NULL;
1051 x_to_locator_id := NULL;
1052 ELSE
1053 IF g_debug = 1 THEN
1054 debug ('Allow Packing: ' || l_allow_packing, 'wms_mdc_pvt.suggest_to_lpn');
1055 END IF;
1056
1057 IF l_allow_packing <> 'Y' THEN
1058 x_to_lpn_id := NULL;
1059 x_to_subinventory_code := NULL;
1060 x_to_locator_id := NULL;
1061 END IF;
1062
1063 END IF;
1064 END IF;
1065 END IF;
1066 END IF;
1067 END IF; -- P_lpn_id is a consol LPN
1068 EXCEPTION
1069 WHEN fnd_api.g_exc_error THEN
1070 x_return_status := 'E';
1071 IF g_debug = 1 THEN
1072 debug('Error', 'wms_mdc_pvt.suggest_to_lpn');
1073 END IF;
1074
1075 WHEN fnd_api.g_exc_unexpected_error THEN
1076 x_return_status := 'U';
1077 IF g_debug = 1 THEN
1078 debug('Unexpected Error', 'wms_mdc_pvt.suggest_to_lpn');
1079 END IF;
1080
1081 WHEN OTHERS THEN
1082 x_return_status := 'U';
1083 x_msg_data := SQLERRM;
1084 IF g_debug = 1 THEN
1085 debug('SQL error: ' || SQLERRM, 'wms_mdc_pvt.suggest_to_lpn');
1086 END IF;
1087 END suggest_to_lpn;
1088
1089 -- check if a delivery D1 can be shipped out
1090 PROCEDURE can_ship_delivery(p_delivery_id NUMBER,
1091 x_allow_shipping OUT nocopy VARCHAR2,
1092 x_return_status OUT nocopy VARCHAR2,
1093 x_msg_count OUT nocopy NUMBER,
1094 x_msg_data OUT nocopy VARCHAR2) IS
1095
1096 l_delivery_id NUMBER;
1097 l_part_of_consol_delivery VARCHAR2(1);
1098 BEGIN
1099 IF g_debug = 1 THEN
1100 debug('Entered can_ship_delivery with p_delivery_id : ' || p_delivery_id ,
1101 'wms_mdc_pvt.can_ship_delivery');
1102 END IF;
1103 x_allow_shipping := 'Y';
1104 x_return_status := 'S';
1105 l_part_of_consol_delivery := NULL;
1106 BEGIN
1107 SELECT 'Y'
1108 INTO l_part_of_consol_delivery
1109 FROM wsh_delivery_legs --_ob_grp_v
1110 WHERE delivery_id = p_delivery_id
1111 AND PARENT_DELIVERY_LEG_ID IS NOT NULL
1112 AND ROWNUM = 1;
1113 EXCEPTION
1114 WHEN NO_DATA_FOUND THEN
1115 l_part_of_consol_delivery := 'N';
1116 END ;
1117
1118 IF g_debug = 1 THEN
1119 debug('l_part_of_consol_delivery : ' || l_part_of_consol_delivery ,
1120 'wms_mdc_pvt.can_ship_delivery');
1121 END IF;
1122 IF l_part_of_consol_delivery = 'Y'
1123 THEN
1124 x_allow_shipping := 'N' ;
1125 x_return_status := 'S';
1126 IF g_debug = 1 THEN
1127 debug('l_part_of_consol_delivery : ' || l_part_of_consol_delivery ,
1128 'wms_mdc_pvt.can_ship_delivery');
1129 debug('WMS_PART_OF_CONSOL : This delivery is a prt of Consol Delivery..' ||
1130 'cannot ship from here' , 'wms_mdc_pvt.can_ship_delivery');
1131 END IF;
1132 fnd_message.set_name('WMS', 'WMS_DEL_PART_OF_CONSOL');
1133 fnd_msg_pub.ADD;
1134 ELSE
1135 x_allow_shipping := 'Y';
1136 END IF;
1137 IF g_debug = 1 THEN
1138 debug('Exit can_ship_delivery with x_allow_shipping : ' || x_allow_shipping ,
1139 'wms_mdc_pvt.can_ship_delivery');
1140 END IF;
1141 END can_ship_delivery;
1142
1143 -- Procedure to check if multiple LPNs LPN1, LPN2, ... can be packed into LPN0
1144 PROCEDURE validate_to_lpn(p_from_lpn_ids IN number_table_type, -- LPN1, LPN2,...
1145 p_from_delivery_ids IN number_table_type, -- Delivery1, Delivery2,...
1146 p_to_lpn_id IN NUMBER, -- LPN0
1147 p_to_sub IN VARCHAR2 DEFAULT NULL,
1148 p_to_locator_id IN NUMBER DEFAULT NULL,
1149 x_allow_packing OUT nocopy VARCHAR2,
1150 -- Y/N/C(consol delivery )
1151 x_return_status OUT nocopy VARCHAR2,
1152 x_msg_count OUT nocopy NUMBER,
1153 x_msg_data OUT nocopy VARCHAR2) IS
1154
1155 l_previous_delivery_id NUMBER := 0;
1156 l_current_delivery_id NUMBER := 0;
1157 l_to_delivery_id NUMBER := 0;
1158 l_deliveries wsh_util_core.id_tab_type;
1159 l_deliveries_same BOOLEAN := TRUE;
1160 l_lpns_ad BOOLEAN := TRUE;
1161 l_allow_packing VARCHAR2(1);
1162 l_loose_exists VARCHAR2(1); -- Does there exist any loose material in an LPN
1163 l_in_staging VARCHAR2(1); -- Does the LPN reside in a non staging locator
1164 l_deconsolidation_location NUMBER;
1165 l_lpn_context NUMBER; -- LPN context
1166 l_is_consol_LPN VARCHAR2(1);
1167 l_local_caller VARCHAR2(1) := 'Y';
1168 BEGIN
1169
1170 IF g_debug = 1 THEN
1171 debug('Entered validate_to_lpn with LPN Count: ' || p_from_lpn_ids.COUNT, 'wms_mdc_pvt.validate_to_lpn');
1172 debug('p_from_delivery_ids count: ' || p_from_delivery_ids.COUNT, 'wms_mdc_pvt.validate_to_lpn');
1173 debug('p_to_lpn_id: ' || p_to_lpn_id, 'wms_mdc_pvt.validate_to_lpn');
1174 END IF;
1175
1176 -- Loop through all the LPNs to check if all the from LPNs have material for the same delivery
1177 -- All the from LPN will be in context 11 , The From Locator validations/LOV checks that
1178 FOR i in 1..p_from_lpn_ids.COUNT LOOP
1179
1180 l_current_delivery_id := NULL;
1181 l_is_consol_LPN := 'N';
1182 l_allow_packing := 'Y';
1183
1184 -- Get the delivery of the current LPN
1185 IF p_from_delivery_ids.COUNT > i THEN
1186 l_current_delivery_id := p_from_delivery_ids(i);
1187 debug('p_from_delivery_ids(i): ' || p_from_delivery_ids(i), 'wms_mdc_pvt.validate_to_lpn');
1188 END IF;
1189
1190 IF get_consol_delivery_id(p_lpn_id => p_from_lpn_ids(i)) IS NOT NULL THEN
1191 IF g_debug = 1 THEN
1192 debug('One of the From LPN is linked to a consol delivery : ' , 'wms_mdc_pvt.validate_to_lpns');
1193 END IF;
1194 l_is_consol_LPN := 'Y' ;
1195 l_deliveries_same := FALSE;
1196 IF P_TO_LPN_ID = 0 OR p_to_lpn_id IS NULL THEN
1197 IF g_debug = 1 THEN
1198 debug('WMS_ONE_FROM_LPN_CONSOL : One of the from lpns is a consol LPN: ' ,
1199 'wms_mdc_pvt.validate_to_lpns');
1200 END IF;
1201 l_allow_packing := 'C'; -- this value is used in lpn_mass_move -- mrcovered
1202 EXIT; -- No need to check the delivery ids of the remaining
1203 ELSE
1204 -- {{- if from_lpn.count = 1 then this condition shld not fail }} -- mrcovered along with the above
1205 IF g_debug = 1 THEN
1206 debug('WMS_CONSOL_LPN_NESTING_NOTALLOWED : One of the From LPNs is ' ||
1207 'a Consol LPN, No further nesting is allowed ' , 'wms_mdc_pvt.validate_to_lpns');
1208 --{{- One of the From LPNs is a Consol LPN, No further nesting is allowed }}
1209 END IF;
1210 fnd_message.set_name('WMS', 'WMS_CONSOL_NESTING_NOTALLOWED');
1211 fnd_msg_pub.ADD;
1212 x_allow_packing := 'N' ;
1213 RAISE fnd_api.g_exc_error;
1214 --EXIT; -- No need to check the delivery ids of the remaining
1215 END IF;
1216 END IF;
1217
1218 IF l_current_delivery_id IS NULL OR l_current_delivery_id = 0 THEN
1219 l_current_delivery_id := get_delivery_id(p_from_lpn_ids(i));
1220 IF l_current_delivery_id is NULL AND NOT (p_to_lpn_id = 0 OR p_to_lpn_id IS NULL) THEN
1221 l_allow_packing := 'N' ; -- U gets used in WMSPKDPB.pls
1222 IF g_debug = 1 THEN
1223 debug('WMS_ONE_FROM_LPN_NO_DEL : from lpn has no delivery : ' , 'wms_mdc_pvt.validate_to_lpn');
1224 END IF;
1225 fnd_message.set_name('WMS', 'WMS_ONE_FROM_LPN_NO_DEL');
1226 fnd_msg_pub.ADD;
1227 -- Check the to_lpn before raising it
1228 --RAISE fnd_api.g_exc_error;
1229 END IF;
1230 END IF;
1231
1232
1233
1234 IF g_debug = 1 THEN
1235 debug('Delivery ID: ' || l_current_delivery_id, 'wms_mdc_pvt.validate_to_lpn');
1236 END IF;
1237
1238 -- If the delivery of the current LPN is different from the delivery of the previous LPN,
1239 -- exit out of the loop
1240 IF (i > 1 AND l_current_delivery_id <> l_previous_delivery_id) OR l_current_delivery_id IS NULL THEN
1241 l_deliveries_same := FALSE;
1242 EXIT;
1243 END IF;
1244
1245 l_previous_delivery_id := l_current_delivery_id;
1246
1247 END LOOP;
1248 IF g_debug = 1 THEN
1249 IF l_deliveries_same THEN
1250 debug(' l_deliveries_same is TRUE', 'wms_mdc_pvt.validate_to_lpn');
1251 ELSE
1252 debug(' l_deliveries_same is FALSE', 'wms_mdc_pvt.validate_to_lpn');
1253 END IF;
1254 END IF;
1255
1256 IF (p_to_lpn_id = 0 OR p_to_lpn_id IS NULL) THEN
1257 null;
1258 ELSE
1259
1260 --IF l_allow_packing = 'Y' AND l_deliveries_same THEN
1261 l_to_delivery_id := get_delivery_id(p_to_lpn_id);
1262
1263 IF g_debug = 1 THEN
1264 debug('l_to_delivery_id : ' || l_to_delivery_id , 'wms_mdc_pvt.validate_to_lpn');
1265 debug('l_current_delivery_id : ' || l_current_delivery_id , 'wms_mdc_pvt.validate_to_lpn');
1266 END IF;
1267 IF NOT (l_to_delivery_id is NOT NULL AND l_current_delivery_id IS NOT NULL)
1268 THEN
1269 IF (l_to_delivery_id is NULL ) THEN
1270 x_allow_packing := 'N' ; -- U gets used in WMSPKDPB.pls
1271 IF g_debug = 1 THEN
1272 debug('WMS_TO_LPN_NO_DELIVERY : TO lpn has no delivery : ' , 'wms_mdc_pvt.validate_to_lpn');
1273 END IF;
1274 fnd_message.set_name('WMS', 'WMS_TO_LPN_NO_DELIVERY');
1275 fnd_msg_pub.ADD;
1276 RAISE fnd_api.g_exc_error;
1277 ELSE -- TODEL is not null and FROMDEL is null
1278 x_allow_packing := 'N' ; -- U gets used in WMSPKDPB.pls
1279 RAISE fnd_api.g_exc_error;
1280 END IF;
1281 END IF;
1282 IF (l_to_delivery_id <> 0 ) THEN
1283 debug('l_to_delivery_id <> 0', 'wms_mdc_pvt.validate_to_lpn');
1284 IF NOT (l_current_delivery_id = l_to_delivery_id) THEN
1285 l_deliveries_same := FALSE;
1286 IF g_debug = 1 THEN
1287 debug('From LPNS Delivery is not same as TOLPNs delivery', 'wms_mdc_pvt.validate_to_lpn');
1288 END IF;
1289 END IF;
1290 ELSE
1291 IF l_deliveries_same THEN
1292 -- l_deliveries_same is for from deliveries_same ) THEN
1293 null;
1294 IF g_debug = 1 THEN
1295 debug('l_deliveries_same l_to_delivery_id=0 ', 'wms_mdc_pvt.validate_to_lpn');
1296 END IF;
1297 END IF;
1298 END IF;
1299
1300 IF l_allow_packing = 'Y' AND l_deliveries_same THEN
1301 IF g_debug = 1 THEN
1302 debug('All FROM and TO LPNS have material for the same delivery', 'wms_mdc_pvt.validate_to_lpn');
1303 END IF;
1304
1305 -- All material in the from and to lpns is for the same delivery, allow packing
1306 l_allow_packing := 'Y';
1307 END IF;
1308 IF l_allow_packing = 'Y' AND NOT l_deliveries_same THEN
1309
1310 IF g_debug = 1 THEN
1311 debug('All LPNS do not have material for the same delivery', 'wms_mdc_pvt.validate_to_lpn');
1312 END IF;
1313 validate_to_lpn(p_from_lpn_id => p_to_lpn_id,
1314 -- we are not using it in the calling API
1315 p_from_delivery_id => NULL,
1316 p_to_lpn_id => p_to_lpn_id,
1317 p_is_from_to_delivery_same => 'N',
1318 p_to_sub => p_to_sub,
1319 p_to_locator_id => p_to_locator_id,
1320 p_local_caller => l_local_caller , -- Y
1321 x_allow_packing => l_allow_packing,
1322 x_return_status => x_return_status,
1323 x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data);
1325
1326 -- {{ There is loose material in LPN2}}
1327 -- {{ LPN2 is in a non staging locator}}
1328 -- {{ There is no loose material in LPN2}}
1329 -- {{ LPN2 is in a staging locator}}
1330 -- Find if there is any loose material in LPN2 or if LPN2 is in a non staging locator
1331 IF g_debug = 1 THEN
1332 debug('x_return_status: : ' || x_return_status, 'wms_mdc_pvt.validate_to_lpn');
1333 debug('x_msg_count: : ' || x_msg_count, 'wms_mdc_pvt.validate_to_lpn');
1334 debug('x_msg_data: : ' || x_msg_data, 'wms_mdc_pvt.validate_to_lpn');
1335 debug('x_allow_packing: : ' || l_allow_packing, 'wms_mdc_pvt.validate_to_lpn');
1336 END IF;
1337
1338 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1339 RAISE fnd_api.g_exc_error;
1340 END IF;
1341 IF l_allow_packing = 'N' THEN
1342 RAISE fnd_api.g_exc_error;
1343 END IF;
1344
1345 IF l_allow_packing = 'V' THEN -- further validation needed for non-consol from LPNs
1346 -- Loop through all the LPNs to make sure that all the from LPNs are AD
1347 FOR i in 1..p_from_lpn_ids.COUNT LOOP
1348 IF NOT is_mdc_lpn(p_from_lpn_ids(i)) THEN
1349 l_lpns_ad := FALSE;
1350 EXIT;
1351 END IF;
1352 END LOOP;
1353
1354 IF g_debug = 1 THEN
1355 IF l_lpns_ad THEN
1356 debug('From Lpn MDC: TRUE ', 'wms_mdc_pvt.validate_to_lpn');
1357 ELSE
1358 debug('From Lpn MDC: FALSE', 'wms_mdc_pvt.validate_to_lpn');
1359 END IF;
1360 END IF;
1361
1362 IF l_to_delivery_id IS NULL OR l_to_delivery_id = 0 THEN
1363 NULL;
1364 -- l_lpns_ad will stay as is..false or true..need not reassign
1365 ELSE
1366 IF l_lpns_ad AND NOT is_mdc_lpn(p_to_lpn_id) THEN
1367 l_lpns_ad := FALSE;
1368 END IF;
1369
1370 IF g_debug = 1 THEN
1371 IF l_lpns_ad THEN
1372 debug('To Lpn MDC: TRUE ', 'wms_mdc_pvt.validate_to_lpn');
1373 ELSE
1374 debug('To Lpn MDC: FALSE', 'wms_mdc_pvt.validate_to_lpn');
1375 END IF;
1376 END IF;
1377 END IF;
1378
1379 IF l_lpns_ad THEN
1380
1381 /*mrana: 08/22/06 Bug: 5478071
1382 * l_to_delivery_id will be 0 only if lpn_context is 5 (defined but not
1383 * used) and is returned by get_delivery_id function above
1384 * If LPN_context <> 5 and there is no delivery, then l_to_delivery_id
1385 * will be NULL and it gets checked in validate_to_lpn API*/
1386 IF l_to_delivery_id IS NULL THEN
1387 l_allow_packing := 'N';
1388 x_allow_packing := 'N' ;
1389 ELSE
1390 IF g_debug = 1 THEN
1391 debug('All LPNS are across delivery', 'wms_mdc_pvt.validate_to_lpn');
1392 END IF;
1393
1394 FOR i IN 1..p_from_lpn_ids.COUNT LOOP
1395 l_deliveries(i) := get_delivery_id(p_from_lpn_ids(i));
1396 END LOOP;
1397
1398 --Call shipping API to validate
1399 WSH_WMS_LPN_GRP.is_valid_consol
1400 (p_init_msg_list => NULL,
1401 p_input_delivery_id_tab => l_deliveries,
1402 p_target_consol_delivery_id => get_consol_delivery_id(p_lpn_id => p_to_lpn_id),
1403 p_caller => 'WMS',
1404
1405 x_deconsolidation_location => l_deconsolidation_location,
1406 x_return_status => x_return_status,
1407 x_msg_count => x_msg_count,
1408 x_msg_data => x_msg_data);
1409
1410 IF x_return_status <> 'S' THEN
1411 IF g_debug = 1 THEN
1412 debug ('Error from wsh_fte_comp_constraint_grp.is_valid_consol: '
1413 || x_return_status, 'wms_mdc_pvt.validate_to_lpn');
1414 END IF;
1415
1416 IF x_return_status = 'E' THEN
1417 RAISE fnd_api.g_exc_error;
1418 ELSE
1419 RAISE fnd_api.g_exc_unexpected_error;
1420 END IF;
1421 ELSE
1422 IF l_deconsolidation_location IS NOT NULL THEN
1423 l_allow_packing := 'Y';
1424 ELSE
1425 l_allow_packing := 'N';
1426 END IF;
1427 END IF;
1428 END IF;
1429
1430 ELSE -- Both from and to lpns are not MDC
1431 IF g_debug = 1 THEN
1432 debug('All LPNS are not across delivery', 'wms_mdc_pvt.validate_to_lpn');
1433 END IF;
1434
1435 l_allow_packing := 'N';
1436 x_allow_packing := 'N' ;
1437 IF g_debug = 1 THEN
1438 debug('WMS_CANNOT_COMMINGLE_ADWD : cannto comingle AD/WD material: ' ,
1439 'wms_mdc_pvt.validate_to_lpn');
1440 --{{Cannot comingle AD/WD material in TO LPN }}
1441 END IF;
1442 fnd_message.set_name('WMS', 'WMS_CANNOT_COMMINGLE_ADWD');
1443 fnd_msg_pub.ADD;
1444 RAISE fnd_api.g_exc_error;
1445 END IF; -- Are all from LPNS AD?
1446 END IF; -- allow packing = 'V' , futher validations
1447 END IF; -- Allow packing is Y and deliveries and not same
1448 END IF; -- to_lpn_id is null or 0
1449
1450 x_allow_packing := l_allow_packing;
1451 x_return_status := 'S';
1452 EXCEPTION
1453 WHEN fnd_api.g_exc_error THEN
1454 x_return_status := 'E';
1455 IF g_debug = 1 THEN
1456 debug('Error', 'wms_mdc_pvt.validate_to_lpn');
1457 END IF;
1458
1459 WHEN fnd_api.g_exc_unexpected_error THEN
1460 x_return_status := 'U';
1461 IF g_debug = 1 THEN
1462 debug('Unexpected Error', 'wms_mdc_pvt.validate_to_lpn');
1463 END IF;
1464 WHEN OTHERS THEN
1465 x_return_status := 'U';
1466 x_msg_data := SQLERRM;
1467 IF g_debug = 1 THEN
1468 debug('SQL error: ' || SQLERRM, 'wms_mdc_pvt.validate_to_lpn');
1469 END IF;
1470
1471 END validate_to_lpn;
1472
1473
1474 END wms_mdc_pvt;
[Home] [Help]