1 package body CZ_PS_MGR as
2 /* $Header: czpsmgrb.pls 120.8.12020000.4 2013/01/02 18:56:51 smanna 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 --Bug12608421 : Added Deleted_flag in ps_nodes query
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) AND deleted_flag <> '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
83 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before logical delete of CZ_PS_NODES : 2','CZ_PS_NODES',0,1);
84 IF t_ps_node_id.Count>0 THEN
85 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
86 UPDATE CZ_PS_NODES SET deleted_flag='1'
87 WHERE ps_node_id=t_ps_node_id(i);
88 COMMIT;
89 END IF;
90
91 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After logical delete of CZ_PS_NODES : 2','CZ_PS_NODES',t_ps_node_id.COUNT,1);
92 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS : 2','CZ_LOCALIZED_TEXTS',0,2);
93 IF t_intl_text_id.Count>0 THEN
94 FORALL i IN t_intl_text_id.First..t_intl_text_id.Last
95 DELETE FROM CZ_LOCALIZED_TEXTS a
96 WHERE a.intl_text_id=t_intl_text_id(i) AND a.seeded_flag<>'1'
97 AND not exists (SELECT null FROM CZ_PS_NODES b
98 WHERE b.deleted_flag='0'
99 and b.intl_text_id = a.intl_text_id);
100 COMMIT;
101 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS : 2','CZ_LOCALIZED_TEXTS',t_intl_text_id.COUNT,2);
102 END IF;
103 IF t_ps_node_id.Count>0 THEN
104 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
105 UPDATE CZ_LCE_HEADERS SET deleted_flag='1'
106 WHERE component_id=t_ps_node_id(i);
107 COMMIT;
108 END IF;
109 END LOOP;
110 CLOSE C1;
111 END;
112 IF t_devl_project_id.Count>0 THEN
113 FORALL i IN t_devl_project_id.First..t_devl_project_id.Last
114 UPDATE CZ_RULES SET deleted_flag='1'
115 WHERE devl_project_id=t_devl_project_id(i);
116 COMMIT;
117 END IF;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );
122 raise;
123 END;
124
125 DECLARE
126 CURSOR C1 IS
127 SELECT rule_id,reason_id
128 FROM CZ_RULES a
129 WHERE NOT EXISTS
130 (SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=a.devl_project_id
131 AND deleted_flag='0') AND a.seeded_flag<>'1' AND devl_project_id NOT IN(0,1);
132
133 BEGIN
134 OPEN C1;
135 LOOP
136 t_rule_id.delete; t_reason_id.delete;
137 FETCH C1 BULK COLLECT INTO t_rule_id,t_reason_id
138 LIMIT CZ_BASE_MGR.BATCH_SIZE;
139 EXIT WHEN C1%NOTFOUND AND t_rule_id.COUNT = 0;
140
141 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);
142 IF t_reason_id.Count>0 THEN
143 FORALL i IN t_reason_id.First..t_reason_id.Last
144 DELETE FROM CZ_LOCALIZED_TEXTS
145 WHERE intl_text_id=t_reason_id(i) AND seeded_flag<>'1';
146 COMMIT;
147 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (reason_id): 2','CZ_LOCALIZED_TEXTS',t_reason_id.COUNT,2);
148 END IF;
149
150 IF t_rule_id.Count>0 THEN
151 FORALL i IN t_rule_id.First..t_rule_id.Last
152 UPDATE CZ_RULES SET deleted_flag='1'
153 WHERE rule_id=t_rule_id(i) AND seeded_flag<>'1';
154 COMMIT;
155 END IF;
156 END LOOP;
157 ClOSE C1;
158 EXCEPTION
159 WHEN OTHERS THEN
160 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.delete_Orphaned_Nodes',SQLERRM );
161 raise;
162 END;
163
164 CZ_BASE_MGR.exec('CZ_PS_NODES','WHERE NOT EXISTS '||
165 '(SELECT devl_project_id FROM CZ_DEVL_PROJECTS WHERE devl_project_id=cz_ps_nodes.devl_project_id)',
166 'ps_node_id', FALSE);
167
168 --SKM:Bug13927565 . Physical Delete of Orphan Record for cz_ps_prop_vals
169 --SKM:Bug16042373. Removing Orphan cz_ps_prop_vals deletion as this causes publication & Purge concurrency issue that SUN reported.
170 --This is safe to remove as the clean up is done and there will not be any orphan cz_ps_prop_vals
171 -- CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before Physical delete of CZ_PS_PROP_VALS (Orphan):','CZ_PS_PROP_VALS',0,2);
172
173 -- CZ_BASE_MGR.exec('CZ_PS_PROP_VALS','WHERE NOT EXISTS '||
174 -- '(SELECT null FROM CZ_PS_NODES WHERE ps_node_id=cz_ps_prop_vals.ps_node_id)',
175 -- 'ps_node_id', TRUE);
176
177 -- CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After Physical delete of CZ_PS_PROP_VALS (Orphan):','CZ_PS_PROP_VALS',0,2);
178
179 --SKM:Bug13946677 . Physical Delete of Orphan Record for cz_signatures and cz_sinature_arguments
180
181 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before Physical delete of CZ_SIGNATURES (Orphan):','CZ_SIGNATURES',0,2);
182
183 CZ_BASE_MGR.exec('CZ_SIGNATURES','WHERE NOT EXISTS '||
184 '(SELECT null FROM CZ_EXPRESSION_NODES WHERE (argument_signature_id=cz_signatures.signature_id OR param_signature_id=cz_signatures.signature_id)) '||
185 'AND signature_type=''JME'' AND NVL(seeded_flag,''0'') <> ''1''',
186 'signature_id', TRUE);
187
188 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After Physical delete of CZ_SIGNATURES (Orphan):','CZ_SIGNATURES',0,2);
189
190 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before Physical delete of CZ_SIGNATURE_ARGUMENTS (Orphan):','CZ_SIGNATURE_ARGUMENTS',0,2);
191
192 CZ_BASE_MGR.exec('CZ_SIGNATURE_ARGUMENTS','WHERE NOT EXISTS '||
193 '(SELECT null FROM CZ_EXPRESSION_NODES WHERE param_signature_id=cz_signature_arguments.argument_signature_id OR argument_signature_id = cz_signature_arguments.argument_signature_id) '||
194 ' AND NVL(seeded_flag,''0'') <> ''1''',
195 'argument_signature_id', TRUE);
196
197 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After Physical delete of CZ_SIGNATURE_ARGUMENTS (Orphan):','CZ_SIGNATURE_ARGUMENTS',0,2);
198
199 END;
200
201 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
202 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
203
204 PROCEDURE update_deleted_flag(p_ps_node_id IN NUMBER) IS
205 updCount NUMBER:=0;
206 BEGIN
207 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before logical delete of CZ_PS_NODES : 1','CZ_PS_NODES',0,1);
208 FOR child IN (SELECT ps_node_id FROM cz_ps_nodes WHERE parent_id = p_ps_node_id
209 AND deleted_flag = '0') LOOP
210
211 UPDATE cz_ps_nodes SET deleted_flag = '1' WHERE ps_node_id = child.ps_node_id;
212 updCount := updCount + SQL%ROWCOUNT;
213 update_deleted_flag(child.ps_node_id);
214 END LOOP;
215 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After logical delete of CZ_PS_NODES : 1','CZ_PS_NODES',updCount, 1);
216 updCount := 0;
217 END update_deleted_flag;
218
219 procedure Propogate_DeletedFlag is
220
221 begin
222
223 CZ_BASE_MGR.exec('CZ_MODEL_REF_EXPLS','where deleted_flag='''||'0'||''' AND '||
224 'model_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
225 'model_ref_expl_id',FALSE);
226
227 --
228 -- vsingava 24th Dec '10. Bug10425915
229 -- Just for Sun, a temporary change. We must have done this by a parallel script.
230 -- TODO: IMPORTANT We need to revert this change.
231 -- Reverted the changes as a part of the bug#12608421 (As a one part of it)
232
233 CZ_BASE_MGR.exec('CZ_PS_NODES','where deleted_flag='''||'0'||''' AND '||
234 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
235 'ps_node_id',FALSE);
236
237 -- necessary?
238 /*
239 for n in(select ps_node_id from cz_ps_nodes where deleted_flag='1')
240 loop
241 update cz_ps_nodes
242 set deleted_flag='1' where deleted_flag='0' and ps_node_id in
243 (select ps_node_id from cz_ps_nodes
244 start with ps_node_id=n.ps_node_id
245 connect by prior ps_node_id=parent_id);
246 commit;
247 end loop;
248 */
249
250 CZ_BASE_MGR.exec('CZ_RULE_FOLDERS','where deleted_flag='''||'0'||''' AND '||
251 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
252 'rule_folder_id','object_type',FALSE);
253
254
255 CZ_BASE_MGR.exec('CZ_RULES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
256 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag=''1'' and devl_project_id NOT IN(0,1))',
257 'rule_id',FALSE);
258
259 CZ_BASE_MGR.exec('CZ_GRID_DEFS','where deleted_flag='''||'0'||''' AND '||
260 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
261 'grid_id',FALSE);
262
263 CZ_BASE_MGR.exec('CZ_FUNC_COMP_SPECS','where deleted_flag='''||'0'||''' AND '||
264 'devl_project_id in(select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
265 'func_comp_id',FALSE);
266
267 CZ_BASE_MGR.exec('CZ_COMBO_FEATURES','where deleted_flag='''||'0'||''' AND '||
268 'rule_id in(select rule_id from cz_rules where deleted_flag='''||'1'||''')',
269 'feature_id','model_ref_expl_id','rule_id',FALSE);
270
271 CZ_BASE_MGR.exec('CZ_POPULATORS','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
272 'owned_by_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
273 'populator_id',FALSE);
274
275 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_PS_PROP_VALS : 1','CZ_PS_PROP_VALS',0,2);
276 CZ_BASE_MGR.exec('CZ_PS_PROP_VALS','where deleted_flag='''||'0'||''' AND '||
277 'ps_node_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
278 'property_id','ps_node_id',TRUE);
279 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_PS_PROP_VALS : 1','CZ_PS_PROP_VALS',0,2);
280
281 CZ_BASE_MGR.exec('CZ_SUB_CON_SETS','where deleted_flag='''||'0'||''' AND '||
282 'sub_cons_id in(select sub_cons_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
283 'sub_cons_id',FALSE);
284
285 CZ_BASE_MGR.exec('CZ_FILTER_SETS','where deleted_flag='''||'0'||''' AND '||
286 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
287 'filter_set_id',FALSE);
288
289 CZ_BASE_MGR.exec('CZ_EXPRESSION_NODES','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
290 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
291 'expr_node_id',TRUE);
292
293 CZ_BASE_MGR.exec('CZ_LCE_HEADERS','where deleted_flag='''||'0'||''' AND '||
294 'component_id in(select ps_node_id from CZ_PS_NODES where deleted_flag='''||'1'||''')',
295 'lce_header_id',FALSE);
296
297 CZ_BASE_MGR.exec('CZ_LCE_LOAD_SPECS','where deleted_flag='''||'0'||''' AND '||
298 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
299 'lce_header_id','attachment_expl_id','required_expl_id',TRUE);
300
301 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LCE_TEXTS : 1','CZ_LCE_TEXTS',0,2);
302 CZ_BASE_MGR.exec('CZ_LCE_TEXTS','where '||
303 'lce_header_id in(select lce_header_id from CZ_LCE_HEADERS where deleted_flag='''||'1'||''')',
304 'lce_header_id','seq_nbr',TRUE);
305 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LCE_TEXTS : 1','CZ_LCE_TEXTS',0,2);
306
307 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.intl_text_id): 1','CZ_LOCALIZED_TEXTS',0,2);
308 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag=''0'' AND seeded_flag<>'''||'1'||''' AND '||
309 ' EXISTS(select NULL from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and deleted_flag=''1'''||
310 ') and not exists(select null from CZ_PS_NODES where intl_text_id=cz_localized_texts.intl_text_id and '||
311 ' deleted_flag=''0'')',
312 'language','intl_text_id',TRUE);
313 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.intl_text_id): 1','CZ_LOCALIZED_TEXTS',0,2);
314
315 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);
316 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND seeded_flag<>'''||'1'||''' AND '||
317 'exists (select reason_id from CZ_RULES where reason_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''') and '||
318 'not exists(select reason_id from CZ_RULES where reason_id=cz_localized_texts.intl_text_id and deleted_flag='''||'0'||''')',
319 'language','intl_text_id',TRUE);
320 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.reason_id): 2','CZ_LOCALIZED_TEXTS',0,2);
321
322 CZ_BASE_MGR.exec('CZ_DES_CHART_CELLS','where deleted_flag='''||'0'||''' AND '||
323 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
324 'RULE_ID','PRIMARY_OPT_ID','SECONDARY_OPT_ID','SECONDARY_FEAT_EXPL_ID',TRUE);
325
326 CZ_BASE_MGR.exec('CZ_DES_CHART_COLUMNS','where '||
327 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
328 'rule_id','option_id',TRUE);
329
330 CZ_BASE_MGR.exec('CZ_DES_CHART_FEATURES','where deleted_flag='''||'0'||''' AND '||
331 'rule_id in(select rule_id from CZ_RULES where deleted_flag='''||'1'||''')',
332 'RULE_ID', 'FEATURE_ID', 'MODEL_REF_EXPL_ID', FALSE);
333
334 CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
335 'archive_id in (select archive_id from cz_archives where deleted_flag='''||'1'||''')',
336 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);
337
338 CZ_BASE_MGR.exec('CZ_ARCHIVE_REFS','where deleted_flag='''||'0'||''' AND '||
339 'devl_project_id in (select devl_project_id from cz_devl_projects where deleted_flag='''||'1'||''')',
340 'DEVL_PROJECT_ID', 'SEQ_NBR', 'ARCHIVE_ID', FALSE);
341
342 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (devl_proj.intl_text_id): 3','CZ_LOCALIZED_TEXTS',0,2);
343 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
344 '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))',
345 'language','intl_text_id',TRUE);
346 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (devl_proj.intl_text_id): 3','CZ_LOCALIZED_TEXTS',0,2);
347
348 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.violation_text): 4','CZ_LOCALIZED_TEXTS',0,2);
349 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
350 'exists (select violation_text_id from CZ_PS_NODES where violation_text_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
351 'language','intl_text_id',TRUE);
352 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (ps_nodes.violation_text): 4','CZ_LOCALIZED_TEXTS',0,2);
353
354 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (cz_rules.unsatisfied_msg): 5','CZ_LOCALIZED_TEXTS',0,2);
355 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
356 'exists (select unsatisfied_msg_id from CZ_RULES where unsatisfied_msg_id = cz_localized_texts.intl_text_id and deleted_flag='''||'1'||''')',
357 'language','intl_text_id',TRUE);
358 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (cz_rules.unsatisfied_msg): 5','CZ_LOCALIZED_TEXTS',0,2);
359
360 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_LOCALIZED_TEXTS (deleted model_id): 6','CZ_LOCALIZED_TEXTS',0,2);
361 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag='''||'0'||''' AND '||
362 '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))',
363 'language','intl_text_id',TRUE);
364 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_LOCALIZED_TEXTS (deleted model_id): 6','CZ_LOCALIZED_TEXTS',0,2);
365
366 delete_Orphaned_Nodes;
367
368 commit;
369
370 exception
371 when others then
372 --null;
373 raise;
374 end;
375
376 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
377 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
378
379 procedure PURGE is
380
381
382 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
383
384 -- changes for bug #5093264
385 -- this cursor returns model nodes which refer to a model that does not exist.
386 -- ( such nodes will be deleted )
387 CURSOR del_nodes_cur IS
388 SELECT a.ps_node_id
389 FROM CZ_PS_NODES a WHERE a.reference_id IS NOT NULL AND NOT EXISTS
390 (SELECT NULL FROM CZ_DEVL_PROJECTS b
391 WHERE b.devl_project_id=a.reference_id AND b.deleted_flag='0');
392
393 t_ps_node_id number_tbl_type;
394
395 begin
396 --
397 -- propogate deleted_flag for all subschema's tables
398 --
399 Propogate_DeletedFlag;
400
401 --
402 -- delete views associated with the deleted Populators --
403 --
404 for i in(select view_name from cz_populators where deleted_flag='1')
405 loop
406 begin
407 execute immediate 'drop view '||i.view_name;
408 exception
409 when others then
410 CZ_BASE_MGR.LOG_REPORT('CZ_PS_MGR.Purge','View "'||i.view_name||'" error :'||SQLERRM );
411 end;
412 end loop;
413
414 --
415 -- apply Purge --
416 --
417 CZ_BASE_MGR.PURGE('PS');
418
419 OPEN del_nodes_cur;
420
421 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','Before physical delete of CZ_PS_NODES : 1','CZ_PS_NODES',0,2);
422 LOOP
423 FETCH del_nodes_cur BULK COLLECT INTO t_ps_node_id
424 LIMIT 10000;
425 EXIT WHEN del_nodes_cur%NOTFOUND AND t_ps_node_id.COUNT = 0;
426
427 IF t_ps_node_id.Count>0 THEN
428 FORALL i IN t_ps_node_id.First..t_ps_node_id.Last
429 DELETE FROM CZ_PS_NODES
430 WHERE ps_node_id=t_ps_node_id(i);
431 COMMIT;
432 END IF;
433 END LOOP;
434
435 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG','After physical delete of CZ_PS_NODES : 1','CZ_PS_NODES',t_ps_node_id.COUNT,2);
436
437 CLOSE del_nodes_cur;
438
439 exception
440 when OTHERS then
441 raise;
442 end;
443
444 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
445 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
446
447 procedure RESET_CLEAR is
448 begin
449 null;
450 end;
451
452 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
453 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
454
455 procedure MODIFIED
456 (AS_OF in OUT NOCOPY date) is
457 begin
458 CZ_BASE_MGR.MODIFIED('PS',AS_OF);
459 end;
460
461 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
462 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
463
464 end;
[Home] [Help]