[Home] [Help]
PACKAGE BODY: APPS.WMS_PICK_LOAD_UI
Source
1 PACKAGE BODY wms_pick_load_ui AS
2 /* $Header: WMSPLUIB.pls 120.4.12010000.4 2008/09/11 10:51:57 ssrikaku ship $ */
3
4 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
5
6 PROCEDURE debug(p_message VARCHAR2,
7 p_module VARCHAR2 DEFAULT 'Pick Load UI') IS
8 BEGIN
9 inv_log_util.trace(p_message, p_module, 9);
10 END debug;
11
12 PROCEDURE validate_subinventory(p_organization_id IN NUMBER,
13 p_item_id IN NUMBER,
14 p_subinventory_code IN VARCHAR2,
15 p_restrict_subinventories_code IN NUMBER,
16 p_transaction_type_id IN NUMBER,
17 x_is_valid_subinventory OUT nocopy VARCHAR2,
18 x_is_lpn_controlled OUT nocopy VARCHAR2,
19 x_message OUT nocopy VARCHAR2)
20
21 IS
22
23 TYPE sub_record_type IS RECORD
24 (subinventory_code mtl_secondary_inventories.secondary_inventory_name%TYPE,
25 locator_type mtl_secondary_inventories.locator_type%TYPE,
26 description mtl_secondary_inventories.description%TYPE,
27 asset_inventory mtl_secondary_inventories.asset_inventory%TYPE,
28 lpn_controlled_flag mtl_secondary_inventories.lpn_controlled_flag%TYPE,
29 subinventory_type mtl_secondary_inventories.subinventory_type%TYPE,
30 reservable_type mtl_secondary_inventories.reservable_type%TYPE,
31 enable_alias mtl_secondary_inventories.enable_locator_alias%TYPE);
32
33 l_sub_rec sub_record_type;
34 l_subinventories t_genref;
35
36 BEGIN
37 x_is_valid_subinventory := 'N';
38
39 inv_ui_item_sub_loc_lovs.get_sub_lov_rcv
40 (x_sub => l_subinventories,
41 p_organization_id => p_organization_id,
42 p_item_id => p_item_id,
43 p_sub => p_subinventory_code,
44 p_restrict_subinventories_code => p_restrict_subinventories_code,
45 p_transaction_type_id => p_transaction_type_id,
46 p_wms_installed => 'Y',
47 p_location_id => NULL,
48 p_lpn_context => 1,
49 p_putaway_code => 1);
50
51
52 LOOP
53 FETCH l_subinventories INTO l_sub_rec;
54 EXIT WHEN l_subinventories%notfound;
55
56
57 IF l_sub_rec.subinventory_code = p_subinventory_code THEN
58 x_is_valid_subinventory := 'Y';
59
60 IF l_sub_rec.lpn_controlled_flag = 1 THEN
61 x_is_lpn_controlled := 'Y';
62 ELSE
63 x_is_lpn_controlled := 'N';
64 END IF;
65
66 EXIT;
67 END IF;
68
69 END LOOP;
70
71 IF x_is_valid_subinventory = 'N' THEN
72 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
73 fnd_msg_pub.add;
74
75 inv_mobile_helper_functions.get_stacked_messages(x_message => x_message);
76 END IF;
77
78 END validate_subinventory;
79
80 PROCEDURE validate_locator_lpn
81 (p_organization_id IN NUMBER,
82 p_restrict_locators_code IN NUMBER,
83 p_inventory_item_id IN NUMBER,
84 p_revision IN VARCHAR2,
85 p_locator_lpn IN VARCHAR2,
86 p_subinventory_code IN VARCHAR2,
87 p_transaction_temp_id IN NUMBER,
88 p_transaction_type_id IN NUMBER,
89 p_project_id IN NUMBER,
90 p_task_id IN NUMBER,
91 p_allocated_lpn IN VARCHAR2,
92 p_suggested_loc IN VARCHAR2,
93 p_suggested_loc_id IN NUMBER,
94 p_suggested_sub IN VARCHAR2,
95 p_serial_allocated IN VARCHAR2,
96 p_allow_locator_change IN VARCHAR2,
97 p_is_loc_or_lpn IN VARCHAR2,
98 x_is_valid_locator OUT nocopy VARCHAR2,
99 x_is_valid_lpn OUT nocopy VARCHAR2,
100 x_subinventory_code OUT nocopy VARCHAR2,
101 x_locator OUT nocopy VARCHAR2,
102 x_locator_id OUT nocopy NUMBER,
103 x_lpn_id OUT nocopy NUMBER,
104 x_is_lpn_controlled OUT nocopy VARCHAR2,
105 x_return_status OUT nocopy VARCHAR2,
106 x_msg_count OUT nocopy NUMBER,
107 x_msg_data OUT nocopy VARCHAR2)
108 IS
109
110 TYPE loc_record_type IS RECORD
111 (locator_id NUMBER,
112 locator VARCHAR2(204),
113 description VARCHAR2(50));
114
115 TYPE loc_sub_record_type IS RECORD
116 (locator_id NUMBER,
117 locator VARCHAR2(204),
118 description VARCHAR2(50),
119 subinventory VARCHAR2(10));
120
121 TYPE lpn_record_type IS RECORD
122 (license_plate_number VARCHAR2(30),
123 lpn_id NUMBER,
124 inventory_item_id NUMBER,
125 organization_id NUMBER,
126 revision VARCHAR2(3),
127 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
128 lot_number VARCHAR2(80),
129 serial_number VARCHAR2(30),
130 subinventory_code VARCHAR2(10),
131 locator_id NUMBER,
132 parent_lpn_id NUMBER,
133 sealed_status NUMBER,
134 gross_weight_uom_code VARCHAR2(3),
135 gross_weight NUMBER,
136 content_volume_uom_code VARCHAR2(3),
137 content_volume NUMBER,
138 concatenated_segments VARCHAR2(204),
139 lpn_context NUMBER);
140
141 l_loc_rec loc_record_type;
142 l_loc_sub_rec loc_sub_record_type;
143 l_lpn_rec lpn_record_type;
144 l_locators t_genref;
145 l_lpns t_genref;
146 l_project_id NUMBER := p_project_id;
147 l_task_id NUMBER := p_task_id;
148
149 l_alias_enabled VARCHAR2(1); -- Bug 7225845
150 l_locator_lpn VARCHAR2(100); -- Bug 7225845
151
152 BEGIN
153 x_return_status := 'S';
154 x_is_valid_locator := 'N';
155 x_is_valid_lpn := 'N';
156 x_subinventory_code := p_subinventory_code;
157
158 IF l_project_id = 0 THEN
159 l_project_id := NULL;
160 END IF;
161
162 IF l_task_id = 0 THEN
163 l_task_id := NULL;
164 END IF;
165 debug('Validating Loc/LPN with following params');
166 debug('p_allow_locator_change ==> '||p_allow_locator_change);
167 debug('p_is_loc_or_lpn ==> '||p_is_loc_or_lpn);
168 -- If p_is_loc_or_lpn, only then check if the entered value is
169 -- an LPN
170 IF p_is_loc_or_lpn = 'EITHER' THEN
171
172 IF (g_debug = 1) THEN
173 debug('Check if entered value is an LPN', 'wms_pick_load_ui.validate_locator');
174 END IF;
175 IF p_allow_locator_change = 'N' THEN
176 IF p_serial_allocated = 'Y' THEN
177 wms_lpn_lovs.get_pick_load_serial_lpn_lov
178 (x_lpn_lov => l_lpns,
179 p_lpn => p_locator_lpn,
180 p_organization_id => p_organization_id,
181 p_revision => p_revision,
182 p_inventory_item_id => p_inventory_item_id,
183 p_cost_group_id => 0,
184 p_subinventory_code => p_subinventory_code,
185 p_locator_id => p_suggested_loc_id,
186 p_transaction_temp_id => p_transaction_temp_id);
187 ELSE
188 wms_lpn_lovs.get_pick_load_lpn_lov
189 (x_lpn_lov => l_lpns,
190 p_lpn => p_locator_lpn,
191 p_organization_id => p_organization_id,
192 p_revision => p_revision,
193 p_inventory_item_id => p_inventory_item_id,
194 p_cost_group_id => 0,
195 p_subinventory_code => p_subinventory_code,
196 p_locator_id => p_suggested_loc_id,
197 p_project_id => l_project_id,
198 p_task_id => l_task_id);
199 END IF;
200 ELSIF p_allow_locator_change = 'P' THEN
201 IF p_serial_allocated = 'Y' THEN
202 wms_lpn_lovs.get_sub_apl_serial_lpn_lov
203 (x_lpn_lov => l_lpns,
204 p_lpn => p_locator_lpn,
205 p_organization_id => p_organization_id,
206 p_revision => p_revision,
207 p_inventory_item_id => p_inventory_item_id,
208 p_subinventory_code => p_subinventory_code,
209 p_transaction_temp_id => p_transaction_temp_id);
210 ELSE
211 wms_lpn_lovs.get_sub_apl_lpn_lov
212 (x_lpn_lov => l_lpns,
213 p_lpn => p_locator_lpn,
214 p_organization_id => p_organization_id,
215 p_revision => p_revision,
216 p_inventory_item_id => p_inventory_item_id,
217 p_subinventory_code => p_subinventory_code,
218 p_project_id => l_project_id,
219 p_task_id => l_task_id);
220 END IF;
221
222 ELSIF p_allow_locator_change = 'C' THEN
223 IF p_serial_allocated = 'Y' THEN
224 wms_lpn_lovs.get_all_apl_serial_lpn_lov
225 (x_lpn_lov => l_lpns,
226 p_lpn => p_locator_lpn,
227 p_organization_id => p_organization_id,
228 p_revision => p_revision,
229 p_inventory_item_id => p_inventory_item_id,
230 p_transaction_temp_id => p_transaction_temp_id);
231 ELSE
232 wms_lpn_lovs.get_all_apl_lpn_lov
233 (x_lpn_lov => l_lpns,
234 p_lpn => p_locator_lpn,
235 p_organization_id => p_organization_id,
236 p_revision => p_revision,
237 p_inventory_item_id => p_inventory_item_id,
238 p_project_id => l_project_id,
239 p_task_id => l_task_id);
240 END IF;
241 END IF;
242
243
244
245
246 LOOP
247 FETCH l_lpns INTO l_lpn_rec;
248 EXIT WHEN l_lpns%notfound;
249
250 IF l_lpn_rec.license_plate_number = p_locator_lpn THEN
251 x_is_valid_lpn := 'Y';
252 x_lpn_id := l_lpn_rec.lpn_id;
253 x_is_lpn_controlled := 'Y';
254 x_locator_id := l_lpn_rec.locator_id;
255 x_subinventory_code := l_lpn_rec.subinventory_code;
256 x_locator := INV_PROJECT.GET_LOCSEGS(l_lpn_rec.concatenated_segments);
257 EXIT;
258 END IF;
259
260 END LOOP;
261 END IF;
262
263 -- If locator change is not allowed, error out if the entered LPN is
264 -- from a different locator
265 IF x_locator_id IS NOT NULL AND x_locator_id <> p_suggested_loc_id THEN
266 IF p_allow_locator_change = 'N' THEN
267 IF (g_debug = 1) THEN
268 debug('Locator change is not allowed', 'wms_pick_load_ui.validate_locator');
269 END IF;
270 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
271 fnd_msg_pub.add;
272 RAISE fnd_api.g_exc_error;
273 ELSIF p_allow_locator_change IN ('P', 'C') THEN
274 return;
275 END IF;
276 END IF;
277
278 -- for locator alias bug.
279
280 l_locator_lpn := p_locator_lpn;
281
282 IF x_is_valid_lpn <> 'Y' THEN
283
284 BEGIN
285
286 select ENABLE_LOCATOR_ALIAS INTO l_alias_enabled
287 from MTL_SECONDARY_INVENTORIES
288 where SECONDARY_INVENTORY_NAME = p_subinventory_code
289 and ORGANIZATION_ID = p_organization_id;
290
291 IF (Nvl(l_alias_enabled, 'N') = 'Y') THEN
292
293 select concatenated_segments INTO l_locator_lpn
294 from wms_item_locations_kfv
295 where alias =p_locator_lpn
296 and SUBINVENTORY_CODE = p_subinventory_code
297 and ORGANIZATION_ID = p_organization_id;
298
299
300 END IF;
301
302 EXCEPTION
303 WHEN No_Data_Found THEN
304 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
305 fnd_msg_pub.add;
306 RAISE fnd_api.g_exc_error;
307
308 WHEN OTHERS THEN
309 debug('other exceptions raised');
310 END;
311
312
313 END IF;
314
315 -- locator alias bug
316
317
318 -- If Loc Change = No or Partail, check for the entered Loc in the Sub confirmed
319 IF p_allow_locator_change IN ('N', 'P') THEN
320
321 IF (g_debug = 1) THEN
322 debug('Validating Loc with Loc Change = N (No), P (Partial - Diff loc from the same sub is valid)', 'wms_pick_load_ui.validate_locator');
323 END IF;
324
325 /* Bug 4990550 changing the call to the newly added procedure 'get_pickload_loc' in inv_ui_item_sub_loc_lovs since the locator is no longer an LOV from 11510*/
326 inv_ui_item_sub_loc_lovs.get_pickload_loc
327 (x_locators => l_locators,
328 p_organization_id => p_organization_id,
329 p_subinventory_code => p_subinventory_code,
330 p_restrict_locators_code => p_restrict_locators_code,
331 p_inventory_item_id => p_inventory_item_id,
332 p_concatenated_segments => l_locator_lpn||'%', -- Bug 7225845
333 -- p_concatenated_segments => p_locator_lpn||'%', -- Bug 7225845
334 p_transaction_type_id => p_transaction_type_id,
335 p_wms_installed => 'Y',
336 p_project_id => l_project_id,
337 p_task_id => l_task_id);
338
339 LOOP
340 FETCH l_locators INTO l_loc_rec;
341 EXIT WHEN l_locators%notfound;
342 debug('l_loc_rec.locator : '||l_loc_rec.locator);
343 debug('INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) : '||INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator));
344 -- IF l_loc_rec.locator = p_locator_lpn OR p_locator_lpn = INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) THEN -- Bug 7225845
345 IF l_loc_rec.locator = l_locator_lpn OR l_locator_lpn = INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) THEN -- Bug 7225845
346 x_is_valid_locator := 'Y';
347 x_locator_id := l_loc_rec.locator_id;
348 x_subinventory_code := p_subinventory_code;
349 x_locator := INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator);
350 EXIT;
351 END IF;
352
353 END LOOP;
354 IF p_allow_locator_change = 'N' THEN
355 -- If locator entered is not found, or is different from the sug loc
356 -- then raise exception
357 IF (x_locator_id IS NULL OR x_locator_id <> p_suggested_loc_id) THEN
358 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
359 fnd_msg_pub.add;
360 RAISE fnd_api.g_exc_error;
361 END IF;
362 ELSIF p_allow_locator_change = 'P' THEN
363 -- If locator entered is not found, or is in different sub then raise exception
364 IF (x_locator_id IS NULL OR x_subinventory_code <> p_subinventory_code) THEN
365 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
366 fnd_msg_pub.add;
367 RAISE fnd_api.g_exc_error;
368 END IF;
369 END IF;
370 -- If Loc Change = Complete, check for the entered Loc in any sub
371 -- (filtering on sub not required)
372 ELSIF p_allow_locator_change = 'C' THEN
373 IF (g_debug = 1) THEN
374 debug('Validating Loc with Loc Change = C (Loc from even a diff sub is valid)', 'wms_pick_load_ui.validate_locator');
375 END IF;
376
377 inv_ui_item_sub_loc_lovs.get_pickload_all_loc_lov
378 (x_locators => l_locators,
379 p_organization_id => p_organization_id,
380 p_restrict_locators_code => p_restrict_locators_code,
381 p_inventory_item_id => p_inventory_item_id,
382 p_concatenated_segments => l_locator_lpn||'%', -- Bug 7225845
383 -- p_concatenated_segments => p_locator_lpn||'%', -- Bug 7225845
384 p_transaction_type_id => p_transaction_type_id,
385 p_wms_installed => 'Y',
386 p_project_id => l_project_id,
387 p_task_id => l_task_id);
388
389 LOOP
390 FETCH l_locators INTO l_loc_sub_rec;
391 EXIT WHEN l_locators%notfound;
392
393 -- IF INV_PROJECT.GET_LOCSEGS(l_loc_sub_rec.locator) = p_locator_lpn OR p_locator_lpn = l_loc_sub_rec.locator THEN -- Bug 7225845
394 IF INV_PROJECT.GET_LOCSEGS(l_loc_sub_rec.locator) = l_locator_lpn OR l_locator_lpn = l_loc_sub_rec.locator THEN -- Bug 7225845
395 x_is_valid_locator := 'Y';
396 x_locator_id := l_loc_sub_rec.locator_id;
397 x_subinventory_code := l_loc_sub_rec.subinventory;
398 x_locator := INV_PROJECT.GET_LOCSEGS(l_loc_sub_rec.locator);
399 EXIT;
400 END IF;
401
402 END LOOP;
403 -- If locator entered is not found, or is different from the sug loc
404 -- then raise exception
405 IF (x_locator_id IS NULL) THEN
406 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
407 fnd_msg_pub.add;
408 RAISE fnd_api.g_exc_error;
409 END IF;
410 END IF;
411
412 -- If the subinventory is now different from the suggested subinventory,
413 -- get the new lpn controlled value
414 IF x_subinventory_code <> p_suggested_sub THEN
415
416 IF (g_debug = 1) THEN
417 debug('Check if subinventory is LPN controlled', 'wms_pick_load_ui.validate_locator');
418 END IF;
419
420 SELECT Decode(lpn_controlled_flag, 1, 'Y', 'N')
421 INTO x_is_lpn_controlled
422 FROM mtl_secondary_inventories
423 WHERE organization_id = p_organization_id
424 AND secondary_inventory_name = x_subinventory_code;
425 END IF;
426
427 EXCEPTION
428 WHEN fnd_api.g_exc_error THEN
429
430 IF (g_debug = 1) THEN
431 debug('Error', 'wms_pick_load_ui.validate_locator');
432 END IF;
433
434 x_return_status := 'E';
435
436 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
437
438 WHEN OTHERS THEN
439 IF (g_debug = 1) THEN
440 debug('Unexpected Error: ' || Sqlerrm, 'wms_pick_load_ui.validate_locator');
441 END IF;
442
443 x_return_status := 'U';
444
445 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
446
447 END validate_locator_lpn;
448
449 END wms_pick_load_ui;