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