1 package body CZ_PUB_MGR as
2 /* $Header: czpmmgrb.pls 120.3 2007/11/26 13:23:24 kdande ship $ */
3
4 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
5 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
6
7 procedure ASSESS_DATA is
8 begin
9 null;
10 end;
11
12
13 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
14 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
15
16 procedure REDO_STATISTICS is
17 begin
18 CZ_BASE_MGR.REDO_STATISTICS('PB');
19 end;
20
21 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
22 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
23
24 procedure TRIGGERS_ENABLED
25 (Switch in varchar2) is
26 begin
27 CZ_BASE_MGR.TRIGGERS_ENABLED('PB',Switch);
28 end;
29
30 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
31 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
32
33 procedure CONSTRAINTS_ENABLED
34 (Switch in varchar2) is
35 begin
36 CZ_BASE_MGR.CONSTRAINTS_ENABLED('PB',Switch);
37 end;
38
39 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
40 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
41
42 procedure REDO_SEQUENCES
43 (RedoStart_Flag in varchar2,
44 incr in integer default null) is
45 begin
46 CZ_BASE_MGR.REDO_SEQUENCES('PB',RedoStart_Flag,incr);
47 end;
48
49 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
50 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
51
52 procedure Propogate_DeletedFlag is
53
54 TYPE tPublTable IS TABLE OF CZ_MODEL_PUBLICATIONS.PUBLICATION_ID%TYPE INDEX BY BINARY_INTEGER;
55 TYPE tModelTable is table of cz_model_ref_expls.component_id%type index by binary_integer;
56 TYPE tModelTableIndexVC2 is table of cz_model_ref_expls.component_id%type index by VARCHAR2(15);
57
58 /* requiredModelTbl tPublTable; */
59 requiredModelTbl tModelTable;
60
61 /* hashRequiredModels tPublTable; */
62 hashRequiredModels tModelTableIndexVC2;
63
64 deletedPublsTbl tPublTable;
65 delPublsTbl tPublTable;
66 deletedPubl CZ_MODEL_REF_EXPLS.COMPONENT_ID%TYPE;
67
68 model_found NUMBER := 0;
69
70 x_error BOOLEAN:=FALSE;
71 errbuf VARCHAR2(255);
72
73 /* All deleted publications*/
74
75 CURSOR cDeletedPublication IS
76 SELECT devl_project_id
77 FROM cz_devl_projects
78 WHERE deleted_flag = '0'
79 AND devl_project_id NOT IN (SELECT object_id FROM cz_rp_entries
80 WHERE object_type = 'PRJ'
81 AND deleted_flag = '0');
82
83 begin
84 requiredModelTbl.DELETE;
85 hashRequiredModels.DELETE;
86
87 /* Get all published models, along with the child models that need to exist (undeleted models) */
88 SELECT distinct component_id
89 BULK COLLECT
90 INTO requiredModelTbl
91 FROM cz_model_ref_expls
92 WHERE deleted_flag = '0'
93 AND model_id IN (SELECT model_id FROM cz_model_publications
94 WHERE source_target_flag = 'T'
95 AND deleted_flag = '0')
96 CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id
97 ORDER BY component_id;
98
99 /* Build a hashtable of required model ids*/
100 IF (requiredModelTbl.COUNT > 0) THEN
101 FOR i in requiredModelTbl.FIRST .. requiredModelTbl.LAST
102 LOOP
103 hashRequiredModels(requiredModelTbl(i)) := requiredModelTbl(i);
104 END LOOP;
105 END IF;
106
107 /* Loop through all deleted models and check if it is not being referenced
108 by other publications. If so, then mark it for deletion */
109 BEGIN
110 open cDeletedPublication;
111 LOOP
112 FETCH cDeletedPublication into deletedPubl;
113 EXIT WHEN cDeletedPublication%NOTFOUND;
114
115 IF (NOT (hashRequiredModels.EXISTS(deletedPubl))) THEN
116 BEGIN
117 /* Logically delete model */
118 UPDATE cz_devl_projects
119 SET deleted_flag = '1'
120 WHERE devl_project_id = deletedPubl;
121 COMMIT;
122 EXCEPTION WHEN OTHERS THEN
123 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
124 END;
125 END IF;
126 END LOOP;
127 close cDeletedPublication;
128 EXCEPTION
129 WHEN OTHERS THEN
130 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
131 END;
132
133 exception
134 when no_data_found then
135 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
136 end;
137
138 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
139 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
140
141 PROCEDURE PURGE_RP_ENTRIES IS
142
143 TYPE t_arr IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
144 TYPE t_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
145
146 l_end_folder_ids_tbl t_arr;
147 l_temp NUMBER;
148
149 PROCEDURE collect_Folders(p_folder_id IN NUMBER) IS
150 l_folders_exist BOOLEAN := FALSE;
151 BEGIN
152
153 FOR i IN (SELECT object_id FROM CZ_RP_ENTRIES
154 WHERE enclosing_folder=p_folder_id AND object_type='FLD')
155 LOOP
156 l_folders_exist := TRUE;
157 collect_Folders(i.object_id);
158 END LOOP;
159
160 IF l_folders_exist=FALSE THEN
161 l_end_folder_ids_tbl(l_end_folder_ids_tbl.COUNT+1) := p_folder_id;
162 END IF;
163 END collect_Folders;
164
165 PROCEDURE update_Objects_In_Folder(p_folder_id IN NUMBER,x_enclosing_folder OUT NOCOPY NUMBER) IS
166
167 l_object_id_tbl t_arr;
168 l_object_type_tbl t_char;
169 l_folders_exist BOOLEAN := FALSE;
170
171 BEGIN
172
173 SELECT object_id, object_type
174 BULK COLLECT INTO l_object_id_tbl, l_object_type_tbl
175 FROM CZ_RP_ENTRIES
176 WHERE enclosing_folder=p_folder_id;
177
178 IF l_object_id_tbl.COUNT>0 THEN
179 FORALL i IN l_object_id_tbl.First..l_object_id_tbl.Last
180 DELETE FROM CZ_RP_ENTRIES
181 WHERE object_id=l_object_id_tbl(i) AND object_type=l_object_type_tbl(i);
182 END IF;
183
184 DELETE FROM CZ_RP_ENTRIES
185 WHERE object_id=p_folder_id AND object_type='FLD'
186 RETURNING enclosing_folder INTO x_enclosing_folder;
187
188 END update_Objects_In_Folder;
189
190 PROCEDURE goto_Folder(p_folder_id IN NUMBER, p_subroot_folder_id IN NUMBER) IS
191 l_enclosing_folder NUMBER;
192 BEGIN
193
194 update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
195
196 FOR i IN (SELECT object_id, enclosing_folder FROM CZ_RP_ENTRIES
197 WHERE object_id=l_enclosing_folder AND object_type='FLD')
198 LOOP
199 -- we should stop on this folder
200 IF i.object_id=p_subroot_folder_id THEN
201 update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
202 RETURN;
203 ELSE
204 goto_Folder(i.object_id, p_subroot_folder_id);
205 END IF;
206 END LOOP;
207
208 END goto_Folder;
209
210 BEGIN
211
212 FOR i IN(SELECT object_id FROM CZ_RP_ENTRIES
213 WHERE object_type='FLD' AND deleted_flag='1')
214 LOOP
215 --
216 -- array l_end_folder_ids_tbl will contain all subfolders
217 -- of folder i.object_id which have no subfolders
218 --
219 l_end_folder_ids_tbl.DELETE;
220
221 --
222 -- collect all folders which have no subfolders ( array l_end_folder_ids_tbl )
223 --
224 collect_Folders(i.object_id);
225
226 IF l_end_folder_ids_tbl.COUNT=0 THEN
227 RETURN;
228 END IF;
229
230 --
231 -- go up from folders which have no subfolders ( array l_end_folder_ids_tbl )
232 -- and update CZ_RP_ENTRIES level by level
233 --
234 FOR k IN l_end_folder_ids_tbl.First..l_end_folder_ids_tbl.Last
235 LOOP
236 IF l_end_folder_ids_tbl(k) <> i.object_id THEN
237 goto_Folder(l_end_folder_ids_tbl(k), i.object_id);
238 END IF;
239 END LOOP;
240
241 DELETE FROM CZ_RP_ENTRIES
242 WHERE enclosing_folder=i.object_id;
243
244 DELETE FROM CZ_RP_ENTRIES
245 WHERE object_id=i.object_id AND object_type='FLD';
246
247 END LOOP;
248
249 END PURGE_RP_ENTRIES;
250
251 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
252 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
253
254 procedure PURGE is
255 begin
256 Propogate_DeletedFlag;
257 PURGE_RP_ENTRIES;
258 CZ_BASE_MGR.PURGE('PB');
259
260 delete from cz_pb_client_apps a
261 where not exists ( select null from cz_model_publications b
262 where b.publication_id = a.publication_id );
263 commit;
264
265 delete from cz_pb_languages a
266 where not exists ( select null from cz_model_publications b
267 where b.publication_id = a.publication_id );
268 commit;
269
270 delete from cz_publication_usages a
271 where not exists ( select null from cz_model_publications b
272 where b.publication_id = a.publication_id );
273 commit;
274
275 --
276 -- keep last pb model export record for
277 -- server_id / model_id
278 --
279 for i in(select server_id,model_id,
280 max(export_id) as max_export_id
281 from cz_pb_model_exports
282 where status='OK'
283 group by server_id,model_id)
284 loop
285 delete from cz_pb_model_exports
286 where export_id<>i.max_export_id and model_id=i.model_id and
287 server_id=i.server_id;
288 end loop;
289 commit;
290
291 end;
292
293 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
294 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
295
296 procedure RESET_CLEAR is
297 begin
298 null;
299 end;
300
301 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
302 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
303
304 procedure MODIFIED
305 (AS_OF in OUT NOCOPY date) is
306 begin
307 CZ_BASE_MGR.MODIFIED('PB',AS_OF);
308 end;
309
310 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
311 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
312
313 end;
[Home] [Help]