1 Package Body CCT_CASCADE_DELETE_PUB AS
2 /* $Header: cctcsdeb.pls 120.1 2005/11/09 22:20:19 dbhagat noship $ */
3
4 PROCEDURE delete_defunct_del_middlewares AS
5 BEGIN
6 SAVEPOINT CCT_MIDDLEWARE_DEFUNCT_DEL_DEL;
7
8 delete_deleted_middlewares;
9 delete_defunct_middlewares;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 rollback TO SAVEPOINT CCT_MIDDLEWARE_DEFUNCT_DEL_DEL;
14 raise_application_error(-20000, sqlerrm || '. Could not delete defunct deleted middlewares') ;
15
16 END;
17
18
19 PROCEDURE delete_defunct_middlewares
20 IS
21 l_config_id cct_middlewares.middleware_id%type;
22
23 CURSOR c_configs IS
24 select a.middleware_id
25 from cct_middlewares a
26 where f_deletedflag <> 'D'
27 OR f_deletedflag is null
28 and not exists
29 (select server_group_id from ieo_svr_groups b where b.server_group_id = a.server_group_id );
30
31
32 BEGIN
33 SAVEPOINT CCT_MIDDLEWARE_DEFUNCT_DEL;
34 OPEN c_configs;
35
36 LOOP
37
38 FETCH c_configs INTO l_config_id;
39
40 IF c_configs%NOTFOUND THEN
41 CLOSE c_configs;
42 exit;
43 ELSE
44 delete_middleware(p_middleware_id =>l_config_id);
45 END IF;
46 END LOOP;
47
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 rollback TO SAVEPOINT CCT_MIDDLEWARE_DEFUNCT_DEL;
52 raise_application_error(-20000, sqlerrm || '. Could not delete defunct middlewares') ;
53
54 END;
55
56 PROCEDURE delete_deleted_middlewares
57 IS
58 l_config_id cct_middlewares.middleware_id%type;
59
60 CURSOR c_configs IS
61 select distinct a.server_group_id
62 from cct_middlewares a
63 where f_deletedflag <> 'D'
64 OR f_deletedflag is null
65 and not exists
66 (select server_group_id from ieo_svr_groups b where b.server_group_id = a.server_group_id );
67 BEGIN
68 SAVEPOINT CCT_MIDDLEWARE_DELETED_DEL;
69 OPEN c_configs;
70
71 LOOP
72
73 FETCH c_configs INTO l_config_id;
74
75 IF c_configs%NOTFOUND THEN
76 CLOSE c_configs;
77 exit;
78 ELSE
79 delete_middleware(p_middleware_id =>l_config_id);
80 END IF;
81 END LOOP;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 rollback TO SAVEPOINT CCT_MIDDLEWARE_DELETED_DEL;
86 raise_application_error(-20000, sqlerrm || '. Could not delete deleted middlewares') ;
87 END;
88
89 PROCEDURE delete_middleware
90 ( p_server_group_id IN NUMBER
91 , p_commit_flag IN VARCHAR2)
92 IS
93 l_config_id cct_middlewares.middleware_id%type;
94
95 CURSOR c_configs IS
96 select middleware_id
97 from cct_middlewares
98 where server_group_id = p_server_group_id;
99
100 BEGIN
101 IF p_server_group_id is not null
102 then
103 SAVEPOINT CCT_MIDDLEWARE_SVR_DEL;
104 OPEN c_configs;
105
106 LOOP
107
108 FETCH c_configs INTO l_config_id;
109
110 IF c_configs%NOTFOUND THEN
111 CLOSE c_configs;
112 exit;
113 ELSE
114 delete_middleware(p_middleware_id =>l_config_id);
115 END IF;
116 END LOOP;
117
118
119 END IF;
120 IF p_commit_flag = 'Y' THEN
121 commit;
122 END IF;
123 EXCEPTION
124 WHEN OTHERS THEN
125 rollback TO SAVEPOINT CCT_MIDDLEWARE_SVR_DEL;
126 raise_application_error(-20000, sqlerrm || '. Could not delete teleset') ;
127 END;
128
129
130 PROCEDURE delete_middleware
131 ( p_middleware_id IN NUMBER
132 , p_commit_flag IN VARCHAR2)
133 IS
134
135 CURSOR csr_jtf_rs_resource_values
136 is
137 select resource_param_value_id, value_type, object_version_number
138 from jtf_rs_resource_values
139 where value_type = to_char(p_middleware_id);
140
141 l_param_value_id jtf_rs_resource_values.resource_param_value_id%TYPE;
142 l_value_type jtf_rs_resource_values.value_type%TYPE;
143 l_obj_ver jtf_rs_resource_values.object_version_number%TYPE;
144 l_return_status varchar2(32);
145 l_msg_count NUMBER;
146 l_msg_data VARCHAR2(32);
147
148 BEGIN
149 IF p_middleware_id is not null
150 then
151 SAVEPOINT CCT_MIDDLEWARE_DEL;
152
153 -- Delete Child records first
154 delete_teleset(p_middleware_id => p_middleware_id);
155 delete_ivr(p_middleware_id => p_middleware_id);
156 delete_multisite(p_middleware_id => p_middleware_id);
157 delete_route_point(p_middleware_id => p_middleware_id);
158
159 delete cct_middleware_values
160 where middleware_id = p_middleware_id;
161
162 -- delete jtf_rs_resource_values
163 -- where value_type = to_char(p_middleware_id);
164 BEGIN
165 OPEN csr_jtf_rs_resource_values;
166
167 -- Fixed bug 4676911 dbhagat 10-Nov-2005
168 FETCH csr_jtf_rs_resource_values into l_param_value_id,l_value_type,l_obj_ver ;
169 WHILE (csr_jtf_rs_resource_values%FOUND) LOOP
170 jtf_rs_resource_values_pub.delete_rs_resource_values
171 (
172 p_api_version => 1
173 ,p_commit => fnd_api.g_true
174 ,p_resource_param_value_id => l_param_value_id
175 ,p_object_version_number => l_obj_ver
176 ,x_return_status => l_return_status
177 ,x_msg_count => l_msg_count
178 ,x_msg_data => l_msg_data
179 );
180
181 FETCH csr_jtf_rs_resource_values into l_param_value_id,l_value_type,l_obj_ver ;
182 END LOOP;
183 CLOSE csr_jtf_rs_resource_values;
184 --LOOP
185 -- FETCH csr_jtf_rs_resource_values into l_param_value_id,l_value_type,l_obj_ver ;
186 -- IF csr_jtf_rs_resource_values%NOTFOUND THEN
187 -- CLOSE csr_jtf_rs_resource_values;
188 -- ELSE
189 -- jtf_rs_resource_values_pub.delete_rs_resource_values
190 -- (
191 -- p_api_version => 1
192 -- ,p_commit => fnd_api.g_true
193 -- ,p_resource_param_value_id => l_param_value_id
194 -- ,p_object_version_number => l_obj_ver
195 -- ,x_return_status => l_return_status
196 -- ,x_msg_count => l_msg_count
197 -- ,x_msg_data => l_msg_data
198 -- );
199 -- END IF;
200 --END LOOP;
201
202 END;
203
204 delete cct_middlewares
205 where middleware_id = p_middleware_id;
206 END IF;
207 IF p_commit_flag = 'Y' THEN
208 commit;
209 END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
212 rollback TO SAVEPOINT CCT_MIDDLEWARE_DEL;
213 raise_application_error(-20000, sqlerrm || '. Could not delete middleware for middleware'||p_middleware_id) ;
214 END;
215
216
217 --Tested
218 PROCEDURE delete_teleset
219 ( p_middleware_id IN NUMBER
220 , p_commit_flag IN VARCHAR2)
221 IS
222 BEGIN
223 IF p_middleware_id is not null
224 then
225 SAVEPOINT CCT_TELESET_DEL;
226 -- Delete Child records first
227 delete cct_lines
228 where teleset_id in (select teleset_id
229 from cct_telesets
230 where middleware_id = p_middleware_id);
231
232 delete cct_telesets
233 where middleware_id = p_middleware_id;
234 END IF;
235 IF p_commit_flag = 'Y' THEN
236 commit;
237 END IF;
238 EXCEPTION
239 WHEN OTHERS THEN
240 rollback TO SAVEPOINT CCT_TELESET_DEL;
241 raise_application_error(-20000, sqlerrm || '. Could not delete teleset') ;
242
243 END;
244
245
246 /* Delete IVR values
247 */
248 --Tested
249 PROCEDURE delete_ivr
250 ( p_middleware_id IN NUMBER
251 , p_commit_flag IN VARCHAR2)
252 IS
253 BEGIN
254 IF p_middleware_id is not null
255 then
256 SAVEPOINT CCT_IVR_DEL;
257 -- Delete Child records first
258 delete cct_ivr_maps
259 where mw_route_point_id in (select mw_route_point_id
260 from cct_mw_route_points
261 where middleware_id = p_middleware_id);
262 END IF;
263 IF p_commit_flag = 'Y' THEN
264 commit;
265 END IF;
266 EXCEPTION
267 WHEN OTHERS THEN
268 rollback TO SAVEPOINT CCT_IVR_DEL;
269 raise_application_error(-20000, sqlerrm || '. Could not delete IVR') ;
270 END;
271
272 PROCEDURE delete_ivr
273 ( p_route_point_id IN NUMBER
274 , p_commit_flag IN VARCHAR2)
275 IS
276 BEGIN
277 IF p_route_point_id is not null
278 then
279 SAVEPOINT CCT_IVR_RP_DEL;
280 -- Delete Child records first
281 delete cct_ivr_maps
282 where mw_route_point_id = p_route_point_id;
283 END IF;
284 IF p_commit_flag = 'Y' THEN
285 commit;
286 END IF;
287 EXCEPTION
288 WHEN OTHERS THEN
289 rollback TO SAVEPOINT CCT_IVR_RP_DEL;
290 raise_application_error(-20000, sqlerrm || '. Could not delete IVR') ;
291 END;
292
293
294 PROCEDURE delete_multisite
295 ( p_middleware_id IN NUMBER
296 , p_commit_flag IN VARCHAR2)
297 IS
298 l_config_id cct_multisite_configs.multisite_config_id%type;
299 CURSOR c_configs IS
300 select multisite_config_id
301 from cct_multisite_configs
302 where from_middleware_id = p_middleware_id
303 or to_middleware_id = p_middleware_id;
304 BEGIN
305 IF p_middleware_id is not null
306 then
307 SAVEPOINT CCT_MULTISITE_DEL;
308 -- Delete Child records first
309 OPEN c_configs;
310
311 LOOP
312
313 FETCH c_configs INTO l_config_id;
314 IF c_configs%NOTFOUND THEN
315 CLOSE c_configs;
316 exit;
317 ELSE
318 delete_multisite_paths(p_multisite_config_id =>l_config_id);
319
320 delete cct_multisite_values
321 where multisite_config_id = l_config_id;
322
323 END IF;
324 END LOOP;
325
326 delete cct_multisite_configs
327 where from_middleware_id = p_middleware_id
328 or to_middleware_id = p_middleware_id;
329 END IF;
330 IF p_commit_flag = 'Y' THEN
331 commit;
332 END IF;
333 EXCEPTION
334 WHEN OTHERS THEN
335 rollback TO SAVEPOINT CCT_MULTISITE_DEL;
336 raise_application_error(-20000, sqlerrm || '. Could not delete multisite') ;
337
338 END;
339
340 PROCEDURE delete_multisite_paths
341 ( p_multisite_config_id IN NUMBER
342 , p_commit_flag IN VARCHAR2)
343 IS
344 BEGIN
345 IF p_multisite_config_id is not null
346 then
347 SAVEPOINT CCT_MULTISITE_PATHS_DEL;
348 -- Delete Child records first
349 delete cct_multisite_path_values
350 where multisite_path_id IN(select multisite_path_id
351 from cct_multisite_paths
352 where multisite_config_id = p_multisite_config_id);
353
354 delete cct_multisite_paths
355 where multisite_config_id = p_multisite_config_id;
356 END IF;
357 IF p_commit_flag = 'Y' THEN
358 commit;
359 END IF;
360 EXCEPTION
361 WHEN OTHERS THEN
362 rollback TO SAVEPOINT CCT_MULTISITE_PATHS_DEL;
363 raise_application_error(-20000, sqlerrm || '. Could not delete multisite paths') ;
364 END;
365
366 PROCEDURE delete_route_point
367 ( p_middleware_id IN NUMBER
368 , p_commit_flag IN VARCHAR2)
369 IS
370 l_id cct_mw_route_points.mw_route_point_id%type;
371 CURSOR c_rpts IS
372 select mw_route_point_id
373 from cct_mw_route_points
374 where middleware_id = p_middleware_id;
375 BEGIN
376 IF p_middleware_id is not null
377 then
378 SAVEPOINT CCT_ROUTE_POINT_DEL;
379 -- Delete Child records first
380 OPEN c_rpts;
381
382 LOOP
383
384 FETCH c_rpts INTO l_id;
385 IF c_rpts%NOTFOUND THEN
386 CLOSE c_rpts;
387 exit;
388
389 ELSE
390 delete_multisite_paths(p_mw_route_point_id =>l_id);
391 delete_ivr(p_route_point_id=>l_id);
392 delete cct_mw_route_point_values
393 where mw_route_point_id = l_id;
394 END IF;
395 END LOOP;
396
397 delete cct_mw_route_points
398 where middleware_id = p_middleware_id;
399
400 END IF;
401 IF p_commit_flag = 'Y' THEN
402 commit;
403 END IF;
404 EXCEPTION
405 WHEN OTHERS THEN
406 rollback TO SAVEPOINT CCT_ROUTE_POINT_DEL;
407 raise_application_error(-20000, sqlerrm || '. Could not delete Route Points') ;
408 END;
409
410
411
412 PROCEDURE delete_multisite_paths
413 ( p_mw_route_point_id IN NUMBER
414 , p_commit_flag IN VARCHAR2)
415 IS
416 BEGIN
417 IF p_mw_route_point_id is not null
418 then
419 SAVEPOINT CCT_MULTISITE_PATHS_RP_DEL;
420 -- Delete Child records first
421 delete cct_multisite_path_values
422 where multisite_path_id IN(select multisite_path_id
423 from cct_multisite_paths
424 where mw_route_point_id = p_mw_route_point_id);
425
426 delete cct_multisite_paths
427 where mw_route_point_id = p_mw_route_point_id;
428 END IF;
429 IF p_commit_flag = 'Y' THEN
430 commit;
431 END IF;
432 EXCEPTION
433 WHEN OTHERS THEN
434 rollback TO SAVEPOINT CCT_MULTISITE_PATHS_RP_DEL;
435 raise_application_error(-20000, sqlerrm || '. Could not delete multisite paths') ;
436 END;
437
438 END CCT_CASCADE_DELETE_PUB; -- Package Specification CCT_CASCADE_DELETE_PUB
[Home] [Help]