Query to get the resource details based Hiearchial

select srm.Full_name,

srm.last_name,

srm.first_name,

srm.email,

srm.User_Id,

srm.date_of_hire,

(select prIsOpen from prj_resources pr where srm.id=pr.prid) openfortime, 

(select full_name from srm_resources where user_id=srm.manager_id)manager_name,

(select ndo.path OBS from prj_obs_associations poa,nbi_dim_obs ndo where srm.id=poa.record_id 

AND poa.unit_id=ndo.obs_unit_id

AND ndo.obs_type_id='5000010')"Resource OBS",

(select srm.full_name from prj_resources pr where PR.PRPRIMARYROLEID = srm.ID and srm.id=pr.prid) Role,

(select cmn.name from cmn_lookups_v cmn where cmn.language_code='en' and cmn.lookup_type='SRM_RESOURCE_TYPE' and srm.person_type=cmn.id) "Employment Type",

--decode(SR.IS_ACTIVE, 0, 'Inactive', 1, 'Active') "Active/Inactive"

decode(user_status_id, 201, 'inactive', 200, 'Active',202,'Lock') "Active/Inactive/Lock"

--CSU.USER_STATUS_ID

from srm_resources srm,

cmn_sec_users csu,

prj_resources pr,

prj_obs_associations poa,

nbi_dim_obs ndo

where csu.id=srm.user_id and user_status_id='200'

AND srm.id=pr.prid

AND srm.id=poa.record_id

AND poa.unit_id=ndo.obs_unit_id

AND poa.table_name='SRM_RESOURCES'

AND ndo.obs_type_id='5000010'

and poa.unit_id='5000009'