Product: | CS - Service |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | Not implemented in this database |
SELECT CI.ROWID ROW_ID
, CI.INTERACTION_ID
, CI.CREATED_BY
, CI.CREATION_DATE
, CI.LAST_UPDATED_BY
, CI.LAST_UPDATE_DATE
, CI.LAST_UPDATE_LOGIN
, CI.CUSTOMER_ID
, CUST.CUSTOMER_NAME
, CUST.CUSTOMER_NUMBER
, CI.CONTACT_ID
, DECODE(CI.CONTACT_ID
, NULL
, CI.CONTACT_LASTNAME
, CONT.LAST_NAME) CONTACT_LASTNAME
, DECODE(CI.CONTACT_ID
, NULL
, CI.CONTACT_FIRSTNAME
, CONT.FIRST_NAME) CONTACT_FIRSTNAME
, CI.PHONE_AREA_CODE
, CI.PHONE_NUMBER
, CI.PHONE_EXTENSION
, CI.FAX_AREA_CODE
, CI.FAX_NUMBER
, CI.EMAIL_ADDRESS
, CI.INTERACTION_TYPE_CODE
, LK1.MEANING INTERACTION_TYPE
, CI.INTERACTION_CATEGORY_CODE
, LK2.MEANING INTERACTION_CATEGORY
, CI.INTERACTION_METHOD_CODE
, LK3.MEANING INTERACTION_METHOD
, CI.INTERACTION_DATE
, CI.INTERACTION_DOCUMENT_CODE
, LK4.MEANING INTERACTION_DOCUMENT
, CI.SOURCE_DOCUMENT_ID
, CI.SOURCE_DOCUMENT_NAME
, CI.REFERENCE_FORM
, CI.SOURCE_DOCUMENT_STATUS
, CI.EMPLOYEE_ID
, PER.FULL_NAME EMPLOYEE_NAME
, NVL(CI.PUBLIC_FLAG
, 'N') PUBLIC_FLAG
, CI.FOLLOW_UP_ACTION
, COM.COMMENTS NOTES
, CI.PARENT_INTERACTION_ID
, CI.ORG_ID
, OP.NAME OPERATING_UNIT
, CI.ATTRIBUTE1
, CI.ATTRIBUTE2
, CI.ATTRIBUTE3
, CI.ATTRIBUTE4
, CI.ATTRIBUTE5
, CI.ATTRIBUTE6
, CI.ATTRIBUTE7
, CI.ATTRIBUTE8
, CI.ATTRIBUTE9
, CI.ATTRIBUTE10
, CI.ATTRIBUTE11
, CI.ATTRIBUTE12
, CI.ATTRIBUTE13
, CI.ATTRIBUTE14
, CI.ATTRIBUTE15
, CI.ATTRIBUTE_CATEGORY
, CI.CALL_ID
FROM RA_CUSTOMERS CUST
, RA_CONTACTS CONT
, CS_LOOKUPS LK1
, CS_LOOKUPS LK2
, CS_LOOKUPS LK3
, CS_LOOKUPS LK4
, PER_PEOPLE_X PER
, CS_COMMENTS COM
, HR_OPERATING_UNITS OP
, CS_INTERACTIONS CI
WHERE CI.CUSTOMER_ID = CUST.CUSTOMER_ID
AND CI.CONTACT_ID = CONT.CONTACT_ID (+)
AND CI.INTERACTION_TYPE_CODE = LK1.LOOKUP_CODE
AND LK1.LOOKUP_TYPE = 'INTERACTION_TYPE'
AND CI.INTERACTION_CATEGORY_CODE = LK2.LOOKUP_CODE
AND LK2.LOOKUP_TYPE = 'INTERACTION_CATEGORY'
AND CI.INTERACTION_METHOD_CODE = LK3.LOOKUP_CODE
AND LK3.LOOKUP_TYPE = 'INTERACTION_METHOD'
AND CI.INTERACTION_DOCUMENT_CODE = LK4.LOOKUP_CODE (+)
AND LK4.LOOKUP_TYPE (+) = 'INTERACTION_DOCUMENT'
AND CI.EMPLOYEE_ID = PER.PERSON_ID (+)
AND CI.NOTE_ID = COM.COMMENT_ID(+)
AND CI.ORG_ID = OP.ORGANIZATION_ID (+)