1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
DECLARE
cur_not_used_type MENU_TYPE%ROWTYPE;
cur_gen_menu GENERIC_MENU%ROWTYPE;
cur_option_menu OPTION_MENU%ROWTYPE;
tmp_gen_menu GENERIC_MENU%ROWTYPE;
BEGIN
BEGIN
ALTER TABLE MENU_TYPE
DROP COLUMN MENU_ORDER;
ALTER TABLE MENU_TYPE
DROP COLUMN INDIC_VISIBLE;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Column already dropped');
END;
DELETE FROM OPTION_MENU
WHERE TYPE_ID NOT IN ('FPI','BPI',
'CAR','SHO','SOF',
'ARR','ADO',
'RMO','RMR','IRR',
'UHF','TRO','HOW',
'IDI','KNI','TII');
--These generic menu must be deleted
FOR cur_gen_menu IN (SELECT * FROM WHERE TYPE_ID NOT IN ('DES','REM','TRO'))
LOOP
--manage the option menu
FOR cur_option_menu IN (SELECT * FROM OPTION_MENU WHERE GENERIC_MENU_ID = cur_gen_menu.GENERIC_MENU_ID)
LOOP
IF cur_option_menu.TYPE_ID = 'FPI' OR
cur_option_menu.TYPE_ID = 'BPI' OR
cur_option_menu.TYPE_ID = 'CAR' OR
cur_option_menu.TYPE_ID = 'SHO' OR
cur_option_menu.TYPE_ID = 'SOF'
THEN
BEGIN
SELECT *
INTO tmp_gen_menu
FROM GENERIC_MENU
WHERE TYPE_ID = 'DES'
AND MODEL_ID = cur_gen_menu.MODEL_ID;
UPDATE OPTION_MENU
SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No DES Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
END;
ELSIF cur_option_menu.TYPE_ID = 'ARR' OR
cur_option_menu.TYPE_ID = 'ADO' OR
cur_option_menu.TYPE_ID = 'RMO' OR
cur_option_menu.TYPE_ID = 'RMR' OR
cur_option_menu.TYPE_ID = 'IRR' OR
cur_option_menu.TYPE_ID = 'UHF'
THEN
BEGIN
SELECT *
INTO tmp_gen_menu
FROM GENERIC_MENU
WHERE TYPE_ID = 'REM'
AND MODEL_ID = cur_gen_menu.MODEL_ID;
UPDATE OPTION_MENU
SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No REM Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
END;
ELSIF cur_option_menu.TYPE_ID = 'TRO' OR
cur_option_menu.TYPE_ID = 'HOW' OR
cur_option_menu.TYPE_ID = 'IDI' OR
cur_option_menu.TYPE_ID = 'KNI' OR
cur_option_menu.TYPE_ID = 'TII'
THEN
BEGIN
SELECT *
INTO tmp_gen_menu
FROM GENERIC_MENU
WHERE TYPE_ID = 'TRO'
AND MODEL_ID = cur_gen_menu.MODEL_ID;
UPDATE OPTION_MENU
SET GENERIC_MENU_ID = tmp_gen_menu.GENERIC_MENU_ID
WHERE OPTION_MENU_ID = cur_option_menu.OPTION_MENU_ID;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No TRO Generic Menu for the model ID ' || cur_gen_menu.MODEL_ID);
END;
END IF;
END LOOP;
END LOOP;
DELETE FROM GENERIC_MENU
WHERE TYPE_ID NOT IN ('DES','REM','TRO');
DELETE FROM MENU_TYPE
WHERE TYPE_ID NOT IN ('FPI','BPI','DES',
'CAR','SHO','SOF',
'ARR','ADO','REM',
'RMO','RMR','IRR',
'UHF','TRO','HOW',
'IDI','KNI','TII');
END; |
