The most general view into package transaction data. A blind query (that is,
SELECT * FROM mpkg_packages) returns one row for each package present in the system, including closed packages. For information about individual package lines, use the other views that provide line detail.
The view columns map to package header fields, such as Priority, Package Group, and Assigned-to User. There are also columns for the package status and the dates on which it was submitted, closed, or cancelled. Because global package user data fields are present on all packages, there is also a view column for each global package user data field that is defined.
The column name for each global package user data field is the same as the token name for that field. Context-sensitive package user data sets have their own views. See MPKG_UD_<Context Value>.
The MPKGL_PACKAGE_LINES view can be used to query general package line data, including package line user data fields. If it is necessary to report on the activity of specific object types, the set of object type-specific views is more appropriate. See MPKGL_<Object Type Name>.
To determine the number of open packages and to whom they are assigned:
SELECT assigned_to_username ASSIGNED_USER, COUNT(*) NUM_OPEN FROM mpkg_packages WHERE close_date IS NULL AND cancel_date IS NULL AND submission_date IS NOT NULL GROUP BY assigned_to_username ORDER BY 1;
ASSIGNED_USER NUM_OPEN ---------------------------------------- ... rfrazier 13 rjeffries 1 rjones 28 rnelson 9 rsmith 3 ...
A global package user data field has been defined to capture the username of a backup user responsible for each package. The token name for this field is BACKUP_USERNAME.
Name Null? Type ------------------------------- -------- ---- PACKAGE_NUMBER NOT NULL VARCHAR2(30) PACKAGE_DESCRIPTION VARCHAR2(240) ... PACKAGE_TYPE_CODE NOT NULL DATE BACKUP_USERNAME VARCHAR2(200) PARENT_REQUEST_ID NUMBER CREATED_BY NOT NULL VARCHAR2(30) ...
This new column can be used to drive a report. For example, to report on packages 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 mpkg_packages 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 original user to whom the package was assigned.