MPKGL_<Object Type Name>

Set of views containing object type-specific package line information.

When the Reporting Meta Layer is synchronized, a view is created for every object type defined in the system. The name of each view is defined on the object type screen in the Meta Layer View field. It defaults to a prefix MPKGL_ and a suffix that defaults to the first 20 alphanumeric characters of the corresponding object type name.

If there are three object types defined in Deployment Management named Java File Migration, SQL Script Migration, and Forms 4.5 Migration, then three corresponding Meta Layer views would exist: MPKGL_JAVA_FILE_MIGRATION, MPKGL_SQL_SCRIPT_MIGRATION, and MPGKL_FORMS_45_MIGRATION.

The view columns are identical to those of the general MPKGL_PACKAGE_LINES view (including the package line user data fields), and they include additional columns for each custom field for the object type. This allows a report designer to create a report that implements business logic that drives off of customer-defined object type fields.

For example, consider the Java File Migration object type. This object type might have custom fields with tokens such as FILE_NAME, FILE_LOCATION, and SUB_PATH. The corresponding view MPKGL_JAVA_FILE_MIGRATION would contain columns with these names.

Sample 1

SQL> desc mpkgl_java_file_migration;

Results 1

Name                            Null?    Type
------------------------------- -------- ----
PACKAGE_NUMBER                  NOT NULL VARCHAR2(40)
LINE_NUMBER                     NOT NULL NUMBER
...
CANCEL_DATE                              DATE
FILE_NAME                                VARCHAR2(200)
SUB_PATH                                 VARCHAR2(200)
FILE_LOCATION                            VARCHAR2(200)
CREATION_DATE                   NOT NULL DATE
CREATED_BY_USERNAME             NOT NULL VARCHAR2(30)
...

Sample 2

To continue the example, a report is needed that will list the PPM user who is assigned to open packages containing one or more package lines that are Java File Migration objects, and that are eligible for migration.

A SQL query such as the following might handle this:

SELECT p.workflow,
       p.assigned_to_username ASSIGNED_USER, 
       COUNT(UNIQUE(p.package_id)) NUM_ELIGIBLE
FROM   mpkg_packages p,
       mpkgl_package_line_actions pla,
       mpkgl_java_file_migration j
WHERE  j.close_date IS NULL
AND    j.cancelled_flag = 'N'
AND    j.submission_date IS NOT NULL
AND    j.package_line_id = pla.package_line_id
AND    pla.status_type = 'ELIGIBLE'
AND    j.package_id = p.package_id
GROUP BY p.workflow, p.assigned_to_username
ORDER BY 1, 2;