Sessions with Rejected Rows(Yesterday)
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.
Click on the text to copy the query report to your clipboard.
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"