FND W3H_Object_Users

Description
Categories: Application, BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_Object_Users
DB package:
select * from (
select fg.grantee_key as UserName,fg.name as GrantName, fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') as Accessibility,
 (select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
 fg.creation_date as GrantCreationDate,
 (select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
 fg.last_update_date as GrantUpdateDate
 from fnd_objects fo,fnd_grants fg,fnd_user fu
 where fo.object_id=fg.object_id
 and fg.start_date <= sysdate 
 and (sysdate < fg.end_date or fg.end_date is null)
 and fo.obj_name= :PARAM1
 and fg.grantee_type='USER'
 and fu.user_name = fg.grantee_key
 and fu.start_date <= sysdate 
 and nvl(fu.end_date,sysdate+1) > sysdate
 union all 
 /*Direct Grants to User*/
 select fg.grantee_key as UserName,fg.name as GrantName, fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') as Accessibility,
 (select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
 fg.creation_date as GrantCreationDate,
 (select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
 fg.last_update_date as GrantUpdateDate
 from fnd_objects fo,fnd_grants fg,fnd_user fu
 where fo.object_id=fg.object_id
 and fo.obj_name= :PARAM1
 and fg.grantee_type='USER'
 and fu.user_name = fg.grantee_key
 and (NOT (fu.start_date <= sysdate and nvl(fu.end_date,sysdate+1) > sysdate)
 OR (sysdate < fg.start_date  or sysdate >= fg.end_date))
 union all
 /*Grants to user through Role/Resp */
 select wur.user_name as UserName,fg.name as GrantName, fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') as Accessibility,
 (select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
 fg.creation_date as GrantCreationDate,
 (select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
 fg.last_update_date as GrantUpdateDate
 from fnd_objects fo,fnd_grants fg,wf_user_role_assignments wur
 where fo.object_id=fg.object_id
 and wur.role_name=fg.grantee_key
 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 fo.obj_name= :PARAM1
 and fg.grantee_orig_system in ('FND_RESP','UMX')
 and fg.grantee_type='GROUP'
 union all
 /*Grants to user through Role/Resp */
 select wur.user_name as UserName,fg.name as GrantName,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') as Accessibility,
 (select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
 fg.creation_date as GrantCreationDate,
 (select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
 fg.last_update_date as GrantUpdateDate
 from fnd_objects fo,fnd_grants fg,wf_user_role_assignments wur
 where fo.object_id=fg.object_id
 and wur.role_name=fg.grantee_key
 and ((sysdate < fg.start_date  or sysdate >= fg.end_date)
 or (wur.effective_start_date > sysdate or wur.effective_end_date <= sysdate))
 and fo.obj_name=  :PARAM1
 and fg.grantee_orig_system in ('FND_RESP','UMX')
 and fg.grantee_type='GROUP'
 union 
/*Global Grants */
select fg.grantee_key as UserName,fg.name as GrantName, fnd_message.get_STRING('FND','W3H_YES_ACCESSIBILITY') as Accessibility,
(select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
fg.creation_date as GrantCreationDate,
(select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
fg.last_update_date as GrantUpdateDate
from fnd_objects fo,fnd_grants fg
where fo.object_id=fg.object_id
and fg.start_date <= sysdate 
and (sysdate < fg.end_date or fg.end_date is null)
and fo.obj_name= :PARAM1
and fg.grantee_type= :PARAM2
union
/*Global Grants */
select fg.grantee_key as UserName,fg.name as GrantName,fnd_message.get_STRING('FND','W3H_NO_ACCESSIBILITY') as Accessibility,
(select user_name from fnd_user where user_id=fg.created_by) as GrantCreatedBy,
fg.creation_date as GrantCreationDate,
(select user_name from fnd_user where user_id=fg.last_updated_by) as GrantUpdatedBy,
fg.last_update_date as GrantUpdateDate
from fnd_objects fo,fnd_grants fg
where fo.object_id=fg.object_id
and (sysdate < fg.start_date  or sysdate >= fg.end_date)
and fo.obj_name=  :PARAM1
and fg.grantee_type =  :PARAM2
) &PARAM3