VIEW: APPS.IEX_CASE_CONTACTS_V

Source

View Text - Preformatted

SELECT CASE_CONT.ROWID, CASE_CONT.CAS_CONTACT_ID, CASE_CONT.CONTACT_PARTY_ID, CASE_CONT.CAS_ID, CASE_CONT.OBJECT_VERSION_NUMBER, CASE_CONT.ADDRESS_ID, CASE_CONT.PHONE_ID, PARTY_CONT.PERSON_FIRST_NAME, PARTY_CONT.PERSON_LAST_NAME, PHONE.PHONE_COUNTRY_CODE, PHONE.PHONE_AREA_CODE, PHONE.PHONE_NUMBER, PHONE.PHONE_EXTENSION, PHONE.PHONE_COUNTRY_CODE||DECODE(PHONE.PHONE_COUNTRY_CODE,NULL,'',' ')||PHONE.PHONE_AREA_CODE||DECODE(PHONE.PHONE_AREA_CODE,NULL,'',' ')||PHONE.PHONE_NUMBER||DECODE(PHONE.PHONE_NUMBER,NULL,'',' ')||PHONE.PHONE_EXTENSION, PHONE.PHONE_LINE_TYPE, PHONE.RAW_PHONE_NUMBER, REL.RELATIONSHIP_ID, REL.RELATIONSHIP_CODE, REL.SUBJECT_ID, REL.OBJECT_ID, AR_LKUP1.MEANING PARTY_RELATIONSHIP, AR_LKUP2.MEANING PERSON_TITLE, PHONE1.EMAIL_ADDRESS, CASE_CONT.PRIMARY_FLAG, CASE_CONT.LAST_UPDATE_DATE, CASE_CONT.LAST_UPDATED_BY, CASE_CONT.CREATION_DATE, CASE_CONT.CREATED_BY, CASE_CONT.LAST_UPDATE_LOGIN, CASE_CONT.REQUEST_ID, CASE_CONT.PROGRAM_APPLICATION_ID, CASE_CONT.PROGRAM_ID, CASE_CONT.PROGRAM_UPDATE_DATE, CASE_CONT.ATTRIBUTE_CATEGORY, CASE_CONT.ATTRIBUTE1, CASE_CONT.ATTRIBUTE2, CASE_CONT.ATTRIBUTE3, CASE_CONT.ATTRIBUTE4, CASE_CONT.ATTRIBUTE5, CASE_CONT.ATTRIBUTE6, CASE_CONT.ATTRIBUTE7, CASE_CONT.ATTRIBUTE8, CASE_CONT.ATTRIBUTE9, CASE_CONT.ATTRIBUTE10, CASE_CONT.ATTRIBUTE11, CASE_CONT.ATTRIBUTE12, CASE_CONT.ATTRIBUTE13, CASE_CONT.ATTRIBUTE14, CASE_CONT.ATTRIBUTE15 FROM IEX_CASE_CONTACTS CASE_CONT, HZ_PARTIES PARTY_CONT, HZ_RELATIONSHIPS REL, HZ_CONTACT_POINTS PHONE, HZ_CONTACT_POINTS PHONE1, AR_LOOKUPS AR_LKUP1, AR_LOOKUPS AR_LKUP2 WHERE CASE_CONT.CONTACT_PARTY_ID = REL.PARTY_ID AND REL.DIRECTIONAL_FLAG ='F' AND PARTY_CONT.PARTY_ID = REL.SUBJECT_ID AND CASE_CONT.PHONE_ID = PHONE.CONTACT_POINT_ID(+) AND CASE_CONT.CONTACT_PARTY_ID = PHONE1.OWNER_TABLE_ID(+) AND PHONE1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND PHONE1.CONTACT_POINT_TYPE(+) = 'EMAIL' AND PHONE1.PRIMARY_FLAG(+) = 'Y' AND AR_LKUP1.LOOKUP_CODE(+) = REL.RELATIONSHIP_CODE AND AR_LKUP1.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE' AND AR_LKUP2.LOOKUP_CODE(+) = PARTY_CONT.PERSON_PRE_NAME_ADJUNCT AND AR_LKUP2.LOOKUP_TYPE(+) = 'CONTACT_TITLE'

