MREQ_REQUEST_ACTIONS

Used to gather information about all workflow actions for any given request in Demand Management. Contains columns to display the result status of each step, how long it took to complete, details about the step (for example, source and destination environment), and other relevant details. It also adds the submission (Process Open) and completion (Process Close) of a request as pseudo workflow step actions, displaying the entire life cycle of the request in a single view.

This view can be used directly to view the full transaction history of a request, or it can be used as a basis for more complex reports showing, for example, throughput at specific request steps.

To relate information from this view with information from relevant requests, use the request identifier REQUEST_ID to join with MREQ_REQUESTS or MREQ_<Request Type Name>.

Sample 1

Consider a report that takes a request ID as input from the person running the report, and shows all transactions for that request. To include the name of the step, the date an action was taken, the result, and how long the step stayed active before the action was taken, you could write a query similar to: 

SELECT action_name,
       action_date,
       action_result,
       duration
FROM   mreq_request_actions
WHERE  request_id = <DesiredID>
ORDER BY action_date;

Results 1 

                               Action    Action
Process Step                   Date      Result            Duration
------------------------------ --------- ----------------- --------
Open                           26-APR-01 Released               .00
Check Priority                 26-APR-01 Normal                 .00
SA - Check Prodcut             26-APR-01 NULL result            .00
CL - Check issue assignment    26-APR-01 aaslani                .00
Work In Progress               15-MAY-01 Resolved             18.72
Feedback                       20-MAY-01 Timeout               5.00
Close                          20-MAY-01 Closed [Success]       .00
Request resolved               20-MAY-01 Succeeded              .00

Sample 2

Consider a work order request type that has a Customer field with token CUSTOMER.

The name of the corresponding request view will be MREQ_WORK_ORDER based on the general view MREQ_<Request Type Name>. A report is needed to show all work order requests that are eligible for fjohnson to act on, broken down by customer:

SELECT wo.customer                CUSTOMER,
       wo.request_id              REQ_NUM,
       ra.request_workflow_step_label || ': '|| ra.action_name
                                  ELIGIBLE_STEP,
       ra.duration                DAYS_ELIGIBLE
FROM   mreq_work_order wo,
       mwfl_step_security_users ssu,
       mreq_request_actions ra
WHERE  ra.status_type = 'ELIGIBLE'
AND    ssu.workflow_step_id = ra.workflow_step_id
AND    ssu.username = 'fjohnson'
AND    ra.request_id = wo.request_id
ORDER BY 1,2,3,4;

The format of the ELIGIBLE_STEP column being selected, which will return a value similar to 12.3.1: Review by Lead. MWFL_STEP_SECURITY_USERS (see MWFL_STEP_SECURITY_GROUPS and MWFL_STEP_SECURITY_USERS) is used to determine if a specified user is authorized for a specified workflow step.

Additional considerations:

  • The column STATUS is the status name that is displayed in the status tab of requests in the Demand Management application.

    The internal code STATUS_TYPE is provided to group these status names into logical groupings. For example, there may be many different statuses that all represent a COMPLETE status type (the result value of any workflow step—Approved, Succeeded, Rejected, or Failed QA Test). While STATUS may have many different possible values, STATUS_TYPE has only the following possible values:

    • SUBMITTED

    • IN_PROGRESS

    • CLOSED_SUCCESS

    • ELIGIBLE

    • ERROR

    • CLOSED_FAILURE

    • PENDING

    • COMPLETE

    • CANCELLED