[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
INTO dummy
FROM fnd_responsibility r
WHERE r.responsibility_key = responsibility_exists.responsibility_key;
SELECT 1
INTO dummy
FROM fnd_form_functions f
WHERE f.function_name = form_function_exists.function_name;
SELECT 1
INTO dummy
FROM fnd_menus f
WHERE f.menu_name = menu_exists.menu_name;
SELECT 1
INTO dummy
FROM fnd_menu_entries me, fnd_menus m, fnd_menus s, fnd_form_functions f
WHERE me.menu_id = m.menu_id
AND m.menu_name = menu_entry_exists.menu_name
AND me.sub_menu_id = s.menu_id (+)
AND nvl(s.menu_name, 'x') = nvl(menu_entry_exists.sub_menu_name, 'x')
AND me.function_id = f.function_id (+)
AND nvl(f.function_name, 'x') = nvl(menu_entry_exists.function_name, 'x');
SELECT 1
INTO dummy
FROM fnd_resp_functions rf, fnd_responsibility r, fnd_form_functions f
WHERE rf.responsibility_id = r.responsibility_id
AND rf.application_id = r.application_id
AND r.responsibility_key = security_rule_exists.responsibility_key
AND rf.rule_type = 'F'
AND rf.action_id = f.function_id
AND f.function_name = security_rule_exists.rule_name;
SELECT 1
INTO dummy
FROM fnd_resp_functions rf, fnd_responsibility r, fnd_menus m
WHERE rf.responsibility_id = r.responsibility_id
AND rf.application_id = r.application_id
AND r.responsibility_key = security_rule_exists.responsibility_key
AND rf.rule_type = 'M'
AND rf.action_id = m.menu_id
AND m.menu_name = security_rule_exists.rule_name;
delete_flag IN VARCHAR2 DEFAULT 'N'
)
IS
namebuf VARCHAR2(100);
SELECT a.application_id
INTO application_id
FROM fnd_application a
WHERE a.application_short_name = responsibility.application;
-- Delete if requested
IF (delete_flag <> 'N') THEN
-- Resps are never deleted. Set the end_date instead.
UPDATE fnd_responsibility
SET end_date = sysdate
WHERE responsibility_key = responsibility.responsibility_key;
fnd_function_security_cache.update_resp(responsibility.responsibility_id,
responsibility.application_id);
-- This is an insert/update.
-- Bump responsibility_name by prepending '@' if needed to avoid any
-- possible unique key violations.
namebuf := responsibility.responsibility_name;
SELECT count(1)
INTO dummy
FROM fnd_responsibility_vl fr
WHERE fr.responsibility_name = namebuf
AND (fr.responsibility_id <> responsibility.responsibility_id
OR fr.application_id <> responsibility.application_id);
-- Select all other hidden keys
-- Data group
SELECT dg.data_group_id, a.application_id
INTO data_group_id, data_group_application_id
FROM fnd_data_groups_standard_view dg, fnd_data_group_units dgu, fnd_application a
WHERE dg.data_group_name = responsibility.data_group_name
AND dg.data_group_id = dgu.data_group_id
AND dgu.application_id = a.application_id
AND a.application_short_name = responsibility.data_group_application;
SELECT m.menu_id
INTO menu_id
FROM fnd_menus m
WHERE m.menu_name = responsibility.menu_name;
SELECT rg.request_group_id, a.application_id
INTO request_group_id, group_application_id
FROM fnd_request_groups rg, fnd_application a
WHERE rg.request_group_name = responsibility.request_group_name
AND rg.application_id = a.application_id
AND a.application_short_name = responsibility.request_group_application;
-- Select to decide if this is insert or update
BEGIN
SELECT responsibility_id
INTO dummy
FROM fnd_responsibility r
WHERE r.responsibility_key = responsibility.responsibility_key;
-- Insert into base
INSERT INTO fnd_responsibility (
application_id,
responsibility_id,
responsibility_key,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
data_group_id,
data_group_application_id,
menu_id,
START_DATE,
end_date,
group_application_id,
request_group_id,
version,
web_host_name,
web_agent_name
)
VALUES (
responsibility.application_id,
responsibility.responsibility_id,
responsibility.responsibility_key,
sysdate,
1,
sysdate,
1,
0,
responsibility.data_group_id,
responsibility.data_group_application_id,
responsibility.menu_id,
responsibility.START_DATE,
responsibility.end_date,
responsibility.group_application_id,
responsibility.request_group_id,
responsibility.version,
responsibility.web_host_name,
responsibility.web_agent_name
);
fnd_function_security_cache.insert_resp(responsibility.responsibility_id,
responsibility.application_id);
-- Insert into tl
INSERT INTO fnd_responsibility_tl (
application_id,
responsibility_id,
LANGUAGE,
responsibility_name,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
source_lang
)
SELECT responsibility.application_id,
responsibility.responsibility_id,
l.language_code,
namebuf,
responsibility.description,
1,
sysdate,
1,
sysdate,
0,
userenv('LANG')
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B');
-- Update existing row
UPDATE fnd_responsibility r
SET responsibility_key = responsibility.responsibility_key,
START_DATE = responsibility.START_DATE,
end_date = responsibility.end_date,
data_group_id = responsibility.data_group_id,
data_group_application_id = responsibility.data_group_application_id,
menu_id = responsibility.menu_id,
request_group_id = responsibility.request_group_id,
group_application_id = responsibility.group_application_id,
version = responsibility.version,
web_host_name = responsibility.web_host_name,
web_agent_name = responsibility.web_agent_name
WHERE r.responsibility_id = responsibility.responsibility_id
AND r.application_id = responsibility.application_id;
fnd_function_security_cache.update_resp(responsibility.responsibility_id,
responsibility.application_id);
-- Update TL
UPDATE fnd_responsibility_tl r
SET responsibility_name = namebuf,
description = responsibility.description
WHERE r.responsibility_id = responsibility.responsibility_id
AND r.application_id = responsibility.application_id
AND r.LANGUAGE = userenv('LANG');
delete_flag IN VARCHAR2 DEFAULT 'N'
)
IS
curlang VARCHAR2(30);
SELECT APPLICATION_ID, RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE rule_type = 'F'
AND action_id = form_function.function_id;
SELECT function_id
INTO form_function.function_id
FROM fnd_form_functions
WHERE function_name = form_function.function_name;
-- Delete if requested
IF (delete_flag <> 'N') THEN
-- Check for foreign key references
IF (delete_flag <> 'F') THEN
BEGIN
SELECT 1
INTO dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_menu_entries me
WHERE me.function_id = form_function.function_id);
SELECT 1
INTO dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_attachment_functions af
WHERE af.function_type = 'F'
AND af.function_id = form_function.function_id);
DELETE FROM fnd_form_functions
WHERE function_id = form_function.function_id;
fnd_function_security_cache.delete_function(form_function.function_id);
DELETE FROM fnd_form_functions_tl
WHERE function_id = form_function.function_id;
-- Cascade deletes to resp functions
DELETE FROM fnd_resp_functions rf
WHERE rf.rule_type = 'F'
AND rf.action_id = form_function.function_id;
fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
SELECT count(1)
INTO dummy
FROM fnd_form_functions_vl ff
WHERE ff.user_function_name = namebuf
AND ff.function_id <> form_function.function_id;
SELECT f.form_id, f.application_id
INTO form_function.form_id, form_function.application_id
FROM fnd_form f
WHERE f.form_name = form_function.form_name;
SELECT fnd_form_functions_s.NEXTVAL
INTO form_function.function_id
FROM dual;
INSERT INTO fnd_form_functions (
function_id,
function_name,
application_id,
form_id,
PARAMETERS,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
TYPE)
SELECT
form_function.function_id,
form_function.function_name,
form_function.application_id,
form_function.form_id,
form_function.PARAMETERS,
sysdate,
1,
sysdate,
1,
1,
form_function.TYPE
FROM sys.dual;
fnd_function_security_cache.insert_function(form_function.function_id);
INSERT INTO fnd_form_functions_tl (
LANGUAGE,
function_id,
user_function_name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
source_lang)
SELECT
l.language_code,
f.function_id,
form_function.namebuf,
form_function.description,
sysdate,
1,
sysdate,
1,
1,
userenv('LANG')
FROM fnd_form_functions f, fnd_languages l
WHERE f.function_name = form_function.function_name
AND l.installed_flag IN ('I', 'B');
UPDATE fnd_form_functions SET
application_id = form_function.application_id,
form_id = form_function.form_id,
PARAMETERS = form_function.PARAMETERS,
last_update_date = sysdate,
last_updated_by = 1,
last_update_login = 1,
TYPE = form_function.TYPE
WHERE function_id = form_function.function_id;
fnd_function_security_cache.update_function(form_function.function_id);
UPDATE fnd_form_functions_tl SET
user_function_name = form_function.namebuf,
description = form_function.description,
last_update_date = sysdate,
last_updated_by = 1,
last_update_login = 1,
source_lang = userenv('LANG')
WHERE function_id = form_function.function_id
AND userenv('LANG') IN (LANGUAGE, source_lang);
delete_flag IN VARCHAR2 DEFAULT 'N'
)
IS
curlang VARCHAR2(30);
SELECT APPLICATION_ID, RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE rule_type = 'M'
AND action_id = menu.menu_id;
SELECT sub_menu_id, function_id
FROM fnd_menu_entries
WHERE menu_id = menu.menu_id;
SELECT menu_id
INTO menu.menu_id
FROM fnd_menus
WHERE menu_name = menu.menu_name;
-- Delete if requested
IF (delete_flag <> 'N') THEN
-- Check for foreign key references
IF (delete_flag <> 'F') THEN
BEGIN
SELECT 1
INTO dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_menu_entries me
WHERE me.sub_menu_id = menu.menu_id);
SELECT 1
INTO dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_responsibility r
WHERE r.menu_id = menu.menu_id);
DELETE FROM fnd_menus
WHERE menu_id = menu.menu_id;
fnd_function_security_cache.delete_menu(menu.menu_id);
DELETE FROM fnd_menus_tl
WHERE menu_id = menu.menu_id;
-- Cascade delete to menu entries and resp functions
DELETE FROM fnd_menu_entries
WHERE menu_id = menu.menu_id;
-- Need make sure that each menu entry deleted is taken into account.
FOR mn IN MN_ENTRY LOOP
fnd_function_security_cache.delete_menu_entry(menu.menu_id,
mn.sub_menu_id, mn.function_id);
DELETE FROM fnd_menu_entries_tl
WHERE menu_id = menu.menu_id;
DELETE FROM fnd_resp_functions rf
WHERE rf.rule_type = 'M'
AND rf.action_id = menu.menu_id;
-- Need make sure that each responsibility updated is taken into account.
FOR rs IN RESP_FUNC LOOP
fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
-- This is an insert/update.
-- Bump responsibility_name by prepending '@' if needed to avoid any
-- possible unique key violations.
namebuf := menu.user_menu_name;
SELECT count(1)
INTO dummy
FROM fnd_menus_vl fm
WHERE fm.user_menu_name = namebuf
AND fm.menu_id <> menu.menu_id;
-- Select to decide if this is insert or update
IF (menu_id = -1) THEN
SELECT fnd_menus_s.NEXTVAL
INTO menu.menu_id
FROM dual;
-- Insert into base
INSERT INTO fnd_menus (
menu_id,
menu_name,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT menu.menu_id,
menu.menu_name,
sysdate,
1,
sysdate,
1,
1
FROM sys.dual;
fnd_function_security_cache.insert_menu(menu.menu_id);
-- Insert into _TL
INSERT INTO fnd_menus_tl (
LANGUAGE,
menu_id,
user_menu_name,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
source_lang
)
SELECT l.language_code,
f.menu_id,
menu.namebuf,
menu.description,
sysdate,
1,
sysdate,
1,
1,
userenv('LANG')
FROM fnd_menus f, fnd_languages l
WHERE f.menu_name = menu.menu_name
AND l.installed_flag IN ('I', 'B');
UPDATE fnd_menus_tl
SET user_menu_name = menu.namebuf,
description = menu.description,
last_update_date = sysdate,
last_updated_by = 1,
last_update_login = 1,
source_lang = userenv('LANG')
WHERE menu_id = menu.menu_id
AND userenv('LANG') IN (LANGUAGE, source_lang);
FND_FUNCTION_SECURITY_CACHE.update_menu(menu.menu_id);
delete_flag IN VARCHAR2 DEFAULT 'N'
)
IS
curlang VARCHAR2(30);
SELECT menu_id
INTO menu_entry.menu_id
FROM fnd_menus
WHERE menu_name = menu_entry.menu_name;
SELECT menu_id
INTO menu_entry.sub_menu_id
FROM fnd_menus
WHERE menu_name = menu_entry.sub_menu_name;
SELECT function_id
INTO menu_entry.function_id
FROM fnd_form_functions
WHERE function_name = menu_entry.function_name;
SELECT fme.entry_sequence
INTO l_entry_sequence
FROM fnd_menu_entries fme
WHERE fme.menu_id = menu_entry.menu_id
AND nvl(fme.sub_menu_id, -1) = nvl(menu_entry.sub_menu_id, -1)
AND nvl(fme.function_id, -1) = nvl(menu_entry.function_id, -1);
-- Delete if requested
IF (delete_flag = 'Y') THEN
-- Determine the correct sub_menu_id and function_id using the menu_id
-- and entry sequence before deleting. It may not be safe to use the
-- previous values determined above since those values may have failed
-- in the matching test previous to this code.
SELECT sub_menu_id, function_id
INTO menu_entry.sub_menu_id, menu_entry.function_id
FROM fnd_menu_entries
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence;
DELETE FROM fnd_menu_entries
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence;
fnd_function_security_cache.delete_menu_entry(menu_entry.menu_id,
menu_entry.sub_menu_id, menu_entry.function_id);
DELETE FROM fnd_menu_entries_tl
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence;
-- Select to decide if this is insert or update
BEGIN
SELECT menu_id
INTO menu_entry.menu_id
FROM fnd_menu_entries
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence;
-- Insert into base
INSERT INTO fnd_menu_entries (
menu_id,
entry_sequence,
sub_menu_id,
function_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES (
menu_entry.menu_id,
menu_entry.entry_sequence,
menu_entry.sub_menu_id,
menu_entry.function_id,
sysdate,
1,
sysdate,
1,
1
);
fnd_function_security_cache.insert_menu_entry(menu_entry.menu_id,
menu_entry.sub_menu_id, menu_entry.function_id);
-- Insert into _TL
INSERT INTO fnd_menu_entries_tl (
LANGUAGE,
menu_id,
entry_sequence,
PROMPT,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
source_lang
)
SELECT l.language_code,
menu_entry.menu_id,
menu_entry.entry_sequence,
menu_entry.PROMPT,
menu_entry.description,
sysdate,
1,
sysdate,
1,
1,
userenv('LANG')
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B');
-- Update base
UPDATE fnd_menu_entries
SET sub_menu_id = menu_entry.sub_menu_id,
function_id = menu_entry.function_id,
last_update_date = sysdate,
last_updated_by = 1,
last_update_login = 1
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence;
fnd_function_security_cache.update_menu_entry(menu_entry.menu_id,
menu_entry.sub_menu_id, menu_entry.function_id);
-- Update TL
UPDATE fnd_menu_entries_tl
SET PROMPT = menu_entry.PROMPT,
description = menu_entry.description,
last_update_date = sysdate,
last_updated_by = 1,
last_update_login = 1,
source_lang = userenv('LANG')
WHERE menu_id = menu_entry.menu_id
AND entry_sequence = l_entry_sequence
AND userenv('LANG') IN (LANGUAGE, source_lang);
delete_flag IN VARCHAR2 DEFAULT 'N')
IS
curlang VARCHAR2(30);
SELECT fr.responsibility_id, fr.application_id
INTO security_rule.responsibility_id, security_rule.application_id
FROM fnd_responsibility fr
WHERE fr.responsibility_key = security_rule.responsibility_key;
SELECT function_id
INTO security_rule.action_id
FROM fnd_form_functions
WHERE function_name = security_rule.rule_name;
SELECT menu_id
INTO security_rule.action_id
FROM fnd_menus
WHERE menu_name = security_rule.rule_name;
IF (delete_flag = 'Y') THEN
DELETE FROM fnd_resp_functions
WHERE responsibility_id = security_rule.responsibility_id
AND application_id = security_rule.application_id
AND rule_type = security_rule.rule_type
AND action_id = security_rule.action_id;
fnd_function_security_cache.update_resp(security_rule.responsibility_id, security_rule.application_id);
INSERT INTO fnd_resp_functions (
application_id,
responsibility_id,
action_id,
rule_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
VALUES (
security_rule.application_id,
security_rule.responsibility_id,
security_rule.action_id,
security_rule.rule_type,
sysdate,
1,
1,
sysdate,
1);
fnd_function_security_cache.update_resp(security_rule.responsibility_id,
security_rule.application_id);
PROCEDURE UPDATE_FUNCTION_NAME (
oldname IN VARCHAR2,
newname IN VARCHAR2
)
IS
oldid NUMBER;
SELECT MENU_ID, ENTRY_SEQUENCE, SUB_MENU_ID
FROM FND_MENU_ENTRIES
WHERE FUNCTION_ID = newid;
SELECT APPLICATION_ID, RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE rule_type = 'F'
AND action_id = newid;
SELECT FUNCTION_ID
INTO oldid
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_NAME = oldname;
SELECT FUNCTION_ID
INTO newid
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_NAME = newname;
-- If only oldname exists, only update oldname to newname
IF ((oldid <> -1) AND (newid = -1)) THEN
UPDATE FND_FORM_FUNCTIONS
SET FUNCTION_NAME = newname
WHERE FUNCTION_NAME = oldname;
fnd_function_security_cache.update_function(oldid);
-- 2. Delete new row
-- 3. Update oldname to newname in old row
-- 1. Reset Fks to all point to old row
UPDATE FND_MENU_ENTRIES
SET FUNCTION_ID = oldid
WHERE FUNCTION_ID = newid;
-- Need make sure that each menu entry updated is taken into account.
FOR mn IN MNU_ENTRY LOOP
fnd_function_security_cache.update_menu_ENTRY(mn.menu_id, mn.sub_menu_id, newid);
UPDATE FND_RESP_FUNCTIONS
SET ACTION_ID = oldid
WHERE RULE_TYPE = 'F'
AND ACTION_ID = newid;
-- Need make sure that each responsibility updated is taken into account.
FOR rs IN RESP_FUNC LOOP
fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
UPDATE FND_ATTACHMENT_FUNCTIONS
SET FUNCTION_ID = oldid
WHERE FUNCTION_TYPE = 'F'
AND FUNCTION_ID = newid;
-- 2. Delete new row
DELETE FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_ID = newid;
fnd_function_security_cache.update_function(newid);
DELETE FROM FND_FORM_FUNCTIONS_TL
WHERE FUNCTION_ID = newid;
-- 3. Update oldname to newname in old row
UPDATE FND_FORM_FUNCTIONS
SET FUNCTION_NAME = newname
WHERE FUNCTION_ID = oldid;
fnd_function_security_cache.update_function(oldid);
END UPDATE_FUNCTION_NAME;
PROCEDURE UPDATE_MENU_NAME (
oldname IN VARCHAR2,
newname IN VARCHAR2
)
IS
oldid NUMBER;
SELECT RESPONSIBILITY_ID, APPLICATION_ID
FROM FND_RESPONSIBILITY
WHERE MENU_ID = oldid;
SELECT APPLICATION_ID, RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE rule_type = 'M'
AND action_id = newid;
SELECT SUB_MENU_ID, FUNCTION_ID
FROM FND_MENU_ENTRIES
WHERE MENU_ID = newid;
SELECT MENU_ID
INTO oldid
FROM FND_MENUS
WHERE MENU_NAME = oldname;
SELECT MENU_ID
INTO newid
FROM FND_MENUS
WHERE MENU_NAME = newname;
-- If only oldname exists, only update oldname to newname
IF ((oldid <> -1) AND (newid = -1)) THEN
UPDATE FND_MENUS
SET MENU_NAME = newname
WHERE MENU_NAME = oldname;
fnd_function_security_cache.update_menu(oldid);
-- 2. Delete new row
-- 3. Update oldname to newname in old row
-- 1. Reset Fks to all point to old row
UPDATE FND_MENU_ENTRIES
SET SUB_MENU_ID = oldid
WHERE MENU_ID = newid;
-- Need make sure that each menu entry updated is taken into account.
FOR mn IN mn_entry LOOP
fnd_function_security_cache.update_menu_entry(newid, mn.sub_menu_id, mn.function_id);
UPDATE FND_RESPONSIBILITY
SET MENU_ID = oldid
WHERE MENU_ID = newid;
-- Need make sure that each responsibility updated is taken into account.
FOR rs IN RESP_CURSOR LOOP
fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
UPDATE FND_MENU_ENTRIES
SET MENU_ID = oldid
WHERE MENU_ID = newid;
-- Need make sure that each menu entry updated is taken into account.
FOR mn IN mn_entry LOOP
fnd_function_security_cache.update_menu_entry(newid, mn.sub_menu_id, mn.function_id);
UPDATE FND_MENU_ENTRIES_TL
SET MENU_ID = oldid
WHERE MENU_ID = newid;
UPDATE FND_RESP_FUNCTIONS
SET ACTION_ID = oldid
WHERE RULE_TYPE = 'M'
AND ACTION_ID = newid;
-- Seems that I need make sure that each responsibility updated is taken into account.
FOR rs IN RESP_FUNC LOOP
fnd_function_security_cache.update_resp(rs.responsibility_id, rs.application_id);
-- 2. Delete new row
DELETE FROM FND_MENUS
WHERE MENU_ID = newid;
fnd_function_security_cache.update_menu(newid);
DELETE FROM FND_MENUS_TL
WHERE MENU_ID = newid;
-- 3. Update oldname to newname in old row
UPDATE FND_MENUS
SET MENU_NAME = newname
WHERE MENU_ID = oldid;
fnd_function_security_cache.update_menu(oldid);
END UPDATE_MENU_NAME;