Share this page

# 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.

https://admhelp.microfocus.com PPM 8942
Tips to improve your search results... OpenText™ Project and Portfolio Management Help Center PPM Help 26.2-26.3 All View Local Help Center View Online Help Center Version: