MREQ_REQUESTS

The most general view into request transaction data.

A blind query (SELECT * FROM mreq_requests;) will return one row for each request present in the system, including closed requests.

The view columns map to the request fields that are common to all request types (for example, priority, department, application, assigned-to user, and contact information). There are also columns for the status of a request and the dates on which it was submitted, closed, or cancelled.

Because global request user data fields are present on all requests, there is also a view column for each global request user data field that is defined. The column name for each global request user data field is the same as the token name for that field.

Use this view when writing a report to present general request information without respect to a particular request type.

To build reports that make use of custom detail fields of a particular request type, see MREQ_<Request Type Name>.

Sample 1

To get information about the number of open requests in the system and to whom they are assigned:

SELECT assigned_to_username ASSIGNED_USER,
       COUNT(*) NUM_OPEN
FROM   mreq_requests
WHERE  close_date IS NULL
AND    cancel_date IS NULL
AND    submission_date IS NOT NULL
GROUP BY assigned_to_username
ORDER BY 1;

Results 1

ASSIGNED_USER                  NUM_OPEN
----------------------------------------
...
rfrazier                              13
rjeffries                              1
rjones                                28
rnelson                                9
rsmith                                 3
...

Sample 2

Or consider a similar query with the results grouped by the request type, to see how many requests of each type are open:

SELECT request_type_name REQUEST_TYPE,
       COUNT(*) NUM_OPEN
FROM   mreq_requests
WHERE  close_date IS NULL
AND    cancel_date IS NULL
AND    submission_date IS NOT NULL
GROUP BY request_type_name
ORDER BY 1;

Results 2

REQUEST_TYPE                       NUM_OPEN
--------------------------------------------
HR Job Requisition                        37
HR New Hire Process                       11
Press Release                              3
Product Patch                             33
Purchase Request                          11
Services Work Order                       81
Training Approval Request                115
Vacation Request                          56

Sample 3

Consider the case where a global request user data field has been defined to capture the username of a backup user responsible for each request.

The token name for this field is BACKUP_USERNAME. Therefore, in this view there would be a column named BACKUP_USERNAME:

SQL> desc mreq_requests;

Results 3

Name                            Null?    Type
------------------------------- -------- ----
REQUEST_ID                      NOT NULL NUMBER
REQUEST_DESCRIPTION             NOT NULL VARCHAR2(240)
SUBMISSION_DATE                 NOT NULL DATE
REQUEST_STATUS                  NOT NULL VARCHAR2(80)
...
CANCEL_DATE                     NOT NULL DATE
BACKUP_USERNAME                          VARCHAR2(200)
REQUEST_TYPE_NAME                        VARCHAR2(80)
REQUEST_SUBTYPE_NAME                     VARCHAR2(80)
...

Sample 4

The new column can be used to drive a report, if necessary. For example, to report on requests that have been open for more than five days and assigned to a particular backup user:

SELECT backup_username BACKUP_USER,
       assigned_to_username ASSIGNED_USER,
       COUNT(*) NUM_OLD_REQS
FROM   mreq_requests
WHERE  backup_username = '<ValidUsername>'
AND    close_date IS NULL
AND    cancel_date IS NULL
AND    submission_date IS NOT NULL
AND    (sysdate – submission_date) > 5
GROUP BY backup_username, assigned_to_username
ORDER BY 1, 2;

This query also displays the name of the original user to whom the request was assigned.