VIEW: APPS.IGS_PE_HZ_REL_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'