eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WMS_CONTAINER_GRP

Source


1 PACKAGE BODY WMS_Container_GRP AS
2 /* $Header: WMSGCNTB.pls 120.5.12020000.2 2012/07/04 06:34:10 abasheer ship $ */
3 
4 --  Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_CONTAINER_GRP';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSGCNTB.pls 120.5.12020000.2 2012/07/04 06:34:10 abasheer ship $';
7 
8 -- Various debug levels
9 G_ERROR     CONSTANT NUMBER := 1;
10 G_INFO      CONSTANT NUMBER := 5;
11 G_MESSAGE   CONSTANT NUMBER := 9;
12 
13 PROCEDURE mdebug(msg IN VARCHAR2, LEVEL NUMBER := G_MESSAGE) IS
14 BEGIN
15   INV_TRX_UTIL_PUB.TRACE(msg, g_pkg_name, LEVEL);
16 END;
17 
18 PROCEDURE Auto_Create_LPNs (
19   p_api_version   IN         NUMBER
20 , p_init_msg_list IN         VARCHAR2
21 , p_commit        IN         VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_msg_count     OUT NOCOPY NUMBER
24 , x_msg_data      OUT NOCOPY VARCHAR2
25 , p_caller        IN         VARCHAR2
26 , p_gen_lpn_rec   IN         WMS_Data_Type_Definitions_PUB.AutoCreateLPNRecordType
27 , p_lpn_table     OUT NOCOPY WMS_Data_Type_Definitions_PUB.LPNTableType
28 ) IS
29 
30   l_debug number;
31   l_api_name VARCHAR2(20);
32 
33   l_msgdata VARCHAR2(1000);
34   l_ucc_128_suffix_flag VARCHAR2(1);
35   l_serial_range WMS_Data_Type_Definitions_PUB.SerialRangeTableType;
36   l_lpn_attr WMS_Data_Type_Definitions_PUB.LPNRecordType;
37 
38 BEGIN
39   l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
40   l_api_name := 'Auto_Create_LPNs';
41 
42   x_return_status := fnd_api.g_ret_sts_success;
43 
44   /* Validate input parameters in p_gen_lpn_rec */
45   /* Required parameters: organization_id, quantity */
46   IF p_gen_lpn_rec.organization_id IS NULL THEN
47       IF (l_debug = 1) THEN
48           mdebug('Organization is required, can not auto create LPN ');
49       END IF;
50       fnd_message.set_name('INV','INV_ORG_REQUIRED');
51       fnd_msg_pub.add;
52       RAISE fnd_api.g_exc_error;
53 
54   ELSIF p_gen_lpn_rec.quantity IS NULL THEN
55       IF (l_debug = 1) THEN
56           mdebug('Quantity is required, can not auto create LPN ');
57       END IF;
58       fnd_message.set_name('WMS','WMS_QUANTITY_REQUIRED');
59       fnd_msg_pub.add;
60       RAISE fnd_api.g_exc_error;
61 
62   END IF;
63 
64   /* Set value for ucc_128_suffix_flag
65      1 - Y
66      2 - N
67   */
68   IF p_gen_lpn_rec.ucc_128_suffix_flag = 1 THEN
69       l_ucc_128_suffix_flag := 'Y';
70   ELSIF p_gen_lpn_rec.ucc_128_suffix_flag = 2 THEN
71       l_ucc_128_suffix_flag := 'N';
72   ELSE
73       /* Invalid value for ucc_128_suffix_flag, set it to N */
74       IF (l_debug = 1) THEN
75           mdebug('Invalid value for ucc_128_suffix_flag '||p_gen_lpn_rec.ucc_128_suffix_flag||', consider it as NULL');
76       END IF;
77       l_ucc_128_suffix_flag := NULL; -- Bug14019072 - Making it as NULL so that we will fetch the value from the organization parameter. 'N';
78   END IF;
79 
80 
81   /* Set value for p_lpn_attributes */
82   l_lpn_attr.organization_id   := p_gen_lpn_rec.organization_id;
83   l_lpn_attr.source_transaction_id := p_gen_lpn_rec.source_transaction_id;
84   l_lpn_attr.subinventory_code := p_gen_lpn_rec.subinventory_code;
85   l_lpn_attr.locator_id        := p_gen_lpn_rec.locator_id;
86   l_lpn_attr.inventory_item_id := p_gen_lpn_rec.container_item_id;
87   l_lpn_attr.lpn_context       := p_gen_lpn_rec.lpn_context;
88 
89   /* Call Auto_Create_LPNs prcedure in private package */
90   WMS_CONTAINER_PVT.Auto_Create_LPNs (
91     p_api_version         =>   p_api_version
92   , p_init_msg_list       =>   p_init_msg_list
93   , p_commit              =>   p_commit
94   , x_return_status       =>   x_return_status
95   , x_msg_count           =>   x_msg_count
96   , x_msg_data            =>   x_msg_data
97   , p_caller              =>   p_caller
98 
99   , p_quantity            =>   p_gen_lpn_rec.quantity
100   , p_lpn_prefix          =>   p_gen_lpn_rec.lpn_prefix
101   , p_lpn_suffix          =>   p_gen_lpn_rec.lpn_suffix
102   , p_starting_number     =>   p_gen_lpn_rec.starting_num
103   , p_total_lpn_length    =>   p_gen_lpn_rec.total_lpn_length
104   , p_ucc_128_suffix_flag =>   l_ucc_128_suffix_flag
105 
106   , p_lpn_attributes      =>   l_lpn_attr
107   , p_serial_ranges       =>   l_serial_range
108   , x_created_lpns        =>   p_lpn_table
109   );
110 
111 EXCEPTION
112   WHEN FND_API.G_EXC_ERROR THEN
113     x_return_status := fnd_api.g_ret_sts_error;
114     FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
115     IF (l_debug = 1) THEN
116       FOR i in 1..x_msg_count LOOP
117         l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
118       END LOOP;
119       mdebug(l_api_name ||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
120       mdebug('msg: '||l_msgdata, G_ERROR);
121     END IF;
122   WHEN OTHERS THEN
123     x_return_status := fnd_api.g_ret_sts_unexp_error;
124     FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
125     IF (l_debug = 1) THEN
126       mdebug(l_api_name ||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
127     END IF;
128 
129 END Auto_Create_LPNs;
130 
131 PROCEDURE Create_LPNs (
132   p_api_version   IN            NUMBER
133 , p_init_msg_list IN            VARCHAR2
134 , p_commit        IN            VARCHAR2
135 , x_return_status OUT    NOCOPY VARCHAR2
136 , x_msg_count     OUT    NOCOPY NUMBER
137 , x_msg_data      OUT    NOCOPY VARCHAR2
138 , p_caller        IN            VARCHAR2
139 , p_lpn_table     IN OUT NOCOPY WMS_Data_Type_Definitions_PUB.LPNTableType
140 ) IS
141 BEGIN
142   x_return_status := fnd_api.g_ret_sts_success;
143 
144   /* Call Create_LPNs prcedure in private package */
145   WMS_CONTAINER_PVT.Create_LPNs (
146     p_api_version    =>   p_api_version
147   , p_init_msg_list  =>   p_init_msg_list
148   , p_commit         =>   p_commit
149   , x_return_status  =>   x_return_status
150   , x_msg_count      =>   x_msg_count
151   , x_msg_data       =>   x_msg_data
152   , p_caller         =>   p_caller
153   , p_lpn_table      =>   p_lpn_table
154   );
155 
156 END Create_LPNs;
157 
158 PROCEDURE Modify_LPNs (
159   p_api_version   IN         NUMBER
160 , p_init_msg_list IN         VARCHAR2
161 , p_commit        IN         VARCHAR2
162 , x_return_status OUT NOCOPY VARCHAR2
163 , x_msg_count     OUT NOCOPY NUMBER
164 , x_msg_data      OUT NOCOPY VARCHAR2
165 , p_caller        IN         VARCHAR2
166 , p_lpn_table     IN         WMS_Data_Type_Definitions_PUB.LPNTableType
167 ) IS
168 BEGIN
169   x_return_status := fnd_api.g_ret_sts_success;
170 
171   /* Call Modify_LPNs prcedure in private package */
172   WMS_CONTAINER_PVT.Modify_LPNs (
173     p_api_version   =>   p_api_version
174   , p_init_msg_list =>   p_init_msg_list
175   , p_commit        =>   p_commit
176   , x_return_status =>   x_return_status
177   , x_msg_count     =>   x_msg_count
178   , x_msg_data      =>   x_msg_data
179   , p_caller        =>   p_caller
180   , p_lpn_table     =>   p_lpn_table
181   );
182 
183 END Modify_LPNs;
184 
185 PROCEDURE LPN_Purge_Actions (
186   p_api_version   IN            NUMBER
187 , p_init_msg_list IN            VARCHAR2
188 , p_commit        IN            VARCHAR2
189 , x_return_status OUT    NOCOPY VARCHAR2
190 , x_msg_count     OUT    NOCOPY NUMBER
191 , x_msg_data      OUT    NOCOPY VARCHAR2
192 , p_caller        IN            VARCHAR2
193 , p_action        IN            VARCHAR2
194 , p_lpn_purge_rec IN OUT NOCOPY WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType
195 ) IS
196 
197   l_debug number;
198   l_api_name VARCHAR2(20);
199   l_valid_lpns WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
200   l_purge_count WMS_DATA_TYPE_DEFINITIONS_PUB.NumberTableType;
201   l_msgdata VARCHAR2(1000);
202 
203 BEGIN
204   l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
205   l_api_name := 'LPN_Purge_Actions';
206 
207   x_return_status := fnd_api.g_ret_sts_success;
208 
209   IF (l_debug = 1) THEN
210     mdebug(l_api_name || ' Entered ' || g_pkg_version);
211     mdebug('ver='||p_api_version||' initmsg='||p_init_msg_list||' commit='||p_commit||' caller='||p_caller);
212     mdebug('p_action='||p_action||' p_lpn_purge_rec.lpn_ids has '||p_lpn_purge_rec.lpn_ids.count||' records');
213   END IF;
214 
215   l_valid_lpns := p_lpn_purge_rec.lpn_ids;
216 
217   IF (p_action = G_LPN_PURGE_ACTION_VALIDATE) THEN
218     -- Validate only
219     -- Call WMS_PURGE_PVT.Check_Purge_LPNs
220     --  with p_lock_flag as 'N'
221     IF (l_debug = 1 ) THEN
222       mdebug('Validate only, Calling WMS_PURGE_PVT.Check_Purge_LPNs with p_lock_flag as N');
223     END IF;
224 
225     WMS_PURGE_PVT.Check_Purge_LPNs(
226       p_api_version     =>  p_api_version
227     , p_init_msg_list   =>  p_init_msg_list
228     , p_commit          =>  p_commit
229     , x_return_status   =>  x_return_status
230     , x_msg_count       =>  x_msg_count
231     , x_msg_data        =>  x_msg_data
232     , p_caller          =>  p_caller
233     , p_lock_flag       =>  'N'
234     , p_lpn_id_table    =>  l_valid_lpns
235     );
236 
237     IF x_return_status = fnd_api.g_ret_sts_success THEN
238       IF (l_debug = 1) THEN
239         mdebug('Number of validated LPNs: '||l_valid_lpns.count);
240       END IF;
241       p_lpn_purge_rec.lpn_ids := l_valid_lpns;
242     ELSE
243       IF (l_debug = 1) THEN
244         mdebug('Error calling Check_Purge_LPNs');
245       END IF;
246     END IF;
247 
248   ELSIF (p_action = G_LPN_PURGE_ACTION_DELETE) THEN
249     -- Validate then delete
250     -- Call WMS_PURGE_PVT.Check_Purge_LPNs
251     --  with p_lock_flag as 'Y'
252     -- Then call WMS_PURGE_PVT.Purge_LPNs to delete LPNs
253     IF (l_debug = 1 ) THEN
254       mdebug('Validate, Calling WMS_PURGE_PVT.Check_Purge_LPNs with p_lock_flag as Y');
255     END IF;
256 
257     WMS_PURGE_PVT.Check_Purge_LPNs(
258       p_api_version     =>  p_api_version
259     , p_init_msg_list   =>  p_init_msg_list
260     , p_commit          =>  p_commit
261     , x_return_status   =>  x_return_status
262     , x_msg_count       =>  x_msg_count
263     , x_msg_data        =>  x_msg_data
264     , p_caller          =>  p_caller
265     , p_lock_flag       =>  'Y'
266     , p_lpn_id_table    =>  l_valid_lpns
267     );
268 
269     IF x_return_status <> fnd_api.g_ret_sts_success THEN
270       IF (l_debug = 1) THEN
271         mdebug('Error calling Check_Purge_LPNs, can not proceed');
272       END IF;
273       RETURN;
274     ELSE
275       IF (l_debug = 1) THEN
276         mdebug('Number of validated LPNs: '||l_valid_lpns.count);
277       END IF;
278       IF (l_valid_lpns.count <> p_lpn_purge_rec.lpn_ids.count) THEN
279         IF (l_debug = 1) THEN
280           mdebug('Validation failed, can not proceed');
281         END IF;
282         fnd_message.set_name('WMS','WMS_LPN_PURGE_VALIDATION');
283         fnd_msg_pub.add;
284         RAISE FND_API.G_EXC_ERROR;
285       ELSE
286         -- Validation passed, call Purge_LPNs to delete LPNs
287         IF (l_debug = 1) THEN
288           mdebug('Validation passed, calling WMS_PURGE_PVT.Purge_LPNs');
289         END IF;
290         WMS_PURGE_PVT.Purge_LPNs(
291           p_api_version     =>  p_api_version
292         , p_init_msg_list   =>  p_init_msg_list
293         , p_commit          =>  p_commit
294         , x_return_status   =>  x_return_status
295         , x_msg_count       =>  x_msg_count
296         , x_msg_data        =>  x_msg_data
297         , p_caller          =>  p_caller
298         , p_lpn_id_table    =>  l_valid_lpns
299         , p_purge_count     =>  l_purge_count
300         );
301        END IF; -- End if of l_valid_lpns.count<>p_lpn_purge_rec.lpn_ids.count
302 
303     END IF; -- End if of x_return_status<>fnd_api.g_ret_sts_success
304 
305   END IF; -- End if of p_action
306 EXCEPTION
307   WHEN FND_API.G_EXC_ERROR THEN
308     x_return_status := fnd_api.g_ret_sts_error;
309     FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
310     IF (l_debug = 1) THEN
311       FOR i in 1..x_msg_count LOOP
312         l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
313       END LOOP;
314       mdebug(l_api_name ||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
315       mdebug('msg: '||l_msgdata, G_ERROR);
316     END IF;
317   WHEN OTHERS THEN
318     x_return_status := fnd_api.g_ret_sts_unexp_error;
319     FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
320     IF (l_debug = 1) THEN
321       mdebug(l_api_name ||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
322     END IF;
323 
324 END LPN_Purge_Actions;
325 
326 END WMS_Container_GRP;