SELECT DISTINCT PS.POSITION_STRUCTURE_ID    审批链ID,
                PS.NAME                     审批链名称,
                PE.POS_STRUCTURE_ELEMENT_ID POS_STRUCTURE_ELEMENT_ID,
                HP.POSITION_ID              职位ID,
                HP.NAME                     职位名,
                PPF.PERSON_ID               员工ID,
                PPF.LAST_NAME               用户名,
                PE.ORDER_FLAG               ORDER_FLAG
  FROM PER_POS_STRUCTURE_VERSIONS PV,
       PER_POSITION_STRUCTURES PS,
       (SELECT POS_STRUCTURE_VERSION_ID,
               POS_STRUCTURE_ELEMENT_ID,
               PARENT_POSITION_ID,
               ORDER_FLAG
          FROM (SELECT PE.POS_STRUCTURE_VERSION_ID,
                       PE.POS_STRUCTURE_ELEMENT_ID,
                       PE.PARENT_POSITION_ID,
                       '1' ORDER_FLAG
                  FROM PER_POS_STRUCTURE_ELEMENTS PE
                UNION ALL
                SELECT PE.POS_STRUCTURE_VERSION_ID,
                       PE.POS_STRUCTURE_ELEMENT_ID,
                       PE.SUBORDINATE_POSITION_ID PARENT_POSITION_ID,
                       '2' ORDER_FLAG
                  FROM PER_POS_STRUCTURE_ELEMENTS PE
                 WHERE NOT EXISTS (SELECT 1
                          FROM PER_POS_STRUCTURE_ELEMENTS PE2
                         WHERE PE2.POS_STRUCTURE_VERSION_ID =
                               PE.POS_STRUCTURE_VERSION_ID
                           AND PE.SUBORDINATE_POSITION_ID =
                               PE2.PARENT_POSITION_ID))) PE,
       PER_PEOPLE_F PPF,
       PER_ALL_ASSIGNMENTS_F PAF,
       HR_POSITIONS_F HP
 WHERE PV.POS_STRUCTURE_VERSION_ID = PE.POS_STRUCTURE_VERSION_ID
   AND PV.POSITION_STRUCTURE_ID = PS.POSITION_STRUCTURE_ID
   AND PPF.PERSON_ID = PAF.PERSON_ID
   AND PE.PARENT_POSITION_ID = HP.POSITION_ID
   AND HP.POSITION_ID = PAF.POSITION_ID
   AND (PAF.EFFECTIVE_END_DATE IS NULL OR PAF.EFFECTIVE_END_DATE > SYSDATE)
   AND (HP.EFFECTIVE_END_DATE IS NULL OR HP.EFFECTIVE_END_DATE > SYSDATE)
   AND (PPF.EFFECTIVE_END_DATE IS NULL OR PPF.EFFECTIVE_END_DATE > SYSDATE)
   AND PAF.EFFECTIVE_END_DATE >= SYSDATE
   –AND PS.NAME = '000100预算申请审批层次'
 ORDER BY PS.POSITION_STRUCTURE_ID,
          PE.POS_STRUCTURE_ELEMENT_ID,
          PE.ORDER_FLAG;