View: IGS_PE_API_ALT_PERS_ID_V

View Text

SELECT PE.PARTY_ID
, PE.PARTY_NUMBER
, PHZ.ORACLE_USERNAME
, IGS_EN_GEN_003.GET_STAFF_IND(PE.PARTY_ID)
, IGS_EN_GEN_007.ENRP_GET_STUDENT_IND(PE.PARTY_ID)
, IGS_EN_GEN_003.ENRP_GET_ENCMBRD_IND(PE.PARTY_ID)
, IGS_EN_GEN_005.ENRP_GET_FIRST_ENR(PE.PARTY_ID)
, IGS_EN_GEN_005.ENRP_GET_LAST_ENR(PE.PARTY_ID)
, TO_NUMBER(NULL) PAL_SEQUENCE_NUMBER
, TO_DATE(NULL) PAL_START_DT
, TO_DATE(NULL) PAL_END_DT
, PP.PERSON_NAME
, PE.PERSON_LAST_NAME
, PE.PERSON_FIRST_NAME
, PE.KNOWN_AS
, PP.GENDER
, TO_CHAR(NULL) SEX_MEANING
, PP.DATE_OF_BIRTH
, PE.EMAIL_ADDRESS
, PE.PERSON_TITLE
, PE.PERSON_NAME_SUFFIX
, PE.PERSON_PRE_NAME_ADJUNCT PREFIX
, PE.SALUTATION
, DECODE(PP.DATE_OF_DEATH
, NULL
, NVL(PHZ.DECEASED_IND
, 'N')
, 'Y') DECEASED_IND
, TO_CHAR(NULL) PAL_COMMENT
, API.START_DT
, API.END_DT
, API.API_PERSON_ID
, API.PERSON_ID_TYPE
, API.DESCRIPTION
, API.PREFERRED_IND
, NULL DISPLAY_OB_IND
, NULL PERSON_ALIAS_DISPLAY
, NULL ADDR_TYPE
, PE.ADDRESS1 ADDR_LINE_1
, PE.ADDRESS2 ADDR_LINE_2
, PE.ADDRESS3 ADDR_LINE_3
, PE.ADDRESS4 ADDR_LINE_4
, PE.CITY CITY
, PE.STATE STATE
, PE.PROVINCE PROVINCE
, PE.COUNTY COUNTY
, PE.COUNTRY COUNTRY
, PE.POSTAL_CODE POSTAL_CODE
, TO_DATE(NULL) START_DT
, TO_DATE(NULL) END_DT
, NULL OTHER_DETAILS_1
, NULL OTHER_DETAILS_2
, NULL OTHER_DETAILS_3
, NULL CORRESPONDENCE_IND
, PE.CREATED_BY
, PE.CREATION_DATE
, PE.LAST_UPDATED_BY
, PE.LAST_UPDATE_DATE
, PE.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) PARTY_SITE_ID
, TO_NUMBER(NULL) LOCATION_ID
, PE.STATUS
, NULL PS_STATUS
FROM HZ_PARTIES PE
, IGS_PE_HZ_PARTIES PHZ
, HZ_PERSON_PROFILES PP
, (SELECT API.START_DT
, API.END_DT
, API.API_PERSON_ID
, API.PERSON_ID_TYPE
, PID.DESCRIPTION
, PID.PREFERRED_IND
, API.PE_PERSON_ID
FROM IGS_PE_ALT_PERS_ID API
, IGS_PE_PERSON_ID_TYP PID
WHERE API.PERSON_ID_TYPE = PID.PERSON_ID_TYPE
AND NVL(END_DT
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD')) <> START_DT ) API
WHERE PE.PARTY_ID = PHZ.PARTY_ID (+)
AND PE.PARTY_ID = PP.PARTY_ID
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)
AND PE.PARTY_ID = API.PE_PERSON_ID (+)

Columns