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