eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: CS_CONTACTS_V

Product:	CS - Service
Description:	Information regarding the Resources and their type of contacts in Installedbase
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT /*+ FIRST_ROWS */ CC.ROWID ROW_ID
, CC.CS_CONTACT_ID
, CC.SOURCE_OBJECT_ID
, CC.SOURCE_OBJECT_CODE
, CC.CONTACT_TYPE
, CSL.MEANING
, CC.CONTACT_ID
, CC.CONTACT_CATEGORY
, CC.PRIMARY_FLAG
, CC.PREFERRED_FLAG
, CC.SVC_PROVIDER_FLAG
, CC.START_DATE_ACTIVE
, CC.END_DATE_ACTIVE
, CC.LAST_UPDATE_DATE
, CC.LAST_UPDATED_BY
, CC.LAST_UPDATE_LOGIN
, CC.CREATION_DATE
, CC.CREATED_BY
, CC.ATTRIBUTE1
, CC.ATTRIBUTE2
, CC.ATTRIBUTE3
, CC.ATTRIBUTE4
, CC.ATTRIBUTE5
, CC.ATTRIBUTE6
, CC.ATTRIBUTE7
, CC.ATTRIBUTE8
, CC.ATTRIBUTE9
, CC.ATTRIBUTE10
, CC.ATTRIBUTE11
, CC.ATTRIBUTE12
, CC.ATTRIBUTE13
, CC.ATTRIBUTE14
, CC.ATTRIBUTE15
, CC.CONTEXT
, CC.OBJECT_VERSION_NUMBER
, CE.RESOURCE_ID RESOURCE_ID
, CE.RESOURCE_NAME RESOURCE_NAME
, CE.RESOURCE_NUMBER RESOURCE_NUMBER
, CE.RESOURCE_ADDRESS ADDRESS
, CE.CONTACT_CATEGORY RESOURCE_CATEGORY
, ( SELECT PP.PHONE_NUMBER
FROM HR_EMPLOYEES HE
, PER_PHONES PP
WHERE PP.PARENT_ID = HE.EMPLOYEE_ID
AND HE.EMPLOYEE_ID = CC.CONTACT_ID
AND PP.PHONE_TYPE = 'W1'
AND ROWNUM < 2) WORK_PHONE_NUMBER
, ( SELECT PP.PHONE_NUMBER
FROM HR_EMPLOYEES HE
, PER_PHONES PP
WHERE PP.PARENT_ID = HE.EMPLOYEE_ID
AND HE.EMPLOYEE_ID = CC.CONTACT_ID
AND PP.PHONE_TYPE = 'M'
AND ROWNUM < 2) CELL_PHONE_NUMBER
, ( SELECT PP.PHONE_NUMBER
FROM HR_EMPLOYEES HE
, PER_PHONES PP
WHERE PP.PARENT_ID = HE.EMPLOYEE_ID
AND HE.EMPLOYEE_ID = CC.CONTACT_ID
AND PP.PHONE_TYPE = 'P'
AND ROWNUM < 2) PAGER_PHONE_NUMBER
, ( SELECT EMAIL_ADDRESS
FROM HR_EMPLOYEES
WHERE EMPLOYEE_ID = CC.CONTACT_ID
AND ROWNUM < 2) EMAIL_ADDRESS
, ( SELECT PP.PHONE_NUMBER
FROM HR_EMPLOYEES HE
, PER_PHONES PP
WHERE PP.PARENT_ID = HE.EMPLOYEE_ID
AND HE.EMPLOYEE_ID = CC.CONTACT_ID
AND PP.PHONE_TYPE = 'WF'
AND ROWNUM < 2) FAX_NUMBER
, ( SELECT PP.PHONE_NUMBER
FROM HR_EMPLOYEES HE
, PER_PHONES PP
WHERE PP.PARENT_ID = HE.EMPLOYEE_ID
AND HE.EMPLOYEE_ID = CC.CONTACT_ID
AND PP.PHONE_TYPE = 'H1'
AND ROWNUM < 2) HOME_PHONE_NUMBER
FROM CS_CONTACTS CC
, CS_IB_EMPLOYEES_V CE
, CS_LOOKUPS CSL
WHERE CC.CONTACT_ID = CE.RESOURCE_ID
AND CC.CONTACT_ID > 0
AND CE.RESOURCE_ID > 0
AND CE.CONTACT_CATEGORY = CC.CONTACT_CATEGORY
AND CE.CONTACT_CATEGORY = 'EMPLOYEE'
AND CC.CONTACT_TYPE = CSL.LOOKUP_CODE(+)
AND CSL.LOOKUP_TYPE(+) = 'CP_CONTACT_TYPE' UNION ALL SELECT /*+ FIRST_ROWS */ CC.ROWID ROW_ID
, CC.CS_CONTACT_ID
, CC.SOURCE_OBJECT_ID
, CC.SOURCE_OBJECT_CODE
, CC.CONTACT_TYPE
, CSL.MEANING
, CC.CONTACT_ID
, CC.CONTACT_CATEGORY
, CC.PRIMARY_FLAG
, CC.PREFERRED_FLAG
, CC.SVC_PROVIDER_FLAG
, CC.START_DATE_ACTIVE
, CC.END_DATE_ACTIVE
, CC.LAST_UPDATE_DATE
, CC.LAST_UPDATED_BY
, CC.LAST_UPDATE_LOGIN
, CC.CREATION_DATE
, CC.CREATED_BY
, CC.ATTRIBUTE1
, CC.ATTRIBUTE2
, CC.ATTRIBUTE3
, CC.ATTRIBUTE4
, CC.ATTRIBUTE5
, CC.ATTRIBUTE6
, CC.ATTRIBUTE7
, CC.ATTRIBUTE8
, CC.ATTRIBUTE9
, CC.ATTRIBUTE10
, CC.ATTRIBUTE11
, CC.ATTRIBUTE12
, CC.ATTRIBUTE13
, CC.ATTRIBUTE14
, CC.ATTRIBUTE15
, CC.CONTEXT
, CC.OBJECT_VERSION_NUMBER
, CE.PARTY_ID RESOURCE_ID
, CE.PARTY_NAME RESOURCE_NAME
, CE.PARTY_NUMBER RESOURCE_NUMBER
, CE.ADDRESS1||' '||CE.ADDRESS2||' '||CE.ADDRESS3||' '||CE.ADDRESS4||' '||CE.CITY||' '|| CE.STATE||' '||CE.POSTAL_CODE||' '||CE.PROVINCE||' '||CE.COUNTY ADDRESS
, 'PARTY' RESOURCE_CATEGORY
, CS_PARTIES_PKG.GET_PARTY_PHONE(CC.CONTACT_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_WORK_PHONE_TYPE') ) WORK_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(CC.CONTACT_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_CELL_PHONE_TYPE') ) CELL_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(CC.CONTACT_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_PAGER_PHONE_TYPE' )) PAGER_PHONE_NUMBER
, CS_PARTIES_PKG.GET_PARTY_EMAIL(CC.CONTACT_ID) EMAIL_ADDRESS
, CS_PARTIES_PKG.GET_PARTY_FAX(CC.CONTACT_ID) FAX_NUMBER
, CS_PARTIES_PKG.GET_PARTY_PHONE(CC.CONTACT_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_HOME_PHONE_TYPE') ) HOME_PHONE_NUMBER
FROM CS_CONTACTS CC
, HZ_PARTIES CE
, CS_LOOKUPS CSL
WHERE CC.CONTACT_ID = CE.PARTY_ID
AND CC.CONTACT_ID > 0
AND CC.CONTACT_CATEGORY = 'PARTY'
AND CC.CONTACT_TYPE = CSL.LOOKUP_CODE(+)
AND CSL.LOOKUP_TYPE(+) = 'CP_CONTACT_TYPE' UNION ALL SELECT /*+ FIRST_ROWS */ CC.ROWID ROW_ID
, CC.CS_CONTACT_ID
, CC.SOURCE_OBJECT_ID
, CC.SOURCE_OBJECT_CODE
, CC.CONTACT_TYPE
, CSL.MEANING
, CC.CONTACT_ID
, CC.CONTACT_CATEGORY
, CC.PRIMARY_FLAG
, CC.PREFERRED_FLAG
, CC.SVC_PROVIDER_FLAG
, CC.START_DATE_ACTIVE
, CC.END_DATE_ACTIVE
, CC.LAST_UPDATE_DATE
, CC.LAST_UPDATED_BY
, CC.LAST_UPDATE_LOGIN
, CC.CREATION_DATE
, CC.CREATED_BY
, CC.ATTRIBUTE1
, CC.ATTRIBUTE2
, CC.ATTRIBUTE3
, CC.ATTRIBUTE4
, CC.ATTRIBUTE5
, CC.ATTRIBUTE6
, CC.ATTRIBUTE7
, CC.ATTRIBUTE8
, CC.ATTRIBUTE9
, CC.ATTRIBUTE10
, CC.ATTRIBUTE11
, CC.ATTRIBUTE12
, CC.ATTRIBUTE13
, CC.ATTRIBUTE14
, CC.ATTRIBUTE15
, CC.CONTEXT
, CC.OBJECT_VERSION_NUMBER
, CE.TEAM_ID RESOURCE_ID
, CE.TEAM_NAME RESOURCE_NAME
, CE.TEAM_NUMBER RESOURCE_NUMBER
, NULL ADDRESS
, 'TEAM' RESOURCE_CATEGORY
, NULL WORK_PHONE_NUMBER
, NULL CELL_PHONE_NUMBER
, NULL PAGER_PHONE_NUMBER
, CE.EMAIL_ADDRESS
, NULL FAX_NUMBER
, NULL HOME_PHONE_NUMBER
FROM CS_CONTACTS CC
, JTF_RS_TEAMS_VL CE
, CS_LOOKUPS CSL
WHERE CC.CONTACT_ID = CE.TEAM_ID
AND CC.CONTACT_ID > 0
AND CE.TEAM_ID > 0
AND CC.CONTACT_CATEGORY = 'TEAM'
AND CC.CONTACT_TYPE = CSL.LOOKUP_CODE(+)
AND CSL.LOOKUP_TYPE(+) = 'CP_CONTACT_TYPE' UNION ALL SELECT /*+ FIRST_ROWS */ CC.ROWID ROW_ID
, CC.CS_CONTACT_ID
, CC.SOURCE_OBJECT_ID
, CC.SOURCE_OBJECT_CODE
, CC.CONTACT_TYPE
, CSL.MEANING
, CC.CONTACT_ID
, CC.CONTACT_CATEGORY
, CC.PRIMARY_FLAG
, CC.PREFERRED_FLAG
, CC.SVC_PROVIDER_FLAG
, CC.START_DATE_ACTIVE
, CC.END_DATE_ACTIVE
, CC.LAST_UPDATE_DATE
, CC.LAST_UPDATED_BY
, CC.LAST_UPDATE_LOGIN
, CC.CREATION_DATE
, CC.CREATED_BY
, CC.ATTRIBUTE1
, CC.ATTRIBUTE2
, CC.ATTRIBUTE3
, CC.ATTRIBUTE4
, CC.ATTRIBUTE5
, CC.ATTRIBUTE6
, CC.ATTRIBUTE7
, CC.ATTRIBUTE8
, CC.ATTRIBUTE9
, CC.ATTRIBUTE10
, CC.ATTRIBUTE11
, CC.ATTRIBUTE12
, CC.ATTRIBUTE13
, CC.ATTRIBUTE14
, CC.ATTRIBUTE15
, CC.CONTEXT
, CC.OBJECT_VERSION_NUMBER
, CE.GROUP_ID RESOURCE_ID
, CE.GROUP_NAME RESOURCE_NAME
, CE.GROUP_NUMBER RESOURCE_NUMBER
, NULL ADDRESS
, 'GROUP' RESOURCE_CATEGORY
, NULL WORK_PHONE_NUMBER
, NULL CELL_PHONE_NUMBER
, NULL PAGER_PHONE_NUMBER
, CE.EMAIL_ADDRESS
, NULL FAX_NUMBER
, NULL HOME_PHONE_NUMBER
FROM CS_CONTACTS CC
, JTF_RS_GROUPS_VL CE
, CS_LOOKUPS CSL
WHERE CC.CONTACT_ID = CE.GROUP_ID
AND CC.CONTACT_ID > 0
AND CC.CONTACT_CATEGORY = 'GROUP'
AND CC.CONTACT_TYPE = CSL.LOOKUP_CODE(+)
AND CSL.LOOKUP_TYPE(+) = 'CP_CONTACT_TYPE'

Columns

Name
ROW_ID
CS_CONTACT_ID
SOURCE_OBJECT_ID
SOURCE_OBJECT_CODE
CONTACT_TYPE
MEANING
CONTACT_ID
CONTACT_CATEGORY
PRIMARY_FLAG
PREFERRED_FLAG
SVC_PROVIDER_FLAG
START_DATE_ACTIVE
END_DATE_ACTIVE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ATTRIBUTE6
ATTRIBUTE7
ATTRIBUTE8
ATTRIBUTE9
ATTRIBUTE10
ATTRIBUTE11
ATTRIBUTE12
ATTRIBUTE13
ATTRIBUTE14
ATTRIBUTE15
CONTEXT
OBJECT_VERSION_NUMBER
RESOURCE_ID
RESOURCE_NAME
RESOURCE_NUMBER
ADDRESS
RESOURCE_CATEGORY
WORK_PHONE_NUMBER
CELL_PHONE_NUMBER
PAGER_PHONE_NUMBER
EMAIL_ADDRESS
FAX_NUMBER
HOME_PHONE_NUMBER