1 package body CZ_PS_MGR as
2 /* $Header: czpsmgrb.pls 120.4 2006/06/29 16:06:59 skudryav ship $ */
3
4
5 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
6 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
7
8 procedure ASSESS_DATA is
9 begin
10 null;
11 end;
12
13
14 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
15 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
16
17 procedure REDO_STATISTICS is
18 begin
19 CZ_BASE_MGR.REDO_STATISTICS('PS');
20 end;
21
22 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
23 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
24
25 procedure TRIGGERS_ENABLED
26 (Switch in varchar2) is
27 begin
28 CZ_BASE_MGR.TRIGGERS_ENABLED('PS',Switch);
29 end;
30
31 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
32 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
33
34 procedure CONSTRAINTS_ENABLED
35 (Switch in varchar2) is
36 begin
37 CZ_BASE_MGR.CONSTRAINTS_ENABLED('PS',Switch);
38 end;
39
40 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
41 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
42
43 procedure REDO_SEQUENCES
44 (RedoStart_Flag in varchar2 , -- default '0',
45 incr in integer default null) is
46 begin
47 CZ_BASE_MGR.REDO_SEQUENCES('PS',RedoStart_Flag,incr);
48 end;
49
50 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
51 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
52
53 PROCEDURE delete_Orphaned_Nodes IS
54 TYPE t_arr IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
55 t_devl_project_id t_arr;
56 t_ps_node_id t_arr;
57 t_intl_text_id t_arr;
58 t_rule_id t_arr;
59 t_reason_id t_arr;
60 BEGIN
61
62 BEGIN
63
64 SELECT DISTINCT devl_project_id
65 BULK COLLECT INTO t_devl_project_id
66 FROM CZ_PS_NODES a WHERE NOT EXISTS
67 (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
68 AND deleted_flag='0') AND devl_project_id NOT IN(0,1);
69
70 DECLARE
71 CURSOR C1 IS
72 SELECT ps_node_id,intl_text_id
73 FROM CZ_PS_NODES a WHERE NOT EXISTS
74 (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
75 AND deleted_flag='0') AND devl_project_id NOT IN(0,1);
76 BEGIN
77 OPEN C1;
78 LOOP
79 t_ps_node_id.delete; t_intl_text_id.delete;
80 FETCH C1 BULK COLLECT INTO t_ps_node_id,t_intl_text_id LIMIT CZ_BASE_MGR.BATCH_SIZE;
81 EXIT WHEN C1%NOTFOUND AND t_ps_node_id.COUNT = 0;
82 IF t_ps_node_id.Count>0 THEN
83 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
84 UPDATE CZ_PS_NODES SET deleted_flag='1'
85 WHERE ps_node_id=t_ps_node_id(i);
86 COMMIT;
87 END IF;
88 IF t_intl_text_id.Count>0 THEN
89 FORALL i IN t_intl_text_id.First..t_intl_text_id.Last
90 DELETE FROM CZ_LOCALIZED_TEXTS a
91 WHERE a.intl_text_id=t_intl_text_id(i) AND a.seeded_flag<>'1'
92 AND not exists (SELECT null FROM CZ_PS_NODES b
93 WHERE b.deleted_flag='0'
94 and b.intl_text_id = a.intl_text_id);
95 COMMIT;
96 END IF;
97 IF t_ps_node_id.Count>0 THEN
98 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
99 UPDATE CZ_LCE_HEADERS SET deleted_flag='1'
100 WHERE component_id=t_ps_node_id(i);
101 COMMIT;
102 END IF;
103 END LOOP;
104 CLOSE C1;
105 END;
106 IF t_devl_project_id.Count>0 THEN
107 FORALL i IN t_devl_project_id.First..t_devl_project_id.Last
108 UPDATE CZ_RULES SET deleted_flag='1'
109 WHERE devl_project_id=t_devl_project_id(i);
110 COMMIT;
111 END IF;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );
116 raise;
117 END;
118
119 DECLARE
120 CURSOR C1 IS
121 SELECT rule_id,reason_id
122 FROM CZ_RULES a
123 WHERE NOT EXISTS
124 (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
125 AND deleted_flag='0') AND a.seeded_flag<>'1' AND devl_project_id NOT IN(0,1);
126
127 BEGIN
128 OPEN C1;
129 LOOP
130 t_rule_id.delete; t_reason_id.delete;
131 FETCH C1 BULK COLLECT INTO t_rule_id,t_reason_id
132 LIMIT CZ_BASE_MGR.BATCH_SIZE;
133 EXIT WHEN C1%NOTFOUND AND t_rule_id.COUNT = 0;
134
135 IF t_reason_id.Count>0 THEN
136 FORALL i IN t_reason_id.First..t_reason_id.Last
137 DELETE FROM CZ_LOCALIZED_TEXTS
138 WHERE intl_text_id=t_reason_id(i) AND seeded_flag<>'1';
139 COMMIT;
140 END IF;
141
142 IF t_rule_id.Count>0 THEN
143 FORALL i IN t_rule_id.First..t_rule_id.Last
144 UPDATE CZ_RULES SET deleted_flag='1'
145 WHERE rule_id=t_rule_id(i) AND seeded_flag<>'1';
146 COMMIT;
147 END IF;
148 END LOOP;
149 ClOSE C1;
150 EXCEPTION
151 WHEN OTHERS THEN
152 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );
153 raise;
154 END;
155
156 CZ_BASE_MGR.exec('CZ_PS_NODES','WHERE NOT EXISTS '||
157 '(SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=cz_ps_nodes.devl_project_id)',
158 'ps_node_id', FALSE);
159
160 END;
161
162 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
163 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
164
165 PROCEDURE update_deleted_flag(p_ps_node_id IN NUMBER) IS
166 BEGIN
167 FOR child IN (SELECT ps_node_id FROM cz_ps_nodes WHERE parent_id = p_ps_node_id
168 AND deleted_flag = '0') LOOP
169
170 UPDATE cz_ps_nodes SET deleted_flag = '1' WHERE ps_node_id = child.ps_node_id;
171 update_deleted_flag(child.ps_node_id);
172 END LOOP;
173 END update_deleted_flag;
174
175 procedure Propogate_DeletedFlag is
176
177 begin
178
179 CZ_BASE_MGR.exec('CZ_MODEL_REF_EXPLS','where deleted_flag='''||'0'||''' AND '||
180 'model_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
181 'model_ref_expl_id',FALSE);
182
183 CZ_BASE_MGR.exec('CZ_PS_NODES','where deleted_flag='''||'0'||''' AND '||
184 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
185 'ps_node_id',FALSE);
186
187 -- necessary?
188 /*
189 for n in(select ps_node_id from cz_ps_nodes where deleted_flag='1')
190 loop
191 update cz_ps_nodes
192 set deleted_flag='1' where deleted_flag='0' and ps_node_id in
193 (select ps_node_id from cz_ps_nodes
194 start with ps_node_id=n.ps_node_id
195 connect by prior ps_node_id=parent_id);
196 commit;
197 end loop;
198 */
199
200 CZ_BASE_MGR.exec('CZ_RULE_FOLDERS','where deleted_flag='''||'0'||''' AND '||
201 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
202 'rule_folder_id','object_type',FALSE);
203
204
205 CZ_BASE_MGR.exec('CZ_RULES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
206 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
207 'rule_id',FALSE);
208
209 CZ_BASE_MGR.exec('CZ_GRID_DEFS','where deleted_flag='''||'0'||''' AND '||
210 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
211 'grid_id',FALSE);
212
213 CZ_BASE_MGR.exec('CZ_FUNC_COMP_SPECS','where deleted_flag='''||'0'||''' AND '||
214 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
215 'func_comp_id',FALSE);
216
217 CZ_BASE_MGR.exec('CZ_COMBO_FEATURES','where deleted_flag='''||'0'||''' AND '||
218 'rule_id in(select rule_id from cz_rules where deleted_flag='''||'1'||''')',
219 'feature_id','model_ref_expl_id','rule_id',FALSE);
220
221 CZ_BASE_MGR.exec('CZ_POPULATORS','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
222 'owned_by_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
223 'populator_id',FALSE);
224
225 CZ_BASE_MGR.exec('CZ_PS_PROP_VALS','where deleted_flag='''||'0'||''' AND '||
226 'ps_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
227 'property_id','ps_node_id',TRUE);
228
229 CZ_BASE_MGR.exec('CZ_SUB_CON_SETS','where deleted_flag='''||'0'||''' AND '||
230 'sub_cons_id in(select sub_cons_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
231 'sub_cons_id',FALSE);
232
233 CZ_BASE_MGR.exec('CZ_FILTER_SETS','where deleted_flag='''||'0'||''' AND '||
234 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
235 'filter_set_id',FALSE);
236
237 CZ_BASE_MGR.exec('CZ_EXPRESSION_NODES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
238 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
239 'expr_node_id',TRUE);
240
241 CZ_BASE_MGR.exec('CZ_LCE_HEADERS','where deleted_flag='''||'0'||''' AND '||
242 'component_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
243 'lce_header_id',FALSE);
244
245 CZ_BASE_MGR.exec('CZ_LCE_LOAD_SPECS','where deleted_flag='''||'0'||''' AND '||
246 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
247 'lce_header_id','attachment_expl_id','required_expl_id',TRUE);
248
249 CZ_BASE_MGR.exec('CZ_LCE_TEXTS','where '||
250 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
251 'lce_header_id','seq_nbr',TRUE);
252
253 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag=''0'' AND seeded_flag<>'''||'1'||''' AND '||
254 ' EXISTS(select NULL from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and deleted_flag=''1'''||
255 ') and not exists(select null from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and '||
256 ' deleted_flag=''0'')',
257 'language','intl_text_id',TRUE);
258
259 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
260 'exists (select reason_id from CZ_RULES where reason_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''') and '||
261 'not exists(select reason_id from CZ_RULES where reason_id=cz_localized_texts.intl_text_id and deleted_flag='''||'0'||''')',
262 'language','intl_text_id',TRUE);
263
264 CZ_BASE_MGR.exec('CZ_DES_CHART_CELLS','where deleted_flag='''||'0'||''' AND '||
265 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
266 'RULE_ID','PRIMARY_OPT_ID','SECONDARY_OPT_ID','SECONDARY_FEAT_EXPL_ID',TRUE);
267
268 CZ_BASE_MGR.exec('CZ_DES_CHART_COLUMNS','where '||
269 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
270 'rule_id','option_id',TRUE);
271
272 CZ_BASE_MGR.exec('CZ_DES_CHART_FEATURES','where deleted_flag='''||'0'||''' AND '||
273 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
274 'RULE_ID', 'FEATURE_ID', 'MODEL_REF_EXPL_ID', FALSE);
275
276 CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
277 'archive_id in (select archive_id from cz_archives where deleted_flag='''||'1'||''')',
278 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);
279
280 CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
281 'devl_project_id in (select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
282 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);
283
284 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
285 'exists (select intl_text_id from CZ_DEVL_PROJECTS where intl_text_id = cz_localized_texts.intl_text_id and deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
286 'language','intl_text_id',TRUE);
287
288 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
289 'exists (select violation_text_id from CZ_PS_NODES where violation_text_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
290 'language','intl_text_id',TRUE);
291
292 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
293 'exists (select unsatisfied_msg_id from CZ_RULES where unsatisfied_msg_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
294 'language','intl_text_id',TRUE);
295
296 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
297 'exists (select devl_project_id from CZ_DEVL_PROJECTS where devl_project_id = cz_localized_texts.model_id and deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
298 'language','intl_text_id',TRUE);
299
300 delete_Orphaned_Nodes;
301
302 commit;
303
304 exception
305 when others then
306 --null;
307 raise;
308 end;
309
310 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
311 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
312
313 procedure PURGE is
314
315
316 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
317
318 -- changes for bug #5093264
319 -- this cursor returns model nodes which refer to a model that does not exist.
320 -- ( such nodes will be deleted )
321 CURSOR del_nodes_cur IS
322 SELECT a.ps_node_id
323 FROM CZ_PS_NODES a WHERE a.reference_id IS NOT NULL AND NOT EXISTS
324 (SELECT NULL FROM CZ_DEVL_PROJECTS b
325 WHERE b.devl_project_id=a.reference_id AND b.deleted_flag='0');
326
327 t_ps_node_id number_tbl_type;
328
329 begin
330 --
331 -- propogate deleted_flag for all subschema's tables
332 --
333 Propogate_DeletedFlag;
334
335 --
336 -- delete views associated with the deleted Populators --
337 --
338 for i in(select view_name from cz_populators where deleted_flag='1')
339 loop
340 begin
341 execute immediate 'drop view '||i.view_name;
342 exception
343 when others then
344 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.Purge','View "'||i.view_name||'" error :'||SQLERRM );
345 end;
346 end loop;
347
348 --
349 -- apply Purge --
350 --
351 CZ_BASE_MGR.PURGE('PS');
352
353 OPEN del_nodes_cur;
354
355 LOOP
356 FETCH del_nodes_cur BULK COLLECT INTO t_ps_node_id
357 LIMIT 10000;
358 EXIT WHEN del_nodes_cur%NOTFOUND AND t_ps_node_id.COUNT = 0;
359
360 IF t_ps_node_id.Count>0 THEN
361 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
362 DELETE FROM CZ_PS_NODES
363 WHERE ps_node_id=t_ps_node_id(i);
364 COMMIT;
365 END IF;
366 END LOOP;
367
368 CLOSE del_nodes_cur;
369
370 exception
371 when OTHERS then
372 raise;
373 end;
374
375 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
376 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
377
378 procedure RESET_CLEAR is
379 begin
380 null;
381 end;
382
383 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
384 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
385
386 procedure MODIFIED
387 (AS_OF in OUT NOCOPY date) is
388 begin
389 CZ_BASE_MGR.MODIFIED('PS',AS_OF);
390 end;
391
392 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
393 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
394
395 end;
[Home] [Help]