Cross-Product Views

Cross-product views relate information across PPM products. Each view is described in the sections that follow.

For example, MWFL_STEP_ACTIVITIES shows statistics about workflow step completion across applications.

MWFL_STEP_ACTIVITIES

This view contains activity statistics for all workflow steps, including subworkflows. For any given workflow or workflow step, MWFL_STEP_ACTIVITIES can be used to get a quick snapshot of aggregate system activity. It is provided as a general reference for gathering data that is not covered by other product-specific statistical views. The internal ID columns for workflow and workflow step (WORKFLOW_ID and WORKFLOW_STEP_ID) can be used to join this view to other product action or workflow-related views to gather additional information about the records contained therein.

This view can also be used to flag step duration issues by looking at step completion times (AVG_TIME_TO_COMPLETE and AVG_TIME_OPEN), or other exceptions like spikes in the number of cancelled workflow steps for a point in time.

Sample

A report needs to contain summary information for the number of errors for step 2 in the FIN dev-test-prod workflow, broken down by month. The calendar table described in KRML_CALENDAR_DAYS and KRML_CALENDAR_MONTHS can be used to provide the month-by-month breakdown to join with the ACTIVITY_DATE column in this view:

SELECT m.calendar_month MONTH,
       sum(sa.error) NUM_ERRORS
FROM   krml_calendar_months m,
       mwfl_step_activities sa
WHERE  sa.workflow = 'FIN dev-test-prod'
AND    sa.workflow_step_number = 2
AND    sa.activity_date >= m.start_date
AND    sa.activity_date < m.end_date
GROUP BY m.calendar_month
ORDER BY 1;

Results

    MONTH    NUM_ERRORS
------------------------
01-APR-01            16
01-MAY-01             4
01-JUN-01             0
01-AUG-01             0
01-SEP-01             1