Query to get ESS Request details(Oracle Fusion)

 SELECT UNIQUE rh1.requestid Child_requestid,rp1.value job_id --,rh.PARENTREQUESTID request_id

  FROM fusion.ess_request_history rh1, fusion.ess_request_property rp1

WHERE rh1.requestid = rp1.requestid

and rp1.name ='outputOverrideParameter'

--and rh.PARENTREQUESTID in (

AND EXISTS (

SELECT UNIQUE rh.requestid Child_requestid --,rh.PARENTREQUESTID request_id

  FROM fusion.ess_request_history rh, fusion.ess_request_property rp

WHERE rh.requestid = rp.requestid

--and rh.requestid = :P_REQ_ID

AND (rh.requestid  = rh1.PARENTREQUESTID OR rh.requestid  = rh1.requestid)

and rh.PARENTREQUESTID = :P_REQ_ID

   )

and rownum = 1