1 package body CZ_UI_MGR as
2 /* $Header: czuimgrb.pls 120.2.12010000.2 2008/12/09 14:52:48 lamrute ship $ */
3
4
5
6
7 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
8 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
9
10 procedure ASSESS_DATA is
11 begin
12 null;
13 end;
14
15
16 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
17 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
18
19 procedure REDO_STATISTICS is
20 begin
21 CZ_BASE_MGR.REDO_STATISTICS('UI');
22 end;
23
24 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
25 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
26
27 procedure TRIGGERS_ENABLED
28 (Switch in varchar2) is
29 begin
30 CZ_BASE_MGR.TRIGGERS_ENABLED('UI',Switch);
31 end;
32
33 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
34 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
35
36 procedure CONSTRAINTS_ENABLED
37 (Switch in varchar2) is
38 begin
39 CZ_BASE_MGR.CONSTRAINTS_ENABLED('UI',Switch);
40 end;
41
42 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
43 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
44
45 procedure REDO_SEQUENCES
46 (RedoStart_Flag in varchar2,
47 incr in integer default null) is
48 begin
49 CZ_BASE_MGR.REDO_SEQUENCES('UI',RedoStart_Flag,incr);
50 end;
51
52 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
53 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
54
55 procedure Propogate_DeletedFlag is
56
57 Type UI_type is table of CZ_UI_DEFS.ui_def_id%TYPE;
58 UIs UI_type;
59 v_ui integer;
60
61 begin
62
63
64 for n in(select ui_node_id from cz_ui_nodes where ui_def_id=v_ui and deleted_flag='1')
65 loop
66 for m in(select ui_node_id from cz_ui_nodes
67 start with ui_node_id=n.ui_node_id
68 connect by prior ui_node_id=parent_id)
69 loop
70 update cz_ui_nodes
71 set deleted_flag='1' where ui_def_id=v_ui and ui_node_id=m.ui_node_id;
72
73 update cz_ui_node_props
74 set deleted_flag='1' where ui_def_id=v_ui and ui_node_id=m.ui_node_id;
75 commit;
76 end loop;
77 end loop;
78
79 select ui_def_id bulk collect into UIs
80 from CZ_UI_DEFS where (devl_project_id in(select devl_project_id from cz_devl_projects where
81 deleted_flag='1' and devl_project_id<>0) or deleted_flag='1') and NVL(seeded_flag,'0')='0' and ui_def_id<>0;
82
83 if UIs.Count>0 then
84 for i in UIs.First..UIs.Last
85 loop
86 v_ui:=UIs(i);
87
88 update cz_ui_defs
89 set deleted_flag='1' where ui_def_id=v_ui;
90 commit;
91
92 update cz_ui_properties
93 set deleted_flag='1' where ui_def_id=v_ui;
94 commit;
95
96 for n in(select ui_node_id from cz_ui_nodes where ui_def_id=v_ui)
97 loop
98
99 update cz_ui_nodes
100 set deleted_flag='1' where ui_def_id=v_ui and ui_node_id=n.ui_node_id;
101
102 update cz_ui_node_props
103 set deleted_flag='1' where ui_def_id=v_ui and ui_node_id=n.ui_node_id;
104 commit;
105
106 end loop;
107
108 update cz_ui_page_elements
109 set deleted_flag='1'
110 where ui_def_id=v_ui;
111 commit;
112
113 update cz_ui_page_sets
114 set deleted_flag='1'
115 where ui_def_id=v_ui;
116 commit;
117
118 update cz_ui_page_refs
119 set deleted_flag='1'
120 where ui_def_id=v_ui;
121 commit;
122
123 update cz_ui_refs
124 set deleted_flag='1'
125 where ui_def_id=v_ui;
126 commit;
127
128 update cz_ui_actions
129 set deleted_flag='1'
130 where ui_def_id=v_ui and NVL(seeded_flag,'0')='0';
131 commit;
132
133 update cz_ui_templates
134 set deleted_flag='1'
135 where ui_def_id=v_ui and NVL(seeded_flag,'0')='0';
136 commit;
137
138 update cz_ui_ref_templates
139 set deleted_flag='1'
140 where template_ui_def_id=v_ui ;
141
142 /*
143 update cz_ui_images
144 set deleted_flag='1'
145 where ui_def_id=v_ui;
146 commit;
147 */
148 delete from cz_ui_images
149 where ui_def_id=v_ui and NVL(seeded_flag,'0')='0';
150
151 update cz_ui_cont_type_templs
152 set deleted_flag='1'
153 where ui_def_id=v_ui;
154
155 --
156 -- drop jrad documents which associated with deleted pages
157 --
158 for k in(select jrad_doc from cz_ui_pages
159 where ui_def_id=v_ui and NVL(seeded_flag,'0')='0' and deleted_flag='0')
160 loop
161 begin
162 jdr_docbuilder.deleteDocument(k.jrad_doc);
163 exception
164 when others then
165 CZ_BASE_MGR.LOG_REPORT('CZ_UI_MGR.PURGE','deleteDocument "'||k.jrad_doc||'" : '||SQLERRM);
166 end;
167 end loop;
168
169 update cz_ui_pages
170 set deleted_flag='1'
171 where ui_def_id=v_ui and NVL(seeded_flag,'0')='0';
172 commit;
173
174 end loop;
175 commit;
176
177 CZ_BASE_MGR.exec('update CZ_LOCALIZED_TEXTS a set deleted_flag=''1'' where deleted_flag=''0'' and NVL(seeded_flag,''0'')='''||'0'||''' AND '||
178 'exists(select null from CZ_UI_NODES where caption_id=a.intl_text_id and deleted_flag=''1'') and '||
179 'not exists(select null from CZ_UI_NODES where caption_id=a.intl_text_id and deleted_flag=''0'')');
180
181
182 end if;
183
184 CZ_BASE_MGR.exec('CZ_LOCALIZED_TEXTS','where deleted_flag=''0'' and '||
185 'exists(select null from CZ_UI_NODES where caption_id=cz_localized_texts.intl_text_id and deleted_flag=''1'') and '||
186 'not exists(select null from CZ_UI_NODES where caption_id=cz_localized_texts.intl_text_id and deleted_flag=''0'')',
187 'language','intl_text_id',TRUE);
188
189 COMMIT;
190
191 exception
192 when others then
193 CZ_BASE_MGR.LOG_REPORT('CZ_UI_MGR.PURGE',SQLERRM);
194 end;
195
196 ---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
197 -----@Propagate_deleted_flag_for_global_entities
198 -----@This procedure handles the propagation of deleted flag for global entities
199 -----@global entities are global rules, actions, texts that are referred in
200 -----@global templates. The global entities are stored in the table cz_ui_template_elements.
201 -----@The element type in the table cz_ui_template_elements are as follows:
202 -----@ element_type : 33,34,700 --- rule_id (cz_rules)
203 -----@ element_type : 8 --- caption text (cz_localized_texts)
204 -----@ element_type : 522 --- ui action (cz_ui_actions)
205
206 PROCEDURE Propagate_del_flag_gl_entities
207 IS
208
209 TYPE global_templates_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
210 TYPE global_jrad_docs_tbl IS TABLE OF CZ_UI_TEMPLATES.jrad_doc%TYPE INDEX BY BINARY_INTEGER;
211 l_gl_template_tbl global_templates_tbl;
212 l_pb_gl_template_tbl global_templates_tbl;
213 l_gl_jrad_docs_tbl global_jrad_docs_tbl;
214 l_pb_gl_jrad_docs_tbl global_jrad_docs_tbl;
215 l_element_id_tbl global_templates_tbl;
216 l_element_type_tbl global_templates_tbl;
217 l_element_id_ref global_templates_tbl;
218 l_element_type_ref global_templates_tbl;
219 l_templ_id_ref global_templates_tbl;
220 l_templ_id_tbl global_templates_tbl;
221 rec_count NUMBER := 0;
222 l_return_status VARCHAR2(1);
223 l_msg_count NUMBER := 0;
224 l_msg_data VARCHAR2(2000);
225
226 BEGIN
227
228 ------collect source global templates from cz_ui_templates
229 ------where ui_def_id = 0 and deleted_flag = '1' and seeded_flag = '0'
230 l_gl_template_tbl.DELETE;
231 SELECT template_id, jrad_doc
232 BULK
233 COLLECT
234 INTO l_gl_template_tbl, l_gl_jrad_docs_tbl
235 FROM cz_ui_templates
236 WHERE cz_ui_templates.deleted_flag = '1'
237 AND cz_ui_templates.seeded_flag = '0'
238 AND cz_ui_templates.ui_def_id = 0;
239
240 -----collect published global templates that are candidates
241 -----for deletion.
242 -----The published global templates that are candidates for
243 -----deletion have
244 ------ ui_def_id = 1 and have no valid entry (deleted_flag = '0')
245 ------ in cz_model_publications
246
247
248 l_pb_gl_template_tbl.DELETE;
249 SELECT template_id BULK COLLECT
250 INTO l_pb_gl_template_tbl
251 FROM cz_ui_templates
252 WHERE cz_ui_templates.seeded_flag = '0'
253 AND cz_ui_templates.ui_def_id = 1
254 AND cz_ui_templates.template_id NOT IN (SELECT object_id
255 FROM cz_model_publications
256 WHERE object_type = 'UIT'
257 AND deleted_flag = '0'
258 AND ui_def_id = 1
259 AND source_target_flag = 'T');
260
261 ----gather the published global templates marked for deletion
262 ----into a single array
263 IF (l_pb_gl_template_tbl.COUNT > 0) THEN
264 rec_count := l_gl_template_tbl.COUNT;
265 FOR I IN l_pb_gl_template_tbl.FIRST..l_pb_gl_template_tbl.LAST
266 LOOP
267 rec_count := rec_count + 1;
268 l_gl_template_tbl(rec_count) := l_pb_gl_template_tbl(i);
269 END LOOP;
270 END IF;
271
272 -----collect published template jrad docs that are candidates for deletion and
273 -----are not part of valid publication
274 SELECT jrad_doc BULK COLLECT INTO l_pb_gl_jrad_docs_tbl
275 FROM cz_ui_templates t0
276 WHERE t0.seeded_flag = '0'
277 AND t0.ui_def_id = 1
278 AND NOT EXISTS (SELECT 1 FROM cz_model_publications
279 WHERE object_type = 'UIT'
280 AND deleted_flag = '0'
281 AND ui_def_id = 1
282 AND source_target_flag = 'T'
283 AND object_id = t0.template_id)
284 AND NOT EXISTS (SELECT 1 FROM cz_ui_templates t
285 WHERE t.seeded_flag = '0'
286 AND t.ui_def_id = 1
287 AND EXISTS (SELECT 1 FROM cz_model_publications
288 WHERE object_type = 'UIT'
289 AND deleted_flag = '0'
290 AND ui_def_id = 1
291 AND source_target_flag = 'T'
292 AND object_id = t.template_id)
293 AND t.jrad_doc = t0.jrad_doc) ;
294
295 ----gather the published global templates marked for deletion
296 ----into a single array
297 IF (l_gl_jrad_docs_tbl.COUNT > 0) THEN
298 rec_count := l_gl_jrad_docs_tbl.COUNT;
299 FOR I IN l_pb_gl_jrad_docs_tbl.FIRST..l_pb_gl_jrad_docs_tbl.LAST
300 LOOP
301 rec_count := rec_count + 1;
302 l_gl_jrad_docs_tbl(rec_count):= l_pb_gl_jrad_docs_tbl(i);
303 END LOOP;
304 END IF;
305
306
307
308 -----For each template id in the array l_gl_template_tbl, collect element id(s)
309 -----and element types from cz_ui_template_elements that have seeded_flag = '0'
310 IF (l_gl_template_tbl.COUNT > 0) THEN
311 l_element_id_ref.DELETE;
312 l_element_type_ref.DELETE;
313 l_templ_id_ref.DELETE;
314 FOR I IN l_gl_template_tbl.FIRST..l_gl_template_tbl.LAST
315 LOOP
316 l_element_id_tbl.DELETE;
317 l_element_type_tbl.DELETE;
318 l_templ_id_tbl.DELETE;
319 SELECT element_id,element_type,template_id
320 BULK
321 COLLECT
322 INTO l_element_id_tbl,l_element_type_tbl,l_templ_id_tbl
323 FROM cz_ui_template_elements
324 WHERE cz_ui_template_elements.template_id = l_gl_template_tbl(i)
325 AND cz_ui_template_elements.seeded_flag = '0';
326
327 IF (l_element_id_tbl.COUNT > 0) THEN
328 rec_count := l_element_id_ref.COUNT;
329 FOR J IN l_element_id_tbl.FIRST..l_element_id_tbl.LAST
330 LOOP
331 rec_count := rec_count + 1;
332 l_element_id_ref(rec_count) := l_element_id_tbl(j);
333 l_element_type_ref(rec_count) := l_element_type_tbl(j);
334 l_templ_id_ref(rec_count) := l_templ_id_tbl(j);
335 END LOOP;
336 END IF;
337
338 UPDATE cz_ui_template_elements
339 SET deleted_flag = '1'
340 WHERE template_id = l_gl_template_tbl(i)
341 AND seeded_flag = '0';
342
343 END LOOP;
344 END IF;
345
346 -- Delete JRAD documents from JDR repository
347 IF (l_gl_jrad_docs_tbl.COUNT > 0) THEN
348 FOR I IN l_gl_jrad_docs_tbl.FIRST..l_gl_jrad_docs_tbl.LAST
349 LOOP
350 BEGIN
351 jdr_docbuilder.deleteDocument(l_gl_jrad_docs_tbl(i));
352 EXCEPTION
353 WHEN OTHERS THEN
354 CZ_BASE_MGR.LOG_REPORT('CZ_UI_MGR.PURGE','deleteDocument "'||l_gl_jrad_docs_tbl(i)||'" : '||SQLERRM);
355 END;
356 END LOOP;
357 END IF;
358
359
360 -----For each element id update the relevant table
361 IF (l_element_id_ref.COUNT > 0) THEN
362 FOR I IN l_element_id_ref.FIRST..l_element_id_ref.LAST
363 LOOP
364 IF (l_element_type_ref(i) IN (33,34,700)) THEN
365 UPDATE cz_rules
366 SET deleted_flag = '1'
367 WHERE rule_id = l_element_id_ref(i)
368 AND seeded_flag = '0';
369 ELSIF (l_element_type_ref(i) = 8) THEN
370 UPDATE cz_localized_texts
371 SET deleted_flag = '1'
372 WHERE intl_text_id = l_element_id_ref(i)
373 AND seeded_flag = '0';
374 ELSIF (l_element_type_ref(i) = 522) THEN
375 UPDATE cz_ui_actions
376 SET deleted_flag = '1'
377 WHERE ui_action_id = l_element_id_ref(i)
378 AND seeded_flag = '0';
379 END IF;
380 END LOOP;
381 END IF;
382 COMMIT;
383 exception
384 when others then
385 CZ_BASE_MGR.LOG_REPORT('CZ_UI_MGR.PURGE',SQLERRM);
386 end;
387
388
389 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
390 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
391
392 procedure PURGE is
393 begin
394 Propogate_DeletedFlag;
395 Propagate_del_flag_gl_entities;
396 CZ_BASE_MGR.PURGE('UI');
397 end;
398
399 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
400 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
401
402 procedure RESET_CLEAR is
403 begin
404 null;
405 end;
406
407 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
408 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
409
410 procedure MODIFIED
411 (AS_OF in OUT NOCOPY date) is
412 begin
413 CZ_BASE_MGR.MODIFIED('UI',AS_OF);
414 end;
415
416 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
417 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
418
419 end;