1 PACKAGE BODY inv_loc_wms_pub AS
2 /* $Header: INVLOCPB.pls 120.6.12000000.3 2007/04/15 12:31:14 smarwah ship $*/
3
4 /**
5 * Table to pass DFF attributes to create_locator and update_locator apis
6 **/
7 TYPE char_tbl IS TABLE OF VARCHAR2(1500) INDEX BY BINARY_INTEGER;
8
9 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_LOC_WMS_PUB';
10
11 PROCEDURE DEBUG(p_msg VARCHAR2) IS
12 l_version VARCHAR2(240);
13 BEGIN
14 l_version := g_pkg_name||'$Revision: 120.6.12000000.3 $';
15
16 inv_mobile_helper_functions.tracelog(
17 p_err_msg => p_msg,
18 p_module => l_version,
19 p_level => 4
20 );
21 END;
22
23 /* Private api to validate the attributes passed to create locator */
24 PROCEDURE validate_loc_attr_info(
25 x_return_status OUT NOCOPY VARCHAR2
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 , p_attribute_category IN VARCHAR2
29 , p_attributes_tbl IN char_tbl
30 , p_attributes_cnt IN NUMBER
31 ) IS
32 TYPE seg_name IS TABLE OF VARCHAR2(1000)
33 INDEX BY BINARY_INTEGER;
34
35 l_context VARCHAR2(1000);
36 l_context_r fnd_dflex.context_r;
37 l_contexts_dr fnd_dflex.contexts_dr;
38 l_dflex_r fnd_dflex.dflex_r;
39 l_segments_dr fnd_dflex.segments_dr;
40 l_enabled_seg_name seg_name;
41 l_wms_all_segs_tbl seg_name;
42 l_nsegments BINARY_INTEGER;
43 l_global_context BINARY_INTEGER;
44 v_index NUMBER := 1;
45 v_index1 NUMBER := 1;
46 l_chk_flag NUMBER := 0;
47 l_char_count NUMBER;
48 l_num_count NUMBER;
49 l_date_count NUMBER;
50 l_wms_attr_chk NUMBER := 1;
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(1000);
54
55 /* Variables used for Validate_desccols procedure */
56 error_segment VARCHAR2(30);
57 errors_received EXCEPTION;
58 error_msg VARCHAR2(5000);
59 s NUMBER;
60 e NUMBER;
61 l_null_char_val VARCHAR2(1000);
62 l_null_num_val NUMBER;
63 l_null_date_val DATE;
64 l_global_nsegments NUMBER := 0;
65 col NUMBER;
66 BEGIN
67 x_return_status := fnd_api.g_ret_sts_success;
68 SAVEPOINT get_lot_attr_information;
69
70 /* Populate the flex field record */
71
72 l_dflex_r.application_id := 401;
73 l_dflex_r.flexfield_name := 'MTL_ITEM_LOCATIONS';
74 /* Get all contexts */
75 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
76
77
78 --DBMS_output.put_line('Found contexts for the Flexfield MTL_LOT_NUMBERS');
79
80
81 /* From the l_contexts_dr, get the position of the global context */
82 l_global_context := l_contexts_dr.global_context;
83
84 --DBMS_output.put_line('Found the position of the global context ');
85
86
87 /* Using the position get the segments in the global context which are enabled */
88 l_context := l_contexts_dr.context_code(l_global_context);
89
90 /* Prepare the context_r type for getting the segments associated with the global context */
91 l_context_r.flexfield := l_dflex_r;
92 l_context_r.context_code := l_context;
93 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
94
95
96 --DBMS_output.put_line('After successfully getting all the enabled segmenst for the Global Context ');
97
98
99 /* read through the segments */
100 l_nsegments := l_segments_dr.nsegments;
101 l_global_nsegments := l_segments_dr.nsegments;
102
103 --DBMS_output.put_line('The number of enabled segments for the Global Context are ' || l_nsegments);
104
105 IF (p_attributes_cnt > l_nsegments) AND
106 p_attribute_category IS NULL THEN
107 /* user passed more parameters than needed by global data elements,
108 * even though context is passed as null. hence error out
109 */
110 --DBMS_output.put_line('more params passed than needed');
111 fnd_message.set_name('FND', 'FLEX-INVALID CONTEXT');
112 fnd_message.set_token('CONTEXT', 'NULL');
113 fnd_message.set_token('ROUTINE','INV_LOC_WMS_PUB');
114 fnd_msg_pub.ADD;
115 RAISE fnd_api.g_exc_error;
116 END IF;
117
118 FOR i IN 1 .. l_nsegments LOOP
119 l_enabled_seg_name(v_index) := l_segments_dr.application_column_name(i);
120 IF l_segments_dr.is_required(i) THEN
121 col := SUBSTR(l_segments_dr.application_column_name(i)
122 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9);
123 --DBMS_output.put_line('col is ' || col);
124 IF ((p_attributes_tbl.EXISTS(col) AND p_attributes_tbl(col) = fnd_api.g_miss_char) OR
125 NOT p_attributes_tbl.EXISTS(col))
126 THEN
127 --DBMS_output.put_line('y r we here');
128 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
129 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
130 fnd_msg_pub.ADD;
131 RAISE fnd_api.g_exc_error;
132 END IF;
133 ELSE
134 --DBMS_output.put_line('This segment is not required');
135 NULL;
136 END IF;
137
138 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
139 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
140 --DBMS_output.put_line('setting column values');
141 fnd_flex_descval.set_column_value(
142 l_segments_dr.application_column_name(i)
143 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
144 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
145 );
146 ELSE
147 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
148 END IF;
149
150 v_index := v_index + 1;
151 END LOOP;
152
153 IF l_enabled_seg_name.COUNT > 0 THEN
154 FOR i IN l_enabled_seg_name.FIRST .. l_enabled_seg_name.LAST LOOP
155
156 --DBMS_output.put_line('The enabled segment : ' || l_enabled_seg_name(i));
157 NULL;
158 END LOOP;
159 END IF;
160
161 /* Initialise the l_context_value to null */
162 l_context := NULL;
163 l_nsegments := 0;
164
165 /*Get the context for the item passed */
166 IF p_attribute_category IS NOT NULL THEN
167 l_context := p_attribute_category;
168 /* Set flex context for validation of the value set */
169 fnd_flex_descval.set_context_value(l_context);
170
171
172 --DBMS_output.put_line('The value of INV context is ' || l_context);
173
174
175 /* Prepare the context_r type */
176 l_context_r.flexfield := l_dflex_r;
177 l_context_r.context_code := l_context;
178 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
179 /* read through the segments */
180 l_nsegments := l_segments_dr.nsegments;
181
182 --DBMS_output.put_line('No of segments enabled for context ' || l_context || ' are ' || l_nsegments);
183 --DBMS_output.put_line('v_index is ' || v_index);
184
185
186
187 FOR i IN 1 .. l_nsegments LOOP
188 l_enabled_seg_name(v_index) := l_segments_dr.application_column_name(i);
189
190
191 --DBMS_output.put_line('The segment is ' || l_segments_dr.segment_name(i));
192
193
194 IF l_segments_dr.is_required(i) THEN
195 col := SUBSTR(l_segments_dr.application_column_name(i)
196 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9);
197 --DBMS_output.put_line('col is ' || col);
198 IF ((p_attributes_tbl.EXISTS(col) AND p_attributes_tbl(col) IS NULL) OR
199 NOT p_attributes_tbl.EXISTS(col))
200 THEN
201 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
202 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
203 fnd_msg_pub.ADD;
204 RAISE fnd_api.g_exc_error;
205 --DBMS_output.put_line('Req segment is not populated');
206 END IF;
207 END IF;
208
209 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
210 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
211 fnd_flex_descval.set_column_value(
212 l_segments_dr.application_column_name(i)
213 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
214 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
215 );
216 ELSE
217 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
218 END IF;
219
220 v_index := v_index + 1;
221 END LOOP;
222
223 END IF;
224 /*Make a call to FND_FLEX_DESCVAL.validate_desccols */
225 IF (l_global_nsegments > 0 AND p_attribute_Category IS NULL ) THEN
226 --DBMS_output.put_line('global segments > 0');
227 l_context := l_contexts_dr.context_code(l_global_context);
228 fnd_flex_descval.set_context_value(l_context);
229 end if;
230 IF( l_global_nsegments > 0 OR p_attribute_category IS NOT NULL )
231 then
232 --DBMS_output.put_line('global segments > 0 or attrib cat is not null');
233 IF fnd_flex_descval.validate_desccols(appl_short_name => 'INV', desc_flex_name => 'MTL_ITEM_LOCATIONS', values_or_ids => 'I'
234 , validation_date => SYSDATE) THEN
235
236 --DBMS_output.put_line('Value set validation successful');
237
238 NULL;
239 ELSE
240
241 error_segment := fnd_flex_descval.error_segment;
242 --DBMS_output.put_line('Value set validation failed for segment ' || error_segment);
243 RAISE errors_received;
244
245 END IF;
246 END IF; /*If P attribute category is not null */
247 --END IF; /* p_attribute_category IS NOT NULL */
248
249 EXCEPTION
250 WHEN errors_received THEN
251 x_return_status := fnd_api.g_ret_sts_error;
252 error_msg := fnd_flex_descval.error_message;
253 s := 1;
254 e := 200;
255
256 --DBMS_output.put_line('Here are the error messages: ');
257 WHILE e < 5001
258 AND SUBSTR(error_msg, s, e) IS NOT NULL LOOP
259 fnd_message.set_name('INV', 'INV_FND_GENERIC_MSG');
260 fnd_message.set_token('MSG', SUBSTR(error_msg, s, e));
261 fnd_msg_pub.ADD;
262 --DBMS_output.put_line(SUBSTR(error_msg, s, e));
263 s := s + 200;
264 e := e + 200;
265 END LOOP;
266
267 ROLLBACK TO get_lot_attr_information;
268 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
269 WHEN fnd_api.g_exc_error THEN
270 x_return_status := fnd_api.g_ret_sts_error;
271 ROLLBACK TO get_lot_attr_information;
272 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
273 WHEN fnd_api.g_exc_unexpected_error THEN
274 x_return_status := fnd_api.g_ret_sts_unexp_error;
275 ROLLBACK TO get_lot_attr_information;
276 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
277 WHEN OTHERS THEN
278 x_return_status := fnd_api.g_ret_sts_unexp_error;
279 ROLLBACK TO get_lot_attr_information;
280 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
281 --DBMS_output.put_line('Error ' || SQLERRM);
282 END validate_loc_attr_info;
283
284
285 /*
286 ** ---------------------------------------------------------------------------
287 ** procedure : create_locator
288 ** description : this procedure creates a new locator in a given organization
289 **
290 ** i/p :
291 ** p_organization_id
292 ** identifier of organization in which locator is to
293 ** be created.
294 ** p_organization_code
295 ** organization code of organziation in which locator
296 ** is to be created. Either p_organization_id or
297 ** p_organziation_code MUST be passed
298 ** p_concatenated_segments
299 ** concatenated segment string with separator
300 ** of the locator to be created. Eg:A.1.1
301 ** p_description
302 ** locator description
303 ** p_inventory_location_type
304 ** type of locator.
305 ** dock door(1) or staging lane(2) or storage locator(3)
306 ** p_picking_order
307 ** number that identifies relative position of locator
308 ** for travel optimization during picking and task dispatching.
309 ** It has a a higher precedence over x,y,z coordinates.
310 ** p_location_maximum_units
311 ** Maxmimum units the locator can hold
312 ** p_subinventory_code
313 ** Subinventory to which locator belongs
314 ** p_location_weight_uom_code
315 ** UOM of locator's max weight capacity
316 ** p_max_weight
317 ** Max weight locator can hold
318 ** p_volume_uom_code
319 ** UOM of locator's max volume capacity
320 ** p_max_cubic_area
321 ** Max volume capacity of the locator
322 ** p_x_coordinate
323 ** X-position of the locator in space. Used
324 ** for travel optimization during picking and task dispatching.
325 ** p_y_coordinate
326 ** Y-position of the locator in space. Used
327 ** for travel optimization during picking and task dispatching.
328 ** p_z_coordinate
329 ** Z-position of the locator in space. Used
330 ** for travel optimization during picking and task dispatching.
331 ** p_physical_location_id
332 ** locators that are the same physically have the same
333 ** inventory_location_id in this column
334 ** p_pick_uom_code
335 ** UOM in which material is picked from locator
336 ** p_dimension_uom_code
337 ** UOM in which locator dimensions are expressed
338 ** p_length
339 ** Length of the locator
340 ** p_width
341 ** Width of the locator
342 ** p_height
343 ** Height of the locator
344 ** p_status_id
345 ** Material Status that needs to be associated to locator
346 ** p_dropping_order
347 ** For ordering drop-off locators and also to order by putaway
348 ** drop-off operations (bug 2681871)
349 ** p_attribute_category Holds the Context of the Descriptive FlexField for the Locator
350 ** p_attribute1 Holds the Descriptive FlexField attribute for the Locator
351 ** p_attribute2 Holds the Descriptive FlexField attribute for the Locator
352 ** p_attribute3 Holds the Descriptive FlexField attribute for the Locator
353 ** p_attribute4 Holds the Descriptive FlexField attribute for the Locator
354 ** p_attribute5 Holds the Descriptive FlexField attribute for the Locator
355 ** p_attribute6 Holds the Descriptive FlexField attribute for the Locator
356 ** p_attribute7 Holds the Descriptive FlexField attribute for the Locator
357 ** p_attribute8 Holds the Descriptive FlexField attribute for the Locator
358 ** p_attribute9 Holds the Descriptive FlexField attribute for the Locator
359 ** p_attribute10 Holds the Descriptive FlexField attribute for the Locator
360 ** p_attribute11 Holds the Descriptive FlexField attribute for the Locator
361 ** p_attribute12 Holds the Descriptive FlexField attribute for the Locator
362 ** p_attribute13 Holds the Descriptive FlexField attribute for the Locator
363 ** p_attribute14 Holds the Descriptive FlexField attribute for the Locator
364 ** p_attribute15 Holds the Descriptive FlexField attribute for the Locator
365 **
366 ** o/p:
367 ** x_return_status
368 ** return status indicating success, error, unexpected error
369 ** x_msg_count
370 ** number of messages in message list
371 ** x_msg_data
372 ** if the number of messages in message list is 1, contains
373 ** message text
374 ** x_inventory_location_id
375 ** identifier of newly created locator or existing locator
376 ** x_locator_exists
377 ** Y - locator exists for given input
378 ** N - locator created for given input
379 **
380 ** ---------------------------------------------------------------------------
381 */
382 PROCEDURE CREATE_LOCATOR (x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_count OUT NOCOPY NUMBER,
384 x_msg_data OUT NOCOPY VARCHAR2,
385 x_inventory_location_id OUT NOCOPY NUMBER,
386 x_locator_exists OUT NOCOPY VARCHAR2,
387 p_organization_id IN NUMBER ,
388 p_organization_code IN VARCHAR2,
389 p_concatenated_segments IN VARCHAR2,
390 p_description IN VARCHAR2,
391 p_inventory_location_type IN NUMBER ,
392 p_picking_order IN NUMBER ,
393 p_location_maximum_units IN NUMBER ,
394 p_SUBINVENTORY_CODE IN VARCHAR2,
395 p_LOCATION_WEIGHT_UOM_CODE IN VARCHAR2,
396 p_mAX_WEIGHT IN NUMBER,
397 p_vOLUME_UOM_CODE IN VARCHAR2,
398 p_mAX_CUBIC_AREA IN NUMBER,
399 p_x_COORDINATE IN NUMBER,
400 p_Y_COORDINATE IN NUMBER,
401 p_Z_COORDINATE IN NUMBER,
402 p_PHYSICAL_LOCATION_ID IN NUMBER,
403 p_PICK_UOM_CODE IN VARCHAR2,
404 p_DIMENSION_UOM_CODE IN VARCHAR2,
405 p_LENGTH IN NUMBER,
406 p_WIDTH IN NUMBER,
407 p_HEIGHT IN NUMBER,
408 p_STATUS_ID IN NUMBER,
409 p_dropping_order IN NUMBER,
410 p_attribute_category IN VARCHAR2 ,
411 p_attribute1 IN VARCHAR2
412 , p_attribute2 IN VARCHAR2
413 , p_attribute3 IN VARCHAR2
414 , p_attribute4 IN VARCHAR2
415 , p_attribute5 IN VARCHAR2
416 , p_attribute6 IN VARCHAR2
417 , p_attribute7 IN VARCHAR2
418 , p_attribute8 IN VARCHAR2
419 , p_attribute9 IN VARCHAR2
420 , p_attribute10 IN VARCHAR2
421 , p_attribute11 IN VARCHAR2
422 , p_attribute12 IN VARCHAR2
423 , p_attribute13 IN VARCHAR2
424 , p_attribute14 IN VARCHAR2
425 , p_attribute15 IN VARCHAR2
426 , p_alias IN VARCHAR2
427 ) IS
428 l_organization_id NUMBER;
429 l_keystat_val BOOLEAN;
430 l_status_id NUMBER;
431 l_validity_check VARCHAR2(10);
432 l_wms_org BOOLEAN;
433 l_loc_type NUMBER;
434 l_return_status VARCHAR2(10);
435 l_msg_count NUMBER;
436 l_msg_data VARCHAR2(20);
437 l_subinventory_code VARCHAR2(10);
438 l_chkflg NUMBER;
439 l_val BOOLEAN;
440 cnt number;
441 -- Material Status Record type declaration
442 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
443 --Table to hold locator DFF Attributes
444 l_inv_attributes_tbl char_tbl;
445 -- Bug# 4903036: Subinventory type, 1 = Storage, 2 = Receiving
446 l_subinventory_type NUMBER;
447
448 l_project_reference_enabled VARCHAR2(1);
449 l_project_control_level NUMBER;
450 l_segment20 VARCHAR2(40);
451
452 l_alias_enabled VARCHAR2(1);
453 l_org_alias_uniqueness VARCHAR2(1);
454 l_sub_alias_uniqueness VARCHAR2(1);
455 l_alias VARCHAR2(30);
456 l_locator VARCHAR2(2000);
457
458 l_procedure_name VARCHAR2(30);
459 l_debug NUMBER;
460 l_progress VARCHAR2(30);
461
462 BEGIN
463
464 l_procedure_name := 'CREATE_LOCATOR';
465 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
466
467 l_validity_check := 'PASSED';
468
469 SAVEPOINT locator_insert;
470
471 -- Initialize return status to success
472 x_return_status := fnd_api.g_ret_sts_success;
473
474 IF l_debug = 1 THEN
475 debug(l_procedure_name);
476 END IF;
477
478 l_progress := '$line$';
479
480 /*
481 * Validate Organization
482 *
483 * If organization id passed use it, else use
484 * organization code
485 */
486
487
488 IF p_organization_id IS NOT NULL THEN
489
490 l_progress := '$line$';
491
492 l_organization_id := p_organization_id;
493
494 BEGIN
495 SELECT enforce_locator_alis_unq_flag
496 INTO l_org_alias_uniqueness
497 FROM mtl_parameters
498 WHERE organization_id = l_organization_id;
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
502 fnd_msg_pub.ADD;
503 RAISE fnd_api.g_exc_error;
504 END;
505
506 ELSE -- p_organization_id is NULL
507
508 IF p_organization_code IS NULL THEN
509
510 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
511 fnd_msg_pub.ADD;
512 RAISE fnd_api.g_exc_error;
513
514 ELSE -- p_organization_code is NULL
515
516 BEGIN
517
518 SELECT organization_id,
519 enforce_locator_alis_unq_flag
520 INTO l_organization_id,
521 l_org_alias_uniqueness
522 FROM mtl_parameters
523 WHERE organization_code = p_organization_code;
524
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
528 fnd_msg_pub.ADD;
529 RAISE fnd_api.g_exc_error;
530 END;
531
532 END IF; -- p_organization_code is NULL
533
534 END IF; -- p_organization_id is NULL
535
536
537
538 /*
539 * Validate Subinvetory
540 *
541 * Check if subinventory code is null.
542 *
543 * If not null, then check subinventory code entered
544 * is valid or not
545 */
546 IF p_subinventory_code IS NULL THEN
547
548 fnd_message.set_name('INV', 'INV_ENTER_SUBINV');
549 fnd_msg_pub.ADD;
550 RAISE fnd_api.g_exc_error;
551
552 ELSE -- p_subinventory_code is NULL
553
554 BEGIN
555 -- Bug# 4903036: Also retrieve the subinventory type
556 SELECT secondary_inventory_name,
557 enable_locator_alias,
558 enforce_alias_uniqueness,
559 NVL(subinventory_type, 1)
560 INTO l_subinventory_code,
561 l_alias_enabled,
562 l_sub_alias_uniqueness,
563 l_subinventory_type
564 FROM mtl_secondary_inventories
565 WHERE secondary_inventory_name = p_subinventory_code
566 AND organization_id = l_organization_id;
567
568 EXCEPTION
569
570 WHEN NO_DATA_FOUND THEN
571 fnd_message.set_name('INV', 'INVALID_SUB');
572 fnd_msg_pub.ADD;
573 RAISE fnd_api.g_exc_error;
574
575 END;
576
577 END IF; -- p_subinventory_code is NULL
578
579 -- Bug# 4903036: Check that the sub type and locator type are compatible.
580 -- If the sub is of type 'Storage', then the locator cannot be of type 'Receiving'.
581 -- If the sub is of type 'Receiving', then the locator cannot be of type 'Storage'.
582 IF ((l_subinventory_type = 1 AND NVL(p_inventory_location_type, 3) = 6) OR
583 (l_subinventory_type = 1 AND NVL(p_inventory_location_type, 3) = 7) OR -- 4911279
584 (l_subinventory_type = 2 AND NVL(p_inventory_location_type, 3) = 3)) THEN
585 fnd_message.set_name('INV', 'INV_INVALID_LOCATOR_TYPE');
586 fnd_msg_pub.ADD;
587 RAISE fnd_api.g_exc_error;
588 END IF;
589
590 /*
591 * Valiate Loator alias
592 *
593 * If subinventory is Alias enabled then Alias is a required
594 * parameter else it must be NULL
595 *
596 */
597 l_alias := p_alias;
598
599 IF l_alias_enabled = 'Y' AND l_alias IS NULL THEN
600
601 fnd_message.set_name('INV', 'INV_ALIAS_REQUIRED');
602 fnd_msg_pub.ADD;
603 RAISE fnd_api.g_exc_error;
604
605 END IF;
606
607 IF NVL(l_alias_enabled, 'N') <> 'Y' THEN
608
609 l_alias := NULL;
610
611 END IF;
612
613
614 IF l_alias_enabled = 'Y' THEN
615
616 IF l_org_alias_uniqueness = 'Y' THEN
617
618 BEGIN
619 SELECT concatenated_segments
620 INTO l_locator
621 FROM mtl_item_locations_kfv
622 WHERE organization_id = p_organization_id
623 AND alias = l_alias
624 AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
625
626 fnd_message.set_name('INV', 'INV_ALIAS_IN_USE');
627 fnd_message.set_token('LOCATOR', l_locator);
628 fnd_msg_pub.ADD;
629 RAISE fnd_api.g_exc_error;
630
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 NULL;
634 END;
635
636 ELSE -- l_org_alias_uniqueness
637
638 IF l_sub_alias_uniqueness = 'Y' THEN
639
640 BEGIN
641
642 SELECT concatenated_segments
643 INTO l_locator
644 FROM mtl_item_locations_kfv
645 WHERE organization_id = p_organization_id
646 AND alias = l_alias
647 AND subinventory_code = p_subinventory_code
648 AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
649
650 fnd_message.set_name('INV', 'INV_ALIAS_IN_USE');
651 fnd_message.set_token('LOCATOR', l_locator);
652 fnd_msg_pub.ADD;
653 RAISE fnd_api.g_exc_error;
654
655 EXCEPTION
656 WHEN NO_DATA_FOUND THEN
657 NULL;
658 END;
659
660 END IF; -- l_sub_alias_uniqueness
661
662 END IF; -- l_org_alias_uniqueness
663
664 END IF; -- l_alias_enabled
665
666
667 /*
668 * Validating Locator
669 */
670
671 IF p_concatenated_segments IS NULL THEN
672
673 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
674 fnd_msg_pub.ADD;
675 RAISE fnd_api.g_exc_error;
676
677 END IF;
678
679 /*
680 BEGIN
681 SELECT inventory_location_id
682 INTO x_inventory_location_id
683 FROM mtl_item_locations_kfv
684 WHERE organization_id = l_organization_id
685 AND subinventory_code = p_subinventory_code
686 AND concatenated_segments = p_concatenated_segments
687 AND ROWNUM < 2;
688
689 x_locator_exists:= 'Y';
690 fnd_message.set_name('INV', 'INV_LOC_DISABLED');
691 fnd_msg_pub.add;
692 fnd_msg_pub.count_and_get
693 ( p_encoded => FND_API.G_FALSE,
694 p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697 return;
698 EXCEPTION
699 WHEN no_data_found THEN
700 null;
701 END;
702 */
703
704 BEGIN
705 l_val :=
706 fnd_flex_keyval.validate_segs(
707 operation => 'FIND_COMBINATION'
708 , appl_short_name => 'INV'
709 , key_flex_code => 'MTLL'
710 , structure_number => 101
711 , concat_segments => p_concatenated_segments
712 , values_or_ids => 'V'
713 , data_set => l_organization_id
714 );
715
716 IF l_val = TRUE THEN
717 x_locator_exists := 'Y';
718 x_inventory_location_id := fnd_flex_keyval.combination_id;
719 fnd_message.set_name('INV', 'INV_LOC_DISABLED');
720 fnd_msg_pub.ADD;
721 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
722 RETURN;
723 END IF;
724 END;
725
726 /*
727 * Validate Status id
728 */
729
730 IF p_status_id IS NOT NULL THEN
731 BEGIN
732 SELECT status_id
733 INTO l_status_id
734 FROM mtl_material_statuses_vl
735 WHERE status_id = p_status_id
736 AND enabled_flag = 1;
737 EXCEPTION
738 WHEN NO_DATA_FOUND THEN
739 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_STATUS_ID');
740 fnd_msg_pub.ADD;
741 RAISE fnd_api.g_exc_error;
742 END;
743 END IF;
744
745 IF (p_location_weight_uom_code IS NOT NULL) THEN
746 BEGIN
747 SELECT 1
748 INTO l_chkflg
749 FROM mtl_units_of_measure
750 WHERE uom_code = p_location_weight_uom_code;
751 EXCEPTION
752 WHEN NO_DATA_FOUND THEN
753 fnd_message.set_name('INV', 'INV_IOI_WEIGHT_UOM_CODE');
754 fnd_msg_pub.ADD;
755 RAISE fnd_api.g_exc_error;
756 END;
757 END IF;
758
759 /* Validate Location volume uom code */
760 --DBMS_output.put_line('Before validating the volume uom ');
761
762 IF (p_volume_uom_code IS NOT NULL) THEN
763 BEGIN
764 SELECT 1
765 INTO l_chkflg
766 FROM mtl_units_of_measure
767 WHERE uom_code = p_volume_uom_code;
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 fnd_message.set_name('INV', 'INV_IOI_VOLUME_UOM_CODE');
771 fnd_msg_pub.ADD;
772 RAISE fnd_api.g_exc_error;
773 END;
774 END IF;
775
776 --DBMS_output.put_line('Before validating the pick uom ');
777 IF (p_pick_uom_code IS NOT NULL) THEN
778 BEGIN
779 SELECT 1
780 INTO l_chkflg
781 FROM mtl_units_of_measure
782 WHERE uom_code = p_pick_uom_code;
783 EXCEPTION
784 WHEN NO_DATA_FOUND THEN
785 fnd_message.set_name('INV', 'INV_IOI_PICK_UOM_CODE');
786 fnd_msg_pub.ADD;
787 RAISE fnd_api.g_exc_error;
788 END;
789 END IF;
790
791 /* Validate Dimension uom code */
792 --DBMS_output.put_line('Before validating the dim uom ');
793
794 IF (p_dimension_uom_code IS NOT NULL) THEN
795 BEGIN
796 SELECT 1
797 INTO l_chkflg
798 FROM mtl_units_of_measure
799 WHERE uom_code = p_dimension_uom_code;
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 fnd_message.set_name('INV', 'INV_IOI_DIMENSION_UOM_CODE');
803 fnd_msg_pub.ADD;
804 RAISE fnd_api.g_exc_error;
805 END;
806 END IF;
807
808 --DBMS_output.put_line('Before check whether locator exists ');
809
810 --- check if locator exists in another subinventory
811 BEGIN
812 -- Bug 5856723 Slight performance improvement for checking validity of locator
813 /* SELECT 'FAILED'
814 INTO l_validity_check
815 FROM DUAL
816 WHERE EXISTS( */
817
818 SELECT subinventory_code
819 INTO l_subinventory_code
820 FROM mtl_item_locations_kfv
821 WHERE concatenated_segments = p_concatenated_segments
822 AND p_subinventory_code <> subinventory_code
823 AND organization_id = l_organization_id
824 AND ROWNUM = 1 ;
825
826 -- );
827 l_validity_check := 'FAILED' ;
828 --DBMS_output.put_line('validity check failed ');
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 --DBMS_output.put_line('In validity check in no data found ');
833 NULL;
834 END;
835
836 /* If the value of l_validity_check is PASSED, call FND API to
837 create locator
838 */
839 IF l_validity_check = 'PASSED' THEN
840 --DBMS_output.put_line('Org id:' || to_char(l_organization_id));
841 --DBMS_output.put_line('Concat :' || p_concatenated_segments);
842
843 l_keystat_val :=
844 fnd_flex_keyval.validate_segs(
845 operation => 'CREATE_COMB_NO_AT'
846 , appl_short_name => 'INV'
847 , key_flex_code => 'MTLL'
848 , structure_number => 101
849 , concat_segments => p_concatenated_segments
850 , values_or_ids => 'V'
851 , data_set => l_organization_id
852 );
853
854 /* Check the value of l_keystat_val .
855 If this returns true,locator has been created successfully.
856 If the value is false,creation of locator failed.
857 */
858
859 --DBMS_output.put_line('Validity check passed ');
860 IF (l_keystat_val = FALSE) THEN
861 --DBMS_output.put_line('validate segment failed ');
862 fnd_message.set_name('INV', 'INV_LOC_CREATION_FAIL');
863 fnd_msg_pub.ADD;
864 RAISE fnd_api.g_exc_unexpected_error;
865 ELSE
866 x_inventory_location_id := fnd_flex_keyval.combination_id;
867
868 IF fnd_flex_keyval.new_combination THEN
869 x_locator_exists := 'N';
870
871 --DBMS_output.put_line('in new combination loop ');
872
873 /* Get default material status if status_id is not passed */
874 IF p_status_id IS NOT NULL THEN
875 l_status_id := p_status_id;
876 ELSE
877 SELECT NVL(default_loc_status_id, 1)
878 INTO l_status_id
879 FROM mtl_secondary_inventories
880 WHERE organization_id = l_organization_id
881 AND secondary_inventory_name = p_subinventory_code;
882 --DBMS_output.put_line('After selecting the status_id ');
883 END IF;
884
885 l_wms_org :=
886 wms_install.check_install(
887 x_return_status => l_return_status
888 , x_msg_count => l_msg_count
889 , x_msg_data => l_msg_data
890 , p_organization_id => l_organization_id
891 );
892
893 -- Use appropriate locator type
894
895 IF l_return_status = 'S' THEN
896 /* Bug 4277516 : Locator type can be same as with WMS orgs for non-WMS orgs too */
897 -- IF l_wms_org THEN
898 l_loc_type := NVL(p_inventory_location_type, 3);
899 -- ELSE
900 -- l_loc_type := NULL;
901 -- END IF;
902 ELSE
903 --DBMS_output.put_line('Wms installed check failed ');
904 fnd_message.set_name('WMS', 'WMS_INSTALL_CHK_ERROR');
905 fnd_msg_pub.ADD;
906 RAISE fnd_api.g_exc_unexpected_error;
907 END IF;
908
909 --Bug Number 5606275
910 BEGIN
911
912 SELECT project_reference_enabled , project_control_level
913 INTO l_project_reference_enabled, l_project_control_level
914 FROM PJM_ORG_PARAMETERS_V
915 WHERE organization_id= l_organization_id;
916
917 SELECT segment20
918 INTO l_segment20
919 FROM mtl_item_locations
920 WHERE organization_id = l_organization_id
921 AND inventory_location_id = x_inventory_location_id;
922
923 IF(l_project_reference_enabled = 'Y') THEN
924 IF( l_project_control_level =2 AND l_segment20 IS NULL) THEN
925
926 fnd_message.set_name('INV', 'INV_TASK_NUM_INVALID');
927 fnd_msg_pub.ADD;
928 RAISE fnd_api.g_exc_error;
929 END IF;
930 END IF;
931
932 EXCEPTION
933 WHEN NO_DATA_FOUND THEN
934 NULL;
935 --Not a project enabled org so continue normally
936 END;
937
938 /* Validate the locator attributes passed by the user */
939 cnt:=0;
940 IF p_attribute1 IS NOT NULL THEN
941 cnt:=cnt+1;
942 END IF;
943 IF p_attribute2 IS NOT NULL THEN
944 cnt:=cnt+1;
945 END IF;
946 IF p_attribute3 IS NOT NULL THEN
947 cnt:=cnt+1;
948 END IF;
949 IF p_attribute4 IS NOT NULL THEN
950 cnt:=cnt+1;
951 END IF;
952 IF p_attribute5 IS NOT NULL THEN
953 cnt:=cnt+1;
954 END IF;
955 IF p_attribute6 IS NOT NULL THEN
956 cnt:=cnt+1;
957 END IF;
958 IF p_attribute7 IS NOT NULL THEN
959 cnt:=cnt+1;
960 END IF;
961 IF p_attribute8 IS NOT NULL THEN
962 cnt:=cnt+1;
963 END IF;
964 IF p_attribute9 IS NOT NULL THEN
965 cnt:=cnt+1;
966 END IF;
967 IF p_attribute10 IS NOT NULL THEN
968 cnt:=cnt+1;
969 END IF;
970 IF p_attribute11 IS NOT NULL THEN
971 cnt:=cnt+1;
972 END IF;
973 IF p_attribute12 IS NOT NULL THEN
974 cnt:=cnt+1;
975 END IF;
976 IF p_attribute13 IS NOT NULL THEN
977 cnt:=cnt+1;
978 END IF;
979 IF p_attribute14 IS NOT NULL THEN
980 cnt:=cnt+1;
981 END IF;
982 IF p_attribute15 IS NOT NULL THEN
983 cnt:=cnt+1;
984 END IF;
985
986 --DBMS_output.put_line('no. of attributes passed ' || cnt);
987 l_inv_attributes_tbl (1) := p_attribute1;
988 l_inv_attributes_tbl (2) := p_attribute2;
989 l_inv_attributes_tbl (3) := p_attribute3;
990 l_inv_attributes_tbl (4) := p_attribute4;
991 l_inv_attributes_tbl (5) := p_attribute5;
992 l_inv_attributes_tbl (6) := p_attribute6;
993 l_inv_attributes_tbl (7) := p_attribute7;
994 l_inv_attributes_tbl (8) := p_attribute8;
995 l_inv_attributes_tbl (9) := p_attribute9;
996 l_inv_attributes_tbl (10) := p_attribute10;
997 l_inv_attributes_tbl (11) := p_attribute11;
998 l_inv_attributes_tbl (12) := p_attribute12;
999 l_inv_attributes_tbl (13) := p_attribute13;
1000 l_inv_attributes_tbl (14) := p_attribute14;
1001 l_inv_attributes_tbl (15) := p_attribute15;
1002
1003 validate_loc_attr_info(
1004 x_return_status => l_return_status
1005 , x_msg_count => l_msg_count
1006 , x_msg_data => l_msg_data
1007 , p_attribute_category => p_attribute_category
1008 , p_attributes_tbl => l_inv_attributes_tbl
1009 , p_attributes_cnt => cnt
1010 );
1011
1012 IF l_return_status = 'E' THEN
1013 --DBMS_output.put_line('Error from validate_loc_attr_info');
1014 fnd_message.set_name('INV', 'INV_LOC_CREATION_FAIL');
1015 fnd_msg_pub.ADD;
1016 RAISE fnd_api.g_exc_error;
1017 ELSIF l_return_status = 'U' THEN
1018 --DBMS_output.put_line('Unexpected Error from validate_loc_attr_info');
1019 fnd_message.set_name('INV', 'INV_LOC_CREATION_FAIL');
1020 fnd_msg_pub.ADD;
1021 RAISE fnd_api.g_exc_unexpected_error;
1022 END IF;
1023 /* End locator DFF attributes validation */
1024
1025 --DBMS_output.put_line('finished calling validate_loc_attr_info');
1026 --DBMS_output.put_line('updating MIL now ');
1027
1028 UPDATE mtl_item_locations
1029 SET subinventory_code = NVL(p_subinventory_code, NULL)
1030 , status_id = NVL(l_status_id, NULL)
1031 , inventory_location_type = NVL(l_loc_type, NULL)
1032 , description = NVL(p_description, NULL)
1033 , picking_order = NVL(p_picking_order, NULL)
1034 , location_maximum_units = NVL(p_location_maximum_units, NULL)
1035 , location_weight_uom_code = NVL(p_location_weight_uom_code, NULL)
1036 , max_weight = NVL(p_max_weight, NULL)
1037 , volume_uom_code = NVL(p_volume_uom_code, NULL)
1038 , max_cubic_area = NVL(p_max_cubic_area, NULL)
1039 , x_coordinate = NVL(p_x_coordinate, NULL)
1040 , y_coordinate = NVL(p_y_coordinate, NULL)
1041 , z_coordinate = NVL(p_z_coordinate, NULL)
1042 , physical_location_id = NVL(p_physical_location_id, NULL)
1043 , pick_uom_code = NVL(p_pick_uom_code, NULL)
1044 , dimension_uom_code = NVL(p_dimension_uom_code, NULL)
1045 , LENGTH = NVL(p_length, NULL)
1046 , width = NVL(p_width, NULL)
1047 , height = NVL(p_height, NULL)
1048 , dropping_order = NVL(p_dropping_order, NULL)
1049 , creation_date = SYSDATE
1050 , created_by = fnd_global.user_id
1051 , last_updated_by = fnd_global.user_id
1052 , last_update_date = SYSDATE
1053 , attribute_category = p_attribute_category
1054 , attribute1 = p_attribute1
1055 , attribute2 = p_attribute2
1056 , attribute3 = p_attribute3
1057 , attribute4 = p_attribute4
1058 , attribute5 = p_attribute5
1059 , attribute6 = p_attribute6
1060 , attribute7 = p_attribute7
1061 , attribute8 = p_attribute8
1062 , attribute9 = p_attribute9
1063 , attribute10 =p_attribute10
1064 , attribute11 =p_attribute11
1065 , attribute12 =p_attribute12
1066 , attribute13 =p_attribute13
1067 , attribute14 =p_attribute14
1068 , attribute15 =p_attribute15
1069 , alias = l_alias
1070 WHERE organization_id = l_organization_id
1071 AND inventory_location_id = x_inventory_location_id;
1072 END IF; -- IF FND_FLEX_KEYVAL.new_combination
1073
1074 IF x_locator_exists = 'N' THEN
1075 -- Stamp material status history
1076 --DBMS_output.put_line('locator_exists = N, populating history');
1077 l_status_rec.organization_id := l_organization_id;
1078 l_status_rec.inventory_item_id := NULL;
1079 l_status_rec.lot_number := NULL;
1080 l_status_rec.serial_number := NULL;
1081 l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
1082 l_status_rec.status_id := l_status_id;
1083 l_status_rec.zone_code := p_subinventory_code;
1084 l_status_rec.locator_id := x_inventory_location_id;
1085 l_status_rec.creation_date := SYSDATE;
1086 l_status_rec.created_by := fnd_global.user_id;
1087 l_status_rec.last_update_date := SYSDATE;
1088 l_status_rec.last_update_login := fnd_global.user_id;
1089 l_status_rec.initial_status_flag := 'Y';
1090 l_status_rec.from_mobile_apps_flag := 'N';
1091 inv_material_status_pkg.insert_status_history(l_status_rec);
1092 END IF;
1093 END IF; -- (l_keystat_val = FALSE)
1094 ELSE
1095 fnd_message.set_name('INV', 'INV_LOC_BELONG_TO_OTH_SUB');
1096 fnd_msg_pub.ADD;
1097 RAISE fnd_api.g_exc_error;
1098 END IF; -- (l_validility = passed)
1099
1100 EXCEPTION
1101 WHEN fnd_api.g_exc_error THEN
1102 x_return_status := 'E';
1103 ROLLBACK TO locator_insert;
1104 debug(l_procedure_name ||' Expected Error ');
1105 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1106 WHEN fnd_api.g_exc_unexpected_error THEN
1107 x_return_status := 'U';
1108 ROLLBACK TO locator_insert;
1109 debug(l_procedure_name ||' Unxpected Error ');
1110 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1111 WHEN OTHERS THEN
1112 x_return_status := 'U';
1113 --DBMS_output.put_line('In others '||sqlerrm);
1114 ROLLBACK TO locator_insert;
1115
1116 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1117 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
1118 END IF;
1119 debug(l_procedure_name ||' Others '||SQLERRM);
1120 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1121 END create_locator;
1122
1123 /*
1124 ** ---------------------------------------------------------------------------
1125 ** procedure : update_locator
1126 ** description : this procedure updates an existing locator
1127 **
1128 ** i/p :
1129 ** NOTE:
1130 ** if the default value of the input parameter is used, then
1131 ** that column retains its original value and is not changed
1132 ** during update.
1133 ** this can be achieved by not passing this parameter during the
1134 ** API call.
1135 **
1136 ** p_organization_id
1137 ** identifier of organization in which locator is to
1138 ** be updated.
1139 ** p_organization_code
1140 ** organization code of organziation in which locator
1141 ** is to be updated. Either p_organization_id or
1142 ** p_organziation_code MUST be passed
1143 ** p_inventory_location_id
1144 ** identifier of locator to be updated
1145 ** p_concatenated_segments
1146 ** concatenated segment string with separator
1147 ** of the locator to be updated. Eg:A.1.1
1148 ** either p_inventory_location_id or p_concatenated_segments
1149 ** MUST be passed.
1150 ** p_description
1151 ** locator description
1152 ** p_inventory_location_type
1153 ** type of locator.
1154 ** dock door(1) or staging lane(2) or storage locator(3)
1155 ** p_picking_order
1156 ** number that identifies physical position of locator
1157 ** for travel optimization during picking and task dispatching.
1158 ** It has a a higher precedence over x,y,z coordinates.
1159 ** p_location_maximum_units
1160 ** Maxmimum units the locator can hold
1161 ** p_subinventory_code
1162 ** Subinventory to which locator belongs
1163 ** p_location_weight_uom_code
1164 ** UOM of locator's max weight capacity
1165 ** p_max_weight
1166 ** Max weight locator can hold
1167 ** p_volume_uom_code
1168 ** UOM of locator's max volume capacity
1169 ** p_max_cubic_area
1170 ** Max volume capacity of the locator
1171 ** p_x_coordinate
1172 ** X-position of the locator in space. Used
1173 ** for travel optimization during picking and task dispatching.
1174 ** p_y_coordinate
1175 ** Y-position of the locator in space. Used
1176 ** for travel optimization during picking and task dispatching.
1177 ** p_z_coordinate
1178 ** Z-position of the locator in space. Used
1179 ** for travel optimization during picking and task dispatching.
1180 ** p_physical_location_id
1181 ** locators that are the same physically have the same
1182 ** inventory_location_id in this column
1183 ** p_pick_uom_code
1184 ** UOM in which material is picked from locator
1185 ** p_dimension_uom_code
1186 ** UOM in which locator dimensions are expressed
1187 ** p_length
1188 ** Length of the locator
1189 ** p_width
1190 ** Width of the locator
1191 ** p_height
1192 ** Height of the locator
1193 ** p_status_id
1194 ** Material Status that needs to be associated to locator
1195 ** p_dropping_order
1196 ** For ordering drop-off locators and also to order by putaway
1197 ** drop-off operations (bug 2681871)
1198 ** For the DFF attributes mentioned below, to update correctly use the following strategy
1199 ** To retain the value in the table, do not pass any value OR pass NULL as i/p
1200 ** To update the attribute with NULL, pass fnd_api.g_miss_char
1201 ** To update with any other value, pass the appropriate value
1202 ** p_attribute_category Holds the Context of the Descriptive FlexField for the Locator
1203 ** p_attribute1 Holds the Descriptive FlexField attribute for the Locator
1204 ** p_attribute2 Holds the Descriptive FlexField attribute for the Locator
1205 ** p_attribute3 Holds the Descriptive FlexField attribute for the Locator
1206 ** p_attribute4 Holds the Descriptive FlexField attribute for the Locator
1207 ** p_attribute5 Holds the Descriptive FlexField attribute for the Locator
1208 ** p_attribute6 Holds the Descriptive FlexField attribute for the Locator
1209 ** p_attribute7 Holds the Descriptive FlexField attribute for the Locator
1210 ** p_attribute8 Holds the Descriptive FlexField attribute for the Locator
1211 ** p_attribute9 Holds the Descriptive FlexField attribute for the Locator
1212 ** p_attribute10 Holds the Descriptive FlexField attribute for the Locator
1213 ** p_attribute11 Holds the Descriptive FlexField attribute for the Locator
1214 ** p_attribute12 Holds the Descriptive FlexField attribute for the Locator
1215 ** p_attribute13 Holds the Descriptive FlexField attribute for the Locator
1216 ** p_attribute14 Holds the Descriptive FlexField attribute for the Locator
1217 ** p_attribute15 Holds the Descriptive FlexField attribute for the Locator
1218 **
1219 ** o/p:
1220 ** x_return_status
1221 ** return status indicating success, error, unexpected error
1222 ** x_msg_count
1223 ** number of messages in message list
1224 ** x_msg_data
1225 ** if the number of messages in message list is 1, contains
1226 ** message text
1227 **
1228 ** ---------------------------------------------------------------------------
1229 */
1230 PROCEDURE UPDATE_LOCATOR (x_return_status OUT NOCOPY VARCHAR2,
1231 x_msg_count OUT NOCOPY NUMBER,
1232 x_msg_data OUT NOCOPY VARCHAR2,
1233 p_organization_id IN NUMBER ,
1234 p_organization_code IN VARCHAR2,
1235 p_inventory_location_id IN NUMBER,
1236 p_concatenated_segments IN VARCHAR2,
1237 p_description IN VARCHAR2 ,
1238 p_disabled_date IN DATE ,
1239 p_inventory_location_type IN NUMBER ,
1240 p_picking_order IN NUMBER ,
1241 p_location_maximum_units IN NUMBER ,
1242 p_location_Weight_uom_code IN VARCHAR2 ,
1243 p_max_weight IN NUMBER ,
1244 p_volume_uom_code IN VARCHAR2 ,
1245 p_max_cubic_area IN NUMBER ,
1246 p_x_coordinate IN NUMBER ,
1247 p_y_coordinate IN NUMBER ,
1248 p_z_coordinate IN NUMBER ,
1249 p_physical_location_id IN NUMBER ,
1250 p_pick_uom_code IN VARCHAR2 ,
1251 p_dimension_uom_code IN VARCHAR2 ,
1252 p_length IN NUMBER ,
1253 p_width IN NUMBER ,
1254 p_height IN NUMBER ,
1255 p_status_id IN NUMBER ,
1256 p_dropping_order IN NUMBER ,
1257 p_attribute_category IN VARCHAR2 ,
1258 p_attribute1 IN VARCHAR2
1259 , p_attribute2 IN VARCHAR2
1260 , p_attribute3 IN VARCHAR2
1261 , p_attribute4 IN VARCHAR2
1262 , p_attribute5 IN VARCHAR2
1263 , p_attribute6 IN VARCHAR2
1264 , p_attribute7 IN VARCHAR2
1265 , p_attribute8 IN VARCHAR2
1266 , p_attribute9 IN VARCHAR2
1267 , p_attribute10 IN VARCHAR2
1268 , p_attribute11 IN VARCHAR2
1269 , p_attribute12 IN VARCHAR2
1270 , p_attribute13 IN VARCHAR2
1271 , p_attribute14 IN VARCHAR2
1272 , p_attribute15 IN VARCHAR2
1273 , p_alias IN VARCHAR2
1274 ) AS
1275 l_organization_id NUMBER;
1276 l_inventory_location_id NUMBER;
1277
1278 l_status_id NUMBER;
1279 l_wms_org BOOLEAN;
1280 l_msg_count NUMBER;
1281 l_msg_data VARCHAR2(20);
1282 l_chk_sub VARCHAR2(20);
1283 l_chkflg NUMBER;
1284 l_val BOOLEAN;
1285 L_RETURN_STATUS VARCHAR2(1);
1286 -- Material Status Record type declaration
1287 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
1288 --Table to hold locator DFF Attributes
1289 l_inv_attributes_tbl char_tbl;
1290 cnt number;
1291 -- Bug# 4903036: Subinventory type, 1 = Storage, 2 = Receiving
1292 l_subinventory_type NUMBER;
1293
1294 l_alias_enabled VARCHAR2(1);
1295 l_org_alias_uniqueness VARCHAR2(1);
1296 l_sub_alias_uniqueness VARCHAR2(1);
1297 l_alias VARCHAR2(30);
1298 l_locator VARCHAR2(2000);
1299 l_subinventory_code VARCHAR2(10);
1300
1301 l_procedure_name VARCHAR2(30);
1302 l_debug NUMBER;
1303
1304
1305 BEGIN
1306 -- Declare a save point
1307
1308 SAVEPOINT locator_update;
1309 -- Default the status to success
1310
1311 l_procedure_name := 'UPDATE_LOCATOR';
1312 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1313
1314
1315 x_return_status := fnd_api.g_ret_sts_success;
1316
1317 IF l_debug = 1 THEN
1318 debug(l_procedure_name);
1319 END IF;
1320
1321 /*
1322 * Validate Organization
1323 */
1324
1325 IF p_organization_id IS NOT NULL THEN
1326
1327 l_organization_id := p_organization_id;
1328
1329 BEGIN
1330 SELECT enforce_locator_alis_unq_flag
1331 INTO l_org_alias_uniqueness
1332 FROM mtl_parameters
1333 WHERE organization_id = l_organization_id;
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
1337 fnd_msg_pub.ADD;
1338 RAISE fnd_api.g_exc_error;
1339 END;
1340
1341 ELSE -- p_organization_id IS NULL
1342
1343 IF p_organization_code IS NULL THEN
1344
1345 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
1346 fnd_msg_pub.ADD;
1347 RAISE fnd_api.g_exc_error;
1348
1349 ELSE -- p_organization_code is NULL
1350
1351 BEGIN
1352 SELECT organization_id,
1353 enforce_locator_alis_unq_flag
1354 INTO l_organization_id,
1355 l_org_alias_uniqueness
1356 FROM mtl_parameters
1357 WHERE organization_code = p_organization_code;
1358 EXCEPTION
1359 WHEN NO_DATA_FOUND THEN
1360 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
1361 fnd_msg_pub.ADD;
1362 RAISE fnd_api.g_exc_error;
1363 END;
1364
1365 END IF; -- p_organization_code is NULL
1366
1367 END IF; -- p_organization_id IS NULL
1368
1369
1370 /*
1371 * Validate locator
1372 */
1373
1374 IF (p_concatenated_segments IS NULL AND p_inventory_location_id IS NULL) THEN
1375
1376 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
1377 fnd_msg_pub.ADD;
1378 RAISE fnd_api.g_exc_error;
1379
1380 ELSIF p_concatenated_segments IS NOT NULL AND p_inventory_location_id IS NULL THEN
1381
1382 /* BEGIN
1383 SELECT inventory_location_id
1384 INTO l_inventory_location_id
1385 FROM MTL_ITEM_LOCATIONS_KFV
1386 WHERE concatenated_segments = p_concatenated_segments
1387 and organization_id = l_organization_id;
1388 END;
1389 */
1390 BEGIN
1391 l_val :=
1392 fnd_flex_keyval.validate_segs(
1393 operation => 'FIND_COMBINATION'
1394 , appl_short_name => 'INV'
1395 , key_flex_code => 'MTLL'
1396 , structure_number => 101
1397 , concat_segments => p_concatenated_segments
1398 , values_or_ids => 'V'
1399 , data_set => l_organization_id
1400 );
1401
1402 IF l_val = FALSE THEN
1403 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
1404 fnd_msg_pub.ADD;
1405 RAISE fnd_api.g_exc_error;
1406 ELSE
1407 l_inventory_location_id := fnd_flex_keyval.combination_id;
1408 END IF;
1409
1410 END;
1411
1412 ELSE -- p_concatenated_segments
1413
1414 l_inventory_location_id := p_inventory_location_id;
1415
1416 END IF; -- p_concatenated_segments
1417
1418
1419 /*
1420 * Validate Inventory_Location_Type
1421 */
1422
1423 IF (p_inventory_location_type <> fnd_api.g_miss_num AND
1424 p_inventory_location_type NOT IN(1, 2, 3, 4, 5, 6, 7)) THEN
1425
1426 fnd_message.set_name('INV', 'INV_INVALID_LOCATOR_TYPE');
1427 fnd_msg_pub.ADD;
1428 RAISE fnd_api.g_exc_error;
1429
1430 END IF;
1431
1432 -- Bug# 4903036: Check that the sub type and locator type are compatible.
1433 -- Do this check only if the inventory location type is being updated for the locator.
1434 IF (p_inventory_location_type <> fnd_api.g_miss_num) THEN
1435 -- Retrieve the subinventory type for the sub that the locator is in.
1436 BEGIN
1437 SELECT NVL(msi.subinventory_type, 1)
1438 INTO l_subinventory_type
1439 FROM mtl_item_locations mil, mtl_secondary_inventories msi
1440 WHERE mil.inventory_location_id = l_inventory_location_id
1441 AND mil.organization_id = l_organization_id
1442 AND mil.subinventory_code = msi.secondary_inventory_name
1443 AND msi.organization_id = l_organization_id;
1444 EXCEPTION
1445 WHEN NO_DATA_FOUND THEN
1446 fnd_message.set_name('INV', 'INVALID_SUB');
1447 fnd_msg_pub.ADD;
1448 RAISE fnd_api.g_exc_error;
1449 END;
1450
1451 -- If the sub is of type 'Storage', then the locator cannot be of type 'Receiving'.
1452 -- If the sub is of type 'Receiving', then the locator cannot be of type 'Storage'.
1453 IF ((l_subinventory_type = 1 AND NVL(p_inventory_location_type, 3) = 6) OR
1454 (l_subinventory_type = 1 AND NVL(p_inventory_location_type, 3) = 7) OR --4911279
1455 (l_subinventory_type = 2 AND NVL(p_inventory_location_type, 3) = 3)) THEN
1456 fnd_message.set_name('INV', 'INV_INVALID_LOCATOR_TYPE');
1457 fnd_msg_pub.ADD;
1458 RAISE fnd_api.g_exc_error;
1459 END IF;
1460 END IF;
1461
1462
1463 /*
1464 * Get the Locator status
1465 */
1466
1467 BEGIN
1468
1469 SELECT status_id,
1470 subinventory_code
1471 INTO l_status_id,
1472 l_subinventory_code
1473 FROM mtl_item_locations
1474 WHERE inventory_location_id = l_inventory_location_id
1475 AND organization_id = l_organization_id;
1476
1477 EXCEPTION
1478 WHEN NO_DATA_FOUND THEN
1479 NULL;
1480 END;
1481
1482 SELECT enable_locator_alias,
1483 enforce_alias_uniqueness
1484 INTO l_alias_enabled,
1485 l_sub_alias_uniqueness
1486 FROM mtl_secondary_inventories
1487 WHERE secondary_inventory_name = l_subinventory_code
1488 AND organization_id = l_organization_id;
1489
1490 /*
1491 * Validate locator Alias
1492 */
1493 l_alias := p_alias;
1494
1495 IF l_alias_enabled = 'Y' AND l_alias IS NULL THEN
1496
1497 fnd_message.set_name('INV', 'INV_ALIAS_REQUIRED');
1498 fnd_msg_pub.ADD;
1499 RAISE fnd_api.g_exc_error;
1500
1501 END IF;
1502
1503 IF NVL(l_alias_enabled, 'N') <> 'Y' THEN
1504 l_alias := NULL;
1505 END IF;
1506
1507 IF l_alias_enabled = 'Y' THEN
1508
1509 IF l_org_alias_uniqueness = 'Y' THEN
1510
1511 BEGIN
1512 SELECT concatenated_segments
1513 INTO l_locator
1514 FROM mtl_item_locations_kfv
1515 WHERE organization_id = p_organization_id
1516 AND alias = l_alias
1517 AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
1518
1519 fnd_message.set_name('INV', 'INV_ALIAS_IN_USE');
1520 fnd_message.set_token('LOCATOR', l_locator);
1521 fnd_msg_pub.ADD;
1522 RAISE fnd_api.g_exc_error;
1523
1524 EXCEPTION
1525 WHEN NO_DATA_FOUND THEN
1526 NULL;
1527 END;
1528
1529 ELSE -- l_org_alias_uniqueness
1530
1531 IF l_sub_alias_uniqueness = 'Y' THEN
1532
1533 BEGIN
1534 SELECT concatenated_segments
1535 INTO l_locator
1536 FROM mtl_item_locations_kfv
1537 WHERE organization_id = p_organization_id
1538 AND alias = l_alias
1539 AND subinventory_code = l_subinventory_code
1540 AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
1541
1542 fnd_message.set_name('INV', 'INV_ALIAS_IN_USE');
1543 fnd_message.set_token('LOCATOR', l_locator);
1544 fnd_msg_pub.ADD;
1545 RAISE fnd_api.g_exc_error;
1546
1547 EXCEPTION
1548 WHEN NO_DATA_FOUND THEN
1549 NULL;
1550 END;
1551
1552 END IF; -- l_sub_alias_uniqueness
1553
1554 END IF; -- l_org_alias_uniqueness
1555
1556 END IF; -- l_alias_enabled
1557
1558
1559 /* Validate Location weight uom code */
1560
1561 --DBMS_output.put_line('Before validating the weight uom ');
1562 IF (p_location_weight_uom_code <> fnd_api.g_miss_char
1563 AND p_location_weight_uom_code IS NOT NULL) THEN
1564 BEGIN
1565 SELECT 1
1566 INTO l_chkflg
1567 FROM mtl_units_of_measure
1568 WHERE uom_code = p_location_weight_uom_code;
1569 EXCEPTION
1570 WHEN NO_DATA_FOUND THEN
1571 fnd_message.set_name('INV', 'INV_IOI_WEIGHT_UOM_CODE');
1572 fnd_msg_pub.ADD;
1573 RAISE fnd_api.g_exc_error;
1574 END;
1575 END IF;
1576
1577 /* Validate Location volume uom code */
1578 --DBMS_output.put_line('Before validating the volume uom ');
1579
1580 IF (p_volume_uom_code <> fnd_api.g_miss_char
1581 AND p_volume_uom_code IS NOT NULL) THEN
1582 BEGIN
1583 SELECT 1
1584 INTO l_chkflg
1585 FROM mtl_units_of_measure
1586 WHERE uom_code = p_volume_uom_code;
1587 EXCEPTION
1588 WHEN NO_DATA_FOUND THEN
1589 fnd_message.set_name('INV', 'INV_IOI_WEIGHT_UOM_CODE');
1590 fnd_msg_pub.ADD;
1591 RAISE fnd_api.g_exc_error;
1592 END;
1593 END IF;
1594
1595 /* Validate Location Pick uom code */
1596 --DBMS_output.put_line('Before validating the pick uom ');
1597 IF (p_pick_uom_code <> fnd_api.g_miss_char
1598 AND p_pick_uom_code IS NOT NULL) THEN
1599 BEGIN
1600 SELECT 1
1601 INTO l_chkflg
1602 FROM mtl_units_of_measure
1603 WHERE uom_code = p_pick_uom_code;
1604 EXCEPTION
1605 WHEN NO_DATA_FOUND THEN
1606 fnd_message.set_name('INV', 'INV_IOI_PICK_UOM_CODE ');
1607 fnd_msg_pub.ADD;
1608 RAISE fnd_api.g_exc_error;
1609 END;
1610 END IF;
1611
1612 /* Validate Dimension uom code */
1613 --DBMS_output.put_line('Before validating the dim uom ');
1614
1615 IF (p_dimension_uom_code <> fnd_api.g_miss_char
1616 AND p_dimension_uom_code IS NOT NULL) THEN
1617 BEGIN
1618 SELECT 1
1619 INTO l_chkflg
1620 FROM mtl_units_of_measure
1621 WHERE uom_code = p_dimension_uom_code;
1622 EXCEPTION
1623 WHEN NO_DATA_FOUND THEN
1624 fnd_message.set_name('INV', 'INV_IOI_DIMENSION_UOM_CODE');
1625 fnd_msg_pub.ADD;
1626 RAISE fnd_api.g_exc_error;
1627 END;
1628 END IF;
1629
1630 /* Validate Status code */
1631 -- --DBMS_output.put_line('Before validating the status ');
1632 IF (p_status_id <> fnd_api.g_miss_num
1633 AND p_status_id IS NOT NULL) THEN
1634 BEGIN
1635 SELECT 1
1636 INTO l_chkflg
1637 FROM mtl_material_statuses_vl
1638 WHERE status_id = p_status_id
1639 AND enabled_flag = 1;
1640 EXCEPTION
1641 WHEN NO_DATA_FOUND THEN
1642 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_STATUS_ID');
1643 fnd_msg_pub.ADD;
1644 RAISE fnd_api.g_exc_error;
1645 END;
1646 END IF;
1647 --DBMS_output.put_line('setting attribs');
1648 /* Validate the locator attributes passed by the user */
1649 cnt:=0;
1650 IF p_attribute1 IS NOT NULL THEN
1651 cnt:=cnt+1;
1652 END IF;
1653 IF p_attribute2 IS NOT NULL THEN
1654 cnt:=cnt+1;
1655 END IF;
1656 IF p_attribute3 IS NOT NULL THEN
1657 cnt:=cnt+1;
1658 END IF;
1659 IF p_attribute4 IS NOT NULL THEN
1660 cnt:=cnt+1;
1661 END IF;
1662 IF p_attribute5 IS NOT NULL THEN
1663 cnt:=cnt+1;
1664 END IF;
1665 IF p_attribute6 IS NOT NULL THEN
1666 cnt:=cnt+1;
1667 END IF;
1668 IF p_attribute7 IS NOT NULL THEN
1669 cnt:=cnt+1;
1670 END IF;
1671 IF p_attribute8 IS NOT NULL THEN
1672 cnt:=cnt+1;
1673 END IF;
1674 IF p_attribute9 IS NOT NULL THEN
1675 cnt:=cnt+1;
1676 END IF;
1677 IF p_attribute10 IS NOT NULL THEN
1678 cnt:=cnt+1;
1679 END IF;
1680 IF p_attribute11 IS NOT NULL THEN
1681 cnt:=cnt+1;
1682 END IF;
1683 IF p_attribute12 IS NOT NULL THEN
1684 cnt:=cnt+1;
1685 END IF;
1686 IF p_attribute13 IS NOT NULL THEN
1687 cnt:=cnt+1;
1688 END IF;
1689 IF p_attribute14 IS NOT NULL THEN
1690 cnt:=cnt+1;
1691 END IF;
1692 IF p_attribute15 IS NOT NULL THEN
1693 cnt:=cnt+1;
1694 END IF;
1695
1696 IF p_attribute1 = fnd_api.g_miss_char THEN
1697 l_inv_attributes_tbl (1) := NULL;
1698 ELSE
1699 l_inv_attributes_tbl (1) := p_attribute1;
1700 END IF;
1701 IF p_attribute2 = fnd_api.g_miss_char THEN
1702 l_inv_attributes_tbl (2) := NULL;
1703 ELSE
1704 l_inv_attributes_tbl (2) := p_attribute2;
1705 END IF;
1706
1707 IF p_attribute3 = fnd_api.g_miss_char THEN
1708 l_inv_attributes_tbl (3) := NULL;
1709 ELSE
1710 l_inv_attributes_tbl (3) := p_attribute3;
1711 END IF;
1712
1713 IF p_attribute4 = fnd_api.g_miss_char THEN
1714 l_inv_attributes_tbl (4) := NULL;
1715 ELSE
1716 l_inv_attributes_tbl (4) := p_attribute4;
1717 END IF;
1718
1719 IF p_attribute5 = fnd_api.g_miss_char THEN
1720 l_inv_attributes_tbl (5) := NULL;
1721 ELSE
1722 l_inv_attributes_tbl (5) := p_attribute5;
1723 END IF;
1724
1725 IF p_attribute6 = fnd_api.g_miss_char THEN
1726 l_inv_attributes_tbl (6) := NULL;
1727 ELSE
1728 l_inv_attributes_tbl (6) := p_attribute6;
1729 END IF;
1730 IF p_attribute7 = fnd_api.g_miss_char THEN
1731 l_inv_attributes_tbl (7) := NULL;
1732 ELSE
1733 l_inv_attributes_tbl (7) := p_attribute7;
1734 END IF;
1735 IF p_attribute8 = fnd_api.g_miss_char THEN
1736 l_inv_attributes_tbl (8) := NULL;
1737 ELSE
1738 l_inv_attributes_tbl (8) := p_attribute8;
1739 END IF;
1740
1741 IF p_attribute9 = fnd_api.g_miss_char THEN
1742 l_inv_attributes_tbl (9) := NULL;
1743 ELSE
1744 l_inv_attributes_tbl (9) := p_attribute9;
1745 END IF;
1746 IF p_attribute10 = fnd_api.g_miss_char THEN
1747 l_inv_attributes_tbl (10) := NULL;
1748 ELSE
1749 l_inv_attributes_tbl (10) := p_attribute10;
1750 END IF;
1751 IF p_attribute11 = fnd_api.g_miss_char THEN
1752 l_inv_attributes_tbl (11) := NULL;
1753 ELSE
1754 l_inv_attributes_tbl (11) := p_attribute11;
1755 END IF;
1756 IF p_attribute12 = fnd_api.g_miss_char THEN
1757 l_inv_attributes_tbl (12) := NULL;
1758 ELSE
1759 l_inv_attributes_tbl (12) := p_attribute12;
1760 END IF;
1761
1762 IF p_attribute13 = fnd_api.g_miss_char THEN
1763 l_inv_attributes_tbl (13) := NULL;
1764 ELSE
1765 l_inv_attributes_tbl (13) := p_attribute13;
1766 END IF;
1767
1768 IF p_attribute14 = fnd_api.g_miss_char THEN
1769 l_inv_attributes_tbl (14) := NULL;
1770 ELSE
1771 l_inv_attributes_tbl (14) := p_attribute14;
1772 END IF;
1773
1774 IF p_attribute15 = fnd_api.g_miss_char THEN
1775 l_inv_attributes_tbl (15) := NULL;
1776 ELSE
1777 l_inv_attributes_tbl (15) := p_attribute15;
1778 END IF;
1779
1780 --DBMS_output.put_line('calling validate');
1781 validate_loc_attr_info(
1782 x_return_status => l_return_status
1783 , x_msg_count => l_msg_count
1784 , x_msg_data => l_msg_data
1785 , p_attribute_category => p_attribute_category
1786 , p_attributes_tbl => l_inv_attributes_tbl
1787 , p_attributes_cnt => cnt
1788 );
1789
1790 IF l_return_status = 'E' THEN
1791 --DBMS_output.put_line('Error from validate_loc_attr_info');
1792 fnd_message.set_name('INV', 'INV_LOC_CREATION_FAIL');
1793 fnd_msg_pub.ADD;
1794 RAISE fnd_api.g_exc_error;
1795 ELSIF l_return_status = 'U' THEN
1796 --DBMS_output.put_line('Unexpected Error from validate_loc_attr_info');
1797 fnd_message.set_name('INV', 'INV_LOC_CREATION_FAIL');
1798 fnd_msg_pub.ADD;
1799 RAISE fnd_api.g_exc_unexpected_error;
1800 END IF;
1801 /* End locator DFF attributes validation */
1802
1803 /* When the control is at this point, data supplied are valid.
1804 We will update the MTL_ITEM_LOCATIONS
1805 table with the information provided
1806 */
1807
1808 --DBMS_output.put_line('Before update ');
1809
1810 UPDATE mtl_item_locations
1811 SET description = DECODE(p_description, fnd_api.g_miss_char, description, p_description)
1812 , disable_date = DECODE(p_disabled_date, fnd_api.g_miss_date, disable_date, p_disabled_date)
1813 , inventory_location_type =
1814 DECODE(
1815 p_inventory_location_type
1816 , fnd_api.g_miss_num, inventory_location_type
1817 , p_inventory_location_type
1818 )
1819 , picking_order = DECODE(p_picking_order, fnd_api.g_miss_num, picking_order, p_picking_order)
1820 , location_maximum_units = DECODE(p_location_maximum_units, fnd_api.g_miss_num, location_maximum_units, p_location_maximum_units)
1821 , location_weight_uom_code =
1822 DECODE(
1823 p_location_weight_uom_code
1824 , fnd_api.g_miss_char, location_weight_uom_code
1825 , p_location_weight_uom_code
1826 )
1827 , max_weight = DECODE(p_max_weight, fnd_api.g_miss_num, max_weight, p_max_weight)
1828 , volume_uom_code = DECODE(p_volume_uom_code, fnd_api.g_miss_char, volume_uom_code, p_volume_uom_code)
1829 , max_cubic_area = DECODE(p_max_cubic_area, fnd_api.g_miss_num, max_cubic_area, p_max_cubic_area)
1830 , x_coordinate = DECODE(p_x_coordinate, fnd_api.g_miss_num, x_coordinate, p_x_coordinate)
1831 , y_coordinate = DECODE(p_y_coordinate, fnd_api.g_miss_num, y_coordinate, p_y_coordinate)
1832 , z_coordinate = DECODE(p_z_coordinate, fnd_api.g_miss_num, z_coordinate, p_z_coordinate)
1833 , pick_uom_code = DECODE(p_pick_uom_code, fnd_api.g_miss_char, pick_uom_code, p_pick_uom_code)
1834 , dimension_uom_code = DECODE(p_dimension_uom_code, fnd_api.g_miss_char, dimension_uom_code, p_dimension_uom_code)
1835 , LENGTH = DECODE(p_length, fnd_api.g_miss_num, LENGTH, p_length)
1836 , width = DECODE(p_width, fnd_api.g_miss_num, width, p_width)
1837 , height = DECODE(p_height, fnd_api.g_miss_num, height, p_height)
1838 , status_id = DECODE(p_status_id, fnd_api.g_miss_num, status_id, p_status_id)
1839 , dropping_order = DECODE(p_dropping_order, fnd_api.g_miss_num, dropping_order, p_dropping_order)
1840 , last_updated_by = fnd_global.user_id
1841 , last_update_date = SYSDATE
1842 , attribute_category = decode(p_attribute_category, NULL, attribute_category, fnd_api.g_miss_char, NULL, p_attribute_category)
1843 , attribute1 = decode(p_attribute1, NULL, attribute1, fnd_api.g_miss_char, NULL, p_attribute1)
1844 , attribute2 = decode(p_attribute2, NULL, attribute2, fnd_api.g_miss_char, NULL, p_attribute2)
1845 , attribute3 = decode(p_attribute3, NULL, attribute3, fnd_api.g_miss_char, NULL, p_attribute3)
1846 , attribute4 = decode(p_attribute4, NULL, attribute4, fnd_api.g_miss_char, NULL, p_attribute4)
1847 , attribute5 = decode(p_attribute5, NULL, attribute5, fnd_api.g_miss_char, NULL, p_attribute5)
1848 , attribute6 = decode(p_attribute6, NULL, attribute6, fnd_api.g_miss_char, NULL, p_attribute6)
1849 , attribute7 = decode(p_attribute7, NULL, attribute7, fnd_api.g_miss_char, NULL, p_attribute7)
1850 , attribute8 = decode(p_attribute8, NULL, attribute8, fnd_api.g_miss_char, NULL, p_attribute8)
1851 , attribute9 = decode(p_attribute9, NULL, attribute9, fnd_api.g_miss_char, NULL, p_attribute9)
1852 , attribute10 = decode(p_attribute10, NULL, attribute10, fnd_api.g_miss_char, NULL, p_attribute10)
1853 , attribute11 = decode(p_attribute11, NULL, attribute11, fnd_api.g_miss_char, NULL, p_attribute11)
1854 , attribute12 = decode(p_attribute12, NULL, attribute12, fnd_api.g_miss_char, NULL, p_attribute12)
1855 , attribute13 = decode(p_attribute13, NULL, attribute13, fnd_api.g_miss_char, NULL, p_attribute13)
1856 , attribute14 = decode(p_attribute14, NULL, attribute14, fnd_api.g_miss_char, NULL, p_attribute14)
1857 , attribute15 = decode(p_attribute15, NULL, attribute15, fnd_api.g_miss_char, NULL, p_attribute15)
1858 , alias = l_alias
1859 WHERE inventory_location_id = l_inventory_location_id
1860 AND organization_id = l_organization_id;
1861
1862 /* If the p_status_id is not null then,stamp the new status in the status history table */
1863 IF (p_status_id IS NOT NULL
1864 AND p_status_id <> fnd_api.g_miss_num
1865 AND p_status_id <> l_status_id) THEN
1866
1867 l_status_rec.organization_id := l_organization_id;
1868 l_status_rec.inventory_item_id := NULL;
1869 l_status_rec.lot_number := NULL;
1870 l_status_rec.serial_number := NULL;
1871 l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
1872 l_status_rec.status_id := p_status_id;
1873 l_status_rec.zone_code := l_subinventory_code;
1874 l_status_rec.locator_id := l_inventory_location_id;
1875 l_status_rec.creation_date := SYSDATE;
1876 l_status_rec.created_by := fnd_global.user_id;
1877 l_status_rec.last_update_date := SYSDATE;
1878 l_status_rec.last_update_login := fnd_global.user_id;
1879 l_status_rec.initial_status_flag := 'N';
1880 l_status_rec.from_mobile_apps_flag := 'N';
1881 --DBMS_output.put_line('Before updating staTUS');
1882 inv_material_status_pkg.insert_status_history(l_status_rec);
1883 END IF;
1884 --DBMS_output.put_line('End of procedure ');
1885 EXCEPTION
1886 WHEN fnd_api.g_exc_error THEN
1887 x_return_status := 'E';
1888 ROLLBACK TO locator_update;
1889 debug(l_procedure_name ||' Expected Error ');
1890 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1891 WHEN fnd_api.g_exc_unexpected_error THEN
1892 x_return_status := 'U';
1893 ROLLBACK TO locator_update;
1894 debug(l_procedure_name ||'Unexpected Error ');
1895 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1896 WHEN OTHERS THEN
1897 x_return_status := 'U';
1898 ROLLBACK TO locator_update;
1899
1900 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1901 fnd_msg_pub.add_exc_msg(g_pkg_name, l_procedure_name);
1902 END IF;
1903 debug(l_procedure_name ||' Others '||SQLERRM);
1904 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1905 END update_locator;
1906
1907 /*
1908 ** ---------------------------------------------------------------------------
1909 ** procedure : create_loc_item_tie
1910 ** description : For a given set of organization, subinventory, item and
1911 ** locator, this API ties the given item to the given locator.
1912 ** i/p :
1913
1914 ** p_inventory_item_id
1915 ** Identifier of item .
1916 ** p_item
1917 ** Concatenated segment string with separator of the item.
1918 ** Either P_inventory_item_id or the p_item MUST be passed
1919 ** p_organization_id
1920 ** Identifier of organization
1921 ** p_organization_code
1922 ** Organization code of organziation in which locator is to
1923 ** be updated. Either p_organization_id or p_organziation_code
1924 ** MUST be passed
1925 ** p_subinventory_code
1926 ** The subinventory to which the locator need to be attached to .
1927 ** p_inventory_location_id
1928 ** Identifier of locator to be attached to the specified subinventory
1929 ** p_locator
1930 ** Concatenated segment string with separator of the locator to be
1931 ** updated. Eg:A.1.1 either p_inventory_location_id or
1932 ** p_concatenated_segments MUST be passed.
1933 ** p_status_id
1934 ** Identifier of status
1935 ** p_par_level
1936 ** PAR level for the item-locator. Valid only when the subinventory is PAR planned.
1937 **
1938 ** o/p:
1939 **
1940 ** x_return_status
1941 ** return status indicating success, error, unexpected error
1942 ** x_msg_count
1943 ** number of messages in message list
1944 ** x_msg_data
1945 ** if the number of messages in message list is 1, contains
1946 ** message text
1947 **
1948 ** ---------------------------------------------------------------------------
1949 */
1950 PROCEDURE create_loc_item_tie(
1951 x_return_status OUT NOCOPY VARCHAR2
1952 , x_msg_count OUT NOCOPY NUMBER
1953 , x_msg_data OUT NOCOPY VARCHAR2
1954 , p_inventory_item_id IN NUMBER
1955 , p_item IN VARCHAR2
1956 , p_organization_id IN NUMBER
1957 , p_organization_code IN VARCHAR2
1958 , p_subinventory_code IN VARCHAR2
1959 , p_inventory_location_id IN NUMBER
1960 , p_locator IN VARCHAR2
1961 , p_status_id IN NUMBER
1962 , p_par_level IN NUMBER DEFAULT NULL
1963 ) AS
1964 l_inventory_item_id NUMBER;
1965 l_organization_id NUMBER;
1966 l_locator_id NUMBER;
1967 l_locator_exists VARCHAR2(1);
1968 l_subflag VARCHAR2(10);
1969 l_status_chk NUMBER;
1970 l_chkflg VARCHAR2(10);
1971 l_item_sub NUMBER;
1972 l_planning_level NUMBER;
1973 BEGIN
1974 -- declare a savepoint
1975
1976 SAVEPOINT location_item_restrict;
1977 x_return_status := fnd_api.g_ret_sts_success;
1978
1979 /* If organization id passed use it, else use organization code */
1980 IF p_organization_id IS NOT NULL THEN
1981 l_organization_id := p_organization_id;
1982 ELSE
1983 IF p_organization_code IS NULL THEN
1984 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
1985 /* Organization is required */
1986 fnd_msg_pub.ADD;
1987 RAISE fnd_api.g_exc_error;
1988 ELSE
1989 BEGIN
1990 SELECT organization_id
1991 INTO l_organization_id
1992 FROM mtl_parameters
1993 WHERE organization_code = p_organization_code;
1994 EXCEPTION
1995 WHEN NO_DATA_FOUND THEN
1996 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
1997 /* The Organization Code provided is invalid */
1998 fnd_msg_pub.ADD;
1999 RAISE fnd_api.g_exc_error;
2000 END;
2001 END IF;
2002 END IF;
2003
2004 /* Validate item concatenated segment */
2005 IF p_inventory_item_id IS NOT NULL THEN
2006 l_inventory_item_id := p_inventory_item_id;
2007 ELSE
2008 IF p_item IS NULL THEN
2009 fnd_message.set_name('INV', 'INV_INT_ITMSEGCODE');
2010 /* Invalid item segments */
2011 fnd_msg_pub.ADD;
2012 RAISE fnd_api.g_exc_error;
2013 ELSE
2014 BEGIN
2015 SELECT inventory_item_id
2016 INTO l_inventory_item_id
2017 FROM mtl_system_items_kfv
2018 WHERE concatenated_segments = p_item
2019 AND organization_id = l_organization_id;
2020 EXCEPTION
2021 WHEN NO_DATA_FOUND THEN
2022 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
2023 /* The Item provided is invalid */
2024 fnd_msg_pub.ADD;
2025 RAISE fnd_api.g_exc_error;
2026 END;
2027 END IF;
2028 END IF;
2029
2030 /*Validate the subinventory code */
2031 IF p_subinventory_code IS NULL THEN
2032 fnd_message.set_name('INV', 'INV_ENTER_SUBINV');
2033 /* Please enter a subinventory before proceeding */
2034 fnd_msg_pub.ADD;
2035 RAISE fnd_api.g_exc_error;
2036 ELSE
2037 BEGIN
2038 SELECT NVL(planning_level, 2)
2039 INTO l_planning_level
2040 FROM mtl_secondary_inventories
2041 WHERE secondary_inventory_name = p_subinventory_code
2042 AND organization_id = l_organization_id;
2043 EXCEPTION
2044 WHEN NO_DATA_FOUND THEN
2045 fnd_message.set_name('INV', 'INVALID_SUB');
2046 /* The subinventory provided is invalid */
2047 fnd_msg_pub.ADD;
2048 RAISE fnd_api.g_exc_error;
2049 END;
2050 END IF;
2051
2052 /* Validate The locator */
2053 IF p_inventory_location_id IS NOT NULL THEN
2054 l_locator_id := p_inventory_location_id;
2055 ELSE
2056 IF p_locator IS NULL THEN
2057 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
2058 /* Invalid locator segments */
2059 fnd_msg_pub.ADD;
2060 RAISE fnd_api.g_exc_error;
2061 ELSE
2062 BEGIN
2063 SELECT inventory_location_id
2064 INTO l_locator_id
2065 FROM mtl_item_locations_kfv
2066 WHERE concatenated_segments = p_locator
2067 AND organization_id = l_organization_id;
2068 EXCEPTION
2069 WHEN NO_DATA_FOUND THEN
2070 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
2071 /* The Locator provided is invalid */
2072 fnd_msg_pub.ADD;
2073 RAISE fnd_api.g_exc_error;
2074 END;
2075 END IF;
2076 END IF;
2077
2078 /* Check if the locator and subinventory_code combination is valid */
2079 BEGIN
2080 SELECT 'VALID'
2081 INTO l_subflag
2082 FROM mtl_item_locations
2083 WHERE inventory_location_id = l_locator_id
2084 AND subinventory_code = p_subinventory_code
2085 AND organization_id = l_organization_id;
2086 EXCEPTION
2087 WHEN NO_DATA_FOUND THEN
2088 fnd_message.set_name('INV', 'INV_INT_LOCSEGEXP');
2089 /* The Locator segments are invalid for the given subinventory, organization combination */
2090 RAISE fnd_api.g_exc_error;
2091 END;
2092
2093 /* Check if this combination of Org, Item and Locator is present in MTL_SECONDARY_LOCATORS */
2094 BEGIN
2095 SELECT 'Y'
2096 INTO l_locator_exists
2097 FROM mtl_secondary_locators
2098 WHERE secondary_locator = l_locator_id
2099 AND organization_id = l_organization_id
2100 AND inventory_item_id = l_inventory_item_id;
2101 EXCEPTION
2102 WHEN NO_DATA_FOUND THEN
2103 l_locator_exists := 'N';
2104 END;
2105
2106 IF p_status_id IS NOT NULL THEN
2107 BEGIN
2108 SELECT 1
2109 INTO l_status_chk
2110 FROM mtl_material_statuses_vl
2111 WHERE status_id = p_status_id
2112 AND enabled_flag = 1;
2113 EXCEPTION
2114 WHEN NO_DATA_FOUND THEN
2115 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_STATUS_ID');
2116 /* Invalid status ID. */
2117 fnd_msg_pub.ADD;
2118 RAISE fnd_api.g_exc_error;
2119 END;
2120 END IF;
2121
2122 /* Check if an entry exists in MTL_ITEM_SUB_INVENTORIES table for the item passed */
2123 BEGIN
2124 SELECT 1
2125 INTO l_item_sub
2126 FROM mtl_item_sub_inventories
2127 WHERE inventory_item_id = l_inventory_item_id
2128 AND secondary_inventory = p_subinventory_code
2129 AND organization_id = l_organization_id;
2130 EXCEPTION
2131 WHEN NO_DATA_FOUND THEN
2132 INSERT INTO mtl_item_sub_inventories
2133 (
2134 inventory_item_id
2135 , organization_id
2136 , secondary_inventory
2137 , last_update_date
2138 , last_updated_by
2139 , creation_date
2140 , created_by
2141 , inventory_planning_code
2142 )
2143 VALUES (
2144 l_inventory_item_id
2145 , l_organization_id
2146 , p_subinventory_code
2147 , SYSDATE
2148 , fnd_global.user_id
2149 , SYSDATE
2150 , fnd_global.user_id
2151 , 6
2152 );
2153 END;
2154
2155 /* If the l_locator_flag is N then insert a row into MTL_SECONDARY_LOCATORS */
2156 IF l_locator_exists = 'N' THEN
2157 IF (inv_control.g_current_release_level >= inv_release.g_j_release_level) THEN
2158 INSERT INTO mtl_secondary_locators
2159 (
2160 inventory_item_id
2161 , organization_id
2162 , secondary_locator
2163 , last_update_date
2164 , last_updated_by
2165 , creation_date
2166 , created_by
2167 , subinventory_code
2168 , status_id
2169 , maximum_quantity
2170 )
2171 VALUES (
2172 l_inventory_item_id
2173 , l_organization_id
2174 , l_locator_id
2175 , SYSDATE
2176 , fnd_global.user_id
2177 , SYSDATE
2178 , fnd_global.user_id
2179 , p_subinventory_code
2180 , p_status_id
2181 , DECODE(l_planning_level, 1, p_par_level, NULL)
2182 );
2183 ELSE
2184 INSERT INTO mtl_secondary_locators
2185 (
2186 inventory_item_id
2187 , organization_id
2188 , secondary_locator
2189 , last_update_date
2190 , last_updated_by
2191 , creation_date
2192 , created_by
2193 , subinventory_code
2194 , status_id
2195 )
2196 VALUES (
2197 l_inventory_item_id
2198 , l_organization_id
2199 , l_locator_id
2200 , SYSDATE
2201 , fnd_global.user_id
2202 , SYSDATE
2203 , fnd_global.user_id
2204 , p_subinventory_code
2205 , p_status_id
2206 );
2207 END IF;
2208 ELSE
2209 fnd_message.set_name('INV', 'INV_LOCATOR_ASSIGNED');
2210 /* Locator selected has already been assigned to this item and subinventory */
2211 fnd_msg_pub.ADD;
2212 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2213 END IF;
2214 EXCEPTION
2215 WHEN fnd_api.g_exc_error THEN
2216 x_return_status := 'E';
2217 ROLLBACK TO location_item_restrict;
2218 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2219 WHEN fnd_api.g_exc_unexpected_error THEN
2220 x_return_status := 'U';
2221 ROLLBACK TO location_item_restrict;
2222 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2223 WHEN OTHERS THEN
2224 x_return_status := 'U';
2225 --DBMS_output.put_line('In others '||sqlerrm);
2226 ROLLBACK TO location_item_restrict;
2227
2228 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2229 fnd_msg_pub.add_exc_msg('INV_LOC_WMS_PUB', 'create_loc_item_tie');
2230 END IF;
2231
2232 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2233 END create_loc_item_tie;
2234
2235 /*
2236 **-----------------------------------------------------------------------------------
2237 **
2238 ** procedure : delete_locator
2239 ** description : this procedure deletes a locator in a given organization.
2240 ** i/p
2241 ** p_inventory_location_id
2242 ** identifier of locator to be deleted
2243 ** p_concatenated_segments
2244 ** concatenated segment string with separator of the locator to be deleted. Eg:A.1.1
2245 ** p_organization_id
2246 ** identifier of organization in which locator is to be deleted.
2247 ** p_organization_code
2248 ** organization code of organziation in which locator is to be deleted.
2249 ** Either p_organization_id or p_organziation_code MUST be passed
2250 ** p_validation_req_flag
2251 ** the flag which determines whether validation is required or not.
2252 ** If it is 'N',the locator is deleted without any further validation
2253 ** on its existence in other tables.If it is'Y', the locator is deleted
2254 ** only if doesnot exist in other tables.
2255 **
2256 ** o/p
2257 ** x_return_status
2258 ** return status indicating success, error, unexpected error
2259 ** x_msg_count
2260 ** number of messages in message list
2261 ** x_msg_data :
2262 ** if the number of messages in message list is 1,
2263 ** contains message text x_inventory_location_id
2264 **
2265 **-----------------------------------------------------------------------------------
2266 */
2267 PROCEDURE delete_locator(
2268 x_return_status OUT NOCOPY VARCHAR2
2269 , x_msg_count OUT NOCOPY NUMBER
2270 , x_msg_data OUT NOCOPY VARCHAR2
2271 , p_inventory_location_id IN NUMBER
2272 , p_concatenated_segments IN VARCHAR2
2273 , p_organization_id IN NUMBER
2274 , p_organization_code IN VARCHAR2
2275 , p_validation_req_flag IN VARCHAR2
2276 ) IS
2277 /* Locator details */
2278 l_inventory_location_id NUMBER;
2279 l_locator VARCHAR2(30);
2280 /* Organisation details */
2281 l_organization_id NUMBER;
2282 /* Others */
2283 l_chk_org NUMBER;
2284 l_chk_loc NUMBER;
2285 l_chk_flag NUMBER;
2286 l_active_loc NUMBER;
2287 l_physical_locator_id NUMBER;
2288 l_val BOOLEAN;
2289 BEGIN
2290 SAVEPOINT del_loc_api;
2291 x_return_status := fnd_api.g_ret_sts_success;
2292
2293 /* Check if the organization_id passed,otherwise get the organisation_id corresponding to the
2294 Non null organisation_code passed */
2295 IF p_organization_id IS NOT NULL THEN
2296 SELECT 1
2297 INTO l_chk_org
2298 FROM DUAL
2299 WHERE EXISTS(SELECT 1
2300 FROM mtl_parameters
2301 WHERE organization_id = p_organization_id);
2302
2303 l_organization_id := p_organization_id;
2304 ELSE
2305 IF p_organization_code IS NULL THEN
2306 fnd_message.set_name('INV', 'INV_ORG_REQUIRED');
2307 /* Organisation is required */
2308 fnd_msg_pub.ADD;
2309 RAISE fnd_api.g_exc_error;
2310 ELSE
2311 SELECT organization_id
2312 INTO l_organization_id
2313 FROM mtl_parameters
2314 WHERE organization_code = p_organization_code;
2315 END IF;
2316 END IF;
2317
2318 /* Check the validity of p_inventory_location_id is not null.
2319 Check if this inventory_location_id exists in MTL_ITEM_LOCATIONS.
2320 Validate if concatenated_segment passed is valid or not.
2321 If the concatenated segments is not null and If it is valid,fnd_flex_keyval.combination_id
2322 will return the inventory_location_id for the Concatenated_segment combination.
2323 Otherwise the concatenated_segment passed is not a valid one.
2324 */
2325 IF p_inventory_location_id IS NOT NULL THEN
2326 SELECT 1
2327 INTO l_chk_loc
2328 FROM DUAL
2329 WHERE EXISTS(SELECT 1
2330 FROM mtl_item_locations
2331 WHERE inventory_location_id = p_inventory_location_id
2332 AND organization_id = l_organization_id);
2333
2334 l_inventory_location_id := p_inventory_location_id;
2335 l_locator := p_inventory_location_id;
2336 ELSE
2337 IF p_concatenated_segments IS NOT NULL THEN
2338 l_val :=
2339 fnd_flex_keyval.validate_segs(
2340 operation => 'FIND_COMBINATION'
2341 , appl_short_name => 'INV'
2342 , key_flex_code => 'MTLL'
2343 , structure_number => 101
2344 , concat_segments => p_concatenated_segments
2345 , values_or_ids => 'V'
2346 , data_set => l_organization_id
2347 );
2348 END IF;
2349
2350 IF l_val = FALSE
2351 OR p_concatenated_segments IS NULL THEN
2352 fnd_message.set_name('INV', 'INV_LOC_SEGCODE');
2353 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2354 /* LOCATOR does not exist */
2355 fnd_msg_pub.ADD;
2356 RAISE fnd_api.g_exc_error;
2357 ELSE
2358 l_locator := p_concatenated_segments;
2359 l_inventory_location_id := fnd_flex_keyval.combination_id;
2360 END IF;
2361 END IF;
2362
2363 /*
2364 * Check if the l_inventory_location_id passed is a physical_location_id
2365 * If the l_inventory_location_id is a physical_location_id for other
2366 * locators, then error
2367 * A locator is a physical locator if inventory_location_id = physical_
2368 * location_id
2369 */
2370 BEGIN
2371 SELECT 1
2372 INTO l_physical_locator_id
2373 FROM DUAL
2374 WHERE EXISTS(SELECT 1
2375 FROM mtl_item_locations
2376 WHERE physical_location_id = l_inventory_location_id
2377 AND organization_id = l_organization_id
2378 AND inventory_location_id <> physical_location_id); --Bug :5036570
2379
2380 fnd_message.set_name('INV', 'INV_LOC_PHY');
2381 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2382 /*Locator LOCATOR cannot be deleted as it exists as a Physical Locator to Some other Locators*/
2383 fnd_msg_pub.ADD;
2384 RAISE fnd_api.g_exc_error;
2385 EXCEPTION
2386 WHEN NO_DATA_FOUND THEN
2387 NULL;
2388 END;
2389
2390 /* Check if the locator is active or is it disabled:
2391 Check in MTL_ITEM_LOCATIONS -DISABLE_DATE > SYSDATE. if true then error
2392 */
2393
2394 /* Replaced disable_date with nvl(disable_date,sysdate+1)
2395 as part of bug 2004798 in the sql below
2396 */
2397 BEGIN
2398 SELECT 1
2399 INTO l_active_loc
2400 FROM mtl_item_locations
2401 WHERE inventory_location_id = l_inventory_location_id
2402 AND organization_id = l_organization_id
2403 AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
2404
2405 fnd_message.set_name('INV', 'INV_LOC_ACTIVE');
2406 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2407 /*Locator locator cannot be deleted as it is active */
2408 fnd_msg_pub.ADD;
2409 RAISE fnd_api.g_exc_error;
2410 EXCEPTION
2411 WHEN NO_DATA_FOUND THEN
2412 NULL;
2413 END;
2414
2415 /* Check the value of p_validation_req_flag
2416 If the value of p_validation_req_flag ='N' then
2417 delete the locator from MTL_ITEM_LOCATIONS table without validating further .
2418 Otherwise do the following steps */
2419 IF p_validation_req_flag = 'Y' THEN --If For Validation
2420 /* Check if the locator is present in MTL_ONHAND_QUANTITIES_DETAIL
2421 If the locator_id exists then error out*/
2422 BEGIN
2423 SELECT 1
2424 INTO l_chk_flag
2425 FROM DUAL
2426 WHERE EXISTS(SELECT 1
2427 FROM mtl_onhand_quantities_detail
2428 WHERE locator_id = l_inventory_location_id
2429 AND organization_id = l_organization_id);
2430
2431 fnd_message.set_name('INV', 'INV_LOC_ONHANDQTY');
2432 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2433 /*Locator locator cannot be deleted as items exist in it*/
2434 fnd_msg_pub.ADD;
2435 RAISE fnd_api.g_exc_error;
2436 EXCEPTION
2437 WHEN NO_DATA_FOUND THEN
2438 NULL;
2439 END;
2440
2441 /* Check if the locator is present in MTL_RESERVATIONS
2442 If the locator_id exists then error out:
2443 */
2444 BEGIN
2445 SELECT 1
2446 INTO l_chk_flag
2447 FROM DUAL
2448 WHERE EXISTS(SELECT 1
2449 FROM mtl_reservations
2450 WHERE locator_id = l_inventory_location_id
2451 AND organization_id = l_organization_id);
2452
2453 fnd_message.set_name('INV', 'INV_LOC_RESERVE');
2454 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2455 /*The locator locator cannot be deleted as reservations exist against it*/
2456 fnd_msg_pub.ADD;
2457 RAISE fnd_api.g_exc_error;
2458 EXCEPTION
2459 WHEN NO_DATA_FOUND THEN
2460 NULL;
2461 END;
2462
2463 /* Check if the locator is present in WMS_LICENSE_PLATE_NUMBERS
2464 If the LOCATOR_ID exists then error out*/
2465 BEGIN
2466 SELECT 1
2467 INTO l_chk_flag
2468 FROM DUAL
2469 WHERE EXISTS(SELECT 1
2470 FROM wms_license_plate_numbers
2471 WHERE locator_id = l_inventory_location_id
2472 AND organization_id = l_organization_id);
2473
2474 fnd_message.set_name('INV', 'INV_LOC_LPNEXIST');
2475 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2476 /*Locator locator cannot be deleted as LPNs reside in it*/
2477 fnd_msg_pub.ADD;
2478 RAISE fnd_api.g_exc_error;
2479 EXCEPTION
2480 WHEN NO_DATA_FOUND THEN
2481 NULL;
2482 END;
2483
2484 /*Check if the locator is present in MTL_SECONDARY_LOCATORS
2485 If the SECONDARY_LOCATOR exists then error out*/
2486 BEGIN
2487 SELECT 1
2488 INTO l_chk_flag
2489 FROM DUAL
2490 WHERE EXISTS(SELECT 1
2491 FROM mtl_secondary_locators
2492 WHERE secondary_locator = l_inventory_location_id
2493 AND organization_id = l_organization_id);
2494
2495 fnd_message.set_name('INV', 'INV_LOC_ITEMTIE');
2496 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2497 /*Locator locator cannot be deleted as it is tied to an item */
2498 fnd_msg_pub.ADD;
2499 RAISE fnd_api.g_exc_error;
2500 EXCEPTION
2501 WHEN NO_DATA_FOUND THEN
2502 NULL;
2503 END;
2504
2505 /*Check if the locator is present in MTL_TRANSACTIONS_INTERFACE
2506 If the locator_id exists then error out*/
2507 BEGIN
2508 SELECT 1
2509 INTO l_chk_flag
2510 FROM DUAL
2511 WHERE EXISTS(SELECT 1
2512 FROM mtl_transactions_interface
2513 WHERE locator_id = l_inventory_location_id
2514 AND organization_id = l_organization_id);
2515
2516 fnd_message.set_name('INV', 'INV_LOC_PENDTXN');
2517 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2518 /*Locator locator cannot be deleted as there are pending transactions against it*/
2519 fnd_msg_pub.ADD;
2520 RAISE fnd_api.g_exc_error;
2521 EXCEPTION
2522 WHEN NO_DATA_FOUND THEN
2523 NULL;
2524 END;
2525
2526 /* Check if the locator is present in MTL_MATERIAL_TRANSACTIONS_TEMP
2527 If the LOCATOR_ID exists then error out*/
2528 /* Added the condition after OR clause in the following SQL to handle the case
2529 when a locator exists in the tranfer_to_location field of the MMTT record
2530 as a part of the Bug Fix:2004798*/
2531 BEGIN
2532 SELECT 1
2533 INTO l_chk_flag
2534 FROM DUAL
2535 WHERE EXISTS(
2536 SELECT 1
2537 FROM mtl_material_transactions_temp
2538 WHERE (locator_id = l_inventory_location_id
2539 AND organization_id = l_organization_id)
2540 OR(transfer_to_location = l_inventory_location_id
2541 AND NVL(transfer_organization, organization_id) = l_organization_id));
2542
2543 fnd_message.set_name('INV', 'INV_LOC_PENDTXN');
2544 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2545 /*Locator locator cannot be deleted as there are pending transactions against it*/
2546 fnd_msg_pub.ADD;
2547 RAISE fnd_api.g_exc_error;
2548 EXCEPTION
2549 WHEN NO_DATA_FOUND THEN
2550 NULL;
2551 END;
2552
2553 /* Check if the locator is existing in RCV_TRANSACTIONS_INTERFACE
2554 if the locator exists then error out*/
2555 BEGIN
2556 SELECT 1
2557 INTO l_chk_flag
2558 FROM DUAL
2559 WHERE EXISTS(SELECT 1
2560 FROM rcv_transactions_interface
2561 WHERE locator_id = l_inventory_location_id
2562 AND to_organization_id = l_organization_id);
2563
2564 fnd_message.set_name('INV', 'INV_LOC_PENDTXN');
2565 fnd_message.set_token('LOCATOR', l_locator, TRUE);
2566 /*Locator locator cannot be deleted as there are pending transactions against it*/
2567 fnd_msg_pub.ADD;
2568 RAISE fnd_api.g_exc_error;
2569 EXCEPTION
2570 WHEN NO_DATA_FOUND THEN
2571 NULL;
2572 END;
2573 END IF; --End If For Validation
2574
2575 /* If all the above steps are success then delete the inventory_location_id
2576 from MTL_ITEM_LOCATIONS
2577 for the combination of inventory_location_id and organization_id*/
2578 DELETE mtl_item_locations
2579 WHERE inventory_location_id = l_inventory_location_id
2580 AND organization_id = l_organization_id;
2581 EXCEPTION
2582 WHEN fnd_api.g_exc_error THEN
2583 x_return_status := fnd_api.g_ret_sts_error;
2584 ROLLBACK TO del_loc_api;
2585 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2586 WHEN fnd_api.g_exc_unexpected_error THEN
2587 x_return_status := fnd_api.g_ret_sts_unexp_error;
2588 ROLLBACK TO del_loc_api;
2589 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2590 WHEN NO_DATA_FOUND THEN
2591 ROLLBACK TO del_loc_api;
2592 x_return_status := fnd_api.g_ret_sts_error;
2593 WHEN OTHERS THEN
2594 x_return_status := fnd_api.g_ret_sts_unexp_error;
2595 ROLLBACK TO del_loc_api;
2596
2597 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2598 fnd_msg_pub.add_exc_msg('inv_loc_wms_pub', 'delete_locator');
2599 END IF;
2600
2601 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2602 END delete_locator;
2603
2604
2605 END inv_loc_wms_pub;
[Home] [Help]