[Home] [Help]
PACKAGE BODY: APPS.WSH_BOLS_UTIL_PKG
Source
1 PACKAGE BODY WSH_BOLS_UTIL_PKG AS
2 /* $Header: WSHBLUTB.pls 120.1 2006/04/10 07:39:44 jnpinto noship $ */
3
4 -- utility function
5 -- purpose: Concatenate string with end of line character, depending
6 -- on the output format. Now supporting HTML and
7 -- Report(PL/SQL).
8
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_BOLS_UTIL_PKG';
11 --
12 FUNCTION concat_eol
13 (p_org_string IN VARCHAR2,
14 p_mode IN VARCHAR2
15 )
16 RETURN VARCHAR2
17 IS
18 --Bugfix 5119785 increased width of variable
19 l_org_string VARCHAR2(32767) := NULL;
20 --
21 l_debug_on BOOLEAN;
22 --
23 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONCAT_EOL';
24 --
25 BEGIN
26 --
27 -- Debug Statements
28 --
29 --
30 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
31 --
32 IF l_debug_on IS NULL
33 THEN
34 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
35 END IF;
36 --
37 IF l_debug_on THEN
38 WSH_DEBUG_SV.push(l_module_name);
39 --
40 WSH_DEBUG_SV.log(l_module_name,'P_ORG_STRING',P_ORG_STRING);
41 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
42 END IF;
43 --
44 IF p_mode = 'WEB' THEN
45 l_org_string := (p_org_string || '<br>');
46 ELSE
47 l_org_string := (p_org_string || fnd_global.local_chr(10));
48 END IF;
49
50 --
51 -- Debug Statements
52 --
53 IF l_debug_on THEN
54 WSH_DEBUG_SV.log(l_module_name,'L_ORG_STRING',l_org_string);
55 WSH_DEBUG_SV.pop(l_module_name);
56 END IF;
57 --
58 RETURN (l_org_string);
59 EXCEPTION
60 WHEN OTHERS THEN
61 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
62 --
63 -- Debug Statements
64 --
65 IF l_debug_on THEN
66 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
67 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
68 END IF;
69 --
70 END concat_eol;
71
72
73 -- utility function
74 -- purpose: Concatenate two end of line characters after
75 -- the string. Now supporting HTML and Report(PL/SQL)
76 -- formats.
77
78 FUNCTION concat_eol2
79 (p_org_string IN VARCHAR2,
80 p_mode IN VARCHAR2
81 )
82 RETURN VARCHAR2
83 IS
84 --Bugfix 5119785 increased width of variable
85 l_org_string VARCHAR2(32767) := NULL;
86 --
87 l_debug_on BOOLEAN;
88 --
89 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CONCAT_EOL2';
90 --
91 BEGIN
92 --
93 -- Debug Statements
94 --
95 --
96 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
97 --
98 IF l_debug_on IS NULL
99 THEN
100 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
101 END IF;
102 --
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.push(l_module_name);
105 --
106 WSH_DEBUG_SV.log(l_module_name,'P_ORG_STRING',P_ORG_STRING);
107 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
108 END IF;
109 --
110 IF p_mode = 'WEB' THEN
111 l_org_string := (p_org_string || '<br><br>');
112 ELSE
113 l_org_string := (p_org_string || fnd_global.local_chr(10) || fnd_global.local_chr(10));
114 END IF;
115
116 --
117 -- Debug Statements
118 --
119 IF l_debug_on THEN
120 WSH_DEBUG_SV.log(l_module_name,'L_ORG_STRING',l_org_string);
121 WSH_DEBUG_SV.pop(l_module_name);
122 END IF;
123 --
124 RETURN (l_org_string);
125 EXCEPTION
126 WHEN OTHERS THEN
127 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
128 --
129 -- Debug Statements
130 --
131 IF l_debug_on THEN
132 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
133 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
134 END IF;
135 --
136 END concat_eol2;
137
138
139 -- utility function
140 -- purpose: get item description
141
142 FUNCTION encode_desc
143 (p_description_mode IN VARCHAR2,
144 p_organization_id IN NUMBER,
145 p_inventory_item_id IN NUMBER,
146 p_item_description IN VARCHAR2
147 )
148 RETURN VARCHAR2
149 IS
150
151 -- Bug# 3306781
152 CURSOR c_get_item_desc (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
153 SELECT description
154 FROM mtl_system_items_vl
155 WHERE organization_id = p_organization_id
156 AND inventory_item_id = p_inventory_item_id;
157
158 l_description VARCHAR2(250) := NULL;
159 --
160 l_debug_on BOOLEAN;
161 --
162 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ENCODE_DESC';
163 --
164 BEGIN
165 --
166 -- Debug Statements
167 --
168 --
169 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
170 --
171 IF l_debug_on IS NULL
172 THEN
173 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
174 END IF;
175 --
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.push(l_module_name);
178 --
179 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION_MODE',P_DESCRIPTION_MODE);
180 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
181 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
182 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_DESCRIPTION',P_ITEM_DESCRIPTION);
183 END IF;
184 --
185 IF p_description_mode = 'D' THEN
186 --
187 -- Debug Statements
188 --
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.pop(l_module_name);
191 END IF;
192 -- Bug# 3306781
193 l_description := p_item_description;
194 IF p_inventory_item_id IS NOT NULL THEN
195 OPEN c_get_item_desc(p_inventory_item_id,p_organization_id);
196 FETCH c_get_item_desc INTO l_description;
197 CLOSE c_get_item_desc;
198 END IF;
199
200 --
201 RETURN l_description;
202 END IF;
203
204 IF p_description_mode = 'F' THEN
205 --
206 --
207 -- Debug Statements
208 --
209 IF l_debug_on THEN
210 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GENERIC_FLEX_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
211 END IF;
212 --
213 -- Debug Statements
214 --
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.pop(l_module_name);
217 END IF;
218 --
219 RETURN wsh_util_core.generic_flex_name(p_inventory_item_id,
220 p_organization_id,
221 'INV',
222 'MSTK',
223 101
224 );
225 END IF;
226
227 -- :P_ITEM_DISPLAY is 'B'
228 --
229 --
230 -- Debug Statements
231 --
232 IF l_debug_on THEN
233 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GENERIC_FLEX_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
234 END IF;
235 --
236 -- Debug Statements
237 --
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.pop(l_module_name);
240 END IF;
241 --
242 -- Bug# 3306781
243 l_description := p_item_description;
244 IF p_inventory_item_id IS NOT NULL THEN
245 OPEN c_get_item_desc(p_inventory_item_id,p_organization_id);
246 FETCH c_get_item_desc INTO l_description;
247 CLOSE c_get_item_desc;
248 END IF;
249
250 RETURN wsh_util_core.generic_flex_name(p_inventory_item_id,
251 p_organization_id,
252 'INV',
253 'MSTK',
254 101) || ' ' || l_description;
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
259 --
260 -- Debug Statements
261 --
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
264 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
265 END IF;
266 --
267 END encode_desc;
268
269
270 -- purpose: get the container contents information for master
271 -- container
272 -- parameters: p_master_container_id = wsh_delivery_detail_id
273 -- p_output_mode = 'WEB' or 'REPORT'
274 -- p_description_mode = 'D', 'F', or 'B'
275 -- p_organization_id (or warehouse id)
276 -- x_data_item_classification, for the container
277 -- x_container_contents, for the container
278 -- x_hazard_code, for the container
279 -- x_return_status
280
281 PROCEDURE get_master_container_contents
282 (p_master_container_id IN NUMBER,
283 p_output_mode IN VARCHAR2,
284 p_description_mode IN VARCHAR2,
285 p_organization_id IN NUMBER,
286 x_data_item_classification IN OUT NOCOPY VARCHAR2,
287 x_container_contents IN OUT NOCOPY VARCHAR2,
288 x_hazard_code IN OUT NOCOPY VARCHAR2,
289 x_num_of_packages IN OUT NOCOPY NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2
291 )
292 IS
293 CURSOR c_get_container_lpn(x_master_inst_id NUMBER) IS
294 SELECT container_name,
295 container_type_code,
296 item_description,
297 inventory_item_id,
298 gross_weight,
299 weight_uom_code,
300 volume,
301 volume_uom_code
302 FROM wsh_delivery_details
303 WHERE delivery_detail_id = x_master_inst_id
304 AND container_flag = 'Y';
305
306 CURSOR c_get_master_container_items(x_master_inst_id NUMBER) IS
307 SELECT wdd.item_description,
308 wdd.inventory_item_id,
309 wdd.hazard_class_id,
310 wdd.shipped_quantity,
311 wdd.requested_quantity_uom,
312 wdd.classification
313 FROM wsh_delivery_details wdd,
314 wsh_delivery_assignments_v wda
315 WHERE wda.parent_delivery_detail_id = x_master_inst_id
316 AND wda.delivery_detail_id = wdd.delivery_detail_id
317 AND wdd.container_flag = 'N';
318
319 CURSOR c_get_inner_containers(x_container_id NUMBER) IS
320 SELECT wdd.delivery_detail_id
321 FROM wsh_delivery_details wdd,
322 wsh_delivery_assignments_v wda
323 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
324 AND wdd.container_flag = 'Y'
325 AND wda.delivery_assignment_id IN
326 (SELECT wda1.delivery_assignment_id
327 FROM wsh_delivery_assignments_v wda1
328 START WITH wda1.parent_delivery_detail_id = x_container_id
329 CONNECT BY PRIOR wda1.delivery_detail_id = wda1.parent_delivery_detail_id);
330
331 CURSOR c_get_container_items(x_cont_inst_id NUMBER) IS
332 SELECT wdd.item_description,
333 wdd.inventory_item_id,
334 wdd.hazard_class_id,
335 wdd.shipped_quantity,
336 wdd.requested_quantity_uom,
337 wdd.classification
338 FROM wsh_delivery_details wdd,
339 wsh_delivery_assignments_v wda
340 WHERE wda.parent_delivery_detail_id = x_cont_inst_id
341 AND wda.delivery_detail_id = wdd.delivery_detail_id
342 AND wdd.container_flag = 'N';
343
344 CURSOR c_get_sub_containers(x_master_inst_id NUMBER) IS
345 SELECT wdd.delivery_detail_id
346 FROM wsh_delivery_assignments_v wda,
347 wsh_delivery_details wdd
348 WHERE wda.parent_delivery_detail_id = x_master_inst_id
349 AND wda.delivery_detail_id = wdd.delivery_detail_id
350 AND wdd.container_flag = 'Y';
351
352 CURSOR c_get_enclosed_containers(x_cont_id NUMBER) IS
353 SELECT container_name
354 FROM wsh_delivery_details
355 WHERE delivery_detail_id = x_cont_id
356 AND container_flag = 'Y';
357
358 -- Bug# 3306781
359 CURSOR c_get_item_desc (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
360 SELECT description
361 FROM mtl_system_items_vl
362 WHERE organization_id = p_organization_id
363 AND inventory_item_id = p_inventory_item_id;
364
365
366 l_contents_count NUMBER := 0;
367 l_item_description VARCHAR2(250) := NULL;
368 l_hazard_class_id NUMBER := NULL;
369 l_shipped_quantity NUMBER := NULL;
370 l_requested_quantity_uom VARCHAR2(3) := NULL;
371 l_item_class VARCHAR2(30) := NULL;
372
373 l_master_container_lpn VARCHAR2(30) := NULL;
374 l_container_type_code VARCHAR2(30) := NULL;
375 l_container_description VARCHAR2(250) := NULL;
376 l_data_container_gross_weight NUMBER := NULL;
377 l_data_cont_uom_weight_code VARCHAR2(3) := NULL;
378 l_data_container_volume NUMBER := NULL;
379 l_data_cont_uom_volume_code VARCHAR2(3) := NULL;
380
381 l_sub_containers_count NUMBER := 0;
382 l_sub_container_id NUMBER := NULL;
383 l_sub_container_lpn VARCHAR2(50) := NULL;
384 l_sub_contents_count NUMBER := 0;
385 l_data_sub_weight NUMBER := NULL;
386 l_data_sub_weight_uom VARCHAR2(3) := NULL;
387 l_data_sub_volume NUMBER := NULL;
388 l_data_sub_volume_uom VARCHAR2(3) := NULL;
389
390 l_enclosed_count NUMBER := 0;
391 l_check_sub_containers NUMBER := 0;
392 l_enclosed_lpn VARCHAR2(50) := NULL;
393 l_enclosed_list VARCHAR2(2000) := NULL;
394
395 l_contains_label VARCHAR2(100) := NULL;
396 l_of_label VARCHAR2(100) := NULL;
397 l_lpn_label VARCHAR2(100) := NULL;
398 l_encloses_label VARCHAR2(100) := NULL;
399
400 l_item_inventory_item_id NUMBER := NULL;
401 l_container_inventory_item_id NUMBER := NULL;
402
403 -- tmp variable
407 l_debug_on BOOLEAN;
404 --p_organization_id NUMBER := 207;
405
406 --
408 --
409 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MASTER_CONTAINER_CONTENTS';
410 --
411 BEGIN
412
413 -- get the labels
414 --
415 -- Debug Statements
416 --
417 --
418 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
419 --
420 IF l_debug_on IS NULL
421 THEN
422 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
423 END IF;
424 --
425 IF l_debug_on THEN
426 WSH_DEBUG_SV.push(l_module_name);
427 --
428 WSH_DEBUG_SV.log(l_module_name,'P_MASTER_CONTAINER_ID',P_MASTER_CONTAINER_ID);
429 WSH_DEBUG_SV.log(l_module_name,'P_OUTPUT_MODE',P_OUTPUT_MODE);
430 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION_MODE',P_DESCRIPTION_MODE);
431 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
432 WSH_DEBUG_SV.log(l_module_name,'X_DATA_ITEM_CLASSIFICATION',X_DATA_ITEM_CLASSIFICATION);
433 WSH_DEBUG_SV.log(l_module_name,'X_CONTAINER_CONTENTS',X_CONTAINER_CONTENTS);
434 WSH_DEBUG_SV.log(l_module_name,'X_HAZARD_CODE',X_HAZARD_CODE);
435 WSH_DEBUG_SV.log(l_module_name,'X_NUM_OF_PACKAGES',X_NUM_OF_PACKAGES);
436 END IF;
437 --
438 fnd_message.set_name('WSH','WSH_WEB_CONTAINS_LABEL');
439 l_contains_label := fnd_message.get;
440
441 fnd_message.set_name('WSH','WSH_WEB_OF_LABEL');
442 l_of_label := fnd_message.get;
443
444 fnd_message.set_name('WSH','WSH_WEB_LPN_LABEL');
445 l_lpn_label := fnd_message.get;
446
447 fnd_message.set_name('WSH','WSH_WEB_ENCLOSES_LABEL');
448 l_encloses_label := fnd_message.get;
449
450 x_num_of_packages := 0;
451 x_container_contents := NULL;
452
453 OPEN c_get_container_lpn(p_master_container_id);
454 FETCH c_get_container_lpn INTO l_master_container_lpn,
455 l_container_type_code,
456 l_container_description,
457 l_container_inventory_item_id,
458 l_data_container_gross_weight,
459 l_data_cont_uom_weight_code,
460 l_data_container_volume,
461 l_data_cont_uom_volume_code;
462 CLOSE c_get_container_lpn;
463
464 -- Bug# 3306781
465 IF l_container_inventory_item_id IS NOT NULL THEN
466 OPEN c_get_item_desc(l_container_inventory_item_id,p_organization_id);
467 FETCH c_get_item_desc INTO l_container_description;
468 CLOSE c_get_item_desc;
469 END IF;
470
471 OPEN c_get_master_container_items(p_master_container_id);
472 l_contents_count := 0;
473 LOOP
474 FETCH c_get_master_container_items INTO l_item_description,
475 l_item_inventory_item_id,
476 l_hazard_class_id,
477 l_shipped_quantity,
478 l_requested_quantity_uom,
479 l_item_class;
480 IF (c_get_master_container_items%FOUND) THEN
481 IF l_item_class IS NOT NULL THEN
482
483 x_data_item_classification := (x_data_item_classification ||
484 ' ' ||
485 l_item_class);
486
487 x_data_item_classification
488 := concat_eol2(x_data_item_classification, p_output_mode);
489
490 END IF;
491
492 IF (l_contents_count = 0 AND x_container_contents IS NOT NULL) THEN
493 l_container_description := encode_desc(p_description_mode,
494 p_organization_id,
495 l_container_inventory_item_id,
496 l_container_description);
497
498 x_container_contents := (x_container_contents ||
499 l_container_type_code ||
500 ' ' ||
501 l_container_description);
502 END IF;
503
504 l_item_description := encode_desc(p_description_mode,
505 p_organization_id,
506 l_item_inventory_item_id,
507 l_item_description);
508
509 x_container_contents := (x_container_contents ||
510 l_contains_label || ' ' ||
511 l_shipped_quantity ||
512 ' ' ||
513 l_requested_quantity_uom ||
514 ' ' || l_of_label || ' ' ||
515 l_item_description);
516
517 x_container_contents
518 := concat_eol2(x_container_contents, p_output_mode);
519
520 IF l_hazard_class_id IS NOT NULL THEN
521 --Bug 4020301 : If item is hazardous, then hazard_code should
522 --should be 'X' which will get printed in the HM field in
523 --BOL report.
524 -- x_hazard_code := (x_hazard_code || ' ' || l_hazard_class_id);
525
526 x_hazard_code := 'X';
527 END IF;
528
529 l_contents_count := l_contents_count + 1;
530 END IF;
531
532 IF (c_get_master_container_items%NOTFOUND) THEN
533 EXIT;
534 END IF;
535
536 END LOOP;
537 CLOSE c_get_master_container_items;
538
539 IF (l_contents_count > 0) THEN
540 NULL;
541 ELSIF (l_contents_count = 0) THEN
542 NULL;
543 END IF;
544
545 --
546 -- Get any subcontainers of this master container
547 --
548
549 OPEN c_get_inner_containers(p_master_container_id);
550 l_sub_containers_count := 0;
551 LOOP
555 --
552 FETCH c_get_inner_containers INTO l_sub_container_id;
553 EXIT WHEN c_get_inner_containers%NOTFOUND;
554 l_sub_containers_count := l_sub_containers_count + 1;
556 -- a subcontainer exists get the LPN#
557 --
558 OPEN c_get_container_lpn(l_sub_container_id);
559 FETCH c_get_container_lpn INTO l_sub_container_lpn,
560 l_container_type_code,
561 l_container_description,
562 l_container_inventory_item_id,
563 l_data_sub_weight,
564 l_data_sub_weight_uom,
565 l_data_sub_volume,
566 l_data_sub_volume_uom;
567
568 IF (c_get_container_lpn%FOUND) THEN
569 l_container_description := encode_desc(p_description_mode,
570 p_organization_id,
571 l_container_inventory_item_id,
572 l_container_description);
573
574 x_container_contents :=
575 (x_container_contents ||
576 l_lpn_label ||
577 ' ' ||
578 l_sub_container_lpn||' '||
579 l_container_type_code||' '||
580 l_container_description);
581
582 x_container_contents
583 := concat_eol(x_container_contents, p_output_mode);
584
585 --
586 -- Check if there are any items in the sub container
587 --
588 OPEN c_get_container_items(l_sub_container_id);
589 l_sub_contents_count := 0;
590 LOOP
591 FETCH c_get_container_items INTO l_item_description,
592 l_item_inventory_item_id,
593 l_hazard_class_id,
594 l_shipped_quantity,
595 l_requested_quantity_uom,
596 l_item_class;
597
598 IF (c_get_container_items%FOUND) THEN
599 l_item_description := encode_desc(p_description_mode,
600 p_organization_id,
601 l_item_inventory_item_id,
602 l_item_description);
603
604 x_container_contents := (x_container_contents ||
605 --' ' ||
606 l_contains_label ||
607 ' ' ||
608 l_shipped_quantity ||
609 ' ' ||
610 l_requested_quantity_uom ||
611 ' ' ||
612 l_of_label ||
613 ' ' ||
614 l_item_description);
615
616 x_container_contents
617 := concat_eol2(x_container_contents, p_output_mode);
618
619 IF l_item_class IS NOT NULL THEN
620 x_data_item_classification
621 := concat_eol(x_data_item_classification, p_output_mode);
622
623 x_data_item_classification := (x_data_item_classification||
624 ' '||
625 l_item_class);
626
627 x_data_item_classification
628 := concat_eol2(x_data_item_classification, p_output_mode);
629 END IF;
630
631 IF l_hazard_class_id IS NOT NULL THEN
632 --Bug 4020301 : If item is hazardous, then hazard_code should
633 --should be 'X' which will get printed in the HM field in
634 --BOL report.
635 --x_hazard_code := (x_hazard_code || ' ' || l_hazard_class_id);
636
637 x_hazard_code := 'X';
638 END IF;
639
640 l_sub_contents_count := l_sub_contents_count + 1;
641 END IF;
642
643 IF (c_get_container_items%NOTFOUND) THEN
644 EXIT;
645 END IF;
646
647 END LOOP;
648 CLOSE c_get_container_items;
649
650
651 IF (l_sub_contents_count > 0) THEN
652 NULL;
653 ELSIF (l_sub_contents_count = 0) THEN
654 x_container_contents := (x_container_contents
655 ||' NO SUB CONTAINER CONTENTS');
656 NULL;
657 END IF;
658
659 --
660 -- Check if this container contains other containers
661 --
662 OPEN c_get_sub_containers(l_sub_container_id);
663 l_enclosed_count := 0;
664 LOOP
665 FETCH c_get_sub_containers INTO l_check_sub_containers;
666 IF (c_get_sub_containers%FOUND) THEN
667 OPEN c_get_enclosed_containers (l_check_sub_containers);
668 LOOP
669 FETCH c_get_enclosed_containers INTO l_enclosed_lpn;
670 EXIT WHEN c_get_enclosed_containers%NOTFOUND;
671 IF c_get_enclosed_containers%FOUND THEN
672 l_enclosed_count := l_enclosed_count+1;
673 l_enclosed_list := (l_enclosed_list
674 ||' '||l_lpn_label
675 ||' '||l_enclosed_lpn);
676 END IF;
677 END LOOP;
678 CLOSE c_get_enclosed_containers;
679
680 ELSIF (c_get_sub_containers%NOTFOUND) THEN
681 IF (l_enclosed_count > 0) THEN
682 x_container_contents := (x_container_contents||
683 l_lpn_label||' '||
684 l_sub_container_lpn||' '||
685 l_encloses_label||' '||
686 l_enclosed_list);
687
688 x_container_contents
689 := concat_eol2(x_container_contents, p_output_mode);
690
691 l_enclosed_list := NULL;
692
693 x_data_item_classification
694 := concat_eol2(x_data_item_classification, p_output_mode);
695
696 END IF;
697 EXIT;
698 END IF;
699 END LOOP;
700 CLOSE c_get_sub_containers;
701
702 ELSIF (c_get_container_lpn%NOTFOUND) THEN
703 x_container_contents := (x_container_contents
704 ||' NO LPN --- ERROR --- ');
705
706 END IF;
707 CLOSE c_get_container_lpn;
708
709 END LOOP; -- c_get_inner_containers;
710 CLOSE c_get_inner_containers;
711
712 x_num_of_packages := l_enclosed_count + l_sub_containers_count;
713
714 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
715
716 --
717 -- Debug Statements
718 --
719 IF l_debug_on THEN
720 WSH_DEBUG_SV.log(l_module_name,'X_NUM_OF_PACKAGES',x_num_of_packages);
721 WSH_DEBUG_SV.pop(l_module_name);
722 END IF;
723 --
724 EXCEPTION
725 WHEN others THEN
726 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
727 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
728 WSH_UTIL_CORE.add_message (x_return_status);
729 --
730 -- Debug Statements
731 --
732 IF l_debug_on THEN
733 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
734 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
735 END IF;
736 --
737 END get_master_container_contents;
738
739 END WSH_BOLS_UTIL_PKG;
740