FND W3H_Role_Objects

Description
Categories: Application, BI Publisher
Application: Application Object Library
Source:
Short Name: W3H_Role_Objects
DB package:
select fg.grantee_key as AssignedThrough,fg.instance_type,fm.menu_name "menu_name" ,   
UMX_W3H_UTL.getObjectDetails(fm.menu_name) "Permissions",
fo.obj_name ,decode( decode (sign(sysdate - nvl (wf.start_date, sysdate - 1)), 1,decode (sign (nvl(wf.expiration_date, sysdate + 1) - sysdate), 1, 'ACTIVE', 'INACTIVE'),-1, 'INACTIVE', 'ACTIVE'),'INACTIVE',fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'), decode ( (fg.start_date-sysdate)-abs(fg.start_date-sysdate) ,0,fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'), decode((nvl(fg.end_date,sysdate+1)-sysdate)-abs((nvl(fg.end_date,sysdate+1)-sysdate)),0,
fnd_message.get_string('FND','W3H_YES_ACCESSIBILITY'),fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'))  ) )   "Accessibility"
from fnd_grants fg,fnd_objects fo, fnd_menus fm,wf_local_roles wf
where fg.object_id=fo.object_id
and fm.menu_id=fg.menu_id
and wf.name=fg.grantee_key
and fg.grantee_key= :PARAM1
and fo.obj_name = :ObjName
union all
select fg.grantee_key as AssignedThrough,fg.instance_type,fm.menu_name "menu_name" ,
UMX_W3H_UTL.getObjectDetails(fm.menu_name) "Permissions",
fo.obj_name ,decode( decode (sign(sysdate - nvl (wf.start_date, sysdate - 1)), 1,decode (sign (nvl(wf.expiration_date, sysdate + 1) - sysdate), 1, 'ACTIVE', 'INACTIVE'),-1, 'INACTIVE', 'ACTIVE'),'INACTIVE',fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'), decode ( (fg.start_date-sysdate)-abs(fg.start_date-sysdate) ,0,fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'), decode((nvl(fg.end_date,sysdate+1)-sysdate)-abs((nvl(fg.end_date,sysdate+1)-sysdate)),0,
fnd_message.get_string('FND','W3H_YES_ACCESSIBILITY'),fnd_message.get_string('FND','W3H_NO_ACCESSIBILITY'))  ) )   "Accessibility"
from fnd_grants fg,fnd_objects fo, fnd_menus fm,wf_local_roles wf
where fg.object_id=fo.object_id
and fm.menu_id=fg.menu_id
and wf.name=fg.grantee_key
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
)
and fo.obj_name = :ObjName