Product: | CSI - Install Base |
---|---|
Description: | Instance Party Details view. |
Implementation/DBA Data: | APPS.CSI_INST_PARTY_DETAILS_V |
SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, 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.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_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
, PV.VENDOR_NAME PARTY_NAME
, PV.SEGMENT1 PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_I_PARTIES CIP
, PO_VENDORS PV
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.PARTY_ID = PV.VENDOR_ID AND CIP.PARTY_SOURCE_TABLE = 'PO_VENDORS' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, 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.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_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
, PAP.FULL_NAME PARTY_NAME
, PAP.EMPLOYEE_NUMBER PARTY_NUMBER
, CIR.NAME RELATIONSHIP_NAME
, CIR.DESCRIPTION RELATIONSHIP_DESCR
, CL.MEANING PARTY_TYPE
FROM CSI_I_PARTIES CIP
, PER_ALL_PEOPLE_F PAP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE 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 CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, 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.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_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
, CL.MEANING PARTY_TYPE
FROM CSI_I_PARTIES CIP
, HZ_PARTIES HP
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.PARTY_ID = HP.PARTY_ID AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, 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.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_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
, CL.MEANING PARTY_TYPE
FROM CSI_I_PARTIES CIP
, JTF_RS_TEAMS_VL JT
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.PARTY_ID = JT.TEAM_ID AND CIP.PARTY_SOURCE_TABLE = 'TEAM' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE UNION ALL SELECT CIP.INSTANCE_PARTY_ID INSTANCE_PARTY_ID
, CIP.INSTANCE_ID INSTANCE_ID
, CIP.PARTY_SOURCE_TABLE PARTY_SOURCE_TABLE
, 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.PRIMARY_FLAG PRIMARY_FLAG
, CIP.PREFERRED_FLAG PREFERRED_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
, CL.MEANING PARTY_TYPE
FROM CSI_I_PARTIES CIP
, JTF_RS_GROUPS_VL JG
, CSI_IPA_RELATION_TYPES CIR
, CSI_LOOKUPS CL
WHERE CIP.PARTY_ID = JG.GROUP_ID AND CIP.PARTY_SOURCE_TABLE = 'GROUP' AND CIP.RELATIONSHIP_TYPE_CODE = CIR.IPA_RELATION_TYPE_CODE AND CIR.PARTY_USE_FLAG = 'Y' AND CL.LOOKUP_TYPE = 'CSI_PARTY_SOURCE_TABLE' AND CL.LOOKUP_CODE = CIP.PARTY_SOURCE_TABLE