1 PACKAGE BODY CCT_CLASS_ENGINE_UPG as
2 /* $Header: cctupgcb.pls 115.13 2003/12/10 01:08:23 gvasvani noship $ */
3
4 procedure Upgrade_Class_Schema
5 IS
6 l_upgraded NUMERIC;
7 l_updated VARCHAR2(1) := 'N';
8 status NUMERIC;
9 l_loop_count INTEGER := 0;
10 l_count INTEGER := 0;
11 l_deleted_count INTEGER := 0;
12 BEGIN
13 BEGIN
14 SELECT Count(*) INTO l_upgraded
15 FROM CCT_CLASSIFICATIONS
16 WHERE CLASSIFICATION_VALUE_ID IS NOT NULL ;
17 --EXECUTE IMMEDIATE 'DROP SEQUENCE cct_upgclass_priority_s';
18 -- EXECUTE IMMEDIATE 'CREATE SEQUENCE cct_upgclass_priority_s START WITH 1';
19 EXCEPTION
20 When Others Then
21 status := SQLCODE;
22 --dbms_output.put_line('In Upgrade_Class_Schema Proc '||SQLERRM(STATUS));
23 return;
24 END;
25 if (l_upgraded > 0) THEN
26 --dbms_output.put_line('Classification Schema is Already Upgraded');
27 return;
28 END IF;
29 insert into cct_classification_values (classification_value_id, classification_value,seeded,
30 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,f_deletedflag)
31 select cct_classification_values_s.nextval, classification,'N',
32 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
33 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,a.f_deletedflag from cct_classifications a
34 where (upper(type)= 'LITERAL') AND classification_value_id is null
35 AND upper(classification) <> 'UNCLASSIFIED';
36
37 insert into cct_classification_values (classification_value_id, classification_value,
38 seeded,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
39 select cct_classification_values_s.nextval, 'unClassified', 'Y',
40 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
41 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1 from dual;
42
43 --dbms_output.put_line('Classification Values is Upgraded');
44 insert into cct_plsql_functions (function_id, function, function_name, package,appdb,
45 dburl, dbdriver, return_type,seeded,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
46 CREATED_BY)
47 select cct_plsql_functions_s.nextval,substr(classification, instr(classification,'.',1)+1),
48 classification,substr(classification,1, instr(classification,'.',1)-1),
49 DECODE(db_driver, nvl(db_driver,'Y'),'N','Y'), dburl, db_driver, 'occtClassification',
50 'N', TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'),1
51 from cct_classifications a
52 where (upper(type)= 'DBPROC')
53 AND classification_value_id is null AND nvl(a.f_deletedflag,'N') <> 'D';
54
55 --dbms_output.put_line(' PL/SQL Functions is Upgraded');
56
57 FOR l_record IN (
58 SELECT classification_id, classification FROM cct_classifications
59 WHERE type = 'LITERAL' order by f_deletedflag desc)
60 LOOP
61 l_loop_count := l_loop_count + 1;
62 l_updated := 'N';
63 select COUNT(*) into l_count from cct_classification_values where l_record.classification = classification_value
64 and nvl(f_deletedflag,'N')<>'D';
65 select COUNT(*) into l_deleted_count from cct_classification_values where l_record.classification = classification_value
66 and nvl(f_deletedflag,'N')='D';
67 FOR l_classValue_record IN (select classification_value_id, nvl(f_deletedflag,'N') f_deletedflag
68 from cct_classification_values where l_record.classification = classification_value
69 OR (upper(l_record.classification)='UNCLASSIFIED' AND classification_value='unClassified'))
70 LOOP
71 IF ((l_count >= 1 OR l_deleted_count >= 1 OR upper(l_record.classification)='UNCLASSIFIED') AND l_updated = 'N') THEN
72 l_updated := 'Y';
73 --In case we have multiple classifications defined in cct_classifications table, We don't
74 --want to update cct_classification table with already deleted classification. Although we still
75 --want deleted classificaiton value to sit into cct_classification_values table (due to Business Intelligence req).
76 update cct_classifications a set a.classification_value_id = l_classValue_record.classification_value_id,
77 a.IsEnabled = nvl(a.IsEnabled,'YES'),
78 a.Priority = l_loop_count,
79 a.Rule_Chaining = nvl(a.Rule_Chaining,'AND')
80 where a.classification_id = l_record.classification_id;
81 --DBMS_OUTPUT.PUT_LINE('Inserted into cct_classifications table '||l_classValue_record.classification_value_id||' and '||l_record.classification);
82 END IF;
83 END LOOP;
84 END LOOP;
85
86
87 FOR l_record IN (
88 SELECT classification_id, classification FROM cct_classifications
89 WHERE type = 'DBPROC' AND nvl(f_deletedflag,'N')<>'D')
90 LOOP
91 l_loop_count := l_loop_count + 1;
92 update cct_classifications a set a.classification_value_id = (select b.function_id
93 from cct_plsql_functions b
94 where l_record.classification = b.package||decode(b.package, '','','.')||b.function
95 AND nvl(b.f_deletedflag,'N')<>'D'), a.IsEnabled = nvl(a.IsEnabled,'YES'),
96 a.Priority = l_loop_count,
97 a.Rule_Chaining = nvl(a.Rule_Chaining,'AND')
98 where a.classification_id = l_record.classification_id;
99 END LOOP;
100
101 --dbms_output.put_line(' Populate cct_classifications table with PL/SQL Function ids');
102 insert into cct_plsql_function_params (function_param_id,function_id,param,datatype,direction,
103 sequence,value,LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
104 select cct_plsql_function_params_s.nextval,b.classification_value_id,a.param,
105 a.datatype,a.direction, a.sequence,a.value, TO_DATE ('01/01/1951', 'DD/MM/YYYY'),
106 1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1
107 from cct_route_params a, cct_classifications b
108 where a.classification_id=b.classification_id AND upper(b.type)='DBPROC'
109 AND nvl(a.f_deletedflag,'N')<>'D' AND nvl(b.f_deletedflag,'N')<>'D';
110
111 --dbms_output.put_line('Populated pl/sql function params..');
112 insert into cct_classification_sg_map (classification_sg_map_id, classification_id,
113 server_group_id, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
114 select cct_classification_sg_map_s.nextval, b.classification_id, a.server_group_id,
115 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1
116 from ieo_svr_groups a, cct_classifications b
117 where a.group_group_id is null AND nvl(b.f_deletedflag,'N')<>'D';
118
119 --dbms_output.put_line(' Populate cct_sg_map table ');
120 insert into cct_classification_mt_map (classification_mt_map_id,
121 classification_id, media_type_uuid,LAST_UPDATE_DATE, LAST_UPDATED_BY,
122 CREATION_DATE, CREATED_BY)
123 select cct_classification_mt_map_s.nextval, b.classification_id,
124 a.media_type_uuid, TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,
125 TO_DATE ('01/01/1951', 'DD/MM/YYYY'),
126 1 from cct_supported_media_types a, cct_classifications b
127 where nvl(b.f_deletedflag,'N')<>'D';
128
129 --dbms_output.put_line(' Populate cct_mt_map table ');
130
131 END Upgrade_Class_Schema;
132
133
134 procedure fix_class_priorities
135 IS
136 Cursor csr_Server_Groups IS
137 Select distinct Server_Group_id
138 From cct_classification_sg_map
139 where nvl(f_deletedflag,'N')<>'D';
140
141 CURSOR csr_classifications(p_server_group_id number) IS
142 SELECT map.classification_id
143 FROM cct_classification_sg_map map,cct_classifications class
144 where map.server_group_id=p_server_Group_id
145 and nvl(map.f_deletedflag,'N')<>'D'
146 and map.priority is null
147 and map.classification_id=class.classification_id
148 and nvl(class.f_deletedflag,'N')<>'D'
149 ORDER BY class.priority;
150 l_server_Group_id Number;
151 l_priority Number;
152 l_class_id Number;
153 BEGIN
154 SAVEPOINT fix_class_priorities_pt;
155
156 OPEN csr_server_groups;
157 LOOP
158 FETCH csr_server_groups INTO l_server_Group_id;
159 EXIT WHEN csr_server_groups%NOTFOUND;
160 --dbms_output.put_line('ServerGroupId='||to_char(l_server_Group_id));
161 l_priority:=1;
162
163 Open csr_classifications(l_server_group_id);
164 Loop
165 Fetch csr_classifications into l_class_id;
166 Exit when csr_classifications%NOTFOUND;
167 --dbms_output.put_line('Classification ID='||to_char(l_class_id)||' priority='||to_char(l_priority));
168 update cct_classification_sg_map
169 set priority = l_priority
170 where classification_id = l_class_id;
171 l_priority:=l_priority+1;
172 END LOOP;
173 Close csr_classifications;
174 End Loop;
175 CLOSE csr_server_Groups;
176
177 EXCEPTION
178 WHEN others THEN
179 ROLLBACK TO fix_class_priorities_pt;
180 END fix_class_priorities;
181
182 -- Procedure to transfer deleted Classification records to new schema, as Business Intelligence
183 -- also, looks for deleted Classifications.
184
185 procedure Upgrade_Class_BI
186 IS
187 l_class_value_id Number(15);
188 BEGIN
189 -- Insert all deleted Classifications from cct_classifications table to a cct_classification_values table. This
190 -- newly inserted record in cct_classification_values table must have deletedflag set to 'D'.
191 -- As 1158 upgrade script won't carry over deleted classifications and Business Intelligence team needs those.
192 -- Update cct_classifications table with this new classification_value_id.
193
194 FOR l_record IN (
195 SELECT classification, classification_id FROM cct_classifications
196 where (upper(type)= 'LITERAL') AND classification_value_id is null
197 AND nvl(f_deletedflag,'N')='D' AND upper(classification) <> 'UNCLASSIFIED')
198 LOOP
199 select cct_classification_values_s.nextval into l_class_value_id from dual;
200 insert into cct_classification_values (classification_value_id, classification_value,seeded,
204 from dual;
201 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,f_deletedflag)
202 select l_class_value_id, l_record.classification,'N',
203 TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1, TO_DATE ('01/01/1951', 'DD/MM/YYYY'), 1,'D'
205
206 update cct_classifications a set a.classification_value_id = l_class_value_id
207 where a.classification_id = l_record.classification_id;
208
209 --dbms_output.put_line('At End of UpgradeBI loop');
210 END LOOP;
211
212 END Upgrade_Class_BI;
213
214 PROCEDURE UpgradeIKeys
215 IS
216 l_upgraded NUMERIC;
217 status NUMERIC;
218 StringOperation Varchar2(20);
219 l_loop_count INTEGER := 0;
220
221 -- Declare program variables as shown above
222 BEGIN
223 FOR l_record IN (
224 select classification_rule_id, operation from CCT_CLASSIFICATION_RULES
225 where key in ('AccountCode','AccountNum','occtRoutePoint', 'ContractNum')
226 AND operation in ('=','>','>=','<','<=','!=','DOESNOTEXISTSIN','BETWEEN')
227 AND nvl(f_deletedflag,'N')<>'D'
228 )
229 LOOP
230 --dbms_output.put_line('At beginning of For Loop');
231 StringOperation:='EQUAL';
232 IF l_record.operation='!=' THEN
233 StringOperation:='NOTEQUAL';
234 ELSIF UPPER(l_record.operation)='DOESNOTEXISTSIN' THEN
235 StringOperation:='DONOTEXISTIN';
236 END IF;
237
238 update CCT_CLASSIFICATION_RULES set operation=StringOperation
239 where classification_rule_id = l_record.classification_rule_id;
240
241 END LOOP;
242 --dbms_output.put_line('At End of 1st For Loop');
243
244 FOR l_record IN (
245 select route_param_id, operation from cct_route_params
246 WHERE value IS NOT NULL AND direction is null and sequence is null
247 AND param in ('AccountCode','AccountNum','occtRoutePoint', 'ContractNum')
248 AND operation in ('=','>','>=','<','<=','!=','DOESNOTEXISTSIN','BETWEEN')
249 AND nvl(f_deletedflag,'N')<>'D'
250 )
251 LOOP
252 --dbms_output.put_line('At beginning of 2nd For Loop');
253 StringOperation:='EQUAL';
254 IF l_record.operation='!=' THEN
255 StringOperation:='NOTEQUAL';
256 ELSIF UPPER(l_record.operation)='DOESNOTEXISTSIN' THEN
257 StringOperation:='DONOTEXISTIN';
258 END IF;
259
260 update cct_route_params set operation=StringOperation
261 where route_param_id = l_record.route_param_id;
262
263 END LOOP;
264
265 -- Update occtAgentID Key.
266 update cct_interaction_keys set IVR_MAP_ENABLED='Y' where interaction_key='occtAgentID' ;
267
268 --dbms_output.put_line('At End of 2nd For Loop');
269 EXCEPTION
270 WHEN Others THEN
271 status := SQLCODE;
272 --dbms_output.put_line('In Upgrade_Class_Schema Proc's CCT_IKEY_UPGRADE Portion '||SQLERRM(STATUS));
273 END UpgradeIKeys; -- Procedure
274
275
276 END CCT_CLASS_ENGINE_UPG;
[Home] [Help]