Configuration Management reports
This page will show you all the Available reports for Configuration Management reports, once you select a report name, it will open the report query page. copy and paste it to your PC and run it from, toad or any other DB tools you have.
Deployment
Displays, by group name, type, and creator, the details of the deployed objects by a particular deployment group. This report is the first node in the analytic workflow associated with the Deployment Group History primary report
select “DEP_GROUP_NAME”,
“REPOSITORY_NAME”,
“CREATED_BY”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“OBJECT_TYPE_NAME”,
case when (“GROUP_TYPE” = 0) then (‘Normal’) else (case when (“GROUP_TYPE” = 1) then (‘Dynamic’) else (”) end) end as “Calc_GROUP_TYPE”,
“TARG_SUBJECT_AREA”,
“OBJECT_NAME”,
“SRC_VERSION_NUMBER”,
“TARGET_REP_NAME”,
“SRC_SUBJECT_AREA”,
“DEPLOY_TIME”,
“TARG_VERSION_NUMBER”
from (SELECT DISTINCT DEPLOYMENT_GROUP.DEP_GROUP_NAME, DEPLOYMENT_GROUP.GROUP_TYPE as GROUP_TYPE, DEPLOYMENT_GROUP.CREATED_BY, DEPLOYMENT_GROUP_DETAIL.DEPLOY_TIME, DEPLOYMENT_REPOSIT_INFO.REPOSITORY_NAME, DEPLOYMENT_GROUP_DETAIL.TARGET_REP_NAME, DEPLOYMENT_GROUP_DETAIL.OBJECT_NAME, DEPLOYMENT_GROUP_DETAIL.OBJECT_TYPE_NAME, DEPLOYMENT_GROUP_DETAIL.SRC_VERSION_NUMBER, DEPLOYMENT_GROUP_DETAIL.TARG_VERSION_NUMBER, DEPLOYMENT_GROUP_DETAIL.SRC_SUBJECT_AREA, DEPLOYMENT_GROUP_DETAIL.TARG_SUBJECT_AREA, DEPLOYMENT_GROUP_DETAIL.IS_SHORTCUT as IS_SHORTCUT, DEPLOYMENT_GROUP.DESCRIPTION as DESCRIPTION, DEPLOYMENT_GROUP_DETAIL.USER_NAME FROM REP_REPOSIT_INFO DEPLOYMENT_REPOSIT_INFO, REP_DEPLOY_GROUP DEPLOYMENT_GROUP LEFT OUTER JOIN REP_DEPLOY_GROUP_DETAIL DEPLOYMENT_GROUP_DETAIL ON DEPLOYMENT_GROUP.DEP_GROUP_ID = DEPLOYMENT_GROUP_DETAIL.DEP_GROUP_ID WHERE (DEPLOYMENT_GROUP.DEP_GROUP_ID <> DEPLOYMENT_REPOSIT_INFO.REPOSITORY_ID)) “query”
order by “DEP_GROUP_NAME”, “Calc_GROUP_TYPE”, “CREATED_BY”
Displays, by group, deployment groups and the dates they were deployed. It also displays the source and target repository names of the deployment group for all deployment dates. This is a primary report in an analytic workflow.
select “DEP_GROUP_NAME”,
“DESCRIPTION”,
“REPOSITORY_NAME”,
“CREATED_BY”,
case when (“GROUP_TYPE” = 0) then (‘Normal’) else (case when (“GROUP_TYPE” = 1) then (‘Dynamic’) else (”) end) end as “Calc_GROUP_TYPE”,
“TARGET_REP_NAME”,
“USER_NAME”,
“DEPLOY_TIME”
from (SELECT DISTINCT DEPLOYMENT_GROUP.DEP_GROUP_NAME, DEPLOYMENT_GROUP.GROUP_TYPE as GROUP_TYPE, DEPLOYMENT_GROUP.CREATED_BY, DEPLOYMENT_GROUP_DETAIL.DEPLOY_TIME, DEPLOYMENT_REPOSIT_INFO.REPOSITORY_NAME, DEPLOYMENT_GROUP_DETAIL.TARGET_REP_NAME, DEPLOYMENT_GROUP_DETAIL.OBJECT_NAME, DEPLOYMENT_GROUP_DETAIL.OBJECT_TYPE_NAME, DEPLOYMENT_GROUP_DETAIL.SRC_VERSION_NUMBER, DEPLOYMENT_GROUP_DETAIL.TARG_VERSION_NUMBER, DEPLOYMENT_GROUP_DETAIL.SRC_SUBJECT_AREA, DEPLOYMENT_GROUP_DETAIL.TARG_SUBJECT_AREA, DEPLOYMENT_GROUP_DETAIL.IS_SHORTCUT as IS_SHORTCUT, DEPLOYMENT_GROUP.DESCRIPTION as DESCRIPTION, DEPLOYMENT_GROUP_DETAIL.USER_NAME FROM REP_REPOSIT_INFO DEPLOYMENT_REPOSIT_INFO, REP_DEPLOY_GROUP DEPLOYMENT_GROUP LEFT OUTER JOIN REP_DEPLOY_GROUP_DETAIL DEPLOYMENT_GROUP_DETAIL ON DEPLOYMENT_GROUP.DEP_GROUP_ID = DEPLOYMENT_GROUP_DETAIL.DEP_GROUP_ID WHERE (DEPLOYMENT_GROUP.DEP_GROUP_ID <> DEPLOYMENT_REPOSIT_INFO.REPOSITORY_ID)) “query”
order by “DEP_GROUP_NAME”
Object Version
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 (‘<Object_Type>’) and “OBJECT_NAME” in (‘<Object_Name>’) and “LAST_SAVED” between DATE ‘<Last_Saved_Between>’ and DATE ‘<And_Date>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
I am actually thankful to the owner of this web page who has
shared this enormous paragraph at here.