1 package body CZ_PUB_MGR as
2 /* $Header: czpmmgrb.pls 120.5.12020000.2 2012/08/17 15:26:03 smanna 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 deletedPKey CZ_DEVL_PROJECTS.PRODUCT_KEY%TYPE;
68 vModelKeyStr CZ_DB_LOGS.MESSAGE%TYPE:=NULL;
69
70 model_found NUMBER := 0;
71
72 x_error BOOLEAN:=FALSE;
73 errbuf VARCHAR2(255);
74
75 /* All deleted publications*/
76 --Bug12653412: SKM - Adding an additional condition in order to run
77 --publication and purge at the same time. During publication we first create the
78 --records in cz_devl_projects and at much latter stage to cz_model_ref_expls.
79 --So, excluding such records not to be purge candidates.
80
81 --Bug#12653412: Additional condition added for the models with on-going
82 --publications are marked as published date not null. DO NOT select such models.
83
84 CURSOR cDeletedPublication IS
85 SELECT devl_project_id, NVL(product_key,name)
86 FROM cz_devl_projects
87 WHERE deleted_flag = '0'
88 AND published IS NULL
89 AND EXISTS (SELECT null FROM cz_model_ref_expls
90 WHERE model_id=cz_devl_projects.devl_project_id
91 AND deleted_flag='0')
92 AND devl_project_id NOT IN (SELECT object_id FROM cz_rp_entries
93 WHERE object_type = 'PRJ'
94 AND deleted_flag = '0');
95
96 begin
97 requiredModelTbl.DELETE;
98 hashRequiredModels.DELETE;
99
100 /* Get all published models, along with the child models that need to exist (undeleted models) */
101
102 SELECT distinct component_id
103 BULK COLLECT
104 INTO requiredModelTbl
105 FROM cz_model_ref_expls
106 WHERE deleted_flag = '0'
107 AND model_id IN (SELECT model_id FROM cz_model_publications
108 WHERE source_target_flag = 'T'
109 AND deleted_flag = '0')
110 CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id
111 ORDER BY component_id;
112
113 /* Build a hashtable of required model ids*/
114 IF (requiredModelTbl.COUNT > 0) THEN
115 FOR i in requiredModelTbl.FIRST .. requiredModelTbl.LAST
116 LOOP
117 hashRequiredModels(requiredModelTbl(i)) := requiredModelTbl(i);
118 END LOOP;
119 END IF;
120
121 /* Loop through all deleted models and check if it is not being referenced
122 by other publications. If so, then mark it for deletion */
123 BEGIN
124 open cDeletedPublication;
125 LOOP
126 FETCH cDeletedPublication into deletedPubl,deletedPKey;
127 EXIT WHEN cDeletedPublication%NOTFOUND;
128
129 IF (NOT (hashRequiredModels.EXISTS(deletedPubl))) THEN
130 BEGIN
131 /* Logically delete model */
132 UPDATE cz_devl_projects
133 SET deleted_flag = '1'
134 WHERE devl_project_id = deletedPubl
135 AND published IS NULL; /* Extra condition for over precaution*/
136
137 /*SKM: Added Loggin for deleted models / Product Key*/
138 /* Length of the db_logs is 4000 Max and hence inserting little less than that*/
139 vModelKeyStr := vModelKeyStr||deletedPKey||' - '||deletedPubl||'<-->';
140 IF (LENGTH (vModelKeyStr) > 3900 ) THEN
141 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG',vModelKeyStr, 'Tracking Deleted Projects',0,1);
142 vModelKeyStr := NULL;
143 END IF;
144 COMMIT;
145 EXCEPTION WHEN OTHERS THEN
146 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
147 END;
148 END IF;
149 END LOOP;
150 --SKM: for the Remaining fetch < 3900 length insert after exit from loop.
151 CZ_BASE_MGR.WRITE_TO_DB_LOGS('CZ_PURGE_LOG',vModelKeyStr, 'Tracking Deleted Projects',0,1);
152 close cDeletedPublication;
153 EXCEPTION
154 WHEN OTHERS THEN
155 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
156 END;
157
158 exception
159 when no_data_found then
160 errbuf := cz_utils.get_text('CZ_PUB_MGR_ERR','ERR',SQLERRM);
161 end;
162
163 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
164 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
165
166 PROCEDURE PURGE_RP_ENTRIES IS
167
168 TYPE t_arr IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
169 TYPE t_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
170
171 l_end_folder_ids_tbl t_arr;
172 l_temp NUMBER;
173
174 PROCEDURE collect_Folders(p_folder_id IN NUMBER) IS
175 l_folders_exist BOOLEAN := FALSE;
176 BEGIN
177
178 FOR i IN (SELECT object_id FROM CZ_RP_ENTRIES
179 WHERE enclosing_folder=p_folder_id AND object_type='FLD')
180 LOOP
181 l_folders_exist := TRUE;
182 collect_Folders(i.object_id);
183 END LOOP;
184
185 IF l_folders_exist=FALSE THEN
186 l_end_folder_ids_tbl(l_end_folder_ids_tbl.COUNT+1) := p_folder_id;
187 END IF;
188 END collect_Folders;
189
190 PROCEDURE update_Objects_In_Folder(p_folder_id IN NUMBER,x_enclosing_folder OUT NOCOPY NUMBER) IS
191
192 l_object_id_tbl t_arr;
193 l_object_type_tbl t_char;
194 l_folders_exist BOOLEAN := FALSE;
195
196 BEGIN
197
198 SELECT object_id, object_type
199 BULK COLLECT INTO l_object_id_tbl, l_object_type_tbl
200 FROM CZ_RP_ENTRIES
201 WHERE enclosing_folder=p_folder_id;
202
203 IF l_object_id_tbl.COUNT>0 THEN
204 FORALL i IN l_object_id_tbl.First..l_object_id_tbl.Last
205 DELETE FROM CZ_RP_ENTRIES
206 WHERE object_id=l_object_id_tbl(i) AND object_type=l_object_type_tbl(i);
207 END IF;
208
209 DELETE FROM CZ_RP_ENTRIES
210 WHERE object_id=p_folder_id AND object_type='FLD'
211 RETURNING enclosing_folder INTO x_enclosing_folder;
212
213 END update_Objects_In_Folder;
214
215 PROCEDURE goto_Folder(p_folder_id IN NUMBER, p_subroot_folder_id IN NUMBER) IS
216 l_enclosing_folder NUMBER;
217 BEGIN
218
219 update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
220
221 FOR i IN (SELECT object_id, enclosing_folder FROM CZ_RP_ENTRIES
222 WHERE object_id=l_enclosing_folder AND object_type='FLD')
223 LOOP
224 -- we should stop on this folder
225 IF i.object_id=p_subroot_folder_id THEN
226 update_Objects_In_Folder(p_folder_id, l_enclosing_folder);
227 RETURN;
228 ELSE
229 goto_Folder(i.object_id, p_subroot_folder_id);
230 END IF;
231 END LOOP;
232
233 END goto_Folder;
234
235 BEGIN
236
237 FOR i IN(SELECT object_id FROM CZ_RP_ENTRIES
238 WHERE object_type='FLD' AND deleted_flag='1')
239 LOOP
240 --
241 -- array l_end_folder_ids_tbl will contain all subfolders
242 -- of folder i.object_id which have no subfolders
243 --
244 l_end_folder_ids_tbl.DELETE;
245
246 --
247 -- collect all folders which have no subfolders ( array l_end_folder_ids_tbl )
248 --
249 collect_Folders(i.object_id);
250
251 IF l_end_folder_ids_tbl.COUNT=0 THEN
252 RETURN;
253 END IF;
254
255 --
256 -- go up from folders which have no subfolders ( array l_end_folder_ids_tbl )
257 -- and update CZ_RP_ENTRIES level by level
258 --
259 FOR k IN l_end_folder_ids_tbl.First..l_end_folder_ids_tbl.Last
260 LOOP
261 IF l_end_folder_ids_tbl(k) <> i.object_id THEN
262 goto_Folder(l_end_folder_ids_tbl(k), i.object_id);
263 END IF;
264 END LOOP;
265
266 DELETE FROM CZ_RP_ENTRIES
267 WHERE enclosing_folder=i.object_id;
268
269 DELETE FROM CZ_RP_ENTRIES
270 WHERE object_id=i.object_id AND object_type='FLD';
271
272 END LOOP;
273
274 END PURGE_RP_ENTRIES;
275
276 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
277 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
278
279 procedure PURGE is
280 begin
281 Propogate_DeletedFlag;
282 PURGE_RP_ENTRIES;
283 CZ_BASE_MGR.PURGE('PB');
284
285 delete from cz_pb_client_apps a
286 where not exists ( select null from cz_model_publications b
287 where b.publication_id = a.publication_id );
288 commit;
289
290 delete from cz_pb_languages a
291 where not exists ( select null from cz_model_publications b
292 where b.publication_id = a.publication_id );
293 commit;
294
295 delete from cz_publication_usages a
296 where not exists ( select null from cz_model_publications b
297 where b.publication_id = a.publication_id );
298 commit;
299
300 --
301 -- keep last pb model export record for
302 -- server_id / model_id
303 --
304 for i in(select server_id,model_id,
305 max(export_id) as max_export_id
306 from cz_pb_model_exports
307 where status='OK'
308 group by server_id,model_id)
309 loop
310 delete from cz_pb_model_exports
311 where export_id<>i.max_export_id and model_id=i.model_id and
312 server_id=i.server_id;
313 end loop;
314 commit;
315
316 end;
317
318 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
319 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
320
321 procedure RESET_CLEAR is
322 begin
323 null;
324 end;
325
326 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
327 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
328
329 procedure MODIFIED
330 (AS_OF in OUT NOCOPY date) is
331 begin
332 CZ_BASE_MGR.MODIFIED('PB',AS_OF);
333 end;
334
335 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
336 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
337
338 end;
[Home] [Help]