View: CSI_IPH_DETAILS_V

Product:	CSI - Install Base
Description:	Instance Party History details view.
Implementation/DBA Data:	APPS.CSI_IPH_DETAILS_V

View Text

SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.INSTANCE_PARTY_ID
, CIPH.TRANSACTION_ID
, CIP.INSTANCE_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, CIPH.NEW_PARTY_ID
, CIPH.OLD_RELATIONSHIP_TYPE_CODE
, CIPH.NEW_RELATIONSHIP_TYPE_CODE
, CIPH.OLD_CONTACT_FLAG
, CIPH.NEW_CONTACT_FLAG
, CIPH.OLD_CONTACT_IP_ID
, CIPH.NEW_CONTACT_IP_ID
, CIPH.OLD_PREFERRED_FLAG
, CIPH.NEW_PREFERRED_FLAG
, CIPH.OLD_PRIMARY_FLAG
, CIPH.NEW_PRIMARY_FLAG
, CIPH.OLD_ACTIVE_START_DATE
, CIPH.NEW_ACTIVE_START_DATE
, CIPH.OLD_ACTIVE_END_DATE
, CIPH.NEW_ACTIVE_END_DATE
, CIPH.OLD_CONTEXT
, CIPH.NEW_CONTEXT
, CIPH.OLD_ATTRIBUTE1
, CIPH.NEW_ATTRIBUTE1
, CIPH.OLD_ATTRIBUTE2
, CIPH.NEW_ATTRIBUTE2
, CIPH.OLD_ATTRIBUTE3
, CIPH.NEW_ATTRIBUTE3
, CIPH.OLD_ATTRIBUTE4
, CIPH.NEW_ATTRIBUTE4
, CIPH.OLD_ATTRIBUTE5
, CIPH.NEW_ATTRIBUTE5
, CIPH.OLD_ATTRIBUTE6
, CIPH.NEW_ATTRIBUTE6
, CIPH.OLD_ATTRIBUTE7
, CIPH.NEW_ATTRIBUTE7
, CIPH.OLD_ATTRIBUTE8
, CIPH.NEW_ATTRIBUTE8
, CIPH.OLD_ATTRIBUTE9
, CIPH.NEW_ATTRIBUTE9
, CIPH.OLD_ATTRIBUTE10
, CIPH.NEW_ATTRIBUTE10
, CIPH.OLD_ATTRIBUTE11
, CIPH.NEW_ATTRIBUTE11
, CIPH.OLD_ATTRIBUTE12
, CIPH.NEW_ATTRIBUTE12
, CIPH.OLD_ATTRIBUTE13
, CIPH.NEW_ATTRIBUTE13
, CIPH.OLD_ATTRIBUTE14
, CIPH.NEW_ATTRIBUTE14
, CIPH.OLD_ATTRIBUTE15
, CIPH.NEW_ATTRIBUTE15
, CIPH.FULL_DUMP_FLAG
, CIPH.CREATED_BY
, CIPH.CREATION_DATE
, CIPH.LAST_UPDATED_BY
, CIPH.LAST_UPDATE_DATE
, CIPH.LAST_UPDATE_LOGIN
, CIPH.OBJECT_VERSION_NUMBER
, OCIR.NAME OLD_RELATIONSHIP_NAME
, OCIR.DESCRIPTION OLD_RELATIONSHIP_DESCR
, OCL.MEANING OLD_PARTY_TYPE
, NCIR.NAME NEW_RELATIONSHIP_NAME
, NCIR.DESCRIPTION NEW_RELATIONSHIP_DESCR
, NCL.MEANING NEW_PARTY_TYPE
, OPTY.OLD_PARTY_NAME
, OPTY.OLD_PARTY_NUMBER
, NPTY.NEW_PARTY_NAME
, NPTY.NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, CSI_I_PARTIES CIP
, CSI_IPA_RELATION_TYPES OCIR
, CSI_LOOKUPS OCL
, CSI_IPA_RELATION_TYPES NCIR
, CSI_LOOKUPS NCL
, (SELECT INSTANCE_PARTY_HISTORY_ID
, OLD_PARTY_SOURCE_TABLE
, OLD_PARTY_ID
, OLD_PARTY_NAME
, OLD_PARTY_NUMBER
FROM ( SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, PV.VENDOR_NAME OLD_PARTY_NAME
, TO_CHAR(NULL) OLD_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, PO_VENDORS PV
WHERE CIPH.OLD_PARTY_ID = PV.VENDOR_ID
AND CIPH.OLD_PARTY_SOURCE_TABLE = 'PO_VENDORS' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, PAP.FULL_NAME OLD_PARTY_NAME
, PAP.EMPLOYEE_NUMBER OLD_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, PER_ALL_PEOPLE_F PAP
WHERE CIPH.OLD_PARTY_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= SYSDATE
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND CIPH.OLD_PARTY_SOURCE_TABLE = 'EMPLOYEE' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, HZP.PARTY_NAME OLD_PARTY_NAME
, HZP.PARTY_NUMBER OLD_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, HZ_PARTIES HZP
WHERE CIPH.OLD_PARTY_ID = HZP.PARTY_ID
AND CIPH.OLD_PARTY_SOURCE_TABLE = 'HZ_PARTIES' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, JRT.TEAM_NAME OLD_PARTY_NAME
, JRT.TEAM_NUMBER OLD_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, JTF_RS_TEAMS_VL JRT
WHERE CIPH.OLD_PARTY_ID = JRT.TEAM_ID
AND CIPH.OLD_PARTY_SOURCE_TABLE = 'TEAM' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.OLD_PARTY_SOURCE_TABLE
, CIPH.OLD_PARTY_ID
, JRG.GROUP_NAME OLD_PARTY_NAME
, JRG.GROUP_NUMBER OLD_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, JTF_RS_GROUPS_VL JRG
WHERE CIPH.OLD_PARTY_ID = JRG.GROUP_ID
AND CIPH.OLD_PARTY_SOURCE_TABLE = 'GROUP' ) ) OPTY
, (SELECT INSTANCE_PARTY_HISTORY_ID
, NEW_PARTY_SOURCE_TABLE
, NEW_PARTY_ID
, NEW_PARTY_NAME
, NEW_PARTY_NUMBER
FROM ( SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_ID
, PV.VENDOR_NAME NEW_PARTY_NAME
, TO_CHAR(NULL) NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, PO_VENDORS PV
WHERE CIPH.NEW_PARTY_ID = PV.VENDOR_ID
AND CIPH.NEW_PARTY_SOURCE_TABLE = 'PO_VENDORS' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_ID
, PAP.FULL_NAME NEW_PARTY_NAME
, PAP.EMPLOYEE_NUMBER NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, PER_ALL_PEOPLE_F PAP
WHERE CIPH.NEW_PARTY_ID = PAP.PERSON_ID
AND PAP.EFFECTIVE_START_DATE <= SYSDATE
AND PAP.EFFECTIVE_END_DATE >= SYSDATE
AND CIPH.NEW_PARTY_SOURCE_TABLE = 'EMPLOYEE' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_ID
, HZP.PARTY_NAME NEW_PARTY_NAME
, HZP.PARTY_NUMBER NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, HZ_PARTIES HZP
WHERE CIPH.NEW_PARTY_ID = HZP.PARTY_ID
AND CIPH.NEW_PARTY_SOURCE_TABLE = 'HZ_PARTIES' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_ID
, JRT.TEAM_NAME NEW_PARTY_NAME
, JRT.TEAM_NUMBER NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, JTF_RS_TEAMS_VL JRT
WHERE CIPH.NEW_PARTY_ID = JRT.TEAM_ID
AND CIPH.NEW_PARTY_SOURCE_TABLE = 'TEAM' UNION ALL SELECT CIPH.INSTANCE_PARTY_HISTORY_ID
, CIPH.NEW_PARTY_SOURCE_TABLE
, CIPH.NEW_PARTY_ID
, JRG.GROUP_NAME NEW_PARTY_NAME
, JRG.GROUP_NUMBER NEW_PARTY_NUMBER
FROM CSI_I_PARTIES_H CIPH
, JTF_RS_GROUPS_VL JRG
WHERE CIPH.NEW_PARTY_ID = JRG.GROUP_ID
AND CIPH.NEW_PARTY_SOURCE_TABLE = 'GROUP' ) ) NPTY
WHERE CIP.INSTANCE_PARTY_ID = CIPH.INSTANCE_PARTY_ID
AND CIPH.OLD_RELATIONSHIP_TYPE_CODE = OCIR.IPA_RELATION_TYPE_CODE (+)
AND OCL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE'
AND OCL.LOOKUP_CODE (+) = CIPH.OLD_PARTY_SOURCE_TABLE
AND CIPH.NEW_RELATIONSHIP_TYPE_CODE = NCIR.IPA_RELATION_TYPE_CODE (+)
AND NCL.LOOKUP_TYPE (+) = 'CSI_PARTY_SOURCE_TABLE'
AND NCL.LOOKUP_CODE (+) = CIPH.NEW_PARTY_SOURCE_TABLE
AND OPTY.INSTANCE_PARTY_HISTORY_ID (+) = CIPH.INSTANCE_PARTY_HISTORY_ID
AND NPTY.INSTANCE_PARTY_HISTORY_ID (+) = CIPH.INSTANCE_PARTY_HISTORY_ID

Columns