AnsweredAssumed Answered

OBS lookup

Question asked by Itai on Aug 26, 2010
Latest reply on May 16, 2012 by Slovak
Hi there,

we've built a lookup that display the OBS for which a user has resource view rights. I wanted to know if someone already built something similar and if the query we are using is well optimized. Maybe there is another way to do.

Thanks
Itaï

select id,
depth,
name,
Region,
ServiceLine,
Practice_Name
from
(
select id,depth,name,Region,ServiceLine,Practice_Name
from
(
SELECT distinct(PRJ.ID),prj.name,prj.depth,nbi.level3_name Region,nbi.level4_name ServiceLine,nbi.level5_name Practice_Name
FROM CMN_SEC_ASSGND_RIGHT cmn, prj_obs_units prj,
cmn_sec_groups_v cmn_v , nbi_dim_obs_flat ndof,
prj_obs_types obs_type,prj_obs_levels,nbi_dim_obs nbi
WHERE (

SELECT COUNT (1) rec

FROM cmn_sec_assgnd_obj_perm_r_v

WHERE object_type ='RECORD'

AND object_code ='PRJ_RESOURCE'

AND permission_code ='ResourceView'

AND component_code = 'PRJ'

AND user_id =5294081

) = 0
AND cmn.instance_id = ndof.parent_obs_unit_id
AND cmn.right_id = cmn_v.ID
AND cmn_v.language_code='en'
AND cmn_v.group_name = 'Resource - View'
AND obs_type.unique_name = 'Profit & Loss OBS'
AND prj.type_id = obs_type.id
AND prj.id =nbi.obs_unit_id
AND ndof.child_obs_unit_id = prj.id
AND nbi.level3_name IS NOT null
and prj.depth<6
AND (

cmn.principal_id = 5294081 OR

cmn.principal_id IN (
SELECT grp.id








FROM CMN_SEC_USER_GROUPS, CMN_SEC_GROUPS grp


WHERE user_id = 5294081
AND group_id = grp.id
AND group_role_type = 'GROUP'







)
)
UNION ALL
SELECT distinct(PRJ.ID),prj.name,prj.depth,nbi.level3_name Region,
nbi.level4_name ServiceLine,nbi.level5_name Practice_Name
FROM prj_obs_units prj ,
Prj_obs_types obs_type,
nbi_dim_obs nbi
WHERE (

SELECT COUNT (1) rec

FROM cmn_sec_assgnd_obj_perm_r_v

WHERE object_type ='RECORD'

AND object_code ='PRJ_RESOURCE'

AND permission_code ='ResourceView'

AND component_code = 'PRJ'

AND user_id = 5294081

) > 0
AND obs_type.unique_name = 'Profit & Loss OBS'
AND prj.id =nbi.obs_unit_id
AND prj.type_id = obs_type.id
AND nbi.level3_name IS NOT null
and prj.depth<6
)
order by region ,ServiceLine nulls first ,Practice_Name nulls first
)

Outcomes