FND W3H_Role_Functions
Description
Categories: BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_Role_Functions
DB package:
Source:
Short Name: W3H_Role_Functions
DB package:
select * from ( select frm.function_name AS Name,frmt.user_function_name AS DisplayName, frm.type AS Type, (select user_name from fnd_user where user_id=frm.created_by) AS CreatedBy, frm.creation_date AS CreationDate, (select user_name from fnd_user where user_id=frm.last_updated_by) AS LastUpdatedBy, frm.last_update_date AS LastUpdateDate from fnd_form_functions frm,fnd_form_functions_tl frmt, fnd_compiled_menu_functions fcm,fnd_responsibility fr,wf_local_roles wur where frmt.function_id=frm.function_id and frmt.language=userenv('LANG') and frm.function_id=fcm.function_id and fcm.menu_id=fr.menu_id and fr.responsibility_key=substr(wur.name,instr(wur.name,'|',1,2)+1, (instr(wur.name,'|',1,3)-1-instr(wur.name,'|',1,2))) and wur.name= :PARAM1 union /* This selects the functions assigned to the sub roles with partition_id=2 (FND_RESP). This case handles the hierarchy between the roles for FND_RESP orig_system */ select frm.function_name AS Name,frmt.user_function_name AS DisplayName, frm.type AS Type, (select user_name from fnd_user where user_id=frm.created_by) AS CreatedBy, frm.creation_date AS CreationDate, (select user_name from fnd_user where user_id=frm.last_updated_by) AS LastUpdatedBy, frm.last_update_date AS LastUpdateDate from fnd_form_functions frm,fnd_form_functions_tl frmt, fnd_compiled_menu_functions fcm,fnd_responsibility fr,wf_local_roles wur where frmt.function_id=frm.function_id and frmt.language=userenv('LANG') and frm.function_id=fcm.function_id and fcm.menu_id=fr.menu_id and fr.responsibility_key=substr(wur.name,instr(wur.name,'|',1,2)+1, (instr(wur.name,'|',1,3)-1-instr(wur.name,'|',1,2))) and wur.orig_system='FND_RESP' and wur.name in ( select super_name from wf_role_hierarchies where enabled_flag='Y' connect by prior super_name=sub_name and prior enabled_flag='Y' start with sub_name= :PARAM1 ) union /* This selects the functions granted to the given role */ select distinct frm.function_name AS Name,frmt.user_function_name AS DisplayName, frm.type AS Type, (select user_name from fnd_user where user_id=frm.created_by) AS CreatedBy, frm.creation_date AS CreationDate, (select user_name from fnd_user where user_id=frm.last_updated_by) AS LastUpdatedBy, frm.last_update_date AS LastUpdateDate from fnd_grants fg,fnd_form_functions frm,fnd_form_functions_tl frmt, fnd_compiled_menu_functions fcm where frmt.function_id=frm.function_id and frmt.language=userenv('LANG') and frm.function_id=fcm.function_id and fcm.menu_id=fg.menu_id and fg.grantee_key= :PARAM1 union /* This selects the functions granted to sub-roles of a given function */ select distinct frm.function_name AS Name,frmt.user_function_name AS DisplayName, frm.type AS Type, (select user_name from fnd_user where user_id=frm.created_by) AS CreatedBy, frm.creation_date AS CreationDate, (select user_name from fnd_user where user_id=frm.last_updated_by) AS LastUpdatedBy, frm.last_update_date AS LastUpdateDate from fnd_grants fg,fnd_form_functions frm,fnd_form_functions_tl frmt, fnd_compiled_menu_functions fcm where frmt.function_id=frm.function_id and frmt.language=userenv('LANG') and frm.function_id=fcm.function_id and fcm.menu_id=fg.menu_id and fg.grantee_key in ( select super_name from wf_role_hierarchies where enabled_flag='Y' connect by prior super_name=sub_name and prior enabled_flag='Y' start with sub_name= :PARAM1 ) ) &PARAM2 |