View: CSC_HZ_PARTIES_SELF_NEW_V

Product:	CSC - Customer Care
Description:	View of HZ_PARTIES, HZ_RELATIONSHIPS. This view is used in Contact center form to get party relationships details along with self party relationship details (Fetches data from the new Relationships model)
Implementation/DBA Data:	APPS.CSC_HZ_PARTIES_SELF_NEW_V

View Text

SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY_REL.RELATIONSHIP_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, SUB_PARTY.PARTY_ID
, SUB_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_ID
, OBJ_PARTY.PARTY_NUMBER
, OBJ_PARTY.PARTY_NAME
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, OBJ_PARTY.PARTY_TYPE
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.PARTY_NAME
, OBJ_PARTY.GROUP_TYPE
, OBJ_PARTY.PERSON_FIRST_NAME
, OBJ_PARTY.PERSON_MIDDLE_NAME
, OBJ_PARTY.PERSON_LAST_NAME
, OBJ_PARTY.PERSON_NAME_SUFFIX
, OBJ_PARTY.PERSON_PRE_NAME_ADJUNCT
, OBJ_PARTY.KNOWN_AS
, OBJ_PARTY.SIC_CODE
, SUB_PARTY.PARTY_TYPE
, SUB_PARTY.PARTY_NAME
, SUB_PARTY.PERSON_FIRST_NAME
, SUB_PARTY.PERSON_MIDDLE_NAME
, SUB_PARTY.PERSON_LAST_NAME
, SUB_PARTY.PERSON_NAME_SUFFIX
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, SUB_PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY_REL.RELATIONSHIP_CODE
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.ADDRESS1
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, OBJ_PARTY.ATTRIBUTE_CATEGORY
, OBJ_PARTY.ATTRIBUTE1
, OBJ_PARTY.ATTRIBUTE2
, OBJ_PARTY.ATTRIBUTE3
, OBJ_PARTY.ATTRIBUTE4
, OBJ_PARTY.ATTRIBUTE5
, OBJ_PARTY.ATTRIBUTE6
, OBJ_PARTY.ATTRIBUTE7
, OBJ_PARTY.ATTRIBUTE8
, OBJ_PARTY.ATTRIBUTE9
, OBJ_PARTY.ATTRIBUTE10
, OBJ_PARTY.ATTRIBUTE11
, OBJ_PARTY.ATTRIBUTE12
, OBJ_PARTY.ATTRIBUTE13
, OBJ_PARTY.ATTRIBUTE14
, OBJ_PARTY.ATTRIBUTE15
, OBJ_PARTY.ATTRIBUTE16
, OBJ_PARTY.ATTRIBUTE17
, OBJ_PARTY.ATTRIBUTE18
, OBJ_PARTY.ATTRIBUTE19
, OBJ_PARTY.ATTRIBUTE20
, OBJ_PARTY.ATTRIBUTE21
, OBJ_PARTY.ATTRIBUTE22
, OBJ_PARTY.ATTRIBUTE23
, OBJ_PARTY.ATTRIBUTE24
, OBJ_PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, OBJ_PARTY.GLOBAL_ATTRIBUTE1
, OBJ_PARTY.GLOBAL_ATTRIBUTE2
, OBJ_PARTY.GLOBAL_ATTRIBUTE3
, OBJ_PARTY.GLOBAL_ATTRIBUTE4
, OBJ_PARTY.GLOBAL_ATTRIBUTE5
, OBJ_PARTY.GLOBAL_ATTRIBUTE6
, OBJ_PARTY.GLOBAL_ATTRIBUTE7
, OBJ_PARTY.GLOBAL_ATTRIBUTE8
, OBJ_PARTY.GLOBAL_ATTRIBUTE9
, OBJ_PARTY.GLOBAL_ATTRIBUTE10
, OBJ_PARTY.GLOBAL_ATTRIBUTE11
, OBJ_PARTY.GLOBAL_ATTRIBUTE12
, OBJ_PARTY.GLOBAL_ATTRIBUTE13
, OBJ_PARTY.GLOBAL_ATTRIBUTE14
, OBJ_PARTY.GLOBAL_ATTRIBUTE15
, OBJ_PARTY.GLOBAL_ATTRIBUTE16
, OBJ_PARTY.GLOBAL_ATTRIBUTE17
, OBJ_PARTY.GLOBAL_ATTRIBUTE18
, OBJ_PARTY.GLOBAL_ATTRIBUTE19
, OBJ_PARTY.GLOBAL_ATTRIBUTE20
, SUB_PARTY.LAST_UPDATE_DATE
, OBJ_PARTY.LAST_UPDATE_DATE
, PARTY_REL.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, OBJ_PARTY.STATUS
, SUB_PARTY.STATUS
FROM HZ_PARTIES PARTY
, HZ_PARTIES SUB_PARTY
, HZ_PARTIES OBJ_PARTY
, HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY.PARTY_ID(+) = PARTY_REL.PARTY_ID
AND (PARTY_REL.SUBJECT_ID = SUB_PARTY.PARTY_ID
AND PARTY_REL.OBJECT_ID = OBJ_PARTY.PARTY_ID)
AND NVL(PARTY_REL.START_DATE
, SYSDATE-1) < SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE+1) > SYSDATE UNION ALL SELECT PARTY.ROWID
, PARTY.PARTY_ID
, TO_NUMBER(NULL)
, PARTY.PARTY_NUMBER
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_ID
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'PERSON'
, PARTY.PARTY_NUMBER
, NULL)
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATION_DATE
, PARTY.CREATED_BY
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, PARTY.GROUP_TYPE
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY.KNOWN_AS
, PARTY.SIC_CODE
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_MIDDLE_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PERSON_NAME_SUFFIX
, PARTY.PERSON_PRE_NAME_ADJUNCT
, PARTY.PERSON_PRE_NAME_ADJUNCT
, 'SELF'
, PARTY.ADDRESS1||DECODE(PARTY.ADDRESS2
, NULL
, NULL
, ';'||PARTY.ADDRESS2|| DECODE(PARTY.ADDRESS3
, NULL
, NULL
, ';'||PARTY.ADDRESS3|| DECODE(PARTY.ADDRESS4
, NULL
, NULL
, ';'||PARTY.ADDRESS4))) ADDRESS
, PARTY.ADDRESS1
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, PARTY.COUNTRY
, PARTY.ATTRIBUTE_CATEGORY
, PARTY.ATTRIBUTE1
, PARTY.ATTRIBUTE2
, PARTY.ATTRIBUTE3
, PARTY.ATTRIBUTE4
, PARTY.ATTRIBUTE5
, PARTY.ATTRIBUTE6
, PARTY.ATTRIBUTE7
, PARTY.ATTRIBUTE8
, PARTY.ATTRIBUTE9
, PARTY.ATTRIBUTE10
, PARTY.ATTRIBUTE11
, PARTY.ATTRIBUTE12
, PARTY.ATTRIBUTE13
, PARTY.ATTRIBUTE14
, PARTY.ATTRIBUTE15
, PARTY.ATTRIBUTE16
, PARTY.ATTRIBUTE17
, PARTY.ATTRIBUTE18
, PARTY.ATTRIBUTE19
, PARTY.ATTRIBUTE20
, PARTY.ATTRIBUTE21
, PARTY.ATTRIBUTE22
, PARTY.ATTRIBUTE23
, PARTY.ATTRIBUTE24
, PARTY.GLOBAL_ATTRIBUTE_CATEGORY
, PARTY.GLOBAL_ATTRIBUTE1
, PARTY.GLOBAL_ATTRIBUTE2
, PARTY.GLOBAL_ATTRIBUTE3
, PARTY.GLOBAL_ATTRIBUTE4
, PARTY.GLOBAL_ATTRIBUTE5
, PARTY.GLOBAL_ATTRIBUTE6
, PARTY.GLOBAL_ATTRIBUTE7
, PARTY.GLOBAL_ATTRIBUTE8
, PARTY.GLOBAL_ATTRIBUTE9
, PARTY.GLOBAL_ATTRIBUTE10
, PARTY.GLOBAL_ATTRIBUTE11
, PARTY.GLOBAL_ATTRIBUTE12
, PARTY.GLOBAL_ATTRIBUTE13
, PARTY.GLOBAL_ATTRIBUTE14
, PARTY.GLOBAL_ATTRIBUTE15
, PARTY.GLOBAL_ATTRIBUTE16
, PARTY.GLOBAL_ATTRIBUTE17
, PARTY.GLOBAL_ATTRIBUTE18
, PARTY.GLOBAL_ATTRIBUTE19
, PARTY.GLOBAL_ATTRIBUTE20
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATE_DATE
, TO_DATE(NULL)
, PARTY.LAST_UPDATE_DATE
, PARTY.STATUS OBJ_STATUS
, PARTY.STATUS SUB_STATUS
FROM HZ_PARTIES PARTY
WHERE NOT EXISTS (SELECT 'X'
FROM HZ_RELATIONSHIPS PARTY_REL
WHERE PARTY.PARTY_ID = PARTY_REL.PARTY_ID)

Columns