PROCEDURE sp_get_info  ( w_order_id IN workorder.w_id%TYPE,
    RC1 OUT sys_refcursor )
AS
  v_id obj_details.object_id%TYPE;
  v_sub_id obj_details.sub_obj_id%TYPE;
  
  
  
BEGIN
  OPEN RC1 FOR


WITH subq AS
  (SELECT DISTINCT ci.sub_obj_id,
    ci.path_id
     FROM sub_item ci
    WHERE ci.w_id      = w_order_id
  AND ci.sub_item_code = 'A'
  AND ci.updt_tms      =
    (SELECT MAX (updt_tms)
       FROM sub_item si
      WHERE si.sub_obj_id = ci.sub_obj_id
    AND si.path_id        = ci.path_id
    )
  )
 SELECT subq.sub_obj_id,
  subq.path_id         ,
  status_code          
   FROM obj_details    ,
  subq
  WHERE obj_details.sub_obj_id = subq.sub_obj_id
AND obj_details.path_id        = subq.path_id
AND row_ins_tms                =
  (SELECT MAX (row_ins_tms)
     FROM obj_details x,
    subq
    WHERE x.sub_obj_id = subq.sub_obj_id
  AND x.path_id        = subq.path_id
  );
END sp_get_info;

select username,
          v$lock.sid,
              trunc(id1/power(2,16)) rbs,
               bitand(id1,to_number('ffff','xxxx'))+0 slot,
               id2 seq,
           lmode,
               request
    from v$lock, v$session
   where v$lock.type = 'TX'
     and v$lock.sid = v$session.sid
    and v$session.username = USER
/