MPKG_PACKAGES

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

Sample 1

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;

Results 1

ASSIGNED_USER                  NUM_OPEN
----------------------------------------
...
rfrazier                              13
rjeffries                              1
rjones                                28
rnelson                                9
rsmith                                 3
...

Sample 2

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.

SQL> desc mpkg_packages;

Results 2

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.