VIEW: APPS.IGS_PE_FAMILY_DTLS_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')