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;