VIEW: APPS.IGS_PE_API_ALT_PERS_ID_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)