View: CSF_PO_CONTACT_POINTS_V

Product:	CSF - Field Service
Description:
Implementation/DBA Data:	APPS.CSF_PO_CONTACT_POINTS_V

View Text

SELECT SRCP.SR_CONTACT_POINT_ID SR_CONTACT_POINT_ID
, SRCP.PARTY_ID CONTACT_PARTY_ID
, SRCP.INCIDENT_ID INCIDENT_ID
, SRCP.CONTACT_POINT_TYPE CONTACT_POINT_TYPE
, SRCP.CONTACT_POINT_ID CONTACT_POINT_ID
, SRCP.PRIMARY_FLAG PRIMARY_FLAG
, SRCP.CONTACT_TYPE CONTACT_TYPE
, LKP.MEANING LOOKUP_MEANING
, R.RELATIONSHIP_ID RELATIONSHIP_ID
, R.SUBJECT_ID SUBJECT_PARTY_ID
, P.PARTY_NAME || ' ' || PERSON_PRE_NAME_ADJUNCT CONTACT_NAME
, DECODE(HZCP.CONTACT_POINT_TYPE
, 'PHONE'
, DECODE(HZCP.PHONE_NUMBER
, ''
, ''
, AR1.MEANING || ':'|| DECODE(HZCP.PHONE_AREA_CODE
, ''
, ''
, '('|| HZCP.PHONE_AREA_CODE || ')' ) || HZCP.PHONE_NUMBER)
, 'EMAIL'
, DECODE(HZCP.EMAIL_ADDRESS
, ''
, ''
, AR.MEANING ||':'|| HZCP.EMAIL_ADDRESS)
, 'TLX'
, DECODE(HZCP.TELEX_NUMBER
, ''
, ''
, AR.MEANING ||':'|| HZCP.TELEX_NUMBER)
, 'WEB'
, DECODE(HZCP.URL
, ''
, ''
, AR.MEANING ||':'|| HZCP.URL)
, 'EDI'
, DECODE(HZCP.EDI_ID_NUMBER
, ''
, ''
, AR.MEANING ||':'|| HZCP.EDI_ID_NUMBER) ) CONTACT_COMM_PREF
, 1 AS MODIFIABLE_TYPE
, 1 AS MODIFIABLE_NAME
, SRCP.CONTACT_TYPE DISPLAY_CONTACT_TYPE
, DECODE(SRCP.PRIMARY_FLAG
, 'Y'
, SRCP.PRIMARY_FLAG
, 'N') DISPLAY_PRIMARY_FLAG
FROM CS_INCIDENTS_ALL_VL SR
, CS_HZ_SR_CONTACT_POINTS SRCP
, HZ_RELATIONSHIPS R
, HZ_CONTACT_POINTS HZCP
, HZ_PARTIES P
, CS_LOOKUPS LKP
, AR_LOOKUPS AR
, AR_LOOKUPS AR1
WHERE SR.INCIDENT_ID = SRCP.INCIDENT_ID
AND SRCP.CONTACT_TYPE = 'PARTY_RELATIONSHIP'
AND SRCP.PARTY_ID = R.PARTY_ID
AND R.SUBJECT_ID = P.PARTY_ID
AND SR.CUSTOMER_ID = R.OBJECT_ID
AND R.SUBJECT_TYPE = 'PERSON'
AND R.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND SRCP.CONTACT_POINT_ID = HZCP.CONTACT_POINT_ID (+)
AND SRCP.CONTACT_TYPE = LKP.LOOKUP_CODE(+)
AND LKP.LOOKUP_TYPE(+) = 'CS_SR_CONTACT_TYPE'
AND HZCP.CONTACT_POINT_TYPE = AR.LOOKUP_CODE(+)
AND AR.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND HZCP.PHONE_LINE_TYPE = AR1.LOOKUP_CODE(+)
AND AR1.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE' UNION SELECT SRCP.SR_CONTACT_POINT_ID SR_CONTACT_POINT_ID
, SRCP.PARTY_ID CONTACT_PARTY_ID
, SRCP.INCIDENT_ID INCIDENT_ID
, SRCP.CONTACT_POINT_TYPE CONTACT_POINT_TYPE
, SRCP.CONTACT_POINT_ID CONTACT_POINT_ID
, SRCP.PRIMARY_FLAG PRIMARY_FLAG
, SRCP.CONTACT_TYPE CONTACT_TYPE
, LKP.MEANING LOOKUP_MEANING
, TO_NUMBER(NULL) RELATIONSHIP_ID
, TO_NUMBER(NULL) SUBJECT_PARTY_ID
, P.PARTY_NAME || ' ' || PERSON_PRE_NAME_ADJUNCT CONTACT_NAME
, DECODE(HZCP.CONTACT_POINT_TYPE
, 'PHONE'
, DECODE(HZCP.PHONE_NUMBER
, ''
, ''
, AR1.MEANING || ':'|| DECODE(HZCP.PHONE_AREA_CODE
, ''
, ''
, '('|| HZCP.PHONE_AREA_CODE || ')' ) || HZCP.PHONE_NUMBER)
, 'EMAIL'
, DECODE(HZCP.EMAIL_ADDRESS
, ''
, ''
, AR.MEANING ||':'|| HZCP.EMAIL_ADDRESS)
, 'TLX'
, DECODE(HZCP.TELEX_NUMBER
, ''
, ''
, AR.MEANING ||':'|| HZCP.TELEX_NUMBER)
, 'WEB'
, DECODE(HZCP.URL
, ''
, ''
, AR.MEANING ||':'|| HZCP.URL)
, 'EDI'
, DECODE(HZCP.EDI_ID_NUMBER
, ''
, ''
, AR.MEANING ||':'|| HZCP.EDI_ID_NUMBER) ) CONTACT_COMM_PREF
, 1 AS MODIFIABLE_TYPE
, 1 AS MODIFIABLE_NAME
, DECODE(SRCP.CONTACT_TYPE
, 'PERSON'
, 'PARTY_RELATIONSHIP'
, SRCP.CONTACT_TYPE) DISPLAY_CONTACT_TYPE
, DECODE(SRCP.PRIMARY_FLAG
, 'Y'
, SRCP.PRIMARY_FLAG
, 'N') DISPLAY_PRIMARY_FLAG
FROM CS_INCIDENTS_ALL_VL SR
, CS_HZ_SR_CONTACT_POINTS SRCP
, HZ_CONTACT_POINTS HZCP
, HZ_PARTIES P
, CS_LOOKUPS LKP
, AR_LOOKUPS AR
, AR_LOOKUPS AR1
WHERE SR.INCIDENT_ID = SRCP.INCIDENT_ID
AND SRCP.CONTACT_TYPE = 'PERSON'
AND SRCP.PARTY_ID = P.PARTY_ID
AND SRCP.CONTACT_POINT_ID = HZCP.CONTACT_POINT_ID (+)
AND SRCP.CONTACT_TYPE = LKP.LOOKUP_CODE(+)
AND LKP.LOOKUP_TYPE(+) = 'CS_SR_CONTACT_TYPE'
AND HZCP.CONTACT_POINT_TYPE = AR.LOOKUP_CODE(+)
AND AR.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND HZCP.PHONE_LINE_TYPE = AR1.LOOKUP_CODE(+)
AND AR1.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE' UNION SELECT SRCP.SR_CONTACT_POINT_ID SR_CONTACT_POINT_ID
, SRCP.PARTY_ID CONTACT_PARTY_ID
, SRCP.INCIDENT_ID INCIDENT_ID
, SRCP.CONTACT_POINT_TYPE CONTACT_POINT_TYPE
, SRCP.CONTACT_POINT_ID CONTACT_POINT_ID
, SRCP.PRIMARY_FLAG PRIMARY_FLAG
, SRCP.CONTACT_TYPE CONTACT_TYPE
, LKP.MEANING LOOKUP_MEANING
, TO_NUMBER(NULL) RELATIONSHIP_ID
, TO_NUMBER(NULL) SUBJECT_PARTY_ID
, P.FIRST_NAME || ' ' || P.LAST_NAME || ' ' || P.TITLE CONTACT_NAME
, DECODE(SRCP.CONTACT_POINT_TYPE
, 'EMAIL'
, DECODE(P.EMAIL_ADDRESS
, ''
, ''
, AR.MEANING||':'|| P.EMAIL_ADDRESS)
, 'PHONE'
, DECODE(PHO.PHONE_NUMBER
, ''
, ''
, HRL.MEANING||':'||PHO.PHONE_NUMBER) ) CONTACT_COMM_PREF
, 1 AS MODIFIABLE_TYPE
, 1 AS MODIFIABLE_NAME
, SRCP.CONTACT_TYPE DISPLAY_CONTACT_TYPE
, DECODE(SRCP.PRIMARY_FLAG
, 'Y'
, SRCP.PRIMARY_FLAG
, 'N') DISPLAY_PRIMARY_FLAG
FROM CS_INCIDENTS_ALL_VL SR
, CS_HZ_SR_CONTACT_POINTS SRCP
, PER_PHONES PHO
, PER_ALL_PEOPLE_F P
, PER_ALL_ASSIGNMENTS_F A
, CS_LOOKUPS LKP
, HR_LOOKUPS HRL
, AR_LOOKUPS AR
WHERE SR.INCIDENT_ID = SRCP.INCIDENT_ID
AND SRCP.CONTACT_TYPE = 'EMPLOYEE'
AND SRCP.PARTY_ID = P.PERSON_ID
AND A.PERSON_ID = P.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'E'
AND P.EFFECTIVE_START_DATE = (SELECT MAX(Q.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F Q
WHERE Q.PERSON_ID = P.PERSON_ID)
AND A.EFFECTIVE_START_DATE = (SELECT MAX(B.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F B
WHERE B.PERSON_ID = A.PERSON_ID
AND B.ASSIGNMENT_ID = A.ASSIGNMENT_ID )
AND SRCP.CONTACT_POINT_ID = PHO.PHONE_ID(+)
AND SRCP.CONTACT_TYPE = LKP.LOOKUP_CODE(+)
AND LKP.LOOKUP_TYPE(+) = 'CS_SR_CONTACT_TYPE'
AND PHO.PHONE_TYPE = HRL.LOOKUP_CODE(+)
AND HRL.LOOKUP_TYPE(+) = 'PHONE_TYPE'
AND AR.LOOKUP_CODE(+) = 'EMAIL'
AND AR.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'

Columns