FND W3H_Function_Users
Description
Categories: BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_Function_Users
DB package:
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 |