[Home] [Help]
PACKAGE BODY: APPS.WMS_WCS_DIAGNOSTICS
Source
1 PACKAGE BODY wms_wcs_diagnostics AS
2 /* $Header: WMSDIAGB.pls 120.0 2005/05/25 08:57:58 appldev noship $ */
3
4 PROCEDURE LOG (p_data IN VARCHAR2)
5 IS
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 BEGIN
8 -- Output
9 DBMS_OUTPUT.put_line (p_data);
10
11 -- Insert into the log table
12 wms_carousel_integration_pvt.log(NULL,p_data);
13
14 COMMIT;
15 END;
16
17 --
18 --
19 FUNCTION get_config_parameter (
20 p_name IN VARCHAR2,
21 p_sequence_id IN NUMBER DEFAULT NULL
22 )
23 RETURN VARCHAR2
24 IS
25 v_value VARCHAR2 (4000) := NULL;
26
27 CURSOR c_config_parameter (
28 p_name IN VARCHAR2,
29 p_sequence_id IN NUMBER DEFAULT NULL
30 )
31 IS
32 SELECT CONFIG_VALUE
33 FROM wms_carousel_configuration
34 WHERE CONFIG_NAME = p_name
35 AND NVL (sequence_id, 0) = NVL (p_sequence_id, 0)
36 AND active_ind = 'Y';
37 BEGIN
38 OPEN c_config_parameter (p_name, p_sequence_id);
39
40 FETCH c_config_parameter
41 INTO v_value;
42
43 CLOSE c_config_parameter;
44
45 RETURN v_value;
46 EXCEPTION
47 WHEN OTHERS
48 THEN
49 RETURN NULL;
50 END;
51
52 --
53 --
54 PROCEDURE run
55 IS
56 BEGIN
57 -- set outbut buffer size
58 DBMS_OUTPUT.ENABLE (200000);
59 -- database objects
60 --check_objects;
61 -- Bug# 4088489
62 --check_privileges;
63
64 -- wms_integration
65 check_wms_integration;
66 -- configuration
67 check_configuration;
68 -- jobs
69 check_jobs;
70 END;
71
72 /*
73 --
74 --
75 PROCEDURE check_objects
76 IS
77 v_count NUMBER;
78 v_status VARCHAR2 (64);
79 v_version VARCHAR2 (256);
80 v_version_key VARCHAR2 (128);
81 v_object_name VARCHAR2 (128);
82 v_object_owner VARCHAR2 (128);
83 v_object_type VARCHAR2 (128);
84 v_object_count NUMBER := 0;
85
86 CURSOR c_object_count (
87 p_owner IN VARCHAR2,
88 p_name IN VARCHAR2,
89 p_type IN VARCHAR2
90 )
91 IS
92 SELECT COUNT (*)
93 FROM all_objects
94 WHERE object_name = p_name
95 AND owner = p_owner
96 AND object_type = p_type;
97
98 CURSOR c_object_status (
99 p_owner IN VARCHAR2,
100 p_name IN VARCHAR2,
101 p_type IN VARCHAR2
102 )
103 IS
104 SELECT status
105 FROM all_objects
106 WHERE object_name = p_name
107 AND owner = p_owner
108 AND object_type = p_type;
109
110 CURSOR c_package_version_count (
111 p_owner IN VARCHAR2,
112 p_name IN VARCHAR2,
113 p_type IN VARCHAR2,
114 p_version_key IN VARCHAR2
115 )
116 IS
117 SELECT COUNT (*)
118 FROM all_source
119 WHERE NAME = p_name
120 AND TYPE = p_type
121 AND UPPER (text) LIKE '%' || p_version_key || '%';
122
123 CURSOR c_package_version (
124 p_owner IN VARCHAR2,
125 p_name IN VARCHAR2,
126 p_type IN VARCHAR2,
127 p_version_key IN VARCHAR2
128 )
129 IS
130 SELECT SUBSTR (text,
131 INSTR (text, p_version_key) + LENGTH (p_version_key)
132 + 1,
133 LENGTH (text)
134 - INSTR (text, p_version_key)
135 - LENGTH (p_version_key)
136 - 1
137 )
138 FROM all_source
139 WHERE NAME = p_name
140 AND TYPE = p_type
141 AND UPPER (text) LIKE '%' || p_version_key || '%';
142 BEGIN
143 LOG ('================================== object status check');
144
145 -- Obtain total object count
146 SELECT MAX (sequence_id)
147 INTO v_object_count
148 FROM wms_carousel_configuration
149 WHERE CONFIG_NAME = 'DIAG_OBJECT_NAME';
150
151 -- Obtain version key parameter
152 v_version_key :=
153 UPPER
154 (NVL (get_config_parameter (p_name => 'DIAG_PACKAGE_VERSION_KEY'),
155 '$VERSION:'
156 )
157 );
158
159 -- Check all objects
160 FOR i IN 1 .. v_object_count
161 LOOP
162 v_object_name :=
163 UPPER (get_config_parameter (p_name => 'DIAG_OBJECT_NAME',
164 p_sequence_id => i
165 )
166 );
167 v_object_owner :=
168 UPPER (get_config_parameter (p_name => 'DIAG_OBJECT_OWNER',
169 p_sequence_id => i
170 )
171 );
172 v_object_type :=
173 UPPER (get_config_parameter (p_name => 'DIAG_OBJECT_TYPE',
174 p_sequence_id => i
175 )
176 );
177
178 IF NVL (v_object_name, 'noname') <> 'noname'
179 THEN
180 -- Obtain object count
181 OPEN c_object_count (v_object_owner, v_object_name,
182 v_object_type);
183
184 FETCH c_object_count
185 INTO v_count;
186
187 CLOSE c_object_count;
188
189 -- Check existance
190 IF v_count = 0
191 THEN
192 LOG ( '*** Error: '
193 || LOWER (v_object_type)
194 || ' '
195 || v_object_owner
196 || '.'
197 || v_object_name
198 || ' does not exist !'
199 );
200 ELSE
201 -- Obtain status
202 OPEN c_object_status (v_object_owner,
203 v_object_name,
204 v_object_type
205 );
206
207 FETCH c_object_status
208 INTO v_status;
209
210 CLOSE c_object_status;
211
212 -- Check status
213 IF UPPER (v_status) <> 'VALID'
214 THEN
215 LOG ( '*** Error: '
216 || LOWER (v_object_type)
217 || ' '
218 || v_object_owner
219 || '.'
220 || v_object_name
221 || ' status is '
222 || v_status
223 || ' !'
224 );
225 ELSE
226 LOG ( INITCAP (LOWER (v_object_type))
227 || ' '
228 || v_object_owner
229 || '.'
230 || v_object_name
231 || ' status is '
232 || v_status
233 );
234 END IF;
235
236 -- Is it a package or body ?
237 IF v_object_type = 'PACKAGE' OR v_object_type = 'PACKAGE BODY'
238 THEN
239 -- Obtain version count
240 OPEN c_package_version_count (v_object_owner,
241 v_object_name,
242 v_object_type,
243 v_version_key
244 );
245
246 FETCH c_package_version_count
247 INTO v_count;
248
249 CLOSE c_package_version_count;
250
251 -- Check existance of version
252 IF v_count = 0
253 THEN
254 LOG ( '*** Error: '
255 || LOWER (v_object_type)
256 || ' '
257 || v_object_owner
258 || '.'
259 || v_object_name
260 || ' does not have version !'
261 );
262 ELSE
263 -- Obtain version number
264 OPEN c_package_version (v_object_owner,
265 v_object_name,
266 v_object_type,
267 v_version_key
268 );
269
270 FETCH c_package_version
271 INTO v_version;
272
273 CLOSE c_package_version;
274
275 LOG ( INITCAP (LOWER (v_object_type))
276 || ' '
277 || v_object_owner
278 || '.'
279 || v_object_name
280 || ' version is '
281 || v_version
282 );
283 END IF;
284 END IF;
285 END IF;
286 END IF;
287 END LOOP;
288 EXCEPTION
289 WHEN OTHERS
290 THEN
291 LOG ('*** Error: exception in CHECK_OBJECTS: ' || SQLERRM || ' !');
292 END;
293 */
294 --
295 --
296 /*
297 PROCEDURE check_privileges
298 IS
299 v_count NUMBER;
300 v_privilege_object VARCHAR2 (128);
301 v_privilege_type VARCHAR2 (128);
302 v_privilege_owner VARCHAR2 (128);
303 v_privilege_grantee VARCHAR2 (128);
304 v_privilege_count NUMBER := 0;
305
306 CURSOR c_table_privilege_count (
307 p_owner IN VARCHAR2,
308 p_object IN VARCHAR2,
309 p_grantee IN VARCHAR2,
310 p_type IN VARCHAR2
311 )
312 IS
313 SELECT COUNT (*)
314 FROM all_tab_privs
315 WHERE table_schema = p_owner
316 AND table_name = p_object
317 AND grantee = p_grantee
318 AND PRIVILEGE = p_type;
319
320 CURSOR c_package_privilege_count (
321 p_owner IN VARCHAR2,
322 p_object IN VARCHAR2
323 )
324 IS
325 SELECT COUNT (*)
326 FROM all_procedures
327 WHERE owner = p_owner AND object_name = p_object;
328 BEGIN
329 LOG ('================================== object privilege check');
330
331 -- Obtain total object count
332 SELECT MAX (sequence_id)
333 INTO v_privilege_count
334 FROM wms_carousel_configuration
335 WHERE CONFIG_NAME = 'DIAG_PRIVILEGE_OBJECT';
336
337 -- Check all objects
338 FOR i IN 1 .. v_privilege_count
339 LOOP
340 v_privilege_object :=
341 UPPER (get_config_parameter (p_name => 'DIAG_PRIVILEGE_OBJECT',
342 p_sequence_id => i
343 )
344 );
345 v_privilege_owner :=
346 UPPER (get_config_parameter (p_name => 'DIAG_PRIVILEGE_OWNER',
347 p_sequence_id => i
348 )
349 );
350 v_privilege_grantee :=
351 UPPER (get_config_parameter (p_name => 'DIAG_PRIVILEGE_GRANTEE',
352 p_sequence_id => i
353 )
354 );
355 v_privilege_type :=
356 UPPER (get_config_parameter (p_name => 'DIAG_PRIVILEGE_TYPE',
357 p_sequence_id => i
358 )
359 );
360
361 IF NVL (v_privilege_object, 'noname') <> 'noname'
362 THEN
363 IF v_privilege_type IN
364 ('SELECT', 'UPDATE', 'INSERT', 'DELETE', 'EXECUTE')
365 THEN
366 -- Lookup table privilege
367 OPEN c_table_privilege_count (v_privilege_owner,
368 v_privilege_object,
369 v_privilege_grantee,
370 v_privilege_type
371 );
372
373 FETCH c_table_privilege_count
374 INTO v_count;
375
376 CLOSE c_table_privilege_count;
377
378 -- Check it
379 IF v_count = 0
380 THEN
381 LOG ( '*** Error: '
382 || v_privilege_grantee
383 || ' user does not have '
384 || v_privilege_type
385 || ' privilege on '
386 || v_privilege_owner
387 || '.'
388 || v_privilege_object
389 || ' !'
390 );
391 ELSE
392 LOG ( v_privilege_grantee
393 || ' user has '
394 || v_privilege_type
395 || ' privilege on '
396 || v_privilege_owner
397 || '.'
398 || v_privilege_object
399 );
400 END IF;
401 ELSE
402 LOG ( '*** Error: unknown privilege '
403 || v_privilege_type
404 || ' for user '
405 || v_privilege_grantee
406 || ' on '
407 || v_privilege_owner
408 || '.'
409 || v_privilege_object
410 || ' !'
411 );
412 END IF;
413 END IF;
414 END LOOP;
415 EXCEPTION
416 WHEN OTHERS
417 THEN
418 LOG ('*** Error: exception in CHECK_PRIVILEGES: ' || SQLERRM || ' !');
419 END;
420 */
421 --
422 --
423 PROCEDURE check_wms_integration
424 IS
425 v_cnt NUMBER := 0;
426 BEGIN
427 LOG ('================================== WMS/WCS integration check');
428
429 -- check for call to sync_device_request
430 SELECT COUNT (*)
431 INTO v_cnt
432 FROM user_source
433 WHERE NAME = 'WMS_DEVICE_INTEGRATION_PUB'
434 AND LOWER (text) LIKE
435 '%wms_carousel_integration_pub.sync_device_request%';
436
437 IF v_cnt > 0
438 THEN
439 LOG
440 ('WMS_DEVICE_INTEGRATION_PUB calls WMS_CAROUSEL_INTEGRATION_PUB.SYNC_DEVICE_REQUEST'
441 );
442 ELSE
443 LOG
447
444 ('*** Error: WMS_DEVICE_INTEGRATION_PUB does not call WMS_CAROUSEL_INTEGRATION_PUB.SYNC_DEVICE_REQUEST !'
445 );
446 END IF;
448 -- check for call to sync_device
449 SELECT COUNT (*)
450 INTO v_cnt
451 FROM user_source
452 WHERE NAME = 'WMS_DEVICE_INTEGRATION_PUB'
453 AND LOWER (text) LIKE '%wms_carousel_integration_pub.sync_device%'
454 AND LOWER (text) NOT LIKE
455 '%wms_carousel_integration_pub.sync_device_request%';
456
457 IF v_cnt > 0
458 THEN
459 LOG
460 ('WMS_DEVICE_INTEGRATION_PUB calls WMS_CAROUSEL_INTEGRATION_PUB.SYNC_DEVICE'
461 );
462 ELSE
463 LOG
464 ('*** Error: WMS_DEVICE_INTEGRATION_PUB does not call WMS_CAROUSEL_INTEGRATION_PUB.SYNC_DEVICE !'
465 );
466 END IF;
467 EXCEPTION
468 WHEN OTHERS
469 THEN
470 LOG ( '*** Error: exception in CHECK_WMS_INTEGRATION: '
471 || SQLERRM
472 || ' !'
473 );
474 END;
475
476 --
477 --
478 FUNCTION get_hash_value (v_data IN VARCHAR2)
479 RETURN NUMBER
480 IS
481 v_length NUMBER;
482 v_hash_value NUMBER := 0;
483 BEGIN
484 IF NVL (v_data, '<nullvalue>') = '<nullvalue>'
485 THEN
486 RETURN 0;
487 END IF;
488
489 v_length := LENGTH (v_data);
490
491 FOR i IN 1 .. v_length
492 LOOP
493 v_hash_value := v_hash_value + ASCII (SUBSTR (v_data, i, 1));
494 END LOOP;
495
496 RETURN v_hash_value;
497 END;
498
499 --
500 --
501 FUNCTION add_hash_values (v_first IN NUMBER, v_second IN NUMBER)
502 RETURN NUMBER
503 IS
504 BEGIN
505 RETURN v_first + v_second;
506 END;
507
508 --
509 --
510 FUNCTION calculate_config_hash_value
511 RETURN NUMBER
512 IS
513 v_calc_hash_value NUMBER := 0;
514
515 CURSOR c_configuration
516 IS
517 SELECT *
518 FROM wms_carousel_configuration
519 WHERE CONFIG_NAME <> 'DIAG_CONFIG_HASH_VALUE';
520 BEGIN
521 -- Calculate the hash value
522 FOR v_row IN c_configuration
523 LOOP
524 v_calc_hash_value :=
525 add_hash_values (v_calc_hash_value, get_hash_value (v_row.CONFIG_NAME));
526 v_calc_hash_value :=
527 add_hash_values (v_calc_hash_value, get_hash_value (v_row.CONFIG_VALUE));
528 v_calc_hash_value :=
529 add_hash_values (v_calc_hash_value,
530 get_hash_value (v_row.device_type_id)
531 );
532 v_calc_hash_value :=
533 add_hash_values (v_calc_hash_value,
534 get_hash_value (v_row.sequence_id)
535 );
536 v_calc_hash_value :=
537 add_hash_values (v_calc_hash_value,
538 get_hash_value (v_row.active_ind)
539 );
540 v_calc_hash_value :=
541 add_hash_values (v_calc_hash_value, get_hash_value (v_row.SUBINVENTORY));
542 END LOOP;
543
544 RETURN v_calc_hash_value;
545 END;
546
547 --
548 --
549 PROCEDURE update_hash_value
550 IS
551 v_calc_hash_value NUMBER := 0;
552 PRAGMA AUTONOMOUS_TRANSACTION;
553 BEGIN
554 -- Calculate the hash value
555 v_calc_hash_value := calculate_config_hash_value;
556
557 -- Update
558 UPDATE wms_carousel_configuration
559 SET CONFIG_VALUE = v_calc_hash_value
560 WHERE CONFIG_NAME = 'DIAG_CONFIG_HASH_VALUE' AND active_ind = 'Y';
561
562 -- Commit
563 COMMIT;
564 END;
565
566 --
567 --
568 PROCEDURE check_configuration
569 IS
570 v_config_hash_value NUMBER := 0;
571 v_calc_hash_value NUMBER := 0;
572 v_version VARCHAR2 (4000);
573 BEGIN
574 LOG ('================================== Configuration check');
575 -- Obtain version value
576 v_version :=
577 NVL (get_config_parameter (p_name => 'VERSION_COMMENTS'),
578 'no comments'
579 );
580 LOG ('Version comments: ' || v_version);
581 -- Obtain the specified hash value
582 v_config_hash_value :=
583 NVL (get_config_parameter (p_name => 'DIAG_CONFIG_HASH_VALUE'), '0');
584
585 -- Check to make sure there is 1 and only 1
586 IF v_config_hash_value = 0
587 THEN
588 LOG
589 ('*** Error: DIAG_CONFIG_HASH_VALUE configuration parameter not found or is invalid !'
590 );
591 ELSE
592 LOG ('The specified hash value is ' || v_config_hash_value);
593 -- Calculate the hash value
594 v_calc_hash_value := calculate_config_hash_value;
595 LOG ('Calculated hash value is ' || v_calc_hash_value);
596
597 -- Compare
598 IF v_config_hash_value <> v_calc_hash_value
599 THEN
600 LOG
601 ('*** Error: the specified and calculated hash values are not equal !'
602 );
603 ELSE
604 LOG ('The specified and calculated hash values are equal');
605 END IF;
606 END IF;
607 EXCEPTION
608 WHEN OTHERS
609 THEN
610 LOG ('*** Error: exception in CHECK_CONFIGURATION: ' || SQLERRM
611 || ' !'
612 );
613 END;
614
615 /* Commenting out this procedure as the following line is an error in GSCC
616 v_i2 := INSTR (v_row.VALUE, CHR (10), v_i1);
617 And all this procedure does is create an insert script which when run
618 on another DB will insert all the config data into it
619 */
620
621 --
622 -- examples: export_configuration('10,11','BA,BD'); export_configuration('*'/*all*/,'*'/*all*/);
623 /*
624 PROCEDURE export_configuration (
625 p_device_type_ids IN VARCHAR2,
626 p_zones IN VARCHAR2
627 )
628 IS
629 v_statement VARCHAR2 (1024);
630 v_statement1 VARCHAR2 (1024);
634 TYPE my_curs_type IS REF CURSOR; -- must be weakly typed
631 v_insert VARCHAR2 (10240);
632 v_row wms.wms_carousel_configuration%ROWTYPE;
633
635
636 c_config my_curs_type;
637 v_i1 NUMBER;
638 v_i2 NUMBER;
639 v_calc_hash_value NUMBER := 0;
640 BEGIN
641 -- set outbut buffer size
642 DBMS_OUTPUT.ENABLE (200000);
643 DBMS_OUTPUT.put_line ( '-- Configuration data for devices '
644 || p_device_type_ids
645 || ' and zones '
646 || p_zones
647 );
648 DBMS_OUTPUT.put_line ('set scan off;');
649 DBMS_OUTPUT.put_line ('delete from wms_carousel_configuration;');
650 -- Build dynamic SQL
651 v_statement :=
652 'select * from wms.wms_carousel_configuration where (1 = 0 ';
653
654 IF p_device_type_ids <> '*'
655 THEN
656 v_statement :=
657 v_statement || 'or device_type_id in (' || p_device_type_ids
658 || ') ';
659 ELSE
660 v_statement := v_statement || 'or device_type_id like ''%'' ';
661 END IF;
662
663 IF p_zones <> '*'
664 THEN
665 v_statement :=
666 v_statement
667 || 'or zone in ('''
668 || REPLACE (REPLACE (p_zones, ' ', ''), ',', ''',''')
669 || ''') ';
670 ELSE
671 v_statement := v_statement || 'or zone like ''%'' ';
672 END IF;
673
674 v_statement :=
675 v_statement || 'or (device_type_id is null and zone is null)) ';
676 --dbms_output.put_line('-- '||v_statement);
677 v_statement1 :=
678 'and name is not null and active_ind is not null '
679 || 'and name <> ''DIAG_CONFIG_HASH_VALUE'' '
680 || -- skip hash value and add at the end
681 'order by device_type_id, sequence_id, zone, active_ind, name';
682 --dbms_output.put_line('-- '||v_statement1);
683 v_statement := v_statement || v_statement1;
684
685 OPEN c_config
686 FOR v_statement;
687
688 LOOP
689 FETCH c_config
690 INTO v_row;
691
692 EXIT WHEN c_config%NOTFOUND;
693 DBMS_OUTPUT.put_line ('insert into wms_carousel_configuration');
694 DBMS_OUTPUT.put_line
695 (' (name, device_type_id, sequence_id, zone, active_ind, value)'
696 );
697 DBMS_OUTPUT.put_line ('values');
698 -- Name, device_type_id, and sequence_id
699 v_insert :=
700 ' ('
701 || ''''
702 || v_row.CONFIG_NAME
703 || ''','
704 || NVL ('' || v_row.device_type_id, 'null')
705 || ','
706 || NVL ('' || v_row.sequence_id, 'null')
707 || ',';
708
709 -- Zone
710 IF NVL (v_row.SUBINVENTORY, 'null') = 'null'
711 THEN
712 v_insert := v_insert || 'null,';
713 ELSE
714 v_insert := v_insert || '''' || v_row.SUBINVENTORY || ''',';
715 END IF;
716
717 -- Active indicator
718 v_insert := v_insert || '''' || v_row.active_ind || ''',';
719 -- print out what is so far
720 DBMS_OUTPUT.put_line (v_insert);
721
722 -- Value is null ?
723 IF NVL (v_row.CONFIG_VALUE, '<nullvalue>') = '<nullvalue>'
724 THEN
725 DBMS_OUTPUT.put_line (' null);');
726 ELSE
727 -- print out value one line at a time
728 v_i1 := 1;
729
730 LOOP
731 EXIT WHEN v_i1 > LENGTH (v_row.CONFIG_VALUE);
732 -- Find new line character
733 v_i2 := INSTR (v_row.CONFIG_VALUE, CHR (10), v_i1);
734
735 IF v_i2 = 0
736 THEN
737 v_i2 := LENGTH (v_row.CONFIG_VALUE) + 1;
738 END IF;
739
740 -- get the line
741 v_insert := SUBSTR (v_row.CONFIG_VALUE, v_i1, v_i2 - v_i1);
742
743 -- Null ?
744 IF NVL (v_insert, '<nullvalue>') = '<nullvalue>'
745 THEN
746 v_insert := 'null';
747 ELSE
748 -- Take care of single quotes
749 v_insert := '''' || REPLACE (v_insert, '''', '''''')
750 || '''';
751 END IF;
752
753 -- New line character
754 IF v_i1 = 1
755 THEN
756 v_insert := ' ' || v_insert;
757 ELSE
758 v_insert := ' ||chr(10)||' || v_insert;
759 END IF;
760
761 -- print out what is so far
762 DBMS_OUTPUT.put_line (v_insert);
763 -- next ?
764 EXIT WHEN v_i2 >= LENGTH (v_row.CONFIG_VALUE) + 1;
765 v_i1 := v_i2 + 1;
766 END LOOP;
767
768 -- Close parenthesis
769 DBMS_OUTPUT.put_line (' );');
770 END IF;
771
772 -- Hash value
773 v_calc_hash_value :=
774 add_hash_values (v_calc_hash_value, get_hash_value (v_row.CONFIG_NAME));
775 v_calc_hash_value :=
776 add_hash_values (v_calc_hash_value, get_hash_value (v_row.CONFIG_VALUE));
777 v_calc_hash_value :=
778 add_hash_values (v_calc_hash_value,
782 add_hash_values (v_calc_hash_value,
779 get_hash_value (v_row.device_type_id)
780 );
781 v_calc_hash_value :=
783 get_hash_value (v_row.sequence_id)
784 );
785 v_calc_hash_value :=
786 add_hash_values (v_calc_hash_value,
787 get_hash_value (v_row.active_ind)
788 );
789 v_calc_hash_value :=
790 add_hash_values (v_calc_hash_value, get_hash_value (v_row.SUBINVENTORY));
791 END LOOP;
792
793 CLOSE c_config;
794
795 -- Add hash value parameter
796 DBMS_OUTPUT.put_line ('insert into wms_carousel_configuration');
797 DBMS_OUTPUT.put_line (' (name, value)');
798 DBMS_OUTPUT.put_line ('values');
799 DBMS_OUTPUT.put_line ( ' (''DIAG_CONFIG_HASH_VALUE'','
800 || v_calc_hash_value
801 || ');'
802 );
803 -- Done
804 DBMS_OUTPUT.put_line ('commit;');
805 DBMS_OUTPUT.new_line;
806 EXCEPTION
807 WHEN OTHERS
808 THEN
809 DBMS_OUTPUT.put_line
810 ( '*** Error: exception in EXPORT_CONFIGURATION: '
811 || SQLERRM
812 || ' !'
813 );
814 END;
815 */
816
817 --
818 --
819 PROCEDURE check_jobs
820 IS
821 -- Cursor for receive pipes
822 CURSOR c_receive_pipes
823 IS
824 SELECT *
825 FROM wms_carousel_configuration
826 WHERE CONFIG_NAME = 'RECEIVE_PIPE' AND active_ind = 'Y';
827
828 CURSOR c_zone_job (p_zone IN VARCHAR2)
829 IS
830 SELECT *
831 FROM all_jobs
832 WHERE what LIKE
833 'wms_carousel_integration_pub.pipe_listener_loop%,'''
834 || p_zone
835 || '''%'
836 AND schema_user = 'APPS';
837
838 v_switch VARCHAR (16);
839 v_count NUMBER;
840 BEGIN
841 LOG ('================================== Pipe listener job check');
842 -- Obtain pipe listener switch
843 v_switch :=
844 NVL (get_config_parameter (p_name => 'PIPE_LISTENER_SWITCH'), 'OFF');
845
846 IF v_switch = 'OFF'
847 THEN
848 LOG
849 ('*** Error: PIPE_LISTENER_SWITCH is OFF - pipe listeners are down !'
850 );
851 ELSE
852 LOG ('PIPE_LISTENER_SWITCH is ON');
853 END IF;
854
855 -- Look for pipe listeners
856 FOR v_cfg IN c_receive_pipes
857 LOOP
858 -- Look for the job
859 v_count := 0;
860
861 FOR v_job IN c_zone_job (v_cfg.SUBINVENTORY)
862 LOOP
863 v_count := v_count + 1;
864 -- Log some stats
865 LOG ( 'Pipe listener job '
866 || v_job.job
867 || ' for zone '
868 || v_cfg.SUBINVENTORY
869 || ' is scheduled'
870 );
871 LOG ( 'Job '
872 || v_job.job
873 || ': last date = '
874 || TO_CHAR (v_job.last_date, 'MM/DD/YYYY HH24:MI:SS')
875 || ': this date = '
876 || TO_CHAR (v_job.this_date, 'MM/DD/YYYY HH24:MI:SS')
877 || ': next date = '
878 || TO_CHAR (v_job.next_date, 'MM/DD/YYYY HH24:MI:SS')
879 );
880 LOG ( 'Job '
881 || v_job.job
882 || ' has been running for '
883 || NVL (v_job.total_time, 0)
884 || ' seconds and has had '
885 || NVL (v_job.failures, 0)
886 || ' failures'
887 );
888
889 -- Broken ?
890 IF UPPER (v_job.broken) = 'Y'
891 THEN
892 LOG ( '*** Error: job '
893 || v_job.job
894 || ' is currently broken, restart pipe listeners !'
895 );
896 ELSE
897 LOG ('Job ' || v_job.job || ' is currently NOT broken');
898 END IF;
899 END LOOP;
900
901 -- Verify job count for the zone
902 IF v_count = 0
903 THEN
904 LOG ( '*** Error: no jobs are scheduled for zone '
905 || v_cfg.SUBINVENTORY
906 || ', resubmit pipe listeners !'
907 );
908 ELSIF v_count > 1
909 THEN
910 LOG ( '*** Error: more than one job is schedules for zone '
911 || v_cfg.SUBINVENTORY
912 || ', resubmit pipe listeners !'
913 );
914 END IF;
915 END LOOP;
916 EXCEPTION
917 WHEN OTHERS
918 THEN
919 DBMS_OUTPUT.put_line ( '*** Error: exception in CHECK_JOBS: '
920 || SQLERRM
921 || ' !'
922 );
923 END;
924
925 --
926 --
927 FUNCTION get_segment (
928 p_data IN VARCHAR2,
929 p_separator IN VARCHAR2,
930 p_index IN NUMBER
931 )
935 v_i1 NUMBER;
932 RETURN VARCHAR2
933 IS
934 v_segment VARCHAR2 (128);
936 v_i2 NUMBER;
937 BEGIN
938 IF p_index = 1
939 THEN
940 v_i1 := 0;
941 ELSE
942 v_i1 := INSTR (p_data, p_separator, 1, p_index - 1);
943
944 IF v_i1 = 0
945 THEN
946 RETURN NULL;
947 END IF;
948 END IF;
949
950 v_i2 := INSTR (p_data, p_separator, 1, p_index);
951
952 IF v_i2 = 0
953 THEN
954 v_i2 := LENGTH (p_data) + 1;
955 END IF;
956
957 RETURN SUBSTR (p_data, v_i1 + 1, v_i2 - v_i1 - 1);
958 EXCEPTION
959 WHEN OTHERS
960 THEN
961 RETURN NULL;
962 END;
963
964 --
965 --
966 PROCEDURE test_task (
967 p_zone IN VARCHAR2,
968 p_device_type_id IN NUMBER,
969 p_locator IN VARCHAR2,
970 p_directive IN VARCHAR2 DEFAULT 'GO',
971 p_task_type_id IN NUMBER DEFAULT 10,
972 p_quantity IN NUMBER DEFAULT 123,
973 p_device_id IN NUMBER DEFAULT 0,
974 p_business_event_id IN NUMBER DEFAULT 10,
975 p_lpn IN VARCHAR2 DEFAULT 'TestLPN'
976 )
977 IS
978 v_send_pipe VARCHAR2 (128);
979 v_status NUMBER;
980 v_status_code VARCHAR2 (128);
981 v_status_msg VARCHAR2 (128);
982 v_device_status VARCHAR2 (128);
983 v_segment1 VARCHAR2 (32);
984 v_segment2 VARCHAR2 (32);
985 v_segment3 VARCHAR2 (32);
986 v_segment4 VARCHAR2 (32);
987 v_segment5 VARCHAR2 (32);
988 v_segment6 VARCHAR2 (32);
989 v_segment7 VARCHAR2 (32);
990 v_segment8 VARCHAR2 (32);
991 v_segment9 VARCHAR2 (32);
992 v_segment10 VARCHAR2 (32);
993 v_request_id NUMBER := 999999999;
994 PRAGMA AUTONOMOUS_TRANSACTION;
995 BEGIN
996 -- set outbut buffer size
997 DBMS_OUTPUT.ENABLE (200000);
998 LOG ('================================== Test task');
999 -- Derive segments
1000 v_segment1 := get_segment (p_locator, '.', 1);
1001 v_segment2 := get_segment (p_locator, '.', 2);
1002 v_segment3 := get_segment (p_locator, '.', 3);
1003 v_segment4 := get_segment (p_locator, '.', 4);
1004 v_segment5 := get_segment (p_locator, '.', 5);
1005 v_segment6 := get_segment (p_locator, '.', 6);
1006 v_segment7 := get_segment (p_locator, '.', 7);
1007 v_segment8 := get_segment (p_locator, '.', 8);
1008 v_segment9 := get_segment (p_locator, '.', 9);
1009 v_segment10 := get_segment (p_locator, '.', 10);
1010 -- Create a test task
1011 LOG ( 'Creating test task: request_id='
1012 || v_request_id
1013 || ', locator='
1014 || p_locator
1015 || ', quantity='
1016 || p_quantity
1017 );
1018 /*
1019 DELETE FROM wms_wcs_request_test;
1020
1021 INSERT INTO wms_wcs_request_test
1022 (request_id, task_id, sequence_id, task_type_id, quantity,
1023 subinventory_code, LOCATOR, device_name, device_id,
1024 device_type_id, business_event, business_event_id,
1025 relation_id, lpn, item, segment1, segment2,
1026 segment3, segment4, segment5, segment6,
1027 segment7, segment8, segment9, segment10
1028 )
1029 VALUES (v_request_id, 1, 1, p_task_type_id, p_quantity,
1030 'TestSI', p_locator, 'TestDev', p_device_id,
1031 p_device_type_id, 'TestPick', p_business_event_id,
1032 NULL, p_lpn, 'TestItem', v_segment1, v_segment2,
1033 v_segment3, v_segment4, v_segment5, v_segment6,
1034 v_segment7, v_segment8, v_segment9, v_segment10
1035 );
1036 */
1037 COMMIT;
1038 -- Update to simulation mode
1039 LOG ('Changing zone ' || p_zone || ' to simulation mode');
1040
1041 UPDATE wms_carousel_configuration
1042 SET CONFIG_VALUE = 'ON'
1043 WHERE CONFIG_NAME = 'SIMULATION_MODE'
1044 AND (device_type_id = p_device_type_id OR device_type_id IS NULL)
1045 AND (SUBINVENTORY = p_zone OR SUBINVENTORY IS NULL);
1046
1047 COMMIT;
1048 -- Get send pipe parameter
1049 v_send_pipe := 'OUT_' ||
1050 NVL
1051 (wms_carousel_integration_pvt.get_config_parameter
1052 (p_name => 'PIPE_NAME',
1053 p_sequence_id => p_device_id
1054 ),
1055 'PIPE_NAME_' || p_device_id
1056 );
1057
1058 -- Tell the bridge to reread parameters to switch to simulation
1059 wms_carousel_integration_pvt.send_directive (p_device_id => p_device_id,
1060 p_pipe_name => v_send_pipe,
1061 p_addr => NULL,
1062 p_directive => 'R',
1063 p_time_out => 10
1064 -- 10 sec send timeout
1065 );
1066 -- Wait for 5 seconds
1067 v_status := DBMS_PIPE.receive_message ('wcs_nonexistant_pipe', 5);
1068 -- Process the request
1069 LOG ('Processing the test request');
1070 wms_carousel_integration_pvt.process_request
1071 (p_request_id => v_request_id,
1072 x_status_code => v_status_code,
1073 x_status_msg => v_status_msg,
1074 x_device_status => v_device_status
1075 );
1076 -- Wait for 2 minutes
1077 LOG ('Waiting for 3 minutes');
1078 v_status := DBMS_PIPE.receive_message ('wcs_nonexistant_pipe', 180);
1079
1080 -- Look for the answer
1081 SELECT status, attempts
1082 INTO v_status_code, v_status
1083 FROM wms_carousel_directive_queue
1084 WHERE request_id = v_request_id AND directive = p_directive;
1085
1086 IF v_status_code <> 'S'
1087 THEN
1088 LOG ( '*** Error: status code for the '
1089 || p_directive
1090 || ' directive is '
1091 || v_status_code
1092 || ' on the attempt '
1093 || v_status
1094 || ' !'
1095 );
1096 ELSE
1097 LOG ('Success !');
1098 END IF;
1099
1100 -- Update to non-simulation mode
1101 LOG ('Switching off simulation in zone ' || p_zone);
1102
1103 UPDATE wms_carousel_configuration
1104 SET CONFIG_VALUE = 'OFF'
1105 WHERE CONFIG_NAME = 'SIMULATION_MODE'
1106 AND (device_type_id = p_device_type_id OR device_type_id IS NULL)
1107 AND (SUBINVENTORY = p_zone OR SUBINVENTORY IS NULL);
1108
1109 COMMIT;
1110 -- Tell the bridge to reread parameters
1111 wms_carousel_integration_pvt.send_directive (p_device_id => p_device_id,
1112 p_pipe_name => v_send_pipe,
1113 p_addr => 1,
1114 p_directive => 'R',
1115 p_time_out => 10
1116 -- 10 sec send timeout
1117 );
1118
1119 -- Get rid of the test request
1120 -- DELETE FROM wms_wcs_request_test;
1121
1122 DELETE FROM wms_carousel_directive_queue
1123 WHERE request_id = v_request_id;
1124
1125 COMMIT;
1126 EXCEPTION
1127 WHEN OTHERS
1128 THEN
1129 DBMS_OUTPUT.put_line ( '*** Error: exception in TEST_GO: '
1130 || SQLERRM
1131 || ' !'
1132 );
1133 END;
1134 END wms_wcs_diagnostics;