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;
