VIEW: APPS.UMX_USER_PVT_V

Source

View Text - Preformatted

SELECT user_name, user_id, email_address, person_first_name , person_last_name, party_id, person_id from ( select fu.user_name, fu.user_id, fu.email_address, null as person_first_name, null as person_last_name, null as party_id, null as person_id from fnd_user fu WHERE fu.person_party_id is null and fu.employee_id is null union all select fu.user_name, fu.user_id, fu.email_address, hpr.person_first_name , hpr.person_last_name, hpr.party_id, null as person_id from fnd_user fu, hz_parties hpr where fu.person_party_id is not null and fu.employee_id is null and fu.person_party_id = hpr.party_id union all select fu.user_name, fu.user_id, fu.email_address, emp.first_name , emp.last_name, null as party_id, emp.person_id as person_id from fnd_user fu, per_all_people_f emp where fu.employee_id is not null and fu.employee_id = emp.person_id and nvl(emp.effective_end_date, sysdate + 1) > sysdate and emp.effective_start_date <= sysdate )

View Text - HTML Formatted

SELECT USER_NAME
, USER_ID
, EMAIL_ADDRESS
, PERSON_FIRST_NAME
, PERSON_LAST_NAME
, PARTY_ID
, PERSON_ID
FROM ( SELECT FU.USER_NAME
, FU.USER_ID
, FU.EMAIL_ADDRESS
, NULL AS PERSON_FIRST_NAME
, NULL AS PERSON_LAST_NAME
, NULL AS PARTY_ID
, NULL AS PERSON_ID
FROM FND_USER FU
WHERE FU.PERSON_PARTY_ID IS NULL
AND FU.EMPLOYEE_ID IS NULL UNION ALL SELECT FU.USER_NAME
, FU.USER_ID
, FU.EMAIL_ADDRESS
, HPR.PERSON_FIRST_NAME
, HPR.PERSON_LAST_NAME
, HPR.PARTY_ID
, NULL AS PERSON_ID
FROM FND_USER FU
, HZ_PARTIES HPR
WHERE FU.PERSON_PARTY_ID IS NOT NULL
AND FU.EMPLOYEE_ID IS NULL
AND FU.PERSON_PARTY_ID = HPR.PARTY_ID UNION ALL SELECT FU.USER_NAME
, FU.USER_ID
, FU.EMAIL_ADDRESS
, EMP.FIRST_NAME
, EMP.LAST_NAME
, NULL AS PARTY_ID
, EMP.PERSON_ID AS PERSON_ID
FROM FND_USER FU
, PER_ALL_PEOPLE_F EMP
WHERE FU.EMPLOYEE_ID IS NOT NULL
AND FU.EMPLOYEE_ID = EMP.PERSON_ID
AND NVL(EMP.EFFECTIVE_END_DATE
, SYSDATE + 1) > SYSDATE
AND EMP.EFFECTIVE_START_DATE <= SYSDATE )