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.