VIEW: APPS.CSF_PO_CONTACT_POINTS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'