View: IGS_PE_FAMILY_DTLS_V

View Text

SELECT PFD.ROWID ROW_ID
, PFD.FAMILY_DTL_ID FAMILY_DTL_ID
, HPR.SUBJECT_ID PERSON_ID
, HP1.PARTY_NUMBER PERSON_NUMBER
, PFD.MEMBER_ID MEMBER_ID
, HP.PARTY_NUMBER MEMBER_NUMBER
, HP.PERSON_LAST_NAME SURNAME
, HP.PERSON_FIRST_NAME GIVEN_NAMES
, HPR.RELATIONSHIP_ID RELATIONSHIP_TYPE_ID
, HPR.RELATIONSHIP_TYPE RELATIONSHIP_TYPE
, L4.MEANING RELATIONSHIP_TYPE_MEANING
, HPR.RELATIONSHIP_CODE RELATIONSHIP_CODE
, HPR.DIRECTIONAL_FLAG RECIPROCAL
, PFD.PRIMARY PRIMARY
, PFD.SECONDARY SECONDARY
, HP.PERSON_PRE_NAME_ADJUNCT PRE_NAME_ADJUNCT
, L3.MEANING PRE_NAME_ADJUNCT_MEANING
, HP.PERSON_NAME_SUFFIX SUFFIX
, PFD.JOINT_SALUTATION JOINT_SALUTATION
, HP.PERSON_TITLE TITLE
, PP1.GENDER SEX
, L1.MEANING SEX_MEANING
, PP1.PERSON_PROFILE_ID MEMBER_PROFILE_ID
, PP1.MARITAL_STATUS MARITAL_STATUS
, L5.MEANING MARITAL_STATUS_M
, HP.EMAIL_ADDRESS EMAIL_ADDRESS
, PFD.FACULTY FACULTY
, PFD.STAFF STAFF
, PFD.ALUMNI ALUMNI
, PFD.CURRENT_STUDENT CURRENT_STUDENT
, PP2.PERSON_PROFILE_ID PERSON_PROFILE_ID
, SD2.NEXT_TO_KIN DSP_NEXTTOKIN
, PHP.DECEASED_IND DECEASED
, HPR.START_DATE START_DATE
, HPR.END_DATE END_DATE
, PFD.CREATION_DATE CREATION_DATE
, PFD.CREATED_BY CREATED_BY
, PFD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PFD.LAST_UPDATED_BY LAST_UPDATED_BY
, PFD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, HPR.LAST_UPDATE_DATE REL_LAST_UPDATE_DATE
, HP.PERSON_MIDDLE_NAME
FROM IGS_PE_FAMILY_DTLS PFD
, HZ_PARTIES HP
, HZ_PARTIES HP1
, HZ_RELATIONSHIPS HPR
, HZ_PERSON_PROFILES PP1
, HZ_PERSON_PROFILES PP2
, IGS_PE_STAT_DETAILS SD2
, IGS_PE_HZ_PARTIES PHP
, FND_LOOKUP_VALUES L1
, FND_LOOKUP_VALUES L3
, FND_LOOKUP_VALUES L4
, IGS_LOOKUPS_VIEW L5
WHERE HP.PARTY_ID = PFD.MEMBER_ID
AND L3.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND HP.PERSON_PRE_NAME_ADJUNCT = L3.LOOKUP_CODE (+)
AND HPR.OBJECT_ID = PFD.MEMBER_ID
AND L4.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE'
AND HPR.RELATIONSHIP_CODE = L4.LOOKUP_CODE (+)
AND L5.LOOKUP_TYPE(+) = 'MARITAL_STATUS'
AND L5.LOOKUP_CODE(+) = PP1.MARITAL_STATUS
AND HP1.PARTY_ID = HPR.SUBJECT_ID
AND PP1.PARTY_ID = PFD.MEMBER_ID
AND SYSDATE BETWEEN PP1.EFFECTIVE_START_DATE
AND NVL(PP1.EFFECTIVE_END_DATE
, SYSDATE)
AND PP2.PARTY_ID = HPR.SUBJECT_ID
AND PFD.MEMBER_ID = SD2.PERSON_ID(+)
AND SYSDATE BETWEEN PP2.EFFECTIVE_START_DATE
AND NVL(PP2.EFFECTIVE_END_DATE
, SYSDATE)
AND L1.LOOKUP_TYPE (+) = 'HZ_GENDER'
AND L1.LOOKUP_CODE (+) = PP1.GENDER
AND PHP.PARTY_ID = PFD.MEMBER_ID
AND L1.VIEW_APPLICATION_ID(+) = 222
AND L1.SECURITY_GROUP_ID (+) = 0
AND L1.LANGUAGE(+) = USERENV('LANG')

Columns