eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CSI_IPH_DETAILS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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