View: CSI_CONTACTS_V

Product:	CSI - Install Base
Description:	Backward Compatible : Customer Product Contacts (View for table CS_CONTACTS).
Implementation/DBA Data:	APPS.CSI_CONTACTS_V

View Text

SELECT CIP.ROWID ROW_ID
, CIP.INSTANCE_PARTY_ID CS_CONTACT_ID
, CIP.INSTANCE_ID SOURCE_OBJECT_ID
, 'CP' SOURCE_OBJECT_CODE
, CIP.RELATIONSHIP_TYPE_CODE CONTACT_TYPE
, CIRT.NAME MEANING
, CIP.PARTY_ID CONTACT_ID
, CIP.PARTY_SOURCE_TABLE CONTACT_CATEGORY
, CSL.MEANING CONTACT_CATEGORY_MEANING
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, NULL SVC_PROVIDER_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.CREATION_DATE CREATION_DATE
, CIP.CREATED_BY CREATED_BY
, CIP.ATTRIBUTE1 ATTRIBUTE1
, CIP.ATTRIBUTE2 ATTRIBUTE2
, CIP.ATTRIBUTE3 ATTRIBUTE3
, CIP.ATTRIBUTE4 ATTRIBUTE4
, CIP.ATTRIBUTE5 ATTRIBUTE5
, CIP.ATTRIBUTE6 ATTRIBUTE6
, CIP.ATTRIBUTE7 ATTRIBUTE7
, CIP.ATTRIBUTE8 ATTRIBUTE8
, CIP.ATTRIBUTE9 ATTRIBUTE9
, CIP.ATTRIBUTE10 ATTRIBUTE10
, CIP.ATTRIBUTE11 ATTRIBUTE11
, CIP.ATTRIBUTE12 ATTRIBUTE12
, CIP.ATTRIBUTE13 ATTRIBUTE13
, CIP.ATTRIBUTE14 ATTRIBUTE14
, CIP.ATTRIBUTE15 ATTRIBUTE15
, CIP.CONTEXT CONTEXT
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, RS.RESOURCE_ID RESOURCE_ID
, RS.ADDRESS ADDRESS
, RS.RESOURCE_NAME RESOURCE_NAME
, RS.RESOURCE_NUMBER RESOURCE_NUMBER
, NVL(RS.CONTACT_CATEGORY
, CIP.PARTY_SOURCE_TABLE) RESOURCE_CATEGORY
, CSI_PARTIES_PKG.GET_PARTY_PHONE(CIP.PARTY_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_WORK_PHONE_TYPE')) WORK_PHONE_NUMBER
, CSI_PARTIES_PKG.GET_PARTY_PHONE(CIP.PARTY_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_CELL_PHONE_TYPE')) CELL_PHONE_NUMBER
, CSI_PARTIES_PKG.GET_PARTY_PHONE(CIP.PARTY_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_PAGER_PHONE_TYPE')) PAGER_PHONE_NUMBER
, CSI_PARTIES_PKG.GET_PARTY_EMAIL(CIP.PARTY_ID) EMAIL_ADDRESS
, CSI_PARTIES_PKG.GET_PARTY_FAX(CIP.PARTY_ID) FAX_NUMBER
, CSI_PARTIES_PKG.GET_PARTY_PHONE(CIP.PARTY_ID
, FND_PROFILE.VALUE_WNPS('CS_AR_HOME_PHONE_TYPE')) HOME_PHONE_NUMBER
FROM CSI_I_PARTIES CIP
, ( SELECT E.EMPLOYEE_ID RESOURCE_ID
, E.FULL_NAME RESOURCE_NAME
, E.EMPLOYEE_NUM RESOURCE_NUMBER
, A.ADDRESS_LINE1||' '||A.ADDRESS_LINE2||' '||A.ADDRESS_LINE3||' '||A.TOWN_OR_CITY||' '|| A.REGION_2||' '||A.POSTAL_CODE||' '||A.COUNTRY ADDRESS
, 'EMPLOYEE' CONTACT_CATEGORY FROM HR_EMPLOYEES E
, PER_ADDRESSES A WHERE E.EMPLOYEE_ID = A.PERSON_ID(+) UNION ALL SELECT PARTY_ID RESOURCE_ID
, PARTY_NAME RESOURCE_NAME
, PARTY_NUMBER RESOURCE_NUMBER
, ADDRESS1||' '||ADDRESS2||' '||ADDRESS3||' '||ADDRESS4||' '||CITY||' '||STATE||' '||POSTAL_CODE||' '||PROVINCE||' '||COUNTRY ADDRESS
, 'PARTY' CONTACT_CATEGORY FROM HZ_PARTIES UNION ALL SELECT TEAM_ID RESOURCE_ID
, TEAM_NAME RESOURCE_NAME
, TEAM_NUMBER RESOURCE_NUMBER
, NULL ADDRESS
, 'TEAM' CONTACT_CATEGORY FROM JTF_RS_TEAMS_VL UNION ALL SELECT GROUP_ID RESOURCE_ID
, GROUP_NAME RESOURCE_NAME
, GROUP_NUMBER RESOURCE_NUMBER
, NULL ADDRESS
, 'GROUP' CONTACT_CATEGORY FROM JTF_RS_GROUPS_VL ) RS
, CSI_IPA_RELATION_TYPES CIRT
, CSI_LOOKUPS CSL
WHERE CIP.CONTACT_FLAG = 'Y' AND RS.RESOURCE_ID (+) = CIP.PARTY_ID AND RS.CONTACT_CATEGORY (+) = DECODE( CIP.PARTY_SOURCE_TABLE
, 'HZ_PARTIES'
, 'PARTY'
, CIP.PARTY_SOURCE_TABLE) AND CIP.RELATIONSHIP_TYPE_CODE = CIRT.IPA_RELATION_TYPE_CODE(+) AND CSL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE

Columns