[Home] [Help]
PACKAGE BODY: APPS.WMS_PICK_LOAD_UI
Source
1 PACKAGE BODY wms_pick_load_ui AS
2 /* $Header: WMSPLUIB.pls 120.8 2011/02/21 10:28:30 ssikhara 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 p_confirm_locator_hidden IN VARCHAR2, --10435590
99 x_is_valid_locator OUT nocopy VARCHAR2,
100 x_is_valid_lpn OUT nocopy VARCHAR2,
101 x_subinventory_code OUT nocopy VARCHAR2,
102 x_locator OUT nocopy VARCHAR2,
103 x_locator_id OUT nocopy NUMBER,
104 x_lpn_id OUT nocopy NUMBER,
105 x_is_lpn_controlled OUT nocopy VARCHAR2,
106 x_return_status OUT nocopy VARCHAR2,
107 x_msg_count OUT nocopy NUMBER,
108 x_msg_data OUT nocopy VARCHAR2)
109 IS
110
111 TYPE loc_record_type IS RECORD
112 (locator_id NUMBER,
113 locator VARCHAR2(204),
114 description VARCHAR2(50));
115
116 TYPE loc_sub_record_type IS RECORD
117 (locator_id NUMBER,
118 locator VARCHAR2(204),
119 description VARCHAR2(50),
120 subinventory VARCHAR2(10));
121
122 TYPE lpn_record_type IS RECORD
123 (license_plate_number VARCHAR2(30),
124 lpn_id NUMBER,
125 inventory_item_id NUMBER,
126 organization_id NUMBER,
127 revision VARCHAR2(3),
128 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
129 lot_number VARCHAR2(80),
130 serial_number VARCHAR2(30),
131 subinventory_code VARCHAR2(10),
132 locator_id NUMBER,
133 parent_lpn_id NUMBER,
134 sealed_status NUMBER,
135 gross_weight_uom_code VARCHAR2(3),
136 gross_weight NUMBER,
137 content_volume_uom_code VARCHAR2(3),
138 content_volume NUMBER,
139 concatenated_segments VARCHAR2(204),
140 lpn_context NUMBER);
141
142 l_loc_rec loc_record_type;
143 l_loc_sub_rec loc_sub_record_type;
144 l_lpn_rec lpn_record_type;
145 l_locators t_genref;
146 l_lpns t_genref;
147 l_project_id NUMBER := p_project_id;
148 l_task_id NUMBER := p_task_id;
149
150 l_alias_enabled VARCHAR2(1); -- Bug 7225845
151 l_locator_lpn VARCHAR2(100); -- Bug 7225845
152
153 BEGIN
154 x_return_status := 'S';
155 x_is_valid_locator := 'N';
156 x_is_valid_lpn := 'N';
157 x_subinventory_code := p_subinventory_code;
158
159 IF l_project_id = 0 THEN
160 l_project_id := NULL;
161 END IF;
162
163 IF l_task_id = 0 THEN
164 l_task_id := NULL;
165 END IF;
166 debug('Validating Loc/LPN with following params');
167 debug('p_allow_locator_change ==> '||p_allow_locator_change);
168 debug('p_is_loc_or_lpn ==> '||p_is_loc_or_lpn);
169 -- If p_is_loc_or_lpn, only then check if the entered value is
170 -- an LPN
171 IF p_is_loc_or_lpn = 'EITHER' THEN
172
173 IF (g_debug = 1) THEN
174 debug('Check if entered value is an LPN', 'wms_pick_load_ui.validate_locator');
175 END IF;
176 IF p_allow_locator_change = 'N' THEN
177 IF p_serial_allocated = 'Y' THEN
178 wms_lpn_lovs.get_pick_load_serial_lpn_lov
179 (x_lpn_lov => l_lpns,
180 p_lpn => p_locator_lpn,
181 p_organization_id => p_organization_id,
182 p_revision => p_revision,
183 p_inventory_item_id => p_inventory_item_id,
184 p_cost_group_id => 0,
185 p_subinventory_code => p_subinventory_code,
186 p_locator_id => p_suggested_loc_id,
187 p_transaction_temp_id => p_transaction_temp_id);
188 ELSE
189 wms_lpn_lovs.get_pick_load_lpn_lov
190 (x_lpn_lov => l_lpns,
191 p_lpn => p_locator_lpn,
192 p_organization_id => p_organization_id,
193 p_revision => p_revision,
194 p_inventory_item_id => p_inventory_item_id,
195 p_cost_group_id => 0,
196 p_subinventory_code => p_subinventory_code,
197 p_locator_id => p_suggested_loc_id,
198 p_project_id => l_project_id,
199 p_task_id => l_task_id);
200 END IF;
201 ELSIF p_allow_locator_change = 'P' THEN
202 IF p_serial_allocated = 'Y' THEN
203 wms_lpn_lovs.get_sub_apl_serial_lpn_lov
204 (x_lpn_lov => l_lpns,
205 p_lpn => p_locator_lpn,
206 p_organization_id => p_organization_id,
207 p_revision => p_revision,
208 p_inventory_item_id => p_inventory_item_id,
209 p_subinventory_code => p_subinventory_code,
210 p_transaction_temp_id => p_transaction_temp_id);
211 ELSE
212 wms_lpn_lovs.get_sub_apl_lpn_lov
213 (x_lpn_lov => l_lpns,
214 p_lpn => p_locator_lpn,
215 p_organization_id => p_organization_id,
216 p_revision => p_revision,
217 p_inventory_item_id => p_inventory_item_id,
218 p_subinventory_code => p_subinventory_code,
219 p_project_id => l_project_id,
220 p_task_id => l_task_id);
221 END IF;
222
223 ELSIF p_allow_locator_change = 'C' THEN
224 IF p_serial_allocated = 'Y' THEN
225 wms_lpn_lovs.get_all_apl_serial_lpn_lov
226 (x_lpn_lov => l_lpns,
227 p_lpn => p_locator_lpn,
228 p_organization_id => p_organization_id,
229 p_revision => p_revision,
230 p_inventory_item_id => p_inventory_item_id,
231 p_transaction_temp_id => p_transaction_temp_id);
232 ELSE
233 wms_lpn_lovs.get_all_apl_lpn_lov
234 (x_lpn_lov => l_lpns,
235 p_lpn => p_locator_lpn,
236 p_organization_id => p_organization_id,
237 p_revision => p_revision,
238 p_inventory_item_id => p_inventory_item_id,
239 p_project_id => l_project_id,
240 p_task_id => l_task_id);
241 END IF;
242 END IF;
243
244
245
246
247 LOOP
248 FETCH l_lpns INTO l_lpn_rec;
249 EXIT WHEN l_lpns%notfound;
250
251 IF l_lpn_rec.license_plate_number = p_locator_lpn THEN
252 x_is_valid_lpn := 'Y';
253 x_lpn_id := l_lpn_rec.lpn_id;
254 x_is_lpn_controlled := 'Y';
255 x_locator_id := l_lpn_rec.locator_id;
256 x_subinventory_code := l_lpn_rec.subinventory_code;
257 x_locator := INV_PROJECT.GET_LOCSEGS(l_lpn_rec.concatenated_segments);
258 EXIT;
259 END IF;
260
261 END LOOP;
262 END IF;
263
264 -- If locator change is not allowed, error out if the entered LPN is
265 -- from a different locator
266 IF x_locator_id IS NOT NULL AND x_locator_id <> p_suggested_loc_id THEN
267 IF p_allow_locator_change = 'N' THEN
268 IF (g_debug = 1) THEN
269 debug('Locator change is not allowed', 'wms_pick_load_ui.validate_locator');
270 END IF;
271 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
272 fnd_msg_pub.add;
273 RAISE fnd_api.g_exc_error;
274 ELSIF p_allow_locator_change IN ('P', 'C') THEN
275 return;
276 END IF;
277 END IF;
278
279 -- for locator alias bug.
280
281 l_locator_lpn := p_locator_lpn;
282
283 IF x_is_valid_lpn <> 'Y' THEN
284
285 BEGIN
286
287 select ENABLE_LOCATOR_ALIAS INTO l_alias_enabled
288 from MTL_SECONDARY_INVENTORIES
289 where SECONDARY_INVENTORY_NAME = p_subinventory_code
290 and ORGANIZATION_ID = p_organization_id;
291
292 IF (Nvl(l_alias_enabled, 'N') = 'Y') THEN
293 IF (p_confirm_locator_hidden = 'Y') THEN
294 IF (g_debug = 1) THEN
295 debug('130', 'wms_pick_load_ui.validate_locator_lpn.p_confirm_locator_hidden:' || p_confirm_locator_hidden);
296 END IF;
297 select concatenated_segments INTO l_locator_lpn
298 from wms_item_locations_kfv
299 where concatenated_segments = p_locator_lpn
300 and SUBINVENTORY_CODE = p_subinventory_code
301 and ORGANIZATION_ID = p_organization_id;
302 ELSE
303 IF (g_debug = 1) THEN
304 debug('131', 'wms_pick_load_ui.validate_locator_lpn.p_confirm_locator_hidden:' || p_confirm_locator_hidden);
305 END IF;
306 select concatenated_segments INTO l_locator_lpn
307 from wms_item_locations_kfv
308 where alias = p_locator_lpn
309 and SUBINVENTORY_CODE = p_subinventory_code
310 and ORGANIZATION_ID = p_organization_id;
311 END IF;
312 END IF;
313
314 EXCEPTION
315 WHEN No_Data_Found THEN
316 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
317 fnd_msg_pub.add;
318 RAISE fnd_api.g_exc_error;
319
320 WHEN OTHERS THEN
321 debug('other exceptions raised');
322 END;
323
324
325 END IF;
326
327 -- locator alias bug
328
329
330 -- If Loc Change = No or Partail, check for the entered Loc in the Sub confirmed
331 IF p_allow_locator_change IN ('N', 'P') THEN
332
333 IF (g_debug = 1) THEN
334 debug('Validating Loc with Loc Change = N (No), P (Partial - Diff loc from the same sub is valid)', 'wms_pick_load_ui.validate_locator');
335 END IF;
336
337 /* 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*/
338 inv_ui_item_sub_loc_lovs.get_pickload_loc
339 (x_locators => l_locators,
340 p_organization_id => p_organization_id,
341 p_subinventory_code => p_subinventory_code,
342 p_restrict_locators_code => p_restrict_locators_code,
343 p_inventory_item_id => p_inventory_item_id,
344 p_concatenated_segments => l_locator_lpn||'%', -- Bug 7225845
345 -- p_concatenated_segments => p_locator_lpn||'%', -- Bug 7225845
346 p_transaction_type_id => p_transaction_type_id,
347 p_wms_installed => 'Y',
348 p_project_id => l_project_id,
349 p_task_id => l_task_id);
350
351 LOOP
352 FETCH l_locators INTO l_loc_rec;
353 EXIT WHEN l_locators%notfound;
354 debug('l_loc_rec.locator : '||l_loc_rec.locator);
355 debug('INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) : '||INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator));
356 -- IF l_loc_rec.locator = p_locator_lpn OR p_locator_lpn = INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) THEN -- Bug 7225845
357 IF l_loc_rec.locator = l_locator_lpn OR l_locator_lpn = INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator) THEN -- Bug 7225845
358 x_is_valid_locator := 'Y';
359 x_locator_id := l_loc_rec.locator_id;
360 x_subinventory_code := p_subinventory_code;
361 x_locator := INV_PROJECT.GET_LOCSEGS(l_loc_rec.locator);
362 EXIT;
363 END IF;
364
365 END LOOP;
366 IF p_allow_locator_change = 'N' THEN
367 -- If locator entered is not found, or is different from the sug loc
368 -- then raise exception
369 IF (x_locator_id IS NULL OR x_locator_id <> p_suggested_loc_id) THEN
370 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
371 fnd_msg_pub.add;
372 RAISE fnd_api.g_exc_error;
373 END IF;
374 ELSIF p_allow_locator_change = 'P' THEN
375 -- If locator entered is not found, or is in different sub then raise exception
376 IF (x_locator_id IS NULL OR x_subinventory_code <> p_subinventory_code) THEN
377 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
378 fnd_msg_pub.add;
379 RAISE fnd_api.g_exc_error;
380 END IF;
381 END IF;
382 -- If Loc Change = Complete, check for the entered Loc in any sub
383 -- (filtering on sub not required)
384 ELSIF p_allow_locator_change = 'C' THEN
385 IF (g_debug = 1) THEN
386 debug('Validating Loc with Loc Change = C (Loc from even a diff sub is valid)', 'wms_pick_load_ui.validate_locator');
387 END IF;
388
389 inv_ui_item_sub_loc_lovs.get_pickload_all_loc_lov
390 (x_locators => l_locators,
391 p_organization_id => p_organization_id,
392 p_restrict_locators_code => p_restrict_locators_code,
393 p_inventory_item_id => p_inventory_item_id,
394 p_concatenated_segments => l_locator_lpn||'%', -- Bug 7225845
395 -- p_concatenated_segments => p_locator_lpn||'%', -- Bug 7225845
396 p_transaction_type_id => p_transaction_type_id,
397 p_wms_installed => 'Y',
398 p_project_id => l_project_id,
399 p_task_id => l_task_id);
400
401 LOOP
402 FETCH l_locators INTO l_loc_sub_rec;
403 EXIT WHEN l_locators%notfound;
404
405 -- 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
406 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
407 x_is_valid_locator := 'Y';
408 x_locator_id := l_loc_sub_rec.locator_id;
409 x_subinventory_code := l_loc_sub_rec.subinventory;
410 x_locator := INV_PROJECT.GET_LOCSEGS(l_loc_sub_rec.locator);
411 EXIT;
412 END IF;
413
414 END LOOP;
415 -- If locator entered is not found, or is different from the sug loc
416 -- then raise exception
417 IF (x_locator_id IS NULL) THEN
418 fnd_message.set_name('WMS', 'WMS_INVALID_VALUE');
419 fnd_msg_pub.add;
420 RAISE fnd_api.g_exc_error;
421 END IF;
422 END IF;
423
424 -- If the subinventory is now different from the suggested subinventory,
425 -- get the new lpn controlled value
426 IF x_subinventory_code <> p_suggested_sub THEN
427
428 IF (g_debug = 1) THEN
429 debug('Check if subinventory is LPN controlled', 'wms_pick_load_ui.validate_locator');
430 END IF;
431
432 SELECT Decode(lpn_controlled_flag, 1, 'Y', 'N')
433 INTO x_is_lpn_controlled
434 FROM mtl_secondary_inventories
435 WHERE organization_id = p_organization_id
436 AND secondary_inventory_name = x_subinventory_code;
437 END IF;
438
439 EXCEPTION
440 WHEN fnd_api.g_exc_error THEN
441
442 IF (g_debug = 1) THEN
443 debug('Error', 'wms_pick_load_ui.validate_locator');
444 END IF;
445
446 x_return_status := 'E';
447
448 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
449
450 WHEN OTHERS THEN
451 IF (g_debug = 1) THEN
452 debug('Unexpected Error: ' || Sqlerrm, 'wms_pick_load_ui.validate_locator');
453 END IF;
454
455 x_return_status := 'U';
456
457 inv_mobile_helper_functions.get_stacked_messages(x_message => x_msg_data);
458
459 END validate_locator_lpn;
460
461 END wms_pick_load_ui;