SELECT srm.last_name "Last Name",
srm.first_name "First Name",
srm.unique_name "Resource ID",
srm.email "Email Address",
(SELECT srm.last_name ||srm.first_name FROM srm_resources srm WHERE srm.id=
prj.prprimaryroleid) "Primary Role",
DECODE(srm.person_type,300,'EMPLOYEE',301,'CONTRACTOR',5000580,'EXEMPT',
5000581,'NON-EXEMPT',5000620,'TEMPORARY') "Employment Type",
SRM.DATE_OF_HIRE "Date of Hire",
srm.date_of_termination "Date of Termination",
PR.HOURS_PER_DAY "Availability",
odr.contract_time_week "Contract Hours",
DECODE(prj.prtrackmode,0,'None',1,'Other',2,'Clarity') "Track Mode",
DECODE(prj.prisopen,0,'false','true') "Open for Time-entry",
(SELECT cmn.last_name ||', '||cmn.first_name FROM cmn_sec_users cmn WHERE
cmn.id=srm.manager_id) "Manager Name",
(SELECT user_name FROM cmn_sec_users cmn WHERE cmn.id=srm.manager_id)
"Manager Id",
DECODE(srm.is_active,1,'true','false') "Status",
DECODE(srm.is_external,1,'true','false') "External",
cmn.Timezone "Timezone",
(SELECT DECODE(prt.prname,'Normal Working Hours','Standard','Overtime') FROM prtypecode prt WHERE prt.prid=prj.prtypecodeid)
"Input Type Code",
cmn.user_name "User Name",
odr.salary_number "Employee Number",
DECODE(odr.employee_charge_type,'ect_direct','Direct','ect_indirect','Indirect') "Charge Type",
NBI.PATH HIERARCHICAL_OBS,
sysdate "Extract Date"
FROM srm_resources srm
JOIN prj_resources prj
ON srm.id=prj.prid
JOIN odf_ca_resource odr
ON srm.id=odr.id
JOIN cmn_sec_users cmn
ON cmn.id=srm.user_id
Join PRCALENDAR PR
on prj.PRCALENDARID=PR.PRID
JOIN prj_obs_associations aso
ON aso.record_id = srm.id
Join prj_obs_units_flat pouf
ON aso.unit_id = pouf.unit_id
JOIN obs_units_flat_by_mode mde
ON mde.linked_unit_id = aso.unit_id
JOIN nbi_dim_obs nbi
ON aso.unit_id=nbi.obs_unit_id
WHERE mde.unit_mode ='OBS_UNIT_AND_CHILDREN'
AND mde.unit_id =5054143
AND aso.table_name ='SRM_RESOURCES' and NBI.OBS_TYPE_ID=5000009
and
srm.is_active='1'
