FND W3H_Role_Functions

Description
Categories: Application, BI Publisher
Application: Application Object Library
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