1 PACKAGE BODY WMS_Container2_PUB AS
2 /* $Header: WMSCNT2B.pls 115.17 2003/02/05 02:15:59 rbande ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Container2_PUB';
6
7
8 PROCEDURE mdebug(msg in varchar2)
9 IS
10 l_msg VARCHAR2(5100);
11 l_ts VARCHAR2(30);
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14
15 select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
16 l_msg:=l_ts||' '||msg;
17
18
19 inv_mobile_helper_functions.tracelog
20 (p_err_msg => l_msg,
21 p_module => 'WMS_Container2_PUB',
22 p_level => 4);
23 --dbms_output.put_line(msg);
24 --INSERT INTO amintemp1 VALUES (msg);
25 null;
26
27 END;
28
29
30 -- ----------------------------------------------------------------------------------
31 -- ----------------------------------------------------------------------------------
32 PROCEDURE Purge_LPN
33 ( p_api_version IN NUMBER ,
34 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
35 p_commit IN VARCHAR2 := fnd_api.g_false ,
36 x_return_status OUT VARCHAR2 ,
37 x_msg_count OUT NUMBER ,
38 x_msg_data OUT VARCHAR2 ,
39 p_lpn_id IN NUMBER ,
40 p_purge_history IN NUMBER := 2 ,
41 p_del_history_days_old IN NUMBER := NULL
42 )
43 IS
44 l_api_name CONSTANT VARCHAR2(30) := 'Purge_LPN';
45 l_api_version CONSTANT NUMBER := 1.0;
46 l_lpn WMS_CONTAINER_PUB.LPN;
47 l_result NUMBER;
48 l_lpn_exist VARCHAR2(20);
49 l_lpn_contents_exist VARCHAR2(20);
50 l_lpn_serial_contents_exist VARCHAR2(20);
51 CURSOR c_lpn IS
52 SELECT 'Check for empty LPN'
53 FROM DUAL
54 WHERE EXISTS
55 (SELECT 'Child LPN'
56 FROM WMS_LICENSE_PLATE_NUMBERS
57 WHERE parent_lpn_id = p_lpn_id);
58 CURSOR c_lpn_contents IS
59 SELECT 'Check for empty LPN'
60 FROM DUAL
61 WHERE EXISTS
62 (SELECT 'Non serialized items'
63 FROM WMS_LPN_CONTENTS
64 WHERE parent_lpn_id = p_lpn_id);
65 CURSOR c_lpn_serial_contents IS
66 SELECT 'Check for empty LPN'
67 FROM DUAL
68 WHERE EXISTS
69 (SELECT 'Serialized items'
70 FROM MTL_SERIAL_NUMBERS
71 WHERE lpn_id = p_lpn_id);
72
73 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
74 BEGIN
75 -- Standard Start of API savepoint
76 SAVEPOINT Purge_LPN_PUB;
77 -- Standard call to check for call compatibility.
78 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
79 p_api_version ,
80 l_api_name ,
81 G_PKG_NAME )
82 THEN
83 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
84 FND_MSG_PUB.ADD;
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean( p_init_msg_list ) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91 -- Initialize API return status to success
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- API body
95 /* Validate all inputs */
96
97 /* Validate LPN */
98 l_lpn.lpn_id := p_lpn_id;
99 l_lpn.license_plate_number := NULL;
100 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
101 IF (l_result = INV_Validate.F) THEN
102 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
103 FND_MSG_PUB.ADD;
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106
107 /* End of input validation */
108
109 /* Check WMS_LICENSE_PLATE_NUMBERS table for any item stored within the LPN */
110 OPEN c_lpn;
111 FETCH c_lpn INTO l_lpn_exist;
112 IF c_lpn%FOUND THEN
113 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NON_EMPTY_LPN');
114 FND_MSG_PUB.ADD;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117 CLOSE c_lpn;
118
119 /* Check WMS_LPN_CONTENTS table for any item(s) stored within the LPN */
120 OPEN c_lpn_contents;
121 FETCH c_lpn_contents INTO l_lpn_contents_exist;
122 IF c_lpn_contents%FOUND THEN
123 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NON_EMPTY_LPN');
124 FND_MSG_PUB.ADD;
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127 CLOSE c_lpn_contents;
128
129 /* Check MTL_SERIAL_NUMBERS table for any serialized item(s) stored within the LPN */
130 OPEN c_lpn_serial_contents;
131 FETCH c_lpn_serial_contents INTO l_lpn_serial_contents_exist;
132 IF c_lpn_serial_contents%FOUND THEN
133 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NON_EMPTY_LPN');
134 FND_MSG_PUB.ADD;
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137 CLOSE c_lpn_serial_contents;
138
139 /* Nothing found within the LPN so okay to purge it now */
140 DELETE FROM WMS_LICENSE_PLATE_NUMBERS
141 WHERE lpn_id = p_lpn_id;
142
143 /* Check if the LPN history of this should be deleted or not */
144 IF (p_purge_history = 1) THEN
145 -- If this value is other than 1, we will just assume that no
146 -- LPN history records shall be purged
147 DELETE FROM WMS_LPN_HISTORIES
148 WHERE lpn_id = p_lpn_id
149 OR parent_lpn_id = p_lpn_id;
150 END IF;
151
152 -- If the entire history is not to be purged, check if some of
153 -- the past history records should be deleted or not
154 IF ((p_purge_history <> 1) AND
155 (p_del_history_days_old IS NOT NULL)) THEN
156 DELETE FROM wms_lpn_histories
157 WHERE (lpn_id = p_lpn_id
158 OR parent_lpn_id = p_lpn_id)
159 AND (SYSDATE - creation_date >= p_del_history_days_old);
160 END IF;
161
162 -- End of API body
163
164 -- Standard check of p_commit.
165 IF FND_API.To_Boolean( p_commit ) THEN
166 COMMIT WORK;
167 END IF;
168 -- Standard call to get message count and if count is 1,
169 -- get message info.
170 FND_MSG_PUB.Count_And_Get
171 ( p_count => x_msg_count,
172 p_data => x_msg_data
173 );
174 EXCEPTION
175 WHEN FND_API.G_EXC_ERROR THEN
176 ROLLBACK TO Purge_LPN_PUB;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 FND_MSG_PUB.Count_And_Get
179 ( p_count => x_msg_count,
180 p_data => x_msg_data
181 );
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 ROLLBACK TO Purge_LPN_PUB;
184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
185 FND_MSG_PUB.Count_And_Get
186 ( p_count => x_msg_count,
187 p_data => x_msg_data
188 );
189 WHEN OTHERS THEN
190 ROLLBACK TO Purge_LPN_PUB;
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 IF FND_MSG_PUB.Check_Msg_Level
193 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 THEN
195 FND_MSG_PUB.Add_Exc_Msg
196 ( G_PKG_NAME ,
197 l_api_name
198 );
199 END IF;
200 FND_MSG_PUB.Count_And_Get
201 ( p_count => x_msg_count,
202 p_data => x_msg_data
203 );
204
205 END Purge_LPN;
206
207
208 -- ----------------------------------------------------------------------------------
209 -- ----------------------------------------------------------------------------------
210 PROCEDURE Explode_LPN
211 ( p_api_version IN NUMBER ,
212 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
213 p_commit IN VARCHAR2 := fnd_api.g_false ,
214 x_return_status OUT VARCHAR2 ,
215 x_msg_count OUT NUMBER ,
216 x_msg_data OUT VARCHAR2 ,
217 p_lpn_id IN NUMBER ,
218 p_explosion_level IN NUMBER := 0 ,
219 x_content_tbl OUT WMS_CONTAINER_PUB.WMS_Container_Tbl_Type
220 )
221 IS
222 l_api_name CONSTANT VARCHAR2(30) := 'Explode_LPN';
223 l_api_version CONSTANT NUMBER := 1.0;
224 l_lpn WMS_CONTAINER_PUB.LPN;
225 l_result NUMBER;
226 l_counter NUMBER := 1; -- Counter variable initialized to 1
227 l_current_lpn NUMBER;
228 CURSOR nested_lpn_cursor IS
229 -- Bug# 1546081
230 -- SELECT *
231 SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
232 revision, lot_number, serial_number, cost_group_id
233 FROM WMS_LICENSE_PLATE_NUMBERS
234 WHERE Level <= p_explosion_level
235 START WITH lpn_id = p_lpn_id
236 CONNECT BY parent_lpn_id = PRIOR lpn_id;
237 CURSOR all_nested_lpn_cursor IS
238 -- Bug# 1546081
239 -- SELECT *
240 SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
241 revision, lot_number, serial_number, cost_group_id
242 FROM WMS_LICENSE_PLATE_NUMBERS
243 START WITH lpn_id = p_lpn_id
244 CONNECT BY parent_lpn_id = PRIOR lpn_id;
245 CURSOR lpn_contents_cursor IS
246 -- Bug# 1546081
247 -- SELECT *
248 SELECT parent_lpn_id, inventory_item_id, item_description,
249 organization_id, revision, lot_number,
250 serial_number, quantity, uom_code, cost_group_id
251 FROM WMS_LPN_CONTENTS
252 WHERE parent_lpn_id = l_current_lpn
253 AND NVL(serial_summary_entry, 2) = 2;
254 CURSOR lpn_serial_contents_cursor IS
255 -- Bug# 1546081
256 -- SELECT *
257 SELECT inventory_item_id, current_organization_id, lpn_id,
258 revision, lot_number, serial_number, cost_group_id
259 FROM MTL_SERIAL_NUMBERS
260 WHERE lpn_id = l_current_lpn;
261 l_container_content_rec WMS_CONTAINER_PUB.WMS_Container_Content_Rec_Type;
262 l_temp_uom_code VARCHAR2(3);
263
264 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
265 BEGIN
266 -- Standard Start of API savepoint
267 SAVEPOINT Explode_LPN_PUB;
268 -- Standard call to check for call compatibility.
269 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
270 p_api_version ,
271 l_api_name ,
272 G_PKG_NAME )
273 THEN
274 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
275 FND_MSG_PUB.ADD;
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278 -- Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean( p_init_msg_list ) THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282 -- Initialize API return status to success
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 -- API body
286 /* Validate all inputs */
287
288 /* Validate LPN */
289 l_lpn.lpn_id := p_lpn_id;
290 l_lpn.license_plate_number := NULL;
291 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
292 IF (l_result = INV_Validate.F) THEN
293 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
294 FND_MSG_PUB.ADD;
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297
298 /* Validate Explosion Level */
299 IF (p_explosion_level < 0) THEN
300 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_EXP_LVL');
301 FND_MSG_PUB.ADD;
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304 /* End of input validation */
305
306 IF (p_explosion_level = 0) THEN
307 /* Use the cursor that searches through all levels in the parent child relationship */
308 FOR v_lpn_id IN all_nested_lpn_cursor LOOP
309 l_current_lpn := v_lpn_id.lpn_id;
310
311 /* Store the lpn information also from license plate numbers table */
312 l_container_content_rec.parent_lpn_id := v_lpn_id.parent_lpn_id;
313 l_container_content_rec.content_lpn_id := v_lpn_id.lpn_id;
314 l_container_content_rec.content_item_id := v_lpn_id.inventory_item_id;
315 l_container_content_rec.content_description := NULL;
316 l_container_content_rec.content_type := '2';
317 l_container_content_rec.organization_id := v_lpn_id.organization_id;
318 l_container_content_rec.revision := v_lpn_id.revision;
319 l_container_content_rec.lot_number := v_lpn_id.lot_number;
320 l_container_content_rec.serial_number := v_lpn_id.serial_number;
321 l_container_content_rec.quantity := 1;
322 l_container_content_rec.uom := NULL;
323 l_container_content_rec.cost_group_id := v_lpn_id.cost_group_id;
324
325 x_content_tbl(l_counter) := l_container_content_rec;
326 l_counter := l_counter + 1;
327
328 /* Store all the item information from the lpn contents table */
329 FOR v_lpn_content IN lpn_contents_cursor LOOP
330 l_container_content_rec.parent_lpn_id := v_lpn_content.parent_lpn_id;
331 l_container_content_rec.content_lpn_id := NULL;
332 l_container_content_rec.content_item_id := v_lpn_content.inventory_item_id;
333 l_container_content_rec.content_description := v_lpn_content.item_description;
334 IF (v_lpn_content.inventory_item_id IS NOT NULL) THEN
335 l_container_content_rec.content_type := '1';
336 ELSE
337 l_container_content_rec.content_type := '3';
338 END IF;
339 l_container_content_rec.organization_id := v_lpn_content.organization_id;
340 l_container_content_rec.revision := v_lpn_content.revision;
341 l_container_content_rec.lot_number := v_lpn_content.lot_number;
342 l_container_content_rec.serial_number := v_lpn_content.serial_number;
343 l_container_content_rec.quantity := v_lpn_content.quantity;
344 l_container_content_rec.uom := v_lpn_content.uom_code;
345 l_container_content_rec.cost_group_id := v_lpn_content.cost_group_id;
346
347 x_content_tbl(l_counter) := l_container_content_rec;
348 l_counter := l_counter + 1;
349 END LOOP;
350
351 -- Store all the serialized item information from the serial
352 -- numbers table
353 FOR v_lpn_serial_content IN lpn_serial_contents_cursor LOOP
354 /* Get the primary UOM for the serialized item */
355 SELECT primary_uom_code
356 INTO l_temp_uom_code
357 FROM mtl_system_items
358 WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
359 AND organization_id = v_lpn_serial_content.current_organization_id;
360
361 l_container_content_rec.parent_lpn_id := v_lpn_serial_content.lpn_id;
362 l_container_content_rec.content_lpn_id := NULL;
363 l_container_content_rec.content_item_id := v_lpn_serial_content.inventory_item_id;
364 l_container_content_rec.content_description := NULL;
365 l_container_content_rec.content_type := '1';
366 l_container_content_rec.organization_id := v_lpn_serial_content.current_organization_id;
367 l_container_content_rec.revision := v_lpn_serial_content.revision;
368 l_container_content_rec.lot_number := v_lpn_serial_content.lot_number;
369 l_container_content_rec.serial_number := v_lpn_serial_content.serial_number;
370 l_container_content_rec.quantity := 1;
371 l_container_content_rec.uom := l_temp_uom_code;
372 l_container_content_rec.cost_group_id := v_lpn_serial_content.cost_group_id;
373
374 x_content_tbl(l_counter) := l_container_content_rec;
375 l_counter := l_counter + 1;
376 END LOOP;
377
378 END LOOP;
379 ELSE
380 /* Use the cursor that searches only a specified number of levels */
381 FOR v_lpn_id IN nested_lpn_cursor LOOP
382 l_current_lpn := v_lpn_id.lpn_id;
383
384 /* Store the lpn information also from license plate numbers table */
388 l_container_content_rec.content_description := NULL;
385 l_container_content_rec.parent_lpn_id := v_lpn_id.parent_lpn_id;
386 l_container_content_rec.content_lpn_id := v_lpn_id.lpn_id;
387 l_container_content_rec.content_item_id := v_lpn_id.inventory_item_id;
389 l_container_content_rec.content_type := '2';
390 l_container_content_rec.organization_id := v_lpn_id.organization_id;
391 l_container_content_rec.revision := v_lpn_id.revision;
392 l_container_content_rec.lot_number := v_lpn_id.lot_number;
393 l_container_content_rec.serial_number := v_lpn_id.serial_number;
394 l_container_content_rec.quantity := 1;
395 l_container_content_rec.uom := NULL;
396 l_container_content_rec.cost_group_id := v_lpn_id.cost_group_id;
397
398 x_content_tbl(l_counter) := l_container_content_rec;
399 l_counter := l_counter + 1;
400
401 /* Store all the item information from the lpn contents table */
402 FOR v_lpn_content IN lpn_contents_cursor LOOP
403 l_container_content_rec.parent_lpn_id := v_lpn_content.parent_lpn_id;
404 l_container_content_rec.content_lpn_id := NULL;
405 l_container_content_rec.content_item_id := v_lpn_content.inventory_item_id;
406 l_container_content_rec.content_description := v_lpn_content.item_description;
407 IF (v_lpn_content.inventory_item_id IS NOT NULL) THEN
408 l_container_content_rec.content_type := '1';
409 ELSE
410 l_container_content_rec.content_type := '3';
411 END IF;
412 l_container_content_rec.organization_id := v_lpn_content.organization_id;
413 l_container_content_rec.revision := v_lpn_content.revision;
414 l_container_content_rec.lot_number := v_lpn_content.lot_number;
415 l_container_content_rec.serial_number := v_lpn_content.serial_number;
416 l_container_content_rec.quantity := v_lpn_content.quantity;
417 l_container_content_rec.uom := v_lpn_content.uom_code;
418 l_container_content_rec.cost_group_id := v_lpn_content.cost_group_id;
419
420 x_content_tbl(l_counter) := l_container_content_rec;
421 l_counter := l_counter + 1;
422 END LOOP;
423
424 -- Store all the serialized item information from the serial
425 -- numbers table
426 FOR v_lpn_serial_content IN lpn_serial_contents_cursor LOOP
427 /* Get the primary UOM for the serialized item */
428 SELECT primary_uom_code
429 INTO l_temp_uom_code
430 FROM mtl_system_items
431 WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
432 AND organization_id = v_lpn_serial_content.current_organization_id;
433
434 l_container_content_rec.parent_lpn_id := v_lpn_serial_content.lpn_id;
435 l_container_content_rec.content_lpn_id := NULL;
436 l_container_content_rec.content_item_id := v_lpn_serial_content.inventory_item_id;
437 l_container_content_rec.content_description := NULL;
438 l_container_content_rec.content_type := '1';
439 l_container_content_rec.organization_id := v_lpn_serial_content.current_organization_id;
440 l_container_content_rec.revision := v_lpn_serial_content.revision;
441 l_container_content_rec.lot_number := v_lpn_serial_content.lot_number;
442 l_container_content_rec.serial_number := v_lpn_serial_content.serial_number;
443 l_container_content_rec.quantity := 1;
444 l_container_content_rec.uom := l_temp_uom_code;
445 l_container_content_rec.cost_group_id := v_lpn_serial_content.cost_group_id;
446
447 x_content_tbl(l_counter) := l_container_content_rec;
448 l_counter := l_counter + 1;
449 END LOOP;
450
451 END LOOP;
452 END IF;
453
454 -- End of API body
455
456 -- Standard check of p_commit.
457 IF FND_API.To_Boolean( p_commit ) THEN
458 COMMIT WORK;
459 END IF;
460 -- Standard call to get message count and if count is 1,
461 -- get message info.
462 FND_MSG_PUB.Count_And_Get
463 ( p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466 EXCEPTION
467 WHEN FND_API.G_EXC_ERROR THEN
468 ROLLBACK TO Explode_LPN_PUB;
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 FND_MSG_PUB.Count_And_Get
471 ( p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
475 ROLLBACK TO Explode_LPN_PUB;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 FND_MSG_PUB.Count_And_Get
478 ( p_count => x_msg_count,
479 p_data => x_msg_data
480 );
481 WHEN OTHERS THEN
482 ROLLBACK TO Explode_LPN_PUB;
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 IF FND_MSG_PUB.Check_Msg_Level
485 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
486 THEN
487 FND_MSG_PUB.Add_Exc_Msg
488 ( G_PKG_NAME ,
489 l_api_name
490 );
491 END IF;
492 FND_MSG_PUB.Count_And_Get
493 ( p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497 END Explode_LPN;
498
499
500 -- ----------------------------------------------------------------------------------
504 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
501 -- ----------------------------------------------------------------------------------
502 PROCEDURE Transfer_LPN_Contents
503 ( p_api_version IN NUMBER ,
505 p_commit IN VARCHAR2 := fnd_api.g_false ,
506 x_return_status OUT VARCHAR2 ,
507 x_msg_count OUT NUMBER ,
508 x_msg_data OUT VARCHAR2 ,
509 p_lpn_id_source IN NUMBER ,
510 p_lpn_id_dest IN NUMBER
511 )
512 IS
513 l_api_name CONSTANT VARCHAR2(30) := 'Transfer_LPN_Contents';
514 l_api_version CONSTANT NUMBER := 1.0;
515 l_lpn_source WMS_CONTAINER_PUB.LPN;
516 l_lpn_dest WMS_CONTAINER_PUB.LPN;
517 l_result NUMBER;
518
519 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
520 BEGIN
521 -- Standard Start of API savepoint
522 SAVEPOINT Transfer_LPN_Contents_PUB;
523 -- Standard call to check for call compatibility.
524 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
525 p_api_version ,
526 l_api_name ,
527 G_PKG_NAME )
528 THEN
529 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
530 FND_MSG_PUB.ADD;
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533 -- Initialize message list if p_init_msg_list is set to TRUE.
534 IF FND_API.to_Boolean( p_init_msg_list ) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537 -- Initialize API return status to success
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 -- API body
541 /* Validate all inputs */
542
543 /* Validate source LPN */
544 l_lpn_source.lpn_id := p_lpn_id_source;
545 l_lpn_source.license_plate_number := NULL;
546 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn_source);
547 IF (l_result = INV_Validate.F) THEN
548 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
549 FND_MSG_PUB.ADD;
550 RAISE FND_API.G_EXC_ERROR;
551 END IF;
552
553 /* Validate destination LPN */
554 l_lpn_dest.lpn_id := p_lpn_id_dest;
555 l_lpn_dest.license_plate_number := NULL;
556 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn_dest);
557 IF (l_result = INV_Validate.F) THEN
558 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
559 FND_MSG_PUB.ADD;
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562 /* End of input validation */
563
564 UPDATE WMS_LICENSE_PLATE_NUMBERS
565 SET parent_lpn_id = p_lpn_id_dest
566 WHERE parent_lpn_id = p_lpn_id_source;
567
568 UPDATE WMS_LPN_CONTENTS
569 SET parent_lpn_id = p_lpn_id_dest
570 WHERE parent_lpn_id = p_lpn_id_source;
571
572 UPDATE MTL_SERIAL_NUMBERS
573 SET lpn_id = p_lpn_id_dest
574 WHERE lpn_id = p_lpn_id_source;
575
576 -- End of API body
577
578 -- Standard check of p_commit.
579 IF FND_API.To_Boolean( p_commit ) THEN
580 COMMIT WORK;
581 END IF;
582 -- Standard call to get message count and if count is 1,
583 -- get message info.
584 FND_MSG_PUB.Count_And_Get
585 ( p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 ROLLBACK TO Transfer_LPN_Contents_PUB;
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 FND_MSG_PUB.Count_And_Get
593 ( p_count => x_msg_count,
594 p_data => x_msg_data
595 );
596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
597 ROLLBACK TO Transfer_LPN_Contents_PUB;
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
599 FND_MSG_PUB.Count_And_Get
600 ( p_count => x_msg_count,
601 p_data => x_msg_data
602 );
603 WHEN OTHERS THEN
604 ROLLBACK TO Transfer_LPN_Contents_PUB;
605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
606 IF FND_MSG_PUB.Check_Msg_Level
607 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
608 THEN
609 FND_MSG_PUB.Add_Exc_Msg
610 ( G_PKG_NAME ,
611 l_api_name
612 );
613 END IF;
614 FND_MSG_PUB.Count_And_Get
615 ( p_count => x_msg_count,
616 p_data => x_msg_data
617 );
618
619 END Transfer_LPN_Contents;
620
621
622 -- ----------------------------------------------------------------------------------
623 -- ----------------------------------------------------------------------------------
624 PROCEDURE Container_Required_Qty
625 ( p_api_version IN NUMBER ,
626 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
627 p_commit IN VARCHAR2 := fnd_api.g_false ,
628 x_return_status OUT VARCHAR2 ,
629 x_msg_count OUT NUMBER ,
630 x_msg_data OUT VARCHAR2 ,
631 p_source_item_id IN NUMBER ,
632 p_source_qty IN NUMBER ,
633 p_source_qty_uom IN VARCHAR2 ,
634 p_qty_per_cont IN NUMBER := NULL ,
638 p_qty_required OUT NUMBER
635 p_qty_per_cont_uom IN VARCHAR2 := NULL ,
636 p_organization_id IN NUMBER ,
637 p_dest_cont_item_id IN OUT NUMBER ,
639 )
640 IS
641 l_api_name CONSTANT VARCHAR2(30) := 'Container_Required_Qty';
642 l_api_version CONSTANT NUMBER := 1.0;
643 l_source_item INV_Validate.ITEM;
644 l_dest_cont_item INV_Validate.ITEM;
645 l_cont_item INV_Validate.ITEM;
646 l_org INV_Validate.ORG;
647 l_result NUMBER;
648 l_max_load_quantity NUMBER;
649 l_qty_per_cont NUMBER;
650 l_curr_min_container NUMBER;
651 l_curr_min_value NUMBER;
652 l_curr_load_quantity NUMBER;
653 l_temp_min_value NUMBER;
654 l_temp_value NUMBER;
655 l_temp_load_quantity NUMBER;
656 CURSOR max_load_cursor IS
657 SELECT max_load_quantity
658 FROM WSH_CONTAINER_ITEMS
659 WHERE master_organization_id = p_organization_id
660 AND container_item_id = p_dest_cont_item_id
661 AND load_item_id = p_source_item_id;
662
663 CURSOR container_items_cursor IS
664 SELECT container_item_id, max_load_quantity, preferred_flag
665 FROM WSH_CONTAINER_ITEMS
666 WHERE master_organization_id = p_organization_id
667 AND load_item_id = p_source_item_id
668 AND container_item_id IN
669 (SELECT inventory_item_id
670 FROM MTL_SYSTEM_ITEMS
671 WHERE mtl_transactions_enabled_flag = 'Y'
672 AND container_item_flag = 'Y'
673 AND organization_id = p_organization_id);
674
675 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
676 BEGIN
677 -- Standard Start of API savepoint
678 SAVEPOINT Container_Required_Qty_PUB;
679 -- Standard call to check for call compatibility.
680 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
681 p_api_version ,
682 l_api_name ,
683 G_PKG_NAME )
684 THEN
685 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
686 FND_MSG_PUB.ADD;
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
688 END IF;
689 -- Initialize message list if p_init_msg_list is set to TRUE.
690 IF FND_API.to_Boolean( p_init_msg_list ) THEN
691 FND_MSG_PUB.initialize;
692 END IF;
693 -- Initialize API return status to success
694 x_return_status := FND_API.G_RET_STS_SUCCESS;
695
696 -- API body
697 /* Validate all inputs */
698
699 /* Validate Organization ID */
700 l_org.organization_id := p_organization_id;
701 l_result := INV_Validate.Organization(l_org);
702 IF (l_result = INV_Validate.F) THEN
703 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ORG');
704 FND_MSG_PUB.ADD;
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 /* Validate Source item */
709 l_source_item.inventory_item_id := p_source_item_id;
710 l_result := INV_Validate.inventory_item(l_source_item, l_org);
711 IF (l_result = INV_Validate.F) THEN
712 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ITEM');
713 FND_MSG_PUB.ADD;
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
717 /* Validate Source Quantity */
718 IF ((p_source_qty IS NULL) OR (p_source_qty <= 0)) THEN
719 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SRC_QTY');
720 FND_MSG_PUB.ADD;
721 RAISE FND_API.G_EXC_ERROR;
722 END IF;
723
724 /* Validate Source UOM */
725 l_result := INV_Validate.Uom(p_source_qty_uom, l_org, l_source_item);
726 IF (l_result = INV_Validate.F) THEN
727 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_SRC_UOM');
728 FND_MSG_PUB.ADD;
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732 /* Validate Quantity Per Container */
733 IF (p_qty_per_cont IS NOT NULL) THEN
734 IF (p_qty_per_cont <= 0) THEN
735 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVLD_QTY_PER_CONT');
736 FND_MSG_PUB.ADD;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739 END IF;
740
741 /* Validate Quantity Per Container UOM */
742 IF (p_qty_per_cont IS NOT NULL) THEN
743 l_result := INV_Validate.Uom(p_qty_per_cont_uom, l_org, l_source_item);
744 IF (l_result = INV_Validate.F) THEN
745 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVLD_QTY_PER_UOM');
746 FND_MSG_PUB.ADD;
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749 END IF;
750
751 /* Validate Destination container item */
752 IF (p_dest_cont_item_id IS NOT NULL) THEN
753 l_dest_cont_item.inventory_item_id := p_dest_cont_item_id;
754 l_result := INV_Validate.inventory_item(l_dest_cont_item, l_org);
755 IF (l_result = INV_Validate.F) THEN
756 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONT_ITEM');
757 FND_MSG_PUB.ADD;
758 RAISE FND_API.G_EXC_ERROR;
759 ELSIF (l_dest_cont_item.container_item_flag = 'N') THEN
760 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_ITEM_NOT_A_CONT');
761 FND_MSG_PUB.ADD;
762 RAISE FND_API.G_EXC_ERROR;
763 END IF;
764 END IF;
765 /* End of input validation */
766
767 IF (p_dest_cont_item_id IS NOT NULL) THEN
768 /* Extract or calculate the value of l_max_load_quantity */
769 OPEN max_load_cursor;
770 FETCH max_load_cursor INTO l_max_load_quantity;
771 IF max_load_cursor%NOTFOUND THEN
775 IF ((l_source_item.unit_weight IS NULL) OR
772 /* Need to calculate this value based on weight and volume constraints */
773 -- Check that the source item contains all the physical item information
774 -- needed for calculation of l_max_load_quantity
776 (l_source_item.weight_uom_code IS NULL) OR
777 (l_source_item.unit_volume IS NULL) OR
778 (l_source_item.volume_uom_code IS NULL)) THEN
779 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NOT_ENOUGH_INFO');
780 FND_MSG_PUB.ADD;
781 RAISE FND_API.G_EXC_ERROR;
782 END IF;
783
784 /* Volume constraint */
785 l_temp_value := inv_convert.inv_um_convert(l_source_item.inventory_item_id, 6,
786 l_source_item.unit_volume,
787 l_source_item.volume_uom_code,
788 l_dest_cont_item.volume_uom_code,
789 NULL, NULL);
790 IF (l_temp_value = -99999) THEN
791 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
792 FND_MSG_PUB.ADD;
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795
796 IF (l_dest_cont_item.internal_volume IS NOT NULL) THEN
797 -- Check that the source item's unit volume is less than or
798 -- equal to the destination container item's internal volume
799 IF (l_temp_value <= l_dest_cont_item.internal_volume) THEN
800 l_max_load_quantity := FLOOR(l_dest_cont_item.internal_volume/l_temp_value);
801 ELSE
802 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_ITEM_TOO_LARGE');
803 FND_MSG_PUB.ADD;
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 END IF;
807 /* Weight constraint */
808 l_temp_value := inv_convert.inv_um_convert(l_source_item.inventory_item_id, 6,
809 l_source_item.unit_weight,
810 l_source_item.weight_uom_code,
811 l_dest_cont_item.weight_uom_code,
812 NULL, NULL);
813 IF (l_temp_value = -99999) THEN
814 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
815 FND_MSG_PUB.ADD;
816 RAISE FND_API.G_EXC_ERROR;
817 END IF;
818
819 /* Select the most constraining value for l_max_load_quantity */
820 IF (l_dest_cont_item.maximum_load_weight IS NOT NULL) THEN
821 -- Check that the source item's unit weight is less than or
822 -- equal to the destination container item's maximum load weight
823 IF (l_temp_value <= l_dest_cont_item.maximum_load_weight) THEN
824 IF (l_max_load_quantity > FLOOR (l_dest_cont_item.maximum_load_weight /
825 l_temp_value)) THEN
826 l_max_load_quantity := FLOOR (l_dest_cont_item.maximum_load_weight /
827 l_temp_value);
828 END IF;
829 ELSE
830 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_ITEM_TOO_LARGE');
831 FND_MSG_PUB.ADD;
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834 END IF;
835 END IF;
836 CLOSE max_load_cursor;
837
838 /* Convert l_max_load_quantity into the same UOM as p_source_qty_uom */
839 IF (l_max_load_quantity IS NOT NULL) THEN
840 l_max_load_quantity := inv_convert.inv_um_convert(l_source_item.inventory_item_id, 6,
841 l_max_load_quantity,
842 l_source_item.primary_uom_code,
843 p_source_qty_uom,
844 NULL, NULL);
845 IF (l_max_load_quantity = -99999) THEN
846 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
847 FND_MSG_PUB.ADD;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850 END IF;
851
852 /* Calculate the required number of containers needed to store the items */
853 IF ((p_qty_per_cont IS NOT NULL) AND (l_max_load_quantity IS NOT NULL)) THEN
854 l_qty_per_cont := inv_convert.inv_um_convert(l_source_item.inventory_item_id, 6,
855 p_qty_per_cont,
856 p_qty_per_cont_uom,
857 p_source_qty_uom,
858 NULL, NULL);
859 IF (l_qty_per_cont = -99999) THEN
860 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
861 FND_MSG_PUB.ADD;
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 IF (l_qty_per_cont > l_max_load_quantity) THEN
866 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_OVERPACKED_OPERATION');
867 FND_MSG_PUB.ADD;
868 RAISE FND_API.G_EXC_ERROR;
869 ELSE
870 p_qty_required := CEIL(p_source_qty/l_qty_per_cont);
871 END IF;
872 ELSIF ((p_qty_per_cont IS NULL) AND (l_max_load_quantity IS NOT NULL)) THEN
873 p_qty_required := CEIL(p_source_qty/l_max_load_quantity);
874 ELSE
875 -- If the destination container item contains no internal volume or maximum
876 -- load weight restriction values, assume that it has infinite capacity
877 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NO_RESTRICTIONS_FND');
878 -- FND_MESSAGE.SHOW;
879 p_qty_required := 1;
880 END IF;
881
882 ELSE /* No container item was given */
883 l_curr_min_container := 0;
884 -- Search through all the containers in WSH_CONTAINER_ITEMS table which can store
885 -- the given load_item_id
886 FOR v_container_item IN container_items_cursor LOOP
887 /* Get the item information for the current container item being considered */
888 l_cont_item.inventory_item_id := v_container_item.container_item_id;
889 l_result := INV_Validate.inventory_item(l_cont_item, l_org);
890 IF (l_result = INV_Validate.F) THEN
891 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONT_ITEM');
892 FND_MSG_PUB.ADD;
893 RAISE FND_API.G_EXC_ERROR;
894 END IF;
895
899 l_source_item.primary_uom_code, p_source_qty_uom, NULL, NULL);
896 /* Get the max load quantity for that given container */
897 l_temp_load_quantity := inv_convert.inv_um_convert
898 (l_source_item.inventory_item_id, 6, v_container_item.max_load_quantity,
900 IF (l_temp_load_quantity = -99999) THEN
901 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_CONVERSION');
902 FND_MSG_PUB.ADD;
903 RAISE FND_API.G_EXC_ERROR;
904 END IF;
905
906 -- Calculate the min value, i.e. how much space is empty in the final container
907 -- used to store the items in units of the source item's uom
908 l_temp_min_value := l_temp_load_quantity - MOD(p_source_qty, l_temp_load_quantity);
909
910 -- If ther preferred container flag is set for this container load relationship
911 -- Use it reguardless of it's min value
912 IF ( v_container_item.preferred_flag = 'Y' ) THEN
913 l_curr_min_container := v_container_item.container_item_id;
914 l_curr_load_quantity := l_temp_load_quantity;
915 EXIT;
916 -- Compare the min value for this container with the best one found so far
917 ELSIF ((l_curr_min_container = 0) OR (l_temp_min_value < l_curr_min_value)) THEN
918 l_curr_min_value := l_temp_min_value;
919 l_curr_min_container := v_container_item.container_item_id;
920 l_curr_load_quantity := l_temp_load_quantity;
921 -- If the min values are the same, then choose the container which can hold
922 -- more of the source item, i.e. has a higher load quantity
923 ELSIF (l_temp_min_value = l_curr_min_value) THEN
924 IF (l_temp_load_quantity > l_curr_load_quantity) THEN
925 l_curr_min_value := l_temp_min_value;
926 l_curr_min_container := v_container_item.container_item_id;
927 l_curr_load_quantity := l_temp_load_quantity;
928 END IF;
929 END IF;
930 END LOOP;
931 /* No containers were found that can store the source item */
932 IF (l_curr_min_container = 0) THEN
933 p_qty_required := 0;
934 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NO_CONTAINER_FOUND');
935 FND_MSG_PUB.ADD;
936 RAISE FND_API.G_EXC_ERROR;
937 ELSE
938 /* Valid container found. Store this information in the output parameters */
939 p_dest_cont_item_id := l_curr_min_container;
940 p_qty_required := CEIL(p_source_qty / l_curr_load_quantity);
941 END IF;
942 END IF;
943 -- End of API body
944
945 -- Standard check of p_commit.
946 IF FND_API.To_Boolean( p_commit ) THEN
947 COMMIT WORK;
948 END IF;
949 -- Standard call to get message count and if count is 1,
950 -- get message info.
951 FND_MSG_PUB.Count_And_Get
952 ( p_count => x_msg_count,
953 p_data => x_msg_data
954 );
955 EXCEPTION
956 WHEN FND_API.G_EXC_ERROR THEN
957 ROLLBACK TO Container_Required_Qty_PUB;
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 FND_MSG_PUB.Count_And_Get
960 ( p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964 ROLLBACK TO Container_Required_Qty_PUB;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 FND_MSG_PUB.Count_And_Get
967 ( p_count => x_msg_count,
968 p_data => x_msg_data
969 );
970 WHEN OTHERS THEN
971 ROLLBACK TO Container_Required_Qty_PUB;
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 IF FND_MSG_PUB.Check_Msg_Level
974 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
975 THEN
976 FND_MSG_PUB.Add_Exc_Msg
977 ( G_PKG_NAME ,
978 l_api_name
979 );
980 END IF;
981 FND_MSG_PUB.Count_And_Get
982 ( p_count => x_msg_count,
983 p_data => x_msg_data
984 );
985
986 END Container_Required_Qty;
987
988
989
990 -- ----------------------------------------------------------------------------------
991 -- ----------------------------------------------------------------------------------
992 PROCEDURE Get_Outermost_LPN
993 ( p_api_version IN NUMBER ,
994 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
995 p_commit IN VARCHAR2 := fnd_api.g_false ,
996 x_return_status OUT VARCHAR2 ,
997 x_msg_count OUT NUMBER ,
998 x_msg_data OUT VARCHAR2 ,
999 p_lpn_id IN NUMBER := NULL ,
1000 p_inventory_item_id IN NUMBER := NULL ,
1001 p_revision IN VARCHAR2 := NULL ,
1002 p_lot_number IN VARCHAR2 := NULL ,
1003 p_serial_number IN VARCHAR2 := NULL ,
1004 x_lpn_list OUT WMS_CONTAINER_PUB.LPN_Table_Type
1005 )
1006 IS
1007 l_api_name CONSTANT VARCHAR2(30) := 'Get_Outermost_LPN';
1008 l_api_version CONSTANT NUMBER := 1.0;
1009 l_SelectStmt VARCHAR2(500);
1010 l_CursorID INTEGER;
1011 l_Dummy INTEGER;
1012 l_temp_lpn NUMBER;
1013 CURSOR nested_parent_lpn_cursor IS
1014 SELECT *
1015 FROM WMS_LICENSE_PLATE_NUMBERS
1016 START WITH lpn_id = p_lpn_id
1017 CONNECT BY lpn_id = PRIOR parent_lpn_id;
1018 l_current_lpn NUMBER;
1019 CURSOR nested_parent_lpn_cursor_2 IS
1020 SELECT *
1024 l_lpn WMS_CONTAINER_PUB.LPN;
1021 FROM WMS_LICENSE_PLATE_NUMBERS
1022 START WITH lpn_id = l_current_lpn
1023 CONNECT BY lpn_id = PRIOR parent_lpn_id;
1025 l_temp_table WMS_CONTAINER_PUB.LPN_Table_Type;
1026 l_index BINARY_INTEGER := 1;
1027 l_index_2 BINARY_INTEGER := 1;
1028 l_duplicate_index BINARY_INTEGER := 1;
1029 l_result NUMBER;
1030
1031 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1032 BEGIN
1033 -- Standard Start of API savepoint
1034 SAVEPOINT Get_Outermost_LPN_PUB;
1035 -- Standard call to check for call compatibility.
1036 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1037 p_api_version ,
1038 l_api_name ,
1039 G_PKG_NAME )
1040 THEN
1041 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1042 FND_MSG_PUB.ADD;
1043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1044 END IF;
1045 -- Initialize message list if p_init_msg_list is set to TRUE.
1046 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1047 FND_MSG_PUB.initialize;
1048 END IF;
1049 -- Initialize API return status to success
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 -- API body
1053 /* Validate all inputs */
1054
1055 /* Validate that enough info has been passed in */
1056 IF ((p_lpn_id IS NULL) AND (p_inventory_item_id IS NULL)) THEN
1057 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_NOT_ENOUGH_INFO');
1058 FND_MSG_PUB.ADD;
1059 RAISE FND_API.G_EXC_ERROR;
1060 END IF;
1061
1062 /* Validate LPN */
1063 IF (p_lpn_id IS NOT NULL) THEN
1064 l_lpn.lpn_id := p_lpn_id;
1065 l_lpn.license_plate_number := NULL;
1066 l_result := WMS_CONTAINER_PUB.Validate_LPN(l_lpn);
1067 IF (l_result = INV_Validate.F) THEN
1068 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
1069 FND_MSG_PUB.ADD;
1070 RAISE FND_API.G_EXC_ERROR;
1071 END IF;
1072 END IF;
1073 /* End of input validation */
1074
1075 IF (p_lpn_id IS NOT NULL) THEN
1076 -- Find outermost LPN for a specific LPN
1077 FOR l_lpn_record IN nested_parent_lpn_cursor LOOP
1078 IF (l_lpn_record.parent_lpn_id IS NULL) THEN
1079 x_lpn_list(1) := l_lpn_record;
1080 -- There should only be one record that has no parent
1081 -- which corresponds to the outermost LPN, assuming that
1082 -- the data in the table is consistent.
1083 END IF;
1084 END LOOP;
1085 ELSE -- Find outermost LPN(s) for a specific item
1086 -- First we need to get all of the LPN's which store the given
1087 -- inventory item. Use DBMS_SQL to do a dynamic query on the
1088 -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1089 -- given item with the specified parameters.
1090
1091 l_SelectStmt := 'SELECT PARENT_LPN_ID FROM WMS_LPN_CONTENTS WHERE ';
1092 l_SelectStmt := l_SelectStmt || 'inventory_item_id = ' ||
1093 p_inventory_item_id || ' AND ';
1094 IF (p_revision IS NOT NULL) THEN
1095 l_SelectStmt := l_SelectStmt || 'revision = ' ||
1096 p_revision || ' AND ';
1097 END IF;
1098 IF (p_lot_number IS NOT NULL) THEN
1099 l_SelectStmt := l_SelectStmt || 'lot_number = ' ||
1100 p_lot_number || ' AND ';
1101 END IF;
1102 IF (p_serial_number IS NOT NULL) THEN
1103 l_SelectStmt := l_SelectStmt || 'serial_number = ' ||
1104 p_serial_number || ' AND ';
1105 END IF;
1106
1107 -- Tie up loose ends of the where clause in the query statement
1108 l_SelectStmt := l_SelectStmt || '1 = 1';
1109
1110 -- Open a cursor for processing.
1111 l_CursorID := DBMS_SQL.OPEN_CURSOR;
1112
1113 -- Parse the query
1114 DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, DBMS_SQL.V7);
1115
1116 -- Define the output variables
1117 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, l_temp_lpn);
1118
1119 -- Execute the statement. We don't care about the return value,
1120 -- but we do need to declare a variable for it.
1121 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
1122
1123 -- This is the fetch loop
1124 LOOP
1125 -- Fetch the rows into the buffer, and also check for the exit
1126 -- condition from the loop.
1127 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
1128 EXIT;
1129 END IF;
1130
1131 -- Retrieve the rows from the buffer into a temp variable.
1132 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_temp_lpn);
1133
1134 -- Get the outermost LPN for each parent lpn from WMS_LPN_CONTENTS
1135 l_current_lpn := l_temp_lpn;
1136 FOR l_lpn_record IN nested_parent_lpn_cursor_2 LOOP
1137 IF (l_lpn_record.parent_lpn_id IS NULL) THEN
1138 l_temp_table(l_index) := l_lpn_record;
1139 l_index := l_index + 1;
1140 -- There should only be one record that has no parent lpn
1141 -- which corresponds to the outermost LPN, assuming that
1142 -- the data in the table is consistent.
1143 END IF;
1144 END LOOP;
1145 END LOOP;
1146
1147 -- Close the cursor.
1148 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
1149
1150 -- Now we have a table, l_temp_table which contains the list of
1151 -- outermost LPN records. There are possibly duplicate records
1152 -- since an item can be stored in multiple LPN's. We now need to
1156 IF (l_temp_table.COUNT <> 0) THEN -- Check that entries found
1153 -- pick out only one of each LPN record to be stored in the output
1154 -- table, x_lpn_list.
1155
1157 l_index := l_temp_table.FIRST; -- Initialize the index
1158 LOOP
1159 -- Transfer only new lpn record entries from l_temp_table into
1160 -- x_lpn_list. Check if the current record in l_temp_table is
1161 -- already in x_lpn_list
1162 IF (x_lpn_list.COUNT = 0) THEN -- Insert first initial record
1163 x_lpn_list(l_index_2) := l_temp_table(l_index);
1164 l_index_2 := l_index_2 + 1;
1165 ELSE -- Check if current record in l_temp_table is already there
1166 l_duplicate_index := x_lpn_list.FIRST;
1167
1168 <<Check_Duplicate_Loop>>
1169 LOOP
1170 IF (l_temp_table(l_index).lpn_id =
1171 x_lpn_list(l_duplicate_index).lpn_id) THEN
1172 EXIT Check_Duplicate_Loop; -- Entry is already in table
1173 END IF;
1174
1175 IF (l_duplicate_index = x_lpn_list.LAST) THEN
1176 -- All entries have been checked and none match
1177 x_lpn_list(l_index_2) := l_temp_table(l_index);
1178 l_index_2 := l_index_2 + 1;
1179 END IF;
1180 EXIT WHEN l_duplicate_index = x_lpn_list.LAST;
1181 l_duplicate_index := x_lpn_list.NEXT(l_duplicate_index);
1182 END LOOP Check_Duplicate_Loop;
1183
1184 END IF;
1185 EXIT WHEN l_index = l_temp_table.LAST;
1186 l_index := l_temp_table.NEXT(l_index);
1187 END LOOP;
1188 END IF;
1189
1190 END IF;
1191 -- End of API body
1192
1193 -- Standard check of p_commit.
1194 IF FND_API.To_Boolean( p_commit ) THEN
1195 COMMIT WORK;
1196 END IF;
1197 -- Standard call to get message count and if count is 1,
1198 -- get message info.
1199 FND_MSG_PUB.Count_And_Get
1200 ( p_count => x_msg_count,
1201 p_data => x_msg_data
1202 );
1203
1204 EXCEPTION
1205 WHEN FND_API.G_EXC_ERROR THEN
1206 ROLLBACK TO Get_Outermost_LPN_PUB;
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 FND_MSG_PUB.Count_And_Get
1209 ( p_count => x_msg_count,
1210 p_data => x_msg_data
1211 );
1212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1213 ROLLBACK TO Get_Outermost_LPN_PUB;
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 FND_MSG_PUB.Count_And_Get
1216 ( p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219 WHEN OTHERS THEN
1220 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
1221 ROLLBACK TO Get_Outermost_LPN_PUB;
1222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1223 IF FND_MSG_PUB.Check_Msg_Level
1224 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1225 THEN
1226 FND_MSG_PUB.Add_Exc_Msg
1227 ( G_PKG_NAME ,
1228 l_api_name
1229 );
1230 END IF;
1231 FND_MSG_PUB.Count_And_Get
1232 ( p_count => x_msg_count,
1233 p_data => x_msg_data
1234 );
1235
1236 END Get_Outermost_LPN;
1237
1238
1239
1240 -- ----------------------------------------------------------------------------------
1241 -- ----------------------------------------------------------------------------------
1242 PROCEDURE Get_LPN_List
1243 ( p_api_version IN NUMBER ,
1244 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
1245 p_commit IN VARCHAR2 := fnd_api.g_false ,
1246 x_return_status OUT VARCHAR2 ,
1247 x_msg_count OUT NUMBER ,
1248 x_msg_data OUT VARCHAR2 ,
1249 p_lpn_context IN NUMBER := NULL ,
1250 p_content_item_id IN NUMBER := NULL ,
1251 p_max_content_item_qty IN NUMBER := NULL ,
1252 p_organization_id IN NUMBER ,
1253 p_subinventory IN VARCHAR2 := NULL ,
1254 p_locator_id IN NUMBER := NULL ,
1255 p_revision IN VARCHAR2 := NULL ,
1256 p_lot_number IN VARCHAR2 := NULL ,
1257 p_serial_number IN VARCHAR2 := NULL ,
1258 p_container_item_id IN NUMBER := NULL ,
1259 x_lpn_list OUT WMS_CONTAINER_PUB.LPN_Table_Type
1260 )
1261 IS
1262 l_api_name CONSTANT VARCHAR2(30) := 'Get_LPN_List';
1263 l_api_version CONSTANT NUMBER := 1.0;
1264 l_SelectStmt VARCHAR2(500);
1265 TYPE lpn_id_table IS TABLE OF WMS_LICENSE_PLATE_NUMBERS.LPN_ID%TYPE
1266 INDEX BY BINARY_INTEGER;
1267 l_temp_table lpn_id_table;
1268 l_CursorID INTEGER;
1269 l_Dummy INTEGER;
1270 l_temp_record WMS_LICENSE_PLATE_NUMBERS%ROWTYPE;
1271 l_index BINARY_INTEGER := 1;
1272 l_list_index BINARY_INTEGER := 1;
1273 l_temp_lpn NUMBER;
1274 l_org INV_Validate.ORG;
1275 l_result NUMBER;
1276 l_single_item_flag INTEGER;
1277 l_dummy_lpn NUMBER;
1278 CURSOR item_cursor IS
1279 SELECT parent_lpn_id
1280 FROM WMS_LPN_CONTENTS
1281 WHERE parent_lpn_id = l_temp_lpn
1282 AND NVL(serial_summary_entry, 2) = 2;
1283 CURSOR container_cursor IS
1287
1284 SELECT parent_lpn_id
1285 FROM WMS_LICENSE_PLATE_NUMBERS
1286 WHERE parent_lpn_id = l_temp_lpn;
1288 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1289 BEGIN
1290 -- Standard Start of API savepoint
1291 SAVEPOINT Get_LPN_List_PUB;
1292 -- Standard call to check for call compatibility.
1293 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1294 p_api_version ,
1295 l_api_name ,
1296 G_PKG_NAME )
1297 THEN
1298 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1299 FND_MSG_PUB.ADD;
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 END IF;
1302 -- Initialize message list if p_init_msg_list is set to TRUE.
1303 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1304 FND_MSG_PUB.initialize;
1305 END IF;
1306 -- Initialize API return status to success
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 -- API body
1310 /* Validate all inputs */
1311
1312 /* Validate Organization ID */
1313 l_org.organization_id := p_organization_id;
1314 l_result := INV_Validate.Organization(l_org);
1315 IF (l_result = INV_Validate.F) THEN
1316 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ORG');
1317 FND_MSG_PUB.ADD;
1318 RAISE FND_API.G_EXC_ERROR;
1319 END IF;
1320 /* End of input validation */
1321
1322 -- First we need to get all of the LPN's which store the given
1323 -- inventory item. Use DBMS_SQL to do a dynamic query on the
1324 -- WMS_LPN_CONTENTS table to get all the LPN's that store the
1325 -- given item with the specified parameters.
1326
1327 l_SelectStmt := 'SELECT a.lpn_id
1328 FROM WMS_LICENSE_PLATE_NUMBERS a, WMS_LPN_CONTENTS b WHERE ';
1329
1330 IF (p_lpn_context IS NOT NULL) THEN
1331 l_SelectStmt := l_SelectStmt || 'a.lpn_context = ' ||
1332 p_lpn_context || ' AND ';
1333 END IF;
1334 IF (p_content_item_id IS NOT NULL) THEN
1335 l_SelectStmt := l_SelectStmt || 'b.inventory_item_id = ' ||
1336 p_content_item_id || ' AND ';
1337 END IF;
1338 IF (p_max_content_item_qty IS NOT NULL) THEN
1339 l_SelectStmt := l_SelectStmt || 'b.quantity <= ' ||
1340 p_max_content_item_qty || ' AND ';
1341 END IF;
1342 IF (p_organization_id IS NOT NULL) THEN
1343 l_SelectStmt := l_SelectStmt || 'a.organization_id = ' ||
1344 p_organization_id || ' AND ';
1345 END IF;
1346 IF (p_subinventory IS NOT NULL) THEN
1347 l_SelectStmt := l_SelectStmt || 'a.subinventory_code = ' ||
1348 p_subinventory || ' AND ';
1349 END IF;
1350 IF (p_locator_id IS NOT NULL) THEN
1351 l_SelectStmt := l_SelectStmt || 'a.locator_id = ' ||
1352 p_locator_id || ' AND ';
1353 END IF;
1354 IF (p_revision IS NOT NULL) THEN
1355 l_SelectStmt := l_SelectStmt || 'b.revision = ' ||
1356 p_revision || ' AND ';
1357 END IF;
1358 IF (p_lot_number IS NOT NULL) THEN
1359 l_SelectStmt := l_SelectStmt || 'b.lot_number = ' ||
1360 p_lot_number || ' AND ';
1361 END IF;
1362 IF (p_serial_number IS NOT NULL) THEN
1363 l_SelectStmt := l_SelectStmt || 'b.serial_number = ' ||
1364 p_serial_number || ' AND ';
1365 END IF;
1366 IF (p_container_item_id IS NOT NULL) THEN
1367 l_SelectStmt := l_SelectStmt || 'a.inventory_item_id = ' ||
1368 p_container_item_id || ' AND ';
1369 END IF;
1370
1371 -- Finish the WHERE clause with the join condition for the two tables
1372 l_SelectStmt := l_SelectStmt || 'a.lpn_id = b.parent_lpn_id';
1373
1374 -- Open a cursor for processing.
1375 l_CursorID := DBMS_SQL.OPEN_CURSOR;
1376
1377 -- Parse the query
1378 DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, DBMS_SQL.V7);
1379
1380 -- Define the output variables
1381 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, l_temp_lpn);
1382
1383 -- Execute the statement. We don't care about the return value,
1384 -- but we do need to declare a variable for it.
1385 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
1386
1387 -- This is the fetch loop
1388 LOOP
1389 -- Fetch the rows into the buffer, and also check for the exit
1390 -- condition from the loop.
1391 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
1392 EXIT;
1393 END IF;
1394
1395 -- Retrieve the rows from the buffer into a temp variable.
1396 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_temp_lpn);
1397
1398 -- Insert the fetched data in the temp record into the temp table
1399 l_temp_table(l_index) := l_temp_lpn;
1400 l_index := l_index + 1;
1401
1402 END LOOP;
1403
1404 -- Close the cursor.
1405 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
1406 -- Now that we have all the LPN records which satisfy the set of user
1407 -- parameters, we have to sort through these records to see which of them
1408 -- come from homogeneous LPN's.
1409
1410 -- Check that something was retrieved and populated in the temp table.
1411 IF (l_temp_table.COUNT <> 0) THEN
1412 l_index := l_temp_table.FIRST;
1413 LOOP
1414 l_temp_lpn := l_temp_table(l_index);
1415 OPEN item_cursor;
1416 FETCH item_cursor INTO l_dummy_lpn;
1417 IF item_cursor%FOUND THEN
1418 FETCH item_cursor INTO l_dummy_lpn;
1419 IF item_cursor%NOTFOUND THEN
1420 l_single_item_flag := 2; -- Homogeneously packed item-wise
1424 END IF;
1421 ELSE
1422 l_single_item_flag := 1;
1423 END IF;
1425 CLOSE item_cursor;
1426
1427 IF (l_single_item_flag = 2) THEN
1428 OPEN container_cursor;
1429 FETCH container_cursor INTO l_dummy_lpn;
1430 IF container_cursor%NOTFOUND THEN
1431 SELECT *
1432 INTO l_temp_record
1433 FROM WMS_LICENSE_PLATE_NUMBERS
1434 WHERE lpn_id = l_temp_lpn;
1435 x_lpn_list(l_list_index) := l_temp_record;
1436 l_list_index := l_list_index + 1;
1437 END IF;
1438 CLOSE container_cursor;
1439 END IF;
1440
1441 EXIT WHEN l_index = l_temp_table.LAST;
1442 l_index := l_temp_table.NEXT(l_index);
1443 END LOOP;
1444 END IF;
1445 -- End of API body
1446
1447 -- Standard check of p_commit.
1448 IF FND_API.To_Boolean( p_commit ) THEN
1449 COMMIT WORK;
1450 END IF;
1451 -- Standard call to get message count and if count is 1,
1452 -- get message info.
1453 FND_MSG_PUB.Count_And_Get
1454 ( p_count => x_msg_count,
1455 p_data => x_msg_data
1456 );
1457
1458 EXCEPTION
1459 WHEN FND_API.G_EXC_ERROR THEN
1460 ROLLBACK TO Get_LPN_List_PUB;
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 FND_MSG_PUB.Count_And_Get
1463 ( p_count => x_msg_count,
1464 p_data => x_msg_data
1465 );
1466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1467 ROLLBACK TO Get_LPN_List_PUB;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 FND_MSG_PUB.Count_And_Get
1470 ( p_count => x_msg_count,
1471 p_data => x_msg_data
1472 );
1473 WHEN OTHERS THEN
1474 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
1475 ROLLBACK TO Get_LPN_List_PUB;
1476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1477 IF FND_MSG_PUB.Check_Msg_Level
1478 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1479 THEN
1480 FND_MSG_PUB.Add_Exc_Msg
1481 ( G_PKG_NAME ,
1482 l_api_name
1483 );
1484 END IF;
1485 FND_MSG_PUB.Count_And_Get
1486 ( p_count => x_msg_count,
1487 p_data => x_msg_data
1488 );
1489
1490 END Get_LPN_List;
1491
1492
1493
1494 -- ----------------------------------------------------------------------------------
1495 -- ----------------------------------------------------------------------------------
1496 FUNCTION validate_pick_drop_lpn
1497 ( p_api_version_number IN NUMBER ,
1498 p_init_msg_lst IN VARCHAR2 := fnd_api.g_false ,
1499 p_pick_lpn_id IN NUMBER ,
1500 p_organization_id IN NUMBER ,
1501 p_drop_lpn IN VARCHAR2,
1502 p_drop_sub IN VARCHAR2,
1503 p_drop_loc IN NUMBER)
1504 -- Added sub and loc for validation
1505 RETURN NUMBER
1506
1507 IS
1508 l_dummy VARCHAR2(1) := NULL;
1509
1510 l_api_version_number CONSTANT NUMBER := 1.0;
1511 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Pick_Drop_Lpn';
1512 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1513
1514 l_drop_lpn_exists BOOLEAN := FALSE;
1515 l_drop_lpn_has_picked_inv BOOLEAN := FALSE;
1516 l_pick_lpn_delivery_id NUMBER := NULL;
1517 l_drop_lpn_delivery_id NUMBER := NULL;
1518
1519 TYPE lpn_rectype is RECORD
1520 (
1521 lpn_id wms_license_plate_numbers.lpn_id%TYPE,
1522 lpn_context wms_license_plate_numbers.lpn_context%TYPE,
1523 subinventory_code wms_license_plate_numbers.subinventory_code%TYPE,
1524 locator_id wms_license_plate_numbers.locator_id%TYPE
1525 );
1526 drop_lpn_rec lpn_rectype;
1527
1528 CURSOR drop_lpn_cursor IS
1529 SELECT lpn_id,
1530 lpn_context,
1531 subinventory_code,
1532 locator_id
1533 FROM wms_license_plate_numbers
1534 WHERE license_plate_number = p_drop_lpn
1535 AND organization_id = p_organization_id;
1536
1537 CURSOR pick_delivery_cursor IS
1538 SELECT wda.delivery_id
1539 FROM wsh_delivery_assignments wda,
1540 wsh_delivery_details wdd,
1541 mtl_material_transactions_temp temp
1542 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1543 AND wdd.move_order_line_id = temp.move_order_line_id
1544 AND wdd.organization_id = temp.organization_id
1545 AND temp.transfer_lpn_id = p_pick_lpn_id
1546 AND temp.organization_id = p_organization_id ;
1547
1548 CURSOR drop_delivery_cursor(l_lpn_id IN NUMBER) IS
1549 SELECT wda.delivery_id
1550 FROM wsh_delivery_assignments wda,
1551 wsh_delivery_details wdd,
1552 wms_license_plate_numbers lpn
1553 WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
1554 AND wdd.lpn_id = lpn.lpn_id
1555 AND lpn.outermost_lpn_id = l_lpn_id
1556 AND wdd.organization_id = p_organization_id ;
1557
1558 l_delivery_match_flag NUMBER;
1559
1560 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1561 BEGIN
1562
1563 IF (l_debug = 1) THEN
1564 mdebug ('Start Validate_Pick_Drop_Lpn.');
1568 -- Initialize API return status to success
1565 END IF;
1566
1567 --
1569 --
1570 l_return_status := FND_API.G_RET_STS_SUCCESS;
1571 l_delivery_match_flag := -1;
1572
1573 --
1574 -- Begin validation process:
1575 -- Check if drop lpn exists by trying to retrieve
1576 -- its lpn ID. If it does not exist,
1577 -- no further validations required - return success.
1578 --
1579 OPEN drop_lpn_cursor;
1580 FETCH drop_lpn_cursor INTO drop_lpn_rec;
1581 IF drop_lpn_cursor%NOTFOUND THEN
1582 l_drop_lpn_exists := FALSE;
1583 ELSE
1584 l_drop_lpn_exists := TRUE;
1585 END IF;
1586
1587 IF NOT l_drop_lpn_exists THEN
1588 IF (l_debug = 1) THEN
1589 mdebug ('Drop LPN is a new LPN, no checking required.');
1590 END IF;
1591 RETURN 1;
1592 END IF;
1593
1594 --
1595 -- If the drop lpn was pre-generated, no validations required
1596 --
1597
1598 IF drop_lpn_rec.lpn_context =
1599 WMS_Container_PUB.LPN_CONTEXT_PREGENERATED THEN
1600 --
1601 -- Update the context to "Resides in Inventory" (1)
1602 --
1603 /* UPDATE wms_license_plate_numbers
1604 SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
1605 WHERE lpn_id = drop_lpn_rec.lpn_id;*/
1606
1607 IF (l_debug = 1) THEN
1608 mdebug ('Drop LPN is pre-generated, no checking required.');
1609 END IF;
1610 RETURN 1;
1611
1612 ELSIF drop_lpn_rec.lpn_context = WMS_Container_PUB.lpn_context_picked THEN
1613 IF drop_lpn_rec.subinventory_code <> p_drop_sub or
1614 drop_lpn_rec.locator_id <> p_drop_loc THEN
1615 IF (l_debug = 1) THEN
1616 mdebug ('Drop LPN does not belong to the same sub and loc.');
1617 END IF;
1618 RETURN 2; -- Drop LPN resides in another Staging Lane
1619 END IF;
1620 END IF;
1621
1622 IF drop_lpn_rec.lpn_context =
1623 WMS_Container_PUB.LPN_LOADED_FOR_SHIPMENT THEN
1624 IF (l_debug = 1) THEN
1625 mdebug ('Drop LPN is loaded to dock door already');
1626 END IF;
1627 RETURN 4; -- Drop LPN is loaded to dock door already
1628 END IF;
1629
1630 --
1631 -- Drop LPN cannot be the same as the picked LPN
1632 --
1633 IF drop_lpn_rec.lpn_id = p_pick_lpn_id THEN
1634 IF (l_debug = 1) THEN
1635 mdebug ('Drop LPN cannot be the picked LPN.');
1636 END IF;
1637 RETURN 3; -- Drop LPN Cannot be the same as Pick LPN
1638 END IF;
1639
1640
1641 --
1642 -- Now check if the picked LPN and drop LPN
1643 -- belong to different deliveries
1644 --
1645 OPEN pick_delivery_cursor;
1646 LOOP
1647 FETCH pick_delivery_cursor INTO l_pick_lpn_delivery_id;
1648 EXIT WHEN l_pick_lpn_delivery_id IS NOT NULL OR pick_delivery_cursor%NOTFOUND;
1649 END LOOP;
1650 CLOSE pick_delivery_cursor;
1651
1652 --
1653 -- If the picked LPN is not associated with a delivery yet
1654 -- then no further checking required, return success
1655 --
1656 IF l_pick_lpn_delivery_id is NULL THEN
1657 IF (l_debug = 1) THEN
1658 mdebug('Picked LPN is not associated with a delivery, so dont show ANY lpn.');
1659 END IF;
1660 RETURN 0; -- Change here...
1661 END IF;
1662
1663 --
1664 -- Find the drop LPN's delivery ID
1665 --
1666
1667 OPEN drop_delivery_cursor(drop_lpn_rec.lpn_id);
1668 LOOP
1669 FETCH drop_delivery_cursor INTO l_drop_lpn_delivery_id;
1670 EXIT WHEN drop_delivery_cursor%notfound OR l_delivery_match_flag = 0;
1671
1672 IF l_drop_lpn_delivery_id is NOT NULL THEN
1673
1674 IF l_drop_lpn_delivery_id <> l_pick_lpn_delivery_id THEN
1675 IF (l_debug = 1) THEN
1676 mdebug('Picked and drop LPNs are on different deliveries.');
1677 END IF;
1678
1679 l_delivery_match_flag := 0;
1680 ELSE
1681 --
1682 -- Drop LPN and picked LPN are on the same delivery
1683 -- return success
1684 --
1685 IF (l_debug = 1) THEN
1686 mdebug('Drop and pick LPNs are on the same delivery: '||l_drop_lpn_delivery_id);
1687 END IF;
1688
1689 l_delivery_match_flag := 1;
1690 END IF;
1691 END IF;
1692
1693 END LOOP;
1694 CLOSE drop_delivery_cursor;
1695
1696 IF l_delivery_match_flag = 0 OR l_delivery_match_flag = -1 THEN
1697
1698 RETURN 0;
1699
1700 ELSIF l_delivery_match_flag = 1 THEN
1701
1702 RETURN 1;
1703
1704 END IF;
1705
1706 IF l_return_status =FND_API.g_ret_sts_success THEN
1707 RETURN 1;
1708 ELSE
1709 RETURN 0;
1710 END IF;
1711
1712 EXCEPTION
1713 WHEN fnd_api.g_exc_error THEN
1714
1715 RETURN 0;
1716
1717 WHEN OTHERS THEN
1718
1719 RETURN 0;
1720
1721 END validate_pick_drop_lpn;
1722 /*
1723 -- ----------------------------------------------------------------------------------
1724 -- ----------------------------------------------------------------------------------
1725 PROCEDURE validate_pick_drop_lpn
1726 ( p_api_version_number IN NUMBER ,
1727 p_init_msg_lst IN VARCHAR2 := fnd_api.g_false ,
1731 p_pick_lpn_id IN NUMBER ,
1728 x_return_status OUT VARCHAR2 ,
1729 x_msg_count OUT NUMBER ,
1730 x_msg_data OUT VARCHAR2 ,
1732 p_organization_id IN NUMBER ,
1733 p_drop_lpn IN VARCHAR2 )
1734 IS
1735 l_dummy VARCHAR2(1) := NULL;
1736
1737 l_api_version_number CONSTANT NUMBER := 1.0;
1738 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Pick_Drop_Lpn';
1739 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1740
1741 l_drop_lpn_exists BOOLEAN := FALSE;
1742 l_drop_lpn_has_picked_inv BOOLEAN := FALSE;
1743 l_pick_lpn_delivery_id NUMBER := NULL;
1744 l_drop_lpn_delivery_id NUMBER := NULL;
1745
1746 TYPE lpn_rectype is RECORD
1747 (
1748 lpn_id wms_license_plate_numbers.lpn_id%TYPE,
1749 lpn_context wms_license_plate_numbers.lpn_context%TYPE
1750 );
1751 drop_lpn_rec lpn_rectype;
1752
1753 CURSOR drop_lpn_cursor IS
1754 SELECT lpn_id,
1755 lpn_context
1756 FROM wms_license_plate_numbers
1757 WHERE license_plate_number = p_drop_lpn
1758 AND organization_id = p_organization_id;
1759
1760 CURSOR child_lpns_cursor(l_lpn_id IN NUMBER) IS
1761 SELECT lpn_id
1762 FROM WMS_LICENSE_PLATE_NUMBERS
1763 START WITH lpn_id = l_lpn_id
1764 CONNECT BY parent_lpn_id = PRIOR lpn_id;
1765 child_lpns_rec child_lpns_cursor%ROWTYPE;
1766
1767 CURSOR delivery_detail_cursor(l_lpn_id IN NUMBER) IS
1768 SELECT 'x'
1769 FROM dual
1770 WHERE EXISTS (
1771 SELECT 'x'
1772 FROM wsh_delivery_details
1773 WHERE lpn_id = l_lpn_id
1774 AND organization_id = p_organization_id
1775 );
1776
1777 CURSOR pick_delivery_cursor IS
1778 SELECT wda.delivery_id
1779 FROM wsh_delivery_assignments wda,
1780 wsh_delivery_details wdd,
1781 mtl_material_transactions_temp temp
1782 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1783 AND wdd.move_order_line_id = temp.move_order_line_id
1784 AND wdd.organization_id = temp.organization_id
1785 AND temp.transfer_lpn_id = p_pick_lpn_id
1786 AND temp.organization_id = p_organization_id;
1787
1788 CURSOR drop_delivery_cursor(l_lpn_id IN NUMBER) IS
1789 SELECT wda.delivery_id
1790 FROM wsh_delivery_assignments wda,
1791 wsh_delivery_details wdd
1792 WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
1793 AND wdd.lpn_id = l_lpn_id
1794 AND wdd.organization_id = p_organization_id;
1795
1796 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1797 BEGIN
1798
1799 IF (l_debug = 1) THEN
1800 mdebug ('Start Validate_Pick_Drop_Lpn.');
1801 END IF;
1802
1803 --
1804 -- Standard call to check for call compatibility
1805 --
1806 IF NOT fnd_api.compatible_api_call(l_api_version_number
1807 , p_api_version_number
1808 , l_api_name
1809 , G_PKG_NAME
1810 ) THEN
1811 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1812 FND_MSG_PUB.ADD;
1813 RAISE FND_API.G_EXC_ERROR;
1814 END IF;
1815
1816 --
1817 -- Initialize message list.
1818 --
1819 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1820 fnd_msg_pub.initialize;
1821 END IF;
1822
1823 --
1824 -- Initialize API return status to success
1825 --
1826 x_return_status := FND_API.G_RET_STS_SUCCESS;
1827
1828 --
1829 -- Begin validation process:
1830 -- Check if drop lpn exists by trying to retrieve
1831 -- its lpn ID. If it does not exist,
1832 -- no further validations required - return success.
1833 --
1834 OPEN drop_lpn_cursor;
1835 FETCH drop_lpn_cursor INTO drop_lpn_rec;
1836 IF drop_lpn_cursor%NOTFOUND THEN
1837 l_drop_lpn_exists := FALSE;
1838 ELSE
1839 l_drop_lpn_exists := TRUE;
1840 END IF;
1841
1842 IF NOT l_drop_lpn_exists THEN
1843 IF (l_debug = 1) THEN
1844 mdebug ('Drop LPN is a new LPN, no checking required.');
1845 END IF;
1846 return;
1847 END IF;
1848
1849 --
1850 -- If the drop lpn was pre-generated, no validations required
1851 --
1852 IF drop_lpn_rec.lpn_context =
1853 WMS_Container_PUB.LPN_CONTEXT_PREGENERATED THEN
1854 --
1855 -- Update the context to "Resides in Inventory" (1)
1856 --
1857 UPDATE wms_license_plate_numbers
1858 SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
1859 WHERE lpn_id = drop_lpn_rec.lpn_id;
1860 IF (l_debug = 1) THEN
1861 mdebug ('Drop LPN is pre-generated, no checking required.');
1862 END IF;
1863 return;
1864 END IF;
1865
1866 --
1867 -- Drop LPN cannot be the same as the picked LPN
1868 --
1869 IF drop_lpn_rec.lpn_id = p_pick_lpn_id THEN
1873 FND_MESSAGE.SET_NAME('WMS', 'WMS_PICK_LPN_INVLD_DROP_LPN');
1870 IF (l_debug = 1) THEN
1871 mdebug ('Drop LPN cannot be the picked LPN.');
1872 END IF;
1874 FND_MSG_PUB.ADD;
1875 RAISE FND_API.G_EXC_ERROR;
1876 END IF;
1877
1878 --
1879 -- Make sure the drop LPN or one of its child LPNs
1880 -- is associated with delivery detail, i.e., contains
1881 -- picked inventory
1882 --
1883 OPEN delivery_detail_cursor(drop_lpn_rec.lpn_id);
1884 FETCH delivery_detail_cursor INTO l_dummy;
1885 IF delivery_detail_cursor%FOUND THEN
1886 l_drop_lpn_has_picked_inv := TRUE;
1887 ELSE
1888 l_drop_lpn_has_picked_inv := FALSE;
1889 END IF;
1890 CLOSE delivery_detail_cursor;
1891
1892 --
1893 -- Check the child LPNs if the drop LPN is
1894 -- not picked for an order
1895 --
1896 IF NOT l_drop_lpn_has_picked_inv THEN
1897 IF (l_debug = 1) THEN
1898 mdebug('Drop LPN does not have picked inventory, checking child LPNs..');
1899 END IF;
1900 OPEN child_lpns_cursor(drop_lpn_rec.lpn_id);
1901 LOOP
1902 FETCH child_lpns_cursor INTO child_lpns_rec;
1903 EXIT WHEN child_lpns_cursor%NOTFOUND;
1904 IF (l_debug = 1) THEN
1905 mdebug('Trying to fetch a child (drop) LPN.');
1906 END IF;
1907 IF child_lpns_cursor%FOUND THEN
1908 OPEN delivery_detail_cursor(child_lpns_rec.lpn_id);
1909 FETCH delivery_detail_cursor INTO l_dummy;
1910 IF delivery_detail_cursor%FOUND THEN
1911 IF (l_debug = 1) THEN
1912 mdebug('Child LPN '||child_lpns_rec.lpn_id||' has picked inventory.');
1913 END IF;
1914 l_drop_lpn_has_picked_inv := TRUE;
1915 END IF;
1916 CLOSE delivery_detail_cursor;
1917 END IF;
1918 EXIT WHEN l_drop_lpn_has_picked_inv;
1919 END LOOP;
1920 CLOSE child_lpns_cursor;
1921 --
1922 -- If the child LPNs also don't have picked inventory
1923 -- then the user scanned an non-picked LPN so return
1924 -- an error
1925 --
1926 IF NOT l_drop_lpn_has_picked_inv THEN
1927 IF (l_debug = 1) THEN
1928 mdebug('Drop LPN does not have child LPNs with picked inventory.');
1929 END IF;
1930 FND_MESSAGE.SET_NAME('WMS', 'WMS_DROP_LPN_NOT_PICKED');
1931 FND_MSG_PUB.ADD;
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 END IF;
1935
1936 --
1937 -- Now check if the picked LPN and drop LPN
1938 -- belong to different deliveries
1939 --
1940 OPEN pick_delivery_cursor;
1941 LOOP
1942 FETCH pick_delivery_cursor INTO l_pick_lpn_delivery_id;
1943 EXIT WHEN l_pick_lpn_delivery_id IS NOT NULL OR pick_delivery_cursor%NOTFOUND;
1944 END LOOP;
1945 CLOSE pick_delivery_cursor;
1946
1947 --
1948 -- If the picked LPN is not associated with a delivery yet
1949 -- then no further checking required, return success
1950 --
1951 IF l_pick_lpn_delivery_id is NULL THEN
1952 IF (l_debug = 1) THEN
1953 mdebug('Picked LPN is not associated with a delivery, so ok.');
1954 END IF;
1955 return;
1956 END IF;
1957
1958 --
1959 -- Find the drop LPN's delivery ID
1960 --
1961 OPEN drop_delivery_cursor(drop_lpn_rec.lpn_id);
1962 FETCH drop_delivery_cursor INTO l_drop_lpn_delivery_id;
1963 CLOSE drop_delivery_cursor;
1964
1965 IF l_drop_lpn_delivery_id is NOT NULL THEN
1966 IF l_drop_lpn_delivery_id <> l_pick_lpn_delivery_id THEN
1967 IF (l_debug = 1) THEN
1968 mdebug('Picked and drop LPNs are on different deliveries.');
1969 END IF;
1970 FND_MESSAGE.SET_NAME('WMS', 'WMS_DROP_LPN_DIFF_DELIV');
1971 FND_MSG_PUB.ADD;
1972 RAISE FND_API.G_EXC_ERROR;
1973 ELSE
1974 --
1975 -- Drop LPN and picked LPN are on the same delivery
1976 -- return success
1977 --
1978 IF (l_debug = 1) THEN
1979 mdebug('Drop and pick LPNs are on the same delivery: '||l_drop_lpn_delivery_id);
1980 END IF;
1981 return;
1982 END IF;
1983 ELSE
1984 IF (l_debug = 1) THEN
1985 mdebug('Drop LPN does not have a delivery ID, checking child LPNs');
1986 END IF;
1987 OPEN child_lpns_cursor(drop_lpn_rec.lpn_id);
1988 LOOP
1989 FETCH child_lpns_cursor INTO child_lpns_rec;
1990 EXIT WHEN child_lpns_cursor%NOTFOUND;
1991 IF child_lpns_cursor%FOUND THEN
1992 OPEN drop_delivery_cursor(child_lpns_rec.lpn_id);
1993 FETCH drop_delivery_cursor INTO l_drop_lpn_delivery_id;
1994 CLOSE drop_delivery_cursor;
1995 END IF;
1996 EXIT WHEN l_drop_lpn_delivery_id IS NOT NULL;
1997 END LOOP;
1998 CLOSE child_lpns_cursor;
1999
2000 --
2001 -- If the child LPNs also don't have a delivery ID
2002 -- then ok to deposit
2003 --
2004 IF l_drop_lpn_delivery_id is NOT NULL THEN
2005 IF l_drop_lpn_delivery_id <> l_pick_lpn_delivery_id THEN
2006 IF (l_debug = 1) THEN
2007 mdebug('LPNs are on diff deliveries.');
2008 END IF;
2009 FND_MESSAGE.SET_NAME('WMS', 'WMS_DROP_LPN_DIFF_DELIV');
2013 --
2010 FND_MSG_PUB.ADD;
2011 RAISE FND_API.G_EXC_ERROR;
2012 ELSE
2014 -- Drop LPN has a child LPN that is assigned to the
2015 -- same delivery as the picked LPN, return success
2016 --
2017 IF (l_debug = 1) THEN
2018 mdebug('A child LPN is on the same delivery as the picked LPN, return success.');
2019 END IF;
2020 return;
2021 END IF;
2022 ELSE
2023 --
2024 -- No child LPNs on the drop LPN have a delivery ID yet
2025 -- return success
2026 --
2027 IF (l_debug = 1) THEN
2028 mdebug('Child LPNs of the drop LPN do not have a delivery ID either, return success.');
2029 END IF;
2030 return;
2031 END IF;
2032 END IF;
2033
2034 EXCEPTION
2035 WHEN fnd_api.g_exc_error THEN
2036 x_return_status := fnd_api.g_ret_sts_error;
2037
2038 -- Get message count and data
2039 fnd_msg_pub.count_and_get
2040 ( p_count => x_msg_count
2041 , p_data => x_msg_data
2042 );
2043 IF (l_debug = 1) THEN
2044 mdebug ('@'||x_msg_data||'@');
2045 END IF;
2046
2047 WHEN OTHERS THEN
2048 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2049
2050 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2051 THEN
2052 fnd_msg_pub.add_exc_msg
2053 ( g_pkg_name
2054 , l_api_name
2055 );
2056 END IF;
2057
2058 -- Get message count and data
2059 fnd_msg_pub.count_and_get
2060 ( p_count => x_msg_count
2061 , p_data => x_msg_data
2062 );
2063 IF (l_debug = 1) THEN
2064 mdebug ('@'||x_msg_data||'@');
2065 END IF;
2066
2067 END validate_pick_drop_lpn;
2068 */
2069
2070 Procedure default_pick_drop_lpn
2071 ( p_api_version_number IN NUMBER ,
2072 p_init_msg_lst IN VARCHAR2 := fnd_api.g_false ,
2073 p_pick_lpn_id IN NUMBER ,
2074 p_organization_id IN NUMBER ,
2075 x_lpn_number OUT VARCHAR2)
2076
2077 IS
2078
2079 l_api_version_number CONSTANT NUMBER := 1.0;
2080 l_api_name CONSTANT VARCHAR2(30) :=
2081 'default_pick_drop_lpn';
2082 l_return_status VARCHAR2(1) :=
2083 fnd_api.g_ret_sts_success;
2084 l_delivery_id NUMBER;
2085 l_drop_sub VARCHAR2(10);
2086 l_drop_loc NUMBER;
2087 l_lpn_id NUMBER;
2088
2089
2090 CURSOR pick_delivery_cursor IS
2091 SELECT wda.delivery_id
2092 FROM wsh_delivery_assignments wda,
2093 wsh_delivery_details wdd,
2094 mtl_material_transactions_temp temp
2095 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2096 AND wdd.move_order_line_id = temp.move_order_line_id
2097 AND wdd.organization_id = temp.organization_id
2098 AND temp.transfer_lpn_id = p_pick_lpn_id
2099 AND temp.organization_id = p_organization_id;
2100
2101 CURSOR drop_delivery_cursor (l_delivery_id_c IN NUMBER,
2102 l_drop_sub_c IN VARCHAR2,
2103 l_drop_loc_c IN NUMBER ) IS
2104 SELECT wlpn.outermost_lpn_id
2105 FROM wsh_delivery_assignments wda,
2106 wsh_delivery_details wdd,
2107 wms_license_plate_numbers wlpn
2108 WHERE wda.delivery_id = l_delivery_id_c
2109 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
2110 AND wdd.organization_id = p_organization_id
2111 AND wdd.lpn_id = wlpn.lpn_id
2112 AND wlpn.subinventory_code = l_drop_sub_c
2113 AND wlpn.locator_id = l_drop_loc_c
2114 AND wlpn.lpn_context = 11
2115 ORDER BY wda.CREATION_DATE DESC ;
2116
2117
2118 delivery_id_rec pick_delivery_cursor%ROWTYPE;
2119 license_plate_rec drop_delivery_cursor%ROWTYPE;
2120
2121 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2122 BEGIN
2123
2124
2125 IF NOT fnd_api.compatible_api_call (l_api_version_number
2126 , p_api_version_number
2127 , l_api_name
2128 , G_PKG_NAME
2129 ) THEN
2130 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
2131 FND_MSG_PUB.ADD;
2132 RAISE FND_API.G_EXC_ERROR;
2133 END IF;
2134
2135
2136 l_return_status := FND_API.G_RET_STS_SUCCESS;
2137 --
2138 -- Initialize message list.
2139 --
2140
2141 IF fnd_api.to_boolean(p_init_msg_lst) THEN
2142 fnd_msg_pub.initialize;
2143 END IF;
2144
2145
2146 BEGIN
2147 Select transfer_subinventory, transfer_to_location into l_drop_sub,
2148 l_drop_loc
2149 from mtl_material_transactions_temp
2150 where transfer_lpn_id = p_pick_lpn_id
2151 AND organization_id = p_organization_id;
2152
2153 EXCEPTION
2154 WHEN NO_DATA_FOUND THEN
2155 l_delivery_id := NULL;
2156
2157 WHEN OTHERS THEN
2158 l_delivery_id := NULL;
2159 END;
2160
2161 -- Select the Delivery for the LPN that is being picked
2162
2163 FOR delivery_id_rec IN pick_delivery_cursor
2164 LOOP
2165 l_delivery_id := delivery_id_rec.delivery_id;
2166 EXIT WHEN delivery_id_rec.delivery_id IS NOT NULL OR pick_delivery_cursor%NOTFOUND;
2167 END LOOP;
2168
2169
2170 -- Find the drop LPN's delivery ID
2171 FOR license_plate_rec IN drop_delivery_cursor
2172 (l_delivery_id,l_drop_sub,l_drop_loc )
2173 LOOP
2174 l_lpn_id := license_plate_rec.outermost_lpn_id;
2175 EXIT WHEN license_plate_rec.outermost_lpn_id IS NOT NULL OR drop_delivery_cursor%NOTFOUND;
2176 END LOOP;
2177
2178
2179 BEGIN
2180 SELECT license_plate_number INTO x_lpn_number FROM
2181 wms_license_plate_numbers WHERE lpn_id = l_lpn_id;
2182
2183 EXCEPTION
2184 WHEN NO_DATA_FOUND THEN
2185 x_lpn_number := NULL;
2186
2187 WHEN OTHERS THEN
2188 x_lpn_number := NULL;
2189 END;
2190
2191 END default_pick_drop_lpn;
2192
2193 -- End of package
2194 END WMS_Container2_PUB;
[Home] [Help]