VIEW: APPS.IGS_PE_API_ALT_PERS_API_ID_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)