1 PACKAGE BODY ICX_CAT_CNTNT_SCRTY_UPG_PVT AS
2 /* $Header: ICXVCSUB.pls 120.5.12000000.2 2007/01/29 22:50:52 huphan ship $*/
3
4 -- INFORMATION
5 --------------
6 --
7 -- The security assignment flag can be:
8 -- (1) ALL_USERS (when old store was secured by -2, all operating units)
9 -- (2) OU_SECURED (when old store was secured by some operating units)
10 -- (3) RESP_SECURED (in the case realms needs to be considered)
11 --
12 -- The content zone supplier flag can be:
13 -- (1) INCLUDE_ALL (for the base catalog)
14 -- (2) INCLUDE (some supplier restrictions)
15 -- (3) EXCLUDE_ALL (when only the w/o supplier checkbox is indicated)
16 -- (4) EXCLUDE (can't happen on upgrade as this was not allowed in 11.5.10)
17 --
18 -- The content zone category flag can be:
19 -- (1) INCLUDE_ALL (no realms)
20 -- (2) INCLUDE (for the case with realms)
21 -- (4) EXCLUDE (not used for upgrade, as not supported by realms in 11.5.10)
22 --
23 -- About Logging: Since this is an upgrade script, by default, Exception
24 -- level logging will be enabled. Hence, this script will mark out critical
25 -- data to the log with Exception Level even if there were no exceptional case
26 -- involved.
27
28
29 -- GLOBAL VARIABLES
30 -------------------
31
32 -- For logging purposes
33 g_pkg_name CONSTANT VARCHAR2(30) := 'ICX_CAT_CNTNT_SCRTY_UPG_PVT';
34
35 -- Stores the FND_PROFILE ID for ORG_ID (Operating Unit)
36 g_ou_profile_id NUMBER;
37
38 -- Stores the FND_PROFILE site-level value for ORG_ID (Operating Unit)
39 g_site_ou_profile_value NUMBER;
40
41 -- Stores the FND_PROFILE site-level value for POR_APPROVED_PRICING
42 g_site_approved_pricing VARCHAR2(1);
43
44 -- Map of old stores to new security assignment flag for content zones
45 g_store_security_flags ICX_MAP_VARCHAR20;
46
47 -- Map of local catalogs to their supplier attribute flag values
48 g_catalog_supplier_flags ICX_MAP_VARCHAR20;
49
50 -- Map of old store to new R12 store IDs
51 g_stores_map ICX_MAP_NUMBER;
52
53 -- Map of old exchange item source IDs to new content zone IDs;
54 -- required for upgraded downloaded punchouts from exchange
55 g_exchange_punchout_map ICX_MAP_NUMBER;
56
57 -- Map of item sources to responsibilties that can access them (realms)
58 g_item_sources_to_resp_map ICX_MAP_TBL_NUMBER;
59
60 -- Map of responsibilities to accessible categories (realms)
61 g_resp_to_categories_map ICX_MAP_TBL_NUMBER;
62
63 -- Indicates a pre-11.5.9 upgrade which requires special handling
64 g_is_pre_1159_upgrade BOOLEAN;
65
66 -- Determines if installation has any category realms
67 g_uses_category_realms BOOLEAN;
68
69 -- Determines if installation has any item source realms
70 g_uses_item_source_realms BOOLEAN;
71
72 -- List of all responsibilities with realms
73 g_resp_with_category_realms ICX_TBL_NUMBER;
74
75 -- List of all responsibilities without realms
76 g_resp_without_category_realms ICX_TBL_NUMBER;
77
78 -- List of all responsibilities with realms
79 g_resp_with_isrc_realms ICX_TBL_NUMBER;
80
81 -- List of all responsibilities without realms
82 g_resp_without_isrc_realms ICX_TBL_NUMBER;
83
84
85 -- TOP-LEVEL MIGRATE METHOD
86 ---------------------------
87
88 -- The primary migrate method. This is the starting point for the upgrade
89 -- script for content security. The following outlines the high-level flow
90 -- of the method:
91 --
92 -- 1. Don't run the script if it's already been run.
93 -- 2. Add the default Smart Form link, as it's always required for seeding.
94 -- 3. In the new installation case, also add the link the Main Store.
95 -- 4. Handle the special-case with seeded data (both pre- and post-processing).
96 -- 5. Migrate stores by creating new R12 stores for all existing stores.
97 -- 6. Attempt to migrate local catalogs without category realms.
98 -- 7. Attempt to migrate item sources without item source realms.
99 -- 8. If realms needs to be considered for either local catalogs or
100 -- item sources, then migrate them with realms.
101 -- 9. Migrate non-catalog templates.
102 -- 10. Populate SQEs required for Catalog Search.
103 --
104 -- @return High-level status to indicate success or failure.
105 --
106 PROCEDURE migrate
107 IS
108 l_local_catalogs_migrated BOOLEAN;
109 l_item_sources_migrated BOOLEAN;
110
111 l_api_name CONSTANT VARCHAR2(30) := 'migrate';
112 l_err_loc PLS_INTEGER;
113 BEGIN
114
115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
116 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
117 END IF;
118
119 l_err_loc := 0;
120
121 -- if this script has already run (should be re-runnable), then skip
122 -- the rest of the script
123 IF (is_already_run()) THEN
124 RETURN; -- no need to do further content security migration
125 END IF;
126
127 -- add the link between the default smart form and the Smart Form store
128 add_smart_form_content_link();
129 l_err_loc := 50;
130
131 -- if this is a new installation, no actual migration from the old data
132 -- model is needed; instead, just add the link from the seeded "Main Store"
133 -- to the seeded "All Local Content" as this is not handled by the .ldt
134 IF (is_new_installation()) THEN
135
136 add_main_store_content_link();
137 l_err_loc := 50;
138
139 RETURN; -- no need to do further content security migration
140
141 END IF;
142
143 -- begin by initializing the packages' global variables
144 initialize();
145 l_err_loc := 100;
146
147 -- start populating global data that is accessed often, but can be
148 -- retrieved once from the database and saved to memory
149 populate_global_data();
150 l_err_loc := 200;
151
152 -- special handing, migrate seed data
153 seed_data_preprocessing();
154 l_err_loc := 300;
155
156 -- start by migrating the stores first;
157 -- ALSO, this will populate the global map of old to new stores
158 create_R12_stores();
159 l_err_loc := 400;
160
161 -- attempt to migrate all the local catalogs without the complications
162 -- of realms; if this cannot be done, defer to later
163 l_local_catalogs_migrated := try_migrate_catalogs_no_realms();
164 l_err_loc := 500;
165
166 -- attempt to migrate all the item sources without the complications
167 -- of realms; if this cannot be done, defer to later
168 l_item_sources_migrated := try_migrate_isrcs_no_realms();
169 l_err_loc := 600;
170
171 -- if either local catalogs or item sources have not been migrated (as
172 -- they needed to consider realms), then migrate them now
173 IF NOT (l_local_catalogs_migrated AND l_item_sources_migrated) THEN
174
175 migrate_content_with_realms(l_local_catalogs_migrated,
176 l_item_sources_migrated);
177 l_err_loc := 700;
178
179 END IF;
180
181 -- migrate the non-catalog templates
182 migrate_templates();
183 l_err_loc := 800;
184
185 -- after finishing migration, handle any seed data processing post-upgrade
186 seed_data_postprocessing();
187 l_err_loc := 900;
188
189 -- finally, populate corresponding SQEs for search
190 ICX_CAT_SQE_PVT.sync_sqes_for_all_zones();
191 l_err_loc := 1000;
192
193 -- commit!
194 commit;
195
196 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
197 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RAISE_APPLICATION_ERROR
203 (-20000,
204 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate(' ||
205 l_err_loc || '), ' || SQLERRM);
206
207 END migrate;
208
209
210 -- Determines if this script has already been run by checking to see if there
211 -- is any other content besides the seeded stores and content zones.
212 --
213 -- NOTE: There is one small corner case where this method will return false
214 -- even if the migrate has been run. This is when there are is ONLY
215 -- one local content (no other item sources) and no category realms
216 -- is used AND there is only one seeded store. In this case, after
217 -- migration, only data that appears to be seeded will remain, seemly
218 -- like the migration has not been run. However, the code will handle
219 -- this case in the regular code path by simply moving this one local
220 -- catalog and one store into R12 again.
221 --
222 FUNCTION is_already_run
223 RETURN BOOLEAN
224 IS
225 exists_non_seeded_data NUMBER;
226 l_err_loc PLS_INTEGER;
227 BEGIN
228
229 l_err_loc := 0;
230
231 BEGIN
232
233 SELECT 1
234 INTO exists_non_seeded_data
235 FROM icx_cat_content_zones_b z, icx_cat_shop_stores_b s
236 WHERE (z.zone_id NOT IN (1,2) OR s.store_id NOT IN (1,2))
237 AND rownum = 1;
238 l_err_loc := 100;
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242
243 -- just seeded data; script has not been run
244 exists_non_seeded_data := -1;
245 l_err_loc := 200;
246
247 END;
248
249 -- log fact that script has already been run
250 IF ((exists_non_seeded_data = 1) AND
251 (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
252
253 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
254 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, 'is_already_run'),
255 'Detected that script already run.');
256
257 END IF;
258
259 RETURN (exists_non_seeded_data = 1);
260
261 EXCEPTION
262 WHEN OTHERS THEN
263 RAISE_APPLICATION_ERROR
264 (-20000,
265 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.is_already_run(' ||
266 l_err_loc || '), ' || SQLERRM);
267
268 END is_already_run;
269
270
271 -- Determines if this is a new R12 installation (rather than an upgrade from
272 -- 11.5.10) by checking the existence of the 11.5.10's stores table. There is
273 -- no direct migration from older versions, so 11.5.10 is a sufficient check.
274 --
275 FUNCTION is_new_installation
276 RETURN BOOLEAN
277 IS
278 exists_catalogs_data NUMBER;
279 l_icx_schema_name VARCHAR2(20);
280 l_err_loc PLS_INTEGER;
281 BEGIN
282
283 l_err_loc := 0;
284
285 l_icx_schema_name := ICX_CAT_UTIL_PVT.getIcxSchemaName();
286 l_err_loc := 100;
287
288 BEGIN
289
290 SELECT 1
291 INTO exists_catalogs_data
292 FROM all_objects
293 WHERE owner = l_icx_schema_name
294 AND object_name = 'ICX_POR_ITEM_SOURCES';
295 l_err_loc := 200;
296
297 SELECT 1
298 INTO exists_catalogs_data
299 FROM icx_por_item_sources
300 WHERE rownum = 1;
301 l_err_loc := 300;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305
306 -- there's no item sources table or data
307 exists_catalogs_data := -1;
308 l_err_loc := 400;
309
310 END;
311
312 -- as far as content security upgrade is concerned, if there is no data
313 -- (seeded or otherwise) in either catalogs table, then this is
314 -- a "new installation," and nothing needs to be done
315 IF ((exists_catalogs_data = -1) AND
316 (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
317
318 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
319 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, 'is_new_installation'),
320 'Detected that this is a new R12 installation.');
321
322 END IF;
323
324 RETURN (exists_catalogs_data = -1);
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 RAISE_APPLICATION_ERROR
329 (-20000,
330 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.is_new_installation(' ||
331 l_err_loc || '), ' || SQLERRM);
332
333 END is_new_installation;
334
335
336 -- INITIALIZATION AND GLOBAL DATA
337 ---------------------------------
338
339 -- Initializes the Global Variables. This is required as they must be cleared
340 -- if the upgrade is re-run.
341 --
342 PROCEDURE initialize
343 IS
344 l_api_name CONSTANT VARCHAR2(30) := 'initialize';
345 l_err_loc PLS_INTEGER;
346 BEGIN
347
348 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
350 END IF;
351
352 l_err_loc := 0;
353
354 -- defaults for numbers and strings
355 g_ou_profile_id := -1;
356 g_site_ou_profile_value := -1;
357 g_site_approved_pricing := NULL;
358
359 l_err_loc := 100;
360
361 -- delete all elements from collections
362 g_store_security_flags.DELETE;
363 g_catalog_supplier_flags.DELETE;
364 g_stores_map.DELETE;
365 g_item_sources_to_resp_map.DELETE;
366 g_resp_to_categories_map.DELETE;
367
368 l_err_loc := 200;
369
370 -- empty starting tables
371 g_resp_with_category_realms := ICX_TBL_NUMBER();
372 g_resp_without_category_realms := ICX_TBL_NUMBER();
373 g_resp_with_isrc_realms := ICX_TBL_NUMBER();
374 g_resp_without_isrc_realms := ICX_TBL_NUMBER();
375
376 l_err_loc := 300;
377
378 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
379 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
380 END IF;
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 RAISE_APPLICATION_ERROR
385 (-20000,
386 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.initialize(' ||
387 l_err_loc || '), ' || SQLERRM);
388
389 END initialize;
390
391
392 -- Populates any global data that is accessed frequently by querying the
393 -- database once, and starting this data for later. Currently this includes:
394 --
395 -- 1. Map of old store IDs to the security assignment flag values that will
396 -- be stored on the new content zones. See method for details.
397 --
398 -- 2. Map of old catalog IDs to the supplier attribute action flag values
399 -- that will be stored on the new content zones. See method for details.
400 --
401 -- 3. List of all the item sources that require a map due to the parent_zone_id
402 -- concept when dealing with punchout catalogs downloaded from exchange.
403 --
404 -- 4. Populating the MO_OPERATING_UNIT profile ID and value for site
405 --
406 -- 5. Populating the POR_APPROVED_PRICING_ONLY profile value at SITE;
407 -- user level values cannot be migrated
408 --
409 PROCEDURE populate_global_data
410 IS
411 l_api_name CONSTANT VARCHAR2(30) := 'populate_global_data';
412 l_err_loc PLS_INTEGER;
413 BEGIN
414
415 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
416 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
417 END IF;
418
419 l_err_loc := 0;
420
421 populate_store_security_flags();
422 l_err_loc := 100;
423
424 populate_catalog_supplr_flags();
425 l_err_loc := 200;
426
427 populate_operator_id_list();
428 l_err_loc := 300;
429
430 -- get the MO:Operating Unit profile ID and value at site level
431 SELECT p.profile_option_id, v.profile_option_value
432 INTO g_ou_profile_id, g_site_ou_profile_value
433 FROM fnd_profile_options p, fnd_profile_option_values v
434 WHERE p.profile_option_name = 'ORG_ID'
435 AND p.profile_option_id = v.profile_option_id(+)
436 AND p.application_id = v.application_id
437 AND v.level_id(+) = 10001;
438 l_err_loc := 400;
439
440 -- get the POR:Approved Pricing Only profile value at site level
441 SELECT nvl(v.profile_option_value, 'N')
442 INTO g_site_approved_pricing
443 FROM fnd_profile_options p, fnd_profile_option_values v
444 WHERE p.application_id = 178
445 AND p.profile_option_name = 'POR_APPROVED_PRICING_ONLY'
446 AND p.profile_option_id = v.profile_option_id
447 AND p.application_id = v.application_id
448 AND v.level_id = 10001;
449 l_err_loc := 500;
450
451 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
453 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
454 'OU Profile ID='||g_ou_profile_id||', val(site)='||
455 g_site_ou_profile_value||', Approved Pricing Profile val(site)='
456 ||g_site_approved_pricing);
457 END IF;
458 l_err_loc := 600;
459
460 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
461 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
462 END IF;
463
464 EXCEPTION
465 WHEN OTHERS THEN
466 RAISE_APPLICATION_ERROR
467 (-20000,
468 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.populate_global_data(' ||
469 l_err_loc || '), ' || SQLERRM);
470
471 END populate_global_data;
472
473
474 -- SEED-DATA RELATED HELPERS
475 ----------------------------
476
477 -- Adds the seeded "All Local Content" content zone to the seeded Main Store.
478 -- This is not handled by the .ldt which does just the data loading, so this
479 -- is done here for new installations. For upgrades, this is handled via
480 -- the main content security upgrade code path.
481 --
482 PROCEDURE add_main_store_content_link
483 IS
484 l_api_name CONSTANT VARCHAR2(30) := 'add_main_store_content_link';
485 l_err_loc PLS_INTEGER;
486 BEGIN
487
488 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
489 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
490 END IF;
491
492 l_err_loc := 0;
493
494 -- secure the seeded "All Local" content zone with the Main Store;
495 -- this is not handled by the .ldt, so it's done here
496 INSERT INTO icx_cat_store_contents
497 (store_id, content_id, content_type, sequence, display_always_flag,
498 created_by, creation_date, last_updated_by, last_update_date,
499 last_update_login)
500 SELECT 1, 1, 'CONTENT_ZONE', 1, NULL, fnd_global.user_id, sysdate,
501 fnd_global.user_id, sysdate, fnd_global.login_id
502 FROM dual;
503 l_err_loc := 100;
504
505 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
506 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
507 END IF;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 RAISE_APPLICATION_ERROR
512 (-20000,
513 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_main_store_content_link(' ||
514 l_err_loc || '), ' || SQLERRM);
515
516 END add_main_store_content_link;
517
518
519 -- Adds the seeded default Smart Form to the seeded Non-Catalog Request tab.
520 -- This is not handled by the .ldt which does just the data loading, so this
521 -- is done here.
522 --
523 PROCEDURE add_smart_form_content_link
524 IS
525 l_api_name CONSTANT VARCHAR2(30) := 'add_smart_form_content_link';
526 l_err_loc PLS_INTEGER;
527 BEGIN
528
529 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
530 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
531 END IF;
532
533 l_err_loc := 0;
534
535 INSERT INTO icx_cat_store_contents
536 (store_id, content_id, content_type, sequence, display_always_flag,
537 created_by, creation_date, last_updated_by, last_update_date,
538 last_update_login)
539 SELECT 2, 10000000, 'SMART_FORM', 1, 'N', fnd_global.user_id, sysdate,
540 fnd_global.user_id, sysdate, fnd_global.login_id
541 FROM dual;
542 l_err_loc := 100;
543
544 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
545 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
546 END IF;
547
548 EXCEPTION
549 WHEN OTHERS THEN
550 RAISE_APPLICATION_ERROR
551 (-20000,
552 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_smart_form_content_link(' ||
553 l_err_loc || '), ' || SQLERRM);
554
555 END add_smart_form_content_link;
556
557
558 -- Migrates any values for seeded data in previous releases. This handles
559 -- everything that can be done prior to processing the upgrade. Additional
560 -- steps that can only occur after upgrade will be handled in postprocessing.
561 -- In preprocessing, steps will include:
562 --
563 -- 1. The Contractor Catalog -- copy translations over to the Contractor Zone.
564 --
565 -- 2. The Main Store (11.5.8 Migration) -- if there are no local catalogs
566 -- (which are seeded in 11.5.9 and 11.5.10 and cannot be deleted), then
567 -- this is a 11.5.8 migration to R12.
568 --
569 -- In this case, the upgrade is a bit complicated:
570 -- There is no Main Store, but the previous 11.5.9/11.5.10 upgrade script
571 -- will forcibly migrate any old 11.5.8 item sources and add the
572 -- relationship in icx_cat_store_catalogs to a "phantom" main store that
573 -- will not exist. To properly complete this process, this script will
574 -- assume the new seeded R12 is the properly "migrated" store to which
575 -- the item sources will be moved over to content zones (as-is in the
576 -- rest of the script).
577 --
578 -- 3. The Main Store (11i) -- copy sequence and translations over to the seeded
579 -- R12 Main Store. All catalogs and item sources will be migrated normally.
580 -- Delete the seeded store if the main store was previously deleted in 11i.
581 --
582 -- 4. The NonCatalog Store -- again, copy sequence and translations over to
583 -- the R12 Non-Catalog Store (subtab). Templates will be migrated normally.
584 --
585 -- 5. No migration is needed for the default Smart Form, as it is identical
586 -- to the one seeded in 11i, except for the name change. This name change
587 -- will not be reflected for customers upgrading as they will retain the
588 -- the old 11i name or whatever name they have used to rename it.
589 --
590 -- 6. "All Local Content" catalog in 11.5.10 will be migrated as any other
591 -- local catalog. The only exception is that for the main store, this
592 -- catalog (if there exists a zone that has no supplier nor category
593 -- restrictions) will be promoted to it's seeded ID of 1. In all cases
594 -- (except in new installations which has already been handled first), the
595 -- new R12 seeded content zone will be removed, as it is no longer relevant.
596 --
597 PROCEDURE seed_data_preprocessing
598 IS
599 l_has_main_store NUMBER;
600 l_api_name CONSTANT VARCHAR2(30) := 'seed_data_preprocessing';
601 l_err_loc PLS_INTEGER;
602 BEGIN
603
604 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
605 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
606 END IF;
607
608 l_err_loc := 0;
609
610 -- migrate translated values for contractor; as nothing can be changed on
611 -- the base table), the easiest way to do migrate is remove the seeded tl
612 -- rows and replace them with the existing translated contractor rows.
613 -- per .ldt file, contractor catalog was 10,000,001 in 11.5.10 and 2 in R12
614
615 DELETE FROM icx_cat_content_zones_tl
616 WHERE zone_id = 2
617 AND EXISTS (SELECT 1
618 FROM icx_por_item_sources_tl
619 WHERE item_source_id = 10000001);
620 l_err_loc := 100;
621
622 INSERT INTO icx_cat_content_zones_tl
623 (zone_id, language, source_lang, name, description,
624 created_by, creation_date, last_updated_by, last_update_date,
625 last_update_login)
626 SELECT 2, language, source_lang, item_source_name, description,
627 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
628 fnd_global.login_id
629 FROM icx_por_item_sources_tl
630 WHERE item_source_id = 10000001;
631 l_err_loc := 200;
632
633 -- check for the existence of local catalogs (meaning 11.5.9 and later)
634 IF (exists_local_catalogs()) THEN
635
636 g_is_pre_1159_upgrade := FALSE;
637
638 -- migrate seeded main store (if exists) and non-catalog stores;
639 -- if the main store does not exist, remove the newly seeded r12 one;
640 IF (exists_old_seeded_main_store()) THEN
641
642 -- per .ldt file, the main store ID was 0 in 11.5.10 and 1 in R12
643 migrate_seeded_store(0, 1);
644 l_err_loc := 300;
645
646 -- add the relationship from old to new stores in the map so that any
647 -- catalogs and item sources will be migrated properly
648 g_stores_map(0) := 1;
649 l_err_loc := 400;
650
651 ELSE -- main store does not exist in the 11.5.10 table
652
653 -- the main store has been deleted in 11i, so delete the new seeded r12
654 -- main store in this case (as it doesn't really make sense to add
655 -- something the user didn't want before); the user can easily recreate
656 -- a main store if desired.
657 DELETE FROM icx_cat_shop_stores_b
658 WHERE store_id = 1;
659 l_err_loc := 600;
660
661 DELETE FROM icx_cat_shop_stores_tl
662 WHERE store_id = 1;
663 l_err_loc := 700;
664
665 END IF;
666
667 -- functionally, for post 11.5.8 upgrade-scenarios, the seeded R12
668 -- "All Local Content" zone should be removed; it might be another
669 -- appropriate zone is promoted later on (see postprocessing step)
670 remove_R12_seeded_all_local();
671 l_err_loc := 750;
672
673 ELSE -- this is now 11.5.8 or before migration, so the R12 main store
674 -- assumes the position of the migrated main store
675
676 g_is_pre_1159_upgrade := TRUE;
677
678 -- add the relationship as if the old Main Store was migrated so that any
679 -- 11.5.8 and prior item sources will be migrated properly
680 g_stores_map(0) := 1;
681 l_err_loc := 800;
682
683 -- also store the security flag as 'ALL_USERS' by default for this case
684 g_store_security_flags(0) := 'ALL_USERS';
685 l_err_loc := 900;
686
687 -- also, special-case handling of the "All Local Content"
688 migrate_all_local_pre_1159();
689 l_err_loc := 950;
690
691 END IF;
692
693 -- as the Non-Catalog store cannot be deleted in 11.5.10, no need to check
694 -- if it exists like the Main Store; just migrate it.
695 -- per .ldt file, the NonCat Store ID was 10,000,000 in 11.5.10 and 2 in R12
696 migrate_seeded_store(10000000, 2);
697 l_err_loc := 1000;
698
699 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
701 END IF;
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 RAISE_APPLICATION_ERROR
706 (-20000,
707 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.seed_data_preprocessing(' ||
708 l_err_loc || '), ' || SQLERRM);
709
710 END seed_data_preprocessing;
711
712
713 -- Determines if the old seeded main store still remains.
714 --
715 -- @return TRUE if so, FALSE if not
716 --
717 FUNCTION exists_old_seeded_main_store
718 RETURN BOOLEAN
719 IS
720 l_has_main_store NUMBER;
721 l_api_name CONSTANT VARCHAR2(30) := 'exists_old_seeded_main_store';
722 l_err_loc PLS_INTEGER;
723 BEGIN
724
725 l_err_loc := 0;
726
727 BEGIN
728
729 SELECT 1
730 INTO l_has_main_store
731 FROM icx_cat_stores_b
732 WHERE store_id = 0;
733
734 l_err_loc := 100;
735
736 EXCEPTION
737 WHEN no_data_found THEN
738 l_has_main_store := -1;
739 l_err_loc := 200;
740 END;
741
742 l_err_loc := 300;
743
744 -- log status of the old seeded store pre-R12
745 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
746 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
747 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
748 'The old seeded Main Store status (1=>EXISTS) = '||l_has_main_store);
749 END IF;
750
751 RETURN (l_has_main_store = 1);
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 RAISE_APPLICATION_ERROR
756 (-20000,
757 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.exists_old_seeded_main_store('
758 || l_err_loc || '), ' || SQLERRM);
759
760 END exists_old_seeded_main_store;
761
762
763 -- Migrates values for seeded stores from previous releases. This includes
764 -- only the sequence number for the base table; for the tl rows, first
765 -- all seeded rows are removed and updated with the existing old rows.
766 --
767 -- @param p_old_id The ID of the seeded store in 11.5.10.
768 -- @param p_new_id The ID of the seeded store in R12.
769 --
770 PROCEDURE migrate_seeded_store
771 (
772 p_old_id NUMBER,
773 p_new_id NUMBER
774 )
775 IS
776 l_api_name CONSTANT VARCHAR2(30) := 'migrate_seeded_store';
777 l_err_loc PLS_INTEGER;
778 BEGIN
779
780 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
781 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
782 'p_old_id='||p_old_id||';p_new_id='||p_new_id);
783 END IF;
784
785 l_err_loc := 0;
786
787 UPDATE icx_cat_shop_stores_b
788 SET sequence =
789 (SELECT sequence_number FROM icx_cat_stores_b
790 WHERE store_id = p_old_id)
791 WHERE store_id = p_new_id;
792 l_err_loc := 100;
793
794 DELETE FROM icx_cat_shop_stores_tl
795 WHERE store_id = p_new_id
796 AND EXISTS (SELECT 1 FROM icx_cat_stores_tl WHERE store_id = p_old_id);
797 l_err_loc := 200;
798
799 INSERT INTO icx_cat_shop_stores_tl
800 (store_id, language, source_lang, name, description, long_description,
801 image, created_by, creation_date, last_updated_by, last_update_date,
802 last_update_login)
803 SELECT p_new_id, language, source_lang, store_name,
804 short_description, long_description, image_location,
805 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
806 fnd_global.login_id
807 FROM icx_cat_stores_tl
808 WHERE store_id = p_old_id;
809 l_err_loc := 300;
810
811 -- add the relationship to the stores map
812 g_stores_map(p_old_id) := p_new_id;
813 l_err_loc := 400;
814
815 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
816 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
817 END IF;
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 RAISE_APPLICATION_ERROR
822 (-20000,
823 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_seeded_store(' ||
824 l_err_loc || '), ' || SQLERRM);
825
826 END migrate_seeded_store;
827
828
829 -- Removes the "All Local Content" that was seeded in R12 because there were
830 -- "All Local Content" catalogs in 11.5.10 that were migrated to R12.
831 --
832 PROCEDURE remove_R12_seeded_all_local
833 IS
834 l_api_name CONSTANT VARCHAR2(30) := 'remove_R12_seeded_all_local';
835 l_err_loc PLS_INTEGER;
836 BEGIN
837
838 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
839 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
840 END IF;
841
842 l_err_loc := 0;
843
844 -- remove the R12 seeded zone data
845 DELETE FROM icx_cat_content_zones_b
846 WHERE zone_id = 1;
847 l_err_loc := 100;
848
849 DELETE FROM icx_cat_content_zones_tl
850 WHERE zone_id = 1;
851 l_err_loc := 200;
852
853 -- there are nothing seeded into icx_cat_secure_contents for the seeded
854 -- content zone, so nothing to handle in that table
855
856 -- remove all links to stores, if any
857 DELETE FROM icx_cat_store_contents
858 WHERE content_id = 1;
859 l_err_loc := 300;
860
861 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
862 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
863 END IF;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 RAISE_APPLICATION_ERROR
868 (-20000,
869 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.remove_R12_seeded_all_local('
870 || l_err_loc || '), ' || SQLERRM);
871
872 END remove_R12_seeded_all_local;
873
874
875 -- For pre-11.5.9 upgrades, the "All Local Content" catalog must be handled
876 -- specially, as it is not seeded during R12. The seeded R12 "All Local
877 -- Content" is kept, but must be duplicated with realms, if needed.
878 --
879 PROCEDURE migrate_all_local_pre_1159
880 IS
881 l_api_name CONSTANT VARCHAR2(30) := 'migrate_all_local_pre_1159';
882 l_err_loc PLS_INTEGER;
883 BEGIN
884
885 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
886 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
887 END IF;
888
889 l_err_loc := 0;
890
891 -- determine all the responsibilities with category realms
892 g_resp_with_category_realms := get_all_resps_with_realms('RT_CATEGORY_ID');
893 l_err_loc := 100;
894
895 -- in the case with no realms, the newly seeded R12 "All Local Content"
896 -- can be used, and nothing is done here
897 IF (g_resp_with_category_realms.COUNT = 0) THEN
898
899 -- nothing needs to be migrated, but the link must be added to the
900 -- new main store, as if this were a new installation
901 add_main_store_content_link();
902 l_err_loc := 150;
903
904 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
905 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
906 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
907 'Pre-11.5.9 upgrade found no Category Realms being used.');
908 END IF;
909
910 l_err_loc := 200;
911
912 ELSE -- populate all responsibilties without realms as this will be
913 -- needed to migrate local catalogs with realms consideration
914
915 g_resp_without_category_realms :=
916 get_all_resps_without_realms('RT_CATEGORY_ID');
917 l_err_loc := 300;
918
919 -- consider realms, and migrate the "All Local Content"
920 migrate_catalog_with_realms(0, 0, g_resp_with_category_realms);
921 l_err_loc := 400;
922
923 -- after this, safely remove the "original" one as it as been migrated
924 -- with realms properly
925 remove_R12_seeded_all_local();
926 l_err_loc := 500;
927
928 END IF;
929
930 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
931 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
932 END IF;
933
934 EXCEPTION
935 WHEN OTHERS THEN
936 RAISE_APPLICATION_ERROR
937 (-20000,
938 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_all_local_pre_1159('
939 || l_err_loc || '), ' || SQLERRM);
940
941 END migrate_all_local_pre_1159;
942
943
944 -- Handles seed data processing that requires the upgrade to happen first.
945 -- Currently, this involves only one step -- promoting a migrated
946 -- "All Local Content" catalog to the seeded zone of ID 1 if it is
947 -- substantially "identical" to the R12 seeded one found in the Main Store.
948 -- "Identical" here means there are no catalog or supplier restrictions
949 -- for the migrated zone.
950 --
951 PROCEDURE seed_data_postprocessing
952 IS
953 l_api_name CONSTANT VARCHAR2(30) := 'seed_data_postprocessing';
954 l_err_loc PLS_INTEGER;
955 BEGIN
956
957 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
958 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
959 END IF;
960
961 l_err_loc := 0;
962
963 -- promote an existing zone to the "All Local Content" seeded zone if
964 -- the "All Local Content" catalog was migrated from 11.5.10;
965 -- this method will do nothing in the case there is no suitable
966 -- content zone to promote to the "seeded" one
967 promote_all_local_to_seeded();
968 l_err_loc := 100;
969
970 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
972 END IF;
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 RAISE_APPLICATION_ERROR
977 (-20000,
978 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.seed_data_postprocessing(' ||
979 l_err_loc || '), ' || SQLERRM);
980
981 END seed_data_postprocessing;
982
983
984 -- Promotes the migrated "All Local Content" content zone from the main store
985 -- to "seeded" status by switching the IDs, if one exists that also has no
986 -- category restrictions (as in the "All Local Content" catalog).
987 -- If there is no qualifying content, then this method does a noop.
988 --
989 PROCEDURE promote_all_local_to_seeded
990 IS
991 l_promoted_zone_id NUMBER;
992 l_api_name CONSTANT VARCHAR2(30) := 'promote_all_local_to_seeded';
993 l_err_loc PLS_INTEGER;
994 BEGIN
995
996 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
997 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
998 END IF;
999
1000 l_err_loc := 0;
1001
1002 -- promote the content zone to "seeded" only if it's in the main store
1003 -- and is not secured by any category (realms)
1004 BEGIN
1005
1006 SELECT zone_id
1007 INTO l_promoted_zone_id
1008 FROM icx_cat_content_zones_b z, icx_cat_store_contents c
1009 WHERE z.supplier_attribute_action_flag = 'INCLUDE_ALL'
1010 AND z.category_attribute_action_flag = 'INCLUDE_ALL'
1011 AND z.zone_id = c.content_id
1012 AND c.store_id = 1;
1013 l_err_loc := 100;
1014
1015 -- update the promoted zone to zone ID = 1 (seeded ID)
1016 UPDATE icx_cat_content_zones_b
1017 SET zone_id = 1
1018 WHERE zone_id = l_promoted_zone_id;
1019 l_err_loc := 200;
1020
1021 UPDATE icx_cat_content_zones_tl
1022 SET zone_id = 1
1023 WHERE zone_id = l_promoted_zone_id;
1024 l_err_loc := 300;
1025
1026 UPDATE icx_cat_secure_contents
1027 SET content_id = 1
1028 WHERE content_id = l_promoted_zone_id;
1029 l_err_loc := 400;
1030
1031 -- update the store-contents association by deleting the old one, and
1032 -- replacing the new one with the new seeded ID of 1 (as there may
1033 -- be other valuable information saved in this relationship row that
1034 -- should be saved, for instance, sequence).
1035 UPDATE icx_cat_store_contents
1036 SET content_id = 1
1037 WHERE content_id = l_promoted_zone_id;
1038 l_err_loc := 500;
1039
1040 EXCEPTION
1041 WHEN no_data_found THEN
1042
1043 NULL; -- nothing needs to be done
1044 l_err_loc := 600;
1045
1046 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1048 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1049 'Did not find an All Local Content to migrate to seeded status.');
1050 END IF;
1051
1052 END;
1053
1054 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1055 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 RAISE_APPLICATION_ERROR
1061 (-20000,
1062 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.promote_all_local_to_seeded('
1063 || l_err_loc || '), ' || SQLERRM);
1064
1065 END promote_all_local_to_seeded;
1066
1067
1068 -- HIGH-LEVEL MIGRATE METHODS
1069 -----------------------------
1070
1071 -- Attempts to migrate all local catalogs without category realms. This
1072 -- migration is sucessfully only in the case where there are no responsibilties
1073 -- with category realms on the installation, or of course, when there are
1074 -- no local catalogs to migrate in the first place. In the former, all
1075 -- local catalogs are migrated without category realms and TRUE is returned.
1076 -- Otherwise, FALSE is returned if category realms must be considered.
1077 --
1078 -- @return TRUE if local catalogs were sucessfully migrated, FALSE if realms
1079 -- must be considered (so this is deferred until a later method).
1080 --
1081 FUNCTION try_migrate_catalogs_no_realms
1082 RETURN BOOLEAN
1083 IS
1084 l_local_catalogs_migrated BOOLEAN;
1085 l_api_name CONSTANT VARCHAR2(30) := 'try_migrate_catalogs_no_realms';
1086 l_err_loc PLS_INTEGER;
1087 BEGIN
1088
1089 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1090 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1091 END IF;
1092
1093 l_err_loc := 0;
1094 l_local_catalogs_migrated := FALSE;
1095
1096 IF (exists_local_catalogs()) THEN
1097
1098 g_resp_with_category_realms := get_all_resps_with_realms('RT_CATEGORY_ID');
1099 l_err_loc := 100;
1100
1101 -- proceed with the "no realms" migration if there are no responsibilities
1102 -- with category realms (that is, the count is 0)
1103 IF (g_resp_with_category_realms.COUNT = 0) THEN
1104
1105 migrate_all_catalogs_no_realms();
1106 l_err_loc := 200;
1107
1108 l_local_catalogs_migrated := TRUE;
1109
1110 ELSE -- populate all responsibilties without realms as this will be
1111 -- needed to migrate local catalogs with realms consideration
1112
1113 g_resp_without_category_realms :=
1114 get_all_resps_without_realms('RT_CATEGORY_ID');
1115 l_err_loc := 300;
1116
1117 END IF;
1118
1119 ELSE -- no local catalogs to migrate
1120
1121 l_local_catalogs_migrated := TRUE;
1122 l_err_loc := 400;
1123
1124 END IF;
1125
1126 l_err_loc := 500;
1127
1128 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 IF (l_local_catalogs_migrated) THEN
1130 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1131 'Local Catalogs MIGRATED successfully.');
1132 ELSE
1133 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1134 'Local Catalogs have NOT been migrated.');
1135 END IF;
1136 END IF;
1137
1138 RETURN l_local_catalogs_migrated;
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 RAISE_APPLICATION_ERROR
1143 (-20000,
1144 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.try_migrate_catalogs_no_realms('
1145 || l_err_loc || '), ' || SQLERRM);
1146
1147 END try_migrate_catalogs_no_realms;
1148
1149
1150 -- Attempts to migrate all item sources without item source realms. This
1151 -- migration is sucessfully only in the case where there are no responsibilties
1152 -- with item source realms on the installation, or of course, when there are
1153 -- no item sources to migrate in the first place. In the former, all
1154 -- item sources are migrated without item source realms and TRUE is returned.
1155 -- Otherwise, FALSE is returned if item source realms must be considered.
1156 --
1157 -- @return TRUE if item sources were sucessfully migrated, FALSE if realms
1158 -- must be considered (so this is deferred until a later method).
1159 --
1160 FUNCTION try_migrate_isrcs_no_realms
1161 RETURN BOOLEAN
1162 IS
1163 l_item_sources_migrated BOOLEAN;
1164 l_api_name CONSTANT VARCHAR2(30) := 'try_migrate_isrcs_no_realms';
1165 l_err_loc PLS_INTEGER;
1166 BEGIN
1167
1168 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1169 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1170 END IF;
1171
1172 l_err_loc := 0;
1173 l_item_sources_migrated := FALSE;
1174
1175 IF (exists_item_sources()) THEN
1176
1177 g_resp_with_isrc_realms :=
1178 get_all_resps_with_realms('ICX_POR_ITEM_SOURCE_ID');
1179 l_err_loc := 100;
1180
1181 -- again, only if there are no responsibilities with item sources realms,
1182 -- then proceed with the migration without consideration to realms
1183 IF (g_resp_with_isrc_realms.COUNT = 0) THEN
1184
1185 migrate_all_isrcs_no_realms();
1186 l_err_loc := 200;
1187
1188 l_item_sources_migrated := TRUE;
1189
1190 ELSE -- the responsibilties without item source realms AND
1191 -- a mapping of item sources to which responsibilties can
1192 -- access them via their realms MUST BE POPULATED
1193
1194 g_resp_without_isrc_realms :=
1195 get_all_resps_without_realms('ICX_POR_ITEM_SOURCE_ID');
1196 l_err_loc := 300;
1197
1198 populate_isrcs_to_resp_map();
1199 l_err_loc := 400;
1200
1201 END IF;
1202
1203 ELSE -- no item sources to migrate
1204
1205 l_item_sources_migrated := TRUE;
1206 l_err_loc := 500;
1207
1208 END IF;
1209
1210 l_err_loc := 600;
1211
1212 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1213 IF (l_item_sources_migrated) THEN
1214 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1215 'Item Sources MIGRATED successfully.');
1216 ELSE
1217 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1218 'Item Sources have NOT been migrated.');
1219 END IF;
1220 END IF;
1221
1222 RETURN l_item_sources_migrated;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 RAISE_APPLICATION_ERROR
1227 (-20000,
1228 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.try_migrate_isrcs_no_realms('
1229 || l_err_loc || '), ' || SQLERRM);
1230
1231 END try_migrate_isrcs_no_realms;
1232
1233
1234 -- Migrates all local catalogs within the customer installation when
1235 -- category realms do not need to be considered. All catalogs, their
1236 -- corresponding stores, and a new content zone sequence number are
1237 -- selected and then passed to a migrate catalogs helper method.
1238 --
1239 PROCEDURE migrate_all_catalogs_no_realms
1240 IS
1241 l_catalog_ids ICX_TBL_NUMBER;
1242 l_store_ids ICX_TBL_NUMBER;
1243 l_new_zone_ids ICX_TBL_NUMBER;
1244
1245 l_api_name CONSTANT VARCHAR2(30) := 'migrate_all_catalogs_no_realms';
1246 l_err_loc PLS_INTEGER;
1247 BEGIN
1248
1249 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1250 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1251 END IF;
1252
1253 l_err_loc := 0;
1254
1255 SELECT sources.item_source_id, sc.store_id, ICX_CAT_CONTENT_ZONES_S.NEXTVAL
1256 BULK COLLECT INTO l_catalog_ids, l_store_ids, l_new_zone_ids
1257 FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
1258 WHERE sources.type = 'LOCAL'
1259 AND sources.item_source_id = sc.item_source_id(+);
1260 l_err_loc := 100;
1261
1262 migrate_catalogs_no_realms(l_catalog_ids, l_store_ids, l_new_zone_ids);
1263 l_err_loc := 200;
1264
1265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1266 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1267 END IF;
1268
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 RAISE_APPLICATION_ERROR
1272 (-20000,
1273 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_all_catalogs_no_realms('
1274 || l_err_loc || '), ' || SQLERRM);
1275
1276 END migrate_all_catalogs_no_realms;
1277
1278
1279 -- Migrates all item sources within the customer installation when
1280 -- item source realms do not need to be considered. All item sources,
1281 -- their corresponding stores, and a new content zone sequence number
1282 -- are selected and then passed to a migrate item sources helper method.
1283 -- The contractor item source is ignored, as it's a seeded element that's
1284 -- handled separately.
1285 --
1286 PROCEDURE migrate_all_isrcs_no_realms
1287 IS
1288 l_item_source_ids ICX_TBL_NUMBER;
1289 l_store_ids ICX_TBL_NUMBER;
1290 l_new_zone_ids ICX_TBL_NUMBER;
1291
1292 l_api_name CONSTANT VARCHAR2(30) := 'migrate_all_isrcs_no_realms';
1293 l_err_loc PLS_INTEGER;
1294 BEGIN
1295
1296 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1297 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1298 END IF;
1299
1300 l_err_loc := 0;
1301
1302 SELECT sources.item_source_id, sc.store_id, ICX_CAT_CONTENT_ZONES_S.NEXTVAL
1303 BULK COLLECT INTO l_item_source_ids, l_store_ids, l_new_zone_ids
1304 FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
1305 WHERE sources.type IN ('EXTERNAL', 'DISTSRCH', 'INFO')
1306 AND sources.item_source_id = sc.item_source_id(+);
1307 l_err_loc := 100;
1308
1309 migrate_isrcs_no_realms(l_item_source_ids, l_store_ids, l_new_zone_ids);
1310 l_err_loc := 200;
1311
1312 -- after all item sources have been migrated, update any downloaded
1313 -- punchouts from exchange (post-processing)
1314 update_exchange_punchouts();
1315 l_err_loc := 300;
1316
1317 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1318 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1319 END IF;
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 RAISE_APPLICATION_ERROR
1324 (-20000,
1325 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_all_isrcs_no_realms('
1326 || l_err_loc || '), ' || SQLERRM);
1327
1328 END migrate_all_isrcs_no_realms;
1329
1330
1331 -- In the case where either category realms or item source realms (or both)
1332 -- need to be considered for migration, this wrapper method is called to
1333 -- oversee the migration. This method will check for category realms
1334 -- and item source realms when migrating local catalogs and item sources,
1335 -- respectively.
1336 --
1337 -- This method is fairly complicated, but the process is broken down as follows:
1338 --
1339 -- (1) Determine all the content that needs to be migrated (this may include
1340 -- local catalogs, item sources, or both -- depending on what had already
1341 -- been migrated without regards to realms).
1342 --
1343 -- (2) This content list is returned ordered by the store (including the
1344 -- possibility of NULL for content not included in a store).
1345 --
1346 -- (3) For each LOCAL content, determine if there are any responsibilties
1347 -- with category realms that must be considered. If there aren't any,
1348 -- then this local catalog can be migrated as if there were no realms
1349 -- to consider. This is not done immediately, but keep track of in
1350 -- a list that will migrated all at once (not unlike what is done if
1351 -- there were no responsibilities with category realms in the entire
1352 -- customer installation).
1353 --
1354 -- (4) If there are responsibilties with realms for this store (if this
1355 -- store is assigned to -2 or is NULL, then all responsibilties with
1356 -- category realms are considered in its place), then this local
1357 -- catalog will be migrated considering realms. This may be complicated
1358 -- so this is done immediately.
1359 --
1360 -- (5) Correspondingly, for each ITEM SOURCE (Info, Punchout, or Transparent
1361 -- Punchout) content, determine if there any responsibilities with
1362 -- item source realms that can access the old store this content belonged
1363 -- to. If there are none, then, like local catalogs, a running list
1364 -- is kept of item sources that can be migrated without considering
1365 -- item source realms. They are migrated all at once in the end.
1366 --
1367 -- (6) If there are responsibilties with item source realms for this store
1368 -- (using the same formula as the local catalogs when the store is
1369 -- assigned -2 or is NULL), then we will migrate this item source
1370 -- considering realms immediately.
1371 --
1372 -- (7) Lastly, check our running lists of catalogs or item sources that
1373 -- can be migrated without consideration of realms. If any do exist,
1374 -- migrate them in batch now.
1375 --
1376 PROCEDURE migrate_content_with_realms
1377 (
1378 p_catalogs_migrated IN BOOLEAN,
1379 p_item_sources_migrated IN BOOLEAN
1380 )
1381 IS
1382 -- stores all the component content data parts which to migrate
1383 l_old_content_ids ICX_TBL_NUMBER;
1384 l_content_types ICX_TBL_VARCHAR15;
1385 l_old_store_ids ICX_TBL_NUMBER;
1386 l_new_zone_ids ICX_TBL_NUMBER;
1387
1388 -- keeps track of the store the following list of responsilbities with and
1389 -- without realms are valid for; will only query once per store to help
1390 -- performance (realms queries are quite large)
1391 l_isrc_realms_list_for_store NUMBER;
1392 l_resp_with_catr_realms ICX_TBL_NUMBER;
1393 l_resp_with_isrc_realms ICX_TBL_NUMBER;
1394 l_resp_without_isrc_realms ICX_TBL_NUMBER;
1395
1396 -- keeps track of all the catalogs and item sources that can be migrated
1397 -- without realms consideration (they are all migrated in bulk)
1398 l_catalog_no_catr_realms_ids ICX_TBL_NUMBER;
1399 l_store_no_catr_realms_ids ICX_TBL_NUMBER;
1400 l_isrc_no_isrc_realms_ids ICX_TBL_NUMBER;
1401 l_store_no_isrc_realms_ids ICX_TBL_NUMBER;
1402
1403 l_api_name CONSTANT VARCHAR2(30) := 'migrate_content_with_realms';
1404 l_err_loc PLS_INTEGER;
1405
1406 BEGIN
1407
1408 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1409 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1410 END IF;
1411
1412 l_err_loc := 0;
1413
1414 -- depending on what's already been migrated, retrieves whatever
1415 -- left needs to be migrated while considering realms
1416 get_contents_to_migrate(p_catalogs_migrated, p_item_sources_migrated,
1417 l_old_content_ids, l_content_types, l_old_store_ids);
1418 l_err_loc := 100;
1419
1420 -- now, initialize the tables that keep track of any local catalogs or
1421 -- item sources that may still be migrated without regard to realms
1422 l_catalog_no_catr_realms_ids := ICX_TBL_NUMBER();
1423 l_store_no_catr_realms_ids := ICX_TBL_NUMBER();
1424 l_isrc_no_isrc_realms_ids := ICX_TBL_NUMBER();
1425 l_store_no_isrc_realms_ids := ICX_TBL_NUMBER();
1426
1427 l_err_loc := 200;
1428
1429 -- keep track of the current store (get_contents_to_migrate will return
1430 -- local catalogs/item sources to migrate ORDERED BY store); this is used
1431 -- to minimize calls to the large queries to find responsibilties with
1432 -- or without item source realms that can access any given store; NOTE,
1433 -- this doesn't help with category realms as there is only one local
1434 -- catalog per store anyways
1435 l_isrc_realms_list_for_store := -999;
1436
1437 -- loop through all catalogs and/or item sources to migrate
1438 FOR i IN 1..l_old_content_ids.COUNT LOOP
1439
1440 IF (l_content_types(i) = 'LOCAL') THEN
1441
1442 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1443 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1444 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1445 'Processing Local Catalog ... '||l_old_content_ids(i)||
1446 ' for Store '||l_old_store_ids(i)||' (is NULL if empty).');
1447 END IF;
1448
1449 -- for local catalogs, determine if there any responsibilities with
1450 -- category realms that can access this store
1451 l_resp_with_catr_realms :=
1452 get_resps_with_realms_store('RT_CATEGORY_ID', l_old_store_ids(i));
1453 l_err_loc := 300;
1454
1455 -- if there are none, then we can migrate this local catalog without
1456 -- consideration of category realms; we simply keep a running list
1457 -- here and will migrate all of them at the end
1458 IF (l_resp_with_catr_realms.COUNT = 0) THEN
1459
1460 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1461 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1462 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1463 'Local Catalog can be migrated WITHOUT realms.');
1464 END IF;
1465
1466 l_catalog_no_catr_realms_ids.extend;
1467 l_catalog_no_catr_realms_ids(l_catalog_no_catr_realms_ids.COUNT) :=
1468 l_old_content_ids(i);
1469
1470 l_store_no_catr_realms_ids.extend;
1471 l_store_no_catr_realms_ids(l_store_no_catr_realms_ids.COUNT) :=
1472 l_old_store_ids(i);
1473
1474 l_err_loc := 400;
1475
1476 ELSE -- there are responsibilities with realms that can access this store
1477
1478 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1480 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1481 'Local Catalog MUST be migrated WITH realms.');
1482 END IF;
1483
1484 -- migrate this individual local catalog with realms consideration
1485 migrate_catalog_with_realms(l_old_content_ids(i),
1486 l_old_store_ids(i),
1487 l_resp_with_catr_realms);
1488 l_err_loc := 500;
1489
1490 END IF; -- (l_resp_with_catr_realms.COUNT = 0)
1491
1492 ELSE -- for item sources (l_content_types(i) <> 'LOCAL')
1493
1494 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1495 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1496 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1497 'Processing Item Source ... '||l_old_content_ids(i)||
1498 ' for Store '||l_old_store_ids(i)||' (is NULL if empty).');
1499 END IF;
1500
1501 -- check to see if the list of realms already queried is still
1502 -- valid for this store (that is, we haven't changed stores yet)
1503 IF ((l_old_store_ids(i) IS NULL AND l_isrc_realms_list_for_store IS NULL)
1504 OR (l_old_store_ids(i) = l_isrc_realms_list_for_store)) THEN
1505
1506 -- it's ok to keep our cached lists; this is done in the REVERSE
1507 -- way because the other condition requires three conditions:
1508 -- (1) old store is NULL and list store is NOT NULL
1509 -- (2) old store is NOT NULL and list store IS NULL
1510 -- (3) they don't match
1511 -- PL/SQL has screwed up ideas of equalities with NULL
1512 NULL;
1513
1514 l_err_loc := 550;
1515
1516 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1517 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1518 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1519 'Processing Item Source ... same store, no need to re-query.');
1520 END IF;
1521
1522 ELSE
1523
1524 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1525 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1526 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1527 'Processing Item Source ... a new store, MUST re-query.');
1528 END IF;
1529
1530 l_err_loc := 600;
1531
1532 -- query up the realms and without realms list (can be reused),
1533 -- also, set the tracking variable
1534 l_resp_with_isrc_realms :=
1535 get_resps_with_realms_store('ICX_POR_ITEM_SOURCE_ID',
1536 l_old_store_ids(i));
1537 l_err_loc := 700;
1538
1539 l_resp_without_isrc_realms :=
1540 get_resps_without_realms_store('ICX_POR_ITEM_SOURCE_ID',
1541 l_old_store_ids(i));
1542 l_err_loc := 800;
1543
1544 l_isrc_realms_list_for_store := l_old_store_ids(i);
1545
1546 END IF;
1547
1548 -- like local catalogs, check if realms needs to be considered;
1549 -- if so, tack them to a running list, otherwise, migrate this
1550 -- one item source with item source realms consideration
1551 IF (l_resp_with_isrc_realms.COUNT = 0) THEN
1552
1553 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1554 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1555 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1556 'Item Source can be migrated WITHOUT realms.');
1557 END IF;
1558
1559 l_isrc_no_isrc_realms_ids.extend;
1560 l_isrc_no_isrc_realms_ids(l_isrc_no_isrc_realms_ids.COUNT) :=
1561 l_old_content_ids(i);
1562
1563 l_store_no_isrc_realms_ids.extend;
1564 l_store_no_isrc_realms_ids(l_store_no_isrc_realms_ids.COUNT) :=
1565 l_old_store_ids(i);
1566
1567 l_err_loc := 900;
1568
1569 ELSE -- there are responsibilities with realms that can access this store
1570
1571 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1572 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1573 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1574 'Item Source MUST be migrated WITH realms.');
1575 END IF;
1576
1577 migrate_isrc_with_realms(l_old_content_ids(i), l_old_store_ids(i),
1578 l_resp_with_isrc_realms,
1579 l_resp_without_isrc_realms);
1580
1581 l_err_loc := 1000;
1582
1583 END IF; -- (l_resp_with_isrc_realms.COUNT = 0)
1584
1585 END IF; -- check for content type
1586
1587 END LOOP;
1588
1589 -- at this point, migrate any local catalogs that are not affected
1590 -- by category realms, if any
1591 IF (l_catalog_no_catr_realms_ids.COUNT > 0) THEN
1592
1593 l_new_zone_ids := get_new_zone_ids(l_catalog_no_catr_realms_ids.COUNT);
1594
1595 migrate_catalogs_no_realms(l_catalog_no_catr_realms_ids,
1596 l_store_no_catr_realms_ids,
1597 l_new_zone_ids);
1598 l_err_loc := 1100;
1599
1600 ELSE -- skip and log
1601
1602 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1603 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1604 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1605 'Found no Local Catalogs that were migrate-able WITHOUT realms.');
1606 END IF;
1607
1608 END IF;
1609
1610 -- similarily, migrate any item sources that are not affected
1611 -- by item source realms, if any
1612 IF (l_isrc_no_isrc_realms_ids.COUNT > 0) THEN
1613
1614 l_new_zone_ids := get_new_zone_ids(l_isrc_no_isrc_realms_ids.COUNT);
1615
1616 migrate_isrcs_no_realms(l_isrc_no_isrc_realms_ids,
1617 l_store_no_isrc_realms_ids,
1618 l_new_zone_ids);
1619 l_err_loc := 1200;
1620
1621 ELSE -- skip and log
1622
1623 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1624 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1625 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1626 'Found no Item Sources that were migrate-able WITHOUT realms.');
1627 END IF;
1628
1629 END IF;
1630
1631 -- after all contents have been migrated, update any downloaded
1632 -- punchouts from exchange (post-processing)
1633 update_exchange_punchouts();
1634 l_err_loc := 1300;
1635
1636 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1637 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1638 END IF;
1639
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 RAISE_APPLICATION_ERROR
1643 (-20000,
1644 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_content_with_realms('
1645 || l_err_loc || '), ' || SQLERRM);
1646
1647 END migrate_content_with_realms;
1648
1649
1650 -- MIGRATE LOCAL CATALOGS AND ITEM SOURCES HELPERS
1651 --------------------------------------------------
1652
1653 -- Migrates all given local catalogs when category realms do not need to
1654 -- be considered for these content. This happens when the responsibilities
1655 -- that can access these catalogs themselves do not have category realms.
1656 -- This method is also used in the general case where category realms is
1657 -- not used on the customer installation.
1658 --
1659 -- @param p_catalog_ids The list of local catalogs to migrate.
1660 -- @param p_old_store_ids The list of corresponding old stores that the
1661 -- local catalogs belonged to.
1662 -- @param p_new_zone_ids The list of new content zone sequence numbers to
1663 -- be used when migrating these local catalogs.
1664 --
1665 PROCEDURE migrate_catalogs_no_realms
1666 (
1667 p_catalog_ids IN ICX_TBL_NUMBER,
1668 p_old_store_ids IN ICX_TBL_NUMBER,
1669 p_new_zone_ids IN ICX_TBL_NUMBER
1670 )
1671 IS
1672 l_security_flags ICX_TBL_VARCHAR20;
1673 l_supplier_flags ICX_TBL_VARCHAR20;
1674 l_r12_store_ids ICX_TBL_NUMBER;
1675
1676 l_api_name CONSTANT VARCHAR2(30) := 'migrate_catalogs_no_realms';
1677 l_err_loc PLS_INTEGER;
1678 BEGIN
1679
1680 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1681 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1682 END IF;
1683
1684 l_err_loc := 0;
1685
1686 -- create a corresponding list for each of the flags/ids needed
1687 l_security_flags := ICX_TBL_VARCHAR20();
1688 l_security_flags.extend(p_old_store_ids.COUNT);
1689
1690 l_supplier_flags := ICX_TBL_VARCHAR20();
1691 l_supplier_flags.extend(p_old_store_ids.COUNT);
1692
1693 l_r12_store_ids := ICX_TBL_NUMBER();
1694 l_r12_store_ids.extend(p_old_store_ids.COUNT);
1695
1696 l_err_loc := 100;
1697
1698 FOR i IN 1..p_old_store_ids.COUNT LOOP
1699
1700 l_supplier_flags(i) := g_catalog_supplier_flags(p_catalog_ids(i));
1701 l_err_loc := 200;
1702
1703 -- if the catalog does not belong to any store, keep NULL for the new store;
1704 -- also, security will default to ALL_USERS, as there are no restrictions
1705 -- on this non-existent store
1706 IF (p_old_store_ids(i) IS NULL) THEN
1707
1708 l_r12_store_ids(i) := NULL;
1709 l_security_flags(i) := 'ALL_USERS';
1710 l_err_loc := 300;
1711
1712 ELSE
1713
1714 l_r12_store_ids(i) := g_stores_map(p_old_store_ids(i));
1715 l_security_flags(i) := g_store_security_flags(p_old_store_ids(i));
1716 l_err_loc := 400;
1717
1718 END IF;
1719
1720 END LOOP;
1721
1722 -- once the list for new stores and security flags have been set, then
1723 -- create all the new zones, secure them by the corresponding store
1724 -- operating unit restrictions, and add the newly created zones to their
1725 -- corresponding stores
1726 create_local_zones(p_new_zone_ids, p_catalog_ids, l_security_flags,
1727 l_supplier_flags, 'INCLUDE_ALL');
1728 l_err_loc := 500;
1729
1730 secure_zones_by_store_orgs(p_new_zone_ids, p_old_store_ids, l_security_flags);
1731 l_err_loc := 600;
1732
1733 add_zones_to_stores(p_new_zone_ids, l_r12_store_ids,
1734 p_catalog_ids, p_old_store_ids);
1735 l_err_loc := 700;
1736
1737 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1738 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1739 END IF;
1740
1741 EXCEPTION
1742 WHEN OTHERS THEN
1743 RAISE_APPLICATION_ERROR
1744 (-20000,
1745 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_catalogs_no_realms(' ||
1746 l_err_loc || '), ' || SQLERRM);
1747
1748 END migrate_catalogs_no_realms;
1749
1750
1751 -- Migrates the one given local catalog with consideration to realms.
1752 -- Unlike the no realms case, this method will handle only one catalog at
1753 -- a time, to minimize complexity. The process is broken down as follows:
1754 --
1755 -- (1) Eliminate any responsibililties with category realms that do not
1756 -- actually have any realms values attached to them.
1757 --
1758 -- (2) If there are any responsibilities left, create a copy of the local
1759 -- catalog for each. Then add the category restrictions that are
1760 -- particular to each responsibility. Lastly, secure this content zone
1761 -- by that particular responsibility.
1762 --
1763 -- (3) For all responsibilities WITHOUT category realms that can access
1764 -- this store (if any), create another copy of the local content.
1765 -- This copy will not be restricted by categories. Secure this by
1766 -- all such responsibilities without category realms.
1767 --
1768 -- @param p_catalog_id The local catalog to migrate.
1769 -- @param p_old_store_id The corresponding old store of the local catalog.
1770 -- @param p_resp_with_realms_ids The responsibilities with catalog realms
1771 -- that can access the old store.
1772 --
1773 PROCEDURE migrate_catalog_with_realms
1774 (
1775 p_catalog_id IN NUMBER,
1776 p_old_store_id IN NUMBER,
1777 p_resp_with_realms_ids IN ICX_TBL_NUMBER
1778 )
1779 IS
1780 l_new_zone_ids ICX_TBL_NUMBER;
1781 l_resp_with_realm_values_ids ICX_TBL_NUMBER;
1782 l_resp_without_realms_ids ICX_TBL_NUMBER;
1783
1784 l_api_name CONSTANT VARCHAR2(30) := 'migrate_catalog_with_realms';
1785 l_err_loc PLS_INTEGER;
1786 BEGIN
1787
1788 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1789 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1790 END IF;
1791
1792 l_err_loc := 0;
1793
1794 -- first filter out any responsibilities that do not have any category
1795 -- realm values; no content zones will be created for them, as they
1796 -- cannot access any categories by definition of realms
1797 l_resp_with_realm_values_ids :=
1798 filter_out_no_category_resps(p_resp_with_realms_ids);
1799 l_err_loc := 100;
1800
1801 -- if there any responsibilities left, migrate and secure by them
1802 IF (l_resp_with_realm_values_ids.COUNT > 0) THEN
1803
1804 -- create one for each responsibility with category realm values
1805 l_new_zone_ids := get_new_zone_ids(l_resp_with_realm_values_ids.COUNT);
1806 l_err_loc := 200;
1807
1808 -- for each, create a corresponding local zone;
1809 -- for pre 11.5.9 upgrade, the content zone must be created specially
1810 -- from the new seeded R12 "All Local Content" zone
1811 IF (g_is_pre_1159_upgrade) THEN
1812
1813 create_local_all_content_zones(l_new_zone_ids, 'INCLUDE');
1814 l_err_loc := 300;
1815
1816 ELSE
1817
1818 create_local_zones(l_new_zone_ids, p_catalog_id, 'RESP_SECURED',
1819 g_catalog_supplier_flags(p_catalog_id), 'INCLUDE');
1820 l_err_loc := 350;
1821
1822 END IF;
1823
1824 -- each responsibility will have a set of categories which they can
1825 -- access; secure the local zone by these categories
1826 add_resp_categories_to_zone(l_new_zone_ids, l_resp_with_realm_values_ids);
1827 l_err_loc := 400;
1828
1829 -- secure each zone to its corresponding responsibility
1830 secure_zones_by_resps(l_new_zone_ids, l_resp_with_realm_values_ids);
1831 l_err_loc := 500;
1832
1833 -- only add the zones to the store if the original catalog was in a store
1834 IF (p_old_store_id IS NOT NULL) THEN
1835
1836 add_zones_to_store(l_new_zone_ids, g_stores_map(p_old_store_id),
1837 p_catalog_id, p_old_store_id);
1838 l_err_loc := 600;
1839
1840 ELSE -- skip and log
1841
1842 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1843 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1844 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1845 'Skipping adding zones migrated from Local Catalog '||p_catalog_id||
1846 ' to any R12 Stores because it did not belong to any Old Stores.');
1847 END IF;
1848
1849 END IF;
1850
1851 ELSE -- skip and log
1852
1853 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1854 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1855 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1856 'Skipping creating category-restricted zones from Local Catalog '||
1857 p_catalog_id||' in Old Store '||p_old_store_id||
1858 ' as there are no resps with realm values that can access it.');
1859 END IF;
1860
1861 END IF;
1862
1863 -- now, handle all the responsibilties without category realms for this
1864 -- store; they should, by default, be able to access this local catalog
1865 -- without any category restrictions
1866 l_resp_without_realms_ids :=
1867 get_resps_without_realms_store('RT_CATEGORY_ID', p_old_store_id);
1868 l_err_loc := 700;
1869
1870 -- of course, create the zone only if these responsibilities exist
1871 IF (l_resp_without_realms_ids.COUNT > 0) THEN
1872
1873 -- only one zone is needed for all these no realms responsibilities
1874 l_new_zone_ids := get_new_zone_ids(1);
1875 l_err_loc := 800;
1876
1877 -- for pre 11.5.9 upgrade, the content zone must be created specially
1878 -- from the new seeded R12 "All Local Content" zone
1879 IF (g_is_pre_1159_upgrade) THEN
1880
1881 -- create the local zone, but don't add any category restrictions
1882 create_local_all_content_zones(l_new_zone_ids, 'INCLUDE_ALL');
1883 l_err_loc := 900;
1884
1885 ELSE
1886
1887 -- create the local zone, but don't add any category restrictions
1888 create_local_zones(l_new_zone_ids, p_catalog_id, 'RESP_SECURED',
1889 g_catalog_supplier_flags(p_catalog_id), 'INCLUDE_ALL');
1890 l_err_loc := 950;
1891
1892 END IF;
1893
1894 -- secure this zone by all these responsibilties without realms
1895 secure_zone_by_resps(l_new_zone_ids(1), l_resp_without_realms_ids);
1896 l_err_loc := 1000;
1897
1898 -- again, add the zone to the store if the original catalog was in a store
1899 IF (p_old_store_id IS NOT NULL) THEN
1900
1901 add_zones_to_store(l_new_zone_ids, g_stores_map(p_old_store_id),
1902 p_catalog_id, p_old_store_id);
1903 l_err_loc := 1100;
1904
1905 ELSE -- skip and log
1906
1907 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1908 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1909 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1910 'Skipping adding zone migrated from Local Catalog '||p_catalog_id||
1911 ' to any R12 Stores because it did not belong to any Old Stores.');
1912 END IF;
1913
1914 END IF;
1915
1916 ELSE -- skip and log
1917
1918 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1919 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1920 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1921 'Skipping creating category-unrestricted zone from Local Catalog '||
1922 p_catalog_id||' in Old Store '||p_old_store_id||
1923 ' as there are no resps without realms that can access it.');
1924 END IF;
1925
1926 END IF;
1927
1928 l_err_loc := 1200;
1929
1930 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1931 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 RAISE_APPLICATION_ERROR
1937 (-20000,
1938 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_catalog_with_realms(' ||
1939 l_err_loc || '), ' || SQLERRM);
1940
1941 END migrate_catalog_with_realms;
1942
1943
1944 -- Migrates all given item sources when item source realms do not need to
1945 -- be considered for them. This happens when the responsibilities
1946 -- that can access these item sources themselves do not have realms. This
1947 -- method is also used in the general case where item source realms is not
1948 -- used on the customer installation.
1949 --
1950 -- @param p_item_source_ids The list of item sources to migrate.
1951 -- @param p_old_store_ids The list of corresponding old stores that the
1952 -- item sources belonged to.
1953 -- @param p_new_zone_ids The list of new content zone sequence numbers to
1954 -- be used when migrating these item sources.
1955 --
1956 PROCEDURE migrate_isrcs_no_realms
1957 (
1958 p_item_source_ids IN ICX_TBL_NUMBER,
1959 p_old_store_ids IN ICX_TBL_NUMBER,
1960 p_new_zone_ids IN ICX_TBL_NUMBER
1961 )
1962 IS
1963 l_security_flags ICX_TBL_VARCHAR20;
1964 l_r12_store_ids ICX_TBL_NUMBER;
1965
1966 l_api_name CONSTANT VARCHAR2(30) := 'migrate_isrcs_no_realms';
1967 l_err_loc PLS_INTEGER;
1968 BEGIN
1969
1970 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1971 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
1972 END IF;
1973
1974 l_err_loc := 0;
1975
1976 -- create a corresponding list for each of the flags/ids needed
1977 l_security_flags := ICX_TBL_VARCHAR20();
1978 l_security_flags.extend(p_old_store_ids.COUNT);
1979
1980 l_r12_store_ids := ICX_TBL_NUMBER();
1981 l_r12_store_ids.extend(p_old_store_ids.COUNT);
1982
1983 l_err_loc := 100;
1984
1985 FOR i IN 1..p_old_store_ids.COUNT LOOP
1986
1987 -- if the item source does not belong to any store, keep NULL for the
1988 -- new store; also, security will default to ALL_USERS, as there are no
1989 -- restrictionson this non-existent store
1990 IF (p_old_store_ids(i) IS NULL) THEN
1991
1992 l_r12_store_ids(i) := NULL;
1993 l_security_flags(i) := 'ALL_USERS';
1994
1995 l_err_loc := 200;
1996
1997 ELSE
1998
1999 l_r12_store_ids(i) := g_stores_map(p_old_store_ids(i));
2000 l_security_flags(i) := g_store_security_flags(p_old_store_ids(i));
2001
2002 l_err_loc := 300;
2003
2004 END IF;
2005
2006 END LOOP;
2007
2008 -- once the new store IDs and security flags have been set, create the
2009 -- actual item source zones
2010 create_item_source_zones(p_new_zone_ids, p_item_source_ids, l_security_flags);
2011 l_err_loc := 400;
2012
2013 -- secure each by the corresponding operating unit restrictions of the old
2014 -- 11.5.10 store (or none, be that the case -- all handled in the method)
2015 secure_zones_by_store_orgs(p_new_zone_ids, p_old_store_ids, l_security_flags);
2016 l_err_loc := 500;
2017
2018 -- add all the zones to the corresponding new R12 stores
2019 add_zones_to_stores(p_new_zone_ids, l_r12_store_ids,
2020 p_item_source_ids, p_old_store_ids);
2021 l_err_loc := 600;
2022
2023 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2024 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2025 END IF;
2026
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 RAISE_APPLICATION_ERROR
2030 (-20000,
2031 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_isrcs_no_realms(' ||
2032 l_err_loc || '), ' || SQLERRM);
2033
2034 END migrate_isrcs_no_realms;
2035
2036
2037 -- Migrates one given item sources when item source realms when realms needs
2038 -- to be considered.
2039 --
2040 -- Unlike the no realms case, this method will handle only one item source
2041 -- at a time, to minimize complexity. The process is broken down as follows:
2042 --
2043 -- (1) Determine which responsibilities have realm values that allow them
2044 -- to access this given item source.
2045 --
2046 -- (2) If there are any responsibilities left, create a copy of the local
2047 -- catalog for each. Then add the category restrictions that are
2048 -- particular to each responsibility. Lastly, secure this content zone
2049 -- by that particular responsibility.
2050 --
2051 -- (3) For all responsibilities WITHOUT category realms that can access
2052 -- this store (if any), create another copy of the local content.
2053 -- This copy will not be restricted by categories. Secure this by
2054 -- all such responsibilities without category realms.
2055 --
2056 -- @param p_item_source_ids The list of item sources to migrate.
2057 -- @param p_old_store_ids The list of corresponding old stores that the
2058 -- item sources belonged to.
2059 -- @param p_resp_with_realms_ids The list of responsibilities with item source
2060 -- realms that can access this store via the
2061 -- store's "org assignments."
2062 -- @param p_resp_without_realms_ids List of responsibilities without item
2063 -- source realms that can access this store.
2064 --
2065 PROCEDURE migrate_isrc_with_realms
2066 (
2067 p_item_source_id IN NUMBER,
2068 p_old_store_id IN NUMBER,
2069 p_resp_with_realms_ids IN ICX_TBL_NUMBER,
2070 p_resp_without_realms_ids IN ICX_TBL_NUMBER
2071 )
2072 IS
2073 l_new_zone_ids ICX_TBL_NUMBER;
2074 l_resp_can_access_ids ICX_TBL_NUMBER;
2075 l_resps_to_secure_by ICX_TBL_NUMBER;
2076
2077 l_api_name CONSTANT VARCHAR2(30) := 'migrate_isrc_with_realms';
2078 l_err_loc PLS_INTEGER;
2079 BEGIN
2080
2081 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2082 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2083 END IF;
2084
2085 l_err_loc := 0;
2086
2087 -- keep a running list of responsibilities that will be secured against
2088 -- this migrated "item source" content zone
2089 l_resps_to_secure_by := ICX_TBL_NUMBER();
2090
2091 IF (g_item_sources_to_resp_map.EXISTS(p_item_source_id)) THEN
2092
2093 -- retrieve list of responsibilities that can access this item source
2094 -- from their item source realm values
2095 l_resp_can_access_ids := g_item_sources_to_resp_map(p_item_source_id);
2096 l_err_loc := 100;
2097
2098 FOR i in 1..l_resp_can_access_ids.COUNT LOOP
2099
2100 FOR j in 1..p_resp_with_realms_ids.COUNT LOOP
2101
2102 -- if this responsibility can access this store AND can access
2103 -- this item source via it's item source realms values, add to the list
2104 IF (l_resp_can_access_ids(i) = p_resp_with_realms_ids(j)) THEN
2105
2106 l_resps_to_secure_by.extend;
2107 l_resps_to_secure_by(l_resps_to_secure_by.COUNT)
2108 := l_resp_can_access_ids(i);
2109
2110 l_err_loc := 200;
2111
2112 END IF;
2113
2114 END LOOP;
2115
2116 END LOOP;
2117
2118 -- log out the responsibilties that can access this item source via realms
2119 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2120 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2121 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2122 'For migrating Item Source '||p_item_source_id||
2123 ' with Realms for Store '||p_old_store_id||
2124 ', the following responsibilities can access it:');
2125
2126 FOR i IN 1..l_resps_to_secure_by.COUNT LOOP
2127 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2128 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2129 'Responsibility #'||i||'.'||l_resps_to_secure_by(i));
2130 END LOOP;
2131 END IF;
2132
2133 -- ELSE no responsibilties can access this item source via its realm
2134 -- values, so do nothing as l_resps_to_secure_by is already empty
2135
2136 END IF;
2137
2138 -- add both lists up: (1) the list just created of the resps that have
2139 -- item source realms and can access the item source via it's realm values
2140 -- AND (2) the list of resps without item source realms for this store(they
2141 -- automatically should be able to access this zone), and determine if there's
2142 -- any responsibilites to do the actually securing by (if none, do nothing)
2143 IF (l_resps_to_secure_by.COUNT + p_resp_without_realms_ids.COUNT > 0) THEN
2144
2145 l_new_zone_ids := get_new_zone_ids(1);
2146 create_item_source_zones(l_new_zone_ids, p_item_source_id, 'RESP_SECURED');
2147
2148 l_err_loc := 300;
2149
2150 -- secure by the responsibility with realms that can access this item source
2151 IF (l_resps_to_secure_by.COUNT > 0) THEN
2152
2153 secure_zone_by_resps(l_new_zone_ids(1), l_resps_to_secure_by);
2154 l_err_loc := 400;
2155
2156 END IF;
2157
2158 -- secure by the responsibility without realms, again, if any
2159 IF (p_resp_without_realms_ids.COUNT > 0) THEN
2160
2161 secure_zone_by_resps(l_new_zone_ids(1), p_resp_without_realms_ids);
2162 l_err_loc := 500;
2163
2164 END IF;
2165
2166 -- add to the store only if the original item source was in a store
2167 IF (p_old_store_id IS NOT NULL) THEN
2168
2169 add_zones_to_store(l_new_zone_ids, g_stores_map(p_old_store_id),
2170 p_item_source_id, p_old_store_id);
2171 l_err_loc := 600;
2172
2173 END IF;
2174
2175 ELSE -- no responsibilities total can access this item source, skip it
2176
2177 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2178 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2179 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2180 'Skipping Item Source '||p_item_source_id||' for Store '||
2181 p_old_store_id||' as no responsibilities can access it.');
2182 END IF;
2183
2184 END IF;
2185
2186 l_err_loc := 700;
2187
2188 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2189 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2190 END IF;
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 RAISE_APPLICATION_ERROR
2195 (-20000,
2196 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_isrc_with_realms(' ||
2197 l_err_loc || '), ' || SQLERRM);
2198
2199 END migrate_isrc_with_realms;
2200
2201
2202 -- Migrates all info-templates over to Smart Forms for R12. In actuality,
2203 -- there is little data migration, as por_noncat_templates_all_b is still
2204 -- being used. So, only the relationship to the store is considered, where
2205 -- the smart form must be added to the new R12 store if the smart form
2206 -- via is org_id field is accessible via the old store's operating units
2207 -- restrictions, if any. The default template is skipped.
2208 --
2209 -- @param p_item_source_ids The list of item sources to migrate.
2210 -- @param p_old_store_ids The list of corresponding old stores that the
2211 -- item sources belonged to.
2212 -- @param p_new_zone_ids The list of new content zone sequence numbers to
2213 -- be used when migrating these item sources.
2214 --
2215 PROCEDURE migrate_templates
2216 IS
2217 l_template_ids ICX_TBL_NUMBER;
2218 l_old_store_ids ICX_TBL_NUMBER;
2219 l_r12_store_ids ICX_TBL_NUMBER;
2220
2221 l_api_name CONSTANT VARCHAR2(30) := 'migrate_templates';
2222 l_err_loc PLS_INTEGER;
2223 BEGIN
2224
2225 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2226 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2227 END IF;
2228
2229 l_err_loc := 0;
2230
2231 BEGIN
2232
2233 SELECT templates.template_id, sc.store_id
2234 BULK COLLECT INTO l_template_ids, l_old_store_ids
2235 FROM icx_cat_store_catalogs sc, por_noncat_templates_all_b templates,
2236 icx_cat_store_org_assignments orgs
2237 WHERE templates.template_id = sc.item_source_id
2238 AND sc.store_id = orgs.store_id
2239 AND orgs.org_id IN (templates.org_id, -2)
2240 AND templates.template_id <> 10000000;
2241
2242 l_err_loc := 100;
2243
2244 l_r12_store_ids := ICX_TBL_NUMBER();
2245 l_r12_store_ids.extend(l_template_ids.COUNT);
2246
2247 l_err_loc := 200;
2248
2249 FOR i in 1..l_template_ids.COUNT LOOP
2250
2251 -- get the corresponding R12 store ID;
2252 -- old store should be NEVER null as queried above
2253 l_r12_store_ids(i) := g_stores_map(l_old_store_ids(i));
2254 l_err_loc := 300;
2255
2256 END LOOP;
2257
2258 -- simply add these forms to their new corresponding R12 stores
2259 add_smart_forms_to_stores(l_template_ids, l_r12_store_ids, l_old_store_ids);
2260 l_err_loc := 400;
2261
2262 EXCEPTION
2263 WHEN no_data_found THEN
2264 NULL; -- there are no smart forms
2265 l_err_loc := 500;
2266
2267 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2268 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2269 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2270 'No Smart Forms found on the installation.');
2271 END IF;
2272 END;
2273
2274 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2275 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2276 END IF;
2277
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 RAISE_APPLICATION_ERROR
2281 (-20000,
2282 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.migrate_templates(' ||
2283 l_err_loc || '), ' || SQLERRM);
2284
2285 END migrate_templates;
2286
2287
2288 -- QUERYING OLD DATA HELPERS
2289 ----------------------------
2290
2291 -- Determines if there are any local catalogs in the installation.
2292 --
2293 -- @return TRUE if so, FALSE if not
2294 --
2295 FUNCTION exists_local_catalogs
2296 RETURN BOOLEAN
2297 IS
2298 l_exists_local_catalogs NUMBER;
2299 l_err_loc PLS_INTEGER;
2300 BEGIN
2301
2302 l_err_loc := 0;
2303
2304 BEGIN
2305
2306 SELECT 1
2307 INTO l_exists_local_catalogs
2308 FROM dual
2309 WHERE exists (SELECT item_source_id
2310 FROM icx_por_item_sources
2311 WHERE type = 'LOCAL');
2312 l_err_loc := 100;
2313
2314 EXCEPTION
2315 WHEN no_data_found THEN
2316 l_exists_local_catalogs := -1;
2317 l_err_loc := 200;
2318 END;
2319
2320 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2321 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2322 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name,
2323 'exists_local_catalogs'),
2324 'Checking if any catalogs exist (1=>TRUE) = '||l_exists_local_catalogs);
2325 END IF;
2326
2327 l_err_loc := 300;
2328
2329 RETURN (l_exists_local_catalogs = 1);
2330
2331 EXCEPTION
2332 WHEN OTHERS THEN
2333 RAISE_APPLICATION_ERROR
2334 (-20000,
2335 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.exists_local_catalogs('
2336 || l_err_loc || '), ' || SQLERRM);
2337
2338 END exists_local_catalogs;
2339
2340
2341 -- Determines if there are any item sources in the installation.
2342 --
2343 -- @return TRUE if so, FALSE if not
2344 --
2345 FUNCTION exists_item_sources
2346 RETURN BOOLEAN
2347 IS
2348 l_exists_item_sources NUMBER;
2349 l_err_loc PLS_INTEGER;
2350 BEGIN
2351
2352 l_err_loc := 0;
2353
2354 BEGIN
2355
2356 SELECT 1
2357 INTO l_exists_item_sources
2358 FROM dual
2359 WHERE exists (SELECT item_source_id
2360 FROM icx_por_item_sources
2361 WHERE type IN ('EXTERNAL', 'DISTSRCH', 'INFO'));
2362 l_err_loc := 100;
2363
2364 EXCEPTION
2365 WHEN no_data_found THEN
2366 l_exists_item_sources := -1;
2367 l_err_loc := 200;
2368 END;
2369
2370 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2371 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2372 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, 'exists_item_sources'),
2373 'Checking if any item sources exist (1=>TRUE) = '||l_exists_item_sources);
2374 END IF;
2375
2376 l_err_loc := 300;
2377
2378 RETURN (l_exists_item_sources = 1);
2379
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 RAISE_APPLICATION_ERROR
2383 (-20000,
2384 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.exists_item_sources('
2385 || l_err_loc || '), ' || SQLERRM);
2386
2387 END exists_item_sources;
2388
2389
2390 -- Creates a map of old store IDs to the security assignment flag values
2391 -- that will be stored on the new content zones. These values will be either
2392 -- 'ALL_USERS' for stores secured by -2 (all OUs), or 'OU_SECURED' for
2393 -- any stores secured by any number of OUs. 'RESP_SECURED' is not stored
2394 -- here, as it will be taken of specially if realms need to be considered.
2395 --
2396 PROCEDURE populate_store_security_flags
2397 IS
2398 l_store_ids ICX_TBL_NUMBER;
2399 l_security_flags ICX_TBL_VARCHAR20;
2400
2401 l_api_name CONSTANT VARCHAR2(30) := 'populate_store_security_flags';
2402 l_err_loc PLS_INTEGER;
2403 BEGIN
2404
2405 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2406 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2407 END IF;
2408
2409 l_err_loc := 0;
2410
2411 SELECT distinct(stores.store_id),
2412 decode(orgs.org_id, -2, 'ALL_USERS', 'OU_SECURED')
2413 BULK COLLECT INTO l_store_ids, l_security_flags
2414 FROM icx_cat_stores_b stores, icx_cat_store_org_assignments orgs
2415 WHERE stores.store_id = orgs.store_id;
2416
2417 l_err_loc := 100;
2418
2419 FOR i IN 1..l_store_ids.COUNT LOOP
2420
2421 g_store_security_flags(l_store_ids(i)) := l_security_flags(i);
2422
2423 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2424 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2425 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2426 'For Store '||l_store_ids(i)||
2427 ', security flag='||l_security_flags(i));
2428 END IF;
2429
2430 END LOOP;
2431
2432 l_err_loc := 200;
2433
2434 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2435 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2436 END IF;
2437
2438 EXCEPTION
2439 WHEN OTHERS THEN
2440 RAISE_APPLICATION_ERROR
2441 (-20000,
2442 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.populate_store_security_flags('
2443 || l_err_loc || '), ' || SQLERRM);
2444
2445 END populate_store_security_flags;
2446
2447
2448 -- Creates a map of old catalog IDs to the supplier attribute action flag
2449 -- values that will be stored on the new content zones. These values will
2450 -- be either 'INCLUDE' if any supplier rows are found, or 'EXCLUDE_ALL' if
2451 -- none are found for all other catalogs other than the local base.
2452 -- This is because in 11i10, exclusion of specific suppliers was not allowed,
2453 -- and the 'INCLUDE_ALL' case is only applicable to the seeded 'LOCAL_BASE'
2454 -- catalog which is handled by the decode.
2455 --
2456 PROCEDURE populate_catalog_supplr_flags
2457 IS
2458 l_sources_ids ICX_TBL_NUMBER;
2459 l_supplier_flags ICX_TBL_VARCHAR20;
2460
2461 l_api_name CONSTANT VARCHAR2(30) := 'populate_catalog_supplr_flags';
2462 l_err_loc PLS_INTEGER;
2463 BEGIN
2464
2465 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2466 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2467 END IF;
2468
2469 l_err_loc := 0;
2470
2471 SELECT distinct sources.item_source_id,
2472 nvl2(details.supplier_id, 'INCLUDE',
2473 DECODE(sources.protocol_supported,
2474 'LOCAL_BASE', 'INCLUDE_ALL', 'EXCLUDE_ALL'))
2475 BULK COLLECT INTO l_sources_ids, l_supplier_flags
2476 FROM icx_por_item_sources sources, icx_cat_item_src_details details
2477 WHERE sources.type = 'LOCAL'
2478 AND sources.item_source_id = details.item_source_id(+);
2479
2480 l_err_loc := 100;
2481
2482 FOR i IN 1..l_sources_ids.COUNT LOOP
2483
2484 g_catalog_supplier_flags(l_sources_ids(i)) := l_supplier_flags(i);
2485
2486 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2487 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2488 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2489 'For Local Catalog '||l_sources_ids(i)||
2490 ', supplier flag='||l_supplier_flags(i));
2491 END IF;
2492
2493 END LOOP;
2494
2495 l_err_loc := 200;
2496
2497 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2498 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2499 END IF;
2500
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 RAISE_APPLICATION_ERROR
2504 (-20000,
2505 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.populate_catalog_supplier_flags('
2506 || l_err_loc || '), ' || SQLERRM);
2507
2508 END populate_catalog_supplr_flags;
2509
2510
2511 -- Creates a list of all the item sources that will require a old item source
2512 -- ID (operator ID in the context of downloaded exchange punchout catalogs)
2513 -- to new content zone ID mapping due the requirement that the
2514 -- parent_zone_id must be properly mapped and populated in the punchout
2515 -- details table (icx_cat_punchout_details).
2516 --
2517 PROCEDURE populate_operator_id_list
2518 IS
2519 l_operator_ids ICX_TBL_NUMBER;
2520
2521 l_api_name CONSTANT VARCHAR2(30) := 'populate_operator_id_list';
2522 l_err_loc PLS_INTEGER;
2523 BEGIN
2524
2525 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2526 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2527 END IF;
2528
2529 l_err_loc := 0;
2530
2531 BEGIN
2532
2533 SELECT distinct operator_id
2534 BULK COLLECT INTO l_operator_ids
2535 FROM icx_por_item_sources
2536 WHERE operator_id IS NOT NULL;
2537
2538 l_err_loc := 100;
2539
2540 FOR i IN 1..l_operator_ids.COUNT LOOP
2541 g_exchange_punchout_map(l_operator_ids(i)) := -999;
2542 END LOOP;
2543
2544 l_err_loc := 200;
2545
2546 EXCEPTION
2547 WHEN NO_DATA_FOUND THEN
2548
2549 NULL; -- nothing needs to be done
2550 l_err_loc := 300;
2551
2552 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2553 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2554 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2555 'No downloaded exchange punchout catalogs found to migrate.');
2556 END IF;
2557
2558 END;
2559
2560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2561 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2562 END IF;
2563
2564 EXCEPTION
2565 WHEN OTHERS THEN
2566 RAISE_APPLICATION_ERROR
2567 (-20000,
2568 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.populate_parent_zone_id_list('
2569 || l_err_loc || '), ' || SQLERRM);
2570
2571 END populate_operator_id_list;
2572
2573
2574 -- In the case where realms must be considered, this method retrieves the
2575 -- list of contents (either local catalogs or item sources or both) depending
2576 -- on what has already been sucessfully migrated without realms. This
2577 -- method should not be called if both have already been migrated, as
2578 -- in that case, nothing more needs to be done, and this method does nothing.
2579 -- In all cases, the contractor catalog is ignored, as it is taken care of
2580 -- in migrate_seed_data().
2581 --
2582 -- @param p_catalogs_migrated True if local catalogs have been migrated
2583 -- (in this case, without realms consideration).
2584 -- @param p_item_sources_migrated True if item sources have been migrated
2585 -- (in this case, without realms consideration).
2586 --
2587 -- @param OUT x_old_content_ids The list of the item source IDs to still migrate.
2588 -- @param OUT x_content_types The corresponding types of the item sources.
2589 -- @param OUT x_old_store_ids The list of corresponding stores, may be NULL;
2590 -- sources need not have been added to stores.
2591 --
2592 PROCEDURE get_contents_to_migrate
2593 (
2594 p_catalogs_migrated IN BOOLEAN,
2595 p_item_sources_migrated IN BOOLEAN,
2596 x_old_content_ids OUT NOCOPY ICX_TBL_NUMBER,
2597 x_content_types OUT NOCOPY ICX_TBL_VARCHAR15,
2598 x_old_store_ids OUT NOCOPY ICX_TBL_NUMBER
2599 )
2600 IS
2601 l_api_name CONSTANT VARCHAR2(30) := 'get_contents_to_migrate';
2602 l_err_loc PLS_INTEGER;
2603 BEGIN
2604
2605 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2606 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2607 END IF;
2608
2609 l_err_loc := 0;
2610
2611 -- if neither has been migrated, select all contents to be migrated
2612 IF NOT (p_catalogs_migrated OR p_item_sources_migrated) THEN
2613
2614 l_err_loc := 100;
2615
2616 SELECT sources.item_source_id, sources.type, sc.store_id
2617 BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
2618 FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
2619 WHERE sources.item_source_id = sc.item_source_id(+)
2620 AND sources.type <> 'CNTRCTR'
2621 ORDER BY sc.store_id;
2622
2623 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2624 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2625 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2626 'Neither Local Catalogs nor Item Sources have been migrated.');
2627 END IF;
2628
2629 -- else if item sources has not been migrated, select only them
2630 ELSIF NOT (p_item_sources_migrated) THEN
2631
2632 l_err_loc := 200;
2633
2634 SELECT sources.item_source_id, sources.type, sc.store_id
2635 BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
2636 FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
2637 WHERE sources.item_source_id = sc.item_source_id(+)
2638 AND sources.type IN ('EXTERNAL', 'DISTSRCH', 'INFO')
2639 ORDER BY sc.store_id;
2640
2641 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2642 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2643 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2644 'Only Local Catalogs have been migrated, retrieving Item Sources.');
2645 END IF;
2646
2647 -- else if local catalogs has not been migrated, select only local catalogs
2648 ELSIF NOT (p_catalogs_migrated) THEN
2649
2650 l_err_loc := 300;
2651
2652 SELECT sources.item_source_id, sources.type, sc.store_id
2653 BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
2654 FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
2655 WHERE sources.item_source_id = sc.item_source_id(+)
2656 AND sources.type = 'LOCAL'
2657 ORDER BY sc.store_id;
2658
2659 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2660 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2661 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2662 'Only Item Sources have been migrated, retrieving Local Catalogs.');
2663 END IF;
2664
2665 END IF;
2666
2667 l_err_loc := 400;
2668
2669 -- log out the contents to be migrated
2670 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2671 FOR i IN 1..x_old_content_ids.COUNT LOOP
2672 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2673 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2674 i||'.old_content_id='||x_old_content_ids(i)||',type='
2675 ||x_content_types(i)||',store='||x_old_store_ids(i));
2676 END LOOP;
2677 END IF;
2678
2679 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2680 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2681 END IF;
2682
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 RAISE_APPLICATION_ERROR
2686 (-20000,
2687 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_contents_to_migrate('
2688 || l_err_loc || '), ' || SQLERRM);
2689
2690 END get_contents_to_migrate;
2691
2692
2693 -- R12 STORE HELPER METHODS
2694 ---------------------------
2695
2696 -- Creates a new R12 store from an existing pre-R12 old store,
2697 -- essentially migrating the data over. This method will NOT migrate
2698 -- the seeded "Main Store" and "Non-Catalog" store, as they were
2699 -- already handled in migrate_seed_data().
2700 --
2701 -- In addition, after this method call, the global map of old store IDs
2702 -- to new R12 store IDs will be populated.
2703 --
2704 PROCEDURE create_R12_stores
2705 IS
2706 l_old_store_ids ICX_TBL_NUMBER;
2707 l_r12_store_ids ICX_TBL_NUMBER;
2708
2709 l_api_name CONSTANT VARCHAR2(30) := 'create_R12_stores';
2710 l_err_loc PLS_INTEGER;
2711 l_old_store_key NUMBER;
2712 BEGIN
2713
2714 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2716 END IF;
2717
2718 l_err_loc := 0;
2719
2720 -- select all the old stores (minus the seeded ones) and new store IDs
2721 SELECT store_id, ICX_CAT_SHOP_STORES_S.NEXTVAL
2722 BULK COLLECT INTO l_old_store_ids, l_r12_store_ids
2723 FROM icx_cat_stores_b
2724 WHERE store_id NOT IN (0, 10000000);
2725
2726 l_err_loc := 100;
2727
2728 -- store a mapping from the old store to the new R12 store IDs;
2729 -- will be needed when migrate the store catalogs and item sources
2730 FOR i IN 1..l_old_store_ids.COUNT LOOP
2731 g_stores_map(l_old_store_ids(i)) := l_r12_store_ids(i);
2732 END LOOP;
2733
2734 l_err_loc := 200;
2735
2736 -- migrate the base and TL table data
2737 FORALL i IN 1..l_old_store_ids.COUNT
2738 INSERT INTO icx_cat_shop_stores_b
2739 (store_id, sequence, local_content_first_flag, created_by, creation_date,
2740 last_updated_by, last_update_date, last_update_login)
2741 SELECT l_r12_store_ids(i), stores.sequence_number, 'Y', fnd_global.user_id,
2742 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
2743 FROM icx_cat_stores_b stores
2744 WHERE stores.store_id = l_old_store_ids(i);
2745
2746 l_err_loc := 300;
2747
2748 FORALL i IN 1..l_old_store_ids.COUNT
2749 INSERT INTO icx_cat_shop_stores_tl
2750 (store_id, language, source_lang, name, description, long_description,
2751 image, created_by, creation_date, last_updated_by, last_update_date,
2752 last_update_login)
2753 SELECT l_r12_store_ids(i), stores_tl.language, stores_tl.source_lang,
2754 stores_tl.store_name, stores_tl.short_description,
2755 stores_tl.long_description, stores_tl.image_location, fnd_global.user_id,
2756 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
2757 FROM icx_cat_stores_tl stores_tl
2758 WHERE stores_tl.store_id = l_old_store_ids(i);
2759
2760 l_err_loc := 400;
2761
2762 -- list the mapping of old stores to the new stores created
2763 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2764
2765 l_old_store_key := g_stores_map.FIRST();
2766
2767 WHILE (l_old_store_key IS NOT NULL) LOOP
2768
2769 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2770 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2771 'New Store '||g_stores_map(l_old_store_key)||
2772 ' created from Old Store '||l_old_store_key);
2773
2774 l_old_store_key := g_stores_map.NEXT(l_old_store_key);
2775
2776 END LOOP;
2777
2778 END IF;
2779
2780 l_err_loc := 500;
2781
2782 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2783 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2784 END IF;
2785
2786 EXCEPTION
2787 WHEN OTHERS THEN
2788 RAISE_APPLICATION_ERROR
2789 (-20000,
2790 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_R12_stores('
2791 || l_err_loc || '), ' || SQLERRM);
2792
2793 END create_R12_stores;
2794
2795
2796 -- Adds the list of content zones to the given store. This method is
2797 -- primarily used in the case where category realms must be considered,
2798 -- and one catalog must be split into multiple content zones based on
2799 -- the responsibilities with category realms.
2800 --
2801 -- @param p_zone_ids List of Content Zone IDs to add to the R12 Store.
2802 -- @param p_r12_store_id The R12 Store ID to which the zones are to be added.
2803 -- @param p_old_store_id The 11.5.10 Store ID corresponding to the R12 store.
2804 --
2805 PROCEDURE add_zones_to_store
2806 (
2807 p_zone_ids IN ICX_TBL_NUMBER,
2808 p_r12_store_id IN NUMBER,
2809 p_old_source_id IN NUMBER,
2810 p_old_store_id IN NUMBER
2811 )
2812 IS
2813 l_api_name CONSTANT VARCHAR2(30) := 'add_zones_to_store';
2814 l_err_loc PLS_INTEGER;
2815 BEGIN
2816
2817 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2818 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2819 END IF;
2820
2821 l_err_loc := 0;
2822
2823 FORALL i IN 1..p_zone_ids.COUNT
2824 INSERT INTO icx_cat_store_contents
2825 (store_id, content_id, content_type, sequence, display_always_flag,
2826 created_by, creation_date, last_updated_by, last_update_date,
2827 last_update_login)
2828 SELECT p_r12_store_id, p_zone_ids(i), 'CONTENT_ZONE',
2829 sc.sequence_number, sc.display_always_flag,
2830 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
2831 fnd_global.login_id
2832 FROM icx_cat_store_catalogs sc
2833 WHERE p_old_store_id IS NOT NULL
2834 AND sc.store_id = p_old_store_id
2835 AND sc.item_source_id = p_old_source_id;
2836
2837 l_err_loc := 100;
2838
2839 -- list all the content zones added to the new stores
2840 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2841 FOR i IN 1..p_zone_ids.COUNT LOOP
2842 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2843 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2844 'Adding Zone '||p_zone_ids(i)||' to R12 Store '||p_r12_store_id);
2845 END LOOP;
2846 END IF;
2847
2848 -- list the old IDs if statement-level logging is enabled
2849 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2850 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2851 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2852 'Old Source='||p_old_source_id||', Old Store='||p_old_store_id);
2853 END IF;
2854
2855 l_err_loc := 200;
2856
2857 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2858 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2859 END IF;
2860
2861 EXCEPTION
2862 WHEN OTHERS THEN
2863 RAISE_APPLICATION_ERROR
2864 (-20000,
2865 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_zones_to_store('
2866 || l_err_loc || '), ' || SQLERRM);
2867
2868 END add_zones_to_store;
2869
2870
2871 -- Adds the list of content zones to the corresponding list of stores (1:1).
2872 -- That is, for each index, the zone at that index will be added to the store
2873 -- add that index. This is used to quickly add multiple zones to multiple
2874 -- stores. This is handled by a simple FORALL statement.
2875 --
2876 -- @param p_zone_ids List of Content Zone IDs to add to the stores.
2877 -- @param p_r12_store_ids The R12 Stores IDs to which the zones are to be added.
2878 -- @param p_old_store_ids The 11.5.10 Stores corresponding to the R12 Stores.
2879 --
2880 PROCEDURE add_zones_to_stores
2881 (
2882 p_zone_ids IN ICX_TBL_NUMBER,
2883 p_r12_store_ids IN ICX_TBL_NUMBER,
2884 p_old_source_ids IN ICX_TBL_NUMBER,
2885 p_old_store_ids IN ICX_TBL_NUMBER
2886 )
2887 IS
2888 l_api_name CONSTANT VARCHAR2(30) := 'add_zones_to_stores';
2889 l_err_loc PLS_INTEGER;
2890 BEGIN
2891
2892 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2893 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2894 END IF;
2895
2896 l_err_loc := 0;
2897
2898 FORALL i IN 1..p_zone_ids.COUNT
2899 INSERT INTO icx_cat_store_contents
2900 (store_id, content_id, content_type, sequence, display_always_flag,
2901 created_by, creation_date, last_updated_by, last_update_date,
2902 last_update_login)
2903 SELECT p_r12_store_ids(i), p_zone_ids(i), 'CONTENT_ZONE',
2904 sc.sequence_number, sc.display_always_flag,
2905 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
2906 fnd_global.login_id
2907 FROM icx_cat_store_catalogs sc
2908 WHERE p_old_store_ids(i) IS NOT NULL
2909 AND sc.store_id = p_old_store_ids(i)
2910 AND sc.item_source_id = p_old_source_ids(i);
2911
2912 l_err_loc := 100;
2913
2914 -- list all the content zones added to the new stores
2915 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2916 FOR i IN 1..p_zone_ids.COUNT LOOP
2917 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2918 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2919 'Adding Zone '||p_zone_ids(i)||' to R12 Store '||p_r12_store_ids(i));
2920 END LOOP;
2921 END IF;
2922
2923 -- list the old IDs if statement-level logging is enabled
2924 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2925 FOR i IN 1..p_old_source_ids.COUNT LOOP
2926 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2927 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2928 'Old Source='||p_old_source_ids(i)||', Old Store='||p_old_store_ids(i));
2929 END LOOP;
2930 END IF;
2931
2932 l_err_loc := 200;
2933
2934 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2935 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
2936 END IF;
2937
2938 EXCEPTION
2939 WHEN OTHERS THEN
2940 RAISE_APPLICATION_ERROR
2941 (-20000,
2942 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_zones_to_stores('
2943 || l_err_loc || '), ' || SQLERRM);
2944
2945 END add_zones_to_stores;
2946
2947
2948 -- Adds the list of smart forms to the corresponding list of stores (1:1).
2949 -- That is, for each index, the smart form at that index will be added to
2950 -- the store add that index. This is used to quickly add multiple
2951 -- smart forms to multiple stores. This is handled by a FORALL statement.
2952 -- Unlike content zones, smart forms keep the same ID since they are not
2953 -- being migrated (they say in por_noncat_templates_all_b), so no need for
2954 -- a corresponding "old_template_id" as with the content zones.
2955 --
2956 -- @param p_zone_ids List of Smart Form IDs to add to the stores.
2957 -- @param p_r12_store_ids The R12 Stores IDs to which the zones are to be added.
2958 -- @param p_old_store_ids The corresponding 11.5.10 store IDs.
2959 --
2960 PROCEDURE add_smart_forms_to_stores
2961 (
2962 p_smart_form_ids IN ICX_TBL_NUMBER,
2963 p_r12_store_ids IN ICX_TBL_NUMBER,
2964 p_old_store_ids IN ICX_TBL_NUMBER
2965 )
2966 IS
2967 l_api_name CONSTANT VARCHAR2(30) := 'add_smart_forms_to_stores';
2968 l_err_loc PLS_INTEGER;
2969 BEGIN
2970
2971 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2972 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
2973 END IF;
2974
2975 l_err_loc := 0;
2976
2977 FORALL i IN 1..p_smart_form_ids.COUNT
2978 INSERT INTO icx_cat_store_contents
2979 (store_id, content_id, content_type, sequence, display_always_flag,
2980 created_by, creation_date, last_updated_by, last_update_date,
2981 last_update_login)
2982 SELECT p_r12_store_ids(i), p_smart_form_ids(i), 'SMART_FORM',
2983 DECODE(default_template_flag, 'Y', 1, NULL),
2984 sc.display_always_flag, fnd_global.user_id, sysdate,
2985 fnd_global.user_id, sysdate, fnd_global.login_id
2986 FROM icx_cat_store_catalogs sc
2987 WHERE sc.store_id = p_old_store_ids(i)
2988 AND sc.item_source_id = p_smart_form_ids(i);
2989
2990 l_err_loc := 100;
2991
2992 -- list all the smart forms migrated to the new stores
2993 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2994 FOR i IN 1..p_smart_form_ids.COUNT LOOP
2995 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2996 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
2997 'Moving Smart Form '||p_smart_form_ids(i)||' from Old Store '||
2998 p_old_store_ids(i)||' to R12 Store '||p_r12_store_ids(i));
2999 END LOOP;
3000 END IF;
3001
3002 l_err_loc := 200;
3003
3004 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3005 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3006 END IF;
3007
3008 EXCEPTION
3009 WHEN OTHERS THEN
3010 RAISE_APPLICATION_ERROR
3011 (-20000,
3012 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_smart_forms_to_store('
3013 || l_err_loc || '), ' || SQLERRM);
3014
3015 END add_smart_forms_to_stores;
3016
3017
3018 -- CONTENT ZONE HELPERS
3019 -----------------------
3020
3021 -- A simple helper method that retreives a list of new content zone IDs
3022 -- based on the database sequence.
3023 --
3024 -- @param p_num_zone The number of zones to insert into the database, and
3025 -- hence the same number of new zone IDs to be returned.
3026 --
3027 FUNCTION get_new_zone_ids
3028 (
3029 p_num_zones IN NUMBER
3030 )
3031 RETURN ICX_TBL_NUMBER
3032 IS
3033 l_new_zone_ids ICX_TBL_NUMBER;
3034 l_api_name CONSTANT VARCHAR2(30) := 'get_new_zone_ids';
3035 l_err_loc PLS_INTEGER;
3036 BEGIN
3037
3038 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3039 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
3040 'p_num_zones='||p_num_zones);
3041 END IF;
3042
3043 l_err_loc := 0;
3044
3045 l_new_zone_ids := ICX_TBL_NUMBER();
3046 l_new_zone_ids.extend(p_num_zones);
3047
3048 FOR i IN 1..p_num_zones LOOP
3049
3050 l_err_loc := 100;
3051
3052 SELECT ICX_CAT_CONTENT_ZONES_S.NEXTVAL
3053 INTO l_new_zone_ids(i)
3054 FROM dual;
3055
3056 END LOOP;
3057
3058 l_err_loc := 200;
3059
3060 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3061 FOR i IN 1..l_new_zone_ids.COUNT LOOP
3062 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3063 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3064 'New Zone ID '||l_new_zone_ids(i)||' allocated.');
3065 END LOOP;
3066 END IF;
3067
3068 l_err_loc := 300;
3069
3070 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3071 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3072 END IF;
3073
3074 RETURN l_new_zone_ids;
3075
3076 EXCEPTION
3077 WHEN OTHERS THEN
3078 RAISE_APPLICATION_ERROR
3079 (-20000,
3080 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_new_zone_ids('
3081 || l_err_loc || '), ' || SQLERRM);
3082
3083 END get_new_zone_ids;
3084
3085
3086 -- Creates new content zones based on the given local catalog. This method
3087 -- is used primarily when migrating catalogs while considering category
3088 -- realms as one local catalog must be duplicated into multiple content
3089 -- zones that are secured by each of the different responsibilities with
3090 -- category realms.
3091 --
3092 -- In addition to the IDs of the new zones to create and that of the old
3093 -- local catalog which to migrate the data from, three more parameters are
3094 -- needed: (1) the security flag of the store that this catalog belonged
3095 -- to in 11.5.10, indicating whether to secure this zone by all_users,
3096 -- operating unit, or responsibility, (2) the supplier flag indicating
3097 -- whether this local catalog has any supplier restrictions, and
3098 -- (3) category_flag indicating whether there are any category restrictions
3099 -- (in the case with category realms).
3100 --
3101 -- This method will leverage the generic create_local_zones() method by
3102 -- simply creating multiple "arrays" storing the duplicated values.
3103 --
3104 -- @param p_new_zone_ids The list of new content zone IDs to create.
3105 -- @param p_catalog_id The old local catalog on which the new zones are based.
3106 -- @param p_security_flag The security flag (used to determined by
3107 -- the store the old catalog was in (all users, etc).
3108 -- @param p_supplier_flag The supplier flag indicating if the local
3109 -- catalog has supplier restrictions.
3110 -- @param p_category_flag The category flag indicating any category
3111 -- restrictions due to category realms.
3112 --
3113 -- Please see top level note for details into the valid values of these flags.
3114 --
3115 PROCEDURE create_local_zones
3116 (
3117 p_new_zone_ids IN ICX_TBL_NUMBER,
3118 p_catalog_id IN NUMBER,
3119 p_security_flag IN VARCHAR2,
3120 p_supplier_flag IN VARCHAR2,
3121 p_category_flag IN VARCHAR2
3122 )
3123 IS
3124 l_catalog_ids ICX_TBL_NUMBER;
3125 l_security_flags ICX_TBL_VARCHAR20;
3126 l_supplier_flags ICX_TBL_VARCHAR20;
3127
3128 l_api_name CONSTANT VARCHAR2(30) := 'create_local_zones';
3129 l_err_loc PLS_INTEGER;
3130 BEGIN
3131
3132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3133 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3134 END IF;
3135
3136 l_err_loc := 0;
3137
3138 l_security_flags := ICX_TBL_VARCHAR20();
3139 l_security_flags.extend(p_new_zone_ids.COUNT);
3140
3141 l_supplier_flags := ICX_TBL_VARCHAR20();
3142 l_supplier_flags.extend(p_new_zone_ids.COUNT);
3143
3144 l_catalog_ids := ICX_TBL_NUMBER();
3145 l_catalog_ids.extend(p_new_zone_ids.COUNT);
3146
3147 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3148
3149 l_err_loc := 100;
3150
3151 l_catalog_ids(i) := p_catalog_id;
3152 l_security_flags(i) := p_security_flag;
3153 l_supplier_flags(i) := p_supplier_flag;
3154
3155 END LOOP;
3156
3157 l_err_loc := 200;
3158
3159 -- after populating all the arrays, leverage the generic method to insert
3160 -- the local zones into the database
3161 create_local_zones(p_new_zone_ids, l_catalog_ids,
3162 l_security_flags, l_supplier_flags, p_category_flag);
3163
3164 l_err_loc := 300;
3165
3166 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3167 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3168 END IF;
3169
3170 EXCEPTION
3171 WHEN OTHERS THEN
3172 RAISE_APPLICATION_ERROR
3173 (-20000,
3174 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_local_zones('
3175 || l_err_loc || '), ' || SQLERRM);
3176
3177 END create_local_zones;
3178
3179
3180 -- Creates new content zones corresponding to the list of local catalogs (1:1).
3181 -- That is, for each index, the zone at that index will be created representing
3182 -- the local catalog at that index. This is used to quickly create multiple
3183 -- zones for multiple local catalogs. In addition to the IDs of the new
3184 -- zones to create and those the old local catalogs which to migrate the
3185 -- data from, three more parameters are needed: (1) list of security flags
3186 -- indicating whether to secure this zone by all_users, operating unit, or
3187 -- responsibility, (2) list of supplier flags indicating whether the local
3188 -- catalog has any supplier restrictions, and (3) category_flag indicating
3189 -- whether there are any category restrictions (in the case with
3190 -- category realms).
3191 --
3192 -- @param p_new_zone_ids The list of new content zone IDs to create.
3193 -- @param p_catalog_ids The list of old local catalogs to migrate over (1:1).
3194 -- @param p_security_flags The list of security flags (used to determined by
3195 -- the store the old catalog was in (all users, etc).
3196 -- @param p_supplier_flags The list of supplier flags indicating if the local
3197 -- catalog has supplier restrictions.
3198 -- @param p_category_flag One flag for indicating any category restrictions
3199 -- which is used for all zones created in this method;
3200 -- currently, only one flag is required instead of a
3201 -- list due to the structure of the code (it is such
3202 -- that either category realms is considered or not).
3203 --
3204 -- Please see top level note for details into the valid values of these flags.
3205 --
3206 PROCEDURE create_local_zones
3207 (
3208 p_new_zone_ids IN ICX_TBL_NUMBER,
3209 p_catalog_ids IN ICX_TBL_NUMBER,
3210 p_security_flags IN ICX_TBL_VARCHAR20,
3211 p_supplier_flags IN ICX_TBL_VARCHAR20,
3212 p_category_flag IN VARCHAR2
3213 )
3214 IS
3215 l_api_name CONSTANT VARCHAR2(30) := 'create_local_zones';
3216 l_err_loc PLS_INTEGER;
3217 BEGIN
3218
3219 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3220 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start(Generic)');
3221 END IF;
3222
3223 l_err_loc := 0;
3224
3225 -- do a forall, migrating values into the base and tl tables
3226 FORALL i IN 1..p_catalog_ids.COUNT
3227 INSERT INTO icx_cat_content_zones_b
3228 (zone_id, type, url, security_assignment_flag,
3229 category_attribute_action_flag, supplier_attribute_action_flag,
3230 items_without_supplier_flag, items_without_shop_catg_flag,
3231 created_by, creation_date, last_updated_by,
3232 last_update_date, last_update_login)
3233 SELECT p_new_zone_ids(i), 'LOCAL', sources.url, p_security_flags(i),
3234 p_category_flag, p_supplier_flags(i),
3235 DECODE(g_site_approved_pricing, 'Y', 'N',
3236 nvl(sources.include_internal_source_flag, 'Y')),
3237 'N', fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
3238 fnd_global.login_id
3239 FROM icx_por_item_sources sources
3240 WHERE sources.item_source_id = p_catalog_ids(i);
3241
3242 l_err_loc := 100;
3243
3244 FORALL i IN 1..p_catalog_ids.COUNT
3245 INSERT INTO icx_cat_content_zones_tl
3246 (zone_id, language, source_lang, name, description, keywords, image,
3247 created_by, creation_date, last_updated_by, last_update_date,
3248 last_update_login)
3249 SELECT p_new_zone_ids(i), sources_tl.language, sources_tl.source_lang,
3250 sources_tl.item_source_name, sources_tl.description,
3251 sources_tl.ctx_keywords, sources.image_url, fnd_global.user_id,
3252 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3253 FROM icx_por_item_sources sources, icx_por_item_sources_tl sources_tl
3254 WHERE sources.item_source_id = p_catalog_ids(i)
3255 AND sources_tl.item_source_id = sources.item_source_id;
3256
3257 l_err_loc := 200;
3258
3259 -- migrate over supplier restrictions, if any
3260 FORALL i IN 1..p_catalog_ids.COUNT
3261 INSERT INTO icx_cat_zone_secure_attributes
3262 (zone_id, securing_attribute, supplier_id, created_by, creation_date,
3263 last_updated_by, last_update_date, last_update_login)
3264 SELECT p_new_zone_ids(i), 'SUPPLIER', supplier_id, fnd_global.user_id,
3265 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3266 FROM icx_cat_item_src_details
3267 WHERE item_source_id = p_catalog_ids(i);
3268
3269 l_err_loc := 300;
3270
3271 -- log out all the new zones created
3272 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3273 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3274 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3275 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3276 'Zone '||p_new_zone_ids(i)||' created from Local Catalog '||
3277 p_catalog_ids(i)||' with security flag='||p_security_flags(i)||
3278 ', supplier flag='||p_supplier_flags(i)||
3279 ', category flag='||p_category_flag);
3280 END LOOP;
3281 END IF;
3282
3283 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End(Generic)');
3285 END IF;
3286
3287 EXCEPTION
3288 WHEN OTHERS THEN
3289 RAISE_APPLICATION_ERROR
3290 (-20000,
3291 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_local_zones('
3292 || l_err_loc || '), ' || SQLERRM);
3293
3294 END create_local_zones;
3295
3296
3297 -- Similar to "create_local_zones," this method will create new local content
3298 -- zones. However, this is done for the special case for pre-11.5.9 upgrades
3299 -- where the "All Local Content" is not already seeded in icx_por_item_sources.
3300 -- In this case, the local zones must be created from hard-coded values.
3301 --
3302 -- Note: This method should be called ONLY when there are realms involved.
3303 -- Values have been hard-coded accordingly. In the non-realms case,
3304 -- this method is not needed as the seeded R12 "All Local Content"
3305 -- content zone is already sufficient, and needs no additional upgrade.
3306 --
3307 -- Please see the "create_local_zones" header for details into how this method
3308 -- is similarily used.
3309 --
3310 -- @param p_new_zone_ids The list of new content zone IDs to create.
3311 -- @param p_category_flag One flag for indicating any category restrictions
3312 -- which is used for all zones created in this method;
3313 -- currently, only one flag is required instead of a
3314 -- list due to the structure of the code (it is such
3315 -- that either category realms is considered or not).
3316 --
3317 -- Please see top level note for details into the valid values of these flags.
3318 --
3319 PROCEDURE create_local_all_content_zones
3320 (
3321 p_new_zone_ids IN ICX_TBL_NUMBER,
3322 p_category_flag IN VARCHAR2
3323 )
3324 IS
3325 l_api_name CONSTANT VARCHAR2(30) := 'create_local_all_content_zones';
3326 l_err_loc PLS_INTEGER;
3327 BEGIN
3328
3329 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3330 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3331 END IF;
3332
3333 l_err_loc := 0;
3334
3335 -- do a forall, migrating values into the base and tl tables
3336 FORALL i IN 1..p_new_zone_ids.COUNT
3337 INSERT INTO icx_cat_content_zones_b
3338 (zone_id, type, url, security_assignment_flag,
3339 category_attribute_action_flag, supplier_attribute_action_flag,
3340 items_without_supplier_flag, items_without_shop_catg_flag,
3341 created_by, creation_date, last_updated_by,
3342 last_update_date, last_update_login)
3343 SELECT p_new_zone_ids(i), 'LOCAL', zones.url, 'RESP_SECURED',
3344 p_category_flag, 'INCLUDE_ALL',
3345 DECODE(g_site_approved_pricing, 'Y', 'N',
3346 zones.items_without_supplier_flag),
3347 zones.items_without_shop_catg_flag, fnd_global.user_id,
3348 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3349 FROM icx_cat_content_zones_b zones
3350 WHERE zones.zone_id = 1;
3351
3352 l_err_loc := 100;
3353
3354 FORALL i IN 1..p_new_zone_ids.COUNT
3355 INSERT INTO icx_cat_content_zones_tl
3356 (zone_id, language, source_lang, name, description, keywords, image,
3357 created_by, creation_date, last_updated_by, last_update_date,
3358 last_update_login)
3359 SELECT p_new_zone_ids(i), zones_tl.language, zones_tl.source_lang,
3360 zones_tl.name, zones_tl.description,
3361 zones_tl.keywords, zones_tl.image, fnd_global.user_id,
3362 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3363 FROM icx_cat_content_zones_tl zones_tl
3364 WHERE zones_tl.zone_id = 1;
3365
3366 l_err_loc := 200;
3367
3368 -- log out all the new zones created
3369 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3370 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3371 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3372 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3373 'Special All-Local-Content Zone '||p_new_zone_ids(i)||' created.');
3374 END LOOP;
3375 END IF;
3376
3377 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3378 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3379 END IF;
3380
3381 EXCEPTION
3382 WHEN OTHERS THEN
3383 RAISE_APPLICATION_ERROR
3384 (-20000,
3385 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_local_all_content_zones('
3386 || l_err_loc || '), ' || SQLERRM);
3387
3388 END create_local_all_content_zones;
3389
3390
3391 -- Add category restrictions to the given content zones. The category
3392 -- restrictions added will be based on the category realm values of the
3393 -- given responsibilities. This is done on a 1:1 basis. That is, for
3394 -- each index, the zone at that index will be secured by the categories
3395 -- based on the realms of the responsibility at that index.
3396 --
3397 -- @param p_new_zone_ids The list of new content zone IDs to which to
3398 -- add category restrictions.
3399 -- @param p_resp_ids The list of responsibilities from which category realms
3400 -- restrictions will be based.
3401 --
3402 PROCEDURE add_resp_categories_to_zone
3403 (
3404 p_new_zone_ids IN ICX_TBL_NUMBER,
3405 p_resp_ids IN ICX_TBL_NUMBER
3406 )
3407 IS
3408 l_current_zone_id NUMBER;
3409 l_category_ids ICX_TBL_NUMBER;
3410
3411 l_zones_to_insert ICX_TBL_NUMBER;
3412 l_categories_to_insert ICX_TBL_NUMBER;
3413
3414 l_api_name CONSTANT VARCHAR2(30) := 'add_resp_categories_to_zone';
3415 l_err_loc PLS_INTEGER;
3416 BEGIN
3417
3418 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3419 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3420 END IF;
3421
3422 l_err_loc := 0;
3423
3424 l_zones_to_insert := ICX_TBL_NUMBER();
3425 l_categories_to_insert := ICX_TBL_NUMBER();
3426
3427 l_err_loc := 100;
3428
3429 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3430
3431 -- for each responsibility with category realms, get all the categories
3432 -- that are accessible
3433 l_category_ids := get_catr_realm_values_cached(p_resp_ids(i));
3434 l_err_loc := 200;
3435
3436 -- for each responsibility, a new content zone will be created
3437 l_current_zone_id := p_new_zone_ids(i);
3438 l_err_loc := 300;
3439
3440 -- collect the responsibilty, zone, and categories into a running list
3441 FOR j IN 1..l_category_ids.COUNT LOOP
3442
3443 l_zones_to_insert.extend;
3444 l_zones_to_insert(l_zones_to_insert.COUNT) := l_current_zone_id;
3445
3446 l_categories_to_insert.extend;
3447 l_categories_to_insert(l_categories_to_insert.COUNT) := l_category_ids(j);
3448
3449 l_err_loc := 400;
3450
3451 END LOOP;
3452
3453 END LOOP;
3454
3455 -- then, for all the responsibility, zone, and categories tuples, add
3456 -- them all as rows in the secure attributes table
3457 FORALL i IN 1..l_zones_to_insert.COUNT
3458 INSERT INTO icx_cat_zone_secure_attributes
3459 (zone_id, securing_attribute, ip_category_id, created_by, creation_date,
3460 last_updated_by, last_update_date, last_update_login)
3461 SELECT l_zones_to_insert(i), 'CATEGORY', l_categories_to_insert(i),
3462 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
3463 fnd_global.login_id
3464 FROM dual;
3465
3466 -- for statement-level logging, print out values inserted
3467 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3468 FOR i IN 1..l_zones_to_insert.COUNT LOOP
3469 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3470 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3471 'Zone '||l_zones_to_insert(i)||' secured by Category '||
3472 l_categories_to_insert(i));
3473 END LOOP;
3474 END IF;
3475
3476 l_err_loc := 500;
3477
3478 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3479 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3480 END IF;
3481
3482 EXCEPTION
3483 WHEN OTHERS THEN
3484 RAISE_APPLICATION_ERROR
3485 (-20000,
3486 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.add_resp_categories_to_zone('
3487 || l_err_loc || '), ' || SQLERRM);
3488
3489 END add_resp_categories_to_zone;
3490
3491
3492 -- Creates new content zones based on the given item source. This method
3493 -- is used primarily when migrating item sources while considering item source
3494 -- realms. However, unlike catalogs, typically, only one zone is still
3495 -- created for the item source even with realms. However, this method still
3496 -- accepts a list of new content zones, if that were the case. In the
3497 -- one zone case, this list would only have one element.
3498 --
3499 -- In addition to the IDs of the new zones to create and the item source
3500 -- to migrate the data from, one more item is needed: (1) list of security
3501 -- flags indicating whether to secure this zone by all_users, operating unit,
3502 -- or responsibility.
3503 --
3504 -- This method will leverage the generic create_item_source_zones() method by
3505 -- simply creating multiple "arrays" storing the duplicated values.
3506 --
3507 -- @param p_new_zone_ids The list of new content zone IDs to create.
3508 -- @param p_item_source_id The item source on which the new zones are based.
3509 -- @param p_security_flag The security flag (used to determined by
3510 -- the store the old item source was in (all users, etc).
3511 --
3512 -- Please see top level note for details into the valid values of these flags.
3513 --
3514 PROCEDURE create_item_source_zones
3515 (
3516 p_new_zone_ids IN ICX_TBL_NUMBER,
3517 p_item_source_id IN NUMBER,
3518 p_security_flag IN VARCHAR2
3519 )
3520 IS
3521 l_item_source_ids ICX_TBL_NUMBER;
3522 l_security_flags ICX_TBL_VARCHAR20;
3523 l_api_name CONSTANT VARCHAR2(30) := 'create_item_source_zones';
3524 l_err_loc PLS_INTEGER;
3525 BEGIN
3526
3527 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3528 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3529 END IF;
3530
3531 l_err_loc := 0;
3532
3533 l_item_source_ids := ICX_TBL_NUMBER();
3534 l_item_source_ids.extend(p_new_zone_ids.COUNT);
3535
3536 l_security_flags := ICX_TBL_VARCHAR20();
3537 l_security_flags.extend(p_new_zone_ids.COUNT);
3538
3539 l_err_loc := 100;
3540
3541 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3542
3543 l_item_source_ids(i) := p_item_source_id;
3544 l_security_flags(i) := p_security_flag;
3545
3546 END LOOP;
3547
3548 l_err_loc := 200;
3549
3550 -- after populating all the arrays, leverage the generic method to insert
3551 -- the item source zones into the database
3552 create_item_source_zones(p_new_zone_ids, l_item_source_ids, l_security_flags);
3553
3554 l_err_loc := 300;
3555
3556 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3557 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3558 END IF;
3559
3560 EXCEPTION
3561 WHEN OTHERS THEN
3562 RAISE_APPLICATION_ERROR
3563 (-20000,
3564 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_item_source_zones('
3565 || l_err_loc || '), ' || SQLERRM);
3566
3567 END create_item_source_zones;
3568
3569
3570 -- Creates new item source zones corresponding to the list of old item
3571 -- sources (1:1). That is, for each index, the zone at that index will be
3572 -- created representing the item source at that index. This is used to
3573 -- quickly create multiple zones for multiple item sources. In addition
3574 -- to the IDs of the new zones to create and those the old item sources which
3575 -- to migrate the data from, one more item is needed: (1) list of security
3576 -- flags indicating whether to secure this zone by all_users, operating unit,
3577 -- or responsibility.
3578 --
3579 -- @param p_new_zone_ids The list of new content zone IDs to create.
3580 -- @param p_item_source_ids The list of old item sources to migrate over (1:1).
3581 -- @param p_security_flags The list of security flags (used to determined by
3582 -- the store the item source was in (all users, etc).
3583 --
3584 -- Please see top level note for details into the valid values of these flags.
3585 --
3586 PROCEDURE create_item_source_zones
3587 (
3588 p_new_zone_ids IN ICX_TBL_NUMBER,
3589 p_item_source_ids IN ICX_TBL_NUMBER,
3590 p_security_flags IN ICX_TBL_VARCHAR20
3591 )
3592 IS
3593 l_encrypted_password VARCHAR2(100); -- length in icx_por_item_sources
3594 l_decrypted_password VARCHAR2(2048); -- length in icx_call decrypt method
3595 l_api_name CONSTANT VARCHAR2(30) := 'create_item_source_zones';
3596 l_err_loc PLS_INTEGER;
3597 BEGIN
3598
3599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3600 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start(Generic)');
3601 END IF;
3602
3603 l_err_loc := 0;
3604
3605 -- using FORALL, migrate values into the base and tl tables
3606 FORALL i IN 1..p_new_zone_ids.COUNT
3607 INSERT INTO icx_cat_content_zones_b
3608 (zone_id, type, url, security_assignment_flag, created_by, creation_date,
3609 last_updated_by, last_update_date, last_update_login)
3610 SELECT p_new_zone_ids(i),
3611 DECODE(type, 'EXTERNAL', 'PUNCHOUT',
3612 'DISTSRCH', 'TRANSPARENT_PUNCHOUT',
3613 'INFO', 'INFORMATIONAL'),
3614 sources.url, p_security_flags(i),
3615 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
3616 fnd_global.login_id
3617 FROM icx_por_item_sources sources
3618 WHERE sources.item_source_id = p_item_source_ids(i);
3619
3620 l_err_loc := 100;
3621
3622 FORALL i IN 1..p_new_zone_ids.COUNT
3623 INSERT INTO icx_cat_content_zones_tl
3624 (zone_id, language, source_lang, name, description, keywords, image,
3625 created_by, creation_date, last_updated_by, last_update_date,
3626 last_update_login)
3627 SELECT p_new_zone_ids(i), sources_tl.language, sources_tl.source_lang,
3628 sources_tl.item_source_name, sources_tl.description,
3629 sources_tl.ctx_keywords, sources.image_url,
3630 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
3631 fnd_global.login_id
3632 FROM icx_por_item_sources sources, icx_por_item_sources_tl sources_tl
3633 WHERE sources.item_source_id = p_item_source_ids(i)
3634 AND sources_tl.item_source_id = sources.item_source_id;
3635
3636 l_err_loc := 200;
3637
3638 -- migrate values into punchout zone details for those type of item sources
3639 FORALL i IN 1..p_new_zone_ids.COUNT
3640 INSERT INTO icx_cat_punchout_zone_details
3641 (zone_id, protocol_supported, user_name, company_name, company_number,
3642 supplier_name, supplier_number, ecgateway_map_key1, vendor_id,
3643 vendor_site_id, encoding, party_site_id, parent_zone_id, authenticated_key,
3644 user_info_flag, lock_item_flag, retain_session_flag, operation_allowed,
3645 negotiated_flag, created_by, creation_date, last_updated_by,
3646 last_update_date, last_update_login)
3647 SELECT p_new_zone_ids(i),
3648 DECODE(protocol_supported, 'EXCHANGE', 'EXCHANGE',
3649 'XML_SUPP', 'XML_SUPPLIER',
3650 'CXML_SUPP', 'CXML_SUPPLIER',
3651 'DISTSRCH_SUPP', 'TRANSPARENT_SUPPLIER',
3652 'DISTSRCH_EXCH', 'TRANSPARENT_EXCHANGE',
3653 'VIA_EXCH', 'VIA_EXCHANGE'),
3654 user_name, company_name, company_number, supplier_name,
3655 supplier_number, key_1, vendor_id, vendor_site_id, encoding,
3656 party_site_id, operator_id, authenticated_key, user_info_flag,
3657 lock_item_flag, icx_session_servlet_flag, operation_allowed,
3658 negotiated_by_preparer_flag, fnd_global.user_id, sysdate,
3659 fnd_global.user_id, sysdate, fnd_global.login_id
3660 FROM icx_por_item_sources
3661 WHERE item_source_id = p_item_source_ids(i)
3662 AND type IN ('EXTERNAL', 'DISTSRCH');
3663
3664 l_err_loc := 300;
3665
3666 -- for distributed search, "supplemental" values may be migrated
3667 FORALL i IN 1..p_new_zone_ids.COUNT
3668 INSERT INTO icx_cat_zone_attributes
3669 (zone_id, attribute_name, attribute_value, created_by, creation_date,
3670 last_updated_by, last_update_date, last_update_login)
3671 SELECT p_new_zone_ids(i), d.name, d.value, fnd_global.user_id,
3672 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3673 FROM icx_por_item_sources s, icx_cat_item_src_details d
3674 WHERE s.item_source_id = p_item_source_ids(i)
3675 AND s.type = 'DISTSRCH'
3676 AND s.item_source_id = d.item_source_id;
3677
3678 l_err_loc := 400;
3679
3680 -- then, specially migrate passwords into fnd_vault (used in R12);
3681 -- also, take care of populate mapping of exchange item sources for
3682 -- downloaded punchouts mapping
3683 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3684
3685 SELECT password
3686 INTO l_encrypted_password
3687 FROM icx_por_item_sources
3688 WHERE item_source_id = p_item_source_ids(i);
3689
3690 -- skip the cases where the password is NULL
3691 IF (l_encrypted_password IS NOT NULL) THEN
3692
3693 l_decrypted_password := icx_call.decrypt(l_encrypted_password);
3694
3695 IF (l_decrypted_password IS NOT NULL) THEN
3696
3697 FND_VAULT.PUT('ICX_CAT_CONTENT_ZONE_PSWD',
3698 p_new_zone_ids(i), l_decrypted_password);
3699
3700 l_err_loc := 500;
3701
3702 END IF;
3703
3704 END IF;
3705
3706 -- only require the mapping for those exchange item sources that have
3707 -- corresponding downloaded punchouts (previously populated in the map)
3708 IF (g_exchange_punchout_map.EXISTS(p_item_source_ids(i))) THEN
3709
3710 g_exchange_punchout_map(p_item_source_ids(i)) := p_new_zone_ids(i);
3711 l_err_loc := 600;
3712
3713 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3714 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3715 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3716 'For exchange item source '||p_item_source_ids(i)||
3717 ', new content zone ID = '||p_new_zone_ids(i));
3718 END IF;
3719
3720 END IF;
3721
3722 END LOOP;
3723
3724 -- log out all the new zones created
3725 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3726 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3727 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3728 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3729 'Zone '||p_new_zone_ids(i)||' created from Item Source '||
3730 p_item_source_ids(i)||' with security flag ='||p_security_flags(i));
3731 END LOOP;
3732 END IF;
3733
3734 l_err_loc := 700;
3735
3736 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3737 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End(Generic)');
3738 END IF;
3739
3740 EXCEPTION
3741 WHEN OTHERS THEN
3742 RAISE_APPLICATION_ERROR
3743 (-20000,
3744 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.create_item_source_zones('
3745 || l_err_loc || '), ' || SQLERRM);
3746
3747 END create_item_source_zones;
3748
3749
3750 -- Currently, for downloaded punchout catalogs from Exchange, a link is
3751 -- maintained via the "operator_id." To properly upgrade these catalogs,
3752 -- when creating the corresponding content zones, a mapping must be maintained
3753 -- such that these old "opeartor_id"s are mapped to the new "parent_zone_ids"
3754 -- (which reflect the new R12 content zone IDs) for R12.
3755 --
3756 -- This way of updating exchange punchouts after-the-fact may not be ideal.
3757 -- However, since the order of content zone upgrade for item sources (nor
3758 -- for that fact local catalogs) is not guaranteed, this avoids complicated
3759 -- logic of (1) pre-storing the new IDs for exchange catalogs that have
3760 -- corresponding downloaded punchouts or (2) having to enforce an order
3761 -- of which item source catalogs are migrated so that exchange item sources
3762 -- are created before all downloaded punchouts (which may prove difficult
3763 -- in the existing context of migrating store security, realms, etc).
3764 --
3765 -- Hence, this method MUST be called in all code paths that require
3766 -- upgrading of item source catalogs from pre-R12.
3767 --
3768 PROCEDURE update_exchange_punchouts
3769 IS
3770 l_old_operator_ids ICX_TBL_NUMBER;
3771 l_new_parent_ids ICX_TBL_NUMBER;
3772
3773 l_api_name CONSTANT VARCHAR2(30) := 'update_exchange_punchouts';
3774 l_err_loc PLS_INTEGER;
3775 BEGIN
3776
3777 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3778 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3779 END IF;
3780
3781 l_err_loc := 0;
3782
3783 -- retrieve all the operator_id values that must be updated
3784 SELECT distinct operator_id
3785 BULK COLLECT INTO l_old_operator_ids
3786 FROM icx_por_item_sources
3787 WHERE operator_id IS NOT NULL;
3788 l_err_loc := 100;
3789
3790 -- create a list of the new zones IDs which will replace old operator IDs
3791 l_new_parent_ids := ICX_TBL_NUMBER();
3792 l_new_parent_ids.extend(l_old_operator_ids.COUNT);
3793 l_err_loc := 200;
3794
3795 -- populate the list from mapping created when creating the content zones
3796 FOR i IN 1..l_old_operator_ids.COUNT LOOP
3797
3798 IF ((g_exchange_punchout_map.EXISTS(l_old_operator_ids(i))) AND
3799 (g_exchange_punchout_map(l_old_operator_ids(i)) <> -999)) THEN
3800
3801 l_new_parent_ids(i) := g_exchange_punchout_map(l_old_operator_ids(i));
3802 l_err_loc := 300;
3803
3804 ELSE
3805
3806 -- this shouldn't happen (bad data), so log out an exception and continue
3807 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3808 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3809 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3810 'Could not find a mapping for the old operator ID = '||
3811 l_old_operator_ids(i));
3812 END IF;
3813 l_err_loc := 400;
3814
3815 END IF;
3816
3817 END LOOP;
3818
3819 -- now update all the downloaded punchout zones with the new zone IDs
3820 FORALL i IN 1..l_old_operator_ids.COUNT
3821 UPDATE icx_cat_punchout_zone_details
3822 SET parent_zone_id = l_new_parent_ids(i)
3823 WHERE parent_zone_id = l_old_operator_ids(i);
3824 l_err_loc := 200;
3825
3826 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3827 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3828 END IF;
3829
3830 EXCEPTION
3831 WHEN OTHERS THEN
3832 RAISE_APPLICATION_ERROR
3833 (-20000,
3834 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.update_exchange_punchouts('
3835 || l_err_loc || '), ' || SQLERRM);
3836
3837 END update_exchange_punchouts;
3838
3839
3840 -- SECURING CONTENT ZONE HELPER METHODS
3841 ---------------------------------------
3842
3843 -- Secures the given content zones with the same Operating Unit
3844 -- restrictions as the old pre-R12 stores. This is done, like many
3845 -- methods in this package with 1:1 correspondence. At each index,
3846 -- the content zone at that index will be secured with the same
3847 -- operating unit restrictions as the old store at that index.
3848 --
3849 -- @param p_zone_ids List of new content zones to be secured.
3850 -- @param p_old_store_ids List of the corresponding stores to which to draw
3851 -- the OU restrictions from. These restrictions will
3852 -- be transferred to the new content zone (as these
3853 -- restrictions have been moved from the store-level to
3854 -- the content zone level for R12.
3855 -- @param p_security_flags Representing what type of security restrictions
3856 -- were on the old store. Only "Operating Unit"-level
3857 -- security is required. "All Users" security does
3858 -- not require adding any relationships (is skipped).
3859 --
3860 PROCEDURE secure_zones_by_store_orgs
3861 (
3862 p_new_zone_ids IN ICX_TBL_NUMBER,
3863 p_old_store_ids IN ICX_TBL_NUMBER,
3864 p_security_flags IN ICX_TBL_VARCHAR20
3865 )
3866 IS
3867 l_api_name CONSTANT VARCHAR2(30) := 'secure_zones_by_store_orgs';
3868 l_err_loc PLS_INTEGER;
3869 BEGIN
3870
3871 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3872 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3873 END IF;
3874
3875 l_err_loc := 0;
3876
3877 -- secure the content zones with the orgs that were set on the old stores
3878 FORALL i IN 1..p_new_zone_ids.COUNT
3879 INSERT INTO icx_cat_secure_contents
3880 (content_id, org_id, secure_by, created_by, creation_date, last_updated_by,
3881 last_update_date, last_update_login)
3882 SELECT p_new_zone_ids(i), oa.org_id, 'OPERATING_UNIT', fnd_global.user_id,
3883 sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
3884 FROM icx_cat_store_org_assignments oa
3885 WHERE p_old_store_ids(i) IS NOT NULL
3886 AND oa.store_id = p_old_store_ids(i)
3887 AND p_security_flags(i) = 'OU_SECURED';
3888
3889 -- note how many pairs secured
3890 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3891 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3892 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3893 'Securing Zones by Operating Units, p_new_zone_ids.COUNT='
3894 ||p_new_zone_ids.COUNT);
3895 END IF;
3896
3897 -- for statement-level logging, print out all tuples
3898 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3899 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3900 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3901 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3902 'Zone '||p_new_zone_ids(i)||' secured by OUs from Store '||
3903 p_old_store_ids(i)||' with security flag='||p_security_flags(i));
3904 END LOOP;
3905 END IF;
3906
3907 l_err_loc := 100;
3908
3909 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3910 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3911 END IF;
3912
3913 EXCEPTION
3914 WHEN OTHERS THEN
3915 RAISE_APPLICATION_ERROR
3916 (-20000,
3917 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.secure_zones_by_store_orgs('
3918 || l_err_loc || '), ' || SQLERRM);
3919
3920 END secure_zones_by_store_orgs;
3921
3922
3923 -- Secures the given content zones by the given responsibilities. At each
3924 -- index, the content zone is secured by the given responsibility (1:1).
3925 -- This method is used when securing multiple content zones to the
3926 -- multiple responsibilities that have realms.
3927 --
3928 -- @param p_zone_ids List of new content zones to be secured.
3929 -- @param p_resp_ids List of the corresponding responsibilities to secure by.
3930 --
3931 PROCEDURE secure_zones_by_resps
3932 (
3933 p_new_zone_ids IN ICX_TBL_NUMBER,
3934 p_resp_ids IN ICX_TBL_NUMBER
3935 )
3936 IS
3937 l_api_name CONSTANT VARCHAR2(30) := 'secure_zones_by_resps';
3938 l_err_loc PLS_INTEGER;
3939 BEGIN
3940
3941 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3942 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, 'Start');
3943 END IF;
3944
3945 l_err_loc := 0;
3946
3947 FORALL i IN 1..p_resp_ids.COUNT
3948 INSERT INTO icx_cat_secure_contents
3949 (content_id, responsibility_id, secure_by, created_by, creation_date,
3950 last_updated_by, last_update_date, last_update_login)
3951 SELECT p_new_zone_ids(i), p_resp_ids(i), 'RESPONSIBILITY',
3952 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
3953 fnd_global.login_id
3954 FROM dual;
3955
3956 -- note how many pairs secured
3957 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3958 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
3959 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3960 'Securing Zones by Responsibilities, p_new_zone_ids.COUNT='
3961 ||p_new_zone_ids.COUNT);
3962 END IF;
3963
3964 -- for statement-level logging, print out all pairs
3965 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3966 FOR i IN 1..p_new_zone_ids.COUNT LOOP
3967 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
3968 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
3969 'Zone '||p_new_zone_ids(i)||' secured by resp '||p_resp_ids(i));
3970 END LOOP;
3971 END IF;
3972
3973 l_err_loc := 100;
3974
3975 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3976 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
3977 END IF;
3978
3979 EXCEPTION
3980 WHEN OTHERS THEN
3981 RAISE_APPLICATION_ERROR
3982 (-20000,
3983 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.secure_zones_by_resps('
3984 || l_err_loc || '), ' || SQLERRM);
3985
3986 END secure_zones_by_resps;
3987
3988
3989 -- Secures one given content zone by ALL the given responsibilities.
3990 -- This method leverages the generic method by simply duplicating
3991 -- the same new zone ID multiple times, and then calling that generic method.
3992 --
3993 -- @param p_zone_id The new content zone to be secured.
3994 -- @param p_resp_ids List of the responsibilities to secure the zone by.
3995 --
3996 PROCEDURE secure_zone_by_resps
3997 (
3998 p_new_zone_id IN NUMBER,
3999 p_resp_ids IN ICX_TBL_NUMBER
4000 )
4001 IS
4002 l_new_zone_ids ICX_TBL_NUMBER;
4003 l_api_name CONSTANT VARCHAR2(30) := 'secure_zone_by_resps';
4004 l_err_loc PLS_INTEGER;
4005 BEGIN
4006
4007 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4008 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4009 'p_new_zone_id='||p_new_zone_id||',p_resp_ids.COUNT='||p_resp_ids.COUNT);
4010 END IF;
4011
4012 l_err_loc := 0;
4013
4014 l_new_zone_ids := ICX_TBL_NUMBER();
4015 l_new_zone_ids.extend(p_resp_ids.COUNT);
4016
4017 l_err_loc := 100;
4018
4019 FOR i IN 1..p_resp_ids.COUNT LOOP
4020 l_new_zone_ids(i) := p_new_zone_id;
4021 END LOOP;
4022
4023 l_err_loc := 200;
4024
4025 -- after populating the zones lists, leverage the generic method
4026 secure_zones_by_resps(l_new_zone_ids, p_resp_ids);
4027
4028 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4029 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4030 END IF;
4031
4032 EXCEPTION
4033 WHEN OTHERS THEN
4034 RAISE_APPLICATION_ERROR
4035 (-20000,
4036 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.secure_zone_by_resps('
4037 || l_err_loc || '), ' || SQLERRM);
4038
4039 END secure_zone_by_resps;
4040
4041
4042 -- RESPONSIBILITY AND REALMS HELPER METHODS
4043 --------------------------------------------
4044
4045 -- Returns a map of item sources to the responsibilities that can access
4046 -- them via Item Source Realms.
4047 --
4048 -- NOTE: This requires that g_resp_with_isrc_realms global variable be
4049 -- populated. This is done via populate_resp_with_isrc_realms().
4050 --
4051 PROCEDURE populate_isrcs_to_resp_map
4052 IS
4053 l_item_source_ids ICX_TBL_NUMBER;
4054 l_resp_list_ids ICX_TBL_NUMBER;
4055
4056 l_api_name CONSTANT VARCHAR2(30) := 'populate_isrcs_to_resp_map';
4057 l_err_loc PLS_INTEGER;
4058 l_isrc_key NUMBER;
4059 BEGIN
4060
4061 l_err_loc := 0;
4062
4063 -- first, initializing the mapping by clearing out all the elements;
4064 -- NOTE: this initialization was moved to the initialize method
4065
4066 FOR i in 1..g_resp_with_isrc_realms.COUNT LOOP
4067
4068 l_item_source_ids := get_realm_values_for_resp(g_resp_with_isrc_realms(i),
4069 'ICX_POR_ITEM_SOURCE_ID');
4070
4071 l_err_loc := 100;
4072
4073 FOR j in 1..l_item_source_ids.COUNT LOOP
4074
4075 IF (g_item_sources_to_resp_map.EXISTS(l_item_source_ids(j))) THEN
4076
4077 l_resp_list_ids := g_item_sources_to_resp_map(l_item_source_ids(j));
4078 l_err_loc := 200;
4079
4080 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4081 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4082 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4083 'Found existing list for Item Source '||l_item_source_ids(j));
4084 END IF;
4085
4086 ELSE
4087
4088 l_resp_list_ids := ICX_TBL_NUMBER();
4089 l_err_loc := 300;
4090
4091 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4092 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4093 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4094 'A new list created for Item Source '||l_item_source_ids(j));
4095 END IF;
4096
4097 END IF;
4098
4099 l_resp_list_ids.extend;
4100 l_resp_list_ids(l_resp_list_ids.count) := g_resp_with_isrc_realms(i);
4101 g_item_sources_to_resp_map(l_item_source_ids(j)) := l_resp_list_ids;
4102
4103 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4104 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4105 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4106 'Appending resp '||g_resp_with_isrc_realms(i));
4107 END IF;
4108
4109 l_err_loc := 400;
4110
4111 END LOOP;
4112
4113 END LOOP;
4114
4115 -- note the final structure constructed
4116 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4117
4118 l_isrc_key := g_item_sources_to_resp_map.FIRST();
4119
4120 WHILE (l_isrc_key IS NOT NULL) LOOP
4121
4122 l_resp_list_ids := g_item_sources_to_resp_map(l_isrc_key);
4123
4124 FOR i IN 1..l_resp_list_ids.COUNT LOOP
4125 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4126 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4127 'Item Source '||l_isrc_key||' accessible via realms to resp '||
4128 l_resp_list_ids(i));
4129 END LOOP;
4130
4131 l_isrc_key := g_item_sources_to_resp_map.NEXT(l_isrc_key);
4132
4133 END LOOP;
4134
4135 END IF;
4136
4137 l_err_loc := 500;
4138
4139 EXCEPTION
4140 WHEN OTHERS THEN
4141 RAISE_APPLICATION_ERROR
4142 (-20000,
4143 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_item_sources_to_resp_map('
4144 || l_err_loc || '), ' || SQLERRM);
4145
4146 END populate_isrcs_to_resp_map;
4147
4148
4149 -- Returns a list of the securing attribute (realms) values for the
4150 -- 'RT_CATEGORY_ID' securing attribute. This method will catch globally
4151 -- the categories that responsibilities can access, as this may be queried
4152 -- many times during migration.
4153 --
4154 -- Values are stored globally in g_resp_to_categories_map. If no values
4155 -- are found there, then get_realm_values_for_resp() is used to query for them.
4156 --
4157 -- @param IN The responsibility whose realms values are desired.
4158 --
4159 -- @return The list of realms values for this responsibility and realm type.
4160 --
4161 FUNCTION get_catr_realm_values_cached
4162 (
4163 p_resp_id IN NUMBER
4164 )
4165 RETURN ICX_TBL_NUMBER
4166 IS
4167 l_realm_values ICX_TBL_NUMBER;
4168 l_api_name CONSTANT VARCHAR2(30) := 'get_catr_realm_values_cached';
4169 l_err_loc PLS_INTEGER;
4170 BEGIN
4171
4172 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4173 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4174 'p_resp_id='||p_resp_id);
4175 END IF;
4176
4177 l_err_loc := 0;
4178
4179 IF (g_resp_to_categories_map.EXISTS(p_resp_id)) THEN
4180
4181 l_realm_values := g_resp_to_categories_map(p_resp_id);
4182 l_err_loc := 100;
4183
4184 -- for statement-level logging, note that the values were cached
4185 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4186 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4187 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4188 'Category realm values for resp '||p_resp_id||
4189 ' was already queried, returning cached values.');
4190 END IF;
4191
4192 ELSE
4193
4194 -- query up the values
4195 l_realm_values := get_realm_values_for_resp(p_resp_id, 'RT_CATEGORY_ID');
4196 l_err_loc := 200;
4197
4198 -- store them for future reference
4199 g_resp_to_categories_map(p_resp_id) := l_realm_values;
4200 l_err_loc := 300;
4201
4202 END IF;
4203
4204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4205 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4206 END IF;
4207
4208 RETURN l_realm_values;
4209
4210 EXCEPTION
4211 WHEN OTHERS THEN
4212 RAISE_APPLICATION_ERROR
4213 (-20000,
4214 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_catr_realm_values_cached('
4215 || l_err_loc || '), ' || SQLERRM);
4216
4217 END get_catr_realm_values_cached;
4218
4219
4220 -- Returns a list of the securing attribute (realms) values
4221 -- based on the given realm type for the given responsibility.
4222 --
4223 -- @param IN The responsibility whose realms values are desired.
4224 -- @param IN The realm type for which realms values are desired:
4225 -- (1) RT_CATEGORY_ID
4226 -- (2) ICX_POR_ITEM_SOURCE_ID
4227 --
4228 -- @return The list of realms values for this responsibility and realm type.
4229 --
4230 FUNCTION get_realm_values_for_resp
4231 (
4232 p_resp_id IN NUMBER,
4233 p_realm_type IN VARCHAR2
4234 )
4235 RETURN ICX_TBL_NUMBER
4236 IS
4237 l_realm_values ICX_TBL_NUMBER;
4238 l_api_name CONSTANT VARCHAR2(30) := 'get_realm_values_for_resp';
4239 l_err_loc PLS_INTEGER;
4240 BEGIN
4241
4242 l_err_loc := 0;
4243
4244 SELECT akrsav.number_value as realm_value
4245 BULK COLLECT INTO l_realm_values
4246 FROM ak_resp_security_attr_values akrsav
4247 WHERE akrsav.responsibility_id = p_resp_id
4248 AND akrsav.attribute_application_id = 178
4249 AND akrsav.attribute_code = p_realm_type
4250 AND akrsav.number_value IS NOT NULL
4251
4252 UNION ALL
4253
4254 SELECT realmcomps.realm_component_value as realm_value
4255 FROM ak_resp_security_attr_values akrsav,
4256 icx_por_realms realms, icx_por_realm_components realmcomps
4257 WHERE akrsav.responsibility_id = p_resp_id
4258 AND akrsav.attribute_code = 'ICX_POR_REALM_ID'
4259 AND akrsav.attribute_application_id = 178
4260 AND akrsav.number_value = realms.realm_id
4261 AND realms.ak_attribute_code = p_realm_type
4262 AND realms.realm_id = realmcomps.realm_id
4263 AND realmcomps.realm_component_value IS NOT NULL;
4264
4265 -- note how many realm values were found
4266 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4267 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4268 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4269 p_realm_type||' realm values for resp '||p_resp_id||', count = '||
4270 l_realm_values.COUNT);
4271 END IF;
4272
4273 l_err_loc := 100;
4274
4275 -- for statement-level logging, log all values
4276 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4277 FOR i IN 1..l_realm_values.COUNT LOOP
4278 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4279 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4280 i||'.'||l_realm_values(i));
4281 END LOOP;
4282 END IF;
4283
4284 RETURN l_realm_values;
4285
4286 EXCEPTION
4287 WHEN OTHERS THEN
4288 RAISE_APPLICATION_ERROR
4289 (-20000,
4290 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_realm_values_for_resp('
4291 || l_err_loc || '), ' || SQLERRM);
4292
4293 END get_realm_values_for_resp;
4294
4295
4296 -- From a given list of responsbilities with realms, this method creates a new
4297 -- list that minus those responsibilities that do not have any CATEGORY realm
4298 -- values. This is useful when a responsibility has category realms enabled,
4299 -- but no actual categories are tied to this responsibility. In this case,
4300 -- this responsibility can be skipped when migrating local content as
4301 -- no local content zone should be created, as no categories are accessible.
4302 --
4303 -- @param IN The list of responsibilities with realms.
4304 --
4305 -- @return List of responsibilities with realms and have categories values.
4306 --
4307 FUNCTION filter_out_no_category_resps
4308 (
4309 p_resp_with_realms_ids IN ICX_TBL_NUMBER
4310 )
4311 RETURN ICX_TBL_NUMBER
4312 IS
4313 l_resp_with_realm_values_ids ICX_TBL_NUMBER;
4314 l_category_ids ICX_TBL_NUMBER;
4315
4316 l_api_name CONSTANT VARCHAR2(30) := 'filter_out_no_category_resps';
4317 l_err_loc PLS_INTEGER;
4318 BEGIN
4319
4320 l_err_loc := 0;
4321
4322 -- initialize as BULK COLLECT will always initialize variables, so keep this
4323 -- consistent so we can check COUNT rather than NULL to determine emptyness
4324 l_resp_with_realm_values_ids := ICX_TBL_NUMBER();
4325
4326 FOR i IN 1..p_resp_with_realms_ids.COUNT LOOP
4327
4328 l_category_ids :=
4329 get_catr_realm_values_cached(p_resp_with_realms_ids(i));
4330
4331 l_err_loc := 100;
4332
4333 IF (l_category_ids.COUNT > 0) THEN
4334
4335 l_resp_with_realm_values_ids.extend;
4336 l_resp_with_realm_values_ids(l_resp_with_realm_values_ids.COUNT) :=
4337 p_resp_with_realms_ids(i);
4338
4339 l_err_loc := 200;
4340
4341 END IF;
4342
4343 END LOOP;
4344
4345 -- note how many responsibilities without realms were found
4346 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4347 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4348 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4349 'Resps after filtering out those without Category values, count = '||
4350 l_resp_with_realm_values_ids.COUNT);
4351 END IF;
4352
4353 l_err_loc := 300;
4354
4355 -- for statement-level logging, log all responsibilties
4356 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4357 FOR i IN 1..l_resp_with_realm_values_ids.COUNT LOOP
4358 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4359 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4360 i||'.'||l_resp_with_realm_values_ids(i));
4361 END LOOP;
4362 END IF;
4363
4364 RETURN l_resp_with_realm_values_ids;
4365
4366 EXCEPTION
4367 WHEN OTHERS THEN
4368 RAISE_APPLICATION_ERROR
4369 (-20000,
4370 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.filter_out_no_category_resps('
4371 || l_err_loc || '), ' || SQLERRM);
4372
4373 END filter_out_no_category_resps;
4374
4375
4376 -- LARGE RESPONSIBILITY WITH REALMS QUERIES
4377 -------------------------------------------
4378
4379 -- Returns a list of all responsibilites with the given realm type
4380 -- of realms.
4381 --
4382 -- @param IN The realm type to restrict the list of responsibilities.
4383 -- @return The list of responsibilities with the given realm type.
4384 --
4385 FUNCTION get_all_resps_with_realms
4386 (
4387 p_realm_type IN VARCHAR2
4388 )
4389 RETURN ICX_TBL_NUMBER
4390 IS
4391 l_all_resp_with_realms_ids ICX_TBL_NUMBER;
4392 l_api_name CONSTANT VARCHAR2(30) := 'get_all_resps_with_realms';
4393 l_err_loc PLS_INTEGER;
4394 BEGIN
4395
4396 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4397 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4398 'p_realm_type='||p_realm_type);
4399 END IF;
4400
4401 l_err_loc := 0;
4402
4403 SELECT distinct resp.responsibility_id
4404 BULK COLLECT INTO l_all_resp_with_realms_ids
4405 FROM fnd_responsibility resp, ak_resp_security_attributes arsa
4406 WHERE resp.application_id IN (177, 178, 201, 396, 426)
4407 AND resp.responsibility_id = arsa.responsibility_id
4408 AND arsa.attribute_application_id = 178
4409 AND (arsa.attribute_code = p_realm_type
4410 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4411 and not exists (
4412 select null
4413 from ak_resp_security_attr_values akrsav
4414 where akrsav.attribute_code = 'ICX_POR_REALM_ID'
4415 and akrsav.responsibility_id = arsa.responsibility_id
4416 and akrsav.attribute_application_id = 178))
4417 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4418 and exists (
4419 select null
4420 from ak_resp_security_attr_values akrsav,
4421 icx_por_realms realms
4422 where akrsav.number_value = realms.realm_id
4423 and akrsav.attribute_code = 'ICX_POR_REALM_ID'
4424 and realms.ak_attribute_code = p_realm_type
4425 and akrsav.responsibility_id = arsa.responsibility_id
4426 and akrsav.attribute_application_id = 178)));
4427
4428 -- note how many responsibilities with realms were found
4429 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4430 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4431 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4432 'All Resps With '||p_realm_type||' Realms, count = '||
4433 l_all_resp_with_realms_ids.COUNT);
4434 END IF;
4435
4436 -- for statement-level logging, log all responsibilties
4437 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4438 FOR i IN 1..l_all_resp_with_realms_ids.COUNT LOOP
4439 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4440 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4441 i||'.'||l_all_resp_with_realms_ids(i));
4442 END LOOP;
4443 END IF;
4444
4445 l_err_loc := 100;
4446
4447 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4448 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4449 END IF;
4450
4451 RETURN l_all_resp_with_realms_ids;
4452
4453 EXCEPTION
4454 WHEN OTHERS THEN
4455 RAISE_APPLICATION_ERROR
4456 (-20000,
4457 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_all_resps_with_realms('
4458 || l_err_loc || '), ' || SQLERRM);
4459
4460 END get_all_resps_with_realms;
4461
4462
4463 -- Returns a list of all responsibilites without the given realm type
4464 -- of realms.
4465 --
4466 -- @param IN The realm type to restrict the list of responsibilities.
4467 -- @return The list of responsibilities without the given realm type.
4468 --
4469 FUNCTION get_all_resps_without_realms
4470 (
4471 p_realm_type IN VARCHAR2
4472 )
4473 RETURN ICX_TBL_NUMBER
4474 IS
4475 l_all_resp_without_realms_ids ICX_TBL_NUMBER;
4476 l_api_name CONSTANT VARCHAR2(30) := 'get_all_resps_without_realms';
4477 l_err_loc PLS_INTEGER;
4478 BEGIN
4479
4480 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4481 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4482 'p_realm_type='||p_realm_type);
4483 END IF;
4484
4485 l_err_loc := 0;
4486
4487 SELECT distinct resp.RESPONSIBILITY_ID
4488 BULK COLLECT INTO l_all_resp_without_realms_ids
4489 FROM FND_RESPONSIBILITY resp
4490 WHERE resp.application_id IN (177, 178, 201, 396, 426)
4491 AND NOT EXISTS
4492 (SELECT 1
4493 FROM ak_resp_security_attributes arsa
4494 WHERE arsa.responsibility_id = resp.RESPONSIBILITY_ID
4495 AND arsa.attribute_application_id = 178
4496 AND (arsa.attribute_code = p_realm_type
4497 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4498 and not exists (
4499 select null
4500 from ak_resp_security_attr_values akrsav
4501 where akrsav.attribute_code = 'ICX_POR_REALM_ID'
4502 and akrsav.responsibility_id = arsa.responsibility_id
4503 and akrsav.attribute_application_id = 178))
4504 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4505 and exists (
4506 select null
4507 from ak_resp_security_attr_values akrsav,
4508 icx_por_realms realms
4509 where akrsav.number_value = realms.realm_id
4510 and akrsav.attribute_code = 'ICX_POR_REALM_ID'
4511 and realms.ak_attribute_code = p_realm_type
4512 and akrsav.responsibility_id = arsa.responsibility_id
4513 and akrsav.attribute_application_id = 178))));
4514
4515 -- note how many responsibilities without realms were found
4516 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4517 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4518 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4519 'All Resps Without '||p_realm_type||' Realms, count = '||
4520 l_all_resp_without_realms_ids.COUNT);
4521 END IF;
4522
4523 -- for statement-level logging, log all responsibilties
4524 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4525 FOR i IN 1..l_all_resp_without_realms_ids.COUNT LOOP
4526 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4527 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4528 i||'.'||l_all_resp_without_realms_ids(i));
4529 END LOOP;
4530 END IF;
4531
4532 l_err_loc := 100;
4533
4534 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4535 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4536 END IF;
4537
4538 RETURN l_all_resp_without_realms_ids;
4539
4540 EXCEPTION
4541 WHEN OTHERS THEN
4542 RAISE_APPLICATION_ERROR
4543 (-20000,
4544 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_all_resps_without_realms('
4545 || l_err_loc || '), ' || SQLERRM);
4546
4547 END get_all_resps_without_realms;
4548
4549
4550 -- Returns a list of all responsibilites (that via their MO Operating Unit
4551 -- profile that determines which Operating Unit they are tied to) with the
4552 -- given realm type of realms that can access a given old pre-R12 store
4553 -- (that is, through any operating unit restrictions). If
4554 -- old pre-R12 store is accessible by All Operating Units (-2), then
4555 -- all responsibilities with realms (across all OUs) will be returned.
4556 --
4557 -- @param IN The realm type to restrict the list of responsibilities.
4558 -- @param IN The old pre-R12 store that the responsibilities can access.
4559 -- @return The list of responsibilities with the given realm type,
4560 -- and via their Operating Unit profile, can access the
4561 -- given store (which is secured by Operating Units).
4562 --
4563 FUNCTION get_resps_with_realms_store
4564 (
4565 p_realm_type IN VARCHAR2,
4566 p_old_store_id IN NUMBER
4567 )
4568 RETURN ICX_TBL_NUMBER
4569 IS
4570 l_resp_with_realms_ids ICX_TBL_NUMBER;
4571 l_api_name CONSTANT VARCHAR2(30) := 'get_resps_with_realms_store';
4572 l_err_loc PLS_INTEGER;
4573 BEGIN
4574
4575 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4576 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4577 'p_realm_type='||p_realm_type||',p_old_store_id='||p_old_store_id);
4578 END IF;
4579
4580 l_err_loc := 0;
4581
4582 IF ((p_old_store_id IS NULL) OR
4583 (g_store_security_flags(p_old_store_id) = 'ALL_USERS')) THEN
4584
4585 IF (p_realm_type = 'RT_CATEGORY_ID') THEN
4586
4587 l_resp_with_realms_ids := g_resp_with_category_realms; -- all orgs
4588 l_err_loc := 100;
4589
4590 ELSE -- IF (p_realm_type = 'ICX_POR_ITEM_SOURCE_ID') THEN
4591
4592 l_resp_with_realms_ids := g_resp_with_isrc_realms; -- all orgs
4593 l_err_loc := 200;
4594
4595 END IF;
4596
4597 ELSE
4598
4599 l_err_loc := 300;
4600
4601 -- get all the responsibilties with realms that can access this store
4602 SELECT distinct resp.responsibility_id
4603 BULK COLLECT INTO l_resp_with_realms_ids
4604 FROM fnd_responsibility resp,
4605 fnd_profile_option_values resp_profile,
4606 fnd_profile_option_values app_profile,
4607 icx_cat_store_org_assignments orgs,
4608 ak_resp_security_attributes arsa
4609 WHERE resp.application_id in (177, 178, 201, 396, 426)
4610 AND app_profile.profile_option_id(+) = g_ou_profile_id
4611 AND app_profile.level_id(+) = 10002
4612 AND app_profile.level_value(+) = resp.application_id
4613 AND resp_profile.profile_option_id(+) = g_ou_profile_id
4614 AND resp_profile.level_id(+) = 10003
4615 AND resp_profile.level_value(+) = resp.responsibility_id
4616 AND nvl(resp_profile.profile_option_value,
4617 nvl(app_profile.profile_option_value,
4618 g_site_ou_profile_value)) = orgs.org_id
4619 AND orgs.store_id = p_old_store_id
4620 AND arsa.responsibility_id = resp.RESPONSIBILITY_ID
4621 AND arsa.attribute_application_id = 178
4622 AND (arsa.attribute_code = p_realm_type
4623 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4624 and not exists (
4625 select null
4626 from ak_resp_security_attr_values akrsav
4627 where akrsav.attribute_code = 'ICX_POR_REALM_ID'
4628 and akrsav.responsibility_id = arsa.responsibility_id
4629 and akrsav.attribute_application_id = 178))
4630 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4631 and exists (
4632 select null
4633 from ak_resp_security_attr_values akrsav,
4634 icx_por_realms realms
4635 where akrsav.number_value = realms.realm_id
4636 and akrsav.attribute_code = 'ICX_POR_REALM_ID'
4637 and realms.ak_attribute_code = p_realm_type
4638 and akrsav.responsibility_id = arsa.responsibility_id
4639 and akrsav.attribute_application_id = 178)));
4640
4641 l_err_loc := 400;
4642
4643 -- for statement-level logging, log all responsibilties
4644 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4645 FOR i IN 1..l_resp_with_realms_ids.COUNT LOOP
4646 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4647 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4648 i||'.'||l_resp_with_realms_ids(i));
4649 END LOOP;
4650 END IF;
4651
4652 END IF; -- check if it should be all orgs
4653
4654 -- note how many responsibilities without realms were found
4655 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4656 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4657 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4658 'Resps With '||p_realm_type||' Realms for Store '||p_old_store_id||
4659 ', count = '||l_resp_with_realms_ids.COUNT);
4660 END IF;
4661
4662 l_err_loc := 500;
4663
4664 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4665 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4666 END IF;
4667
4668 RETURN l_resp_with_realms_ids;
4669
4670 EXCEPTION
4671 WHEN OTHERS THEN
4672 RAISE_APPLICATION_ERROR
4673 (-20000,
4674 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_resps_with_realms_store('
4675 || l_err_loc || '), ' || SQLERRM);
4676
4677 END get_resps_with_realms_store;
4678
4679
4680 -- Returns a list of all responsibilites (that via their MO Operating Unit
4681 -- profile that determines which Operating Unit they are tied to) without the
4682 -- given realm type of realms that can access a given old pre-R12 store
4683 -- (that is, through any operating unit restrictions). If
4684 -- old pre-R12 store is accessible by All Operating Units (-2), then
4685 -- all responsibilities without realms (across all OUs) will be returned.
4686 --
4687 -- @param IN The realm type to restrict the list of responsibilities.
4688 -- @param IN The old pre-R12 store that the responsibilities can access.
4689 -- @return The list of responsibilities without the given realm type,
4690 -- and via their Operating Unit profile, can access the
4691 -- given store (which is secured by Operating Units).
4692 --
4693 FUNCTION get_resps_without_realms_store
4694 (
4695 p_realm_type IN VARCHAR2,
4696 p_old_store_id IN NUMBER
4697 )
4698 RETURN ICX_TBL_NUMBER
4699 IS
4700 l_resp_without_realms_ids ICX_TBL_NUMBER;
4701 l_api_name CONSTANT VARCHAR2(30) := 'get_resps_without_realms_store';
4702 l_err_loc PLS_INTEGER;
4703 BEGIN
4704
4705 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4706 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name,
4707 'p_realm_type='||p_realm_type||',p_old_store_id='||p_old_store_id);
4708 END IF;
4709
4710 l_err_loc := 0;
4711
4712 IF ((p_old_store_id IS NULL) OR
4713 (g_store_security_flags(p_old_store_id) = 'ALL_USERS')) THEN
4714
4715 IF (p_realm_type = 'RT_CATEGORY_ID') THEN
4716
4717 l_resp_without_realms_ids := g_resp_without_category_realms; -- all orgs
4718 l_err_loc := 100;
4719
4720 ELSE -- IF (p_realm_type = 'ICX_POR_ITEM_SOURCE_ID') THEN
4721
4722 l_resp_without_realms_ids := g_resp_without_isrc_realms; -- all orgs
4723 l_err_loc := 200;
4724
4725 END IF;
4726
4727 ELSE
4728
4729 l_err_loc := 300;
4730
4731 -- get all the responsibilties without realms that can access this store
4732 SELECT distinct resp.responsibility_id
4733 BULK COLLECT INTO l_resp_without_realms_ids
4734 FROM fnd_responsibility resp,
4735 fnd_profile_option_values resp_profile,
4736 fnd_profile_option_values app_profile,
4737 icx_cat_store_org_assignments orgs
4738 WHERE resp.application_id in (177, 178, 201, 396, 426)
4739 AND app_profile.profile_option_id(+) = g_ou_profile_id
4740 AND app_profile.level_id(+) = 10002
4741 AND app_profile.level_value(+) = resp.application_id
4742 AND resp_profile.profile_option_id(+) = g_ou_profile_id
4743 AND resp_profile.level_id(+) = 10003
4744 AND resp_profile.level_value(+) = resp.responsibility_id
4745 AND nvl(resp_profile.profile_option_value,
4746 nvl(app_profile.profile_option_value,
4747 g_site_ou_profile_value)) = orgs.org_id
4748 AND orgs.store_id = p_old_store_id
4749 AND NOT EXISTS
4750 (SELECT 1
4751 FROM ak_resp_security_attributes arsa
4752 WHERE arsa.responsibility_id = resp.RESPONSIBILITY_ID
4753 AND arsa.attribute_application_id = 178
4754 AND (arsa.attribute_code = p_realm_type
4755 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4756 and not exists (
4757 select null
4758 from ak_resp_security_attr_values akrsav
4759 where akrsav.attribute_code = 'ICX_POR_REALM_ID'
4760 and akrsav.responsibility_id = arsa.responsibility_id
4761 and akrsav.attribute_application_id = 178))
4762 or (arsa.attribute_code = 'ICX_POR_REALM_ID'
4763 and exists (
4764 select null
4765 from ak_resp_security_attr_values akrsav,
4766 icx_por_realms realms
4767 where akrsav.number_value = realms.realm_id
4768 and akrsav.attribute_code = 'ICX_POR_REALM_ID'
4769 and realms.ak_attribute_code = p_realm_type
4770 and akrsav.responsibility_id = arsa.responsibility_id
4771 and akrsav.attribute_application_id = 178))));
4772
4773 l_err_loc := 400;
4774
4775 -- for statement-level logging, log all responsibilties
4776 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4777 FOR i IN 1..l_resp_without_realms_ids.COUNT LOOP
4778 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
4779 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4780 i||'.'||l_resp_without_realms_ids(i));
4781 END LOOP;
4782 END IF;
4783
4784 END IF; -- check if it should be all orgs
4785
4786 -- note how many responsibilities without realms were found
4787 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4788 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
4789 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
4790 'Resps Without '||p_realm_type||' Realms for Store '||p_old_store_id||
4791 ', count = '||l_resp_without_realms_ids.COUNT);
4792 END IF;
4793
4794 l_err_loc := 500;
4795
4796 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4797 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name, 'End');
4798 END IF;
4799
4800 RETURN l_resp_without_realms_ids;
4801
4802 EXCEPTION
4803 WHEN OTHERS THEN
4804 RAISE_APPLICATION_ERROR
4805 (-20000,
4806 'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.get_resps_without_realms_store('
4807 || l_err_loc || '), ' || SQLERRM);
4808
4809 END get_resps_without_realms_store;
4810
4811 END ICX_CAT_CNTNT_SCRTY_UPG_PVT;
4812
[Home] [Help]