FND W3H_Function_Users

➜ Back to Library
Description
Categories: BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_Function_Users
DB package: 
select fr.responsibility_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility, fnd_message.get_STRING('FND','W3H_FUNCTIONEXCLUSION_REASON') Reason,
 wur.user_name AS UserName
 from fnd_responsibility fr, fnd_resp_functions frf, wf_user_role_assignments wur,
 fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and (frf.responsibility_id=fr.responsibility_id and rule_type='F')
 and frm.function_id=frf.action_id  and frm.function_name= :PARAM1
  and wur.user_name = :Name
 union all
 select fr.responsibility_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility, fnd_message.get_STRING('FND','W3H_MENUEXCLUSION_REASON') Reason,
 wur.user_name AS UserName
 from fnd_resp_functions frf,wf_user_role_assignments wur,fnd_responsibility fr,
 fnd_compiled_menu_functions fcm,fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and frf.responsibility_id=fr.responsibility_id and frf.rule_type='M'
 and fcm.menu_id=frf.action_id
 and frm.function_id=fcm.function_id
 and frm.function_name= :PARAM1
 and wur.user_name = :Name
 union all
 select fr.responsibility_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility, fnd_message.get_STRING('FND','W3H_FUNCTIONGRANTFLAG_REASON') Reason,
 wur.user_name AS UserName
 from fnd_compiled_menu_functions fcm,fnd_responsibility fr,
 wf_user_role_assignments wur,fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and fr.menu_id=fcm.menu_id
 and fcm.function_id=frm.function_id
 and fcm.grant_flag='N'
 and frm.function_name= :PARAM1
  and wur.user_name = :Name
 union all
 /* Accessible through Grant */
 select fg.grantee_key,fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') Accessibility,
 decode(fg.grantee_type,'USER',fnd_message.get_STRING('FND','W3H_USERGRANT_REASON'),
 'GROUP',decode(wur.role_orig_system,'UMX',fnd_message.get_STRING('FND','W3H_ROLEGRANT_REASONAPPLICAT'),fnd_message.get_STRING('FND','W3H_RESPONSIBILITYGRANT_REASON')))
 Reason, wur.user_name AS UserName
 from fnd_grants fg,wf_user_role_assignments wur,fnd_compiled_menu_functions fcm,
 fnd_form_functions frm
 where  (fg.grantee_key=wur.role_name) AND fg.grantee_type IN ('USER', 'GROUP')  
 and (wur.effective_start_date <=sysdate or wur.effective_start_date is null)
 and (wur.effective_end_date is null or sysdate < wur.effective_end_date)
 and (fg.start_date <= sysdate  and (sysdate <= fg.end_date or fg.end_date is null))
 and fcm.menu_id=fg.menu_id
 and frm.function_id=fcm.function_id
 and frm.function_name=  :PARAM1
 and wur.user_name = :Name
 union all
 select  fg.grantee_key,fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') Accessibility,
  fnd_message.get_STRING('FND','W3H_GLOBALGRANT_REASON')   Reason, 'GLOBAL' AS UserName
  from fnd_grants fg,fnd_compiled_menu_functions fcm,
  fnd_form_functions frm
  where 
   (fg.start_date <= sysdate  
  and (sysdate <= fg.end_date or fg.end_date is null))
  and fcm.menu_id=fg.menu_id
  and frm.function_id=fcm.function_id
  and fg.grantee_type='GLOBAL'
  and frm.function_name= :PARAM1
 union all
 select fg.grantee_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility,   
 fnd_message.get_STRING('FND','W3H_GRANTEEENDDATE_REASON') Reason,   
  'GLOBAL' AS UserName   
  from fnd_grants fg,fnd_compiled_menu_functions fcm,   
  fnd_form_functions frm
  WHERE ( (sysdate < fg.start_date  or sysdate > fg.end_date)  ) 
  and fcm.menu_id=fg.menu_id   
  and frm.function_id=fcm.function_id   
  and fg.grantee_type='GLOBAL'   
  and frm.function_name= :PARAM1
 union all
 /* Not Accessible through Grant */
 select fg.grantee_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility,
 fnd_message.get_STRING('FND','W3H_GRANTEEENDDATE_REASON') Reason,
 wur.user_name AS UserName
 from fnd_grants fg,wf_user_role_assignments wur,fnd_compiled_menu_functions fcm,
 fnd_form_functions frm
 where fg.grantee_key=wur.role_name and fg.grantee_type IN ('USER', 'GROUP')  
 and ((sysdate < fg.start_date  or sysdate > fg.end_date)
 or (wur.effective_start_date > sysdate or wur.effective_end_date <= sysdate))
 and fcm.menu_id=fg.menu_id
 and frm.function_id=fcm.function_id
 and frm.function_name= :PARAM1
 and wur.user_name = :Name
 union all
 /* This retrieves only those responsibilities through which Function is Accessible */
 select responsibility_key, fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') Accessibility, fnd_message.get_STRING('FND','W3H_THROUGHRESP_REASON') Reason,
 user_name AS UserName
 from
 (
 select fr.responsibility_key,
 frm.function_name,wur.user_name
 from wf_user_role_assignments wur,fnd_responsibility fr,
 fnd_compiled_menu_functions fcm,fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and (wur.effective_start_date <=sysdate
 and (wur.effective_end_date is null or sysdate < wur.effective_end_date))
 and fcm.menu_id=fr.menu_id
 and frm.function_id=fcm.function_id
 and fcm.grant_flag='Y'
 and frm.function_name= :PARAM1
 and wur.user_name = :Name
 minus
 (
 select fr.responsibility_key,
 frm.function_name,wur.user_name
 from fnd_responsibility fr, fnd_resp_functions frf, wf_user_role_assignments wur,
 fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and (frf.responsibility_id=fr.responsibility_id and frf.rule_type='F')
 and frm.function_id=frf.action_id
 and frm.function_name= :PARAM1
 and wur.user_name = :Name
 union all
 select fr.responsibility_key,
 frm.function_name,wur.user_name
 from fnd_resp_functions frf,wf_user_role_assignments wur,fnd_responsibility fr,
 fnd_compiled_menu_functions fcm,fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and frf.responsibility_id=fr.responsibility_id and frf.rule_type='M'
 and fcm.menu_id=frf.action_id
 and frm.function_id=fcm.function_id
 and frm.function_name= :PARAM1
 and wur.user_name = :Name
 )
 )
 union all
 /* Checks for user/resp effective start_date and end_date */
 select fr.responsibility_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility,
 fnd_message.get_STRING('FND','W3H_USERENDDATE_REASON') Reason,
 wur.user_name AS UserName
 from fnd_responsibility fr, fnd_compiled_menu_functions fcm, wf_user_role_assignments wur,
 fnd_form_functions frm
 where substr(wur.role_name,instr(wur.role_name,'|',1,2)+1,
 (instr(wur.role_name,'|',1,3)-1-instr(wur.role_name,'|',1,2)))=fr.responsibility_key
 and fcm.menu_id=fr.menu_id
 and (wur.effective_start_date > sysdate or sysdate >= wur.effective_end_date)
 and frm.function_id=fcm.function_id  and frm.function_name= :PARAM1
 and wur.user_name = :Name
Download
Related Blitz Reports
FND W3H_User_Functions FND W3H_Role_Functions Blitz Report Assignments and Responsibilities Mrugesh test report FND Responsibility Access 11i FND Responsibility Access FND User Roles FND W3H_User_Roles FND Responsibility Menu Exclusions
Blitz Report™

Blitz Report™ provides multiple benefits: