[Home] [Help]
PACKAGE BODY: APPS.WSH_BOLS_UTIL_PKG
Source
1 PACKAGE BODY WSH_BOLS_UTIL_PKG AS
2 /* $Header: WSHBLUTB.pls 120.1.12010000.2 2009/12/03 15:26:40 mvudugul ship $ */
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 -- LSP PROJECT :
220 /*RETURN wsh_util_core.generic_flex_name(p_inventory_item_id,
221 p_organization_id,
222 'INV',
223 'MSTK',
224 101
225 ); */
226
227 RETURN WSH_UTIL_CORE.get_item_name(p_inventory_item_id
228 ,p_organization_id
229 ,'MSTK'
230 ,101,'Y');
231
232 END IF;
233
234 -- :P_ITEM_DISPLAY is 'B'
235 --
236 --
237 -- Debug Statements
238 --
239 IF l_debug_on THEN
240 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GENERIC_FLEX_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
241 END IF;
242 --
243 -- Debug Statements
244 --
245 IF l_debug_on THEN
246 WSH_DEBUG_SV.pop(l_module_name);
247 END IF;
248 --
249 -- Bug# 3306781
250 l_description := p_item_description;
251 IF p_inventory_item_id IS NOT NULL THEN
252 OPEN c_get_item_desc(p_inventory_item_id,p_organization_id);
253 FETCH c_get_item_desc INTO l_description;
254 CLOSE c_get_item_desc;
255 END IF;
256 -- LSP PROJECT:
257 /* RETURN wsh_util_core.generic_flex_name(p_inventory_item_id,
258 p_organization_id,
259 'INV',
260 'MSTK',
261 101) || ' ' || l_description; */
262 RETURN WSH_UTIL_CORE.get_item_name(p_inventory_item_id
263 ,p_organization_id
264 ,'MSTK'
265 ,101,'Y')|| ' ' || l_description;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
270 --
271 -- Debug Statements
272 --
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
275 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
276 END IF;
277 --
278 END encode_desc;
279
280
281 -- purpose: get the container contents information for master
282 -- container
283 -- parameters: p_master_container_id = wsh_delivery_detail_id
284 -- p_output_mode = 'WEB' or 'REPORT'
285 -- p_description_mode = 'D', 'F', or 'B'
286 -- p_organization_id (or warehouse id)
287 -- x_data_item_classification, for the container
288 -- x_container_contents, for the container
289 -- x_hazard_code, for the container
290 -- x_return_status
291
292 PROCEDURE get_master_container_contents
293 (p_master_container_id IN NUMBER,
294 p_output_mode IN VARCHAR2,
295 p_description_mode IN VARCHAR2,
296 p_organization_id IN NUMBER,
297 x_data_item_classification IN OUT NOCOPY VARCHAR2,
298 x_container_contents IN OUT NOCOPY VARCHAR2,
299 x_hazard_code IN OUT NOCOPY VARCHAR2,
300 x_num_of_packages IN OUT NOCOPY NUMBER,
301 x_return_status OUT NOCOPY VARCHAR2
302 )
303 IS
304 CURSOR c_get_container_lpn(x_master_inst_id NUMBER) IS
305 SELECT container_name,
306 container_type_code,
307 item_description,
308 inventory_item_id,
309 gross_weight,
310 weight_uom_code,
311 volume,
312 volume_uom_code
313 FROM wsh_delivery_details
314 WHERE delivery_detail_id = x_master_inst_id
315 AND container_flag = 'Y';
316
317 CURSOR c_get_master_container_items(x_master_inst_id NUMBER) IS
318 SELECT wdd.item_description,
319 wdd.inventory_item_id,
320 wdd.hazard_class_id,
321 wdd.shipped_quantity,
322 wdd.requested_quantity_uom,
323 wdd.classification
324 FROM wsh_delivery_details wdd,
325 wsh_delivery_assignments_v wda
326 WHERE wda.parent_delivery_detail_id = x_master_inst_id
327 AND wda.delivery_detail_id = wdd.delivery_detail_id
328 AND wdd.container_flag = 'N';
329
330 CURSOR c_get_inner_containers(x_container_id NUMBER) IS
331 SELECT wdd.delivery_detail_id
332 FROM wsh_delivery_details wdd,
333 wsh_delivery_assignments_v wda
334 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
335 AND wdd.container_flag = 'Y'
336 AND wda.delivery_assignment_id IN
337 (SELECT wda1.delivery_assignment_id
338 FROM wsh_delivery_assignments_v wda1
339 START WITH wda1.parent_delivery_detail_id = x_container_id
340 CONNECT BY PRIOR wda1.delivery_detail_id = wda1.parent_delivery_detail_id);
341
342 CURSOR c_get_container_items(x_cont_inst_id NUMBER) IS
343 SELECT wdd.item_description,
344 wdd.inventory_item_id,
345 wdd.hazard_class_id,
346 wdd.shipped_quantity,
347 wdd.requested_quantity_uom,
348 wdd.classification
349 FROM wsh_delivery_details wdd,
350 wsh_delivery_assignments_v wda
351 WHERE wda.parent_delivery_detail_id = x_cont_inst_id
352 AND wda.delivery_detail_id = wdd.delivery_detail_id
353 AND wdd.container_flag = 'N';
354
355 CURSOR c_get_sub_containers(x_master_inst_id NUMBER) IS
356 SELECT wdd.delivery_detail_id
357 FROM wsh_delivery_assignments_v wda,
358 wsh_delivery_details wdd
359 WHERE wda.parent_delivery_detail_id = x_master_inst_id
360 AND wda.delivery_detail_id = wdd.delivery_detail_id
361 AND wdd.container_flag = 'Y';
362
363 CURSOR c_get_enclosed_containers(x_cont_id NUMBER) IS
364 SELECT container_name
365 FROM wsh_delivery_details
366 WHERE delivery_detail_id = x_cont_id
367 AND container_flag = 'Y';
368
369 -- Bug# 3306781
370 CURSOR c_get_item_desc (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
371 SELECT description
372 FROM mtl_system_items_vl
373 WHERE organization_id = p_organization_id
374 AND inventory_item_id = p_inventory_item_id;
375
376
377 l_contents_count NUMBER := 0;
378 l_item_description VARCHAR2(250) := NULL;
379 l_hazard_class_id NUMBER := NULL;
380 l_shipped_quantity NUMBER := NULL;
381 l_requested_quantity_uom VARCHAR2(3) := NULL;
382 l_item_class VARCHAR2(30) := NULL;
383
384 l_master_container_lpn VARCHAR2(30) := NULL;
385 l_container_type_code VARCHAR2(30) := NULL;
386 l_container_description VARCHAR2(250) := NULL;
387 l_data_container_gross_weight NUMBER := NULL;
388 l_data_cont_uom_weight_code VARCHAR2(3) := NULL;
389 l_data_container_volume NUMBER := NULL;
390 l_data_cont_uom_volume_code VARCHAR2(3) := NULL;
391
392 l_sub_containers_count NUMBER := 0;
393 l_sub_container_id NUMBER := NULL;
394 l_sub_container_lpn VARCHAR2(50) := NULL;
395 l_sub_contents_count NUMBER := 0;
396 l_data_sub_weight NUMBER := NULL;
397 l_data_sub_weight_uom VARCHAR2(3) := NULL;
398 l_data_sub_volume NUMBER := NULL;
399 l_data_sub_volume_uom VARCHAR2(3) := NULL;
400
401 l_enclosed_count NUMBER := 0;
402 l_check_sub_containers NUMBER := 0;
403 l_enclosed_lpn VARCHAR2(50) := NULL;
404 l_enclosed_list VARCHAR2(2000) := NULL;
405
406 l_contains_label VARCHAR2(100) := NULL;
407 l_of_label VARCHAR2(100) := NULL;
408 l_lpn_label VARCHAR2(100) := NULL;
409 l_encloses_label VARCHAR2(100) := NULL;
410
411 l_item_inventory_item_id NUMBER := NULL;
412 l_container_inventory_item_id NUMBER := NULL;
413
414 -- tmp variable
415 --p_organization_id NUMBER := 207;
416
417 --
418 l_debug_on BOOLEAN;
419 --
420 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MASTER_CONTAINER_CONTENTS';
421 --
422 BEGIN
423
424 -- get the labels
425 --
426 -- Debug Statements
427 --
428 --
429 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
430 --
431 IF l_debug_on IS NULL
432 THEN
433 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
434 END IF;
435 --
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.push(l_module_name);
438 --
439 WSH_DEBUG_SV.log(l_module_name,'P_MASTER_CONTAINER_ID',P_MASTER_CONTAINER_ID);
440 WSH_DEBUG_SV.log(l_module_name,'P_OUTPUT_MODE',P_OUTPUT_MODE);
441 WSH_DEBUG_SV.log(l_module_name,'P_DESCRIPTION_MODE',P_DESCRIPTION_MODE);
442 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
443 WSH_DEBUG_SV.log(l_module_name,'X_DATA_ITEM_CLASSIFICATION',X_DATA_ITEM_CLASSIFICATION);
444 WSH_DEBUG_SV.log(l_module_name,'X_CONTAINER_CONTENTS',X_CONTAINER_CONTENTS);
445 WSH_DEBUG_SV.log(l_module_name,'X_HAZARD_CODE',X_HAZARD_CODE);
446 WSH_DEBUG_SV.log(l_module_name,'X_NUM_OF_PACKAGES',X_NUM_OF_PACKAGES);
447 END IF;
448 --
449 fnd_message.set_name('WSH','WSH_WEB_CONTAINS_LABEL');
450 l_contains_label := fnd_message.get;
451
452 fnd_message.set_name('WSH','WSH_WEB_OF_LABEL');
453 l_of_label := fnd_message.get;
454
455 fnd_message.set_name('WSH','WSH_WEB_LPN_LABEL');
456 l_lpn_label := fnd_message.get;
457
458 fnd_message.set_name('WSH','WSH_WEB_ENCLOSES_LABEL');
459 l_encloses_label := fnd_message.get;
460
461 x_num_of_packages := 0;
462 x_container_contents := NULL;
463
464 OPEN c_get_container_lpn(p_master_container_id);
465 FETCH c_get_container_lpn INTO l_master_container_lpn,
466 l_container_type_code,
467 l_container_description,
468 l_container_inventory_item_id,
469 l_data_container_gross_weight,
470 l_data_cont_uom_weight_code,
471 l_data_container_volume,
472 l_data_cont_uom_volume_code;
473 CLOSE c_get_container_lpn;
474
475 -- Bug# 3306781
476 IF l_container_inventory_item_id IS NOT NULL THEN
477 OPEN c_get_item_desc(l_container_inventory_item_id,p_organization_id);
478 FETCH c_get_item_desc INTO l_container_description;
479 CLOSE c_get_item_desc;
480 END IF;
481
482 OPEN c_get_master_container_items(p_master_container_id);
483 l_contents_count := 0;
484 LOOP
485 FETCH c_get_master_container_items INTO l_item_description,
486 l_item_inventory_item_id,
487 l_hazard_class_id,
488 l_shipped_quantity,
489 l_requested_quantity_uom,
490 l_item_class;
491 IF (c_get_master_container_items%FOUND) THEN
492 IF l_item_class IS NOT NULL THEN
493
494 x_data_item_classification := (x_data_item_classification ||
495 ' ' ||
496 l_item_class);
497
498 x_data_item_classification
499 := concat_eol2(x_data_item_classification, p_output_mode);
500
501 END IF;
502
503 IF (l_contents_count = 0 AND x_container_contents IS NOT NULL) THEN
504 l_container_description := encode_desc(p_description_mode,
505 p_organization_id,
506 l_container_inventory_item_id,
507 l_container_description);
508
509 x_container_contents := (x_container_contents ||
510 l_container_type_code ||
511 ' ' ||
512 l_container_description);
513 END IF;
514
515 l_item_description := encode_desc(p_description_mode,
516 p_organization_id,
517 l_item_inventory_item_id,
518 l_item_description);
519
520 x_container_contents := (x_container_contents ||
521 l_contains_label || ' ' ||
522 l_shipped_quantity ||
523 ' ' ||
524 l_requested_quantity_uom ||
525 ' ' || l_of_label || ' ' ||
526 l_item_description);
527
528 x_container_contents
529 := concat_eol2(x_container_contents, p_output_mode);
530
531 IF l_hazard_class_id IS NOT NULL THEN
532 --Bug 4020301 : If item is hazardous, then hazard_code should
533 --should be 'X' which will get printed in the HM field in
534 --BOL report.
535 -- x_hazard_code := (x_hazard_code || ' ' || l_hazard_class_id);
536
537 x_hazard_code := 'X';
538 END IF;
539
540 l_contents_count := l_contents_count + 1;
541 END IF;
542
543 IF (c_get_master_container_items%NOTFOUND) THEN
544 EXIT;
545 END IF;
546
547 END LOOP;
548 CLOSE c_get_master_container_items;
549
550 IF (l_contents_count > 0) THEN
551 NULL;
552 ELSIF (l_contents_count = 0) THEN
553 NULL;
554 END IF;
555
556 --
557 -- Get any subcontainers of this master container
558 --
559
560 OPEN c_get_inner_containers(p_master_container_id);
561 l_sub_containers_count := 0;
562 LOOP
563 FETCH c_get_inner_containers INTO l_sub_container_id;
564 EXIT WHEN c_get_inner_containers%NOTFOUND;
565 l_sub_containers_count := l_sub_containers_count + 1;
566 --
567 -- a subcontainer exists get the LPN#
568 --
569 OPEN c_get_container_lpn(l_sub_container_id);
570 FETCH c_get_container_lpn INTO l_sub_container_lpn,
571 l_container_type_code,
572 l_container_description,
573 l_container_inventory_item_id,
574 l_data_sub_weight,
575 l_data_sub_weight_uom,
576 l_data_sub_volume,
577 l_data_sub_volume_uom;
578
579 IF (c_get_container_lpn%FOUND) THEN
580 l_container_description := encode_desc(p_description_mode,
581 p_organization_id,
582 l_container_inventory_item_id,
583 l_container_description);
584
585 x_container_contents :=
586 (x_container_contents ||
587 l_lpn_label ||
588 ' ' ||
589 l_sub_container_lpn||' '||
590 l_container_type_code||' '||
591 l_container_description);
592
593 x_container_contents
594 := concat_eol(x_container_contents, p_output_mode);
595
596 --
597 -- Check if there are any items in the sub container
598 --
599 OPEN c_get_container_items(l_sub_container_id);
600 l_sub_contents_count := 0;
601 LOOP
602 FETCH c_get_container_items INTO l_item_description,
603 l_item_inventory_item_id,
604 l_hazard_class_id,
605 l_shipped_quantity,
606 l_requested_quantity_uom,
607 l_item_class;
608
609 IF (c_get_container_items%FOUND) THEN
610 l_item_description := encode_desc(p_description_mode,
611 p_organization_id,
612 l_item_inventory_item_id,
613 l_item_description);
614
615 x_container_contents := (x_container_contents ||
616 --' ' ||
617 l_contains_label ||
618 ' ' ||
619 l_shipped_quantity ||
620 ' ' ||
621 l_requested_quantity_uom ||
622 ' ' ||
623 l_of_label ||
624 ' ' ||
625 l_item_description);
626
627 x_container_contents
628 := concat_eol2(x_container_contents, p_output_mode);
629
630 IF l_item_class IS NOT NULL THEN
631 x_data_item_classification
632 := concat_eol(x_data_item_classification, p_output_mode);
633
634 x_data_item_classification := (x_data_item_classification||
635 ' '||
636 l_item_class);
637
638 x_data_item_classification
639 := concat_eol2(x_data_item_classification, p_output_mode);
640 END IF;
641
642 IF l_hazard_class_id IS NOT NULL THEN
643 --Bug 4020301 : If item is hazardous, then hazard_code should
644 --should be 'X' which will get printed in the HM field in
645 --BOL report.
646 --x_hazard_code := (x_hazard_code || ' ' || l_hazard_class_id);
647
648 x_hazard_code := 'X';
649 END IF;
650
651 l_sub_contents_count := l_sub_contents_count + 1;
652 END IF;
653
654 IF (c_get_container_items%NOTFOUND) THEN
655 EXIT;
656 END IF;
657
658 END LOOP;
659 CLOSE c_get_container_items;
660
661
662 IF (l_sub_contents_count > 0) THEN
663 NULL;
664 ELSIF (l_sub_contents_count = 0) THEN
665 x_container_contents := (x_container_contents
666 ||' NO SUB CONTAINER CONTENTS');
667 NULL;
668 END IF;
669
670 --
671 -- Check if this container contains other containers
672 --
673 OPEN c_get_sub_containers(l_sub_container_id);
674 l_enclosed_count := 0;
675 LOOP
676 FETCH c_get_sub_containers INTO l_check_sub_containers;
677 IF (c_get_sub_containers%FOUND) THEN
678 OPEN c_get_enclosed_containers (l_check_sub_containers);
679 LOOP
680 FETCH c_get_enclosed_containers INTO l_enclosed_lpn;
681 EXIT WHEN c_get_enclosed_containers%NOTFOUND;
682 IF c_get_enclosed_containers%FOUND THEN
683 l_enclosed_count := l_enclosed_count+1;
684 l_enclosed_list := (l_enclosed_list
685 ||' '||l_lpn_label
686 ||' '||l_enclosed_lpn);
687 END IF;
688 END LOOP;
689 CLOSE c_get_enclosed_containers;
690
691 ELSIF (c_get_sub_containers%NOTFOUND) THEN
692 IF (l_enclosed_count > 0) THEN
693 x_container_contents := (x_container_contents||
694 l_lpn_label||' '||
695 l_sub_container_lpn||' '||
696 l_encloses_label||' '||
697 l_enclosed_list);
698
699 x_container_contents
700 := concat_eol2(x_container_contents, p_output_mode);
701
702 l_enclosed_list := NULL;
703
704 x_data_item_classification
705 := concat_eol2(x_data_item_classification, p_output_mode);
706
707 END IF;
708 EXIT;
709 END IF;
710 END LOOP;
711 CLOSE c_get_sub_containers;
712
713 ELSIF (c_get_container_lpn%NOTFOUND) THEN
714 x_container_contents := (x_container_contents
715 ||' NO LPN --- ERROR --- ');
716
717 END IF;
718 CLOSE c_get_container_lpn;
719
720 END LOOP; -- c_get_inner_containers;
721 CLOSE c_get_inner_containers;
722
723 x_num_of_packages := l_enclosed_count + l_sub_containers_count;
724
725 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
726
727 --
728 -- Debug Statements
729 --
730 IF l_debug_on THEN
731 WSH_DEBUG_SV.log(l_module_name,'X_NUM_OF_PACKAGES',x_num_of_packages);
732 WSH_DEBUG_SV.pop(l_module_name);
733 END IF;
734 --
735 EXCEPTION
736 WHEN others THEN
737 FND_MESSAGE.set_name ('WSH','WSH_UNEXP_ERROR');
738 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
739 WSH_UTIL_CORE.add_message (x_return_status);
740 --
741 -- Debug Statements
742 --
743 IF l_debug_on THEN
744 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
745 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
746 END IF;
747 --
748 END get_master_container_contents;
749
750 END WSH_BOLS_UTIL_PKG;
751