MREQ_CONTACTS

Contains all fields for contacts defined in Demand Management. Contains all relevant pieces of information about a contact, including a denormalized username (if present) and a column for each Contact User Data field defined in the system. The column name for each Contact User Data field is the same as the token name for that field.

A subset of the information provided here is also present in the request views MREQ_REQUESTS and MREQ_<Request Type Name>.

Sample 1

SELECT full_name NAME,
       phone_number PHONE_NUMBER,
       email_address EMAIL
FROM   mreq_contacts
WHERE  enabled_flag = 'Y';

Sample 2

If there are Contact User Data fields defined, the token for each field will appear as a separate column in MREQ_CONTACTS.

For example, two Contact User Data fields have been defined to track additional contact information, with tokens PAGER_NUMBER and HOME_PHONE_NUMBER. Two columns with the same names would be present in MREQ_CONTACTS:

SQL> desc mreq_contacts;

Results 2

Name                            Null?    Type
------------------------------- -------- ----
LAST_NAME                       NOT NULL VARCHAR2(30)
FIRST_NAME                      NOT NULL VARCHAR2(30)
...
PAGER_NUMBER                             VARCHAR2(200)
HOME_PHONE_NUMBER                        VARCHAR2(200)
ENABLED_FLAG                    NOT NULL VARCHAR2(1)
CREATION_DATE                   NOT NULL DATE
...

Sample 3

Building on Sample 1 by using MREQ_REQUESTS, consider designing a report to print the full name, pager, and work numbers of all users who are assigned as backup users on requests that have been open for more than 5 days.

An SQL statement to achieve this type of information might look as follows:

SELECT r.backup_username USERNAME,
       c.full_name NAME,
       c.pager_number PAGER_NUMBER,
       c.phone_number WORK_NUMBER,
FROM   mreq_contacts c,
       mreq_requests r
WHERE  c.enabled_flag = 'Y'
AND    r.backup_username = c.username (+)
AND    r.close_date IS NULL
AND    r.cancel_date IS NULL
AND    r.submission_date IS NOT NULL
AND    (sysdate – r.submission_date) > 5;