VIEW: APPS.CS_SR_CONTACT_POINTS_V

Source

View Text - Preformatted

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, c.end_date_active, (SELECT meaning FROM ar_lookups WHERE lookup_type = 'RESPONSIBILITY' AND lookup_code = f.JOB_TITLE_CODE ) JOB_TITLE_CODE, f.JOB_TITLE JOB_TITLE, (SELECT meaning FROM ar_lookups WHERE lookup_type = 'DEPARTMENT_TYPE' AND lookup_code = f.DEPARTMENT_CODE ) department_code, f.DEPARTMENT DEPARTMENT FROM HZ_CONTACT_POINTS A, AR_LOOKUPS B, CS_HZ_SR_CONTACT_POINTS C, HZ_PARTIES D, HZ_RELATIONSHIPS R, AR_LOOKUPS E , HZ_ORG_CONTACTS F 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 f.party_relationship_id(+) = r.relationship_id 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) ,c. end_date_active, null, null, null, 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'

View Text - HTML Formatted

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
, C.END_DATE_ACTIVE
, (SELECT MEANING
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'RESPONSIBILITY'
AND LOOKUP_CODE = F.JOB_TITLE_CODE ) JOB_TITLE_CODE
, F.JOB_TITLE JOB_TITLE
, (SELECT MEANING
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'DEPARTMENT_TYPE'
AND LOOKUP_CODE = F.DEPARTMENT_CODE ) DEPARTMENT_CODE
, F.DEPARTMENT DEPARTMENT
FROM HZ_CONTACT_POINTS A
, AR_LOOKUPS B
, CS_HZ_SR_CONTACT_POINTS C
, HZ_PARTIES D
, HZ_RELATIONSHIPS R
, AR_LOOKUPS E
, HZ_ORG_CONTACTS F
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 F.PARTY_RELATIONSHIP_ID(+) = R.RELATIONSHIP_ID 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)
, C. END_DATE_ACTIVE
, NULL
, NULL
, NULL
, 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'