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