PACKAGE BODY: APPS.WMS_SHIPPING_LPN_UTILS_PUB

Source

1 PACKAGE BODY WMS_Shipping_LPN_Utils_PUB AS
2 /* $Header: WMSSHUTB.pls 120.1.12010000.2 2008/08/19 09:56:36 anviswan ship $ */
3 
4 
5 PROCEDURE mydebug(msg in varchar2)
6   IS
7      l_msg VARCHAR2(5100);
8      l_ts VARCHAR2(30);
9     l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 --   select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
12 --   l_msg:=l_ts||'  '||msg;
13 
14    l_msg := msg;
15 
16    inv_mobile_helper_functions.tracelog
17      (p_err_msg => l_msg,
18       p_module => 'WMS_Shipping_LPN_Utils_PUB',
19       p_level => 4);
20 
21 END;
22 
23 
24 PROCEDURE update_lpn_context
25   (  p_delivery_id            IN    NUMBER,
26      x_return_status          OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
27      x_msg_count              OUT NOCOPY /* file.sql.39 change */   NUMBER,
28      x_msg_data               OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
29   IS
30 
31      CURSOR child_lpns_cursor(l_lpn_id IN NUMBER) IS
32 	SELECT wlpn.lpn_id
33 	  FROM wms_license_plate_numbers wlpn
34 	  WHERE wlpn.lpn_context  <> wms_globals.lpn_context_inv
35 	  START WITH wlpn.lpn_id  =  l_lpn_id
36 	  CONNECT BY wlpn.lpn_id  =  PRIOR parent_lpn_id;
37 
38      CURSOR lpns_in_delivery IS
39 	SELECT wdd.lpn_id
40 	  FROM
41 	  wsh_delivery_assignments_v wda,
42 	  wsh_delivery_details wdd
43 	  WHERE wda.delivery_id        = p_delivery_id
44 	  AND   wda.delivery_detail_id = wdd.delivery_detail_id
45 	  AND   wdd.lpn_id IS NOT NULL;
46 
47      l_lpn_id            NUMBER;
48      l_innermost_lpn_id  NUMBER;
49 
50     l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
51 BEGIN
52 
53    IF (l_debug = 1) THEN
54       mydebug('update_lpn_context: Begin update_lpn_context');
55    END IF;
56 
57    OPEN lpns_in_delivery;
58    LOOP
59       FETCH lpns_in_delivery INTO l_innermost_lpn_id;
60       EXIT WHEN lpns_in_delivery%notfound;
61 
62       OPEN child_lpns_cursor(l_innermost_lpn_id);
63       LOOP
64 	 FETCH child_lpns_cursor INTO l_lpn_id;
65 	 EXIT WHEN child_lpns_cursor%notfound;
66 
67 	 UPDATE  wms_license_plate_numbers
68 	   SET   lpn_context = wms_globals.lpn_context_inv
69 	   WHERE lpn_id      = l_lpn_id;
70 
71       END LOOP;
72       CLOSE child_lpns_cursor;
73 
74    END LOOP;
75    CLOSE lpns_in_delivery;
76 
77    IF (l_debug = 1) THEN
78       mydebug('update_lpn_context: End update_lpn_context');
79    END IF;
80 
81 EXCEPTION
82 
83    WHEN FND_API.G_EXC_ERROR THEN
84       x_return_status:=FND_API.G_RET_STS_ERROR;
85 
86       fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
87       fnd_msg_pub.ADD;
88 
89       fnd_msg_pub.count_and_get
90 	(  p_count  => x_msg_count
91            , p_data   => x_msg_data
92 	   );
93 
94       IF (l_debug = 1) THEN
95          mydebug('update_lpn_context: Error in update_lpn_context API: ' || sqlerrm);
96       END IF;
97 
98    WHEN OTHERS THEN
99 
100       x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
101 
102       fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
103       fnd_msg_pub.ADD;
104 
105       fnd_msg_pub.count_and_get
106 	(  p_count  => x_msg_count
107            , p_data   => x_msg_data
108 	   );
109 
110       IF (l_debug = 1) THEN
111          mydebug('update_lpn_context: Unexpected Error in update_lpn_context API: ' || sqlerrm);
112       END IF;
113 
114 END;
115 
116 
117 
118 END WMS_Shipping_LPN_Utils_PUB;