[Home] [Help]
PACKAGE BODY: APPS.WMS_ZONES_PVT
Source
1 PACKAGE BODY wms_zones_pvt AS
2 /* $Header: WMSZONEB.pls 120.0 2005/05/25 02:35:41 appldev noship $ */
3
4 -- Package : WMS_ZONES_PVT
5 -- File : $RCSfile: WMSZONEB.pls,v $
6 -- Content :
7 -- Description : This package provides the following services,
8 -- 1. Table handlers for WMSZONES.fmb
9 -- 2. API's for Zones entitiy
10 -- Notes :
11 -- Modified : Mon Jul 14 14:33:10 GMT+05:30 2003
12
13 /**
14 **/
15 g_version_printed BOOLEAN := FALSE;
16 g_pkg_name VARCHAR2 (30) := 'WMS_ZONES_PVT';
17
18 /**
19 * Flag to indicate whether the initialization of the data
20 * structures (g_locator_types.. .etc, see below) needed to
21 * use this package through the WMSZONES.fmb is done
22 *
23 * The default value of this flag is FALSE and is set to true
24 * when the procedure INITIALIZE is called.
25 *
26 */
27 g_initialized BOOLEAN := FALSE;
28
29 TYPE lookup_meaning_table IS TABLE OF mfg_lookups.meaning%TYPE
30 INDEX BY BINARY_INTEGER;
31
32 TYPE locator_status_table IS TABLE OF mtl_material_statuses.status_code%TYPE
33 INDEX BY BINARY_INTEGER;
34
35 TYPE subinventory_status_table IS TABLE OF mtl_material_statuses.status_code%TYPE
36 INDEX BY BINARY_INTEGER;
37
38 g_locator_types lookup_meaning_table;
39 g_subinventory_types lookup_meaning_table;
40 g_locator_status locator_status_table;
41 g_subinventory_status subinventory_status_table;
42
43 g_all_locators_message VARCHAR2(240);
44
45 PROCEDURE set_locator_status IS
46
47 CURSOR sel_status IS
48 SELECT status_id,
49 status_code
50 FROM mtl_material_statuses
51 WHERE locator_control = 1
52 AND enabled_flag = 1;
53
54 BEGIN
55
56 FOR rec IN sel_status LOOP
57 g_locator_status (rec.status_id) := rec.status_code;
58 END LOOP;
59
60 END set_locator_status;
61
62 PROCEDURE set_subinventory_status IS
63
64 CURSOR sel_status IS
65 SELECT status_id, status_code
66 FROM mtl_material_statuses
67 WHERE zone_control = 1
68 AND enabled_flag = 1;
69
70 BEGIN
71
72 FOR rec IN sel_status LOOP
73 g_subinventory_status (rec.status_id) := rec.status_code;
74 END LOOP;
75
76 END set_subinventory_status;
77
78 PROCEDURE set_locator_types IS
79 BEGIN
80
81 SELECT meaning BULK COLLECT
82 INTO g_locator_types
83 FROM mfg_lookups
84 WHERE lookup_type = 'MTL_LOCATOR_TYPES'
85 ORDER BY lookup_code;
86
87 END set_locator_types;
88
89 PROCEDURE set_subinventory_types IS
90 BEGIN
91
92 SELECT meaning BULK COLLECT
93 INTO g_subinventory_types
94 FROM mfg_lookups
95 WHERE lookup_type = 'MTL_SUB_TYPES'
96 ORDER BY lookup_code;
97
98 END;
99
100 PROCEDURE DEBUG (p_message IN VARCHAR2, p_module IN VARCHAR2, p_level NUMBER) IS
101
102 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
103
104 BEGIN
105 dbms_output.put_line(fnd_profile.VALUE ('INV_DEBUG_FILE'));
106 -- dbms_output.put_line(p_message);
107
108 IF NOT g_version_printed THEN
109 inv_log_util.TRACE ('$Header: WMSZONEB.pls 120.0 2005/05/25 02:35:41 appldev noship $',
110 g_pkg_name,
111 9
112 );
113 g_version_printed := TRUE;
114
115 END IF;
116
117 inv_log_util.TRACE (p_message,
118 g_pkg_name || '.' || p_module,
119 p_level
120 );
121
122 END debug;
123
124 PROCEDURE populate_grid (
125 p_zone_id NUMBER,
126 p_org_id NUMBER,
127 x_record_count OUT NOCOPY NUMBER,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_data OUT NOCOPY VARCHAR2
130 ) IS
131
132 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
133
134 l_progress_indicator VARCHAR2(10) := '0';
135 l_module_name VARCHAR2(15) := 'POPULATE_GRID';
136
137 BEGIN
138
139 DEBUG('In procedure :', l_module_name,0);
140
141 IF l_debug > 0 THEN
142
143 DEBUG ('p_zone_id ' || p_zone_id, l_module_name, 9);
144 DEBUG ('p_org_id ' || p_org_id, l_module_name, 9);
145
146 END IF;
147
148 l_progress_indicator := '10';
149
150 /**
151 * This procedure makes use of global data structures, these must be initialized
152 * if not already initialized
153 *
154 **/
155
156 IF NOT g_initialized THEN
157
158 initialize;
159
160 END IF;
161
162 l_progress_indicator := '20';
163
164 DELETE FROM wms_zone_locators_temp;
165
166 IF l_debug > 0 THEN
167 DEBUG ('deleted from wms_zone_locators_temp ' || SQL%ROWCOUNT,
168 l_module_name,
169 9
170 );
171 END IF;
172
173 l_progress_indicator := '30';
174
175 INSERT INTO wms_zone_locators_temp(
176 message
177 , message_id
178 , inventory_location_id
179 , locator_name
180 , subinventory_code
181 , picking_order
182 , dropping_order
183 , locator_status
184 , subinventory_status
185 , locator_status_code
186 , subinventory_status_code
187 , inventory_location_type
188 , subinventory_type
189 , locator_type_meaning
190 , subinventory_type_meaning
191 , organization_id) (
192 SELECT NULL
193 , TO_NUMBER(NULL) message_id
194 , wzl.inventory_location_id
195 , milk.concatenated_segments locator_name
196 , wzl.subinventory_code
197 , milk.picking_order
198 , milk.dropping_order
199 , milk.status_id locator_status
200 , msi.status_id subinventory_status
201 , mms1.status_code locator_status_code
202 , mms2.status_code subinventory_status_code
203 , milk.inventory_location_type
204 , msi.subinventory_type
205 , DECODE (milk.inventory_location_type,
206 1, g_locator_types (1),
207 2, g_locator_types (2),
208 3, g_locator_types (3),
209 4, g_locator_types (4),
210 5, g_locator_types (5),
211 6, g_locator_types (6),
212 7, g_locator_types (7),
213 -- Default value is Storage,i.e. 3
214 g_locator_types (3)
215 )
216 , DECODE (msi.subinventory_type,
217 1, g_subinventory_types (1),
218 2, g_subinventory_types (2),
219 -- Default value should be Storage..i.e 1
220 g_subinventory_types(1)
221 )
222 , p_org_id
223 FROM wms_zone_locators wzl,
224 mtl_item_locations_kfv milk,
225 mtl_secondary_inventories msi,
226 mtl_material_statuses mms1,
227 mtl_material_statuses mms2
228 WHERE wzl.zone_id = p_zone_id
229 AND wzl.organization_id = p_org_id
230 AND NVL(wzl.entire_sub_flag,'N') = 'N'
231 AND wzl.organization_id = msi.organization_id
232 AND wzl.subinventory_code = msi.secondary_inventory_name
233 AND wzl.organization_id = milk.organization_id
234 AND wzl.subinventory_code = milk.subinventory_code
235 AND wzl.inventory_location_id = milk.inventory_location_id
236 AND mms1.status_id(+) = milk.status_id
237 AND mms2.status_id(+) = msi.status_id
238 UNION
239 SELECT NULL
240 , TO_NUMBER(NULL) message_id
241 , wzl.inventory_location_id
242 , g_all_locators_message locator_name
243 , wzl.subinventory_code
244 , TO_NUMBER(NULL) picking_order
245 , TO_NUMBER(NULL) dropping_orders
246 , TO_NUMBER(NULL) locator_status
247 , msi.status_id subinventory_status
248 , NULL locator_status_code
249 , mms.status_code subinventory_status_code
250 , TO_NUMBER(NULL)
251 , msi.subinventory_type
252 , NULL
253 , DECODE (msi.subinventory_type,
254 1, g_subinventory_types (1),
255 2, g_subinventory_types (2),
256 -- The default value should be Storage, i.e. 1
257 g_subinventory_types (1)
258 )
259 , p_org_id
260 FROM wms_zone_locators wzl,
261 mtl_secondary_inventories msi,
262 mtl_material_statuses mms
263 WHERE wzl.zone_id = p_zone_id
264 AND wzl.organization_id = p_org_id
265 AND NVL(wzl.entire_sub_flag,'N') = 'Y'
266 AND wzl.organization_id = msi.organization_id
267 AND wzl.subinventory_code = msi.secondary_inventory_name
268 AND mms.status_id = msi.status_id
269 );
270
271 IF l_debug > 0 THEN
272
273 DEBUG ('no. of records in serted ' || SQL%ROWCOUNT, l_module_name, 9);
274
275 END IF;
276
277 l_progress_indicator := '40';
278
279 x_return_status := fnd_api.g_ret_sts_success;
280
281 DEBUG('Call Success', l_module_name,0);
282
283 EXCEPTION
284
285 WHEN OTHERS THEN
286
287 DEBUG ('Unexpected exception : '|| l_progress_indicator
288 ||' : ' || SQLERRM,
289 l_module_name,
290 9
291 );
292 x_return_status := fnd_api.g_ret_sts_unexp_error;
293 --x_msg_count := 1;
294 x_msg_data := substr(SQLERRM, 200);
295
296 END populate_grid;
297
298 /**
299 * Using the filter criteria given in the Add Locators form,
300 * inserts the locators into the table WMS_ZONE_LOCATORS_TEMP.
301 *
302 * @param p_fm_zone_id from_zone_id in the range. Will have a
303 * null value if the user doesnt choose a from_zone
304 * @param p_to_zone_id to_zone_id in the range. Can have a null
305 * value if the user doesnt choose a to_zone
306 * @param p_current_zone_id The zone_id of the current zone,
307 * for which more locators are being added
308 * @param p_fm_sub_code From Subinventory code
309 * @param p_to_sub_code To Subinventory Code
310 * @param p_fm_loc_id From Locator Id in a range of locators.
311 * Should contain a value only if either
312 * p_fm_sub_code or p_to_sub_code is populated.
313 * @param p_to_loc_id To Locator Id in a range of locators.
314 * Should contain a value only if either
315 * p_fm_sub_code or p_to_sub_code is populated.
316 * @param p_subinventory_status Status id of the subinventories
317 * @param p_locator_status Status id of the locators
318 * @param p_subinventory_type Subinventory Type
319 * @param p_locator_type Locator Type
320 * @param p_fm_picking_order Picking order of the Locators
321 * @param p_to_picking_order Picking order of the Locators
322 * @param p_fm_dropping_order Dropping order of the Locators
323 * @param p_to_dropping_order Dropping order of the Locators
324 * @param p_organization_id Organization identifier
325 **/
326 PROCEDURE add_locators_to_grid (
327 p_fm_zone_id IN NUMBER DEFAULT NULL,
328 p_to_zone_id IN NUMBER DEFAULT NULL,
329 p_current_zone_id IN NUMBER DEFAULT NULL,
330 p_fm_sub_code IN VARCHAR2 DEFAULT NULL,
331 p_to_sub_code IN VARCHAR2 DEFAULT NULL,
332 p_fm_loc_id IN NUMBER DEFAULT NULL,
333 p_to_loc_id IN NUMBER DEFAULT NULL,
334 p_subinventory_status IN NUMBER DEFAULT NULL,
335 p_locator_status IN NUMBER DEFAULT NULL,
336 p_subinventory_type IN NUMBER DEFAULT NULL,
337 p_locator_type IN NUMBER DEFAULT NULL,
338 p_fm_picking_order IN NUMBER DEFAULT NULL,
339 p_to_picking_order IN NUMBER DEFAULT NULL,
340 p_fm_dropping_order IN NUMBER DEFAULT NULL,
341 p_to_dropping_order IN NUMBER DEFAULT NULL,
342 p_organization_id IN NUMBER,
343 p_mode IN NUMBER DEFAULT NULL) IS
344 l_insert_str VARCHAR2 (2000);
345 l_select_str VARCHAR2 (2000);
346 l_from_str VARCHAR2 (2000);
347 l_where_str VARCHAR2 (2000);
348 g_add_locator_message wms_zone_locators_temp.MESSAGE%TYPE;
349 l_query_str VARCHAR2 (4000);
350 l_query_handle NUMBER;
351 l_query_count NUMBER;
352 l_progress VARCHAR2 (10) := '0';
353 l_is_all_locators BOOLEAN := FALSE;
354
355 l_module_name VARCHAR2 (30) := 'ADD_LOCATORS_TO_GRID';
356 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
357 l_progress_indicator VARCHAR2(10) := '0';
358
359 -- Zone enhancement
360 l_jtf_message wms_zone_locators_temp.MESSAGE%TYPE;
361 l_jtf_message_id VARCHAR2(4) := 'NULL';
362
363 -- bug 3659062
364 l_fm_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
365 l_to_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
366
367 BEGIN
368
369 DEBUG('In procedure :', l_module_name,0);
370
371 IF (l_debug = 1) THEN
372
373 DEBUG (' p_fm_zone_id==> ' || p_fm_zone_id, l_module_name, 9);
374 DEBUG (' p_to_zone_id==> ' || p_to_zone_id, l_module_name, 9);
375 DEBUG (' p_current_zone_id==> ' || p_current_zone_id,
376 l_module_name,
377 9
378 );
379 DEBUG (' p_fm_sub_code==> ' || p_fm_sub_code, l_module_name, 9);
380 DEBUG (' p_to_sub_code==> ' || p_to_sub_code, l_module_name, 9);
381 DEBUG (' p_fm_loc_id==> ' || p_fm_loc_id, l_module_name, 9);
382 DEBUG (' p_to_loc_id==> ' || p_to_loc_id, l_module_name, 9);
383 DEBUG (' p_subinventory_status==> ' || p_subinventory_status,
384 l_module_name,
385 9
386 );
387 DEBUG (' p_locator_status==> ' || p_locator_status, l_module_name,
388 9);
389 DEBUG (' p_subinventory_type==> ' || p_subinventory_type,
390 l_module_name,
391 9
392 );
393 DEBUG (' p_locator_type==> ' || p_locator_type, l_module_name, 9);
394 DEBUG (' p_fm_picking_order==> ' || p_fm_picking_order,
395 l_module_name,
396 9
397 );
398 DEBUG (' p_to_picking_order==> ' || p_to_picking_order,
399 l_module_name,
400 9
401 );
402 DEBUG (' p_fm_dropping_order==> ' || p_fm_dropping_order,
403 l_module_name,
404 9
405 );
406 DEBUG (' p_to_dropping_order==> ' || p_to_dropping_order,
407 l_module_name,
408 9
409 );
410 DEBUG (' p_mode==> ' || p_mode,
411 l_module_name,
412 9
413 );
414
415 END IF; /* Debug = 1 */
416
417
418 l_progress_indicator := '10';
419
420 /**
421 * This procedure makes use of global data structures, these must be initialized
422 * if not already initialized
423 *
424 **/
425
426 IF NOT g_initialized THEN
427
428 initialize;
429
430 END IF;
431
432
433 -- enhancement
434
435 IF(Nvl(p_mode, 1) = 1) THEN
436 -- only in view/modify mode, populate message when OK is pressed
437 l_jtf_message_id := '1';
438 l_jtf_message := wms_zones_pvt.g_add_locators_message;
439 ELSE
440 -- clear the temp table if OK was pressed in ADD or REMOVE mode
441 DELETE wms_zone_locators_temp;
442 END IF;
443
444 l_progress_indicator := '20';
445
446 -- bug 3659062
447
448 IF(p_fm_loc_id IS NOT NULL AND p_fm_loc_id <> -999) THEN
449 SELECT concatenated_segments
450 INTO l_fm_loc_name
451 FROM mtl_item_locations_kfv
452 WHERE inventory_location_id = p_fm_loc_id
453 AND organization_id = p_organization_id;
454 END IF;
455
456 IF(p_to_loc_id IS NOT NULL AND p_to_loc_id <> -999) THEN
457 SELECT concatenated_segments
458 INTO l_to_loc_name
459 FROM mtl_item_locations_kfv
460 WHERE inventory_location_id = p_to_loc_id
461 AND organization_id = p_organization_id;
462 END IF;
463
464 l_insert_str :=
465 'INSERT INTO wms_zone_locators_temp(
466 message,
467 message_id,
468 inventory_location_id,
469 locator_name,
470 subinventory_code,
471 picking_order,
472 dropping_order,
473 locator_status,
474 subinventory_status,
475 locator_status_code,
476 subinventory_status_code,
477 inventory_location_type,
478 subinventory_type,
479 locator_type_meaning,
480 subinventory_type_meaning,
481 organization_id)';
482
483 l_progress_indicator := '30';
484
485 IF p_fm_zone_id IS NOT NULL THEN
486
487 l_progress_indicator := '40';
488
489 l_is_all_locators := TRUE;
490
491 l_select_str :=
492 '(SELECT '''
493 || l_jtf_message
494 || ''','
495 || l_jtf_message_id ||' , wzlv.inventory_location_id,
496 nvl(wzlv.locator_name,:all_locators),
497 wzlv.subinventory_code,
498 wzlv.picking_order,
499 wzlv.dropping_order,
500 wzlv.locator_status,
501 wzlv.subinventory_status,
502 mms1.status_code locator_status_code,
503 mms2.status_code subinventory_status_code,';
504
505 DEBUG ('10 l_select_str is ' || l_select_str, l_module_name, 9);
506 l_select_str :=
507 l_select_str
508 || 'wzlv.inventory_location_type, wzlv.subinventory_type, ';
509 DEBUG ('20 l_insert_str is ' || l_select_str, ' add_locators_grid',
510 9);
511 l_select_str :=
512 l_select_str
513 || 'decode(wzlv.inventory_location_type,1, '''
514 || g_locator_types (1)
515 || ''',2,'''
516 || g_locator_types (2)
517 || ''',3,'''
518 || g_locator_types (3)
519 || ''', 4, '''
520 || g_locator_types (4)
521 || ''', 5,'''
522 || g_locator_types (5)
523 || ''', 6,'''
524 || g_locator_types (6)
525 || ''', 7,'''
526 || g_locator_types (7)
527 || ''', '''
528 || g_locator_types (3)
529 || '''),';
530 DEBUG ('30 l_insert_str is ' || l_select_str, ' add_locators_grid',
531 9);
532 l_select_str :=
533 l_select_str
534 || 'decode(wzlv.subinventory_type,1, '''
535 || g_subinventory_types (1)
536 || ''',2, '''
537 || g_subinventory_types (2)
538 || ''', '''
539 || g_subinventory_types (1)
540 || ''') , '||p_organization_id;
541 DEBUG ('40 l_select_str is ' || l_select_str, ' add_locators_grid',
542 9);
543 l_from_str :=
544 ' from wms_zone_locators_v wzlv, mtl_material_statuses mms1, mtl_material_statuses mms2';
545 DEBUG ('l_from_str is ' || l_from_str, l_module_name, 9);
546 l_where_str :=
547 l_where_str
548 || ' where mms1.status_id(+) = wzlv.locator_status ';
549 l_where_str :=
550 l_where_str
551 || ' and mms2.status_id(+) = wzlv.subinventory_status ';
552 l_where_str :=
553 l_where_str
554 || ' and zone_id between :fm_zone_id and nvl(:to_zone_id,:fm_zone_id) ';
555 l_where_str :=
556 l_where_str
557 || ' and wzlv.organization_id = :organization_id ';
558 DEBUG ('10 l_where_str is ' || l_where_str, ' add_locators_grid', 9);
559
560 IF p_fm_sub_code IS NOT NULL AND p_to_sub_code IS NOT NULL
561 THEN
562 l_where_str :=
563 l_where_str
564 || ' and wzlv.subinventory_code between :fm_sub_code and :to_sub_code ';
565 DEBUG ('20 l_where_str is ' || l_where_str,
566 ' add_locators_grid',
567 9
568 );
569 ELSIF p_fm_sub_code IS NOT NULL AND p_to_sub_code IS NULL
570 THEN
571 l_where_str :=
572 l_where_str
573 || ' and wzlv.subinventory_code >= :fm_sub_code ';
574 DEBUG ('30 l_where_str is ' || l_where_str,
575 ' add_locators_grid',
576 9
577 );
578 ELSIF p_fm_sub_code IS NULL AND p_to_sub_code IS NOT NULL
579 THEN
580 l_where_str :=
581 l_where_str
582 || ' and wzlv.subinventory_code <= :to_sub_code ';
583 DEBUG ('40 l_where_str is ' || l_where_str,
584 ' add_locators_grid',
585 9
586 );
587 END IF;
588
589 IF p_fm_loc_id IS NOT NULL AND p_to_loc_id IS NOT NULL
590 THEN
591 l_where_str :=
592 l_where_str
593 || ' and wzlv.locator_name between :fm_loc_name and :to_loc_name'; -- bug 3659062
594
595 DEBUG ('50 l_where_str is ' || l_where_str,
596 ' add_locators_grid',
597 9
598 );
599 ELSIF p_fm_loc_id IS NOT NULL AND p_to_loc_id IS NULL
600 THEN
601 l_where_str :=
602 l_where_str
603 || ' and wzlv.locator_name >= :fm_loc_name'; -- bug 3659062
604
605 DEBUG ('60 l_where_str is ' || l_where_str,
606 ' add_locators_grid',
607 9
608 );
609 ELSIF p_fm_loc_id IS NULL AND p_to_loc_id IS NOT NULL
610 THEN
611 l_where_str :=
612 l_where_str
613 || ' and wzlv.locator_name <= :to_loc_name'; -- bug 3659062
614
615 DEBUG ('70 l_where_str is ' || l_where_str,
616 ' add_locators_grid',
617 9
618 );
619 END IF;
620
621 IF p_fm_picking_order IS NOT NULL AND p_to_picking_order IS NOT NULL
622 THEN
623 l_where_str :=
624 l_where_str
625 || ' and wzlv.picking_order between :fm_picking_order and :to_picking_order';
626 DEBUG ('80 l_where_str is ' || l_where_str,
627 ' add_locators_grid',
628 9
629 );
630 ELSIF p_fm_picking_order IS NOT NULL AND p_to_picking_order IS NULL
631 THEN
632 l_where_str :=
633 l_where_str
634 || ' and wzlv.picking_order >= :fm_picking_order';
635 DEBUG ('90 l_where_str is ' || l_where_str,
636 ' add_locators_grid',
637 9
638 );
639 ELSIF p_fm_picking_order IS NULL AND p_to_picking_order IS NOT NULL
640 THEN
641 l_where_str :=
642 l_where_str
643 || ' and wzlv.picking_order <= :to_picking_order';
644 DEBUG ('100 l_where_str is ' || l_where_str,
645 ' add_locators_grid',
646 9
647 );
648 END IF;
649
650 IF p_fm_dropping_order IS NOT NULL
651 AND p_to_dropping_order IS NOT NULL
652 THEN
653 l_where_str :=
654 l_where_str
655 || ' and wzlv.dropping_order between :fm_dropping_order and :to_dropping_order)';
656 DEBUG ('110 l_where_str is ' || l_where_str,
657 ' add_locators_grid',
658 9
659 );
660 ELSIF p_fm_dropping_order IS NOT NULL AND p_to_dropping_order IS NULL
661 THEN
662 l_where_str :=
663 l_where_str
664 || ' and wzlv.dropping_order >= :fm_dropping_order';
665 DEBUG ('120 l_where_str is ' || l_where_str,
666 ' add_locators_grid',
667 9
668 );
669 ELSIF p_fm_dropping_order IS NULL AND p_to_dropping_order IS NOT NULL
670 THEN
671 l_where_str :=
672 l_where_str
673 || ' and wzlv.dropping_order <= :to_dropping_order';
674 DEBUG ('130 l_where_str is ' || l_where_str,
675 ' add_locators_grid',
676 9
677 );
678 END IF;
679
680 IF p_locator_type IS NOT NULL
681 THEN
682 l_where_str :=
683 l_where_str
684 || ' and wzlv.inventory_location_type = :locator_type ';
685 DEBUG ('140 l_where_str is ' || l_where_str,
686 ' add_locators_grid',
687 9
688 );
689 END IF;
690
691 IF p_subinventory_type IS NOT NULL
692 THEN
693 l_where_str :=
694 l_where_str
695 || ' and wzlv.subinventory_type = :subinventory_type ';
696 DEBUG ('150 l_where_str is ' || l_where_str,
697 ' add_locators_grid',
698 9
699 );
700 END IF;
701
702 IF p_locator_status IS NOT NULL
703 THEN
704 l_where_str :=
705 l_where_str
706 || ' and wzlv.locator_status = :locator_status ';
707 DEBUG ('160 l_where_str is ' || l_where_str,
708 ' add_locators_grid',
709 9
710 );
711 END IF;
712
713 IF p_subinventory_status IS NOT NULL
714 THEN
715 l_where_str :=
716 l_where_str
717 || ' and wzlv.subinventory_status = :sub_status ';
718 DEBUG ('170 l_where_str is ' || l_where_str,
719 ' add_locators_grid',
720 9
721 );
722 END IF;
723 ELSE --zone_id is null
724 DEBUG ('zone id is null ' || wms_zones_pvt.g_add_locators_message,
725 l_module_name,
726 9
727 );
728 l_is_all_locators := FALSE;
729 l_select_str :=
730 '(SELECT '''
731 || l_jtf_message
732 || ''','
733 || l_jtf_message_id ||',
734 milk.inventory_location_id,
735 milk.concatenated_segments,
736 milk.subinventory_code,
737 milk.picking_order,
738 milk.dropping_order,
739 milk.status_id,
740 msi.status_id subinventory_status,
741 mms1.status_code locator_status_code,
742 mms2.status_code subinventory_status_code,';
743 DEBUG ('10 l_select_str is ' || l_select_str, ' add_locators_grid',
744 9);
745 l_select_str :=
746 l_select_str
747 || 'inventory_location_type, subinventory_type, ';
748 DEBUG ('20 l_select_str is ' || l_select_str, ' add_locators_grid',
749 9);
750 l_select_str :=
751 l_select_str
752 || 'decode(milk.inventory_location_type,1, '''
753 || g_locator_types (1)
754 || ''',2,'''
755 || g_locator_types (2)
756 || ''',3,'''
757 || g_locator_types (3)
758 || ''', 4, '''
759 || g_locator_types (4)
760 || ''', 5,'''
761 || g_locator_types (5)
762 || ''', 6,'''
763 || g_locator_types (6)
764 || ''', 7,'''
765 || g_locator_types (7)
766 || ''', '''
767 || g_locator_types (3)
768 || '''),';
769 DEBUG ('30 l_select_str is ' || l_select_str, ' add_locators_grid',
770 9);
771 l_select_str :=
772 l_select_str
773 || 'decode(msi.subinventory_type,1, '''
774 || g_subinventory_types (1)
775 || ''',2, '''
776 || g_subinventory_types (2)
777 || ''', '''
778 || g_subinventory_types (1)
779 || ''') , '||p_organization_id;
780 DEBUG ('40 l_select_str is ' || l_select_str, ' add_locators_grid',
781 9);
782 l_from_str :=
783 ' from mtl_item_locations_kfv milk, mtl_secondary_inventories msi,
784 mtl_material_statuses mms1,mtl_material_statuses mms2 ';
785 DEBUG ('10 l_from_str is ' || l_from_str, ' add_locators_grid', 9);
786 l_where_str := ' where 1=1 ';
787 DEBUG ('10 l_where_str is ' || l_where_str, ' add_locators_grid', 9);
788 l_where_str :=
789 l_where_str
790 || ' and mms1.status_id(+) = milk.status_id ';
791 l_where_str :=
792 l_where_str
793 || ' and mms2.status_id(+) = msi.status_id';
794 l_where_str :=
795 l_where_str
796 || ' and msi.organization_id = :organization_id';
797 l_where_str :=
798 l_where_str
799 || ' and milk.organization_id = :organization_id';
800 l_where_str :=
801 l_where_str
802 || ' and milk.subinventory_code = msi.secondary_inventory_name ';
803 l_where_str :=
804 l_where_str
805 || ' and nvl(milk.disable_date,SYSDATE) >= SYSDATE';
806 DEBUG ('20 l_where_str is ' || l_where_str, ' add_locators_grid', 9);
807
808 /* p_fm_zone_id is null. There are 3 cases here- */
809 IF p_fm_sub_code IS NOT NULL OR p_to_sub_code IS NOT NULL
810 THEN
811 IF p_fm_sub_code IS NOT NULL AND p_to_sub_code IS NOT NULL
812 THEN
813 l_where_str :=
814 l_where_str
815 || ' and milk.subinventory_code between :fm_sub_code and :to_sub_code ';
816 DEBUG ('20 l_where_str is ' || l_where_str,
817 ' add_locators_grid',
818 9
819 );
820 ELSIF p_fm_sub_code IS NOT NULL AND p_to_sub_code IS NULL
821 THEN
822 l_where_str :=
823 l_where_str
824 || ' and milk.subinventory_code >= :fm_sub_code ';
825 DEBUG ('30 l_where_str is ' || l_where_str,
826 ' add_locators_grid',
827 9
828 );
829 ELSIF p_fm_sub_code IS NULL AND p_to_sub_code IS NOT NULL
830 THEN
831 l_where_str :=
832 l_where_str
833 || ' and milk.subinventory_code <= :to_sub_code ';
834 DEBUG ('40 l_where_str is ' || l_where_str,
835 ' add_locators_grid',
836 9
837 );
838 ELSE
839 l_where_str := l_where_str || ' and null ';
840 DEBUG ('50 l_where_str is ' || l_where_str,
841 ' add_locators_grid',
842 9
843 );
844 END IF;
845
846 /* Case 1 - When only a range of subinventories is selected. Locators
847 * field is null. Then We have to chose all the locators in these subs
848 * which are not already present in the current zone -This includes
849 * "All Locators" option also
850 */
851 IF p_fm_loc_id IS NULL AND p_to_loc_id IS NULL
852 THEN
853 NULL;
854 ELSIF p_fm_loc_id IS NOT NULL
855 AND p_fm_loc_id <> -999
856 AND p_to_loc_id IS NOT NULL
857 AND p_to_loc_id <> -999
858 THEN
859 l_where_str :=
860 l_where_str
861 || ' and milk.concatenated_segments between :fm_loc_name and :to_loc_name '; -- bug 3659062
862
863 DEBUG ('20 l_where_str is ' || l_where_str,
864 ' add_locators_grid',
865 9
866 );
867 ELSIF p_fm_loc_id IS NOT NULL AND p_to_loc_id IS NULL
868 THEN
869 l_where_str :=
870 l_where_str
871 || ' and milk.concatenated_segments >= :fm_loc_name '; -- bug 3659062
872
873 DEBUG ('30 l_where_str is ' || l_where_str,
874 ' add_locators_grid',
875 9
876 );
877 ELSIF p_fm_loc_id IS NULL AND p_to_loc_id IS NOT NULL
878 THEN
879 l_where_str :=
880 l_where_str
881 || ' and milk.concatenated_segments <= :fm_loc_name '; -- bug 3659062
882
883 DEBUG ('40 l_where_str is ' || l_where_str,
884 ' add_locators_grid',
885 9
886 );
887 END IF;
888 END IF;
889
890 IF p_fm_picking_order IS NOT NULL AND p_to_picking_order IS NOT NULL
891 THEN
892 l_where_str :=
893 l_where_str
894 || ' and milk.picking_order between :fm_picking_order and :to_picking_order';
895 DEBUG ('50 l_where_str is ' || l_where_str,
896 ' add_locators_grid',
897 9
898 );
899 ELSIF p_fm_picking_order IS NOT NULL AND p_to_picking_order IS NULL
900 THEN
901 l_where_str :=
902 l_where_str
903 || ' and milk.picking_order >= :fm_picking_order';
904 DEBUG ('60 l_where_str is ' || l_where_str,
905 ' add_locators_grid',
906 9
907 );
908 ELSIF p_fm_picking_order IS NULL AND p_to_picking_order IS NOT NULL
909 THEN
910 l_where_str :=
911 l_where_str
912 || ' and milk.picking_order <= :to_picking_order';
913 DEBUG ('70 l_where_str is ' || l_where_str,
914 ' add_locators_grid',
915 9
916 );
917 END IF;
918
919 IF p_fm_dropping_order IS NOT NULL
920 AND p_to_dropping_order IS NOT NULL
921 THEN
922 l_where_str :=
923 l_where_str
924 || ' and milk.dropping_order between :fm_dropping_order and :to_dropping_order';
925 DEBUG ('80 l_where_str is ' || l_where_str,
926 ' add_locators_grid',
927 9
928 );
929 ELSIF p_fm_dropping_order IS NOT NULL AND p_to_dropping_order IS NULL
930 THEN
931 l_where_str :=
932 l_where_str
933 || ' and milk.dropping_order >= :fm_dropping_order';
934 DEBUG ('90 l_where_str is ' || l_where_str,
935 ' add_locators_grid',
936 9
937 );
938 ELSIF p_fm_dropping_order IS NULL AND p_to_dropping_order IS NOT NULL
939 THEN
940 l_where_str :=
941 l_where_str
942 || ' and milk.dropping_order <= :to_dropping_order';
943 DEBUG ('100 l_where_str is ' || l_where_str,
944 ' add_locators_grid',
945 9
946 );
947 END IF;
948
949 IF p_locator_type IS NOT NULL
950 THEN
951 l_where_str :=
952 l_where_str
953 || ' and milk.inventory_location_type = :locator_type ';
954 DEBUG ('110 l_where_str is ' || l_where_str,
955 ' add_locators_grid',
956 9
957 );
958 END IF;
959
960 IF p_subinventory_type IS NOT NULL
961 THEN
962 l_where_str :=
963 l_where_str
964 || ' and msi.subinventory_type = :subinventory_type ';
965 DEBUG ('120 l_where_str is ' || l_where_str,
966 ' add_locators_grid',
967 9
968 );
969 END IF;
970
971 IF p_locator_status IS NOT NULL
972 THEN
973 l_where_str :=
974 l_where_str
975 || ' and milk.status_id = :locator_status ';
976 DEBUG ('130 l_where_str is ' || l_where_str,
977 ' add_locators_grid',
978 9
979 );
980 END IF;
981
982 IF p_subinventory_status IS NOT NULL
983 THEN
984 l_where_str := l_where_str || ' and msi.status_id = :sub_status ';
985 DEBUG ('140 l_where_str is ' || l_where_str,
986 ' add_locators_grid',
987 9
988 );
989 END IF;
990 END IF;
991
992 -- Zone enhancement
993 -- It's a bug fix: no matter what, we need to restrict locators returned
994 -- based on locators belonging to the current zone.
995 -- In add and view/modify mode exclude those locators belonging to the current zone already;
996 -- in remove mode, include only locators belonging to the current zone.
997
998 IF (Nvl(p_mode, 1) <> 3) THEN
999 l_where_str :=
1000 l_where_str
1001 || ' and inventory_location_id not in ';
1002 ELSE -- remove mode
1003 -- Zone enhancement
1004 -- In remove mode, retrieve the intersect of query criteria and current zone
1005 l_where_str :=
1006 l_where_str
1007 || ' and inventory_location_id in ';
1008 END IF;
1009
1010
1011 DEBUG ('60 l_where_str is ' || l_where_str,
1012 ' add_locators_grid',
1013 9
1014 );
1015 l_where_str :=
1016 l_where_str
1017 || ' (select locator_id from wms_zone_locators_all_v
1018 where zone_id = :cur_zone_id) ';
1019 DEBUG ('70 l_where_str is ' || l_where_str,
1020 ' add_locators_grid',
1021 9
1022 );
1023
1024
1025 DEBUG ('from string ' || l_from_str, l_module_name, 9);
1026 DEBUG ('where string ' || l_where_str, l_module_name, 9);
1027 l_select_str := l_select_str || l_from_str || l_where_str || ')';
1028 DEBUG ('select string ' || l_select_str, l_module_name, 9);
1029 l_query_str := l_insert_str || l_select_str;
1030 DEBUG ('final query string ' || l_query_str, l_module_name, 9);
1031 DEBUG ('handling and parsing the query', l_module_name, 9);
1032 l_query_handle := DBMS_SQL.open_cursor;
1033 DBMS_SQL.parse (l_query_handle, l_query_str, DBMS_SQL.native);
1034 DEBUG ('after parsing -- enw ', l_module_name, 9);
1035 DEBUG ('parsing ', l_module_name, 9);
1036
1037 IF p_organization_id IS NOT NULL
1038 THEN
1039 DEBUG ('Assigning Organization' || p_organization_id,
1040 l_module_name,
1041 9
1042 );
1043 DBMS_SQL.bind_variable (l_query_handle,
1044 ':organization_id',
1045 p_organization_id
1046 );
1047 END IF;
1048
1049 -- Zone enhancement
1050 -- It's a bug fix: no matter what, we need to restrict locators returned
1051 -- based on locators belonging to the current zone.
1052 DEBUG ('80-3', 'add_locators_grid', 9);
1053 DBMS_SQL.bind_variable (l_query_handle,
1054 'cur_zone_id',
1055 p_current_zone_id
1056 );
1057 DEBUG ('60', 'add_locators_grid', 9);
1058
1059 IF l_is_all_locators
1060 THEN
1061 DEBUG ('assigning locators: ' || g_all_locators_message, l_module_name, 9);
1062 DBMS_SQL.bind_variable (l_query_handle,
1063 'all_locators',
1064 g_all_locators_message
1065 );
1066 DEBUG ('150', 'add_locators_grid', 9);
1067 END IF;
1068
1069 IF p_fm_loc_id IS NOT NULL AND p_fm_loc_id <> -999
1070 THEN
1071 DEBUG ('fm locid is not null', 'add_locators_grid', 9);
1072 DBMS_SQL.bind_variable (l_query_handle, 'fm_loc_name', l_fm_loc_name); -- bug 3659062
1073
1074 DEBUG ('90-1', 'add_locators_grid', 9);
1075 END IF;
1076
1077 IF p_to_loc_id IS NOT NULL AND p_to_loc_id <> -999
1078 THEN
1079 DEBUG ('to locid is not null', 'add_locators_grid', 9);
1080 DBMS_SQL.bind_variable (l_query_handle, 'to_loc_name', l_to_loc_name); -- bug 3659062
1081
1082 DEBUG ('90-2', 'add_locators_grid', 9);
1083 END IF;
1084
1085 IF p_subinventory_status IS NOT NULL
1086 THEN
1087 DEBUG ('assigning substatus ' || p_subinventory_status,
1088 l_module_name,
1089 9
1090 );
1091 DBMS_SQL.bind_variable (l_query_handle,
1092 'sub_status',
1093 p_subinventory_status
1094 );
1095 DEBUG ('70', 'add_locators_grid', 9);
1096 END IF;
1097
1098 IF p_locator_status IS NOT NULL
1099 THEN
1100 DEBUG ('assigning locator status ' || p_locator_status,
1101 'add_locators_grid',
1102 9
1103 );
1104 DBMS_SQL.bind_variable (l_query_handle,
1105 'locator_status',
1106 p_locator_status
1107 );
1108 DEBUG ('80', 'add_locators_grid', 9);
1109 END IF;
1110
1111 IF p_fm_sub_code IS NOT NULL
1112 THEN
1113 DEBUG ('Assigning fm_sub_code' || p_fm_sub_code, l_module_name, 9);
1114 DBMS_SQL.bind_variable (l_query_handle, 'fm_sub_code',
1115 p_fm_sub_code);
1116 DEBUG ('40', 'add_locators_grid', 9);
1117 END IF;
1118
1119 IF p_to_sub_code IS NOT NULL
1120 THEN
1121 DEBUG ('Assigning to_sub_code' || p_to_sub_code, l_module_name, 9);
1122 DBMS_SQL.bind_variable (l_query_handle, 'to_sub_code',
1123 p_to_sub_code);
1124 DEBUG ('50', 'add_locators_grid', 9);
1125 END IF;
1126
1127 DEBUG ('My changed pls', l_module_name, 9);
1128
1129 IF p_fm_zone_id IS NOT NULL
1130 THEN
1131 DEBUG ('assigning fmzone ' || p_fm_zone_id, l_module_name, 9);
1132 DBMS_SQL.bind_variable (l_query_handle, 'fm_zone_id', p_fm_zone_id);
1133 DEBUG ('10', 'add_locators_grid', 9);
1134 --END IF;
1135
1136 -- IF p_to_zone_id IS NOT NULL THEN
1137 DEBUG ('assigning to zone ' || p_to_zone_id, l_module_name, 9);
1138 DBMS_SQL.bind_variable (l_query_handle, 'to_zone_id', p_to_zone_id);
1139 DEBUG ('20', 'add_locators_grid', 9);
1140 END IF;
1141
1142 IF p_subinventory_type IS NOT NULL
1143 THEN
1144 DEBUG ('assigning sub type ' || p_subinventory_type,
1145 l_module_name,
1146 9
1147 );
1148 DBMS_SQL.bind_variable (l_query_handle,
1149 'subinventory_type',
1150 p_subinventory_type
1151 );
1152 DEBUG ('400', 'add_locators_grid', 9);
1153 END IF;
1154
1155 IF p_locator_type IS NOT NULL
1156 THEN
1157 DEBUG ('assigning loc type ' || p_locator_type, l_module_name, 9);
1158 DBMS_SQL.bind_variable (l_query_handle,
1159 'locator_type',
1160 p_locator_type
1161 );
1162 DEBUG ('410', 'add_locators_grid', 9);
1163 END IF;
1164
1165 IF p_fm_picking_order IS NOT NULL
1166 THEN
1167 DEBUG (' assigning fm picking order ' || p_fm_picking_order,
1168 l_module_name,
1169 9
1170 );
1171 DBMS_SQL.bind_variable (l_query_handle,
1172 'fm_picking_order',
1173 p_fm_picking_order
1174 );
1175 DEBUG ('110', 'add_locators_grid', 9);
1176 END IF;
1177
1178 IF p_to_picking_order IS NOT NULL
1179 THEN
1180 DEBUG (' assigning to picking order ' || p_to_picking_order,
1181 l_module_name,
1182 9
1183 );
1184 DBMS_SQL.bind_variable (l_query_handle,
1185 'to_picking_order',
1186 p_to_picking_order
1187 );
1188 DEBUG ('120', 'add_locators_grid', 9);
1189 END IF;
1190
1191 IF p_fm_dropping_order IS NOT NULL
1192 THEN
1193 DEBUG (' assigning fm dropping order ' || p_fm_dropping_order,
1194 l_module_name,
1195 9
1196 );
1197 DBMS_SQL.bind_variable (l_query_handle,
1198 'fm_dropping_order',
1199 p_fm_dropping_order
1200 );
1201 DEBUG ('130', 'add_locators_grid', 9);
1202 END IF;
1203
1204 IF p_to_dropping_order IS NOT NULL
1205 THEN
1206 DEBUG (' assigning to dropping order ' || p_to_dropping_order,
1207 l_module_name,
1208 9
1209 );
1210 DBMS_SQL.bind_variable (l_query_handle,
1211 'to_dropping_order',
1212 p_to_dropping_order
1213 );
1214 DEBUG ('140', 'add_locators_grid', 9);
1215 END IF;
1216
1217 /* Parse, bind and execute the dynamic query */
1218 /*
1219 IF p_fm_zone_id IS NOT NULL THEN
1220 DBMS_SQL.bind_variable(l_query_handle, 'fm_zone_id', p_fm_zone_id);
1221 DEBUG('10', 'add_locators_grid', 9);
1222 END IF;
1223
1224 IF p_to_zone_id IS NOT NULL THEN
1225 DBMS_SQL.bind_variable(l_query_handle, 'to_zone_id', p_to_zone_id);
1226 DEBUG('20', 'add_locators_grid', 9);
1227 END IF;
1228
1229 IF p_current_zone_id IS NOT NULL THEN
1230 DBMS_SQL.bind_variable(
1231 l_query_handle
1232 , 'current_zone_id'
1233 , p_current_zone_id
1234 );
1235 DEBUG('30', 'add_locators_grid', 9);
1236 END IF;
1237
1238 IF p_fm_sub_code IS NOT NULL THEN
1239 DBMS_SQL.bind_variable(l_query_handle, 'fm_sub_code', p_fm_sub_code);
1240 DEBUG('40', 'add_locators_grid', 9);
1241 END IF;
1242
1243 IF p_to_sub_code IS NOT NULL THEN
1244 DBMS_SQL.bind_variable(l_query_handle, 'to_sub_code', p_to_sub_code);
1245 DEBUG('50', 'add_locators_grid', 9);
1246 END IF;
1247
1248 IF p_fm_loc_id IS NOT NULL THEN
1249 DBMS_SQL.bind_variable(l_query_handle, 'fm_loc_name', l_fm_loc_name); -- bug 3659062
1250
1251 DEBUG('60', 'add_locators_grid', 9);
1252 END IF;
1253
1254 IF p_to_loc_id IS NOT NULL THEN
1255 DBMS_SQL.bind_variable(l_query_handle, 'to_loc_name', l_to_loc_name); -- bug 3659062
1256
1257 DEBUG('70', 'add_locators_grid', 9);
1258 END IF;
1259
1260 IF p_locator_status IS NOT NULL THEN
1261 DBMS_SQL.bind_variable(l_query_handle, 'locator_status', p_locator_status);
1262 DEBUG('90', 'add_locators_grid', 9);
1263 END IF;
1264
1265 IF p_subinventory_type IS NOT NULL THEN
1266 DBMS_SQL.bind_variable(
1267 l_query_handle
1268 , 'subinventory_type'
1269 , p_subinventory_type
1270 );
1271 DEBUG('100', 'add_locators_grid', 9);
1272 END IF;
1273
1274 --IF p_subinventory_status IS NOT NULL THEN
1275 DBMS_SQL.bind_variable(l_query_handle, ':sub_status', 1);
1276 DEBUG('70', 'add_locators_grid', 9);
1277 -- END IF;
1278
1279 IF p_fm_picking_order IS NOT NULL THEN
1280 DBMS_SQL.bind_variable(
1281 l_query_handle
1282 , 'fm_picking_order'
1283 , p_fm_picking_order
1284 );
1285 DEBUG('110', 'add_locators_grid', 9);
1286 END IF;
1287
1288 IF p_to_picking_order IS NOT NULL THEN
1289 DBMS_SQL.bind_variable(
1290 l_query_handle
1291 , 'to_picking_order'
1292 , p_to_picking_order
1293 );
1294 DEBUG('120', 'add_locators_grid', 9);
1295 END IF;
1296
1297 IF p_fm_dropping_order IS NOT NULL THEN
1298 DBMS_SQL.bind_variable(
1299 l_query_handle
1300 , 'fm_dropping_order'
1301 , p_fm_dropping_order
1302 );
1303 DEBUG('130', 'add_locators_grid', 9);
1304 END IF;
1305
1306 IF p_to_dropping_order IS NOT NULL THEN
1307 DBMS_SQL.bind_variable(
1308 l_query_handle
1309 , 'to_dropping_order'
1310 , p_to_dropping_order
1311 );
1312 DEBUG('140', 'add_locators_grid', 9);
1313 END IF;
1314
1315 IF p_all_locators IS NOT NULL THEN
1316 DBMS_SQL.bind_variable(l_query_handle, 'all_locators', p_all_locators);
1317 DEBUG('150', 'add_locators_grid', 9);
1318 END IF; */
1319 l_query_count := DBMS_SQL.EXECUTE (l_query_handle);
1320 COMMIT;
1321 DEBUG ('l_query_count ' || l_query_count, 'add_locators_grid', 9);
1322 END add_locators_to_grid;
1323
1324 /**
1325 * Contains code to insert records into wms_zones_b and
1326 * wms_zones_tl
1327
1328 * @param x_return_status Return Status - Success, Error, Unexpected Error
1329 * @param x_msg_data Contains any error messages added to the stack
1330 * @param x_msg_count Contains the count of the messages added to the stack
1331 * @param p_zone_id Zone_id
1332 * @param p_zone_name Name of the new Zone
1333 * @param p_description Description of the zone
1334 * @param enabled_flag Flag to indicate whether the zone is enabled or not. '
1335 Y' indicates that the zone is enabled.
1336 'N' indicates that the zone is not enabled.
1337 Any other value will be an error
1338 * @param disable_date The date when the zone will be disabled.
1339 This date cannot be less than the SYSDATE.
1340 * @param p_organization_id Current Organization id
1341 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
1342 * @param p_attribute1 Attribute1
1343 * @param p_attribute2 Attribute2
1344 * @param p_attribute3 Attribute3
1345 * @param p_attribute4 Attribute4
1346 * @param p_attribute5 Attribute5
1347 * @param p_attribute6 Attribute6
1348 * @param p_attribute7 Attribute7
1349 * @param p_attribute8 Attribute8
1350 * @param p_attribute9 Attribute9
1351 * @param p_attribute10 Attribute10
1352 * @param p_attribute11 Attribute11
1353 * @param p_attribute12 Attribute12
1354 * @param p_attribute13 Attribute13
1355 * @param p_attribute14 Attribute14
1356 * @param p_attribute15 Attribute15
1357 **/
1358 PROCEDURE insert_wms_zones (
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_data OUT NOCOPY VARCHAR2,
1361 x_msg_count OUT NOCOPY NUMBER,
1362 p_zone_id IN NUMBER,
1363 p_zone_name IN VARCHAR2,
1364 p_description IN VARCHAR2,
1365 p_enabled_flag IN VARCHAR2,
1366 p_labor_enabled IN VARCHAR2,
1367 p_disable_date IN DATE,
1368 p_organization_id IN NUMBER,
1369 p_attribute_category IN VARCHAR2,
1370 p_attribute1 IN VARCHAR2,
1371 p_attribute2 IN VARCHAR2,
1372 p_attribute3 IN VARCHAR2,
1373 p_attribute4 IN VARCHAR2,
1374 p_attribute5 IN VARCHAR2,
1375 p_attribute6 IN VARCHAR2,
1376 p_attribute7 IN VARCHAR2,
1377 p_attribute8 IN VARCHAR2,
1378 p_attribute9 IN VARCHAR2,
1379 p_attribute10 IN VARCHAR2,
1380 p_attribute11 IN VARCHAR2,
1381 p_attribute12 IN VARCHAR2,
1382 p_attribute13 IN VARCHAR2,
1383 p_attribute14 IN VARCHAR2,
1384 p_attribute15 IN VARCHAR2,
1385 p_creation_date IN DATE,
1386 p_created_by IN NUMBER,
1387 p_last_update_date IN DATE,
1388 p_last_updated_by IN NUMBER,
1389 p_last_update_login IN NUMBER
1390 ) IS
1391
1392 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1393 l_module_name VARCHAR2(20) := 'INSERT_WMS_ZONES';
1394 l_progress_indicator VARCHAR2(10) := '0';
1395
1396 l_return_status VARCHAR2(1);
1397 l_msg_data VARCHAR2(2000);
1398 l_msg_count NUMBER;
1399
1400 l_rowid VARCHAR2(200);
1401
1402 BEGIN
1403
1404 x_return_status := fnd_api.g_ret_sts_success;
1405
1406 DEBUG('In procedure :', l_module_name,0);
1407
1408 IF (l_debug = 1) THEN
1409
1410 DEBUG (' p_zone_id==> ' || p_zone_id, l_module_name, 9);
1411 DEBUG (' p_zone_name==> ' || p_zone_name, l_module_name, 9);
1412 DEBUG (' p_description==> ' || p_description, l_module_name, 9);
1413 DEBUG (' enabled_flag==> ' || p_enabled_flag, l_module_name, 9);
1414 DEBUG (' p_labor_enabled==> ' || p_labor_enabled, l_module_name, 9);
1415 DEBUG (' disable_date==> ' || p_disable_date, l_module_name, 9);
1416 DEBUG (' p_organization_id==> ' || p_organization_id,
1417 l_module_name,
1418 9
1419 );
1420 DEBUG (' p_attribute_category==> ' || p_attribute_category,
1421 l_module_name,
1422 9
1423 );
1424 DEBUG (' p_attribute1==> ' || p_attribute1, l_module_name, 9);
1425 DEBUG (' p_attribute2==> ' || p_attribute2, l_module_name, 9);
1426 DEBUG (' p_attribute3==> ' || p_attribute3, l_module_name, 9);
1427 DEBUG (' p_attribute4==> ' || p_attribute4, l_module_name, 9);
1428 DEBUG (' p_attribute5==> ' || p_attribute5, l_module_name, 9);
1429 DEBUG (' p_attribute6==> ' || p_attribute6, l_module_name, 9);
1430 DEBUG (' p_attribute7==> ' || p_attribute7, l_module_name, 9);
1431 DEBUG (' p_attribute8==> ' || p_attribute8, l_module_name, 9);
1432 DEBUG (' p_attribute9==> ' || p_attribute9, l_module_name, 9);
1433 DEBUG (' p_attribute10==> ' || p_attribute10, l_module_name, 9);
1434 DEBUG (' p_attribute11==> ' || p_attribute11, l_module_name, 9);
1435 DEBUG (' p_attribute12==> ' || p_attribute12, l_module_name, 9);
1436 DEBUG (' p_attribute13==> ' || p_attribute13, l_module_name, 9);
1437 DEBUG (' p_attribute14==> ' || p_attribute14, l_module_name, 9);
1438 DEBUG (' p_attribute15==> ' || p_attribute15, l_module_name, 9);
1439 DEBUG (' p_creation_date==> ' || p_creation_date,
1440 l_module_name,
1441 9
1442 );
1443 DEBUG (' p_last_update_date==> ' || p_last_update_date,
1444 l_module_name,
1445 9
1446 );
1447 DEBUG (' created_by==> ' || p_created_by, l_module_name, 9);
1448 DEBUG (' p_last_update_login==> ' || p_last_update_login,
1449 l_module_name,
1450 9
1451 );
1452 DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
1453 l_module_name,
1454 9
1455 );
1456
1457 END IF; /* l_debug = 1 */
1458
1459 l_progress_indicator := '10';
1460
1461 WMS_ZONES_PKG.INSERT_ROW (
1462 X_ROWID => l_rowid,
1463 X_ZONE_ID => p_zone_id,
1464 X_ATTRIBUTE_CATEGORY => p_attribute_category,
1465 X_ATTRIBUTE1 => p_attribute1,
1466 X_ATTRIBUTE2 => p_attribute2,
1467 X_ATTRIBUTE3 => p_attribute3,
1468 X_ATTRIBUTE4 => p_attribute4,
1469 X_ATTRIBUTE5 => p_attribute5,
1470 X_ATTRIBUTE6 => p_attribute6,
1471 X_ATTRIBUTE7 => p_attribute7,
1472 X_ATTRIBUTE8 => p_attribute8,
1473 X_ATTRIBUTE9 => p_attribute9,
1474 X_ATTRIBUTE10 => p_attribute10,
1475 X_ATTRIBUTE11 => p_attribute11,
1476 X_ATTRIBUTE12 => p_attribute12,
1477 X_ATTRIBUTE13 => p_attribute13,
1478 X_ATTRIBUTE14 => p_attribute14,
1479 X_ATTRIBUTE15 => p_attribute15,
1480 X_ORGANIZATION_ID => p_organization_id,
1481 X_DISABLE_DATE => p_disable_date,
1482 X_ENABLED_FLAG => p_enabled_flag,
1483 X_LABOR_ENABLED => p_labor_enabled,
1484 X_ZONE_NAME => p_zone_name,
1485 X_DESCRIPTION => p_description,
1486 X_CREATION_DATE => p_creation_date,
1487 X_CREATED_BY => p_created_by,
1488 X_LAST_UPDATE_DATE => p_last_update_date,
1489 X_LAST_UPDATED_BY => p_last_updated_by,
1490 X_LAST_UPDATE_LOGIN => p_last_update_login
1491 );
1492
1493 IF l_debug > 0 THEN
1494
1495 DEBUG (' Inserted row with rowid ' || l_rowid,
1496 l_module_name,
1497 9
1498 );
1499
1500 END IF;
1501
1502 x_return_status := fnd_api.g_ret_sts_success;
1503
1504 DEBUG('Call Success', l_module_name,0);
1505
1506 EXCEPTION
1507 WHEN OTHERS THEN
1508
1509 DEBUG('Unexpected Exception :' ||l_progress_indicator
1510 ||' : '||SQLERRM ,
1511 l_module_name,0);
1512
1513 x_return_status := fnd_api.g_ret_sts_unexp_error;
1514 x_msg_data := substr(SQLERRM, 200);
1515 x_msg_count := 1;
1516
1517 END insert_wms_zones;
1518
1519 /**
1520 * Contains code to update records in wms_zones_b and
1521 * wms_zones_tl
1522
1523 * @param x_return_status Return Status - Success, Error, Unexpected Error
1524 * @param x_msg_data Contains any error messages added to the stack
1525 * @param x_msg_count Contains the count of the messages added to the stack
1526 * @param p_zone_id Zone_id
1527 * @param p_zone_name Name of the new Zone
1528 * @param p_description Description of the zone
1529 * @param enabled_flag Flag to indicate whether the zone is enabled or not. 'Y' indicates that the zone is enabled 'N' indicates that the zone is not enabled. Any other value will be an error
1530 * @param disable_date The date when the zone will be disabled. This date cannot be less than the SYSDATE.
1531 * @param p_organization_id Current Organization id
1532 * @param p_attribute_category Attribute Category of the Zones Descriptive Flexfield
1533 * @param p_attribute1 Attribute1
1534 * @param p_attribute2 Attribute2
1535 * @param p_attribute3 Attribute3
1536 * @param p_attribute4 Attribute4
1537 * @param p_attribute5 Attribute5
1538 * @param p_attribute6 Attribute6
1539 * @param p_attribute7 Attribute7
1540 * @param p_attribute8 Attribute8
1541 * @param p_attribute9 Attribute9
1542 * @param p_attribute10 Attribute10
1543 * @param p_attribute11 Attribute11
1544 * @param p_attribute12 Attribute12
1545 * @param p_attribute13 Attribute13
1546 * @param p_attribute14 Attribute14
1547 * @param p_attribute15 Attribute15
1548
1549
1550 **/
1551 PROCEDURE update_wms_zones (
1552 x_return_status OUT NOCOPY VARCHAR2,
1553 x_msg_data OUT NOCOPY VARCHAR2,
1554 x_msg_count OUT NOCOPY NUMBER,
1555 p_zone_id IN NUMBER,
1556 p_zone_name IN VARCHAR2,
1557 p_description IN VARCHAR2,
1558 p_enabled_flag IN VARCHAR2,
1559 p_labor_enabled IN VARCHAR2,
1560 p_disable_date IN DATE,
1561 p_organization_id IN NUMBER,
1562 p_attribute_category IN VARCHAR2,
1563 p_attribute1 IN VARCHAR2,
1564 p_attribute2 IN VARCHAR2,
1565 p_attribute3 IN VARCHAR2,
1566 p_attribute4 IN VARCHAR2,
1567 p_attribute5 IN VARCHAR2,
1568 p_attribute6 IN VARCHAR2,
1569 p_attribute7 IN VARCHAR2,
1570 p_attribute8 IN VARCHAR2,
1571 p_attribute9 IN VARCHAR2,
1572 p_attribute10 IN VARCHAR2,
1573 p_attribute11 IN VARCHAR2,
1574 p_attribute12 IN VARCHAR2,
1575 p_attribute13 IN VARCHAR2,
1576 p_attribute14 IN VARCHAR2,
1577 p_attribute15 IN VARCHAR2,
1578 p_creation_date IN DATE,
1579 p_created_by IN NUMBER,
1580 p_last_update_date IN DATE,
1581 p_last_updated_by IN NUMBER,
1582 p_last_update_login IN NUMBER
1583 ) IS
1584 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1585 l_module_name VARCHAR2(20) := 'UPDATE_WMS_ZONES';
1586 l_progress_indicator VARCHAR2(10) := '0';
1587
1588 l_return_status VARCHAR2(1);
1589 l_msg_data VARCHAR2(2000);
1590 l_msg_count NUMBER;
1591
1592 BEGIN
1593
1594 x_return_status := fnd_api.g_ret_sts_success;
1595
1596 DEBUG('In procedure :', l_module_name,0);
1597
1598 IF (l_debug = 1) THEN
1599
1600 DEBUG (' p_zone_id==> ' || p_zone_id, l_module_name, 9);
1601 DEBUG (' p_zone_name==> ' || p_zone_name, l_module_name, 9);
1602 DEBUG (' p_description==> ' || p_description, l_module_name, 9);
1603 DEBUG (' enabled_flag==> ' || p_enabled_flag, l_module_name, 9);
1604 DEBUG (' p_labor_enabled==> ' || p_labor_enabled, l_module_name, 9);
1605 DEBUG (' disable_date==> ' || p_disable_date, l_module_name, 9);
1606 DEBUG (' p_organization_id==> ' || p_organization_id,
1607 l_module_name,
1608 9
1609 );
1610 DEBUG (' p_attribute_category==> ' || p_attribute_category,
1611 l_module_name,
1612 9
1613 );
1614 DEBUG (' p_attribute1==> ' || p_attribute1, l_module_name, 9);
1615 DEBUG (' p_attribute2==> ' || p_attribute2, l_module_name, 9);
1616 DEBUG (' p_attribute3==> ' || p_attribute3, l_module_name, 9);
1617 DEBUG (' p_attribute4==> ' || p_attribute4, l_module_name, 9);
1618 DEBUG (' p_attribute5==> ' || p_attribute5, l_module_name, 9);
1619 DEBUG (' p_attribute6==> ' || p_attribute6, l_module_name, 9);
1620 DEBUG (' p_attribute7==> ' || p_attribute7, l_module_name, 9);
1621 DEBUG (' p_attribute8==> ' || p_attribute8, l_module_name, 9);
1622 DEBUG (' p_attribute9==> ' || p_attribute9, l_module_name, 9);
1623 DEBUG (' p_attribute10==> ' || p_attribute10, l_module_name, 9);
1624 DEBUG (' p_attribute11==> ' || p_attribute11, l_module_name, 9);
1625 DEBUG (' p_attribute12==> ' || p_attribute12, l_module_name, 9);
1626 DEBUG (' p_attribute13==> ' || p_attribute13, l_module_name, 9);
1627 DEBUG (' p_attribute14==> ' || p_attribute14, l_module_name, 9);
1628 DEBUG (' p_attribute15==> ' || p_attribute15, l_module_name, 9);
1629 DEBUG (' p_creation_date==> ' || p_creation_date,
1630 l_module_name,
1631 9
1632 );
1633 DEBUG (' p_last_update_date==> ' || p_last_update_date,
1634 l_module_name,
1635 9
1636 );
1637 DEBUG (' created_by==> ' || p_created_by, l_module_name, 9);
1638 DEBUG (' p_last_update_login==> ' || p_last_update_login,
1639 l_module_name,
1640 9
1641 );
1642 DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
1643 l_module_name,
1644 9
1645 );
1646
1647 END IF; /* l_debug = 1 */
1648
1649 l_progress_indicator := '10';
1650
1651 WMS_ZONES_PKG.UPDATE_ROW (
1652 X_ZONE_ID => p_zone_id,
1653 X_ATTRIBUTE_CATEGORY => p_attribute_category,
1654 X_ATTRIBUTE1 => p_attribute1,
1655 X_ATTRIBUTE2 => p_attribute2,
1656 X_ATTRIBUTE3 => p_attribute3,
1657 X_ATTRIBUTE4 => p_attribute4,
1658 X_ATTRIBUTE5 => p_attribute5,
1659 X_ATTRIBUTE6 => p_attribute6,
1660 X_ATTRIBUTE7 => p_attribute7,
1661 X_ATTRIBUTE8 => p_attribute8,
1662 X_ATTRIBUTE9 => p_attribute9,
1663 X_ATTRIBUTE10 => p_attribute10,
1664 X_ATTRIBUTE11 => p_attribute11,
1665 X_ATTRIBUTE12 => p_attribute12,
1666 X_ATTRIBUTE13 => p_attribute13,
1667 X_ATTRIBUTE14 => p_attribute14,
1668 X_ATTRIBUTE15 => p_attribute15,
1669 X_ORGANIZATION_ID => p_organization_id,
1670 X_DISABLE_DATE => p_disable_date,
1671 X_ENABLED_FLAG => p_enabled_flag,
1672 X_LABOR_ENABLED => p_labor_enabled,
1673 X_ZONE_NAME => p_zone_name,
1674 X_DESCRIPTION => p_description,
1675 X_LAST_UPDATE_DATE => p_last_update_date,
1676 X_LAST_UPDATED_BY => p_last_updated_by,
1677 X_LAST_UPDATE_LOGIN => p_last_update_login
1678 );
1679
1680 IF l_debug > 0 THEN
1681
1682 DEBUG (' After updating wms_zones_b/tl' ,
1683 l_module_name,
1684 9
1685 );
1686
1687 END IF;
1688
1689 x_return_status := fnd_api.g_ret_sts_success;
1690
1691 DEBUG('Call Success', l_module_name,0);
1692
1693 EXCEPTION
1694 WHEN NO_DATA_FOUND THEN
1695
1696 DEBUG('No data found Exception :' ||l_progress_indicator
1697 ||' : '||SQLERRM ,
1698 l_module_name,0);
1699
1700 x_return_status := fnd_api.g_ret_sts_error;
1701 x_msg_data := substrb(SQLERRM, 200);
1702 x_msg_count := 1;
1703
1704 WHEN OTHERS THEN
1705 DEBUG('Unexpected Exception :' ||l_progress_indicator
1706 ||' : '||SQLERRM ,
1707 l_module_name,0);
1708
1709 x_return_status := fnd_api.g_ret_sts_unexp_error;
1710 x_msg_data := substrb(SQLERRM, 200);
1711 x_msg_count := 1;
1712
1713 END update_wms_zones;
1714
1715 /**
1716 * This procedure saves the records from
1717 * wms_zone_locators_temp to wms_zone_locators. For every
1718 * record at a given index in the table p_zoneloc_messages
1719 * table, we get the the corresponding rowid from the input
1720 * parameter table p_zoneloc_rowid_t for the same index.
1721 * If the value in p_zoneloc_messages_t is 0, the
1722 * corresponding record will be inserted into the table.
1723 * If the value in p_zoneloc_messages_t is 1, the
1724 * corresponding record will be deleted from the table.
1725 * Else do nothing.
1726
1727 * @param p_zoneloc_rowid_t Table of records containing the rowids of all the records to be inserted or deleted.
1728 * @param p_zoneloc_messages_t Indicates whether the corresponding record should be inserted or deleted.
1729 If the value is 0, the corresponding record will be inserted into the table.
1730 If the value is 1, the corresponding record will be deleted from the table.
1731 Else do nothing.
1732 **/
1733 PROCEDURE save_sel_locators (
1734 p_zoneloc_rowid_t IN wms_zones_pvt.zoneloc_rowid_t,
1735 p_zone_id IN wms_zone_locators.zone_id%TYPE
1736 ) IS
1737
1738 l_progress_indicator VARCHAR2(10) := '0';
1739 l_module_name VARCHAR2(20) := 'SAVE_SEL_LOCATORS';
1740 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1741
1742 BEGIN
1743
1744 DEBUG('In procedure :', l_module_name,0);
1745
1746 IF (l_debug > 1) THEN
1747
1748 DEBUG(' p_zone_id ==> ' || p_zone_id, l_module_name, 9);
1749 DEBUG(' Rows ==> '||p_zoneloc_rowid_t.COUNT, l_module_name, 9);
1750
1751 END IF;
1752
1753 l_progress_indicator := '10';
1754
1755 FORALL i IN p_zoneloc_rowid_t.FIRST .. p_zoneloc_rowid_t.LAST
1756 DELETE FROM wms_zone_locators
1757 WHERE ROWID IN (
1758 SELECT wzl.ROWID
1759 FROM wms_zone_locators wzl,
1760 wms_zone_locators_temp wzlt
1761 WHERE wzl.zone_id = p_zone_id
1762 AND wzlt.rowid = p_zoneloc_rowid_t(i)
1763 AND wzlt.message_id = 1
1764 AND wzl.organization_id = wzlt.organization_id
1765 AND wzl.subinventory_code = wzlt.subinventory_code
1766 AND NVL(wzlt.inventory_location_id, -999) = -999
1767 );
1768
1769 IF(l_debug > 0) THEN
1770
1771 DEBUG('Rows deleted before insert :'||SQL%ROWCOUNT, l_module_name, 9);
1772
1773 END IF;
1774
1775 l_progress_indicator := '20';
1776
1777 FORALL i IN p_zoneloc_rowid_t.FIRST .. p_zoneloc_rowid_t.LAST
1778 INSERT INTO wms_zone_locators (
1779 organization_id
1780 , zone_id
1781 , inventory_location_id
1782 , subinventory_code
1783 , entire_sub_flag
1784 , last_update_date
1785 , last_updated_by
1786 , creation_date
1787 , created_by
1788 , last_update_login
1789 ) (
1790 SELECT wzlt.organization_id
1791 , p_zone_id
1792 , DECODE(wzlt.inventory_location_id,
1793 -999
1794 , NULL
1795 , wzlt.inventory_location_id)
1796 , wzlt.subinventory_code
1797 , DECODE(wzlt.inventory_location_id,
1798 -999
1799 , 'Y'
1800 , NULL
1801 , 'Y'
1802 , 'N')
1803 , SYSDATE
1804 , fnd_global.user_id
1805 , SYSDATE
1806 , fnd_global.user_id
1807 , fnd_global.login_id
1808 FROM wms_zone_locators_temp wzlt
1809 WHERE wzlt.message_id = 1
1810 AND ROWID = p_zoneloc_rowid_t (i)
1811 AND NOT EXISTS (
1812 SELECT 1
1813 FROM wms_zone_locators wzl
1814 WHERE wzl.zone_id = p_zone_id
1815 AND wzl.organization_id = wzlt.organization_id
1816 AND wzl.subinventory_code = wzlt.subinventory_code
1817 AND wzl.entire_sub_flag = 'Y'
1818 )
1819 AND NOT EXISTS (
1820 SELECT 1
1821 FROM wms_zone_locators wzl
1822 WHERE wzl.zone_id = p_zone_id
1823 AND wzl.organization_id = wzlt.organization_id
1824 AND wzl.subinventory_code = wzlt.subinventory_code
1825 AND NVL(wzl.entire_sub_flag,'N') = 'N'
1826 AND wzl.inventory_location_id = wzlt.inventory_location_id
1827 )
1828 );
1829
1830 IF(l_debug > 0) THEN
1831
1832 DEBUG('Rows inserted :'||SQL%ROWCOUNT, l_module_name, 9);
1833
1834 END IF;
1835
1836 l_progress_indicator := '30';
1837
1838 FORALL i IN p_zoneloc_rowid_t.FIRST .. p_zoneloc_rowid_t.LAST
1839 DELETE FROM wms_zone_locators
1840 WHERE ROWID IN (
1841 SELECT wzl.ROWID
1842 FROM wms_zone_locators wzl,
1843 wms_zone_locators_temp wzlt
1844 WHERE wzl.zone_id = p_zone_id
1845 AND wzlt.rowid = p_zoneloc_rowid_t(i)
1846 AND wzlt.message_id = 2
1847 AND wzl.organization_id = wzlt.organization_id
1848 AND wzl.subinventory_code = wzlt.subinventory_code
1849 AND ( ( wzlt.inventory_location_id IS NULL ) OR
1850 ( wzl.inventory_location_id = wzlt.inventory_location_id ) ) );
1851
1852 IF(l_debug > 0) THEN
1853
1854 DEBUG('Rows removed from zone :'||SQL%ROWCOUNT, l_module_name, 9);
1855
1856 END IF;
1857
1858 DEBUG('Call Success', l_module_name,0);
1859
1860 EXCEPTION
1861
1862 WHEN OTHERS THEN
1863 DEBUG (' Unexpected exception : ' || SQLERRM, l_module_name, 0);
1864
1865 END save_sel_locators;
1866
1867 PROCEDURE save_all_locators (
1868 p_zone_id IN wms_zone_locators.zone_id%TYPE,
1869 p_org_id IN wms_zone_locators.organization_id%TYPE
1870 )
1871 IS
1872 l_org_id NUMBER;
1873 l_zone_id NUMBER;
1874 l_inv_loc_id NUMBER;
1875 l_sub_code VARCHAR2 (10);
1876 l_entire_sub VARCHAR2 (1);
1877 l_last_update_date DATE;
1878 l_last_updated_by NUMBER;
1879 l_creation_date DATE;
1880 l_created_by NUMBER;
1881 l_last_update_login NUMBER;
1882 BEGIN
1883 DEBUG (' p_zone_id==> ' || p_zone_id, 'save_all_locators', 9);
1884 DEBUG (' p_org_id==> ' || p_org_id, 'save_all_locators', 9);
1885
1886 INSERT INTO wms_zone_locators
1887 (organization_id, zone_id, inventory_location_id,
1888 subinventory_code, entire_sub_flag, last_update_date,
1889 last_updated_by, creation_date, created_by,
1890 last_update_login)
1891 (SELECT p_org_id, p_zone_id, inventory_location_id,
1892 subinventory_code,
1893 DECODE (inventory_location_id, -999, 'Y', 'N'), SYSDATE,
1894 fnd_global.user_id, SYSDATE, fnd_global.user_id,
1895 fnd_global.user_id
1896 FROM wms_zone_locators_temp
1897 WHERE MESSAGE_ID = 1 );
1898
1899 DEBUG ('committing' || SQL%ROWCOUNT, 'save_all_locators', 9);
1900
1901 DELETE FROM wms_zone_locators
1902 WHERE inventory_location_id =
1903 (SELECT inventory_location_id
1904 FROM wms_zone_locators_temp
1905 WHERE MESSAGE_id = 2)
1906 AND zone_id = p_zone_id;
1907
1908 COMMIT;
1909 EXCEPTION
1910 WHEN OTHERS
1911 THEN
1912 DEBUG (' other exception ' || SQLERRM, 'save_all_locators', 9);
1913 END save_all_locators;
1914
1915
1916 PROCEDURE lock_row(
1917 x_return_status OUT NOCOPY VARCHAR2,
1918 x_msg_data OUT NOCOPY VARCHAR2,
1919 x_msg_count OUT NOCOPY NUMBER,
1920 p_zone_id IN NUMBER,
1921 p_zone_name IN VARCHAR2,
1922 p_description IN VARCHAR2,
1923 p_enabled_flag IN VARCHAR2,
1924 p_labor_enabled IN VARCHAR2,
1925 p_disable_date IN DATE,
1926 p_organization_id IN NUMBER,
1927 p_attribute_category IN VARCHAR2,
1928 p_attribute1 IN VARCHAR2,
1929 p_attribute2 IN VARCHAR2,
1930 p_attribute3 IN VARCHAR2,
1931 p_attribute4 IN VARCHAR2,
1932 p_attribute5 IN VARCHAR2,
1933 p_attribute6 IN VARCHAR2,
1934 p_attribute7 IN VARCHAR2,
1935 p_attribute8 IN VARCHAR2,
1936 p_attribute9 IN VARCHAR2,
1937 p_attribute10 IN VARCHAR2,
1938 p_attribute11 IN VARCHAR2,
1939 p_attribute12 IN VARCHAR2,
1940 p_attribute13 IN VARCHAR2,
1941 p_attribute14 IN VARCHAR2,
1942 p_attribute15 IN VARCHAR2,
1943 p_creation_date IN DATE,
1944 p_created_by IN NUMBER,
1945 p_last_update_date IN DATE,
1946 p_last_updated_by IN NUMBER,
1947 p_last_update_login IN NUMBER
1948 ) IS
1949 l_module_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
1950
1951 CURSOR C1 IS
1952 SELECT *
1953 FROM WMS_ZONES_VL
1954 WHERE ZONE_ID = P_ZONE_ID
1955 FOR UPDATE OF ZONE_ID NOWAIT;
1956
1957 rec1 C1%ROWTYPE;
1958
1959 record_changed EXCEPTION;
1960
1961 BEGIN
1962
1963 OPEN C1;
1964 FETCH C1 INTO REC1;
1965 IF C1%NOTFOUND THEN
1966 RAISE NO_DATA_FOUND;
1967 END IF;
1968 CLOSE C1;
1969
1970 IF ( (rec1.zone_id = p_zone_id) AND
1971 (rec1.organization_id = p_organization_id) AND
1972 ( (rec1.zone_name = p_zone_name) OR
1973 (rec1.zone_name IS NULL AND p_zone_name IS NULL) ) AND
1974 ( (rec1.enabled_flag = p_enabled_flag) OR
1975 (rec1.enabled_flag IS NULL AND p_enabled_flag IS NULL) ) AND
1976 ( (rec1.labor_enabled = p_labor_enabled) OR
1977 (rec1.labor_enabled IS NULL AND p_labor_enabled IS NULL) ) AND
1978 ( (rec1.description = p_description) OR
1979 (rec1.description IS NULL AND p_description IS NULL) ) AND
1980 ( (rec1.disable_date = p_disable_date) OR
1981 (rec1.disable_date IS NULL AND p_disable_date IS NULL) ) AND
1982 ( (rec1.created_by = p_created_by) OR
1983 (rec1.created_by IS NULL AND p_created_by IS NULL) ) AND
1984 ( (rec1.creation_date = p_creation_date) OR
1985 (rec1.creation_date IS NULL AND p_creation_date IS NULL) ) AND
1986 ( (rec1.last_updated_by = p_last_updated_by) OR
1987 (rec1.last_updated_by IS NULL AND p_last_updated_by IS NULL) ) AND
1988 ( (rec1.last_update_date = p_last_update_date) OR
1989 (rec1.last_update_date IS NULL AND p_last_update_date IS NULL) ) AND
1990 ( (rec1.last_update_login = p_last_update_login) OR
1991 (rec1.last_update_login IS NULL AND p_last_update_login IS NULL) ) AND
1992 ( (rec1.attribute_category = p_attribute_category) OR
1993 (rec1.attribute_category IS NULL AND p_attribute_category IS NULL) ) AND
1994 ( (rec1.attribute1 = p_attribute1) OR
1995 (rec1.attribute1 IS NULL AND p_attribute1 IS NULL) ) AND
1996 ( (rec1.attribute2 = p_attribute2) OR
1997 (rec1.attribute2 IS NULL AND p_attribute2 IS NULL) ) AND
1998 ( (rec1.attribute3 = p_attribute3) OR
1999 (rec1.attribute3 IS NULL AND p_attribute3 IS NULL) ) AND
2000 ( (rec1.attribute4 = p_attribute4) OR
2001 (rec1.attribute4 IS NULL AND p_attribute4 IS NULL) ) AND
2002 ( (rec1.attribute5 = p_attribute5) OR
2003 (rec1.attribute5 IS NULL AND p_attribute5 IS NULL) ) AND
2004 ( (rec1.attribute6 = p_attribute6) OR
2005 (rec1.attribute6 IS NULL AND p_attribute6 IS NULL) ) AND
2006 ( (rec1.attribute7 = p_attribute7) OR
2007 (rec1.attribute7 IS NULL AND p_attribute7 IS NULL) ) AND
2008 ( (rec1.attribute8 = p_attribute8) OR
2009 (rec1.attribute8 IS NULL AND p_attribute8 IS NULL) ) AND
2010 ( (rec1.attribute9 = p_attribute9) OR
2011 (rec1.attribute9 IS NULL AND p_attribute9 IS NULL) ) AND
2012 ( (rec1.attribute10 = p_attribute10) OR
2013 (rec1.attribute10 IS NULL AND p_attribute10 IS NULL) ) AND
2014 ( (rec1.attribute11 = p_attribute11) OR
2015 (rec1.attribute11 IS NULL AND p_attribute11 IS NULL) ) AND
2016 ( (rec1.attribute12 = p_attribute12) OR
2017 (rec1.attribute12 IS NULL AND p_attribute12 IS NULL) ) AND
2018 ( (rec1.attribute13 = p_attribute13) OR
2019 (rec1.attribute13 IS NULL AND p_attribute13 IS NULL) ) AND
2020 ( (rec1.attribute14 = p_attribute14) OR
2021 (rec1.attribute14 IS NULL AND p_attribute14 IS NULL) ) AND
2022 ( (rec1.attribute15 = p_attribute15) OR
2023 (rec1.attribute15 IS NULL AND p_attribute15 IS NULL) )
2024 ) THEN
2025
2026 NULL;
2027 ELSE
2028
2029 RAISE record_changed;
2030
2031 END IF;
2032
2033 x_return_status := FND_API.G_RET_STS_SUCCESS;
2034 fnd_msg_pub.count_and_get(
2035 p_count => x_msg_count,
2036 p_data => x_msg_data
2037 );
2038
2039 EXCEPTION
2040 WHEN NO_DATA_FOUND THEN
2041 x_return_status := fnd_api.g_ret_sts_error;
2042
2043 fnd_message.set_name('INV','OE_LOCK_ROW_DELETED');
2044 fnd_msg_pub.add;
2045
2046 fnd_msg_pub.count_and_get(
2047 p_count => x_msg_count,
2048 p_data => x_msg_data
2049 );
2050
2051 WHEN app_exceptions.record_lock_exception THEN
2052 x_return_status := fnd_api.g_ret_sts_error;
2053
2054 fnd_message.set_name('INV','OE_LOCK_ROW_ALREADY_LOCKED');
2055 fnd_msg_pub.add;
2056
2057 fnd_msg_pub.count_and_get(
2058 p_count => x_msg_count,
2059 p_data => x_msg_data
2060 );
2061
2062
2063 WHEN record_changed THEN
2064 x_return_status := fnd_api.g_ret_sts_error;
2065
2066 fnd_message.set_name('INV','OE_LOCK_ROW_CHANGED');
2067 fnd_msg_pub.add;
2068
2069 fnd_msg_pub.count_and_get(
2070 p_count => x_msg_count,
2071 p_data => x_msg_data
2072 );
2073
2074
2075 WHEN OTHERS THEN
2076 x_return_status := fnd_api.g_ret_sts_unexp_error;
2077
2078 x_msg_data := substrb(sqlerrm, 200);
2079 x_msg_count := 1;
2080
2081 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2082 fnd_msg_pub.add_exc_msg(g_pkg_name, l_module_name);
2083
2084 END IF;
2085
2086 END LOCK_ROW;
2087
2088 /**
2089 * Initialize the data structures needed for procedures of this package to work.
2090 *
2091 * This procedure must always be called once before any call is made to any other
2092 * procedure/function of this package.
2093 *
2094 * If any exception is raised during the process of initialization, the same will is
2095 * propagated
2096 *
2097 **/
2098 PROCEDURE initialize IS
2099
2100 l_progress_indicator VARCHAR2(10) := '0';
2101 l_module_name VARCHAR2(15) := 'INITIALIZE';
2102
2103 BEGIN
2104
2105 DEBUG('In procedure :', l_module_name,0);
2106
2107 l_progress_indicator := '10';
2108
2109 /**
2110 * If the data structures have already been initialized then no need to do any
2111 * furthur processing, return immediately.
2112 */
2113 IF g_initialized THEN
2114
2115 DEBUG('Call Success', l_module_name,0);
2116 RETURN;
2117
2118 END IF;
2119
2120 /**
2121 * In case g_initialized is NULL, make it FALSE
2122 */
2123 g_initialized := FALSE;
2124
2125 l_progress_indicator := '20';
2126 set_locator_status;
2127
2128 l_progress_indicator := '30';
2129 set_subinventory_status;
2130
2131 l_progress_indicator := '40';
2132 set_locator_types;
2133
2134 l_progress_indicator := '50';
2135 set_subinventory_types;
2136
2137 l_progress_indicator := '60';
2138 populate_message_cache;
2139
2140 g_initialized := TRUE;
2141
2142 DEBUG('Call Success', l_module_name,0);
2143
2144 EXCEPTION
2145
2146 WHEN OTHERS THEN
2147 g_initialized := FALSE;
2148 DEBUG('Unexpected Exception :' ||l_progress_indicator
2149 ||' : '||SQLERRM ,
2150 l_module_name,0);
2151 RAISE;
2152
2153 END initialize;
2154
2155 /**
2156 * Caches the commonly used message texts in global variables
2157 *
2158 **/
2159 PROCEDURE populate_message_cache IS
2160
2161 l_progress_indicator VARCHAR2(10) := '0';
2162 l_module_name VARCHAR2(25) := 'POPULATE_MESSAGE_CACHE';
2163
2164 BEGIN
2165
2166 DEBUG('In procedure :', l_module_name,0);
2167
2168 l_progress_indicator := '10';
2169 fnd_message.set_name('WMS', 'WMS_PENDING_ADDITION_TO_ZONE');
2170 g_add_locators_message := fnd_message.get;
2171
2172 l_progress_indicator := '20';
2173 fnd_message.set_name('WMS', 'WMS_PENDING_REMOVAL_FROM_ZONE');
2174 g_remove_locators_message := fnd_message.get;
2175
2176 l_progress_indicator := '30';
2177 fnd_message.set_name('WMS', 'WMS_ALL_LOCATORS');
2178 g_all_locators_message := fnd_message.get;
2179
2180 DEBUG('Call Success', l_module_name,0);
2181
2182 EXCEPTION
2183
2184 WHEN OTHERS THEN
2185 DEBUG('Unexpected Exception :' ||l_progress_indicator
2186 ||' : '||SQLERRM ,
2187 l_module_name,0);
2188 RAISE;
2189
2190 END populate_message_cache;
2191
2192 /**
2193 * Validate the attributes of Zones.
2194 *
2195 * If any validation fails the procedure sets the x_return_status to 'E'.
2196 * If any truncation occurs during validation the x_return status is set to 'W'
2197 *
2198 * Any exception raised during the process of validation is put on the stack
2199 *
2200 * @param x_return_status Return status, this can be 'S', 'E' or 'W'
2201 * @param x_msg_count Count of messages in stack
2202 * @param x_msg_data Message, if the count is 1
2203 * @param p_zone_id Zone id
2204 * @param p_zone_name Zone name
2205 * @param p_description Description
2206 * @param p_enabled_flag Enabled flag
2207 * @param p_disable_date Disable date
2208 * @param p_organization_id Organization id
2209 * @param p_attribute_category Zone DFF context field
2210 * @param p_attribute1 Zone DFF Attribute
2211 * @param p_attribute2 Zone DFF Attribute
2212 * @param p_attribute3 Zone DFF Attribute
2213 * @param p_attribute4 Zone DFF Attribute
2214 * @param p_attribute5 Zone DFF Attribute
2215 * @param p_attribute6 Zone DFF Attribute
2216 * @param p_attribute7 Zone DFF Attribute
2217 * @param p_attribute8 Zone DFF Attribute
2218 * @param p_attribute9 Zone DFF Attribute
2219 * @param p_attribute10 Zone DFF Attribute
2220 * @param p_attribute11 Zone DFF Attribute
2221 * @param p_attribute12 Zone DFF Attribute
2222 * @param p_attribute13 Zone DFF Attribute
2223 * @param p_attribute14 Zone DFF Attribute
2224 * @param p_attribute15 Zone DFF Attribute
2225 * @param p_creation_date WHO column
2226 * @param p_created_by WHO column
2227 * @param p_last_update_date WHO column
2228 * @param p_last_updated_by WHO column
2229 * @param p_last_update_login WHO column
2230 *
2231 **/
2232 PROCEDURE validate_row(
2233 x_return_status OUT NOCOPY VARCHAR2,
2234 x_msg_data OUT NOCOPY VARCHAR2,
2235 x_msg_count OUT NOCOPY NUMBER,
2236 p_zone_id IN NUMBER,
2237 p_zone_name IN VARCHAR2,
2238 p_description IN VARCHAR2,
2239 p_enabled_flag IN VARCHAR2,
2240 p_disable_date IN DATE,
2241 p_organization_id IN NUMBER,
2242 p_attribute_category IN VARCHAR2,
2243 p_attribute1 IN VARCHAR2,
2244 p_attribute2 IN VARCHAR2,
2245 p_attribute3 IN VARCHAR2,
2246 p_attribute4 IN VARCHAR2,
2247 p_attribute5 IN VARCHAR2,
2248 p_attribute6 IN VARCHAR2,
2249 p_attribute7 IN VARCHAR2,
2250 p_attribute8 IN VARCHAR2,
2251 p_attribute9 IN VARCHAR2,
2252 p_attribute10 IN VARCHAR2,
2253 p_attribute11 IN VARCHAR2,
2254 p_attribute12 IN VARCHAR2,
2255 p_attribute13 IN VARCHAR2,
2256 p_attribute14 IN VARCHAR2,
2257 p_attribute15 IN VARCHAR2,
2258 p_creation_date IN DATE,
2259 p_created_by IN NUMBER,
2260 p_last_update_date IN DATE,
2261 p_last_updated_by IN NUMBER,
2262 p_last_update_login IN NUMBER
2263 ) IS
2264
2265 l_progress_indicator VARCHAR2(10) := '0';
2266 l_module_name VARCHAR2(30) := 'VALIDATE_ROW';
2267
2268 l_return_status VARCHAR2(1) := 'S';
2269 l_zone_name VARCHAR2(40);
2270
2271 l_id NUMBER;
2272
2273 CURSOR c_zone_id IS
2274 SELECT 1
2275 FROM wms_zones_b
2276 WHERE zone_id = p_zone_id;
2277
2278 CURSOR c_org_id IS
2279 SELECT 1
2280 FROM mtl_parameters
2281 WHERE organization_id = p_organization_id;
2282
2283 CURSOR c_zone_name IS
2284 SELECT 1
2285 FROM wms_zones_vl
2286 WHERE zone_name = p_zone_name
2287 AND organization_id = p_organization_id;
2288
2289 BEGIN
2290
2291 DEBUG('In procedure :', l_module_name,0);
2292
2293 l_progress_indicator := '10';
2294
2295 /**
2296 * The disable date must be greater than or equal to SYSDATE
2297 **/
2298 IF p_disable_date < SYSDATE THEN
2299
2300 l_return_status := 'E';
2301 fnd_message.set_name('WMS','WMS_ZONE_DISABLE_INAVLID');
2302 fnd_msg_pub.ADD;
2303
2304 END IF;
2305
2306 l_progress_indicator := '20';
2307
2308 /**
2309 * The Enabled flag can only be NULL or Y or N
2310 **/
2311 IF p_enabled_flag IS NULL OR
2312 p_enabled_flag = 'Y' OR
2313 p_enabled_flag = 'N' THEN
2314
2315 NULL;
2316
2317 ELSE
2318
2319 l_return_status := 'E';
2320 fnd_message.set_name('WMS','WMS_ZONE_ENABLED_INVALID');
2321 fnd_msg_pub.ADD;
2322
2323 END IF;
2324
2325 l_progress_indicator := '30';
2326
2327 /**
2328 * The Description field is only 240 bytes long
2329 **/
2330 IF LENGTH(p_description) > 240 THEN
2331
2332 fnd_message.set_name('WMS','WMS_DESCRIP_TOO_LONG');
2333 fnd_msg_pub.ADD;
2334
2335 IF l_return_status = 'S' THEN
2336 l_return_status := 'W';
2337 END IF;
2338
2339 END IF;
2340
2341 l_progress_indicator := '40';
2342
2343 /**
2344 * The Zone Name field is only 30 bytes long
2345 **/
2346 IF LENGTH(p_zone_name) > 30 THEN
2347
2348 fnd_message.set_name('WMS','WMS_ZONE_TOO_LONG');
2349 fnd_msg_pub.ADD;
2350
2351 IF l_return_status = 'S' THEN
2352 l_return_status := 'W';
2353 END IF;
2354
2355 END IF;
2356
2357 l_progress_indicator := '50';
2358
2359 /**
2360 * The Organization id must exist
2361 **/
2362 OPEN c_org_id;
2363 FETCH c_org_id INTO l_id;
2364 IF c_org_id%NOTFOUND THEN
2365
2366 l_return_status := 'E';
2367 fnd_message.set_name('WMS','WMS_ORG_ID');
2368 fnd_msg_pub.add;
2369
2370 END IF;
2371 CLOSE c_org_id;
2372
2373 l_progress_indicator := '60';
2374
2375 /**
2376 * The Zone Id must be unique
2377 **/
2378 OPEN c_zone_id;
2379 FETCH c_zone_id INTO l_id;
2380 IF c_zone_id%NOTFOUND THEN
2381
2382 NULL;
2383
2384 ELSE
2385
2386 l_return_status := 'E';
2387 fnd_message.set_name('WMS','WMS_ZONE_ID');
2388 fnd_msg_pub.ADD;
2389
2390 END IF;
2391 CLOSE c_zone_id;
2392
2393 l_progress_indicator := '70';
2394
2395 /**
2396 * The Zone Name must be unique in an organization
2397 **/
2398 OPEN c_zone_name;
2399 FETCH c_zone_name INTO l_zone_name;
2400 IF c_zone_name%NOTFOUND THEN
2401
2402 NULL;
2403
2404 ELSE
2405
2406 l_return_status := 'E';
2407 fnd_message.set_name('INV', 'INV_ALREADY_EXISTS');
2408 fnd_message.set_token('ENTITY', 'Zone', FALSE);
2409 fnd_msg_pub.ADD;
2410
2411 END IF;
2412 CLOSE c_zone_name;
2413
2414 /**
2415 * Flexfield attributes and WHO colums are not validated
2416 **/
2417 x_return_status := l_return_status;
2418
2419 DEBUG('Call Success', l_module_name,0);
2420
2421 EXCEPTION
2422
2423 WHEN OTHERS THEN
2424 DEBUG('Unexpected Exception :' ||l_progress_indicator
2425 ||' : '||SQLERRM ,
2426 l_module_name,0);
2427
2428 x_return_status := 'U';
2429 x_msg_data := substrb(SQLERRM, 200);
2430 x_msg_count := 1;
2431
2432 END validate_row;
2433
2434
2435 PROCEDURE add_locators(
2436 x_return_status OUT NOCOPY VARCHAR2,
2437 x_msg_data OUT NOCOPY VARCHAR2,
2438 x_msg_count OUT NOCOPY NUMBER,
2439 p_zone_id IN NUMBER,
2440 p_organization_id IN NUMBER,
2441 p_subinventory_code IN VARCHAR,
2442 p_locator_id IN NUMBER,
2443 p_entire_sub_flag IN VARCHAR,
2444 p_creation_date IN DATE,
2445 p_created_by IN NUMBER,
2446 p_last_update_date IN DATE,
2447 p_last_updated_by IN NUMBER,
2448 p_last_update_login IN NUMBER
2449 ) IS
2450
2451 l_module_name VARCHAR2(20) := 'ADD_LOCATORS';
2452 l_progress_indicator VARCHAR2(20) := '0';
2453 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2454
2455 BEGIN
2456
2457 DEBUG('In procedure :', l_module_name,0);
2458
2459 IF l_debug > 0 THEN
2460
2461 DEBUG('p_zone_id => '||p_zone_id, l_module_name, 9);
2462 DEBUG('p_organization_id => '||p_organization_id, l_module_name, 9);
2463 DEBUG('p_subinventory_code => '||p_subinventory_code, l_module_name, 9);
2464 DEBUG('p_locator_id => '||p_locator_id, l_module_name, 9);
2465 DEBUG('p_entire_sub_flag => '||p_entire_sub_flag, l_module_name, 9);
2466 DEBUG (' p_creation_date==> ' || p_creation_date,
2467 l_module_name, 9);
2468 DEBUG (' p_last_update_date==> ' || p_last_update_date,
2469 l_module_name, 9);
2470 DEBUG (' created_by==> ' || p_created_by, l_module_name, 9);
2471 DEBUG (' p_last_update_login==> ' || p_last_update_login,
2472 l_module_name, 9);
2473 DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
2474 l_module_name, 9);
2475
2476 END IF; /* debug > 0 */
2477
2478 l_progress_indicator := '10';
2479
2480 DELETE wms_zone_locators
2481 WHERE zone_id = p_zone_id
2482 AND organization_id = p_organization_id
2483 AND subinventory_code = p_subinventory_code
2484 AND NVL(p_entire_sub_flag, 'N') = 'Y';
2485
2486 IF l_debug > 0 THEN
2487 DEBUG('Records deleted :'||SQL%ROWCOUNT, l_module_name,0);
2488 END IF;
2489
2490 l_progress_indicator := '20';
2491
2492 INSERT INTO wms_zone_locators(
2493 organization_id
2494 , zone_id
2495 , inventory_location_id
2496 , subinventory_code
2497 , entire_sub_flag
2498 , last_update_date
2499 , last_updated_by
2500 , creation_date
2501 , created_by
2502 , last_update_login
2503 )
2504 (
2505 SELECT p_organization_id
2506 , p_zone_id
2507 , DECODE(p_entire_sub_flag, 'Y', NULL, p_locator_id)
2508 , p_subinventory_code
2509 , p_entire_sub_flag
2510 , p_last_update_date
2511 , p_last_updated_by
2512 , p_creation_date
2513 , p_created_by
2514 , p_last_update_login
2515 FROM dual
2516 WHERE NOT EXISTS (
2517 SELECT 1
2518 FROM wms_zone_locators
2519 WHERE zone_id = p_zone_id
2520 AND organization_id = p_organization_id
2521 AND subinventory_code = p_subinventory_code
2522 AND entire_sub_flag = 'Y'
2523 )
2524 AND NOT EXISTS (
2525 SELECT 1
2526 FROM wms_zone_locators
2527 WHERE zone_id = p_zone_id
2528 AND organization_id = p_organization_id
2529 AND subinventory_code = p_subinventory_code
2530 AND NVL(entire_sub_flag,'N') = 'N'
2531 AND inventory_location_id = p_locator_id
2532 )
2533 );
2534
2535 IF l_debug > 0 THEN
2536 DEBUG('Records inserted :'||SQL%ROWCOUNT, l_module_name,0);
2537 END IF;
2538
2539 x_return_status := fnd_api.g_ret_sts_success;
2540
2541 DEBUG('Call Success', l_module_name,0);
2542
2543 EXCEPTION
2544
2545 WHEN OTHERS THEN
2546 DEBUG('Unexpected Exception :' ||l_progress_indicator
2547 ||' : '||SQLERRM ,
2548 l_module_name,0);
2549
2550 x_return_status := fnd_api.g_ret_sts_unexp_error;
2551 x_msg_data := substrb(SQLERRM, 200);
2552 x_msg_count := 1;
2553
2554 END add_locators;
2555
2556 PROCEDURE validate_locators(
2557 x_return_status OUT NOCOPY VARCHAR2,
2558 x_msg_data OUT NOCOPY VARCHAR2,
2559 x_msg_count OUT NOCOPY NUMBER,
2560 p_zone_id IN NUMBER,
2561 p_organization_id IN NUMBER,
2562 p_subinventory_code IN VARCHAR,
2563 p_locator_id IN NUMBER,
2564 p_entire_sub_flag IN VARCHAR,
2565 p_creation_date IN DATE,
2566 p_created_by IN NUMBER,
2567 p_last_update_date IN DATE,
2568 p_last_updated_by IN NUMBER,
2569 p_last_update_login IN NUMBER
2570 ) IS
2571
2572 l_module_name VARCHAR2(20) := 'VALIDATE_LOCATORS';
2573 l_progress_indicator VARCHAR2(20) := '0';
2574 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2575 l_return_status VARCHAR2(1);
2576
2577 l_id VARCHAR2(30);
2578
2579 CURSOR c_org_id IS
2580 SELECT 1
2581 FROM mtl_parameters
2582 WHERE organization_id = p_organization_id;
2583
2584 CURSOR c_zone_id IS
2585 SELECT 1
2586 FROM wms_zones_b
2587 WHERE zone_id = p_zone_id;
2588
2589 CURSOR c_subinventory_code IS
2590 SELECT 1
2591 FROM mtl_secondary_inventories
2592 WHERE organization_id = p_organization_id
2593 AND secondary_inventory_name = p_subinventory_code;
2594
2595 CURSOR c_locator_id IS
2596 SELECT 1
2597 FROM mtl_item_locations
2598 WHERE inventory_location_id = p_locator_id
2599 AND organization_id = p_organization_id
2600 AND subinventory_code = p_subinventory_code;
2601
2602 BEGIN
2603
2604 DEBUG('In procedure :', l_module_name,0);
2605
2606 IF l_debug > 0 THEN
2607
2608 DEBUG('p_zone_id => '||p_zone_id, l_module_name, 9);
2609 DEBUG('p_organization_id => '||p_organization_id, l_module_name, 9);
2610 DEBUG('p_subinventory_code => '||p_subinventory_code, l_module_name, 9);
2611 DEBUG('p_locator_id => '||p_locator_id, l_module_name, 9);
2612 DEBUG('p_entire_sub_flag => '||p_entire_sub_flag, l_module_name, 9);
2613 DEBUG (' p_creation_date==> ' || p_creation_date,
2614 l_module_name, 9);
2615 DEBUG (' p_last_update_date==> ' || p_last_update_date,
2616 l_module_name, 9);
2617 DEBUG (' created_by==> ' || p_created_by, l_module_name, 9);
2618 DEBUG (' p_last_update_login==> ' || p_last_update_login,
2619 l_module_name, 9);
2620 DEBUG (' p_last_updated_by==> ' || p_last_updated_by,
2621 l_module_name, 9);
2622
2623 END IF; /* debug > 0 */
2624
2625 l_progress_indicator := '10';
2626
2627 /**
2628 * p_entire_sub_flag can be 'Y', 'N' or NULL,
2629 * Value of NULL is same as 'N'
2630 */
2631 IF p_entire_sub_flag IS NULL OR
2632 p_entire_sub_flag = 'Y' OR
2633 p_entire_sub_flag = 'N' THEN
2634
2635 NULL;
2636
2637 ELSE
2638
2639 l_return_status := fnd_api.g_ret_sts_error;
2640
2641 END IF;
2642
2643 l_progress_indicator := '20';
2644
2645 /**
2646 * If p_entire_sub_flag = 'Y' then p_locator_id
2647 * MUST be NULL
2648 */
2649 IF p_entire_sub_flag = 'Y' AND p_locator_id IS NOT NULL THEN
2650
2651 l_return_status := fnd_api.g_ret_sts_error;
2652
2653 END IF;
2654
2655 l_progress_indicator := '30';
2656
2657 /**
2658 * p_organization_id must exist in MTL_PARAMETERS
2659 */
2660 OPEN c_org_id;
2661 FETCH c_org_id INTO l_id;
2662 IF c_org_id%NOTFOUND THEN
2663 l_return_status := fnd_api.g_ret_sts_error;
2664 END IF;
2665 CLOSE c_org_id;
2666
2667 l_progress_indicator := '40';
2668
2669 /**
2670 * p_zone_id must exist in WMS_ZONES_B
2671 */
2672 OPEN c_zone_id;
2673 FETCH c_zone_id INTO l_id;
2674 IF c_zone_id%NOTFOUND THEN
2675 l_return_status := fnd_api.g_ret_sts_error;
2676 END IF;
2677 CLOSE c_zone_id;
2678
2679 l_progress_indicator := '50';
2680
2681 /**
2682 * p_subinventory_code must exist in for that
2683 * p_organization_id in MTL_SECONDARY_INVENTORIES
2684 */
2685 OPEN c_subinventory_code;
2686 FETCH c_subinventory_code INTO l_id;
2687 IF c_subinventory_code%NOTFOUND THEN
2688 l_return_status := fnd_api.g_ret_sts_error;
2689 END IF;
2690 CLOSE c_subinventory_code;
2691
2692 l_progress_indicator := '60';
2693
2694 /**
2695 * If p_entire_sub_flag IS 'Y', then p_locator_id must
2696 * be null, this is validated above.
2697 *
2698 * However if p_entire_sub_flag is NULL or N then
2699 * p_locator_id must exist in the p_subinventory_code/
2700 * p_organization_id in MTL_ITEM_LOCATIONS
2701 */
2702 IF NVL(p_entire_sub_flag, 'N') = 'N' THEN
2703
2704 OPEN c_locator_id;
2705 FETCH c_locator_id INTO l_id;
2706 IF c_locator_id%NOTFOUND THEN
2707 l_return_status := fnd_api.g_ret_sts_error;
2708 END IF;
2709 CLOSE c_locator_id;
2710
2711 END IF;
2712
2713 /**
2714 * WHO colums are not validated
2715 **/
2716 x_return_status := l_return_status;
2717
2718 DEBUG('Call Success', l_module_name,0);
2719
2720 EXCEPTION
2721
2722 WHEN OTHERS THEN
2723 DEBUG('Unexpected Exception :' ||l_progress_indicator
2724 ||' : '||SQLERRM ,
2725 l_module_name,0);
2726
2727 x_return_status := fnd_api.g_ret_sts_unexp_error;
2728 x_msg_data := substrb(SQLERRM, 200);
2729 x_msg_count := 1;
2730
2731 END validate_locators;
2732
2733 PROCEDURE delete_locators(
2734 x_return_status OUT NOCOPY VARCHAR2,
2735 x_msg_data OUT NOCOPY VARCHAR2,
2736 x_msg_count OUT NOCOPY NUMBER,
2737 p_zone_id IN NUMBER,
2738 p_organization_id IN NUMBER,
2739 p_subinventory_code IN VARCHAR,
2740 p_locator_id IN NUMBER,
2741 p_entire_sub_flag IN VARCHAR
2742 ) IS
2743
2744 l_module_name VARCHAR2(20) := 'DELETE_LOCATORS';
2745 l_progress_indicator VARCHAR2(20) := '0';
2746 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2747
2748 BEGIN
2749
2750 DEBUG('In procedure :', l_module_name,0);
2751
2752 IF l_debug > 0 THEN
2753
2754 DEBUG('p_zone_id => '||p_zone_id, l_module_name, 9);
2755 DEBUG('p_organization_id => '||p_organization_id, l_module_name, 9);
2756 DEBUG('p_subinventory_code => '||p_subinventory_code, l_module_name, 9);
2757 DEBUG('p_locator_id => '||p_locator_id, l_module_name, 9);
2758 DEBUG('p_entire_sub_flag => '||p_entire_sub_flag, l_module_name, 9);
2759
2760 END IF;
2761
2762 l_progress_indicator := '10';
2763
2764 DELETE wms_zone_locators
2765 WHERE zone_id = p_zone_id
2766 AND organization_id = p_organization_id
2767 AND subinventory_code = p_subinventory_code
2768 AND ( (p_entire_sub_flag = 'Y') OR (
2769 nvl(p_entire_sub_flag,'N') = 'N' AND
2770 inventory_location_id = p_locator_id ) );
2771
2772 IF l_debug > 0 THEN
2773 DEBUG('Deleted Rows : '||SQL%ROWCOUNT, l_module_name, 9);
2774 END IF;
2775
2776 x_return_status := fnd_api.g_ret_sts_success;
2777
2778 DEBUG('Call Success', l_module_name,0);
2779
2780 EXCEPTION
2781
2782 WHEN OTHERS THEN
2783 DEBUG('Unexpected Exception :' ||l_progress_indicator
2784 ||' : '||SQLERRM ,
2785 l_module_name,0);
2786
2787 x_return_status := fnd_api.g_ret_sts_unexp_error;
2788 x_msg_data := substrb(SQLERRM, 200);
2789 x_msg_count := 1;
2790
2791 END delete_locators;
2792
2793 END wms_zones_pvt;