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;

SELECT FFV.DESCRIPTION     机构描述,
       PP.NAME             职位,
       FU.DESCRIPTION      描述,
       PA.AGENT_ID         采购员ID,
       FU.USER_ID          用户ID,
       FU.USER_NAME        系统名称,
       PPF.FULL_NAME       全名,
       PPF.EMPLOYEE_NUMBER 员工编号

  FROM PER_PEOPLE_F        PPF,
       PER_ASSIGNMENTS_F   PAF,
       PER_POSITIONS       PP,
       FND_USER            FU,
       PO_AGENTS           PA,
       FND_FLEX_VALUES_VL  FFV,
       FND_FLEX_VALUE_SETS FF
 WHERE PPF.PERSON_ID = PAF.PERSON_ID
   AND FFV.FLEX_VALUE_SET_ID = FF.FLEX_VALUE_SET_ID
   AND FFV.ENABLED_FLAG = 'Y'
   AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
   AND FFV.FLEX_VALUE = SUBSTR(PP.NAME, 1, INSTR(PP.NAME, '.', 1, 1) – 1)
   AND FF.FLEX_VALUE_SET_NAME = '&ORG_COA'
   AND FFV.SUMMARY_FLAG = 'N'
   AND PA.AGENT_ID = PPF.PERSON_ID
   AND FU.EMPLOYEE_ID = PPF.PERSON_ID
   AND PP.POSITION_ID = PAF.POSITION_ID
   AND PPF.PERSON_ID IN
       (SELECT DISTINCT PA.AGENT_ID
          FROM PO_AGENTS PA, PER_ALL_PEOPLE_F PAPF, FND_USER FU
         WHERE PA.AGENT_ID = PAPF.PERSON_ID
           AND FU.EMPLOYEE_ID = PAPF.PERSON_ID
           AND (PAPF.EMPLOYEE_NUMBER IS NOT NULL OR
               PAPF.NPW_NUMBER IS NOT NULL)
           AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
               PAPF.EFFECTIVE_END_DATE)
   AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
   AND SYSDATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
   AND (FU.END_DATE IS NULL OR FU.END_DATE > SYSDATE)
   AND (PP.DATE_END IS NULL OR PP.DATE_END > SYSDATE)
 ORDER BY PP.NAME, FU.CREATION_DATE DESC;