[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM oke_compiled_access_rules
WHERE role_id = X_Role_ID;
SELECT default_access_level
INTO L_def_access_level
FROM pa_project_role_types
WHERE project_role_id = X_Role_ID;
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_code
, attribute_group_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, access_rule_id
, form_item_flag)
SELECT kar.role_id
, kar.secured_object_name
, oap.attribute_code
, oa.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, DECODE( oa.securable_flag ,
'Y' , kar.access_level ,
'E' , DECODE( kar.access_level ,
'NONE' , 'VIEW' ,
kar.access_level
) ,
'EDIT'
)
, kar.access_rule_id
, oap.form_item_flag
FROM oke_k_access_rules kar
, oke_object_attributes_b oa
, oke_object_attributes_b oap
WHERE kar.role_id = X_Role_ID
AND kar.attribute_code is not null
AND oa.database_object_name = kar.secured_object_name
AND oa.attribute_code = kar.attribute_code
AND oap.database_object_name = oa.database_object_name
AND oa.attribute_code = nvl( oap.parent_attribute_code
, oap.attribute_code )
;
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_code
, attribute_group_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, access_rule_id
, form_item_flag)
SELECT kar.role_id
, kar.secured_object_name
, oap.attribute_code
, oa.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, DECODE( oa.securable_flag ,
'Y' , kar.access_level ,
DECODE( kar.access_level ,
'NONE' , 'VIEW' ,
kar.access_level
)
)
, kar.access_rule_id
, oap.form_item_flag
FROM oke_k_access_rules kar
, oke_object_attributes_b oa
, oke_object_attributes_b oap
WHERE kar.role_id = X_Role_ID
AND kar.attribute_code is null
AND oa.database_object_name = kar.secured_object_name
AND oa.attribute_group_code = kar.attribute_group_code
AND oap.database_object_name = oa.database_object_name
AND oa.attribute_code = nvl( oap.parent_attribute_code
, oap.attribute_code )
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = kar.role_id
AND secured_object_name = kar.secured_object_name
AND attribute_code = oap.attribute_code
)
;
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, access_rule_id
, form_item_flag)
SELECT X_role_id
, oa.database_object_name
, oap.attribute_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, DECODE( oa.securable_flag ,
'Y' , kar.access_level ,
DECODE( kar.access_level ,
'NONE' , 'VIEW' ,
kar.access_level
)
)
, kar.access_rule_id
, oap.form_item_flag
FROM oke_k_access_rules kar
, oke_object_attributes_b oa
, oke_object_attributes_b oap
WHERE kar.role_id = X_Role_ID
AND kar.secured_object_name = oa.database_object_name
AND kar.attribute_group_code IS NULL
AND kar.attribute_code IS NULL
AND oap.database_object_name = oa.database_object_name
AND oa.attribute_code = nvl( oap.parent_attribute_code
, oap.attribute_code )
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = kar.role_id
AND secured_object_name = oap.database_object_name
AND attribute_code = oap.attribute_code
);
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, form_item_flag)
SELECT X_Role_ID
, oap.database_object_name
, oap.attribute_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, L_def_access_level
, oap.form_item_flag
FROM oke_object_attributes_b oa
, oke_object_attributes_b oap
WHERE oa.database_object_name in ( 'OKE_K_HEADERS'
, 'OKE_K_LINES'
, 'OKE_K_DELIVERABLES' )
AND oap.database_object_name = oa.database_object_name
AND oa.attribute_code = nvl( oap.parent_attribute_code
, oap.attribute_code )
AND NOT EXISTS (
SELECT null
FROM oke_k_access_rules
WHERE role_id = X_Role_ID
AND secured_object_name = oap.database_object_name
AND attribute_group_code IS NULL
AND attribute_code IS NULL
)
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = X_Role_ID
AND secured_object_name = oap.database_object_name
AND attribute_code = oap.attribute_code
);
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_group_type
, attribute_group_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, access_rule_id)
SELECT kar.role_id
, kar.secured_object_name
, kar.attribute_group_type
, kar.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, kar.access_level
, kar.access_rule_id
FROM oke_k_access_rules kar
WHERE kar.role_id = X_Role_ID
AND kar.attribute_group_type = 'USER';
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_group_type
, attribute_group_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level
, access_rule_id)
SELECT X_role_id
, kar.secured_object_name
, ag.attribute_group_type
, ag.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, kar.access_level
, kar.access_rule_id
FROM oke_k_access_rules kar
, oke_attribute_groups_v ag
WHERE kar.role_id = X_Role_ID
AND kar.attribute_group_code IS NULL
AND kar.attribute_code IS NULL
ANd ag.attribute_group_type = 'USER'
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = kar.role_id
AND secured_object_name = kar.secured_object_name
AND attribute_group_code = ag.attribute_group_code
AND attribute_group_type = ag.attribute_group_type
)
;
INSERT INTO oke_compiled_access_rules
( role_id
, secured_object_name
, attribute_group_type
, attribute_group_code
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, access_level)
SELECT X_Role_ID
, 'OKE_K_HEADERS'
, ag.attribute_group_type
, ag.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, L_def_access_level
FROM oke_attribute_groups_v ag
WHERE ag.attribute_group_type = 'USER'
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = X_Role_ID
AND secured_object_name = 'OKE_K_HEADERS'
AND attribute_group_type = 'USER'
AND attribute_group_code = ag.attribute_group_code )
UNION ALL
SELECT X_Role_ID
, 'OKE_K_LINES'
, ag.attribute_group_type
, ag.attribute_group_code
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, L_def_access_level
FROM oke_attribute_groups_v ag
WHERE ag.attribute_group_type = 'USER'
AND NOT EXISTS (
SELECT null
FROM oke_compiled_access_rules
WHERE role_id = X_Role_ID
AND secured_object_name = 'OKE_K_LINES'
AND attribute_group_type = 'USER'
AND attribute_group_code = ag.attribute_group_code )
;
DELETE FROM oke_role_functions
WHERE role_id = X_Role_ID;
INSERT INTO oke_role_functions
( role_id
, function_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login)
SELECT DISTINCT X_Role_ID
, f.function_id
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
FROM fnd_form_functions f
, ( SELECT function_id
FROM fnd_menu_entries
START WITH menu_id = (
SELECT menu_id FROM pa_project_role_types
WHERE project_role_id = X_Role_ID )
CONNECT BY menu_id = PRIOR sub_menu_id ) me
WHERE me.function_id = f.function_id;
DELETE FROM oke_k_access_rules
WHERE role_id = X_Target_Role_ID;
UPDATE oke_k_access_rules kar
SET last_update_date = sysdate
, last_updated_by = L_user_id
, access_level = (
SELECT access_level
FROM oke_k_access_rules
WHERE role_id = X_Source_Role_ID
AND secured_object_name = kar.secured_object_name
AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
AND nvl( attribute_code , '*NULL Attribute*' ) =
nvl( kar.attribute_code , '*NULL Attribute*' )
)
WHERE role_id = X_Target_Role_ID
AND EXISTS (
SELECT NULL
FROM oke_k_access_rules
WHERE role_id = X_Source_Role_ID
AND secured_object_name = kar.secured_object_name
AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
AND nvl( attribute_code , '*NULL Attribute*' ) =
nvl( kar.attribute_code , '*NULL Attribute*' )
);
INSERT INTO oke_k_access_rules
( access_rule_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, role_id
, secured_object_name
, attribute_group_code
, attribute_code
, access_level )
SELECT oke_k_access_rules_s.nextval
, sysdate
, L_user_id
, sysdate
, L_user_id
, L_login_id
, X_Target_Role_ID
, kar.secured_object_name
, kar.attribute_group_code
, kar.attribute_code
, kar.access_level
FROM oke_k_access_rules kar
WHERE role_id = X_Source_Role_ID
AND NOT EXISTS (
SELECT NULL
FROM oke_k_access_rules
WHERE role_id = X_Target_Role_ID
AND secured_object_name = kar.secured_object_name
AND nvl( attribute_group_code , '*NULL Attribute Group*' ) =
nvl( kar.attribute_group_code , '*NULL Attribute Group*' )
AND nvl( attribute_code , '*NULL Attribute*' ) =
nvl( kar.attribute_code , '*NULL Attribute*' )
);
SELECT PRT.Project_Role_ID Role_ID
, PRT.Meaning Role_Name
FROM PA_Project_Role_Types PRT
, PA_Role_Controls RC
WHERE RC.Project_Role_ID = PRT.Project_Role_ID
AND RC.Role_Control_Code = 'ALLOW_AS_CONTRACT_MEMBER'
AND PRT.Freeze_Rules_Flag = 'Y'
AND PRT.Project_Role_ID = nvl(X_Role_ID , PRT.Project_Role_ID)
ORDER BY Role_Name;