[Home] [Help]
PACKAGE BODY: APPS.ICX_CONTENTS_POSTUPG_PVT
Source
1 PACKAGE BODY ICX_CONTENTS_POSTUPG_PVT AS
2 /* $Header: ICXVCPUB.pls 120.1 2008/02/07 09:35:06 krsethur noship $*/
3
4 -- GLOBAL VARIABLES
5 -------------------
6
7 -- For logging purposes
8 g_pkg_name CONSTANT VARCHAR2(30) := 'ICX_CONTENTS_POSTUPG_PVT';
9
10 g_categories_per_zone PLS_INTEGER := 150;
11
12
13 -- TOP-LEVEL AUTO_SPLIT METHOD
14 ---------------------------
15
16 -- This is the entry method. This identifies the list of content zones
17 -- whose sqe sequence is not built due to the column size restriction and
18 -- splits them into multiple content zones. The following outlines the
19 -- high-level flow of the method:
20 --
21 -- 1. Get local content zones with sqe_sequence not populated due to size
22 -- restriction
23 -- 2. For each of the zones, find the category count. Group them into
24 -- multiple zones with 150 categories each.
25 -- 3. Create icx_cat_content_zones_b, icx_cat_content_zones_tl for each of
26 -- these new zones.
27 -- 4. Duplicate the supplier restrictions from the original zones to the new
28 -- zones.
29 -- 5. Create Category restrictions to these new zones.
30 -- 6. Duplicate the icx_cat_secure_contents to the new zones.
31 -- 7. Add these zone to all stores with which the original zone was attached.
32 -- 8. After all the new zones are created, delete the original zones from
33 -- all the relevant tables.
34 -- 9. Populate SQEs required for Catalog Search.
35 --
36 -- @return High-level status to indicate success or failure.
37 --
38 PROCEDURE auto_split
39 IS
40
41 l_original_zone_ids ICX_TBL_NUMBER := ICX_TBL_NUMBER();
42 l_new_zone_ids ICX_TBL_NUMBER := ICX_TBL_NUMBER();
43 l_zone_categories ICX_MAP_TBL_NUMBER;
44
45 l_api_name CONSTANT VARCHAR2(30) := 'auto_split';
46 l_err_loc PLS_INTEGER;
47
48 BEGIN
49
50 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
51 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
52 END IF;
53
54 l_err_loc := 0;
55
56 SELECT zone_id
57 BULK COLLECT INTO l_original_zone_ids
58 FROM icx_cat_content_zones_b
59 WHERE type='LOCAL'
60 AND category_attribute_action_flag='INCLUDE'
61 AND sqe_sequence IS NULL;
62
63
64 l_err_loc := 100;
65
66 -- If no zones to split, then just return.
67 IF(l_original_zone_ids.count = 0) THEN
68 return;
69 END IF;
70
71 -- Loop through the zones and split
72 FOR i IN 1..l_original_zone_ids.Count
73 LOOP
74
75 l_new_zone_ids.DELETE;
76 l_zone_categories.DELETE;
77
78 l_err_loc := 200;
79
80 get_new_zones_and_categorylist(l_original_zone_ids(i),
81 l_new_zone_ids,
82 l_zone_categories);
83
84 l_err_loc := 300;
85
86 create_content_zones(l_original_zone_ids(i),
87 l_new_zone_ids);
88
89 l_err_loc := 400;
90
91 create_category_restrictions(l_original_zone_ids(i),
92 l_new_zone_ids,
93 l_zone_categories );
94
95 l_err_loc := 500;
96
97 create_secure_contents(l_original_zone_ids(i),
98 l_new_zone_ids);
99
100 l_err_loc := 600;
101
102 add_new_zones_to_stores(l_original_zone_ids(i),
103 l_new_zone_ids);
104
105 l_err_loc := 700;
106
107 delete_old_zone(l_original_zone_ids(i));
108
109 l_err_loc := 800;
110
111 END LOOP;
112
113 l_err_loc := 900;
114
115 -- finally, populate corresponding SQEs for search
116 ICX_CAT_SQE_PVT.sync_sqes_for_all_zones();
117 l_err_loc := 1000;
118
119 commit;
120
121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
123 END IF;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 RAISE_APPLICATION_ERROR
128 (-20000,
129 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
130 l_err_loc || '), ' || SQLERRM);
131
132 END auto_split;
133
134
135
136 PROCEDURE get_new_zones_and_categorylist(p_original_zone_id IN NUMBER,
137 p_new_zone_ids OUT NOCOPY ICX_TBL_NUMBER,
138 p_zone_categories OUT NOCOPY ICX_MAP_TBL_NUMBER) IS
139
140 l_category_list ICX_TBL_NUMBER := ICX_TBL_NUMBER();
141 l_zone_categories ICX_TBL_NUMBER := ICX_TBL_NUMBER();
142 l_no_of_zones_reqd PLS_INTEGER;
143 l_api_name CONSTANT VARCHAR2(30) := 'get_new_zones_and_categorylist';
144 l_err_loc PLS_INTEGER;
145 l_zone_counter PLS_INTEGER := 0;
146
147 BEGIN
148
149 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
150 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
151 END IF;
152
153 l_err_loc := 0;
154
155 SELECT ip_category_id
156 BULK COLLECT INTO l_category_list
157 FROM icx_cat_zone_secure_attributes
158 WHERE ZONE_ID=p_original_zone_id
159 AND SECURING_ATTRIBUTE= 'CATEGORY';
160
161 l_err_loc := 100;
162
163 l_no_of_zones_reqd := ceil(l_category_list.COUNT / g_categories_per_zone);
164
165 p_new_zone_ids := ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_new_zone_ids(l_no_of_zones_reqd);
166
167 l_err_loc := 200;
168
169 FOR i in 1..l_category_list.COUNT
170 LOOP
171
172 l_zone_categories.EXTEND;
173 l_zone_categories(l_zone_categories.COUNT) := l_category_list(i);
174
175 IF( (mod(i,g_categories_per_zone) = 0) OR i = l_category_list.COUNT ) THEN
176 l_err_loc := 300;
177 l_zone_counter := l_zone_counter +1;
178 p_zone_categories(p_new_zone_ids(l_zone_counter)) := l_zone_categories;
179 l_zone_categories.DELETE;
180
181 END IF;
182
183
184 END LOOP;
185
186 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
187 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
188 END IF;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 RAISE_APPLICATION_ERROR
193 (-20000,
194 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
195 l_err_loc || '), ' || SQLERRM);
196
197 END get_new_zones_and_categorylist;
198
199
200 PROCEDURE create_content_zones(p_old_zone_id IN NUMBER,
201 p_new_zone_ids IN ICX_TBL_NUMBER) IS
202
203 l_api_name CONSTANT VARCHAR2(30) := 'create_content_zones_b';
204 l_err_loc PLS_INTEGER;
205 l_index_tab ICX_IDX_TBL_NUMBER;
206
207 BEGIN
208
209 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
210 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
211 END IF;
212
213 l_err_loc := 0;
214
215 FORALL i IN 1..p_new_zone_ids.COUNT
216 INSERT INTO icx_cat_content_zones_b
217 (zone_id, type, url, security_assignment_flag,
218 category_attribute_action_flag, supplier_attribute_action_flag,
219 items_without_supplier_flag, items_without_shop_catg_flag,
220 created_by, creation_date, last_updated_by,
221 last_update_date, last_update_login)
222 SELECT p_new_zone_ids(i), zones.type, zones.url, zones.security_assignment_flag,
223 zones.category_attribute_action_flag, zones.supplier_attribute_action_flag,
224 zones.items_without_supplier_flag, zones.items_without_shop_catg_flag,
225 zones.created_by, zones.creation_date, zones.last_updated_by,
226 zones.last_update_date, zones.last_update_login
227 FROM icx_cat_content_zones_b zones
228 WHERE zones.zone_id = p_old_zone_id;
229
230 l_err_loc := 100;
231
232 FOR i in 1..p_new_zone_ids.COUNT
233 LOOP
234 l_index_tab(i) := i;
235 END LOOP;
236
237 l_err_loc := 200;
238
239 FORALL i IN 1..p_new_zone_ids.COUNT
240 INSERT INTO icx_cat_content_zones_tl
241 (zone_id, language, source_lang, name, description, keywords, image,
242 created_by, creation_date, last_updated_by, last_update_date,
243 last_update_login)
244 SELECT p_new_zone_ids(i), zones_tl.language, zones_tl.source_lang, zones_tl.name || ' : ' || l_index_tab(i),
245 zones_tl.description, zones_tl.keywords, zones_tl.image,
246 zones_tl.created_by, zones_tl.creation_date, zones_tl.last_updated_by, zones_tl.last_update_date,
247 zones_tl.last_update_login
248 FROM icx_cat_content_zones_tl zones_tl
249 WHERE zones_tl.zone_id = p_old_zone_id;
250
251
252 l_err_loc := 300;
253
254 -- migrate over supplier restrictions, if any
255 FORALL i IN 1..p_new_zone_ids.COUNT
256 INSERT INTO icx_cat_zone_secure_attributes
257 (zone_id, securing_attribute, supplier_id, created_by, creation_date,
258 last_updated_by, last_update_date, last_update_login)
259 SELECT p_new_zone_ids(i), zone_attr.securing_attribute, zone_attr.supplier_id,
260 zone_attr.created_by, zone_attr.creation_date,
261 zone_attr.last_updated_by, zone_attr.last_update_date, zone_attr.last_update_login
262 FROM icx_cat_zone_secure_attributes zone_attr
263 WHERE zone_attr.zone_id = p_old_zone_id
264 AND zone_attr.securing_attribute = 'SUPPLIER';
265
266 l_err_loc := 400;
267
268 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
269 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
270 END IF;
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 RAISE_APPLICATION_ERROR
275 (-20000,
276 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
277 l_err_loc || '), ' || SQLERRM);
278
279 END create_content_zones;
280
281
282 PROCEDURE create_category_restrictions(p_old_zone_id IN NUMBER,
283 p_new_zone_ids IN ICX_TBL_NUMBER ,
284 p_zone_categories IN ICX_MAP_TBL_NUMBER ) IS
285
286 l_api_name CONSTANT VARCHAR2(30) := 'create_category_restrictions';
287 l_err_loc PLS_INTEGER;
288
289 BEGIN
290
291 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
292 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
293 END IF;
294
295 l_err_loc := 0;
296
297 FOR i in 1..p_new_zone_ids.COUNT
298 LOOP
299 l_err_loc := 100;
300 FORALL j in 1..p_zone_categories(p_new_zone_ids(i)).COUNT
301 INSERT INTO icx_cat_zone_secure_attributes
302 (zone_id, securing_attribute, ip_category_id, created_by, creation_date,
303 last_updated_by, last_update_date, last_update_login)
304 SELECT p_new_zone_ids(i), 'CATEGORY', p_zone_categories(p_new_zone_ids(i))(j),
305 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
306 fnd_global.login_id
307 FROM dual;
308
309 END LOOP;
310
311 l_err_loc := 100;
312
313
314 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
315 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
316 END IF;
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 RAISE_APPLICATION_ERROR
321 (-20000,
322 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
323 l_err_loc || '), ' || SQLERRM);
324
325 END create_category_restrictions;
326
327
328 PROCEDURE create_secure_contents(p_old_zone_id IN NUMBER,
329 p_new_zone_ids IN ICX_TBL_NUMBER) IS
330
331 l_api_name CONSTANT VARCHAR2(30) := 'create_secure_contents';
332 l_err_loc PLS_INTEGER;
333
334 BEGIN
335
336 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
337 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
338 END IF;
339
340 l_err_loc := 0;
341
342 FORALL i IN 1..p_new_zone_ids.COUNT
343 INSERT INTO icx_cat_secure_contents
344 (content_id, org_id, responsibility_id, secure_by, created_by, creation_date,
345 last_updated_by, last_update_date, last_update_login)
346 SELECT p_new_zone_ids(i), contents.org_id, contents.responsibility_id,
347 contents.secure_by, contents.created_by, contents.creation_date,
348 contents.last_updated_by, contents.last_update_date, contents.last_update_login
349 FROM icx_cat_secure_contents contents
350 WHERE contents.content_id = p_old_zone_id;
351
352 l_err_loc := 100;
353
354
355 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
356 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 RAISE_APPLICATION_ERROR
362 (-20000,
363 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
364 l_err_loc || '), ' || SQLERRM);
365
366 END create_secure_contents;
367
368 PROCEDURE add_new_zones_to_stores(p_old_zone_id IN NUMBER,
369 p_new_zone_ids IN ICX_TBL_NUMBER) IS
370
371 l_api_name CONSTANT VARCHAR2(30) := 'add_new_zones_to_stores';
372 l_err_loc PLS_INTEGER;
373
374 BEGIN
375
376 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
377 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
378 END IF;
379
380 l_err_loc := 0;
381
382 FORALL i IN 1..p_new_zone_ids.COUNT
383 INSERT INTO icx_cat_store_contents
384 (store_id, content_id, content_type, sequence, display_always_flag,
385 created_by, creation_date, last_updated_by, last_update_date,
386 last_update_login)
387 SELECT sc.store_id, p_new_zone_ids(i), sc.content_type, sc.sequence,
388 sc.display_always_flag, sc.created_by, sc.creation_date,
389 sc.last_updated_by, sc.last_update_date, sc.last_update_login
390 FROM icx_cat_store_contents sc
391 WHERE sc.content_id = p_old_zone_id;
392
393 l_err_loc := 100;
394
395 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
396 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
397 END IF;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 RAISE_APPLICATION_ERROR
402 (-20000,
403 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
404 l_err_loc || '), ' || SQLERRM);
405
406 END add_new_zones_to_stores;
407
408
409 PROCEDURE delete_old_zone(p_old_zone_id IN NUMBER) IS
410
411 l_api_name CONSTANT VARCHAR2(30) := 'delete_old_zone';
412 l_err_loc PLS_INTEGER;
413
414 BEGIN
415
416 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
417 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
418 END IF;
419
420 l_err_loc := 0;
421
422 -- delete from icx_cat_store_contents
423 DELETE icx_cat_store_contents
424 WHERE content_id = p_old_zone_id;
425
426 l_err_loc := 100;
427
428 -- delete from icx_cat_secure_contents
429 DELETE icx_cat_secure_contents
430 WHERE content_id = p_old_zone_id;
431
432 l_err_loc := 200;
433
434 -- delete from icx_cat_zone_secure_attributes
435 DELETE icx_cat_zone_secure_attributes
436 WHERE zone_id=p_old_zone_id;
437
438 l_err_loc := 300;
439
440 -- delete from icx_cat_content_zones_tl
441 DELETE icx_cat_content_zones_tl
442 WHERE zone_id=p_old_zone_id;
443
444 l_err_loc := 400;
445
446 -- delete from icx_cat_content_zones_b
447 DELETE icx_cat_content_zones_b
448 WHERE zone_id=p_old_zone_id;
449
450 l_err_loc := 500;
451
452
453 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
454 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
455 END IF;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 RAISE_APPLICATION_ERROR
460 (-20000,
461 'Exception at ' || g_pkg_name || '.' || l_api_name || '(' ||
462 l_err_loc || '), ' || SQLERRM);
463
464 END delete_old_zone;
465
466
467 END ICX_CONTENTS_POSTUPG_PVT;