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