PowerCenter Reports
This page will show you all the Available reports for PowerCenter 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.
Configuration Management reports
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”
Operations Reports
Session Execution
Displays sessions associated with a particular connection by repository by folder. This report is the first node in the analytic workflow associated with the Connection Usage primary report.
select “SUBJECT_AREA”,
“WIDGET_TYPE”,
“READER_WRITER_TYPE”,
“REPOSITORY_NAME”,
“SESSION_NAME”,
“WIDGET_INSTANCE_ID”,
“SESSION_INSTANCE_NAME”,
“CNX_NAME”,
“WORKFLOW_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_ALL_CNXS.CNX_NAME, SESSION_ALL_CNXS.READER_WRITER_TYPE, CASE WHEN SESSION_ALL_CNXS.WIDGET_TYPE = 2 THEN ‘Target Connection’ ELSE CASE WHEN SESSION_ALL_CNXS.WIDGET_TYPE IN (1,3,56,45,55,84) THEN ‘Source Connection’ ELSE NULL END END AS WIDGET_TYPE, (COUNT(SESSION_ALL_CNXS.WIDGET_INSTANCE_ID)) AS WIDGET_INSTANCE_ID FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_WIDGET_CNXS SESSION_ALL_CNXS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_SESS_LOG SESSION_LOG WHERE (ALL_SESSIONS.SESSION_ID = SESSION_ALL_CNXS.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_ALL_CNXS. SESSION_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_ALL_CNXS.CNX_NAME, SESSION_ALL_CNXS.READER_WRITER_TYPE, CASE WHEN SESSION_ALL_CNXS.WIDGET_TYPE = 2 THEN ‘Target Connection’ ELSE CASE WHEN SESSION_ALL_CNXS.WIDGET_TYPE IN (1,3,56,45,55,84) THEN ‘Source Connection’ ELSE NULL END END) “query”
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the total number of sessions currently running by PowerCenter Server. This cached report is attached to the 5 Minute Refresh schedule, which refreshes every 5 minutes.
select “REPOSITORY_NAME”,
“TASK_ID”,
“SERVER_NAME”,
“END_TIME”
from (SELECT SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_TASK_REPOSIT_INFO.REPOSITORY_NAME, TO_DATE(TO_CHAR(SESSION_TASK_INST_RUN.END_TIME, ‘yyyy-MM-dd’), ‘yyyy-MM-dd’) as END_TIME, (COUNT(SESSION_TASK_INST_RUN.TASK_ID)) as TASK_ID FROM REP_TASK_INST_RUN SESSION_TASK_INST_RUN, REP_REPOSIT_INFO SESSION_TASK_REPOSIT_INFO WHERE (SESSION_TASK_INST_RUN.TASK_ID <> SESSION_TASK_REPOSIT_INFO.REPOSITORY_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68) AND (SESSION_TASK_INST_RUN.END_TIME IS NULL ) GROUP BY SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_TASK_REPOSIT_INFO.REPOSITORY_NAME, TO_DATE(TO_CHAR(SESSION_TASK_INST_RUN.END_TIME, ‘yyyy-MM-dd’), ‘yyyy-MM-dd’)) “query”
Displays session details, by repository and folder, for sessions that started yesterday and failed. This report is the first node in a workflow associated with the Failed Session Statistics (Yesterday) primary report.
select “SERVER_NAME”,
“FIRST_ERROR_MSG”,
“FAILED_ROWS”,
“LAST_ERROR_CODE”,
“SESSION_TIMESTAMP_SUB”,
“SUCCESSFUL_SOURCE_ROWS”,
“SESSION_INSTANCE_NAME”,
“SESSION_TIMESTAMP”,
“SUCCESSFUL_ROWS”,
“SESSION_LOG_FILE”,
“WORKFLOW_NAME”,
“ACTUAL_START”,
“SUBJECT_AREA”,
“FIRST_ERROR_CODE”,
“FAILED_SOURCE_ROWS”,
“SESSION_NAME”,
“ACTUAL_START_SUB”,
“MAPPING_NAME”,
“LAST_ERROR”,
“REPOSITORY_NAME”,
“TOTAL_ERR”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) as ACTUAL_START_SUB, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR as LAST_ERROR, (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS, (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS, (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) as SESSION_TIMESTAMP_SUB FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(SYSDATE-1, ‘DD-MON-YYYY’) AND CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END = ‘Failed’ ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START_SUB”, “SUBJECT_AREA”
Displays the total number of sessions that started yesterday and resulted in failed statuses by repository by folder. A session can fail due to read login failure or custom select statement error.
select “ACTUAL_START”,
“RUN_STATUS_CODE”,
“REPOSITORY_NAME”,
“INSTANCE_ID”,
“SUBJECT_AREA”,
“WORKFLOW_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) AS “ACTUAL_START”, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END AS RUN_STATUS_CODE, (COUNT( CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN SESSION_LOG.INSTANCE_ID END)) AS INSTANCE_ID FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(SYSDATE-1, ‘DD-MON-YYYY’) AND CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END = ‘Failed’ ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START”
Displays the total number of sessions that ran and the total session run duration for yesterday by server by repository. This cached report is attached to the Midnight Daily schedule, where it runs everyday at midnight.
select “ACTUAL_START”,
“SESSION_TIMESTAMP”,
“SERVER_NAME”,
“REPOSITORY_NAME”,
“INSTANCE_ID”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) AS “ACTUAL_START”, SESSION_TASK_INST_RUN.SERVER_NAME, (COUNT(SESSION_LOG.INSTANCE_ID)) AS INSTANCE_ID, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) AS “SESSION_TIMESTAMP” FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(SYSDATE-1, ‘DD-MON-YYYY’) ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) , SESSION_TASK_INST_RUN.SERVER_NAME) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START”
Displays the total number of sessions that ran and the total session run duration for any day of week in any given month of the year by server by repository. For example, all Mondays in September are represented in one row if that month had 4 Mondays.
select “TIME_STAMP”,
“ACTUAL_START_FOURTH”,
“SERVER_NAME”,
“DATE_NAME”,
“REPOSITORY_NAME”,
“INSTANCE_ID”
from (SELECT distinct SESSION_REPOSIT_INFO.REPOSITORY_NAME, (COUNT(SESSION_LOG.INSTANCE_ID)) as INSTANCE_ID, SESSION_TASK_INST_RUN.SERVER_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’YYYYMM’) as “ACTUAL_START_FIRST”, TO_CHAR(SESSION_LOG.ACTUAL_START,’D’) as “ACTUAL_START_SECOND”, TO_CHAR(SESSION_LOG.ACTUAL_START,’MON-YYYY’) as ACTUAL_START_FOURTH, TO_CHAR(SESSION_LOG.ACTUAL_START,’Day’) as DATE_NAME, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) as TIME_STAMP FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’YYYYMM’), SESSION_TASK_INST_RUN.SERVER_NAME , TO_CHAR(SESSION_LOG.ACTUAL_START,’D’), TO_CHAR(SESSION_LOG.ACTUAL_START,’MON-YYYY’) , TO_CHAR(SESSION_LOG.ACTUAL_START,’Day’)) “query”
where “ACTUAL_START_FOURTH” in (‘<Session_Run_Month>’)
order by “REPOSITORY_NAME”
Displays session run details for any start date by repository by folder. This is a primary report in an analytic workflow.
select “FAILED_SOURCE_ROWS”,
“SERVER_NAME”,
“SESSION_TIMESTAMP”,
“SUCCESSFUL_SOURCE_ROWS”,
“SESSION_INSTANCE_NAME”,
“LAST_ERROR”,
“FIRST_ERROR_MSG”,
“SESSION_TIMESTAMP_LOG”,
“SUCCESSFUL_ROWS”,
“WORKFLOW_NAME”,
“FAILED_ROWS”,
“SESSION_NAME”,
“ACTUAL_START”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“LAST_ERROR_CODE”,
“SESSION_LOG_FILE”,
“REPOSITORY_NAME”,
“MAPPING_NAME”,
“TOTAL_ERR”,
“SUBJECT_AREA”,
“FIRST_ERROR_CODE”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, to_char(SESSION_LOG.ACTUAL_START,’mm/dd/yy HH:MI:SS AM’) as ACTUAL_START , SESSION_LOG.SESSION_TIMESTAMP as SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE as RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR as LAST_ERROR, (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS, (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS, (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) as SESSION_TIMESTAMP_LOG FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’) and “SESSION_TIMESTAMP” between DATE ‘<Start_Date_Between>’ and DATE ‘<Date_And>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all sessions that started yesterday and had rejected rows by repository and folder. This cached report is attached to the Midnight Daily schedule, where it runs everyday at midnight.
select “SUCCESSFUL_ROWS”,
“LAST_ERROR_CODE”,
“FIRST_ERROR_CODE”,
“SESSION_TIMESTAMP_DIFF”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“ACTUAL_START_TIME”,
“FAILED_ROWS”,
“LAST_ERROR”,
“SESSION_INSTANCE_NAME”,
“REPOSITORY_NAME”,
“TOTAL_ERR”,
“SERVER_NAME”,
“FIRST_ERROR_MSG”,
“SUBJECT_AREA”,
“SESSION_NAME”,
“FAILED_SOURCE_ROWS”,
“ACTUAL_START”,
“WORKFLOW_NAME”,
“MAPPING_NAME”,
“SESSION_LOG_FILE”,
“SESSION_TIMESTAMP”,
“SUCCESSFUL_SOURCE_ROWS”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) as ACTUAL_START_TIME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE as RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR as LAST_ERROR, (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS, (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS, (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) as SESSION_TIMESTAMP_DIFF FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) =TO_CHAR(SYSDATE-1, ‘DD-MON-YYYY’) ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.RUN_STATUS_CODE, SESSION_LOG.SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR HAVING (SUM(SESSION_LOG.FAILED_ROWS)) > 0) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START_TIME”, “SUBJECT_AREA”
Displays the load statistics for each table for last month by repository by folder. This is a primary report in an analytic workflow.
select “MAPPING_NAME”,
“SESSION_NAME”,
“FAILED_ROWS”,
“REPOSITORY_NAME”,
“TABLE_BUSNAME”,
“SESSION_INSTANCE_ID”,
“SUBJECT_AREA”,
“SESSION_INSTANCE_NAME”,
“SUCCESSFUL_ROWS”,
“WIDGET_NAME”,
“WORKFLOW_NAME”,
“START_TIME”,
“SUCCESSFUL_AFFECTED_ROWS”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_TBL_LOG.START_TIME,’MON-YYYY’) as “START_TIME”, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TBL_LOG.WIDGET_NAME, SESSION_TBL_LOG.TABLE_BUSNAME, (SUM(SESSION_TBL_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_TBL_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_TBL_LOG.SUCCESSFUL_AFFECTED_ROWS)) as SUCCESSFUL_AFFECTED_ROWS, (COUNT(SESSION_TBL_LOG.SESSION_INSTANCE_ID)) as SESSION_INSTANCE_ID FROM REP_SESS_LOG SESSION_LOG, REP_SESS_TBL_LOG SESSION_TBL_LOG, REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (SESSION_LOG.SESSION_ID = SESSION_TBL_LOG.SESSION_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TBL_LOG.SESSION_VERSION_NUMBER AND SESSION_LOG.INSTANCE_ID = SESSION_TBL_LOG.SESSION_INSTANCE_ID AND SESSION_TBL_LOG.START_TIME >= SESSION_LOG.ACTUAL_START AND SESSION_TBL_LOG.START_TIME <= SESSION_LOG.SESSION_TIMESTAMP AND ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) AND (TO_CHAR(SESSION_TBL_LOG.START_TIME,’MON-YYYY’) = TO_CHAR(TO_DATE(’01’||TO_CHAR(SYSDATE,’MON-YY’),’DD-MON-YY’)-1,’MON-YYYY’) ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_TBL_LOG.START_TIME,’MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TBL_LOG.WIDGET_NAME, SESSION_TBL_LOG.TABLE_BUSNAME) “query”
order by “REPOSITORY_NAME”, “START_TIME”, “SUBJECT_AREA”
Displays, by folder and repository, session details for all sessions that started today and failed. This report is the first node in the analytic workflow associated with the Today’s Failed Session Statistics primary report.
select “FAILED_ROWS”,
“SESSION_LOG_FILE”,
“SUCCESSFUL_SOURCE_ROWS”,
“SESSION_TIMESTAMP_SUM”,
“ACTUAL_START_CONVERSION”,
“LAST_ERROR”,
“FAILED_SOURCE_ROWS”,
“MAPPING_NAME”,
“FIRST_ERROR_MSG”,
“SESSION_TIMESTAMP”,
“SERVER_NAME”,
“SUBJECT_AREA”,
“SUCCESSFUL_ROWS”,
“LAST_ERROR_CODE”,
“WORKFLOW_NAME”,
“ACTUAL_START”,
“FIRST_ERROR_CODE”,
“TOTAL_ERR”,
“SESSION_INSTANCE_NAME”,
“REPOSITORY_NAME”,
“SESSION_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) as ACTUAL_START_CONVERSION, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR as LAST_ERROR, (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS, (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS, (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS, (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS, (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR, (SUM( CASE WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL THEN (SESSION_LOG.SESSION_TIMESTAMP – SESSION_LOG.ACTUAL_START) * 86400 END)) as SESSION_TIMESTAMP_SUM FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_TASK_INST_RUN SESSION_TASK_INST_RUN WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER AND SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER AND SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(TRUNC(SYSDATE), ‘DD-MON-YYYY’) AND CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END = ‘Failed’ ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, ALL_SESSIONS.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TASK_INST_RUN.SERVER_NAME, SESSION_LOG.ACTUAL_START, SESSION_LOG.SESSION_TIMESTAMP, SESSION_LOG.SESSION_LOG_FILE, SESSION_LOG.FIRST_ERROR_CODE, SESSION_LOG.FIRST_ERROR_MSG, SESSION_LOG.LAST_ERROR_CODE, SESSION_LOG.LAST_ERROR) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START_CONVERSION”, “SUBJECT_AREA”
Displays, by folder and repository, the target details for all target instances in sessions that started today and failed. This report is the second node in a workflow associated with the Today’s Failed Session Statistics primary report.
select “MAPPING_NAME”,
“THROUGHPUT”,
“SUBJECT_AREA”,
“GROUP_NAME”,
“END_TIME”,
“TABLE_BUSNAME”,
“REPOSITORY_NAME”,
“MAPPLET_INSTANCE_NAME”,
“WORKFLOW_NAME”,
“TABLE_INSTANCE_NAME”,
“ACTUAL_START_CONVERSION”,
“PARTITION_NAME”,
“SESSION_INSTANCE_NAME”,
“WIDGET_NAME”,
“SUCCESSFUL_ROWS”,
“SUCCESSFUL_AFFECTED_ROWS”,
“LAST_ERROR_CODE”,
“START_TIME”,
“LAST_ERROR”,
“FAILED_ROWS”,
“SESSION_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) AS ACTUAL_START_CONVERSION, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, SESSION_LOG.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TBL_LOG.MAPPLET_INSTANCE_NAME, SESSION_TBL_LOG.WIDGET_NAME, SESSION_TBL_LOG.TABLE_INSTANCE_NAME, SESSION_TBL_LOG.TABLE_BUSNAME, SESSION_TBL_LOG.PARTITION_NAME, SESSION_TBL_LOG.GROUP_NAME, SESSION_TBL_LOG.START_TIME, SESSION_TBL_LOG.END_TIME, SESSION_TBL_LOG.LAST_ERROR_CODE, SESSION_TBL_LOG.LAST_ERROR AS LAST_ERROR, (SUM(SESSION_TBL_LOG.SUCCESSFUL_ROWS)) AS SUCCESSFUL_ROWS, (SUM(SESSION_TBL_LOG.FAILED_ROWS)) AS FAILED_ROWS, (SUM(SESSION_TBL_LOG.SUCCESSFUL_AFFECTED_ROWS)) AS SUCCESSFUL_AFFECTED_ROWS, (SUM(SESSION_TBL_LOG.THROUGHPUT)) AS THROUGHPUT FROM REP_SESS_LOG SESSION_LOG, REP_SESS_TBL_LOG SESSION_TBL_LOG, REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (SESSION_LOG.SESSION_ID = SESSION_TBL_LOG.SESSION_ID AND SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TBL_LOG.SESSION_VERSION_NUMBER AND SESSION_LOG.INSTANCE_ID = SESSION_TBL_LOG.SESSION_INSTANCE_ID AND SESSION_TBL_LOG.START_TIME >= SESSION_LOG.ACTUAL_START AND SESSION_TBL_LOG.START_TIME <= SESSION_LOG.SESSION_TIMESTAMP AND ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) AND (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(TRUNC(SYSDATE), ‘DD-MON-YYYY’) AND CASE WHEN SESSION_LOG.RUN_STATUS_CODE <= 9 THEN CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 1 THEN ‘Succeeded’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 2 THEN ‘Disabled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 3 THEN ‘Failed’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 4 THEN ‘Stopped’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 5 THEN ‘Aborted’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 6 THEN ‘Running’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 7 THEN ‘Suspending’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 8 THEN ‘Suspended’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 9 THEN ‘Stopping’ END END END END END END END END END ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 10 THEN ‘Aborting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 11 THEN ‘Waiting’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 12 THEN ‘Scheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 13 THEN ‘UnScheduled’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 14 THEN ‘Unknown’ ELSE CASE WHEN SESSION_LOG.RUN_STATUS_CODE = 15 THEN ‘Terminated’ END END END END END END END = ‘Failed’ ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’), ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, SESSION_LOG.SESSION_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, SESSION_LOG.MAPPING_NAME, SESSION_TBL_LOG.MAPPLET_INSTANCE_NAME, SESSION_TBL_LOG.WIDGET_NAME, SESSION_TBL_LOG.TABLE_INSTANCE_NAME, SESSION_TBL_LOG.TABLE_BUSNAME, SESSION_TBL_LOG.PARTITION_NAME, SESSION_TBL_LOG.GROUP_NAME, SESSION_TBL_LOG.START_TIME, SESSION_TBL_LOG.END_TIME, SESSION_TBL_LOG.LAST_ERROR_CODE, SESSION_TBL_LOG.LAST_ERROR) “query”
order by “REPOSITORY_NAME”, “ACTUAL_START_CONVERSION”, “SUBJECT_AREA”
Displays the top 10 sessions that had the most number of rows rejected. This cached report is attached to the Midnight Daily schedule, where it runs everyday at midnight.
select “NO_FAILED_ROWS”,
“SUBJECT_AREA”,
“SESSION_INSTANCE_NAME”,
“WORKFLOW_NAME”,
“REPOSITORY_NAME”,
“ACTUAL_START”
from (select * from( SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) as ACTUAL_START, (SUM(SESSION_LOG.FAILED_ROWS)) as no_failed_rows, RANK() OVER(ORDER BY (SUM(SESSION_LOG.FAILED_ROWS)) desc) as RANK FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) and (TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) = TO_CHAR(SYSDATE-1,’DD-MON-YYYY’)) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, SESSION_LOG.SESSION_INSTANCE_NAME, ALL_SESSIONS.SUBJECT_AREA, SESSION_LOG.WORKFLOW_NAME, TO_CHAR(SESSION_LOG.ACTUAL_START,’DD-MON-YYYY’) HAVING (SUM(SESSION_LOG.FAILED_ROWS)) > 0 AND (SUM(SESSION_LOG.FAILED_ROWS)) IS NOT NULL ) where RANK<6) “query”
order by “REPOSITORY_NAME”, “SESSION_INSTANCE_NAME”, “ACTUAL_START”, “NO_FAILED_ROWS”
Displays by folder the top 5 most common error messages encountered by sessions.
select “REPOSITORY_NAME”,
“FIRST_ERROR_MSG”,
“NUM”,
“SUBJECT_AREA”
from (select * from ( SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, to_char(SUBSTR(SESSION_LOG.FIRST_ERROR_MSG,1,254)) as FIRST_ERROR_MSG, ALL_SESSIONS.SUBJECT_AREA, (COUNT(SESSION_LOG.INSTANCE_ID)) as NUM, RANK() OVER(ORDER BY (COUNT(SESSION_LOG.INSTANCE_ID)) DESC) AS RANK FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_LOG SESSION_LOG, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) AND (TO_CHAR(SUBSTR(SESSION_LOG.FIRST_ERROR_MSG,1,254)) IS NOT NULL) AND (TO_CHAR(SUBSTR(SESSION_LOG.FIRST_ERROR_MSG,1,254)) <> ‘No errors encountered.’ ) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, TO_CHAR(SUBSTR(SESSION_LOG.FIRST_ERROR_MSG,1,254)), ALL_SESSIONS.SUBJECT_AREA HAVING ((COUNT(SESSION_LOG.INSTANCE_ID)) IS NOT NULL )) where RANK<6) “query”
Workflow Execution
Displays the run statistics of all workflows by repository by folder. This is a primary report in an analytic workflow.
select “USER_NAME”,
“START_TIME”,
“SERVER_NAME”,
“LOG_FILE”,
“REPOSITORY_NAME”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“RUN_ERR_MSG”,
case when (“RUN_TYPE” = 1) then (‘Normal’) else (case when (“RUN_TYPE” = 2) then (‘Recovery’) else (”) end) end as “Calc_RUN_TYPE”,
“SUBJECT_AREA”,
“END_TIME”,
“RUN_ERR_CODE”,
“WORKFLOW_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_RUN.USER_NAME, WORKFLOW_RUN.SERVER_NAME, WORKFLOW_RUN.START_TIME AS START_TIME ,WORKFLOW_RUN.END_TIME as END_TIME , WORKFLOW_RUN.RUN_STATUS_CODE as RUN_STATUS_CODE, WORKFLOW_RUN.RUN_ERR_CODE, WORKFLOW_RUN.RUN_ERR_MSG as RUN_ERR_MSG, WORKFLOW_RUN.LOG_FILE as LOG_FILE, WORKFLOW_RUN.RUN_TYPE as RUN_TYPE FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_WFLOW_RUN WORKFLOW_RUN WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_RUN.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_RUN.VERSION_NUMBER )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’) and “START_TIME” between DATE ‘<Start_Date>’ and DATE ‘<End_Date>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the run statistics of all tasks in a workflow by repository by folder. This report is the first node in the analytic workflow associated with the Workflow Run Details primary report.
select “START_TIME”,
“RUN_ERR_CODE”,
“RUN_ERR_MSG”,
“END_TIME”,
“TASK_TYPE_NAME”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“INSTANCE_NAME”,
“WORKFLOW_NAME”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_TASK_INST_RUN.TASK_TYPE_NAME, WORKFLOW_TASK_INST_RUN.INSTANCE_NAME, WORKFLOW_TASK_INST_RUN.START_TIME as START_TIME, to_char(WORKFLOW_TASK_INST_RUN.END_TIME,’mm/dd/yy HH:MI:SS AM’) as END_TIME, WORKFLOW_TASK_INST_RUN.RUN_STATUS_CODE AS RUN_STATUS_CODE, WORKFLOW_TASK_INST_RUN.RUN_ERR_CODE, WORKFLOW_TASK_INST_RUN.RUN_ERR_MSG As RUN_ERR_MSG FROM REP_WFLOW_RUN WORKFLOW_RUN, REP_TASK_INST_RUN WORKFLOW_TASK_INST_RUN, REP_WORKFLOWS ALL_WORKFLOWS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO WHERE (WORKFLOW_RUN.WORKFLOW_ID = WORKFLOW_TASK_INST_RUN.WORKFLOW_ID AND WORKFLOW_RUN.WORKFLOW_RUN_ID = WORKFLOW_TASK_INST_RUN.WORKFLOW_RUN_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_RUN.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_RUN.VERSION_NUMBER )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’) and “START_TIME” between DATE ‘<Start_Date>’ and DATE ‘<End_Date>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the run statistics of all worklets by repository by folder.
select “RUN_ERR_MSG”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“END_TIME”,
“WORKFLOW_NAME”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“START_TIME”,
“RUN_ERR_CODE”,
“TASK_NAME”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_RUN.WORKFLOW_NAME, WORKLET_RUN.START_TIME as START_TIME, to_char(WORKLET_RUN.END_TIME,’mm/dd/yy HH:MI:SS AM’) as END_TIME, WORKLET_RUN.RUN_STATUS_CODE AS RUN_STATUS_CODE, WORKLET_RUN.RUN_ERR_CODE, WORKLET_RUN.RUN_ERR_MSG AS RUN_ERR_MSG FROM REP_ALL_TASKS ALL_WORKLETS, REP_TASK_INST_RUN WORKLET_RUN, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID = WORKLET_RUN.TASK_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_RUN.TASK_VERSION_NUMBER AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’) and “START_TIME” between DATE ‘<Start_Date>’ and DATE ‘<End_Date>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the run statistics of all tasks in a worklet by repository by folder.
select “WORKFLOW_NAME”,
“END_TIME”,
“RUN_ERR_CODE”,
“REPOSITORY_NAME”,
“RUN_ERR_MSG”,
“INSTANCE_NAME”,
“TASK_TYPE_NAME”,
“SUBJECT_AREA”,
“TASK_NAME”,
case when (“RUN_STATUS_CODE” <= 9) then (case when (“RUN_STATUS_CODE” = 1) then (‘Succeeded’) else (case when (“RUN_STATUS_CODE” = 2) then (‘Disabled’) else (case when (“RUN_STATUS_CODE” = 3) then (‘Failed’) else (case when (“RUN_STATUS_CODE” = 4) then (‘Stopped’) else (case when (“RUN_STATUS_CODE” = 5) then (‘Aborted’) else (case when (“RUN_STATUS_CODE” = 6) then (‘Running’) else (case when (“RUN_STATUS_CODE” = 7) then (‘Suspending’) else (case when (“RUN_STATUS_CODE” = 8) then (‘Suspended’) else (case when (“RUN_STATUS_CODE” = 9) then (‘Stopping’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“RUN_STATUS_CODE” = 10) then (‘Aborting’) else (case when (“RUN_STATUS_CODE” = 11) then (‘Waiting’) else (case when (“RUN_STATUS_CODE” = 12) then (‘Scheduled’) else (case when (“RUN_STATUS_CODE” = 13) then (‘UnScheduled’) else (case when (“RUN_STATUS_CODE” = 14) then (‘Unknown’) else (case when (“RUN_STATUS_CODE” = 15) then (‘Terminated’) else (”) end) end) end) end) end) end) end as “Calc_RUN_STATUS_CODE”,
“START_TIME”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_RUN.WORKFLOW_NAME, WORKLET_TASK_INST_RUN.TASK_TYPE_NAME, WORKLET_TASK_INST_RUN.INSTANCE_NAME, WORKLET_TASK_INST_RUN.START_TIME as START_TIME, WORKLET_TASK_INST_RUN.END_TIME as END_TIME, WORKLET_TASK_INST_RUN.RUN_STATUS_CODE as RUN_STATUS_CODE, WORKLET_TASK_INST_RUN.RUN_ERR_CODE, WORKLET_TASK_INST_RUN.RUN_ERR_MSG as RUN_ERR_MSG FROM REP_ALL_TASKS ALL_WORKLETS, REP_TASK_INST_RUN WORKLET_RUN, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_TASK_INST_RUN WORKLET_TASK_INST_RUN WHERE (ALL_WORKLETS.TASK_ID = WORKLET_RUN.TASK_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_RUN.TASK_VERSION_NUMBER AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND WORKLET_RUN.WORKFLOW_ID = WORKLET_TASK_INST_RUN.WORKFLOW_ID AND WORKLET_RUN.WORKFLOW_RUN_ID = WORKLET_TASK_INST_RUN.WORKFLOW_RUN_ID AND WORKLET_RUN.WORKLET_RUN_ID = WORKLET_TASK_INST_RUN.WORKLET_RUN_ID )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’) and “START_TIME” between DATE ‘<Start_Date>’ and DATE ‘<End_Date>’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Monitoring Reports
Use this to find all mappings that have SAP ABAP.
SELECT A.SUBJECT_AREA,A.MAPPING_NAME,A.MAPPING_LAST_SAVED,
B.PROGRAM_NAME,B.PROGRAM_TYPE, B.INSTALL_TIME,
B.HOST_MACHINE,B.USER_NAME, B.CLIENT_SPACE
FROM dbo.REP_ALL_MAPPINGS A, dbo.OPB_PROGRAM_INFO B
WHERE A.MAPPING_ID = B.MAPPING_ID
This will show “most” errors in the repository as well as sessions with rejected rows for the past 24 hours.
SELECT C.SUBJECT_AREA,A.WORKFLOW_NAME,A.START_TIME,A.END_TIME,
A.USER_NAME,B.SESSION_NAME,B.MAPPING_NAME,
B.SUCCESSFUL_ROWS,B.FAILED_ROWS,B.SUCCESSFUL_SOURCE_ROWS,
B.FAILED_SOURCE_ROWS, B.FIRST_ERROR_CODE,B.FIRST_ERROR_MSG,
B.ACTUAL_START
FROM dbo.REP_WFLOW_RUN A, dbo.REP_SESS_LOG B, dbo.REP_SUBJECT C
WHERE A.SUBJECT_ID = B.SUBJECT_ID AND B.SUBJECT_ID = C.SUBJECT_ID
AND A.WORKFLOW_ID = B.WORKFLOW_ID
AND A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID
AND A.START_TIME >= DATEADD(hh,-24,GetDate())
AND ( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3
This report is Keeping track of changes and will show you when the last time the mapping been checked in/Saved.
SELECT subject_area,mapping_name,mapping_last_saved FROM dbo.REP_ALL_MAPPINGS WHERE mapping_last_saved
Workflow Run Times with row count.
SELECT a.SUBJECT_AREA as Folder, a.WORKFLOW_NAME, DATEADD(dd, DATEDIFF(dd, 0, START_TIME), 0), DateDiff(minute,a.START_TIME,END_TIME) as Run_Time_Minutes, sum(successful_rows) as Row_Count FROM INFPRD9.dbo.REP_WFLOW_RUN A, dbo.REP_SESS_LOG B WHERE A.SUBJECT_ID = B.SUBJECT_ID AND A.WORKFLOW_ID = B.WORKFLOW_ID AND A.WORKFLOW_RUN_ID = B.WORKFLOW_RUN_ID AND START_TIME >= DATEADD(dd,-30,getdate()) Group By a.SUBJECT_AREA, a.WORKFLOW_NAME, DATEADD(dd, DATEDIFF(dd, 0, START_TIME), 0), DateDiff(minute,a.START_TIME,END_TIME)
This will give you an idea of what’s scheduled. The result set will show you what is actually scheduled but will also show workflows with schedules but not currently scheduled. I can’t quite figure out how to limit this to only currently scheduled jobs.
SELECT A.SUBJECT_AREA, A.WORKFLOW_NAME, A.SCHEDULER_NAME , A.SUBJECT_ID, A.START_TIME, B.Run_Options FROM dbo.REP_WORKFLOWS a, dbo.OPB_SCHEDULER b WHERE A.Scheduler_ID = b.Scheduler_id AND B.Run_Options NOT IN ( 1,3 ) ORDER BY A.SUBJECT_AREA --Run_Options 1 = run on demand -- 2 = Run Once -- 3 = Run on Demand -- 4 = Run Evry -- 8 = Customized Repeat --A.START_TIME IS NOT NULL
List invalid sessions, mappings & workflows. You can use the output from this as the input for the pmrep validate command.
SELECT SUBJECT_AREA, SUBJECT_ID, TASK_NAME, TASK_ID, IS_VALID, LAST_SAVED, IS_REUSABLE, TASK_TYPE, TASK_TYPE_NAME FROM dbo.REP_ALL_TASKS WHERE TASK_TYPE IN (68, 70, 71) AND IS_VALID = 0
Powercenter Objects Reports
Mappings
Displays source, targets and all transformations used in a mapping by repository and folder.
select “REPOSITORY_NAME”,
“INSTANCE_NAME”,
“WIDGET_TYPE_NAME”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“DESCRIPTION”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, MAPPING_WIDGET_INST.WIDGET_TYPE_NAME as WIDGET_TYPE_NAME, MAPPING_WIDGET_INST.INSTANCE_NAME as INSTANCE_NAME, MAPPING_WIDGET_INST.DESCRIPTION as DESCRIPTION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “WIDGET_TYPE_NAME”, “INSTANCE_NAME”
Displays sessions that are created for invalid mappings by repository and folder.
select “SESSION_INSTANCE_NAME”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“MAPPING_NAME”,
“SESSION_INSTANCE_ID”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_NAME, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as PARENT_MAPPING_IS_VALID, (COUNT(MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_ID)) as SESSION_INSTANCE_ID FROM REP_SESSION_INSTANCES MAPPING_SESSION_INSTANCES, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (MAPPING_SESSION_INSTANCES.SESSION_ID = MAPPING_SESS_PARTITION_DEF. SESSION_ID AND MAPPING_SESSION_INSTANCES.SESSION_VERSION_NUMBER = MAPPING_SESS_PARTITION_DEF.VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) AND ALL_MAPPINGS.PARENT_MAPPING_IS_VALID = 0 GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, PARENT_MAPPING_IS_VALID, MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_NAME HAVING (COUNT(MAPPING_SESSION_INSTANCES.SESSION_INSTANCE_ID)) > 0) “query”
where case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end = ‘No’ and “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_INSTANCE_ID” > 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “SESSION_INSTANCE_NAME”, “SESSION_INSTANCE_ID”
Displays targets in an invalid mapping by repository and folder.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”,
“TARGET_VERSION_NUMBER”,
“REPOSITORY_NAME”, “TARGET_NAME”,
“PARENT_TARGET_DATABASE_TYPE”, “TARGET_DESC”,
“PARENT_TARGET_BUSINESS_NAME”,
“MAPPING_NAME”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TARGETS.TARGET_NAME, MAPPING_ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME, MAPPING_ALL_TARGETS.TARGET_DESCRIPTION as TARGET_DESC, MAPPING_ALL_TARGETS.TARGET_VERSION_NUMBER, MAPPING_ALL_TARGETS.TARGET_VERSION_STATUS, MAPPING_ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, MAPPING_ALL_TARGETS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG FROM REP_ALL_TARGETS MAPPING_ALL_TARGETS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (MAPPING_ALL_TARGETS.PARENT_TARGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TARGETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 2 AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “TARGET_NAME”, “PARENT_TARGET_BUSINESS_NAME”
Displays invalid mappings by repository and folder.
select “MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“MAPPING_LAST_SAVED”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “MAPPING_DESCRIPTION”, “MAPPING_VERSION_NUMBER”
Displays mappings by repository and folder. It also displays properties of the mapping such as the number of sources used in a mapping, the number of transformations, and the number of targets.
select “WIDGET_TYPE5”,
“WIDGET_TYPE4”,
“WIDGET_TYPE6”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
“SUBJECT_AREA”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
case when (“IS_SHORTCUT1” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT1”,
“MAPPING_NAME”,
“MAPPING_LAST_SAVED”,
“DESCRIPTION”,
case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
case when (“IS_SHORTCUT2″ = 1) then (‘ALL_MAPPINGS.PARENT_SUBJECT_AREA’) else (”) end as “Calc_IS_SHORTCUT2”,
“WIDGET_TYPE1”,
“WIDGET_TYPE2”,
“WIDGET_TYPE3”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS AS MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED AS MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT AS IS_SHORTCUT1, ALL_MAPPINGS.PARENT_SUBJECT_AREA AS PARENT_SUBJECT_AREA, ALL_MAPPINGS.IS_SHORTCUT AS IS_SHORTCUT2, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID AS PARENT_MAPPING_IS_VALID , (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE1, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 2 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE2, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE3, (COUNT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 44 THEN MAPPING_WIDGET_INST.INSTANCE_ID END)) AS WIDGET_TYPE4, (COUNT(DISTINCT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPING_WIDGET_INST.WIDGET_ID END))) AS WIDGET_TYPE5, (COUNT(DISTINCT( CASE WHEN MAPPING_WIDGET_INST.WIDGET_TYPE = 2 THEN MAPPING_WIDGET_INST.WIDGET_ID END))) AS WIDGET_TYPE6 FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.PARENT_SUBJECT_AREA, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “DESCRIPTION”, “MAPPING_VERSION_NUMBER”, “Calc_MAPPING_VERSION_STATUS”, “MAPPING_LAST_SAVED”, “Calc_IS_SHORTCUT1”, “Calc_IS_SHORTCUT2”, “Calc_PARENT_MAPPING_IS_VALID”
Displays Lookup transformations used in a mapping by repository and folder.
select case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“ATTR_VALUE”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“INSTANCE_NAME”,
“DESCRIPTION”,
“WIDGET_VERSION_NUMBER”,
“WIDGET_TYPE_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_WIDGET_INST.INSTANCE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME, MAPPING_WIDGET_INST.DESCRIPTION as DESCRIPTION, CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_DATATYPE IN (1, 4) THEN CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_TYPE = 5 THEN CASE WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE = 1 THEN ‘Yes’ ELSE ‘No’ END ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE END ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE END ATTR_VALUE, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_STATUS, MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE, MAPPING_ALL_TRANSFORMS.IS_SHORTCUT, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_WIDGET_ATTR MAPPING_TRANSFORM_WIDGET_ATTR, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_ID = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE AND MAPPING_TRANSFORM_WIDGET_ATTR.SESSION_TASK_ID = 0 AND MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE NOT IN (1, 2, 44) AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE ) AND (MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME = ‘Lookup Procedure’ AND MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_ID = 2 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets used in a mapping by repository and folder.
select “SUBJECT_AREA”,
“MAPPING_NAME”,
“MAPPLET_NAME”,
case when (“PARENT_MAPPLET_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPLET_IS_VALID”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“DESCRIPTION”,
“MAPPLET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPING_ALL_MAPPLETS.MAPPLET_DESCRIPTION AS DESCRIPTION, MAPPING_ALL_MAPPLETS.MAPPLET_VERSION_NUMBER AS MAPPLET_VERSION_NUMBER, MAPPING_ALL_MAPPLETS.MAPPLET_VERSION_STATUS AS MAPPLET_VERSION_STATUS, MAPPING_ALL_MAPPLETS.IS_SHORTCUT AS IS_SHORTCUT, MAPPING_ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID AS PARENT_MAPPLET_IS_VALID FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPLETS MAPPING_ALL_MAPPLETS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_MAPPLETS.REF_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_MAPPLETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 44)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays metadata extensions associated with a mapping and the properties of the metadata extensions.
select “DOMAIN_DESCRIPTION”,
“MAPPING_NAME”,
“METADATA_EXTN_DESCRIPTION”,
“METADATA_EXTN_NAME”,
“SUBJECT_AREA”,
“METADATA_EXTN_VALUE”,
“DOMAIN_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_METADATA_EXTNS.DOMAIN_NAME, MAPPING_METADATA_EXTNS.DOMAIN_DESCRIPTION as DOMAIN_DESCRIPTION, MAPPING_METADATA_EXTNS.METADATA_EXTN_NAME , MAPPING_METADATA_EXTNS.METADATA_EXTN_VALUE as METADATA_EXTN_VALUE, MAPPING_METADATA_EXTNS.METADATA_EXTN_DESCRIPTION as METADATA_EXTN_DESCRIPTION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_METADATA_EXTNS MAPPING_METADATA_EXTNS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_METADATA_EXTNS. METADATA_EXTN_OBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_METADATA_EXTNS. VERSION_NUMBER AND ALL_MAPPINGS.SUBJECT_ID = MAPPING_METADATA_EXTNS.SUBJECT_ID AND MAPPING_METADATA_EXTNS.METADATA_EXTN_OBJECT_TYPE = 21)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all transformations from source to target in a mapping and how they are connected to each other.
select “FROM_DESCRIPTION”,
“MAPPING_NAME”,
“FROM_TYPE”,
“TO_NAME”,
“TO_DESCRIPTION”,
“TO_TYPE”,
“FROM_NAME”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_FROM_WIDGET_INST.INSTANCE_NAME as FROM_NAME, MAPPING_FROM_WIDGET_INST.WIDGET_TYPE_NAME as FROM_TYPE , MAPPING_FROM_WIDGET_INST.DESCRIPTION as FROM_DESCRIPTION , MAPPING_TO_WIDGET_INST.INSTANCE_NAME as TO_NAME , MAPPING_TO_WIDGET_INST.WIDGET_TYPE_NAME as TO_TYPE , MAPPING_TO_WIDGET_INST.DESCRIPTION as TO_DESCRIPTION from REP_WIDGET_DEP MAPPING_WIDGET_DEP, REP_WIDGET_INST MAPPING_FROM_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_TO_WIDGET_INST WHERE (MAPPING_WIDGET_DEP.FROM_INSTANCE_ID = MAPPING_FROM_WIDGET_INST.INSTANCE_ID AND MAPPING_WIDGET_DEP.MAPPING_ID = MAPPING_FROM_WIDGET_INST.MAPPING_ID AND MAPPING_WIDGET_DEP.VERSION_NUMBER = MAPPING_FROM_WIDGET_INST.VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_DEP.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_DEP. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND MAPPING_WIDGET_DEP.TO_INSTANCE_ID = MAPPING_TO_WIDGET_INST.INSTANCE_ID AND MAPPING_WIDGET_DEP.MAPPING_ID = MAPPING_TO_WIDGET_INST.MAPPING_ID AND MAPPING_WIDGET_DEP.VERSION_NUMBER = MAPPING_TO_WIDGET_INST.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “FROM_NAME”, “FROM_TYPE”, “FROM_DESCRIPTION”
Displays all transformations from source to target in a mapping and how they are connected to each other through ports.
select “FROM_OBJECT_FIELD_NAME”,
“FROM_OBJECT_NAME”,
“TO_OBJECT_FIELD_NAME”,
“FROM_OBJECT_TYPE_NAME”,
“TO_OBJECT_TYPE_NAME”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“TO_OBJECT_NAME”,
“MAPPING_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_TYPE_NAME, MAPPING_CONN_PORTS.FROM_OBJECT_FIELD_NAME, MAPPING_CONN_PORTS.TO_OBJECT_NAME, MAPPING_CONN_PORTS.TO_OBJECT_TYPE_NAME, MAPPING_CONN_PORTS.TO_OBJECT_FIELD_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_MAPPING_CONN_PORTS MAPPING_CONN_PORTS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_CONN_PORTS.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_CONN_PORTS. MAPPING_VERSION_NUMBER AND ALL_MAPPINGS.SUBJECT_ID = MAPPING_CONN_PORTS.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings defined as a shortcut by repository and folder.
select “PARENT_MAPPING_LAST_SAVED”,
“PARENT_MAPPING_DESCRIPTION”,
“SUBJECT_AREA”,
“PARENT_MAPPING_VERSION_NUMBER”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
case when (“PARENT_MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_MAPPING_VERSION_ST”,
“MAPPING_LAST_SAVED”,
“PARENT_MAPPING_NAME”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays column names for all sources by repository, folder, and mapping. It also displays properties of these columns such as datatype, precision, and length.
select “SOURCE_NAME”,
“REPOSITORY_NAME”,
“SOURCE_FIELD_PRECISION”,
“SUBJECT_AREA”,
“SOURCE_FIELD_DESCRIPTION”,
“SOURCE_FIELD_BUSINESS_NAME”,
“SOURCE_FIELD_DATATYPE”,
“SOURCE_FIELD_SCALE”,
case when (“SOURCE_FIELD_NULLTYPE” = 1) then (‘Yes’) else (‘No’) end as “Calc_SOURCE_FIELD_NULLTYPE”,
“SOURCE_FIELD_NAME”,
“MAPPING_NAME”,
“SOURCE_FIELD_KEY_TYPE”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_SOURCES.SOURCE_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NUMBER, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_BUSINESS_NAME, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_DESCRIPTION as SOURCE_FIELD_DESCRIPTION, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_DATATYPE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_PRECISION, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_SCALE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_KEY_TYPE, MAPPING_ALL_SOURCE_FLDS.SOURCE_FIELD_NULLTYPE FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_SOURCES MAPPING_ALL_SOURCES, REP_ALL_SOURCE_FLDS MAPPING_ALL_SOURCE_FLDS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_ALL_SOURCE_FLDS. PARENT_SOURCE_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER = MAPPING_ALL_SOURCE_FLDS. PARENT_SOURCE_VERSION_NUMBER AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_SOURCES.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 1 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”
Displays sources defined in a mapping by repository and folder.
select “SOURCE_DESCRIPTION”,
“SOURCE_NAME”,
“REPOSITORY_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“MAPPING_NAME”,
“PARENT_SOURCE_BUSINESS_NAME”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_SOURCES.SOURCE_DATABASE_NAME AS SOURCE_DATABASE_NAME, MAPPING_ALL_SOURCES.SOURCE_NAME AS SOURCE_NAME, MAPPING_ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME AS PARENT_SOURCE_BUSINESS_NAME, MAPPING_ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION, MAPPING_ALL_SOURCES.SOURCE_VERSION_NUMBER AS SOURCE_VERSION_NUMBER, MAPPING_ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, MAPPING_ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE AS PARENT_SOURCE_DATABASE_TYPE, MAPPING_ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_SOURCES MAPPING_ALL_SOURCES WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_SOURCES.PARENT_SOURCE_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_SOURCES.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 1 )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets defined in a mapping by repository and folder.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“TARGET_NAME”,
“PARENT_TARGET_DATABASE_TYPE”,
“TARGET_DESC”,
“PARENT_TARGET_BUSINESS_NAME”,
“MAPPING_NAME”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TARGETS.TARGET_NAME, MAPPING_ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME, MAPPING_ALL_TARGETS.TARGET_DESCRIPTION as TARGET_DESC, MAPPING_ALL_TARGETS.TARGET_VERSION_NUMBER, MAPPING_ALL_TARGETS.TARGET_VERSION_STATUS, MAPPING_ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, MAPPING_ALL_TARGETS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG FROM REP_ALL_TARGETS MAPPING_ALL_TARGETS, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO WHERE (MAPPING_ALL_TARGETS.PARENT_TARGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TARGETS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_WIDGET_INST.WIDGET_TYPE = 2 AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays ports in a transformation by repository, folder, and mapping. It also displays properties of the ports. This report is the second node in a workflow associated with the Mapping List primary report.
select “MAPPING_NAME”,
“EXPRESSION”,
“DATATYPE”,
“SUBJECT_AREA”,
“WIDGET_TYPE_NAME”,
“DESCRIPTION”,
“WGT_PREC”,
“DEFAULT_VALUE”,
“WIDGET_NAME”,
case when (“PORTTYPE” <= 10) then (case when (“PORTTYPE” = 1) then (‘Input Port’) else (case when (“PORTTYPE” = 2) then (‘Output Port’) else (case when (“PORTTYPE” = 3) then (‘Input Output Port’) else (case when (“PORTTYPE” = 6) then (‘Return Port’) else (case when (“PORTTYPE” = 7) then (‘Input Return Port’) else (case when (“PORTTYPE” = 8) then (‘Lookup Port’) else (case when (“PORTTYPE” = 10) then (‘Output Lookup Port’) else (null) end) end) end) end) end) end) end) else (case when (“PORTTYPE” = 14) then (‘Lookup Return Port’) else (case when (“PORTTYPE” = 32) then (‘Variable Port’) else (case when (“PORTTYPE” = 65) then (‘Input Port and Master Detail Port’) else (case when (“PORTTYPE” = 67) then (‘Input Port, Output Port and Master Detail Port’) else (case when (“PORTTYPE” = 130) then (‘Output Port and Generated Key Port’) else (case when (“PORTTYPE” = 258) then (‘Output Port and Generated Column ID Port’) else (case when (“PORTTYPE” = 515) then (‘Input Port, Output Port and Message ID Port’) else (case when (“PORTTYPE” = 1027) then (‘Input Port, Output Port and Message Data Port’) else (case when (“PORTTYPE” = 2050) then (‘Dynamic Lookup Port’) else (null) end) end) end) end) end) end) end) end) end) end as “Calc_PORTTYPE”,
“REPOSITORY_NAME”,
“WGT_SCALE”,
“FIELD_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME, MAPPING_TRANSFORM_WIDGET_FIELD.FIELD_ORDER, MAPPING_TRANSFORM_WIDGET_FIELD.FIELD_NAME, MAPPING_TRANSFORM_WIDGET_FIELD.DESCRIPTION as DESCRIPTION, MAPPING_TRANSFORM_WIDGET_FIELD.PORTTYPE, MAPPING_TRANSFORM_WIDGET_FIELD.DATATYPE, MAPPING_TRANSFORM_WIDGET_FIELD.WGT_PREC, MAPPING_TRANSFORM_WIDGET_FIELD.WGT_SCALE, MAPPING_TRANSFORM_WIDGET_FIELD.DEFAULT_VALUE as DEFAULT_VALUE, MAPPING_TRANSFORM_WIDGET_FIELD.EXPRESSION as EXPRESSION FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS, REP_WIDGET_FIELD MAPPING_TRANSFORM_WIDGET_FIELD WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_TRANSFORM_WIDGET_FIELD. WIDGET_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER = MAPPING_TRANSFORM_WIDGET_FIELD.VERSION_NUMBER AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”
Displays transformations used in a mapping by repository and folder. This report is the first node in the analytic workflow associated with the Mapping List primary report.
select case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“WIDGET_NAME”,
“MAPPING_NAME”,
“WIDGET_DESCRIPTION”,
“WIDGET_TYPE_NAME”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
“WIDGET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, MAPPING_ALL_TRANSFORMS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME AS MAPPING_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME AS WIDGET_TYPE_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_NAME AS WIDGET_NAME, MAPPING_ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER AS WIDGET_VERSION_NUMBER, MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_STATUS AS WIDGET_VERSION_STATUS , MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE AS PARENT_WIDGET_IS_REUSABLE, MAPPING_ALL_TRANSFORMS.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_WIDGET_INST MAPPING_WIDGET_INST, REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘Mapping_Name’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPING_NAME”, “WIDGET_TYPE_NAME”, “WIDGET_NAME”
Displays mappings that do not have any sessions defined by repository and folder.
select “MAPPING_VERSION_NUMBER”,
“MAPPING_DESCRIPTION”,
“SESSION_ID”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“MAPPING_LAST_SAVED”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“REPOSITORY_NAME”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ShortcutFlag”
from (SELECT MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION AS MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.MAPPING_LAST_SAVED, ALL_MAPPINGS.IS_SHORTCUT as SHORTCUTFLAG, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION AS PARENT_MAPPING_DESCRIPTION, ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as VALIDFLAG, (COUNT(DISTINCT(MAPPING_SESS_PARTITION_DEF.SESSION_ID))) AS SESSION_ID FROM REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_ALL_MAPPINGS ALL_MAPPINGS LEFT OUTER JOIN REP_SESS_PARTITION_DEF MAPPING_SESS_PARTITION_DEF ON ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_SESS_PARTITION_DEF. MAPPING_ID WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPING_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME, ALL_MAPPINGS.MAPPING_DESCRIPTION, ALL_MAPPINGS.MAPPING_VERSION_NUMBER, ALL_MAPPINGS.MAPPING_VERSION_STATUS, ALL_MAPPINGS.IS_SHORTCUT, ALL_MAPPINGS.PARENT_SUBJECT_AREA , ALL_MAPPINGS.PARENT_MAPPING_NAME , ALL_MAPPINGS.PARENT_MAPPING_DESCRIPTION , ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER , ALL_MAPPINGS.PARENT_MAPPING_VERSION_STATUS , ALL_MAPPINGS.PARENT_MAPPING_LAST_SAVED , ALL_MAPPINGS.PARENT_MAPPING_IS_VALID, ALL_MAPPINGS.MAPPING_LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_ID” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the data flow from the source to the target by repository and folder. The report lists all the source and target ports, the mappings in which the ports are connected, the transformation expression that show how data for the target port is derived.
select “REPOSITORY_NAME”,
“TARGET_NAME”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“SOURCE_FIELD_NAME”,
“TARGET_COLUMN_NAME”,
“SOURCE_NAME”
from (SELECT DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPINGS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, MAPPING_FLD_MAPPING.SOURCE_NAME as SOURCE_NAME, MAPPING_FLD_MAPPING.SOURCE_FIELD_NAME as SOURCE_FIELD_NAME, MAPPING_FLD_MAPPING.TARGET_NAME as TARGET_NAME, MAPPING_FLD_MAPPING.TARGET_COLUMN_NAME as TARGET_COLUMN_NAME FROM REP_ALL_MAPPINGS ALL_MAPPINGS, REP_REPOSIT_INFO MAPPING_REPOSIT_INFO, REP_FLD_MAPPING MAPPING_FLD_MAPPING WHERE (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_FLD_MAPPING.MAPPING_ID AND ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_FLD_MAPPING. MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_NAME” in (‘<Mapping_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Mapplet
Displays all transformations used in a mapplet by repository and folder.
select “MAPPLET_NAME”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
“INSTANCE_NAME”,
“DESCRIPTION”,
“WIDGET_TYPE_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA as SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME as MAPPLET_NAME, MAPPLET_WIDGET_INST.WIDGET_TYPE_NAME as WIDGET_TYPE_NAME, MAPPLET_WIDGET_INST.INSTANCE_NAME as INSTANCE_NAME, MAPPLET_WIDGET_INST.DESCRIPTION as DESCRIPTION FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets available by repository and folder. It displays properties of the mapplet such as the number of sources used in a mapplet, the number of transformations, or the number of targets. This is a primary report in an analytic workflow.
select “REPOSITORY_NAME”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“SOURCE_COUNT”,
“MAPPLET_NAME”,
“MAPPLET_VERSION_NUMBER”,
“TRANSFORMATION_COUNT”,
“MAPPLET_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets defined as a shortcut by repository and folder.
select case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“PARENT_MAPPLET_LAST_SAVED”,
“MAPPLET_NAME”,
“MAPPLET_LAST_SAVED”,
“PARENT_MAPPLET_VERSION_NUMBER”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“SHORTCUTFLAG” = 1) then (“PARENT_SUBJECT_AREA”) else (”) end as “Calc_IS_SHORTCUT1”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
case when (“PARENT_MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_PARENT_MAPPLET_VERSION_ST”,
“MAPPLET_VERSION_NUMBER”,
“PARENT_MAPPLET_DESCRIPTION”,
“PARENT_MAPPLET_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays column names for all sources by repository, folder, and mapplet. It also displays properties for these columns such as datatype, length, and precision.
select “SOURCE_FIELD_PRECISION”,
“SOURCE_FIELD_SCALE”,
“SOURCE_FIELD_DATATYPE”,
“MAPPLET_NAME”,
case when (“SOURCE_FIELD_NULLTYPE” = 1) then (‘Yes’) else (‘No’) end as “Calc_SOURCE_FIELD_NULLTYPE”,
“SOURCE_FIELD_NAME”,
“SOURCE_NAME”,
“SUBJECT_AREA”,
“SOURCE_FIELD_KEY_TYPE”,
“SOURCE_FIELD_DESCRIPTION”,
“SOURCE_FIELD_BUSINESS_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPLET_ALL_SOURCES.SOURCE_NAME AS SOURCE_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NUMBER AS SOURCE_FIELD_NUMBER, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NAME AS SOURCE_FIELD_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_BUSINESS_NAME AS SOURCE_FIELD_BUSINESS_NAME, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_DESCRIPTION AS SOURCE_FIELD_DESCRIPTION, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_DATATYPE AS SOURCE_FIELD_DATATYPE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_PRECISION AS SOURCE_FIELD_PRECISION, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_SCALE AS SOURCE_FIELD_SCALE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_KEY_TYPE AS SOURCE_FIELD_KEY_TYPE, MAPPLET_ALL_SOURCE_FLDS.SOURCE_FIELD_NULLTYPE AS SOURCE_FIELD_NULLTYPE FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_SOURCES MAPPLET_ALL_SOURCES, REP_ALL_SOURCE_FLDS MAPPLET_ALL_SOURCE_FLDS WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_ID = MAPPLET_ALL_SOURCE_FLDS. PARENT_SOURCE_ID AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER = MAPPLET_ALL_SOURCE_FLDS. PARENT_SOURCE_VERSION_NUMBER AND MAPPLET_ALL_SOURCES.PARENT_SOURCE_ID = MAPPLET_WIDGET_INST.WIDGET_ID AND MAPPLET_ALL_SOURCES.SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_WIDGET_INST.WIDGET_TYPE = 1)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “MAPPLET_NAME”
This is a subreport in the Mapplet Composite Report. This report displays transformations used in a mapplet by repository and folder.
select “WIDGET_DESCRIPTION”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
case when (“PARENT_WIDGET_IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_WIDGET_IS_REUSABLE”,
“WIDGET_VERSION_NUMBER”,
“SUBJECT_AREA”,
“REPOSITORY_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“WIDGET_TYPE_NAME”,
“WIDGET_NAME”,
“MAPPLET_NAME”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME AS MAPPLET_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_TYPE_NAME AS WIDGET_TYPE_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_NAME AS WIDGET_NAME, MAPPLET_ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, MAPPLET_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER AS WIDGET_VERSION_NUMBER, MAPPLET_ALL_TRANSFORMS.WIDGET_VERSION_STATUS AS WIDGET_VERSION_STATUS, MAPPLET_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE AS PARENT_WIDGET_IS_REUSABLE, MAPPLET_ALL_TRANSFORMS.IS_SHORTCUT AS IS_SHORTCUT FROM REP_ALL_MAPPLETS ALL_MAPPLETS, REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_TRANSFORMS MAPPLET_ALL_TRANSFORMS WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPLET_WIDGET_INST.WIDGET_ID AND MAPPLET_ALL_TRANSFORMS.SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND MAPPLET_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPLET_WIDGET_INST.WIDGET_TYPE )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPLET_NAME” in (‘<Mapplet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mapplets defined in a folder but not used in any mapping in that folder.
select “REPOSITORY_NAME”,
case when (“MAPPLET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPLET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPLET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPLET_VERSION_STATUS”,
“MAPPLET_DESCRIPTION”,
“MAPPLET_NAME”,
“MAPPLET_VERSION_NUMBER”,
“MAPPLET_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
case when (“SHORTCUTFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”
from (SELECT DISTINCT MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION AS MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS, ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION AS PARENT_MAPPLET_DESCRIPTION, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID as VALIDFLAG, ALL_MAPPLETS.IS_SHORTCUT as SHORTCUTFLAG, (COUNT(DISTINCT( MAPPLET_MAPPING_WIDGET_INST.MAPPING_ID))) AS MAPPING_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE = 1 THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS SOURCE_COUNT, (COUNT( CASE WHEN MAPPLET_WIDGET_INST.WIDGET_TYPE NOT IN (1, 2, 44) THEN MAPPLET_WIDGET_INST.INSTANCE_ID END)) AS TRANSFORMATION_COUNT FROM REP_REPOSIT_INFO MAPPLET_REPOSIT_INFO, REP_WIDGET_INST MAPPLET_WIDGET_INST, REP_ALL_MAPPLETS ALL_MAPPLETS LEFT OUTER JOIN REP_WIDGET_INST MAPPLET_MAPPING_WIDGET_INST ON ALL_MAPPLETS.REF_WIDGET_ID = MAPPLET_MAPPING_WIDGET_INST.WIDGET_ID AND ALL_MAPPLETS.SUBJECT_ID = MAPPLET_MAPPING_WIDGET_INST.SUBJECT_ID AND MAPPLET_MAPPING_WIDGET_INST.WIDGET_TYPE = 44 WHERE (ALL_MAPPLETS.PARENT_MAPPLET_ID = MAPPLET_WIDGET_INST.MAPPING_ID AND ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER = MAPPLET_WIDGET_INST. VERSION_NUMBER AND ALL_MAPPLETS.PARENT_SUBJECT_ID = MAPPLET_WIDGET_INST.SUBJECT_ID AND ALL_MAPPLETS.PARENT_MAPPLET_ID <> MAPPLET_REPOSIT_INFO.REPOSITORY_ID) GROUP BY MAPPLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_MAPPLETS.SUBJECT_AREA, ALL_MAPPLETS.MAPPLET_NAME, ALL_MAPPLETS.MAPPLET_DESCRIPTION, ALL_MAPPLETS.MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.MAPPLET_VERSION_STATUS , ALL_MAPPLETS.MAPPLET_LAST_SAVED, ALL_MAPPLETS.PARENT_MAPPLET_NAME, ALL_MAPPLETS.PARENT_MAPPLET_DESCRIPTION , ALL_MAPPLETS.PARENT_MAPPLET_VERSION_NUMBER, ALL_MAPPLETS.PARENT_MAPPLET_VERSION_STATUS, ALL_MAPPLETS.PARENT_MAPPLET_LAST_SAVED, ALL_MAPPLETS.IS_SHORTCUT , ALL_MAPPLETS.PARENT_SUBJECT_AREA, ALL_MAPPLETS.PARENT_MAPPLET_IS_VALID) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “MAPPING_COUNT” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Sessions
Displays invalid sessions by repository by folder.
select case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ReusableFlag”,
“SESSION_NAME”,
“REPOSITORY_NAME”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“COMMENTS”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”,
“MAPPING_VERSION_NUMBER”,
“SESSION_VERSION_NUMBER”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘No’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays, by repository by folder, components included in a workflow, such as pre- and post-session commands and failure and success emails.
select “VAL_NAME”,
“PM_VALUE”,
“SESSION_NAME”,
case when (“OBJECT_TYPE” = 58) then (‘Command’) else (case when (“OBJECT_TYPE” = 65) then (‘Email’) else (”) end) end as “Calc_OBJECT_TYPE”,
“SUBJECT_AREA”,
“DESCRIPTION”,
“REPOSITORY_NAME”
from (SELECT DISTINCT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_COMPONENT.OBJECT_TYPE as OBJECT_TYPE, SESSION_COMPONENT.VAL_NAME, SESSION_COMPONENT.DESCRIPTION as DESCRIPTION, SESSION_COMPONENT.PM_VALUE as PM_VALUE FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_COMPONENT SESSION_COMPONENT WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_COMPONENT.TASK_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_COMPONENT.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays the configurable attributes and their selected values for each session by repository by folder
select “ATTR_VALUE”,
“SUBJECT_AREA”,
“ATTR_NAME”,
“SESSION_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_CONFIG_PARM.ATTR_NAME, SESSION_CONFIG_PARM.ATTR_VALUE AS ATTR_VALUE FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_SESS_CONFIG_PARM SESSION_CONFIG_PARM WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND ALL_SESSIONS.SESSION_ID = SESSION_CONFIG_PARM.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_CONFIG_PARM. SESSION_VERSION_NUMBER )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session-Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all sessions and their properties by repository by folder.
select case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ReusableFlag”,
“SESSION_NAME”,
“REPOSITORY_NAME”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_ValidFlag”,
“COMMENTS”,
“MAPPING_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”,
“MAPPING_VERSION_NUMBER”,
“SESSION_VERSION_NUMBER”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays transformation instances and the transformation type for a partition by repository by folder.
select “SUBJECT_AREA”,
“REPOSITORY_NAME”,
“INSTANCE_NAME”,
“SESSION_NAME”,
“PARTITION_ID”,
“TYPE_NAME”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_PARTITION_DEF.TYPE_NAME, SESSION_PARTITION_DEF.INSTANCE_NAME, (COUNT(SESSION_PARTITION_DEF.PARTITION_ID)) as PARTITION_ID FROM REP_LOAD_SESSIONS ALL_SESSIONS, REP_SESS_PARTITION_DEF SESSION_PARTITION_DEF, REP_REPOSIT_INFO SESSION_REPOSIT_INFO WHERE (ALL_SESSIONS.SESSION_ID = SESSION_PARTITION_DEF.SESSION_ID AND ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_PARTITION_DEF.VERSION_NUMBER AND ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, SESSION_PARTITION_DEF.TYPE_NAME, SESSION_PARTITION_DEF.INSTANCE_NAME) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SESSION_NAME” in (‘<Session_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sessions that are not associated with any workflow by repository by folder
select “SESSION_NAME”,
“REPOSITORY_NAME”,
“MAPPING_NAME”,
“WORKFLOWID”,
“SUBJECT_AREA”
from (SELECT SESSION_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA AS SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME AS SESSION_NAME, ALL_SESSIONS.MAPPING_NAME, (COUNT(DISTINCT( SESSION_TASK_INST.WORKFLOW_ID))) AS WORKFLOWID, ALL_SESSIONS.COMMENTS AS COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER AS SESSION_VERSION_NUMBER, ALL_SESSIONS.MAPPING_VERSION_NUMBER AS MAPPING_VERSION_NUMBER, ALL_SESSIONS.IS_VALID AS VALIDFLAG, ALL_SESSIONS.IS_REUSABLE AS REUSABLEFLAG, ALL_SESSIONS.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO SESSION_REPOSIT_INFO, REP_LOAD_SESSIONS ALL_SESSIONS LEFT OUTER JOIN REP_TASK_INST SESSION_TASK_INST ON ALL_SESSIONS.SESSION_ID = SESSION_TASK_INST.TASK_ID AND SESSION_TASK_INST.TASK_TYPE = 68 WHERE (ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SESSION_REPOSIT_INFO.REPOSITORY_NAME, ALL_SESSIONS.SUBJECT_AREA, ALL_SESSIONS.SESSION_NAME, ALL_SESSIONS.MAPPING_NAME , ALL_SESSIONS.COMMENTS, ALL_SESSIONS.SESSION_VERSION_NUMBER , ALL_SESSIONS.MAPPING_VERSION_NUMBER , ALL_SESSIONS.IS_VALID , ALL_SESSIONS.IS_REUSABLE , ALL_SESSIONS.LAST_SAVED) “query”
where “SUBJECT_AREA” in (<Folder_Name>’) and “WORKFLOWID” = 0
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Sources
Displays relational and non-relational sources by repository and folder. It also shows the source properties.
select “REF_WIDGET_ID_GREATER”,
“PARENT_SOURCE_BUSINESS_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“REF_WIDGET_ID”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“SUBJECT_AREA”,
“SOURCE_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SOURCE_NAME” in (‘<Source_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings that use a particular source by repository and folder.
select “MAPPING_DESCRIPTION”,
“REPOSITORY_NAME”,
“MAPPING_VERSION_NUMBER”,
case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
“SUBJECT_AREA”,
“MAPPING_NAME”,
“SOURCE_NAME”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘LogicallyDeleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (‘NULL’) end) end) end as “Calc_MAPPING_VERSION_STATUS”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_NAME, SRC_ALL_MAPPINGS.MAPPING_NAME, SRC_ALL_MAPPINGS.MAPPING_DESCRIPTION as MAPPING_DESCRIPTION, SRC_ALL_MAPPINGS.MAPPING_VERSION_NUMBER, SRC_ALL_MAPPINGS.MAPPING_VERSION_STATUS , SRC_ALL_MAPPINGS.PARENT_MAPPING_IS_VALID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_MAPPINGS SRC_ALL_MAPPINGS, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID AND SRC_WIDGET_INST.MAPPING_ID = SRC_ALL_MAPPINGS.PARENT_MAPPING_ID AND SRC_WIDGET_INST.VERSION_NUMBER = SRC_ALL_MAPPINGS. PARENT_MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “SOURCE_NAME” in (‘<Source_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sources that are defined as shortcuts by repository and folder.
select “PARENT_SUBJECT_AREA”,
“PARENT_SOURCE_DATABASE_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
case when (“PARENT_SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_SOURCE_VERSION_STA”,
“SUBJECT_AREA”,
“PARENT_SOURCE_VERSION_NUMBER”,
“PARENT_SOURCE_DESCRIPTION”,
“SOURCE_NAME”,
“PARENT_SOURCE_LAST_SAVED”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”,
“PARENT_SOURCE_NAME”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end = ‘yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays sources that are not used in any mapping or mapplet by repository and folder.
select “SUBJECT_AREA”,
“SOURCE_NAME”,
“PARENT_SOURCE_DATABASE_TYPE”,
“SOURCE_VERSION_NUMBER”,
“PARENT_SOURCE_BUSINESS_NAME”,
“REPOSITORY_NAME”,
“SOURCE_LAST_SAVED”,
“SOURCE_DATABASE_NAME”,
“SOURCE_DESCRIPTION”,
case when (“SOURCE_VERSION_STATUS” = 0) then (‘Active’) else (case when (“SOURCE_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“SOURCE_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_SOURCE_VERSION_STATUS”,
“MAPPING_ID”,
case when (“IS_SHORTCUT” = 1) then (‘yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION AS SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS AS SOURCE_VERSION_STATUS, ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION AS PARENT_SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS AS PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT AS IS_SHORTCUT, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID > 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID_GREATER, (COUNT(DISTINCT( CASE WHEN SRC_WIDGET_INST.REF_WIDGET_ID = 0 THEN SRC_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID , (COUNT(DISTINCT(SRC_WIDGET_INST.MAPPING_ID))) AS MAPPING_ID FROM REP_REPOSIT_INFO SRC_REPOSIT_INFO, REP_ALL_SOURCES ALL_SOURCES LEFT OUTER JOIN REP_WIDGET_INST SRC_WIDGET_INST ON ALL_SOURCES.PARENT_SOURCE_ID = SRC_WIDGET_INST.WIDGET_ID AND ALL_SOURCES.SUBJECT_ID = SRC_WIDGET_INST.SUBJECT_ID AND SRC_WIDGET_INST.WIDGET_TYPE = 1 WHERE (ALL_SOURCES.PARENT_SOURCE_ID <> SRC_REPOSIT_INFO.REPOSITORY_ID) GROUP BY SRC_REPOSIT_INFO.REPOSITORY_NAME, ALL_SOURCES.SUBJECT_AREA, ALL_SOURCES.SOURCE_DATABASE_NAME, ALL_SOURCES.SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_BUSINESS_NAME, ALL_SOURCES.SOURCE_DESCRIPTION, ALL_SOURCES.PARENT_SOURCE_DATABASE_TYPE, ALL_SOURCES.SOURCE_VERSION_NUMBER, ALL_SOURCES.SOURCE_VERSION_STATUS , ALL_SOURCES.SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_NAME, ALL_SOURCES.PARENT_SOURCE_DESCRIPTION , ALL_SOURCES.PARENT_SOURCE_VERSION_NUMBER, ALL_SOURCES.PARENT_SOURCE_VERSION_STATUS, ALL_SOURCES.PARENT_SOURCE_LAST_SAVED, ALL_SOURCES.PARENT_SOURCE_DATABASE_NAME, ALL_SOURCES.PARENT_SUBJECT_AREA, ALL_SOURCES.IS_SHORTCUT) “query”
where “MAPPING_ID” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”, “SOURCE_DATABASE_NAME”, “SOURCE_NAME”, “PARENT_SOURCE_BUSINESS_NAME”
Targets
Displays relational and non-relational targets available by repository and folder. It also displays the target properties.
select “SUBJECT_AREA”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“REF_WIDGET_ID2”,
“REF_WIDGET_ID1”,
“TARGET_NAME”,
“TARGET_LAST_SAVED”,
“REPOSITORY_NAME”,
“TARGET_DESCRIPTION”,
“PARENT_TARGET_BUSINESS_NAME”,
“PARENT_TARGET_DATABASE_TYPE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TARGET_NAME” in (‘<Target_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays mappings that use a particular target by repository and folder.
select case when (“PARENT_MAPPING_IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_PARENT_MAPPING_IS_VALID”,
“REPOSITORY_NAME”,
“DESCRPTION”,
“MAPPING_NAME”,
“MAPPING_VERSION_NUMBER”,
case when (“MAPPING_VERSION_STATUS” = 0) then (‘Active’) else (case when (“MAPPING_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“MAPPING_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_MAPPING_VERSION_STATUS”,
“SUBJECT_AREA”,
“TARGET_NAME”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA as SUBJECT_AREA, ALL_TARGETS.TARGET_NAME as TARGET_NAME, TARG_ALL_MAPPINGS.MAPPING_NAME as MAPPING_NAME, TARG_ALL_MAPPINGS.MAPPING_DESCRIPTION as DESCRPTION, TARG_ALL_MAPPINGS.MAPPING_VERSION_NUMBER as MAPPING_VERSION_NUMBER, TARG_ALL_MAPPINGS.MAPPING_VERSION_STATUS as MAPPING_VERSION_STATUS, TARG_ALL_MAPPINGS.PARENT_MAPPING_IS_VALID as PARENT_MAPPING_IS_VALID FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_MAPPINGS TARG_ALL_MAPPINGS, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID AND TARG_WIDGET_INST.MAPPING_ID = TARG_ALL_MAPPINGS.PARENT_MAPPING_ID AND TARG_WIDGET_INST.VERSION_NUMBER = TARG_ALL_MAPPINGS. PARENT_MAPPING_VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TARGET_NAME” in (‘<Target_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets that are defined as shortcuts by repository and folder.
select “SUBJECT_AREA”,
“PARENT_TARGET_DESCRIPTION”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“TARGET_LAST_SAVED”,
case when (“PARENT_TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_TARGET_VERSION_STA”,
“TARGET_DESCRIPTION”,
“PARENT_TARGET_NAME”,
“TARGET_VERSION_NUMBER”,
“PARENT_TARGET_VERSION_NUMBER”,
“TARGET_NAME”,
“REPOSITORY_NAME”,
“PARENT_SUBJECT_AREA”,
“PARENT_TARGET_LAST_SAVED”,
“PARENT_TARGET_DATABASE_TYPE”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays targets that are not used in any mapping by repository and folder.
select “SUBJECT_AREA”,
case when (“TARGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“TARGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“TARGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_TARGET_VERSION_STATUS”,
“TARGET_NAME”,
“TARGET_LAST_SAVED”,
“REPOSITORY_NAME”,
“TARGET_DESCRIPTION”,
“MAPPING_COUNT”,
“PARENT_TARGET_DATABASE_TYPE”,
“PARENT_TARGET_BUSINESS_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”,
“TARGET_VERSION_NUMBER”
from (SELECT DISTINCT TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION AS TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS as TARGET_VERSION_STATUS, ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION AS PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS as PARENT_TARGET_VERSION_STATUS, ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT as IS_SHORTCUT, ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME as PARENT_TARGET_BUSINESS_NAME, (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS MAPPING_COUNT , (COUNT(DISTINCT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.MAPPING_ID END))) AS REF_WIDGET_ID1, (COUNT( CASE WHEN TARG_WIDGET_INST.REF_WIDGET_ID = 0 THEN TARG_WIDGET_INST.INSTANCE_ID END)) AS REF_WIDGET_ID2 FROM REP_REPOSIT_INFO TARG_REPOSIT_INFO, REP_ALL_TARGETS ALL_TARGETS LEFT OUTER JOIN REP_WIDGET_INST TARG_WIDGET_INST ON ALL_TARGETS.PARENT_TARGET_ID = TARG_WIDGET_INST.WIDGET_ID AND ALL_TARGETS.SUBJECT_ID = TARG_WIDGET_INST.SUBJECT_ID AND TARG_WIDGET_INST.WIDGET_TYPE = 2 WHERE (ALL_TARGETS.PARENT_TARGET_ID <> TARG_REPOSIT_INFO.REPOSITORY_ID) GROUP BY TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA , ALL_TARGETS.TARGET_NAME , ALL_TARGETS.PARENT_TARGET_BUSINESS_NAME , ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE , ALL_TARGETS.TARGET_VERSION_NUMBER , ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED , ALL_TARGETS.IS_SHORTCUT, TARG_REPOSIT_INFO.REPOSITORY_NAME, ALL_TARGETS.SUBJECT_AREA, ALL_TARGETS.TARGET_NAME, ALL_TARGETS.TARGET_DESCRIPTION , ALL_TARGETS.PARENT_TARGET_DATABASE_TYPE, ALL_TARGETS.TARGET_VERSION_NUMBER, ALL_TARGETS.TARGET_VERSION_STATUS , ALL_TARGETS.TARGET_LAST_SAVED, ALL_TARGETS.PARENT_TARGET_NAME, ALL_TARGETS.PARENT_TARGET_DESCRIPTION, ALL_TARGETS.PARENT_TARGET_VERSION_NUMBER, ALL_TARGETS.PARENT_TARGET_VERSION_STATUS , ALL_TARGETS.PARENT_TARGET_LAST_SAVED, ALL_TARGETS.PARENT_SUBJECT_AREA, ALL_TARGETS.IS_SHORTCUT) “query”
where “MAPPING_COUNT” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Transformations
Displays transformations that are defined as shortcuts by repository and folder.
select “WIDGET_LAST_SAVED”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“REPOSITORY_NAME”,
“PARENT_SUBJECT_AREA”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“PARENT_WIDGET_DESCRIPTION”,
“PARENT_WIDGET_NAME”,
case when (“PARENT_WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“PARENT_WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“PARENT_WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_PARENT_WIDGET_VERSION_STA”,
“WIDGET_VERSION_NUMBER”,
“PARENT_WIDGET_LAST_SAVED”,
“WIDGET_DESCRIPTION”,
“PARENT_WIDGET_VERSION_NUMBER”,
“WIDGET_TYPE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS as WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION AS PARENT_WIDGET_DESCRIPTION, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS as PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, ALL_TRANSFORMS.IS_SHORTCUT as IS_SHORTCUT , (COUNT(DISTINCT(TRANSFORM_WIDGET_INST.MAPPING_ID))) as MAPPING_ID FROM REP_REPOSIT_INFO TRANSFORM_REPOSIT_INFO, REP_ALL_TRANSFORMS ALL_TRANSFORMS LEFT OUTER JOIN REP_WIDGET_INST TRANSFORM_WIDGET_INST ON ALL_TRANSFORMS.PARENT_WIDGET_ID = TRANSFORM_WIDGET_INST.WIDGET_ID AND ALL_TRANSFORMS.SUBJECT_ID = TRANSFORM_WIDGET_INST.SUBJECT_ID AND ALL_TRANSFORMS.WIDGET_TYPE_ID = TRANSFORM_WIDGET_INST.WIDGET_TYPE AND (TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 1 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 2 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 44 ) AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1 WHERE (ALL_TRANSFORMS.PARENT_WIDGET_ID <> TRANSFORM_REPOSIT_INFO.REPOSITORY_ID AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1) GROUP BY TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.IS_SHORTCUT , ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION , ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS , PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME) “query”
where case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays transformations that are not used in any mapping or mapplet by repository and folder.
select “WIDGET_LAST_SAVED”,
case when (“WIDGET_VERSION_STATUS” = 0) then (‘Active’) else (case when (“WIDGET_VERSION_STATUS” = 9) then (‘Logically Deleted’) else (case when (“WIDGET_VERSION_STATUS” = 10) then (‘Physically Deleted’) else (”) end) end) end as “Calc_WIDGET_VERSION_STATUS”,
“WIDGET_VERSION_NUMBER”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“WIDGET_NAME”,
“WIDGET_DESCRIPTION”,
“MAPPING_ID”,
“WIDGET_TYPE_NAME”,
case when (“IS_SHORTCUT” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_SHORTCUT”
from (SELECT DISTINCT TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION AS WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS as WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION AS PARENT_WIDGET_DESCRIPTION, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS as PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, ALL_TRANSFORMS.IS_SHORTCUT as IS_SHORTCUT , (COUNT(DISTINCT(TRANSFORM_WIDGET_INST.MAPPING_ID))) as MAPPING_ID FROM REP_REPOSIT_INFO TRANSFORM_REPOSIT_INFO, REP_ALL_TRANSFORMS ALL_TRANSFORMS LEFT OUTER JOIN REP_WIDGET_INST TRANSFORM_WIDGET_INST ON ALL_TRANSFORMS.PARENT_WIDGET_ID = TRANSFORM_WIDGET_INST.WIDGET_ID AND ALL_TRANSFORMS.SUBJECT_ID = TRANSFORM_WIDGET_INST.SUBJECT_ID AND ALL_TRANSFORMS.WIDGET_TYPE_ID = TRANSFORM_WIDGET_INST.WIDGET_TYPE AND (TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 1 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 2 AND TRANSFORM_WIDGET_INST.WIDGET_TYPE <> 44 ) AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1 WHERE (ALL_TRANSFORMS.PARENT_WIDGET_ID <> TRANSFORM_REPOSIT_INFO.REPOSITORY_ID AND ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE = 1) GROUP BY TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME, ALL_TRANSFORMS.SUBJECT_AREA, ALL_TRANSFORMS.WIDGET_TYPE_NAME, ALL_TRANSFORMS.WIDGET_NAME, ALL_TRANSFORMS.WIDGET_DESCRIPTION, ALL_TRANSFORMS.WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.WIDGET_VERSION_STATUS, ALL_TRANSFORMS.WIDGET_LAST_SAVED, ALL_TRANSFORMS.IS_SHORTCUT , ALL_TRANSFORMS.PARENT_WIDGET_NAME, ALL_TRANSFORMS.PARENT_WIDGET_DESCRIPTION , ALL_TRANSFORMS.PARENT_WIDGET_VERSION_NUMBER, ALL_TRANSFORMS.PARENT_WIDGET_VERSION_STATUS , PARENT_WIDGET_VERSION_STATUS, ALL_TRANSFORMS.PARENT_WIDGET_LAST_SAVED, ALL_TRANSFORMS.PARENT_SUBJECT_AREA, TRANSFORM_REPOSIT_INFO.REPOSITORY_NAME) “query”
where “MAPPING_ID” = 0 and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Workflows
Displays all the reusable schedulers defined in the repository and their description and properties by repository by folder.
select case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_REUSABLE_FLAG”,
“RUN_COUNT”,
“END_TIME”,
case when (“RUN_OPTIONS” <= 9) then (case when (“RUN_OPTIONS” = 1) then (‘Run On Demand’) else (case when (“RUN_OPTIONS” = 2) then (‘Run Once Schedule’) else (case when (“RUN_OPTIONS” = 3) then (‘Run On Demand and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 5) then (‘Run On Demand and Delta Schedule’) else (case when (“RUN_OPTIONS” = 8) then (‘Custom Repeat’) else (case when (“RUN_OPTIONS” = 9) then (‘Run On Demand and Custom Repeat’) else (”) end) end) end) end) end) end) else (case when (“RUN_OPTIONS” = 18) then (‘Run On Server Init and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 20) then (‘Run On Server Init and Delta Schedule’) else (case when (“RUN_OPTIONS” = 24) then (‘Run On Server Init and Custom Repeat’) else (case when (“RUN_OPTIONS” = 34) then (‘Run Continuously and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 36) then (‘Run Continuously and Delta Schedule’) else (case when (“RUN_OPTIONS” = 40) then (‘Run Continuously and Custom Repeat’) else (”) end) end) end) end) end) end) end as “Calc_RUN_OPTIONS”,
“VERSION_NUMBER”,
case when (“END_OPTIONS” = 0) then (‘End On Date’) else (case when (“END_OPTIONS” = 1) then (‘End After Count’) else (case when (“END_OPTIONS” = 2) then (‘Run Forever’) else (”) end) end) end as “Calc_END_OPTIONS”,
“COUNT_WORKFLOW_ID”,
“SCHEDULER_NAME”,
“DESCRIPTION”,
“START_TIME”,
“DELTA_VALUE”,
“REPOSITORY_NAME”,
“SUBJECT_AREA”,
“LAST_SAVED”
from (SELECT WORKFLOW_SCHEDULER_REPOSITORY.REPOSITORY_NAME AS REPOSITORY_NAME, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS SUBJECT_AREA, WORKFLOW_SCHEDULERS.SCHEDULER_NAME AS SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS DESCRIPTION, WORKFLOW_SCHEDULERS.VERSION_NUMBER AS VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED AS LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME AS START_TIME, WORKFLOW_SCHEDULERS.END_TIME AS END_TIME, WORKFLOW_SCHEDULERS.RUN_OPTIONS AS RUN_OPTIONS, WORKFLOW_SCHEDULERS.END_OPTIONS AS END_OPTIONS, WORKFLOW_SCHEDULERS.RUN_COUNT AS RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE AS DELTA_VALUE, WORKFLOW_SCHEDULERS.IS_REUSABLE AS IS_REUSABLE, (COUNT(WORKFLOW_SCHEDULER_ASSOCIATION.WORKFLOW_ID)) AS COUNT_WORKFLOW_ID FROM REP_REPOSIT_INFO WORKFLOW_SCHEDULER_REPOSITORY, REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS LEFT OUTER JOIN REP_WORKFLOWS WORKFLOW_SCHEDULER_ASSOCIATION ON WORKFLOW_SCHEDULERS.SCHEDULER_ID = WORKFLOW_SCHEDULER_ASSOCIATION. SCHEDULER_ID AND WORKFLOW_SCHEDULERS.VERSION_NUMBER = WORKFLOW_SCHEDULER_ASSOCIATION. SCHEDULER_VERSION_NUMBER WHERE (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_SCHEDULER_REPOSITORY. REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID) AND ( WORKFLOW_SCHEDULERS.IS_REUSABLE = 1 ) GROUP BY WORKFLOW_SCHEDULER_REPOSITORY.REPOSITORY_NAME, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.RUN_OPTIONS, WORKFLOW_SCHEDULERS.END_OPTIONS, WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, WORKFLOW_SCHEDULERS.IS_REUSABLE) “query”
where case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all workflows using a given reusable scheduler by repository by folder.
select “SCHEDULER_NAME”,
“SUBJECT_AREA”,
“WORKFLOW_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end = ‘Yes’
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflow events and its properties by repository by folder.
select case when (“EVENT_TYPE” = 0) then (‘Built In’) else (case when (“EVENT_TYPE” = 1) then (‘User Defined’) else (‘NULL’) end) end as “Calc_EVENT_TYPE”,
“LAST_SAVED”,
“REPOSITORY_NAME”,
“EVENT_DESCRIPTION”,
“SUBJECT_AREA”,
“WORKFLOW_NAME”,
“EVENT_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_EVENT.EVENT_TYPE , WORKFLOW_EVENT.EVENT_NAME, WORKFLOW_EVENT.EVENT_DESCRIPTION AS EVENT_DESCRIPTION, WORKFLOW_EVENT.LAST_SAVED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_EVENT WORKFLOW_EVENT WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_EVENT.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_EVENT.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_EVENT.SUBJECT_ID )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflows and workflow properties by repository by folder. This is a primary report in an analytic workflow.
select “WORKFLOW_COMMENTS”,
“SUBJECT_AREA”,
“SERVER_NAME”,
“WORKFLOW_NAME”,
“WORKFLOW_LAST_SAVED”,
case when (“VALIDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_WORKFLOW_IS_VALID”,
“WORKFLOWLIST_SCHEDULER”,
“REPOSITORY_NAME”,
case when (“ENABLEDFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“WORKFLOW_VERSION_NUMBER”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “TASK_TYPE” = 71 and “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays, by repository by folder, scheduler settings for a workflow and properties of the scheduler, such as reusable flag and run on demand .This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “RUN_COUNT”,
“SCHEDULER_NAME”,
case when (“END_OPTIONS” = 0) then (‘End On Date’) else (case when (“END_OPTIONS” = 1) then (‘End After Count’) else (case when (“END_OPTIONS” = 2) then (‘Run Forever’) else (”) end) end) end as “Calc_END_OPTIONS”,
“WSD_SUBJECT_AREA”,
case when (“REUSABLEFLAG” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”,
case when (“RUN_OPTIONS” <= 9) then (case when (“RUN_OPTIONS” = 1) then (‘Run On Demand’) else (case when (“RUN_OPTIONS” = 2) then (‘Run Once Schedule’) else (case when (“RUN_OPTIONS” = 3) then (‘Run On Demand and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 5) then (‘Run On Demand and Delta Schedule’) else (case when (“RUN_OPTIONS” = 8) then (‘Custom Repeat’) else (case when (“RUN_OPTIONS” = 9) then (‘Run On Demand and Custom Repeat’) else (null) end) end) end) end) end) end) else (case when (“RUN_OPTIONS” = 18) then (‘Run On Server Init and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 20) then (‘Run On Server Init and Delta Schedule’) else (case when (“RUN_OPTIONS” = 24) then (‘Run On Server Init and Custom Repeat’) else (case when (“RUN_OPTIONS” = 34) then (‘Run Continuously and Run Once Schedule’) else (case when (“RUN_OPTIONS” = 36) then (‘Run Continuously and Delta Schedule’) else (case when (“RUN_OPTIONS” = 40) then (‘Run Continuously and Custom Repeat’) else (null) end) end) end) end) end) end) end as “Calc_Run_Options”,
“WSD_DESC”,
“LAST_SAVED”,
“START_TIME”,
“WORKFLOW_NAME”,
“END_TIME”,
“VERSION_NUMBER”,
“DELTA_VALUE”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME, WORKFLOW_SCHEDULERS.SCHEDULER_NAME, WORKFLOW_SCHEDULERS.DESCRIPTION AS WSD_DESC, WORKFLOW_SCHEDULERS.VERSION_NUMBER, WORKFLOW_SCHEDULERS.LAST_SAVED, WORKFLOW_SCHEDULERS.START_TIME, WORKFLOW_SCHEDULERS.END_TIME, WORKFLOW_SCHEDULERS.IS_REUSABLE as REUSABLEFLAG, WORKFLOW_SCHEDULERS.RUN_OPTIONS , WORKFLOW_SCHEDULERS.END_OPTIONS , WORKFLOW_SCHEDULERS.RUN_COUNT, WORKFLOW_SCHEDULERS.DELTA_VALUE, ALL_WORKFLOWS.SERVER_NAME, ALL_WORKFLOWS.SCHEDULER_NAME WORKFLOWLIST_SCHEDULER, ALL_WORKFLOWS.WORKFLOW_COMMENTS AS WORKFLOW_COMMENTS, ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER, ALL_WORKFLOWS.WORKFLOW_IS_VALID as VALIDFLAG, WORKFLOW_ALL_TASKS.IS_ENABLED as ENABLEDFLAG, ALL_WORKFLOWS.WORKFLOW_LAST_SAVED, WORKFLOW_ALL_TASKS.TASK_TYPE, WORKFLOW_SCHEDULER_FOLDER.SUBJECT_AREA AS WSD_SUBJECT_AREA FROM REP_WORKFLOWS ALL_WORKFLOWS, REP_ALL_SCHEDULERS WORKFLOW_SCHEDULERS, REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_ALL_TASKS WORKFLOW_ALL_TASKS , REP_SUBJECT WORKFLOW_SCHEDULER_FOLDER WHERE (ALL_WORKFLOWS.SCHEDULER_ID = WORKFLOW_SCHEDULERS.SCHEDULER_ID AND ALL_WORKFLOWS.SCHEDULER_VERSION_NUMBER = WORKFLOW_SCHEDULERS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_SCHEDULERS.SUBJECT_ID AND ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_ALL_TASKS.TASK_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_ALL_TASKS.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_ALL_TASKS.SUBJECT_ID) AND (WORKFLOW_SCHEDULERS.SCHEDULER_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND WORKFLOW_SCHEDULERS.SUBJECT_ID = WORKFLOW_SCHEDULER_FOLDER.SUBJECT_ID)) “query”
where “WSD_SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name’)
order by “REPOSITORY_NAME”, “WSD_SUBJECT_AREA”
Displays all tasks created in a workflow by repository by folder. This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “SUBJECT_AREA”,
case when (“IS_ENABLED” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“WORKFLOW_NAME”,
“DESCRIPTION”,
“NSTANCE_NAME”,
“TASK_TYPE_NAME”,
“REPOSITORY_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME as REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA as SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME as WORKFLOW_NAME, WORKFLOW_TASK_INST.TASK_TYPE_NAME as TASK_TYPE_NAME, WORKFLOW_TASK_INST.INSTANCE_NAME as NSTANCE_NAME, WORKFLOW_TASK_INST.DESCRIPTION as DESCRIPTION, WORKFLOW_TASK_INST.IS_ENABLED as IS_ENABLED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_TASK_INST WORKFLOW_TASK_INST WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_TASK_INST.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_TASK_INST.VERSION_NUMBER)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays workflow variables and their properties by repository by folder. This report is the first node in the analytic workflow associated with the Workflow List primary report.
select “SUBJECT_AREA”,
“VARIABLE_DEFAULT_VALUE”,
case when (“VARIABLE_TYPE” = 0) then (‘Built In’) else (case when (“VARIABLE_TYPE” = 1) then (‘User Defined’) else (”) end) end as “Calc_VARIABLE_TYPE”,
“WORKFLOW_NAME”,
“LAST_SAVED”,
“DESCRIPTION”,
case when (“VARIABLE_DATATYPE” <= 8) then (case when (“VARIABLE_DATATYPE” = -1) then (‘Text’) else (case when (“VARIABLE_DATATYPE” = -2) then (‘Binary’) else (case when (“VARIABLE_DATATYPE” = -9) then (‘nString’) else (case when (“VARIABLE_DATATYPE” = -10) then (‘nText’) else (case when (“VARIABLE_DATATYPE” = 3) then (‘Decimal’) else (case when (“VARIABLE_DATATYPE” = 4) then (‘Integer’) else (case when (“VARIABLE_DATATYPE” = 5) then (‘Small Integer’) else (case when (“VARIABLE_DATATYPE” = 7) then (‘Real’) else (case when (“VARIABLE_DATATYPE” = 8) then (‘Double’) else (”) end) end) end) end) end) end) end) end) end) else (case when (“VARIABLE_DATATYPE” = 11) then (‘Date/Time’) else (case when (“VARIABLE_DATATYPE” = 12) then (‘String’) else (”) end) end) end as “Calc_VARIABLE_DATATYPE”,
“REPOSITORY_NAME”,
“VARIABLE_NAME”
from (SELECT DISTINCT WORKFLOW_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKFLOWS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKFLOWS.WORKFLOW_NAME AS WORKFLOW_NAME, WORKFLOW_VAR.VARIABLE_TYPE AS VARIABLE_TYPE, WORKFLOW_VAR.VARIABLE_NAME AS VARIABLE_NAME, WORKFLOW_VAR.VARIABLE_DESCRIPTION AS DESCRIPTION, WORKFLOW_VAR.VARIABLE_DATATYPE as VARIABLE_DATATYPE, WORKFLOW_VAR.VARIABLE_DEFAULT_VALUE AS VARIABLE_DEFAULT_VALUE, WORKFLOW_VAR.LAST_SAVED AS LAST_SAVED FROM REP_REPOSIT_INFO WORKFLOW_REPOSIT_INFO, REP_WORKFLOWS ALL_WORKFLOWS, REP_WFLOW_VAR WORKFLOW_VAR WHERE (ALL_WORKFLOWS.WORKFLOW_ID <> WORKFLOW_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKFLOWS.WORKFLOW_ID = WORKFLOW_VAR.WORKFLOW_ID AND ALL_WORKFLOWS.WORKFLOW_VERSION_NUMBER = WORKFLOW_VAR.VERSION_NUMBER AND ALL_WORKFLOWS.SUBJECT_ID = WORKFLOW_VAR.SUBJECT_ID )) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “WORKFLOW_NAME” in (‘<Workflow_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Worklets
Displays all invalid worklets by repository by folder.
select case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_VALID”,
“DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”,
“VERSION_NUMBER”,
“REPOSITORY_NAME”,
“LAST_SAVED”,
case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKLETS.TASK_NAME AS TASK_NAME, ALL_WORKLETS.DESCRIPTION AS DESCRIPTION, ALL_WORKLETS.VERSION_NUMBER AS VERSION_NUMBER, ALL_WORKLETS.IS_VALID AS IS_VALID, ALL_WORKLETS.IS_REUSABLE AS IS_REUSABLE , ALL_WORKLETS.LAST_SAVED AS LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end = ‘No’ and “SUBJECT_AREA” in (‘<Folder_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays worklet events and its properties by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “SUBJECT_AREA”,
“LAST_SAVED”,
“TASK_NAME”,
case when (“EVENT_TYPE” = 0) then (‘Built In’) else (case when (“EVENT_TYPE” = 1) then (‘User Defined’) else (”) end) end as “Calc_EVENT_TYPE”,
“REPOSITORY_NAME”,
“EVENT_NAME”,
“EVENT_DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_EVENT.EVENT_TYPE as EVENT_TYPE, WORKLET_EVENT.EVENT_NAME, WORKLET_EVENT.EVENT_DESCRIPTION as EVENT_DESCRIPTION, WORKLET_EVENT.LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_EVENT WORKLET_EVENT WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID = WORKLET_EVENT.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_EVENT.VERSION_NUMBER AND ALL_WORKLETS.SUBJECT_ID = WORKLET_EVENT.SUBJECT_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays worklets and worklet properties by repository by folder. This is a primary report in an analytic workflow.
select case when (“IS_VALID” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_VALID”,
“DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”,
“VERSION_NUMBER”,
“REPOSITORY_NAME”,
“LAST_SAVED”,
case when (“IS_REUSABLE” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_REUSABLE”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA AS SUBJECT_AREA, ALL_WORKLETS.TASK_NAME AS TASK_NAME, ALL_WORKLETS.DESCRIPTION AS DESCRIPTION, ALL_WORKLETS.VERSION_NUMBER AS VERSION_NUMBER, ALL_WORKLETS.IS_VALID AS IS_VALID, ALL_WORKLETS.IS_REUSABLE AS IS_REUSABLE , ALL_WORKLETS.LAST_SAVED AS LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays how tasks are connected to each other in a worklet by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “TASK_NAME”,
“TASK_TYPE_NAME”,
“TO_TASK_TYPE_NAME”,
“TO_INSTANCE_NAME”,
“DESCRIPTION”,
“SUBJECT_AREA”,
“INSTANCE_NAME”,
“REPOSITORY_NAME”,
“CONDITION”,
“TO_DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_FROM_TASK_INST.INSTANCE_NAME , WORKLET_FROM_TASK_INST.TASK_TYPE_NAME, WORKLET_FROM_TASK_INST.DESCRIPTION as DESCRIPTION, WORKLET_TO_TASK_INST.INSTANCE_NAME as TO_INSTANCE_NAME, WORKLET_TO_TASK_INST.TASK_TYPE_NAME as TO_TASK_TYPE_NAME, WORKLET_TO_TASK_INST.DESCRIPTION as TO_DESCRIPTION, WORKLET_DEP.CONDITION as CONDITION FROM REP_ALL_TASKS ALL_WORKLETS, REP_WORKFLOW_DEP WORKLET_DEP, REP_TASK_INST WORKLET_TO_TASK_INST, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_TASK_INST WORKLET_FROM_TASK_INST WHERE (ALL_WORKLETS.TASK_ID = WORKLET_DEP.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_DEP.VERSION_NUMBER AND ALL_WORKLETS.TASK_TYPE = 70 AND WORKLET_DEP.WORKFLOW_ID = WORKLET_TO_TASK_INST.WORKFLOW_ID AND WORKLET_DEP.VERSION_NUMBER = WORKLET_TO_TASK_INST.VERSION_NUMBER AND WORKLET_DEP.TO_INSTANCE_ID = WORKLET_TO_TASK_INST.INSTANCE_ID AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND WORKLET_DEP.WORKFLOW_ID = WORKLET_FROM_TASK_INST.WORKFLOW_ID AND WORKLET_DEP.VERSION_NUMBER = WORKLET_FROM_TASK_INST.VERSION_NUMBER AND WORKLET_DEP.FROM_INSTANCE_ID = WORKLET_FROM_TASK_INST.INSTANCE_ID)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays all the tasks created in a worklet by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “REPOSITORY_NAME”,
“SUBJECT_AREA”,
“INSTANCE_NAME”,
“TASK_NAME”,
case when (“IS_ENABLED” = 1) then (‘Yes’) else (‘No’) end as “Calc_IS_ENABLED”,
“TASK_TYPE_NAME”,
“DESCRIPTION”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_TASK_INST.TASK_TYPE_NAME, WORKLET_TASK_INST.INSTANCE_NAME, WORKLET_TASK_INST.DESCRIPTION as DESCRIPTION, WORKLET_TASK_INST.IS_ENABLED FROM REP_ALL_TASKS ALL_WORKLETS, REP_TASK_INST WORKLET_TASK_INST, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO WHERE (ALL_WORKLETS.TASK_ID = WORKLET_TASK_INST.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_TASK_INST.VERSION_NUMBER AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
Displays variables defined for a worklet and its properties by repository by folder. This report is the first node in the analytic workflow associated with the Worklet List primary report.
select “VARIABLE_DEFAULT_VALUE”,
case when (“VARIABLE_DATATYPE” <= 8) then (case when (“VARIABLE_DATATYPE” = -1) then (‘Text’) else (case when (“VARIABLE_DATATYPE” = -2) then (‘Binary’) else (case when (“VARIABLE_DATATYPE” = -9) then (‘nString’) else (case when (“VARIABLE_DATATYPE” = -10) then (‘nText’) else (case when (“VARIABLE_DATATYPE” = 3) then (‘Decimal’) else (case when (“VARIABLE_DATATYPE” = 4) then (‘Integer’) else (case when (“VARIABLE_DATATYPE” = 5) then (‘Small Integer’) else (case when (“VARIABLE_DATATYPE” = 7) then (‘Real’) else (case when (“VARIABLE_DATATYPE” = 8) then (‘Double’) else (null) end) end) end) end) end) end) end) end) end) else (case when (“VARIABLE_DATATYPE” = 11) then (‘Date/Time’) else (case when (“VARIABLE_DATATYPE” = 12) then (‘String’) else (null) end) end) end as “Calc_VARIABLE_DATATYPE”,
“LAST_SAVED”,
“REPOSITORY_NAME”,
“VARIABLE_NAME”,
case when (“VARIABLE_TYPE” = 0) then (‘Built In’) else (case when (“VARIABLE_TYPE” = 1) then (‘User Defined’) else (‘NULL’) end) end as “Calc_VARIABLE_TYPE”,
“VARIABLE_DESCRIPTION”,
“TASK_NAME”,
“SUBJECT_AREA”
from (SELECT DISTINCT WORKLET_REPOSIT_INFO.REPOSITORY_NAME, ALL_WORKLETS.SUBJECT_AREA, ALL_WORKLETS.TASK_NAME, WORKLET_VAR.VARIABLE_TYPE , WORKLET_VAR.VARIABLE_NAME, WORKLET_VAR.VARIABLE_DESCRIPTION as VARIABLE_DESCRIPTION, WORKLET_VAR.VARIABLE_DATATYPE , WORKLET_VAR.VARIABLE_DEFAULT_VALUE as VARIABLE_DEFAULT_VALUE, WORKLET_VAR.LAST_SAVED FROM REP_ALL_TASKS ALL_WORKLETS, REP_REPOSIT_INFO WORKLET_REPOSIT_INFO, REP_WFLOW_VAR WORKLET_VAR WHERE (ALL_WORKLETS.TASK_ID <> WORKLET_REPOSIT_INFO.REPOSITORY_ID AND ALL_WORKLETS.TASK_TYPE = 70 AND ALL_WORKLETS.TASK_ID = WORKLET_VAR.WORKFLOW_ID AND ALL_WORKLETS.VERSION_NUMBER = WORKLET_VAR.VERSION_NUMBER AND ALL_WORKLETS.SUBJECT_ID = WORKLET_VAR.SUBJECT_ID AND ALL_WORKLETS.TASK_TYPE = 70)) “query”
where “SUBJECT_AREA” in (‘<Folder_Name>’) and “TASK_NAME” in (‘<Worklet_Name>’)
order by “REPOSITORY_NAME”, “SUBJECT_AREA”
I hope you will enjoy the content of my website.
Please leave me a comment below if you have any suggestion for improving