[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO exists_non_seeded_data
FROM icx_cat_content_zones_b z, icx_cat_shop_stores_b s
WHERE (z.zone_id NOT IN (1,2) OR s.store_id NOT IN (1,2))
AND rownum = 1;
SELECT 1
INTO exists_catalogs_data
FROM all_objects
WHERE owner = l_icx_schema_name
AND object_name = 'ICX_POR_ITEM_SOURCES';
SELECT 1
INTO exists_catalogs_data
FROM icx_por_item_sources
WHERE rownum = 1;
g_store_security_flags.DELETE;
g_catalog_supplier_flags.DELETE;
g_stores_map.DELETE;
g_item_sources_to_resp_map.DELETE;
g_resp_to_categories_map.DELETE;
SELECT p.profile_option_id, v.profile_option_value
INTO g_ou_profile_id, g_site_ou_profile_value
FROM fnd_profile_options p, fnd_profile_option_values v
WHERE p.profile_option_name = 'ORG_ID'
AND p.profile_option_id = v.profile_option_id(+)
AND p.application_id = v.application_id
AND v.level_id(+) = 10001;
SELECT nvl(v.profile_option_value, 'N')
INTO g_site_approved_pricing
FROM fnd_profile_options p, fnd_profile_option_values v
WHERE p.application_id = 178
AND p.profile_option_name = 'POR_APPROVED_PRICING_ONLY'
AND p.profile_option_id = v.profile_option_id
AND p.application_id = v.application_id
AND v.level_id = 10001;
INSERT INTO icx_cat_store_contents
(store_id, content_id, content_type, sequence, display_always_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT 1, 1, 'CONTENT_ZONE', 1, NULL, fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
FROM dual;
INSERT INTO icx_cat_store_contents
(store_id, content_id, content_type, sequence, display_always_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT 2, 10000000, 'SMART_FORM', 1, 'N', fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
FROM dual;
DELETE FROM icx_cat_content_zones_tl
WHERE zone_id = 2
AND EXISTS (SELECT 1
FROM icx_por_item_sources_tl
WHERE item_source_id = 10000001);
INSERT INTO icx_cat_content_zones_tl
(zone_id, language, source_lang, name, description,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT 2, language, source_lang, item_source_name, description,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_por_item_sources_tl
WHERE item_source_id = 10000001;
DELETE FROM icx_cat_shop_stores_b
WHERE store_id = 1;
DELETE FROM icx_cat_shop_stores_tl
WHERE store_id = 1;
SELECT 1
INTO l_has_main_store
FROM icx_cat_stores_b
WHERE store_id = 0;
UPDATE icx_cat_shop_stores_b
SET sequence =
(SELECT sequence_number FROM icx_cat_stores_b
WHERE store_id = p_old_id)
WHERE store_id = p_new_id;
DELETE FROM icx_cat_shop_stores_tl
WHERE store_id = p_new_id
AND EXISTS (SELECT 1 FROM icx_cat_stores_tl WHERE store_id = p_old_id);
INSERT INTO icx_cat_shop_stores_tl
(store_id, language, source_lang, name, description, long_description,
image, created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_new_id, language, source_lang, store_name,
short_description, long_description, image_location,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_cat_stores_tl
WHERE store_id = p_old_id;
DELETE FROM icx_cat_content_zones_b
WHERE zone_id = 1;
DELETE FROM icx_cat_content_zones_tl
WHERE zone_id = 1;
DELETE FROM icx_cat_store_contents
WHERE content_id = 1;
SELECT zone_id
INTO l_promoted_zone_id
FROM icx_cat_content_zones_b z, icx_cat_store_contents c
WHERE z.supplier_attribute_action_flag = 'INCLUDE_ALL'
AND z.category_attribute_action_flag = 'INCLUDE_ALL'
AND z.zone_id = c.content_id
AND c.store_id = 1;
UPDATE icx_cat_content_zones_b
SET zone_id = 1
WHERE zone_id = l_promoted_zone_id;
UPDATE icx_cat_content_zones_tl
SET zone_id = 1
WHERE zone_id = l_promoted_zone_id;
UPDATE icx_cat_secure_contents
SET content_id = 1
WHERE content_id = l_promoted_zone_id;
UPDATE icx_cat_store_contents
SET content_id = 1
WHERE content_id = l_promoted_zone_id;
SELECT sources.item_source_id, sc.store_id, ICX_CAT_CONTENT_ZONES_S.NEXTVAL
BULK COLLECT INTO l_catalog_ids, l_store_ids, l_new_zone_ids
FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
WHERE sources.type = 'LOCAL'
AND sources.item_source_id = sc.item_source_id(+);
SELECT sources.item_source_id, sc.store_id, ICX_CAT_CONTENT_ZONES_S.NEXTVAL
BULK COLLECT INTO l_item_source_ids, l_store_ids, l_new_zone_ids
FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
WHERE sources.type IN ('EXTERNAL', 'DISTSRCH', 'INFO')
AND sources.item_source_id = sc.item_source_id(+);
update_exchange_punchouts();
update_exchange_punchouts();
SELECT templates.template_id, sc.store_id
BULK COLLECT INTO l_template_ids, l_old_store_ids
FROM icx_cat_store_catalogs sc, por_noncat_templates_all_b templates,
icx_cat_store_org_assignments orgs
WHERE templates.template_id = sc.item_source_id
AND sc.store_id = orgs.store_id
AND orgs.org_id IN (templates.org_id, -2)
AND templates.template_id <> 10000000;
SELECT 1
INTO l_exists_local_catalogs
FROM dual
WHERE exists (SELECT item_source_id
FROM icx_por_item_sources
WHERE type = 'LOCAL');
SELECT 1
INTO l_exists_item_sources
FROM dual
WHERE exists (SELECT item_source_id
FROM icx_por_item_sources
WHERE type IN ('EXTERNAL', 'DISTSRCH', 'INFO'));
SELECT distinct(stores.store_id),
decode(orgs.org_id, -2, 'ALL_USERS', 'OU_SECURED')
BULK COLLECT INTO l_store_ids, l_security_flags
FROM icx_cat_stores_b stores, icx_cat_store_org_assignments orgs
WHERE stores.store_id = orgs.store_id;
SELECT distinct sources.item_source_id,
nvl2(details.supplier_id, 'INCLUDE',
DECODE(sources.protocol_supported,
'LOCAL_BASE', 'INCLUDE_ALL', 'EXCLUDE_ALL'))
BULK COLLECT INTO l_sources_ids, l_supplier_flags
FROM icx_por_item_sources sources, icx_cat_item_src_details details
WHERE sources.type = 'LOCAL'
AND sources.item_source_id = details.item_source_id(+);
SELECT distinct operator_id
BULK COLLECT INTO l_operator_ids
FROM icx_por_item_sources
WHERE operator_id IS NOT NULL;
SELECT sources.item_source_id, sources.type, sc.store_id
BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
WHERE sources.item_source_id = sc.item_source_id(+)
AND sources.type <> 'CNTRCTR'
ORDER BY sc.store_id;
SELECT sources.item_source_id, sources.type, sc.store_id
BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
WHERE sources.item_source_id = sc.item_source_id(+)
AND sources.type IN ('EXTERNAL', 'DISTSRCH', 'INFO')
ORDER BY sc.store_id;
SELECT sources.item_source_id, sources.type, sc.store_id
BULK COLLECT INTO x_old_content_ids, x_content_types, x_old_store_ids
FROM icx_por_item_sources sources, icx_cat_store_catalogs sc
WHERE sources.item_source_id = sc.item_source_id(+)
AND sources.type = 'LOCAL'
ORDER BY sc.store_id;
SELECT store_id, ICX_CAT_SHOP_STORES_S.NEXTVAL
BULK COLLECT INTO l_old_store_ids, l_r12_store_ids
FROM icx_cat_stores_b
WHERE store_id NOT IN (0, 10000000);
INSERT INTO icx_cat_shop_stores_b
(store_id, sequence, local_content_first_flag, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT l_r12_store_ids(i), stores.sequence_number, 'Y', fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_stores_b stores
WHERE stores.store_id = l_old_store_ids(i);
INSERT INTO icx_cat_shop_stores_tl
(store_id, language, source_lang, name, description, long_description,
image, created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT l_r12_store_ids(i), stores_tl.language, stores_tl.source_lang,
stores_tl.store_name, stores_tl.short_description,
stores_tl.long_description, stores_tl.image_location, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_stores_tl stores_tl
WHERE stores_tl.store_id = l_old_store_ids(i);
INSERT INTO icx_cat_store_contents
(store_id, content_id, content_type, sequence, display_always_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_r12_store_id, p_zone_ids(i), 'CONTENT_ZONE',
sc.sequence_number, sc.display_always_flag,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_cat_store_catalogs sc
WHERE p_old_store_id IS NOT NULL
AND sc.store_id = p_old_store_id
AND sc.item_source_id = p_old_source_id;
INSERT INTO icx_cat_store_contents
(store_id, content_id, content_type, sequence, display_always_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_r12_store_ids(i), p_zone_ids(i), 'CONTENT_ZONE',
sc.sequence_number, sc.display_always_flag,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_cat_store_catalogs sc
WHERE p_old_store_ids(i) IS NOT NULL
AND sc.store_id = p_old_store_ids(i)
AND sc.item_source_id = p_old_source_ids(i);
INSERT INTO icx_cat_store_contents
(store_id, content_id, content_type, sequence, display_always_flag,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_r12_store_ids(i), p_smart_form_ids(i), 'SMART_FORM',
DECODE(default_template_flag, 'Y', 1, NULL),
sc.display_always_flag, fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_store_catalogs sc
WHERE sc.store_id = p_old_store_ids(i)
AND sc.item_source_id = p_smart_form_ids(i);
SELECT ICX_CAT_CONTENT_ZONES_S.NEXTVAL
INTO l_new_zone_ids(i)
FROM dual;
INSERT INTO icx_cat_content_zones_b
(zone_id, type, url, security_assignment_flag,
category_attribute_action_flag, supplier_attribute_action_flag,
items_without_supplier_flag, items_without_shop_catg_flag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
SELECT p_new_zone_ids(i), 'LOCAL', sources.url, p_security_flags(i),
p_category_flag, p_supplier_flags(i),
DECODE(g_site_approved_pricing, 'Y', 'N',
nvl(sources.include_internal_source_flag, 'Y')),
'N', fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_por_item_sources sources
WHERE sources.item_source_id = p_catalog_ids(i);
INSERT INTO icx_cat_content_zones_tl
(zone_id, language, source_lang, name, description, keywords, image,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_new_zone_ids(i), sources_tl.language, sources_tl.source_lang,
sources_tl.item_source_name, sources_tl.description,
sources_tl.ctx_keywords, sources.image_url, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_por_item_sources sources, icx_por_item_sources_tl sources_tl
WHERE sources.item_source_id = p_catalog_ids(i)
AND sources_tl.item_source_id = sources.item_source_id;
INSERT INTO icx_cat_zone_secure_attributes
(zone_id, securing_attribute, supplier_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT p_new_zone_ids(i), 'SUPPLIER', supplier_id, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_item_src_details
WHERE item_source_id = p_catalog_ids(i);
INSERT INTO icx_cat_content_zones_b
(zone_id, type, url, security_assignment_flag,
category_attribute_action_flag, supplier_attribute_action_flag,
items_without_supplier_flag, items_without_shop_catg_flag,
created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
SELECT p_new_zone_ids(i), 'LOCAL', zones.url, 'RESP_SECURED',
p_category_flag, 'INCLUDE_ALL',
DECODE(g_site_approved_pricing, 'Y', 'N',
zones.items_without_supplier_flag),
zones.items_without_shop_catg_flag, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_content_zones_b zones
WHERE zones.zone_id = 1;
INSERT INTO icx_cat_content_zones_tl
(zone_id, language, source_lang, name, description, keywords, image,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_new_zone_ids(i), zones_tl.language, zones_tl.source_lang,
zones_tl.name, zones_tl.description,
zones_tl.keywords, zones_tl.image, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_content_zones_tl zones_tl
WHERE zones_tl.zone_id = 1;
l_zones_to_insert ICX_TBL_NUMBER;
l_categories_to_insert ICX_TBL_NUMBER;
l_zones_to_insert := ICX_TBL_NUMBER();
l_categories_to_insert := ICX_TBL_NUMBER();
l_zones_to_insert.extend;
l_zones_to_insert(l_zones_to_insert.COUNT) := l_current_zone_id;
l_categories_to_insert.extend;
l_categories_to_insert(l_categories_to_insert.COUNT) := l_category_ids(j);
FORALL i IN 1..l_zones_to_insert.COUNT
INSERT INTO icx_cat_zone_secure_attributes
(zone_id, securing_attribute, ip_category_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT l_zones_to_insert(i), 'CATEGORY', l_categories_to_insert(i),
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM dual;
FOR i IN 1..l_zones_to_insert.COUNT LOOP
FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
'Zone '||l_zones_to_insert(i)||' secured by Category '||
l_categories_to_insert(i));
INSERT INTO icx_cat_content_zones_b
(zone_id, type, url, security_assignment_flag, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT p_new_zone_ids(i),
DECODE(type, 'EXTERNAL', 'PUNCHOUT',
'DISTSRCH', 'TRANSPARENT_PUNCHOUT',
'INFO', 'INFORMATIONAL'),
sources.url, p_security_flags(i),
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_por_item_sources sources
WHERE sources.item_source_id = p_item_source_ids(i);
INSERT INTO icx_cat_content_zones_tl
(zone_id, language, source_lang, name, description, keywords, image,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
SELECT p_new_zone_ids(i), sources_tl.language, sources_tl.source_lang,
sources_tl.item_source_name, sources_tl.description,
sources_tl.ctx_keywords, sources.image_url,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM icx_por_item_sources sources, icx_por_item_sources_tl sources_tl
WHERE sources.item_source_id = p_item_source_ids(i)
AND sources_tl.item_source_id = sources.item_source_id;
INSERT INTO icx_cat_punchout_zone_details
(zone_id, protocol_supported, user_name, company_name, company_number,
supplier_name, supplier_number, ecgateway_map_key1, vendor_id,
vendor_site_id, encoding, party_site_id, parent_zone_id, authenticated_key,
user_info_flag, lock_item_flag, retain_session_flag, operation_allowed,
negotiated_flag, created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
SELECT p_new_zone_ids(i),
DECODE(protocol_supported, 'EXCHANGE', 'EXCHANGE',
'XML_SUPP', 'XML_SUPPLIER',
'CXML_SUPP', 'CXML_SUPPLIER',
'DISTSRCH_SUPP', 'TRANSPARENT_SUPPLIER',
'DISTSRCH_EXCH', 'TRANSPARENT_EXCHANGE',
'VIA_EXCH', 'VIA_EXCHANGE'),
user_name, company_name, company_number, supplier_name,
supplier_number, key_1, vendor_id, vendor_site_id, encoding,
party_site_id, operator_id, authenticated_key, user_info_flag,
lock_item_flag, icx_session_servlet_flag, operation_allowed,
negotiated_by_preparer_flag, fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_por_item_sources
WHERE item_source_id = p_item_source_ids(i)
AND type IN ('EXTERNAL', 'DISTSRCH');
INSERT INTO icx_cat_zone_attributes
(zone_id, attribute_name, attribute_value, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT p_new_zone_ids(i), d.name, d.value, fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_por_item_sources s, icx_cat_item_src_details d
WHERE s.item_source_id = p_item_source_ids(i)
AND s.type = 'DISTSRCH'
AND s.item_source_id = d.item_source_id;
SELECT password
INTO l_encrypted_password
FROM icx_por_item_sources
WHERE item_source_id = p_item_source_ids(i);
PROCEDURE update_exchange_punchouts
IS
l_old_operator_ids ICX_TBL_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'update_exchange_punchouts';
SELECT distinct operator_id
BULK COLLECT INTO l_old_operator_ids
FROM icx_por_item_sources
WHERE operator_id IS NOT NULL;
UPDATE icx_cat_punchout_zone_details
SET parent_zone_id = l_new_parent_ids(i)
WHERE parent_zone_id = l_old_operator_ids(i);
'Exception at ICX_CAT_CNTNT_SCRTY_UPG_PVT.update_exchange_punchouts('
|| l_err_loc || '), ' || SQLERRM);
END update_exchange_punchouts;
INSERT INTO icx_cat_secure_contents
(content_id, org_id, secure_by, created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
SELECT p_new_zone_ids(i), oa.org_id, 'OPERATING_UNIT', fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
FROM icx_cat_store_org_assignments oa
WHERE p_old_store_ids(i) IS NOT NULL
AND oa.store_id = p_old_store_ids(i)
AND p_security_flags(i) = 'OU_SECURED';
INSERT INTO icx_cat_secure_contents
(content_id, responsibility_id, secure_by, created_by, creation_date,
last_updated_by, last_update_date, last_update_login)
SELECT p_new_zone_ids(i), p_resp_ids(i), 'RESPONSIBILITY',
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
fnd_global.login_id
FROM dual;
SELECT akrsav.number_value as realm_value
BULK COLLECT INTO l_realm_values
FROM ak_resp_security_attr_values akrsav
WHERE akrsav.responsibility_id = p_resp_id
AND akrsav.attribute_application_id = 178
AND akrsav.attribute_code = p_realm_type
AND akrsav.number_value IS NOT NULL
UNION ALL
SELECT realmcomps.realm_component_value as realm_value
FROM ak_resp_security_attr_values akrsav,
icx_por_realms realms, icx_por_realm_components realmcomps
WHERE akrsav.responsibility_id = p_resp_id
AND akrsav.attribute_code = 'ICX_POR_REALM_ID'
AND akrsav.attribute_application_id = 178
AND akrsav.number_value = realms.realm_id
AND realms.ak_attribute_code = p_realm_type
AND realms.realm_id = realmcomps.realm_id
AND realmcomps.realm_component_value IS NOT NULL;
SELECT distinct resp.responsibility_id
BULK COLLECT INTO l_all_resp_with_realms_ids
FROM fnd_responsibility resp, ak_resp_security_attributes arsa
WHERE resp.application_id IN (177, 178, 201, 396, 426)
AND resp.responsibility_id = arsa.responsibility_id
AND arsa.attribute_application_id = 178
AND (arsa.attribute_code = p_realm_type
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and not exists (
select null
from ak_resp_security_attr_values akrsav
where akrsav.attribute_code = 'ICX_POR_REALM_ID'
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and exists (
select null
from ak_resp_security_attr_values akrsav,
icx_por_realms realms
where akrsav.number_value = realms.realm_id
and akrsav.attribute_code = 'ICX_POR_REALM_ID'
and realms.ak_attribute_code = p_realm_type
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178)));
SELECT distinct resp.RESPONSIBILITY_ID
BULK COLLECT INTO l_all_resp_without_realms_ids
FROM FND_RESPONSIBILITY resp
WHERE resp.application_id IN (177, 178, 201, 396, 426)
AND NOT EXISTS
(SELECT 1
FROM ak_resp_security_attributes arsa
WHERE arsa.responsibility_id = resp.RESPONSIBILITY_ID
AND arsa.attribute_application_id = 178
AND (arsa.attribute_code = p_realm_type
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and not exists (
select null
from ak_resp_security_attr_values akrsav
where akrsav.attribute_code = 'ICX_POR_REALM_ID'
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and exists (
select null
from ak_resp_security_attr_values akrsav,
icx_por_realms realms
where akrsav.number_value = realms.realm_id
and akrsav.attribute_code = 'ICX_POR_REALM_ID'
and realms.ak_attribute_code = p_realm_type
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))));
SELECT distinct resp.responsibility_id
BULK COLLECT INTO l_resp_with_realms_ids
FROM fnd_responsibility resp,
fnd_profile_option_values resp_profile,
fnd_profile_option_values app_profile,
icx_cat_store_org_assignments orgs,
ak_resp_security_attributes arsa
WHERE resp.application_id in (177, 178, 201, 396, 426)
AND app_profile.profile_option_id(+) = g_ou_profile_id
AND app_profile.level_id(+) = 10002
AND app_profile.level_value(+) = resp.application_id
AND resp_profile.profile_option_id(+) = g_ou_profile_id
AND resp_profile.level_id(+) = 10003
AND resp_profile.level_value(+) = resp.responsibility_id
AND nvl(resp_profile.profile_option_value,
nvl(app_profile.profile_option_value,
g_site_ou_profile_value)) = orgs.org_id
AND orgs.store_id = p_old_store_id
AND arsa.responsibility_id = resp.RESPONSIBILITY_ID
AND arsa.attribute_application_id = 178
AND (arsa.attribute_code = p_realm_type
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and not exists (
select null
from ak_resp_security_attr_values akrsav
where akrsav.attribute_code = 'ICX_POR_REALM_ID'
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and exists (
select null
from ak_resp_security_attr_values akrsav,
icx_por_realms realms
where akrsav.number_value = realms.realm_id
and akrsav.attribute_code = 'ICX_POR_REALM_ID'
and realms.ak_attribute_code = p_realm_type
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178)));
SELECT distinct resp.responsibility_id
BULK COLLECT INTO l_resp_without_realms_ids
FROM fnd_responsibility resp,
fnd_profile_option_values resp_profile,
fnd_profile_option_values app_profile,
icx_cat_store_org_assignments orgs
WHERE resp.application_id in (177, 178, 201, 396, 426)
AND app_profile.profile_option_id(+) = g_ou_profile_id
AND app_profile.level_id(+) = 10002
AND app_profile.level_value(+) = resp.application_id
AND resp_profile.profile_option_id(+) = g_ou_profile_id
AND resp_profile.level_id(+) = 10003
AND resp_profile.level_value(+) = resp.responsibility_id
AND nvl(resp_profile.profile_option_value,
nvl(app_profile.profile_option_value,
g_site_ou_profile_value)) = orgs.org_id
AND orgs.store_id = p_old_store_id
AND NOT EXISTS
(SELECT 1
FROM ak_resp_security_attributes arsa
WHERE arsa.responsibility_id = resp.RESPONSIBILITY_ID
AND arsa.attribute_application_id = 178
AND (arsa.attribute_code = p_realm_type
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and not exists (
select null
from ak_resp_security_attr_values akrsav
where akrsav.attribute_code = 'ICX_POR_REALM_ID'
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))
or (arsa.attribute_code = 'ICX_POR_REALM_ID'
and exists (
select null
from ak_resp_security_attr_values akrsav,
icx_por_realms realms
where akrsav.number_value = realms.realm_id
and akrsav.attribute_code = 'ICX_POR_REALM_ID'
and realms.ak_attribute_code = p_realm_type
and akrsav.responsibility_id = arsa.responsibility_id
and akrsav.attribute_application_id = 178))));