View: WF_USERS_OLD

View Text

SELECT /* $HEADER: AFREGVW.LDT 115.3 2002/06/17 18:39:12 SSUNG NOSHIP $ */ USR.USER_NAME
, PER.FULL_NAME
, PER.FULL_NAME
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'MAILTYPE')
, 'MAILHTML')
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'LANGUAGE')
, FNDL.NLS_LANGUAGE)
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'TERRITORY')
, FNDL.NLS_TERRITORY)
, PER.EMAIL_ADDRESS
, USR.FAX
, 'PER'
, PER.PERSON_ID
, DECODE(USR.END_DATE
, NULL
, 'ACTIVE'
, 'INACTIVE')
, TO_DATE(NULL)
FROM FND_LANGUAGES FNDL
, PER_ALL_PEOPLE_F PER
, FND_USER USR
WHERE TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND PER.PERSON_ID = USR.EMPLOYEE_ID
AND FNDL.INSTALLED_FLAG = 'B' /* FND_USERS NOT LINKED TO PER_ALL_PEOPLE_F */ UNION ALL SELECT USR.USER_NAME
, USR.USER_NAME
, USR.DESCRIPTION
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'MAILTYPE')
, 'MAILHTML')
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'LANGUAGE')
, FNDL.NLS_LANGUAGE)
, NVL(WF_PREF.GET_PREF(USR.USER_NAME
, 'TERRITORY')
, FNDL.NLS_TERRITORY)
, USR.EMAIL_ADDRESS
, USR.FAX
, 'FND_USR'
, USR.USER_ID
, DECODE(USR.END_DATE
, NULL
, 'ACTIVE'
, 'INACTIVE')
, TO_DATE(NULL)
FROM FND_LANGUAGES FNDL
, FND_USER USR
WHERE USR.EMPLOYEE_ID IS NULL
AND FNDL.INSTALLED_FLAG = 'B' /* CUSTOMER ADDRESS CONTACTS (2 SELECTS) THESE REPLACE PREVIOUS CUST_CONT QUERY : SEE BUG 1391687 */ UNION ALL SELECT 'HZ_PARTY:'||TO_CHAR(PR.PARTY_ID)
, PER.PARTY_NAME
, PRP.PARTY_NAME
, 'MAILTEXT'
, NVL(L1.NLS_LANGUAGE
, L2.NLS_LANGUAGE)
, NVL(T1.NLS_TERRITORY
, T2.NLS_TERRITORY)
, PRP.EMAIL_ADDRESS
, NULL
, 'HZ_PARTY'
, PR.PARTY_ID
, DECODE(PRP.STATUS
, 'I'
, 'INACTIVE'
, 'ACTIVE')
, TO_DATE(NULL)
FROM HZ_PARTY_RELATIONSHIPS PR
, HZ_PARTIES PRP
, HZ_ORG_CONTACTS OC
, HZ_PARTIES PER
, HZ_PERSON_LANGUAGE PL
, HZ_PARTIES OBJ
, FND_TERRITORIES T1
, FND_TERRITORIES T2
, FND_LANGUAGES L1
, FND_LANGUAGES L2
WHERE PR.SUBJECT_ID = PER.PARTY_ID
AND PR.PARTY_ID = PRP.PARTY_ID
AND PR.PARTY_RELATIONSHIP_ID = OC.PARTY_RELATIONSHIP_ID
AND PR.OBJECT_ID = OBJ.PARTY_ID
AND PRP.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PRP.COUNTRY = T1.TERRITORY_CODE(+)
AND OBJ.COUNTRY = T2.TERRITORY_CODE(+)
AND PER.PARTY_ID = PL.PARTY_ID(+)
AND PL.PRIMARY_LANGUAGE_INDICATOR(+) = 'Y'
AND PL.LANGUAGE_NAME = L1.LANGUAGE_CODE(+)
AND T2.NLS_TERRITORY = L2.NLS_TERRITORY(+) UNION ALL SELECT 'HZ_PARTY:'||TO_CHAR(P.PARTY_ID)
, P.PARTY_NAME
, P.PARTY_NAME
, 'MAILTEXT'
, NVL(L1.NLS_LANGUAGE
, L2.NLS_LANGUAGE)
, T.NLS_TERRITORY
, P.EMAIL_ADDRESS
, NULL
, 'HZ_PARTY'
, P.PARTY_ID
, DECODE(P.STATUS
, 'I'
, 'INACTIVE'
, 'ACTIVE')
, TO_DATE(NULL)
FROM HZ_PARTIES P
, HZ_PERSON_LANGUAGE PL
, FND_TERRITORIES T
, FND_LANGUAGES L1
, FND_LANGUAGES L2
WHERE P.PARTY_TYPE = 'PERSON'
AND P.COUNTRY = T.TERRITORY_CODE(+)
AND P.PARTY_ID = PL.PARTY_ID(+)
AND PL.PRIMARY_LANGUAGE_INDICATOR(+) = 'Y'
AND PL.LANGUAGE_NAME = L1.LANGUAGE_CODE(+)
AND T.NLS_TERRITORY = L2.NLS_TERRITORY(+)

Columns