MREQ_CHANGES

When a field is being audited, a record is stored in the PPM database every time the value in that field changes on any open Request. This audit history can be important to business decision-making.

MREQ_CHANGES allows a report to display and drive off of changes to request fields. This view exposes the audit trail for the request header and detail fields. It contains columns for the old and new values, and the field prompts and tokens.

Sample

To report on the frequency at which the request priority is changed from any value to Critical, an SQL statement such as the following can be used:

SELECT m.calendar_month MONTH,
       c.old_field_value OLD_VALUE,
       count(*) NUM_CHANGED
FROM   mreq_changes c,
       krml_calendar_months m
WHERE  c.field_prompt = 'Priority'
AND    c.new_field_code = 'C'
AND    c.change_date >= m.start_date
AND    c.change_date < m.end_date
GROUP BY m.calendar_month, c.old_field_value
ORDER BY 1, 2;

In the WHERE clause of this statement that is tested, the NEW_FIELD_CODE is used instead of NEW_FIELD_VALUE. Either would work.

C is the code for the Critical priority; this statement could also have been written WHERE c.new_field_value = 'Critical'.

The validation for the request priority field contains the hidden and visible values for this field. Consult this validation in the Validations window for verification of these values.

Consider a slight extension to the previous SQL statement. If it was necessary to limit this information to a specific request type, an additional AND condition could be used: AND c.request_type = '<Name>'.