Product: | IGS - Student System (Obsolete) |
---|---|
Description: | Alternate ID for API person id information. |
Implementation/DBA Data: | Not implemented in this database |
SELECT PE.PARTY_ID
, IGS_EN_GEN_003.GET_STAFF_IND(PE.PARTY_ID)
, IGS_EN_GEN_007.ENRP_GET_STUDENT_IND(PE.PARTY_ID)
, IGS_EN_GEN_005.ENRP_GET_FRST_ENR_YR(PE.PARTY_ID)
, IGS_EN_GEN_005.ENRP_GET_LAST_ENR_YR(PE.PARTY_ID)
, IGS_EN_GEN_003.ENRP_GET_ENCMBRD_IND(PE.PARTY_ID)
, PE.PERSON_LAST_NAME
, PE.PERSON_FIRST_NAME
, PE.KNOWN_AS
, PE.PERSON_TITLE
, PP.GENDER
, PP.DATE_OF_BIRTH
, DECODE(PP.DATE_OF_DEATH
, NULL
, NVL(PHZ.DECEASED_IND
, 'N')
, 'Y') DECEASED_IND
, PE.SALUTATION
, PHZ.ORACLE_USERNAME
, PE.EMAIL_ADDRESS
, PE.PERSON_FIRST_NAME || ' ' || PE.PERSON_LAST_NAME
, NVL(PE.KNOWN_AS
, SUBSTR (PE.PERSON_FIRST_NAME
, 1
, DECODE(INSTR(PE.PERSON_FIRST_NAME
, ' ')
, 0
, LENGTH(PE.PERSON_FIRST_NAME)
, (INSTR(PE.PERSON_FIRST_NAME
, ' ')-1)))) || ' ' || PE.PERSON_LAST_NAME
, PE.PERSON_TITLE || ' ' || PE.PERSON_FIRST_NAME || ' ' || PE.PERSON_LAST_NAME
, IGS_GE_GEN_002.GENP_GET_INITIALS(PE.PERSON_FIRST_NAME) || ' ' || PE.PERSON_LAST_NAME
, PE.PERSON_LAST_NAME || '
, ' || PE.PERSON_TITLE || ' ' || PE.PERSON_FIRST_NAME
, PAL.SEQUENCE_NUMBER
, PAL.START_DT
, PAL.END_DT
, PAL.SURNAME
, PAL.GIVEN_NAMES
, PAL.TITLE
, PAL.ALIAS_COMMENT
, API.START_DT
, API.END_DT
, API.API_PERSON_ID
, API.PERSON_ID_TYPE
, API.CREATED_BY
, API.CREATION_DATE
, API.LAST_UPDATED_BY
, API.LAST_UPDATE_DATE
, API.LAST_UPDATE_LOGIN
FROM HZ_PARTIES PE
, IGS_PE_HZ_PARTIES PHZ
, IGS_PE_PERSON_ALIAS PAL
, HZ_PERSON_PROFILES PP
, IGS_PE_ALT_PERS_ID API
WHERE PE.PARTY_ID = PHZ.PARTY_ID
AND PAL.PERSON_ID(+) = PE.PARTY_ID
AND API.PE_PERSON_ID (+) = PE.PARTY_ID
AND PE.PARTY_ID = PP.PARTY_ID
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)