15.1 List of workflow run statistics
SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME,
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_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_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
ORDER BY 1,2,4
SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME,OPB_WFLOW_RUN.WORKFLOW_NAME,
OPB_TASK_INST_RUN.INSTANCE_NAME,
OPB_TASK_INST_RUN.START_TIME LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24') HOUR,
OPB_SESS_TASK_LOG.SRC_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.SRC_FAILED_ROWS,
OPB_SESS_TASK_LOG.TARG_SUCCESS_ROWS,
OPB_SESS_TASK_LOG.TARG_FAILED_ROWS,
TO_CHAR (OPB_TASK_INST_RUN.START_TIME, 'Day') weekday,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_LOG
WHERE OPB_SUBJECT.SUBJ_ID = OPB_TASK_INST_RUN.SUBJECT_ID
--AND OPB_SUBJECT.SUBJ_NAME
LIKE '%ICOCRM_%'
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_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
--AND TRUNC(OPB_TASK_INST_RUN.START_TIME) > TRUNC(SYSDATE -2)
and OPB_TASK_INST_RUN.INSTANCE_NAME in
(
)
ORDER BY 4,1,2,3
15.2 List of workflow run status
SELECT DISTINCT subject_area, workflow_name, server_name,
start_time, end_time, TO_CHAR (start_time, 'Day') weekday,
TRUNC (TO_CHAR ((end_time - start_time) * 1440)
) elapsedminutes,
CASE
WHEN run_status_code = '1'THEN 'Succeeded'
WHEN run_status_code = '2'THEN 'Disabled'
WHEN run_status_code = '3'THEN 'Failed'
WHEN run_status_code = '4'THEN 'Stopped'
WHEN run_status_code = '5'THEN 'Aborted'
WHEN run_status_code = '6'THEN 'Running'
WHEN run_status_code = '15'THEN 'Terminated'
else 'dummy'
END AS load_status
FROM rep_wflow_run
--WHERE run_status_code not in ( '1')
where
TRUNC(start_time) > TRUNC(SYSDATE -8)
-- TRUNC(start_time)
BETWEEN TRUNC(SYSDATE -7) AND TRUNC(SYSDATE)
ORDER BY 5
15.3 List workflow names
SELECT SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME
FROM REP_ALL_TASKS
WHERE TASK_TYPE IN (71)
--AND
SUBJECT_AREA= 'ABC'
ORDER BY 1, 2, 3
15.4 List save workflow log count
SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS WORKFLOW_NAME,
B.ATTR_VALUE AS SAVEWFLOG
FROM
REP_ALL_TASKS A ,
REP_TASK_ATTR B
WHERE
A.TASK_ID = B.TASK_ID
AND B.ATTR_ID = '4'
AND B.TASK_TYPE = 71
--AND B.ATTR_VALUE NOT IN (8,4)
-- AND A.SUBJECT_AREA = 'ABC'
ORDER BY 1,2,3
15.5 List workflow log names
SELECT DISTINCT
SUBJ_NAME,
WORKFLOW_NAME,
SUBSTR(LOG_FILE,23,300) AS EXISTING_WFLOGNAME
FROM
OPB_WFLOW_RUN,
OPB_SUBJECT
WHERE
OPB_WFLOW_RUN.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
-- AND SUBSTR(LOG_FILE,23,300) !=
CONCAT(LOWER(WORKFLOW_NAME),'.log')
ORDER BY 1,2
15.6 List write backward compatible check
SELECT DISTINCT
A.SUBJECT_AREA,A.TASK_NAME,
DECODE
(B.ATTR_VALUE,0,'TO
BE CHECKED',1,'CHECKED')
WRITEBACKWARDCOMPATIBLE
FROM
REP_ALL_TASKS
A,
OPB_TASK_ATTR B
WHERE
A.TASK_ID = B.TASK_ID
AND B.TASK_TYPE IN 71
AND B.ATTR_ID = 12
AND B.ATTR_VALUE <> 1
15.7 List fail_parent_if_task_fails objects
SELECT
REPOSITORY,
FOLDER_NAME,
WORKFLOW_OR_WORKLET,
TASK_TYPE,
WORKLET_OR_SESSION,
FAIL_PARENT_IF_TASK_FAILS
FROM
(SELECT DISTINCT
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS
FOLDER_NAME,
OPB_TASK.TASK_NAME AS
WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,
OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
DECODE
(BITAND (OPB_TASK_INST.BIT_OPTIONS, 17),17,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_FAILS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,
OPB_SUBJECT, OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID
= OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN
<> 0
--AND
OPB_SUBJECT.SUBJ_NAME NOT LIKE 'WA%'
)
WHERE FAIL_PARENT_IF_TASK_FAILS <> 'SELECTED'
15.8 List fail_parent_if_task_dont_run objects
SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,FAIL_PARENT_IF_TASK_DONT_RUN
FROM
(SELECT DISTINCT
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION, DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 49),49,'SELECTED','NOT SELECTED') AS FAIL_PARENT_IF_TASK_DONT_RUN
FROM OPB_TASK_INST, OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0
)
WHERE FAIL_PARENT_IF_TASK_DONT_RUN <> 'SELECTED'
ORDER BY 2
15.9 List is_task_enabled objects
SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,WORKLET_OR_SESSION,IS_TASK_ENABLED
FROM
(SELECT DISTINCT
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME, OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL', NULL) TASK_TYPE, OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
DECODE (OPB_TASK_INST.IS_ENABLED,1, 'ENABLED','DISABLED') AS IS_TASK_ENABLED
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0 )
WHERE IS_TASK_ENABLED = 'DISABLED'
ORDER BY 2,3
15.10 List treat_input_links_as objects
SELECT REPOSITORY,FOLDER_NAME,WORKFLOW_OR_WORKLET,TASK_TYPE,
WORKLET_OR_SESSION,TREAT_INPUT_LINKS_AS
FROM
(SELECT DISTINCT
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,
OPB_TASK.TASK_NAME AS WORKFLOW_OR_WORKLET,
DECODE(OPB_TASK_INST.TASK_TYPE,58,'COMMAND',59,'DECISION',60,'EVENT WAIT',62,'START',65,'EMAIL',66,'TIMER',67,'ASSIGNMENT',68,'SESSION',70,'WORKLET',91,'CONTROL',NULL) TASK_TYPE,
OPB_TASK_INST.INSTANCE_NAME AS WORKLET_OR_SESSION,
DECODE (BITAND (OPB_TASK_INST.BIT_OPTIONS, 3),1,'AND',2, 'OR') AS TREAT_INPUT_LINKS_AS
FROM OPB_TASK_INST,OPB_OBJECT_TYPE,OPB_TASK,OPB_SUBJECT,OPB_REPOSIT_INFO WHERE OPB_TASK_INST.TASK_TYPE != 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0
)
--WHERE TREAT_INPUT_LINKS_AS = 'OR'
ORDER BY 2,3
15.11 List all workflows whose server is not assigned
SELECT SUBJECT_AREA,WORKFLOW_NAME,SERVER_NAME
FROM REP_WORKFLOWS
WHERE SERVER_NAME IS NULL
15.12 List of sessions last run details
SELECT DISTINCT
(Select reposit_name from opb_reposit where recid =1) as repo,
OPB_SUBJECT.SUBJ_NAME,
OPB_WFLOW_RUN.SERVER_NAME,
OPB_WFLOW_RUN.WORKFLOW_NAME,
OPB_TASK_INST_RUN.INSTANCE_NAME,
TRUNC(OPB_TASK_INST_RUN.START_TIME) LOAD_DATE,
TO_CHAR(OPB_TASK_INST_RUN.START_TIME,'HH24:MI.SS') SESS_START_TIME,
TO_CHAR(OPB_TASK_INST_RUN.END_TIME,'HH24:MI.SS') SESS_END_TIME,
TRUNC((OPB_TASK_INST_RUN.END_TIME - OPB_TASK_INST_RUN.START_TIME) * 1440,2) DURATION_IN_MINS
FROM OPB_SUBJECT,OPB_TASK_INST_RUN,OPB_WFLOW_RUN,OPB_OBJECT_TYPE, OPB_SESS_TASK_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_TASK_INST_RUN.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_OBJECT_TYPE.OBJECT_TYPE = 68
AND OPB_WFLOW_RUN.WORKFLOW_ID = OPB_SESS_TASK_LOG.WORKFLOW_ID
AND OPB_WFLOW_RUN.WORKFLOW_RUN_ID = OPB_SESS_TASK_LOG.WORKFLOW_RUN_ID
AND OPB_TASK_INST_RUN.WORKLET_RUN_ID = OPB_SESS_TASK_LOG.WORKLET_RUN_ID
AND OPB_TASK_INST_RUN.INSTANCE_ID = OPB_SESS_TASK_LOG.INSTANCE_ID
AND OPB_TASK_INST_RUN.INSTANCE_NAME in
(
's_Load_STG_GCO_1_00')
and TRUNC(OPB_TASK_INST_RUN.START_TIME) = (
select MAX(TRUNC(AA.START_TIME)) from OPB_TASK_INST_RUN AA
where AA.INSTANCE_NAME = OPB_TASK_INST_RUN.INSTANCE_NAME
group by AA.INSTANCE_NAME
)
ORDER BY 1,2,4
SELECT DISTINCT
C.SUBJECT_AREA AS FOLDER_NAME,
A.WORKFLOW_NAME,
A.START_TIME,
A.END_TIME,
A.USER_NAME,B.SESSION_NAME,B.MAPPING_NAME,
B.SUCCESSFUL_SOURCE_ROWS AS SOURCE_SUCCESFUL_READ_ROWS,
B.FAILED_SOURCE_ROWS AS SOURCE_SUCCESFUL_FAILED_ROWS,
B.SUCCESSFUL_ROWS AS TARGET_SUCCESSFUL_ROWS,
B.FAILED_ROWS AS
TARGET_FAILED_ROWS,
-- B.FIRST_ERROR_CODE,B.FIRST_ERROR_MSG,
B.ACTUAL_START
FROM REP_WFLOW_RUN A, REP_SESS_LOG B,
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 >= SYSDATE-1
AND
( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3
15.13 List of workflows currently running
SELECT DISTINCT subject_area, workflow_name, server_name,
start_time, end_time,
TO_CHAR (start_time, 'Day') weekday,
TRUNC (TO_CHAR ((end_time
- start_time) * 1440)
) elapsedminutes,
CASE
WHEN run_status_code = '1' THEN 'Succeeded'
WHEN run_status_code = '2' THEN 'Disabled'
WHEN run_status_code = '3'
THEN 'Failed'
WHEN run_status_code = '4'
THEN 'Stopped'
WHEN run_status_code = '5' THEN 'Aborted'
WHEN run_status_code = '6'
THEN 'Running'
WHEN run_status_code = '15'
THEN 'Terminated'
END AS load_status
FROM rep_wflow_run
WHERE run_status_code = '6'
ORDER BY 1,2,3
15.14 Check Session with Failed Records and Error Code/Message
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 REP_WFLOW_RUN A, REP_SESS_LOG B, 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 >= SYSDATE-10
AND ( B.FAILED_ROWS > 0 OR FIRST_ERROR_CODE <> 0 )
ORDER BY 1,3
15.15 Workflow Task Conditions
Below query lists the task conditions defined in the workflow.
SELECT opb_subject.subj_name, wf.task_name,
from_inst.instance_name AS from_instance, opb_wflow_expr.condition,
to_inst.instance_name AS to_instance
FROM opb_task wf,
opb_wflow_dep,
opb_wflow_expr,
opb_task,
opb_subject,
opb_task_inst from_inst,
opb_task_inst to_inst
WHERE wf.task_id = opb_wflow_dep.workflow_id
AND wf.is_visible = 1
AND wf.version_number = opb_wflow_dep.version_number
AND opb_wflow_dep.workflow_id = opb_wflow_expr.workflow_id
AND opb_wflow_dep.condition_id = opb_wflow_expr.condition_id
AND opb_wflow_dep.version_number = opb_wflow_expr.version_number
AND opb_wflow_dep.workflow_id = opb_task.task_id
AND opb_wflow_dep.version_number = opb_task.version_number
AND opb_task.is_visible = 1
AND opb_wflow_expr.line_no = 1
AND opb_task.subject_id = opb_subject.subj_id
AND from_inst.instance_id = opb_wflow_dep.from_inst_id
AND to_inst.instance_id = opb_wflow_dep.to_inst_id
AND from_inst.version_number = opb_wflow_dep.version_number
AND to_inst.version_number = opb_wflow_dep.version_number
-- AND opb_subject.subj_name = < Input your subject Name >
-- Input your workflow name
AND wf.task_name = 'wf_EXTRACT_CTRM_LOAD_P_CCTR'
-- and opb_wflow_expr.condition is not null
1.1.1.2 Workflow , Session & Mapping Details
SELECT F.SUBJ_NAME AS FOLDER_NAME,
WF.TASK_NAME AS WORKFLOW_NAME,
SE.INSTANCE_NAME AS SESSION_NAME,
M.MAPPING_NAME
FROM OPB_SUBJECT F,
OPB_TASK WF,
( SELECT WORKFLOW_ID,
INSTANCE_ID,
TASK_ID,
TASK_TYPE,
INSTANCE_NAME,
MAX (VERSION_NUMBER)
FROM OPB_TASK_INST SESS
WHERE SESS.TASK_TYPE = 68
GROUP BY WORKFLOW_ID,
INSTANCE_ID,
TASK_ID,
TASK_TYPE,
INSTANCE_NAME) SE,
( SELECT SESSION_ID, MAPPING_ID, MAX (VERSION_NUMBER)
FROM OPB_SESSION
GROUP BY SESSION_ID, MAPPING_ID) S,
OPB_MAPPING M
WHERE WF.IS_VISIBLE = 1
AND WF.SUBJECT_ID = F.SUBJ_ID
AND SE.WORKFLOW_ID = WF.TASK_ID
AND WF.TASK_TYPE = 71
AND SE.TASK_ID = S.SESSION_ID
AND S.MAPPING_ID = M.MAPPING_ID
AND M.IS_VISIBLE = 1
AND F.SUBJ_NAME = 'ACCUM_HNT'
1.1.1.3 Folder, Workflow , Session & Mapping, SRC and TGT Details
SELECT DISTINCT TAR.SUBJECT_AREA FOLDER_NAME,
TAR.WORKFLOW_NAME WORKFLOW_NAME,
TAR.MAPPING_NAME MAPPING_NAME,
TAR.SESSION_NAME SESSION_NAME,
SRC.SOURCE_NAME SOURCE_NAME,
SRC_BUSNAME SRC_BUSNAME,
SRC.SRC_OWNERNAME SRC_OWNERNAME,
SRC.SRC_DBNAME SRC_DBNAME,
SRC.SRC_DBTYPE SRC_DBTYPE,
TAR.TARGET_NAME TARGET_NAME,
TAR.TARG_DBTYPE TARGRT_DBTYPE,
TAR.TARG_BUSNAME TARGET_BUSNAME
FROM (SELECT DISTINCT
OPB_TARG.TARGET_NAME TARGET_NAME,
OPB_TARG.BUSINESS_NAME TARG_BUSNAME,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_MAPPING.MAPPING_NAME MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
REP_SESS_LOG.WORKFLOW_NAME WORKFLOW_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID,
DECODE (OPB_TARG.DBTYPE,
0, 'VSAM',
1, 'IMS',
2, 'Sybase',
3, 'Oracle',
4, 'Informix',
5, 'Microsoft SQL Server',
6, 'DB2',
7, 'Flat File',
8, 'ODBC',
9, 'SAP BW',
10, 'PeopleSoft',
11, 'SAP R/3',
12, 'XML',
13, 'MQSeries',
14, 'Siebel',
15, 'Teradata')
AS TARG_DBTYPE
FROM OPB_TARG,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND REP_SESS_LOG.mapping_name = OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 2
AND OPB_WIDGET_INST.WIDGET_ID = OPB_TARG.TARGET_ID
AND OPB_TDS.TARGET_INSTANCE_ID = OPB_WIDGET_INST.INSTANCE_ID
AND OPB_TARG.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_SUBJECT.SUBJ_NAME = 'ACCUM_HNT'
AND OPB_TARG.IS_VISIBLE = 1) TAR,
(SELECT DISTINCT OPB_SRC.SOURCE_NAME,
OPB_SRC.SRC_ID SOURCE_ID,
OPB_SRC.BUSINESS_NAME SRC_BUSNAME,
OPB_SRC.FILE_NAME SCHEMA_NAME,
OPB_SRC.OWNERNAME SRC_OWNERNAME,
OPB_DBD.DBDNAM SRC_DBNAME,
OPB_MMD_DBTYPE.DBTYPE_NAME SRC_DBTYPE,
OPB_SUBJECT.SUBJ_NAME SUBJECT_AREA,
OPB_SUBJECT.SUBJ_ID SUBJECT_ID,
OPB_MAPPING.MAPPING_NAME,
REP_SESS_LOG.SESSION_NAME SESSION_NAME,
OPB_MAPPING.MAPPING_ID MAPPING_ID
FROM OPB_SRC,
OPB_SUBJECT,
OPB_MAPPING,
REP_SESS_LOG,
OPB_TDS,
OPB_WIDGET_INST,
OPB_DBD,
OPB_MMD_DBTYPE
WHERE OPB_TDS.MAPPING_ID = OPB_MAPPING.MAPPING_ID
AND OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID
AND REP_SESS_LOG.MAPPING_NAME = OPB_MAPPING.MAPPING_NAME
AND OPB_WIDGET_INST.WIDGET_TYPE = 1
AND OPB_WIDGET_INST.WIDGET_ID = OPB_SRC.SRC_ID
AND OPB_SRC.SUBJ_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_DBD.DBDID = OPB_SRC.DBDID
AND OPB_MAPPING.REF_WIDGET_ID = 0
AND OPB_MAPPING.IS_VISIBLE = 1
AND OPB_SRC.IS_VISIBLE = 1
AND OPB_DBD.DBTYPE = OPB_MMD_DBTYPE.DBTYPE_ID
AND OPB_SUBJECT.SUBJ_NAME = 'ACCUM_HNT') SRC
WHERE TAR.MAPPING_ID = SRC.MAPPING_ID
ORDER BY 1,2,3
1.1.1.4 Workflow First Run Details from Reposotory
SELECT *
FROM ( SELECT opb_subject.subj_name,
server_name,
WORKFLOW_NAME,
MIN (START_TIME) AS START_TIME,
TO_CHAR (MIN (START_TIME), 'YYYY-MM') AS YEAR_MONTH
FROM OPB_WFLOW_RUN
INNER JOIN opb_subject
ON OPB_WFLOW_RUN.subject_id = opb_subject.subj_id
GROUP BY opb_subject.subj_name,
OPB_WFLOW_RUN.server_name,
OPB_WFLOW_RUN.WORKFLOW_NAME
ORDER BY opb_subject.subj_name,
OPB_WFLOW_RUN.server_name,
OPB_WFLOW_RUN.WORKFLOW_NAME)
1.1.1.5 Workflows which are not run in reposotories
SELECT * FROM (
SELECT DISTINCT SUBJECT_AREA, TASK_NAME
FROM REP_ALL_TASKS
WHERE TASK_TYPE=71 ORDER BY SUBJECT_AREA )
MINUS
SELECT * FROM (
SELECT DISTINCT OPB_SUBJECT.SUBJ_NAME AS SUBJECT_AREA, WORKFLOW_NAME AS TASK_NAME
FROM OPB_WFLOW_RUN INNER JOIN OPB_SUBJECT
ON OPB_WFLOW_RUN.SUBJECT_ID=OPB_SUBJECT.SUBJ_ID
ORDER BY OPB_SUBJECT.SUBJ_NAME )