View Text - HTML Formatted

SELECT CASE_CONT.ROWID
, CASE_CONT.CAS_CONTACT_ID
, CASE_CONT.CONTACT_PARTY_ID
, CASE_CONT.CAS_ID
, CASE_CONT.OBJECT_VERSION_NUMBER
, CASE_CONT.ADDRESS_ID
, CASE_CONT.PHONE_ID
, PARTY_CONT.PERSON_FIRST_NAME
, PARTY_CONT.PERSON_LAST_NAME
, PHONE.PHONE_COUNTRY_CODE
, PHONE.PHONE_AREA_CODE
, PHONE.PHONE_NUMBER
, PHONE.PHONE_EXTENSION
, PHONE.PHONE_COUNTRY_CODE||DECODE(PHONE.PHONE_COUNTRY_CODE
, NULL
, ''
, ' ')||PHONE.PHONE_AREA_CODE||DECODE(PHONE.PHONE_AREA_CODE
, NULL
, ''
, ' ')||PHONE.PHONE_NUMBER||DECODE(PHONE.PHONE_NUMBER
, NULL
, ''
, ' ')||PHONE.PHONE_EXTENSION
, PHONE.PHONE_LINE_TYPE
, PHONE.RAW_PHONE_NUMBER
, REL.RELATIONSHIP_ID
, REL.RELATIONSHIP_CODE
, REL.SUBJECT_ID
, REL.OBJECT_ID
, AR_LKUP1.MEANING PARTY_RELATIONSHIP
, AR_LKUP2.MEANING PERSON_TITLE
, PHONE1.EMAIL_ADDRESS
, CASE_CONT.PRIMARY_FLAG
, CASE_CONT.LAST_UPDATE_DATE
, CASE_CONT.LAST_UPDATED_BY
, CASE_CONT.CREATION_DATE
, CASE_CONT.CREATED_BY
, CASE_CONT.LAST_UPDATE_LOGIN
, CASE_CONT.REQUEST_ID
, CASE_CONT.PROGRAM_APPLICATION_ID
, CASE_CONT.PROGRAM_ID
, CASE_CONT.PROGRAM_UPDATE_DATE
, CASE_CONT.ATTRIBUTE_CATEGORY
, CASE_CONT.ATTRIBUTE1
, CASE_CONT.ATTRIBUTE2
, CASE_CONT.ATTRIBUTE3
, CASE_CONT.ATTRIBUTE4
, CASE_CONT.ATTRIBUTE5
, CASE_CONT.ATTRIBUTE6
, CASE_CONT.ATTRIBUTE7
, CASE_CONT.ATTRIBUTE8
, CASE_CONT.ATTRIBUTE9
, CASE_CONT.ATTRIBUTE10
, CASE_CONT.ATTRIBUTE11
, CASE_CONT.ATTRIBUTE12
, CASE_CONT.ATTRIBUTE13
, CASE_CONT.ATTRIBUTE14
, CASE_CONT.ATTRIBUTE15
FROM IEX_CASE_CONTACTS CASE_CONT
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS PHONE1
, AR_LOOKUPS AR_LKUP1
, AR_LOOKUPS AR_LKUP2
WHERE CASE_CONT.CONTACT_PARTY_ID = REL.PARTY_ID
AND REL.DIRECTIONAL_FLAG ='F'
AND PARTY_CONT.PARTY_ID = REL.SUBJECT_ID
AND CASE_CONT.PHONE_ID = PHONE.CONTACT_POINT_ID(+)
AND CASE_CONT.CONTACT_PARTY_ID = PHONE1.OWNER_TABLE_ID(+)
AND PHONE1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE1.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND PHONE1.PRIMARY_FLAG(+) = 'Y'
AND AR_LKUP1.LOOKUP_CODE(+) = REL.RELATIONSHIP_CODE
AND AR_LKUP1.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'
AND AR_LKUP2.LOOKUP_CODE(+) = PARTY_CONT.PERSON_PRE_NAME_ADJUNCT
AND AR_LKUP2.LOOKUP_TYPE(+) = 'CONTACT_TITLE'