View: CS_SR_CONTACT_POINTS_V

Product:	CS - Service
Description:	This view corresponds to the contact point type information such as PHONE/FAX/EMAIL & numbers of the individual contacts.
Implementation/DBA Data:	APPS.CS_SR_CONTACT_POINTS_V

View Text

SELECT DISTINCT C.PRIMARY_FLAG
, B.MEANING
, C.CONTACT_TYPE
, E.MEANING SUB_TITLE
, D.PERSON_FIRST_NAME SUB_FIRST_NAME
, D.PERSON_LAST_NAME SUB_LAST_NAME
, E.MEANING || ' ' || D.PERSON_FIRST_NAME || ' ' || D.PERSON_LAST_NAME CONTACT
, C.CONTACT_POINT_TYPE
, DECODE(C.CONTACT_POINT_TYPE
, 'EMAIL'
, A.EMAIL_ADDRESS
, 'WEB'
, A.EMAIL_ADDRESS
, DECODE(A.PHONE_COUNTRY_CODE
, ''
, ''
, A.PHONE_COUNTRY_CODE||'-')|| DECODE(A.PHONE_AREA_CODE
, ''
, ''
, A.PHONE_AREA_CODE|| '-' ) || A.PHONE_NUMBER) CONTACT_PHONE
, A.PHONE_EXTENSION EXTENSION
, D.ADDRESS1||DECODE(D.ADDRESS2
, NULL
, NULL
, ';'||D.ADDRESS2|| DECODE(D.ADDRESS3
, NULL
, NULL
, ';'||D.ADDRESS3|| DECODE(D.ADDRESS4
, NULL
, NULL
, ';'||D.ADDRESS4))) ADDRESS
, C.SR_CONTACT_POINT_ID
, C.INCIDENT_ID
, C.PARTY_ID
, C.CONTACT_POINT_ID
, R.OBJECT_ID
FROM HZ_CONTACT_POINTS A
, AR_LOOKUPS B
, CS_HZ_SR_CONTACT_POINTS C
, HZ_PARTIES D
, HZ_RELATIONSHIPS R
, AR_LOOKUPS E
WHERE C.PARTY_ID = R.PARTY_ID(+)
AND ((D.PARTY_ID = C.PARTY_ID
AND C.CONTACT_TYPE = 'PERSON') OR (D.PARTY_ID = R.SUBJECT_ID
AND C.CONTACT_TYPE = 'PARTY_RELATIONSHIP'
AND D.PARTY_TYPE = 'PERSON' ))
AND C.CONTACT_POINT_TYPE = B.LOOKUP_CODE(+)
AND B.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND A.CONTACT_POINT_ID (+) = C.CONTACT_POINT_ID
AND D.PERSON_PRE_NAME_ADJUNCT = E.LOOKUP_CODE(+)
AND E.LOOKUP_TYPE(+)= 'CONTACT_TITLE'
AND C.PARTY_ROLE_CODE = 'CONTACT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(C.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(C.END_DATE_ACTIVE
, SYSDATE) ) UNION SELECT DISTINCT C.PRIMARY_FLAG
, B.MEANING
, C.CONTACT_TYPE
, NULL
, NULL
, NULL
, NULL
, C.CONTACT_POINT_TYPE
, NULL
, NULL
, NULL
, C.SR_CONTACT_POINT_ID
, C.INCIDENT_ID
, C.PARTY_ID
, C.CONTACT_POINT_ID
, TO_NUMBER(NULL)
FROM AR_LOOKUPS B
, CS_HZ_SR_CONTACT_POINTS C
WHERE C.CONTACT_POINT_TYPE = B.LOOKUP_CODE(+)
AND B.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND C.CONTACT_TYPE = 'EMPLOYEE'
AND C.PARTY_ROLE_CODE = 'CONTACT'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(C.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(C.END_DATE_ACTIVE
, SYSDATE) )

Columns