View: IGS_PE_HZ_REL_V

Product:	IGS - Student System
Description:	View based on join between IGS_PE_HZ_REL and HZ_RELATIONSHIPS
Implementation/DBA Data:	APPS.IGS_PE_HZ_REL_V

View Text

SELECT PR.ROWID ROW_ID
, PR.PARTY_ID PARTY_ID
, PR.RELATIONSHIP_ID RELATIONSHIP_ID
, PR.SUBJECT_ID SUBJECT_ID
, PR.OBJECT_ID OBJECT_ID
, HZP.PARTY_NUMBER OBJECT_ID_NUMBER
, PR.RELATIONSHIP_TYPE PARTY_RELATIONSHIP_TYPE
, L1.MEANING PARTY_RELATIONSHIP_TYP_MEANING
, PR.RELATIONSHIP_CODE RELATIONSHIP_CODE
, PR.DIRECTIONAL_FLAG DIRECTIONAL_FLAG
, PP.PERSON_NAME FULL_NAME
, PE.PRIMARY PRIMARY
, PE.SECONDARY SECONDARY
, PE.JOINT_SALUTATION JOINT_SALUTATION
, PE.REP_FACULTY REP_FACULTY
, PE.REP_STAFF REP_STAFF
, PE.REP_STUDENT REP_STUDENT
, PE.REP_ALUMNI REP_ALUMNI
, PR.START_DATE START_DATE
, PR.END_DATE END_DATE
, PE.NEXT_TO_KIN NEXT_TO_KIN
, PR.COMMENTS COMMENTS
, PR.CONTENT_SOURCE_TYPE CONTENT_SOURCE_TYPE
, PR.STATUS STATUS
, PR.OBJECT_VERSION_NUMBER
, PR.ATTRIBUTE_CATEGORY
, PR.ATTRIBUTE1
, PR.ATTRIBUTE2
, PR.ATTRIBUTE3
, PR.ATTRIBUTE4
, PR.ATTRIBUTE5
, PR.ATTRIBUTE6
, PR.ATTRIBUTE7
, PR.ATTRIBUTE8
, PR.ATTRIBUTE9
, PR.ATTRIBUTE10
, PR.ATTRIBUTE11
, PR.ATTRIBUTE12
, PR.ATTRIBUTE13
, PR.ATTRIBUTE14
, PR.ATTRIBUTE15
, PR.ATTRIBUTE16
, PR.ATTRIBUTE17
, PR.ATTRIBUTE18
, PR.ATTRIBUTE19
, PR.ATTRIBUTE20
, PR.GLOBAL_ATTRIBUTE_CATEGORY
, PR.GLOBAL_ATTRIBUTE1
, PR.GLOBAL_ATTRIBUTE2
, PR.GLOBAL_ATTRIBUTE3
, PR.GLOBAL_ATTRIBUTE4
, PR.GLOBAL_ATTRIBUTE5
, PR.GLOBAL_ATTRIBUTE6
, PR.GLOBAL_ATTRIBUTE7
, PR.GLOBAL_ATTRIBUTE8
, PR.GLOBAL_ATTRIBUTE9
, PR.GLOBAL_ATTRIBUTE10
, PR.GLOBAL_ATTRIBUTE11
, PR.GLOBAL_ATTRIBUTE12
, PR.GLOBAL_ATTRIBUTE13
, PR.GLOBAL_ATTRIBUTE14
, PR.GLOBAL_ATTRIBUTE15
, PR.GLOBAL_ATTRIBUTE16
, PR.GLOBAL_ATTRIBUTE17
, PR.GLOBAL_ATTRIBUTE18
, PR.GLOBAL_ATTRIBUTE19
, PR.GLOBAL_ATTRIBUTE20
, PR.CREATED_BY CREATED_BY
, PR.CREATION_DATE CREATION_DATE
, PR.LAST_UPDATED_BY LAST_UPDATED_BY
, PR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, HZP.PERSON_PRE_NAME_ADJUNCT PREFIX
, HZP.PERSON_FIRST_NAME FIRST_NAME
, HZP.PERSON_MIDDLE_NAME MIDDLE_NAME
, HZP.PERSON_LAST_NAME LAST_NAME
, HZP.PERSON_NAME_SUFFIX SUFFIX
, PP.DATE_OF_BIRTH DATE_OF_BIRTH
, PP.GENDER GENDER
, HZP.PERSON_TITLE
, HZP.KNOWN_AS
, PP.MARITAL_STATUS
, HZP.OBJECT_VERSION_NUMBER HP_OVN
, PE.EMERGENCY_CONTACT_FLAG
, PR.SUBJECT_TYPE
, PR.OBJECT_TYPE
, PR.START_DATE REL_START_DATE
, PR.END_DATE REL_END_DATE
, IGS_PE_GEN_004.GET_DECEASED_INDICATOR (HZP.PARTY_ID) DECEASED_IND
, CASE WHEN (PE.PRIMARY = 'Y' OR PE.SECONDARY = 'Y') THEN 'Y' ELSE 'N' END JOINT_MAILING
FROM HZ_RELATIONSHIPS PR
, HZ_PARTIES HZP
, HZ_PERSON_PROFILES PP
, IGS_PE_HZ_REL PE
, FND_LOOKUP_VALUES L1
WHERE PR.RELATIONSHIP_ID = PE.RELATIONSHIP_ID(+)
AND PR.DIRECTIONAL_FLAG = PE.DIRECTIONAL_FLAG(+)
AND PR.OBJECT_ID = HZP.PARTY_ID
AND HZP.PARTY_ID = PP.PARTY_ID
AND L1.LOOKUP_CODE = PR.RELATIONSHIP_CODE
AND L1.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE'
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)
AND L1.VIEW_APPLICATION_ID = 222
AND L1.LANGUAGE = USERENV('LANG')
AND L1.SECURITY_GROUP_ID = 0
AND PR.STATUS = 'A'

Columns