[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oracle_username
FROM fnd_data_group_units_v dgrp,
fnd_responsibility resp
WHERE dgrp.application_id = resp.application_id
AND dgrp.data_group_id = resp.data_group_id
AND resp.responsibility_id = p_resp_id;
SELECT install_group_num
FROM fnd_oracle_userid
WHERE read_only_flag = 'U'
ORDER BY install_group_num;
SELECT NVL(multi_currency_flag, 'N') multi_currency_flag,
NVL(multi_lingual_flag, 'N') multi_lingual_flag
FROM fnd_product_groups;
SELECT oracle_username
FROM fnd_oracle_userid
WHERE (install_group_num = p_install_group_num
OR install_group_num = (SELECT MIN (install_group_num)
FROM fnd_oracle_userid
WHERE 1=DECODE(p_install_group_num,0,1,2)
AND read_only_flag = p_read_only_flag))
AND read_only_flag = p_read_only_flag;
SELECT COUNT(*)
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND object_name IN ('APPS_DDL', 'APPS_ARRAY_DDL')
AND status = 'VALID'
AND owner = UPPER (p_schema_name);
SELECT COUNT(*)
INTO l_count
FROM igi_sls_allocations
WHERE sls_allocation = p_table_name
AND sls_allocation_type = 'T'
AND sls_group_type = 'S'
AND date_disabled IS NULL
AND date_removed IS NULL;
SELECT COUNT(*)
INTO l_count
FROM igi_sls_allocations a,
igi_sls_allocations b
WHERE a.sls_group_type = 'S'
AND a.sls_allocation = b.sls_group
AND a.sls_allocation_type = 'P'
AND a.date_disabled IS NULL
AND a.date_removed IS NULL
AND b.sls_group_type = 'P'
AND b.sls_allocation = p_table_name
AND b.sls_allocation_type = 'T'
AND b.date_disabled IS NULL
AND b.date_removed IS NULL;
SELECT owner,
table_name,
sls_table_name,
date_enabled,
date_disabled,
date_removed,
date_security_applied,
date_object_created,
update_allowed,
NVL(optimise_sql,'N') optimise_sql
FROM igi_sls_secure_tables
WHERE date_security_applied IS NULL;
IF rt_c_get_sectab.update_allowed = 'Y'
THEN
l_policy_type := 'SELECT,UPDATE';
l_policy_type := 'SELECT';
UPDATE igi_sls_secure_tables
SET date_object_created = SYSDATE,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE table_name = rt_c_get_sectab.table_name
AND owner = rt_c_get_sectab.owner;
END IF; -- (Enabled / Disabled / Re-enabled / Deleted)
UPDATE igi_sls_secure_tables
SET date_security_applied = l_date_security_applied,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE table_name = rt_c_get_sectab.table_name
AND owner = rt_c_get_sectab.owner;
UPDATE igi_sls_secure_tables_audit a
SET a.date_security_applied = SYSDATE
WHERE a.date_security_applied IS NULL
AND ROWID = (SELECT MAX(ROWID) b
FROM igi_sls_secure_tables_audit b
WHERE a.table_name = b.table_name
AND a.owner = b.owner)
AND table_name = rt_c_get_sectab.table_name
AND owner = rt_c_get_sectab.owner;
SELECT owner,
table_name,
sls_table_name,
date_enabled,
date_disabled,
date_removed,
date_security_applied,
update_allowed,
Nvl(optimise_sql,'N') optimise_sql
FROM igi_sls_secure_tables
WHERE date_removed IS NULL
AND date_object_created IS NOT NULL;
IF rt_c_get_enab_sectab.update_allowed = 'Y'
THEN
l_policy_type := 'SELECT,UPDATE';
l_policy_type := 'SELECT';
write_to_log (l_state_level, 'populate_group_alloc', 'Populate_Group_Alloc, Inserting into table igi_sls_security_group_alloc ');
INSERT INTO igi_sls_security_group_alloc
(SLS_SECURITY_GROUP
,TABLE_NAME
)
SELECT DISTINCT sls_group,
table_name
FROM igi_sls_enabled_alloc_v;
SELECT table_name,
date_removed
FROM igi_sls_secure_tables
WHERE date_removed IS NOT NULL;
SELECT sls_group,
sls_group_type,
date_removed
FROM igi_sls_groups
WHERE date_removed IS NOT NULL;
(SELECT a.sls_group sls_group,
a.sls_allocation table_name
FROM igi_sls_allocations a
WHERE a.sls_group_type = 'S'
AND a.sls_allocation_type = 'T'
AND a.date_removed IS NOT NULL
UNION
SELECT a.sls_group sls_group,
d.sls_allocation table_name
FROM igi_sls_aLlocations a,
igi_sls_allocations d
WHERE a.sls_allocation = d.sls_group
AND a.sls_group_type = 'S'
AND a.sls_allocation_type = 'P'
AND d.sls_group_type = 'P'
AND d.sls_allocation_type = 'T'
AND (a.date_removed IS NOT NULL or d.date_removed IS NOT NULL))
MINUS
SELECT sls_security_group sls_group,
table_name table_name
FROM igi_sls_security_group_alloc;
SELECT sls_table_name,
date_removed,
table_name,
NVL(optimise_sql,'N') optimise_sql
FROM igi_sls_secure_tables
WHERE table_name = p_table_name;
write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Updating deleted tables in igi_sls_allocations');
UPDATE igi_sls_allocations
SET date_removed = rt_c_del_table.date_removed,
date_disabled = Nvl(date_disabled, rt_c_del_table.date_removed),
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE sls_allocation = rt_c_del_table.table_name
AND sls_allocation_type = 'T'
AND date_removed IS NULL;
INSERT INTO igi_sls_allocations_audit
(sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
SELECT
sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
SYSDATE,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
FROM igi_sls_allocations a
WHERE a.sls_allocation = rt_c_del_table.table_name
AND a.date_removed = rt_c_del_table.date_removed
AND NOT EXISTS (SELECT 'X'
FROM igi_sls_allocations_audit b
WHERE a.sls_allocation = b.sls_allocation
AND a.sls_group = b.sls_group
AND a.date_enabled = b.date_enabled
AND a.date_removed = b.date_removed);
END LOOP ; -- for each deleted table
write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Updating deleted group in igi_sls_allocations');
UPDATE igi_sls_allocations
SET date_removed = rt_c_del_group.date_removed,
date_disabled = Nvl(date_disabled, rt_c_del_group.date_removed),
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE sls_group = rt_c_del_group.sls_group
AND sls_group_type = rt_c_del_group.sls_group_type
AND date_removed IS NULL;
INSERT INTO igi_sls_allocations_audit
(sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
SELECT
sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
SYSDATE,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
FROM igi_sls_allocations a
WHERE a.sls_group = rt_c_del_group.sls_group
AND a.sls_group_type = rt_c_del_group.sls_group_type
AND a.date_removed = rt_c_del_group.date_removed
AND NOT EXISTS (SELECT 'X'
FROM igi_sls_allocations_audit b
WHERE a.sls_allocation = b.sls_allocation
AND a.sls_group = b.sls_group
AND a.sls_group_type = b.sls_group_type
AND a.date_enabled = b.date_enabled
AND a.date_removed = b.date_removed);
UPDATE igi_sls_allocations
SET date_removed = rt_c_del_group.date_removed,
date_disabled = Nvl(date_disabled, rt_c_del_group.date_removed),
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE sls_allocation = rt_c_del_group.sls_group
AND sls_group_type = rt_c_del_group.sls_group_type
AND date_removed IS NULL;
INSERT INTO igi_sls_allocations_audit
(sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
SELECT
sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
SYSDATE,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
FROM igi_sls_allocations a
WHERE a.sls_allocation = rt_c_del_group.sls_group
AND a.date_removed = rt_c_del_group.date_removed
AND NOT EXISTS (SELECT 'X'
FROM igi_sls_allocations_audit b
WHERE a.sls_allocation = b.sls_allocation
AND a.sls_group = b.sls_group
AND a.sls_group_type = b.sls_group_type
AND a.date_enabled = b.date_enabled
AND a.date_removed = b.date_removed);
END LOOP ; -- for each deleted group
AND l_date_removed IS NULL -- If table is deleted, then the extended table will have been
-- dropped by now.
THEN
IF l_optimise_sql = 'N'
THEN
write_to_log (l_state_level, 'cleanup_data', 'Cleanup_Data, Deleting records from table '||l_sls_table_name ||
' for group '|| rt_c_del_alloc.sls_group );
l_sql_stmt := ' DELETE FROM '|| l_sls_table_name ||
' WHERE sls_sec_grp = :sls_group';
l_sql_stmt := ' UPDATE ' || l_table_name ||
' SET igi_sls_sec_group = NULL ' ||
' WHERE igi_sls_sec_group = :sls_group';
delete from FND_PROFILE_OPTION_VALUES
where PROFILE_OPTION_ID = (select profile_option_id from fnd_profile_options where
profile_option_name = 'IGI_SLS_SECURITY_GROUP')
and APPLICATION_ID = ( select application_id from fnd_application_vl where
application_short_name = 'IGI' )
and profile_option_value = rt_c_del_alloc.sls_group ;
write_to_log (l_state_level, 'cleanup_data', 'After Delete stmt. Immediate' || rt_c_del_alloc.sls_group);
DELETE FROM igi_sls_allocations
WHERE date_removed IS NOT NULL;
DELETE FROM igi_sls_secure_tables
WHERE date_removed IS NOT NULL;
DELETE FROM igi_sls_groups
WHERE date_removed IS NOT NULL;
SELECT sls_group,
date_removed,
date_disabled
FROM igi_sls_groups
WHERE date_security_applied IS NULL
FOR UPDATE OF date_security_applied;
SELECT sls_group,
sls_allocation,
date_disabled,
date_removed
FROM igi_sls_allocations
WHERE date_security_applied IS NULL
FOR UPDATE OF date_security_applied;
UPDATE igi_sls_groups
SET date_security_applied = SYSDATE,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE CURRENT OF c_all_group;
UPDATE igi_sls_groups_audit a
SET a.date_security_applied = SYSDATE
WHERE date_security_applied IS NULL
AND ROWID = (SELECT MAX(ROWID) b
FROM igi_sls_groups_audit b
WHERE a.sls_group = b.sls_group)
AND sls_group = rt_c_all_group.sls_group;
UPDATE igi_sls_allocations
SET date_security_applied = SYSDATE,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE CURRENT OF c_all_alloc;
UPDATE igi_sls_allocations_audit a
SET a.date_security_applied = SYSDATE
WHERE a.date_security_applied IS NULL
AND ROWID = (SELECT MAX(ROWID) b
FROM igi_sls_allocations_audit b
WHERE a.sls_allocation = b.sls_allocation
AND a.sls_group = b.sls_group)
AND sls_allocation = rt_c_all_alloc.sls_allocation
AND sls_group = rt_c_all_alloc.sls_group;
SELECT from_sls_security_group,
to_sls_security_group
FROM igi_sls_consolidate_groups
WHERE date_security_applied IS NULL;
SELECT sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
FROM igi_sls_allocations
WHERE sls_group = p_sls_group
AND date_removed IS NULL;
SELECT sls_allocation
FROM igi_sls_allocations
WHERE sls_group = p_sls_group
AND sls_allocation_type = 'T'
AND date_removed IS NULL;
SELECT sls_table_name,
-- Bug 5144650 .. Start
NVL(optimise_sql,'N') optimise_sql
-- Bug 5144650 .. End
FROM igi_sls_secure_tables
WHERE table_name = p_table_name;
l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
' SET sls_sec_grp = '''||rt_c_cons_recs.to_sls_security_group ||''',' ||
' prev_sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||''','||
' change_date = SYSDATE ' ||
' WHERE sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||'''';
l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
' SET sls_sec_grp = :to_sls_security_group ,'||
' prev_sls_sec_grp = :from_sls_security_group,'||
' change_date = SYSDATE ' ||
' WHERE sls_sec_grp = :from_sls_security_group';
l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
' SET sls_sec_grp = '''||rt_c_cons_recs.to_sls_security_group ||''',' ||
' prev_sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||''','||
' change_date = SYSDATE ' ||
' WHERE sls_sec_grp = '''||rt_c_cons_recs.from_sls_security_group||'''';
l_sql_stmt := ' UPDATE ' ||l_sls_tabname ||
' SET sls_sec_grp = :to_sls_security_group ,'||
' prev_sls_sec_grp = :from_sls_security_group,'||
' change_date = SYSDATE ' ||
' WHERE sls_sec_grp = :from_sls_security_group';
l_sql_stmt := ' UPDATE ' || rt_c_grp_alloc.sls_allocation ||
' SET igi_sls_sec_group = :to_sls_security_group '||
' WHERE igi_sls_sec_group = :from_sls_security_group';
SELECT COUNT(*)
INTO l_alloc_count
FROM igi_sls_allocations
WHERE sls_allocation = rt_c_grp_alloc.sls_allocation
AND sls_group = rt_c_cons_recs.to_sls_security_group
AND sls_allocation_type = rt_c_grp_alloc.sls_allocation_type;
SELECT COUNT(*)
INTO l_enab_rec_count
FROM igi_sls_allocations a,
igi_sls_consolidate_groups b
WHERE a.sls_group = b.from_sls_security_group
AND a.date_disabled IS NULL
AND a.date_removed IS NULL
AND a.sls_allocation = rt_c_grp_alloc.sls_allocation
AND b.to_sls_security_group = rt_c_cons_recs.to_sls_security_group;
INSERT INTO igi_sls_allocations
(sls_group,
sls_group_type,
sls_allocation,
sls_allocation_type,
date_enabled,
date_disabled,
date_removed ,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
VALUES
(rt_c_cons_recs.to_sls_security_group,
'S',
rt_c_grp_alloc.sls_allocation,
rt_c_grp_alloc.sls_allocation_type,
SYSDATE,
l_date_disabled,
NULL,
NULL,
SYSDATE,
to_number(fnd_profile.value('USER_ID')),
to_number(fnd_profile.value('LOGIN_ID')),
SYSDATE,
to_number(fnd_profile.value('USER_ID')));
UPDATE igi_sls_groups
SET date_disabled = Nvl(date_disabled, l_sysdate),
date_removed = SYSDATE,
date_security_applied = NULL,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE sls_group = rt_c_cons_recs.from_sls_security_group
AND date_removed IS NULL;
INSERT INTO igi_sls_groups_audit
(sls_group,
sls_group_type,
description,
date_enabled,
date_disabled,
date_removed,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
SELECT
sls_group,
sls_group_type,
description,
date_enabled,
date_disabled,
date_removed,
date_security_applied,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by
FROM igi_sls_groups
WHERE sls_group = rt_c_cons_recs.from_sls_security_group
AND date_removed = l_sysdate;
UPDATE igi_sls_consolidate_groups
SET date_security_applied = SYSDATE,
last_update_login = to_number(fnd_profile.value('LOGIN_ID')),
last_update_date = SYSDATE,
last_updated_by = to_number(fnd_profile.value('USER_ID'))
WHERE date_security_applied IS NULL;
SELECT a.sls_table_name,
b.table_name,
b.sls_security_group,
a.owner,
Nvl(a.optimise_sql,'N') optimise_sql
FROM igi_sls_secure_tables a,
igi_sls_security_group_alloc b
WHERE a.table_name = b.table_name
AND b.sls_security_group = Nvl(p_sec_grp , b.sls_security_group);
SELECT DISTINCT a.table_name
FROM igi_sls_security_group_alloc a
WHERE a.table_name in (SELECT table_name
FROM igi_sls_security_group_alloc
GROUP BY table_name
HAVING COUNT(*) > 1)
AND a.sls_security_group = Nvl(p_sec_grp , a.sls_security_group);
SELECT COUNT(*)
INTO l_count
FROM igi_sls_secure_tables a,
all_objects b
WHERE a.sls_table_name = b.object_name
AND b.owner = l_schema; -- Bug 3431843 hkaniven
l_sql_stmt := ' INSERT INTO '||l_sec_dat.sls_table_name ||
' (sls_rowid, sls_sec_grp) ' ||
' SELECT rowid, ' ||
''''||l_sec_dat.sls_security_group||'''' ||
' FROM '|| l_sec_dat.table_name || ' a ' ||
' WHERE NOT EXISTS (SELECT ''X''' ||
' FROM '||l_sec_dat.sls_table_name ||' b' ||
' WHERE a.rowid = b.sls_rowid )';
l_sql_stmt := ' INSERT INTO '||l_sec_dat.sls_table_name ||
' (sls_rowid, sls_sec_grp) ' ||
' SELECT rowid, ' ||
' :sls_security_group' ||
' FROM '|| l_sec_dat.table_name || ' a ' ||
' WHERE NOT EXISTS (SELECT ''X''' ||
' FROM '||l_sec_dat.sls_table_name ||' b' ||
' WHERE a.rowid = b.sls_rowid )';
l_sql_stmt := ' UPDATE ' || l_sec_dat.table_name ||
' SET igi_sls_sec_group = '||''''||l_sec_dat.sls_security_group||''''||
' WHERE igi_sls_sec_group IS NULL ';
l_sql_stmt := ' UPDATE ' || l_sec_dat.table_name ||
' SET igi_sls_sec_group = :sls_security_group'||
' WHERE igi_sls_sec_group IS NULL ';