This view obtains the contact information such as Job Title, Address etc. for each of the contacts pertaining to a service request depending on the Caller Type i,e. ORGANIZATION / PERSON.
Implementation/DBA Data:
Not implemented in this database
View Text
SELECT DISTINCT A.PARTY_ID , A.CONTACT_POINT_ID , A.INCIDENT_ID , B.PERSON_TITLE SUB_TITLE , B.PERSON_TITLE || ' ' || B.PERSON_FIRST_NAME || ' ' || B.PERSON_LAST_NAME || B.PERSON_NAME_SUFFIX CONTACT , B.PERSON_FIRST_NAME SUB_FIRST_NAME , B.PERSON_LAST_NAME SUB_LAST_NAME , O.DECISION_MAKER_FLAG SUB_DECISION_MAKER_FLAG , O.JOB_TITLE SUB_JOB_TITLE , B.ADDRESS1 || DECODE(B.ADDRESS2 , NULL , NULL , ';') || B.ADDRESS2 || DECODE(B.ADDRESS3 , NULL , NULL , ';') || B.ADDRESS3 || DECODE(B.ADDRESS4 , NULL , NULL , ';') || B.ADDRESS4 || B.CITY || B.STATE ADDRESS1 , B.PROVINCE || B.POSTAL_CODE || B.COUNTRY ADDRESS2 , A.PRIMARY_FLAG , A.CONTACT_TYPE FROM CS_HZ_SR_CONTACT_POINTS A , JTF_PARTIES_ALL_V B , HZ_ORG_CONTACTS O , HZ_PARTY_RELATIONSHIPS R WHERE A.PARTY_ID = B.PARTY_ID AND B.PARTY_TYPE='PERSON' AND B.PARTY_ID = R.SUBJECT_ID AND O.PARTY_RELATIONSHIP_ID (+) = R.PARTY_RELATIONSHIP_ID AND (R.START_DATE IS NULL OR (R.START_DATE IS NOT NULL AND SYSDATE BETWEEN R.START_DATE AND NVL(R.END_DATE , SYSDATE))) UNION SELECT DISTINCT A.PARTY_ID , A.CONTACT_POINT_ID , A.INCIDENT_ID , B.PERSON_TITLE SUB_TITLE , B.PERSON_TITLE || ' ' || B.PERSON_FIRST_NAME || ' ' || B.PERSON_LAST_NAME || B.PERSON_NAME_SUFFIX CONTACT , B.PERSON_FIRST_NAME SUB_FIRST_NAME , B.PERSON_LAST_NAME SUB_LAST_NAME , NULL SUB_DECISION_MAKER_FLAG , NULL SUB_JOB_TITLE , B.ADDRESS1 || DECODE(B.ADDRESS2 , NULL , NULL , ';') || B.ADDRESS2 || DECODE(B.ADDRESS3 , NULL , NULL , ';') || B.ADDRESS3 || DECODE(B.ADDRESS4 , NULL , NULL , ';') || B.ADDRESS4 || B.CITY || B.STATE ADDRESS1 , B.PROVINCE || B.POSTAL_CODE || B.COUNTRY ADDRESS2 , A.PRIMARY_FLAG , A.CONTACT_TYPE FROM CS_HZ_SR_CONTACT_POINTS A , JTF_PARTIES_ALL_V B WHERE A.PARTY_ID = B.PARTY_ID AND B.PARTY_TYPE='PERSON' ORDER BY 3 , 1 , 2
[Home] [Help]