FND W3H_User_Functions

➜ Back to Library
Description
Categories: BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_User_Functions
DB package:
Run FND W3H_User_Functions and other Oracle EBS reports with Blitz Report™ on our demo environment
select fr.responsibility_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility, 
fnd_message.get_STRING('FND','W3H_FUNCTIONEXCLUSION_REASON') Reason,
  wur.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_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 rule_type='F')
  and frm.function_id=frf.action_id  
  and wur.user_name= :PARAM1
  and frm.function_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.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_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 wur.user_name= :PARAM1
  and frm.function_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.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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 wur.user_name= :PARAM1
  and frm.function_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,
  fg.start_date Function_Assignment_Start_Date,
  fg.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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 UMX_W3H_UTL.isFunctionAccessible(wur.user_name,wur.role_name)='true'
  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 wur.user_name= :PARAM1
  and frm.function_name = :Name
  union all
  /* Accessible through Grant */
  select distinct fg.grantee_key,fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') Accessibility,
  fnd_message.get_STRING('FND','W3H_GLOBALGRANT_REASON') Reason,
  fg.start_date Function_Assignment_Start_Date,
  fg.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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.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 = :Name
  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,
  fg.start_date Function_Assignment_Start_Date,
  fg.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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 ((sysdate < fg.start_date  or sysdate > fg.end_date)
  or (UMX_W3H_UTL.isFunctionAccessible(wur.user_name,wur.role_name)='false'))
  and fcm.menu_id=fg.menu_id
  and frm.function_id=fcm.function_id
  and wur.user_name= :PARAM1
  and frm.function_name = :Name
  union all
  /* Not Accessible through Grant */
  select distinct fg.grantee_key,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') Accessibility,
  fnd_message.get_STRING('FND','W3H_GRANTEEENDDATE_REASON')
  Reason,
  fg.start_date Function_Assignment_Start_Date,
  fg.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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 (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 = :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,
  Function_Assignment_Start_Date,Function_Assignment_End_Date, function_name AS Function_name
  from
  (
  select fr.responsibility_key,
  wur.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_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 UMX_W3H_UTL.isFunctionAccessible(wur.user_name,wur.role_name)='true'
  and fcm.menu_id=fr.menu_id 
  and frm.function_id=fcm.function_id
  and fcm.grant_flag='Y'
  and wur.user_name= :PARAM1
  and frm.function_name = :Name
  minus
  ( 
  select fr.responsibility_key,
  wur.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_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 rule_type='F')
  and frm.function_id=frf.action_id 
  and wur.user_name= :PARAM1
  and frm.function_name = :Name
  union all
  select fr.responsibility_key,
  wur.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_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 wur.user_name= :PARAM1
  and frm.function_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.start_date Function_Assignment_Start_Date,
  wur.end_date Function_Assignment_End_Date,
  frm.function_name AS Function_name
  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 UMX_W3H_UTL.isFunctionAccessible(wur.user_name,wur.role_name)='false'
  and frm.function_id=fcm.function_id  
  and wur.user_name= :PARAM1 
and frm.function_name = :Name
Download
Related Blitz Reports
FND W3H_Function_Users FND W3H_Role_Functions FND Access Control 11i FND Access Control FND User Roles FND W3H_User_Roles FND Responsibility Menu Exclusions FLS PA Budgets and Actuals - Totals, FLS PA Project, PA Fundings, PA Projects XXEN: FND User Login History with Geolocation
Blitz Report™

Blitz Report™ provides multiple benefits: