1 PACKAGE BODY cz_cx_util_pvt AS
2 /* $Header: czvcxub.pls 120.1 2006/04/27 11:41:32 skudryav ship $ */
3
4 FC_OUTPUT CONSTANT VARCHAR2(1) := '5';
5 FC_AUTOCONFIG CONSTANT VARCHAR2(1) := '6';
6 CX_OUTPUT CONSTANT VARCHAR2(2) := '31';
7 CX_AUTOCONFIG CONSTANT VARCHAR2(2) := '32';
8
9 DEFAULT_INCREMENT NUMBER := 20;
10 m_rule_id NUMBER := 0; -- next available seq no allocated
11 m_last_rule_id NUMBER := 0; -- last seq no in allocated block
12 m_increment NUMBER := DEFAULT_INCREMENT;
13
14 --------------------------------------------------------------------------------
15 FUNCTION get_next_rule_id RETURN NUMBER
16 IS
17 BEGIN
18 IF (m_rule_id = 0 OR m_rule_id = m_last_rule_id) THEN
19 IF (m_rule_id = 0) THEN
20 BEGIN
21 SELECT NVL(cz_utils.conv_num(value), DEFAULT_INCREMENT) INTO m_increment
22 FROM CZ_DB_SETTINGS
23 WHERE section_name = 'SCHEMA' AND setting_id = 'OracleSequenceIncr';
24 EXCEPTION
25 WHEN NO_DATA_FOUND THEN
26 NULL;
27 END;
28 END IF;
29
30 SELECT cz_rules_s.NEXTVAL INTO m_rule_id FROM DUAL;
31 m_last_rule_id := m_rule_id + m_increment - 1;
32 ELSE
33 m_rule_id := m_rule_id + 1;
34 END IF;
35
36 RETURN m_rule_id;
37 END get_next_rule_id;
38
39 --------------------------------------------------------------------------------
40 -- Converting old functional companion data to configurator extension foramt.
41 -- Returns return_status 1 if the conversion process is successful, 0 otherwise.
42 -- The tasks performed here are as follows.
43 -- 1. transform old fc recs to new cx rule data: cz_func_comp_specs => cz_rules
44 -- 2. update cz_rule_folders's id and type: func_comp_id, FNC => rule_id, CXT
45 -- 3. convert old ui fc data to new cx form
46 -- cz_ui_nodes: func_comp_id => cx_command_name
47 -- cz_ui_node_props: old value_str type (5,6) => new value_str type (31,32)
48 -- Note: event binding expression trees are created by Java program after
49 -- the above three tasks are finished. At the end of migration process, old fc
50 -- recs will be logically deleted by the same java program.
51 --
52 PROCEDURE convert_fc_by_model(p_model_id IN NUMBER
53 ,p_deep_migration_flag IN VARCHAR2
54 ,x_num_fc_processed OUT NOCOPY NUMBER
55 ,x_return_status OUT NOCOPY VARCHAR2
56 ,x_msg_data OUT NOCOPY VARCHAR2
57 )
58 IS
59 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
60 model_tbl number_tbl_type;
61 fc_tbl number_tbl_type;
62 seq_tbl number_tbl_type;
63 fc2rule_map number_tbl_type;
64 deluinodes_ids_tbl number_tbl_type;
65 deluinodes_uidefids_tbl number_tbl_type;
66
67 l_fc_count INTEGER := 0;
68 l_rule_id NUMBER;
69 l_dummy NUMBER;
70 l_stat NUMBER := 0;
71
72 l_command_name VARCHAR2(255);
73 l_value_str cz_ui_node_props.value_str%TYPE;
74
75 no_source_model_exc EXCEPTION;
76 no_ui_fc_exc EXCEPTION;
77
78 BEGIN
79 -- verify input model is a source model
80 BEGIN
81 SELECT 1 INTO l_dummy FROM cz_rp_entries
82 WHERE object_id = p_model_id AND object_type = 'PRJ' AND deleted_flag = '0';
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 RAISE no_source_model_exc;
86 END;
87 l_stat := 1;
88
89 UPDATE cz_func_comp_specs
90 SET rule_folder_type = NULL
91 WHERE rule_folder_type IS NOT NULL AND deleted_flag = '0';
92 l_stat := 2;
93
94 -- get all child models if necessary
95 -- flag value depends on value_set cz_yes_no setting
96 IF (upper(p_deep_migration_flag) = 'YES') THEN
97 SELECT DISTINCT component_id
98 BULK COLLECT INTO model_tbl
99 FROM cz_model_ref_expls
100 WHERE model_id = p_model_id AND ps_node_type = 263
101 AND deleted_flag = '0';
102 END IF;
103 l_stat := 3;
104
105 model_tbl(model_tbl.COUNT + 1) := p_model_id;
106
107 FOR i IN model_tbl.FIRST .. model_tbl.LAST LOOP
108 SELECT rule_folder_id, tree_seq
109 BULK COLLECT INTO fc_tbl, seq_tbl
110 FROM cz_rule_folders
111 WHERE devl_project_id = model_tbl(i)
112 AND object_type = 'FNC' AND deleted_flag = '0';
113 l_stat := 3.1;
114
115 IF (fc_tbl.COUNT > 0 ) THEN
116 FOR j IN fc_tbl.FIRST .. fc_tbl.LAST LOOP
117 l_rule_id := get_next_rule_id;
118 UPDATE cz_func_comp_specs
119 SET rule_folder_type = l_rule_id
120 WHERE func_comp_id = fc_tbl(j);
121 l_stat := 3.2;
122
123 UPDATE cz_rule_folders
124 SET rule_folder_id = l_rule_id, object_type = 'CXT'
125 WHERE rule_folder_id = fc_tbl(j) AND object_type = 'FNC';
126 l_stat := 3.3;
127
128 INSERT INTO cz_rules(rule_id
129 ,persistent_rule_id
130 ,rule_type
131 ,seq_nbr
132 ,invalid_flag
133 ,disabled_flag
134 ,mutable_flag
135 ,effective_usage_mask
136 ,effective_from
137 ,effective_until
138 ,instantiation_scope
139 ,class_name
140 ,devl_project_id
141 ,component_id
142 ,model_ref_expl_id
143 ,rule_folder_id
144 ,name
145 ,desc_text
146 )
147 SELECT l_rule_id
148 ,l_rule_id
149 ,300
150 ,seq_tbl(j)
151 ,'0'
152 ,'0'
153 ,'0'
154 ,'0000000000000000'
155 ,cz_utils.EPOCH_BEGIN
156 ,cz_utils.EPOCH_END
157 ,1
158 ,program_string
159 ,devl_project_id
160 ,component_id
161 ,model_ref_expl_id
162 ,rule_folder_id
163 ,name
164 ,desc_text
165 FROM cz_func_comp_specs
166 WHERE func_comp_id = fc_tbl(j);
167
168 l_fc_count := l_fc_count + 1;
169 l_stat := 3.4;
170 END LOOP;
171 END IF;
172 END LOOP;
173 l_stat := 4;
174
175 -- convert fc data in ui subschema if there exists any
176 -- build fc to rule id lookup map
177 FOR fc_rec IN (SELECT func_comp_id, rule_folder_type
178 FROM cz_func_comp_specs
179 WHERE deleted_flag = '0'
180 AND rule_folder_type IS NOT NULL) LOOP
181 fc2rule_map(fc_rec.func_comp_id) := fc_rec.rule_folder_type;
182 END LOOP;
183 l_stat := 5;
184
185 -- convert fc to cx
186 FOR i IN model_tbl.FIRST .. model_tbl.LAST LOOP
187 FOR ui_rec IN (SELECT def.ui_def_id, node.ui_node_id,
188 node.func_comp_id, prop.value_str
189 FROM cz_ui_defs def, cz_ui_nodes node, cz_ui_node_props prop
190 WHERE def.devl_project_id = model_tbl(i)
191 AND def.deleted_flag = '0'
192 AND def.ui_def_id = node.ui_def_id
193 AND node.deleted_flag = '0'
194 AND node.func_comp_id IS NOT NULL
195 AND node.ui_def_id = prop.ui_def_id
196 AND node.ui_node_id = prop.ui_node_id
197 AND prop.key_str = 'ActionType'
198 AND prop.value_str IN (FC_OUTPUT, FC_AUTOCONFIG)
199 AND prop.deleted_flag = '0'
200 AND node.parent_id <> (SELECT ui_node_id FROM CZ_UI_NODES
201 WHERE ui_def_id=def.ui_def_id AND
202 name='Limbo' AND
203 deleted_flag='0')
204 ) LOOP
205 l_stat := 5.1;
206 IF (NOT fc2rule_map.EXISTS(ui_rec.func_comp_id)) THEN
207
208 -- collect those UI nodes ( buttons ) which have a references to unexisting Functional Companions
209 deluinodes_ids_tbl(deluinodes_ids_tbl.COUNT+1) := ui_rec.ui_node_id;
210 deluinodes_uidefids_tbl(deluinodes_uidefids_tbl.COUNT+1) := ui_rec.ui_def_id;
211
212 END IF;
213
214 IF (ui_rec.value_str = FC_OUTPUT) THEN
215 l_command_name := fc2rule_map(ui_rec.func_comp_id) || '_GO';
216 l_value_str := CX_OUTPUT; -- GO = generateOutput
217 ELSE
218 l_command_name := fc2rule_map(ui_rec.func_comp_id) || '_AC';
219 l_value_str := CX_AUTOCONFIG; -- AC = autoconfig
220 END IF;
221
222 UPDATE cz_ui_nodes
223 SET cx_command_name = l_command_name, func_comp_id = NULL
224 WHERE ui_node_id = ui_rec.ui_node_id;
225 l_stat := 5.2;
226
227 UPDATE cz_ui_node_props
228 SET value_str = l_value_str
229 WHERE ui_def_id = ui_rec.ui_def_id
230 AND ui_node_id = ui_rec.ui_node_id
231 AND key_str = 'ActionType';
232 l_stat := 5.3;
233 END LOOP;
234 END LOOP;
235 l_stat := 6;
236
237 IF deluinodes_ids_tbl.COUNT > 0 THEN
238 -- collect those UI nodes ( buttons ) which have a references to unexisting Functional Companions
239 FORALL k IN deluinodes_ids_tbl.First..deluinodes_ids_tbl.Last
240 UPDATE CZ_UI_NODES
241 SET deleted_flag='1'
242 WHERE ui_def_id=deluinodes_uidefids_tbl(k) AND ui_node_id=deluinodes_ids_tbl(k);
243
244 FORALL k IN deluinodes_ids_tbl.First..deluinodes_ids_tbl.Last
245 UPDATE CZ_UI_NODE_PROPS
246 SET deleted_flag='1'
247 WHERE ui_def_id=deluinodes_uidefids_tbl(k) AND ui_node_id=deluinodes_ids_tbl(k);
248 END IF;
249
250 x_num_fc_processed := l_fc_count;
251 x_return_status := 1;
252 EXCEPTION
253 WHEN no_source_model_exc THEN
254 x_return_status := 0;
255 x_msg_data := 'Error in cz_cx_util_pvt.convert_fc_by_model: the model with '
256 || 'id ' || p_model_id || ' is not a source model.';
257 WHEN no_ui_fc_exc THEN
258 x_return_status := 0;
259 WHEN OTHERS THEN
260 ROLLBACK;
261 x_return_status := 0;
262 x_msg_data := 'Error in cz_cx_util_pvt.convert_fc_by_model, stat=' || l_stat
263 || ': ' || SQLERRM;
264 END convert_fc_by_model;
265
266 --------------------------------------------------------------------------------
267
268 END cz_cx_util_pvt;
[Home] [Help]