VIEW: APPS.CSI_INST_PARTY_DETAILS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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