View: CSI_PARTY_CONTACT_DETAILS_V

Product:	CSI - Install Base
Description:	Party Contact details.
Implementation/DBA Data:	APPS.CSI_PARTY_CONTACT_DETAILS_V

View Text

SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_IP_ID CONTACT_IP_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, 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.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, DECODE( HCP_WP.PHONE_COUNTRY_CODE
, NULL
, NULL
, HCP_WP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_WP.PHONE_AREA_CODE
, NULL
, NULL
, '(' || HCP_WP.PHONE_AREA_CODE || ')' ) || HCP_WP.PHONE_NUMBER || DECODE( HCP_WP.PHONE_EXTENSION
, NULL
, NULL
, 'X' || HCP_WP.PHONE_EXTENSION) WORK_PHONE_NUMBER
, DECODE( HCP_HP.PHONE_AREA_CODE
, NULL
, NULL
, HCP_HP.PHONE_COUNTRY_CODE || '-' ) || DECODE( HCP_HP.PHONE_AREA_CODE
, NULL
, NULL
, '(' || HCP_HP.PHONE_AREA_CODE || ')' ) || HCP_HP.PHONE_NUMBER || DECODE( HCP_HP.PHONE_EXTENSION
, NULL
, NULL
, 'X' || HCP_HP.PHONE_EXTENSION ) HOME_PHONE_NUMBER
, HCP_EM.EMAIL_ADDRESS EMAIL_ADDRESS
, HP.ADDRESS1 ADDRESS1
, HP.ADDRESS2 ADDRESS2
, HP.ADDRESS3 ADDRESS3
, HP.ADDRESS4 ADDRESS4
, HP.CITY CITY
, HP.POSTAL_CODE POSTAL_CODE
, HP.STATE STATE
, HP.PROVINCE PROVINCE
, HP.COUNTY COUNTY
, HP.COUNTRY COUNTRY
FROM CSI_I_PARTIES CIP
, HZ_PARTIES HP
, CSI_IPA_RELATION_TYPES CIR
, HZ_CONTACT_POINTS HCP_WP
, HZ_CONTACT_POINTS HCP_HP
, HZ_CONTACT_POINTS HCP_EM
, CSI_LOOKUPS CL
, HZ_RELATIONSHIPS HR
, CSI_I_PARTIES CIPO
WHERE CIP.PARTY_ID = HR.SUBJECT_ID
AND CIP.CONTACT_IP_ID = CIPO.INSTANCE_PARTY_ID
AND CIPO.PARTY_ID = HR.OBJECT_ID
AND HR.SUBJECT_ID = HP.PARTY_ID
AND CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND HCP_WP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_WP.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_WP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP_WP.PRIMARY_FLAG (+) = 'Y'
AND HCP_HP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_HP.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_HP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCP_HP.PRIMARY_FLAG (+) = 'Y'
AND HCP_HP.CONTACT_POINT_PURPOSE (+) = 'PERSONAL'
AND HCP_EM.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCP_EM.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCP_EM.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND HCP_EM.PRIMARY_FLAG (+) = 'Y'
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND CL.LOOKUP_TYPE(+) LIKE 'CSI%' UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_IP_ID CONTACT_IP_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, 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.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPADTED_BY
, CIP.LAST_UPDATE_DATE LAST_UPADTE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, PAP.FULL_NAME PARTY_NAME
, PAP.EMPLOYEE_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, PP.PHONE_NUMBER WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, PAP.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_I_PARTIES CIP
, PER_PHONES PP
, PER_ALL_PEOPLE_F PAP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= SYSDATE
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND CIP.PARTY_SOURCE_TABLE = 'EMPLOYEE'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PAP.PERSON_ID = PP.PARENT_ID(+)
AND PP.PHONE_TYPE(+) = 'W1'
AND SYSDATE BETWEEN PP.DATE_FROM(+) AND NVL(PP.DATE_TO(+)
, SYSDATE)
AND CL.LOOKUP_TYPE (+) LIKE 'CSI%' UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_IP_ID CONTACT_IP_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, 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.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, PVC.PREFIX || ' ' || PVC.FIRST_NAME || ' ' || PVC.MIDDLE_NAME || ' '|| PVC.LAST_NAME PARTY_NAME
, NULL PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, PVC.AREA_CODE || ' ' || PVC.PHONE WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, PVC.MAIL_STOP EMAIL_ADDRESS
, PVS.ADDRESS_LINE1 ADDRESS1
, PVS.ADDRESS_LINE2 ADDRESS2
, PVS.ADDRESS_LINE3 ADDRESS3
, NULL ADDRESS4
, PVS.CITY CITY
, PVS.ZIP POSTAL_CODE
, PVS.STATE STATE
, PVS.PROVINCE PROVINCE
, NULL COUNTY
, PVS.COUNTRY COUNTRY
FROM CSI_I_PARTIES CIP
, PO_VENDOR_CONTACTS PVC
, PO_VENDOR_SITES_ALL PVS
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_SOURCE_TABLE = 'PO_VENDORS'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE (+)
AND CIP.PARTY_ID = PVC.VENDOR_CONTACT_ID
AND PVS.VENDOR_SITE_ID = PVC.VENDOR_SITE_ID
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) LIKE 'CSI%' UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_IP_ID CONTACT_IP_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, 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.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, JT.TEAM_NAME PARTY_NAME
, JT.TEAM_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, NULL WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, JT.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_I_PARTIES CIP
, JTF_RS_TEAMS_VL JT
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_ID = JT.TEAM_ID
AND CIP.PARTY_SOURCE_TABLE = 'TEAM'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) LIKE 'CSI%' UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, CL.MEANING PARTY_SOURCE_MEANING
, CIP.PARTY_ID PARTY_ID
, CIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE
, CIP.CONTACT_FLAG CONTACT_FLAG
, CIP.CONTACT_IP_ID CONTACT_IP_ID
, CIP.PREFERRED_FLAG PREFERRED_FLAG
, CIP.PRIMARY_FLAG PRIMARY_FLAG
, CIP.ACTIVE_START_DATE ACTIVE_START_DATE
, CIP.ACTIVE_END_DATE ACTIVE_END_DATE
, CIP.CONTEXT CONTEXT
, 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.CREATED_BY CREATED_BY
, CIP.CREATION_DATE CREATION_DATE
, CIP.LAST_UPDATED_BY LAST_UPDATED_BY
, CIP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, JG.GROUP_NAME PARTY_NAME
, JG.GROUP_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, NULL WORK_PHONE_NUMBER
, NULL HOME_PHONE_NUMBER
, JG.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL POSTAL_CODE
, NULL STATE
, NULL PROVINCE
, NULL COUNTY
, NULL COUNTRY
FROM CSI_I_PARTIES CIP
, JTF_RS_GROUPS_VL JG
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.CONTACT_FLAG = 'Y'
AND CIP.PARTY_ID = JG.GROUP_ID
AND CIP.PARTY_SOURCE_TABLE = 'GROUP'
AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE(+)
AND CL.LOOKUP_CODE (+) = CIP.PARTY_SOURCE_TABLE
AND CL.LOOKUP_TYPE (+) LIKE 'CSI%'

Columns