3.1 List Object Version History of Informatica Objects
Displays all versions of an object by the date the object is saved in the repository. This is a stand-alone report.
SELECT USER_NAME,
COMMENTS,
OBJECT_NAME,
SAVED_FROM,
OBJECT_TYPE,
LAST_SAVED,
SUBJECT_AREA,
VERSION_NUMBER,
REPOSITORY_NAME
FROM (SELECT DISTINCT
VERSION_REPOSIT_INFO.REPOSITORY_NAME,
VERSION_SUBJECT.SUBJECT_AREA,
VERSION_PROPS.OBJECT_NAME,
VERSION_USERS.USER_NAME,
VERSION_PROPS.SAVED_FROM,
TO_DATE (VERSION_PROPS.LAST_SAVED, 'MM/DD/YYYY HH24:Mi:ss')
AS LAST_SAVED,
VERSION_PROPS.VERSION_NUMBER,
VERSION_PROPS.COMMENTS AS COMMENTS,
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <= 9
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 1
THEN
'Source Definition'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 2
THEN
'Target Definition'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 3
THEN
'Source Qualifier'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 4
THEN
'Update Strategy'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 5
THEN
'EXPRESSION'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 6
THEN
'Stored Procedure'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 7
THEN
'Sequence'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 8
THEN
'External Procedure'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 9
THEN
'Aggregator'
ELSE
NULL
END
END
END
END
END
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <= 17
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 10
THEN
'Filter'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 11
THEN
'Lookup Procedure'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 12
THEN
'Joiner'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 13
THEN
'Procedure'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 14
THEN
'Normalizer'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 15
THEN
'Router'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 16
THEN
'Merger'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 17
THEN
'Pivot'
ELSE
NULL
END
END
END
END
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <= 45
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 20
THEN
'Shortcut'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 21
THEN
'Mapping'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 26
THEN
'RANK'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 30
THEN
'Cube'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 31
THEN
'Dimension'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 44
THEN
'Mapplet'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 45
THEN
'Application Source Qualifier'
ELSE
NULL
END
END
END
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <=
59
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 46
THEN
'Input Transformation'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 50
THEN
'Advanced External Procedure'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 55
THEN
'XML Source Qualifier'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 56
THEN
'MQ Source Qualifier'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 58
THEN
'Command'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 59
THEN
'Decision'
ELSE
NULL
END
END
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <=
68
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 62
THEN
'Start'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 65
THEN
'Email'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 66
THEN
'Timer'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 67
THEN
'Assignment'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 68
THEN
'Session'
ELSE
NULL
END
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <=
72
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 69
THEN
'Scheduler'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 70
THEN
'Worklet'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 71
THEN
'Workflow'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 72
THEN
'SessionConfig'
ELSE
NULL
END
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE <=
92
THEN
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 80
THEN
'Sorter'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 84
THEN
'App Multi-Group Source Qualifier'
ELSE
CASE VERSION_PROPS.OBJECT_TYPE
WHEN 92
THEN
'Transaction Control'
ELSE
NULL
END
END
END
ELSE
CASE
WHEN VERSION_PROPS.OBJECT_TYPE =
97
THEN
'Custom Transformation'
ELSE
NULL
END
END
END
END
END
END
END
END
AS OBJECT_TYPE
FROM REP_VERSION_PROPS VERSION_PROPS,
REP_SUBJECT VERSION_SUBJECT,
REP_REPOSIT_INFO VERSION_REPOSIT_INFO,
REP_USERS VERSION_USERS
WHERE ( VERSION_PROPS.SUBJECT_ID = VERSION_SUBJECT.SUBJECT_ID
AND VERSION_PROPS.OBJECT_ID <>
VERSION_REPOSIT_INFO.REPOSITORY_ID
AND VERSION_PROPS.USER_ID = VERSION_USERS.USER_ID)) query
WHERE OBJECT_TYPE IN ('
AND OBJECT_NAME IN ('')
AND LAST_SAVED BETWEEN DATE ''
AND DATE ''
ORDER BY REPOSITORY_NAME, SUBJECT_AREA