查询用户具备的职责和用户相关信息
查看所有用户的ID、用户名、职责名和姓:
SELECT FU.USER_ID, FU.USER_NAME, FRV.RESPONSIBILITY_NAME, PPF.LAST_NAME
FROM FND_USER FU,
APPS.FND_USER_RESP_GROUPS_DIRECT FURG,
FND_RESPONSIBILITY_VL FRV,
PER_PEOPLE_F PPF
WHERE FU.USER_ID = FURG.USER_ID
AND FURG.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
AND FU.USER_NAME = 'SIE_ZSH'
AND FU.EMPLOYEE_ID = PPF.PERSON_ID;
根据用户名查寻用户相关信息:
SELECT DISTINCT USERS.USER_ID,
USERS.USER_NAME,
USERS.DESCRIPTION,
USERS.START_DATE USER_START_DATE,
USERS.END_DATE USER_END_DATE,
USERS.EMAIL_ADDRESS,
RESP.RESPONSIBILITY_NAME,
USER_RESP.START_DATE,
USER_RESP.END_DATE,
USER_RESP.LAST_UPDATE_DATE,
APPL.APPLICATION_SHORT_NAME,
APPL.APPLICATION_NAME
FROM APPS.FND_USER USERS,
APPS.FND_USER_RESP_GROUPS_DIRECT USER_RESP,
APPS.FND_RESPONSIBILITY_VL RESP,
APPS.FND_APPLICATION_VL APPL
WHERE USERS.USER_ID = USER_RESP.USER_ID
AND USER_RESP.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
AND USER_RESP.RESPONSIBILITY_ID = RESP.RESPONSIBILITY_ID
AND RESP.APPLICATION_ID = APPL.APPLICATION_ID
AND USERS.USER_NAME = 'SIE_ZSH';