SELECT 'person_id:' || ppf.person_id
  FROM per_all_people_f ppf, per_all_assignments_f paf
 WHERE paf.person_id = ppf.person_id
       AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                               AND ppf.effective_end_date
       AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                               AND paf.effective_end_date
       AND paf.position_id IN
              (    SELECT ppe.parent_position_id
                     FROM PER_POS_STRUCTURE_ELEMENTS ppe
                    WHERE ppe.pos_structure_version_id IN
                             (SELECT pps.position_structure_id
                                FROM per_position_structures pps
                               WHERE pps.primary_position_flag = 'Y')
               START WITH ppe.subordinate_position_id =
                             (SELECT position_id
                                FROM per_all_assignments_f paaf
                               WHERE TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                                         AND paaf.effective_end_date
                                     AND person_id =
                                            (SELECT fu.employee_id
                                               FROM fnd_user fu
                                              WHERE fu.user_id =
                                                       (SELECT created_by
                                                          FROM pon_auction_headers_all
                                                         WHERE award_appr_ame_trans_id =
                                                                  :transactionId)))
                 CONNECT BY PRIOR ppe.parent_position_id =                 
                             ppe.subordinate_position_id)

orcl> select level,ename from emp start with ename='MILLER' connect by prior mgr=empno;

     LEVEL ENAME
---------- ----------
         1 MILLER
         2 CLARK
         3 KING

orcl> update emp set mgr=(select empno from emp where ename='MILLER')
  2  where ename='KING';

1 row updated.

orcl> select level,ename from emp start with ename='MILLER' connect by prior mgr=empno;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

orcl>