Query to get the session information from the repository

Query to get the session information from the repository

1) use the following sql if u want just the times for the session or workflow
SELECT
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_WFLOW_RUN.WORKFLOW_NAME,
OPB_TASK_INST_RUN.SUBJECT_ID,
OPB_TASK_INST_RUN.WORKFLOW_ID,
OPB_TASK_INST_RUN.WORKFLOW_RUN_ID,
OPB_TASK_INST_RUN.WORKLET_RUN_ID,
OPB_TASK_INST_RUN.CHILD_RUN_ID,
OPB_TASK_INST_RUN.INSTANCE_ID,
OPB_TASK_INST_RUN.INSTANCE_NAME,
OPB_TASK_INST_RUN.TASK_ID,
OPB_TASK_INST_RUN.TASK_TYPE,
OPB_TASK_INST_RUN.START_TIME,
OPB_TASK_INST_RUN.END_TIME,
OPB_TASK_INST_RUN.RUN_ERR_CODE,
OPB_TASK_INST_RUN.RUN_ERR_MSG,
OPB_TASK_INST_RUN.RUN_STATUS_CODE,
OPB_TASK_INST_RUN.TASK_NAME,
OPB_TASK_INST_RUN.VERSION_NUMBER TASK_VERSION_NUMBER,
OPB_TASK_INST_RUN.SERVER_ID,
OPB_TASK_INST_RUN.SERVER_NAME
FROM
OPB_TASK_INST_RUN, OPB_WFLOW_RUN, OPB_SUBJECT
WHERE
OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID

3) Use the following if u want the number of records
NOTE :: widget_type 3 = source / source qualifier
2 = target

so u cannot do sum on the total....u might have to split into 2 queries...or u can use decode

SELECT
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_WFLOW_RUN.WORKFLOW_NAME,
OPB_TASK_INST_RUN.SUBJECT_ID,
OPB_TASK_INST_RUN.WORKFLOW_ID,
OPB_TASK_INST_RUN.WORKFLOW_RUN_ID,
OPB_TASK_INST_RUN.WORKLET_RUN_ID,
OPB_TASK_INST_RUN.CHILD_RUN_ID,
OPB_TASK_INST_RUN.INSTANCE_ID,
OPB_TASK_INST_RUN.INSTANCE_NAME,
OPB_TASK_INST_RUN.TASK_ID,
OPB_TASK_INST_RUN.TASK_TYPE,
OPB_TASK_INST_RUN.START_TIME,
OPB_TASK_INST_RUN.END_TIME,
OPB_TASK_INST_RUN.RUN_ERR_CODE,
OPB_TASK_INST_RUN.RUN_ERR_MSG,
OPB_TASK_INST_RUN.RUN_STATUS_CODE,
OPB_TASK_INST_RUN.TASK_NAME,
OPB_SWIDGINST_LOG.INSTANCE_NAME,
OPB_SWIDGINST_LOG.WIDGET_TYPE,
APPLIED_ROWS SUCCESSFUL_ROWS,
AFFECTED_ROWS SUCCESSFUL_AFFECTED_ROWS,
REJECTED_ROWS FAILED_ROWS,
LAST_ERR_MSG LAST_ERROR,
LAST_ERR_CODE LAST_ERROR_CODE
FROM
OPB_TASK_INST_RUN, OPB_WFLOW_RUN, OPB_SUBJECT, OPB_SWIDGINST_LOG
WHERE
OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_TASK_INST_RUN.WORKFLOW_ID
AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
AND OPB_SWIDGINST_LOG.WORKFLOW_RUN_ID = OPB_TASK_INST_RUN.WORKFLOW_RUN_ID
AND OPB_SWIDGINST_LOG.TASK_INSTANCE_ID = OPB_TASK_INST_RUN.INSTANCE_ID
AND OPB_SWIDGINST_LOG.WIDGET_TYPE IN (3, 2)