Search Results locking_process_id

Overview

The view AMW_LATEST_HIERARCHY_RL_V belongs to the Oracle E-Business Suite module AMW — Internal Controls Manager, a product now classified as obsolete in the ETRM repository. Its documented purpose is to expose the latest hierarchy of risk library processes, flattening a parent–child process relationship into a single row per parent/child pair together with the child's process attributes and hierarchy ordering.

The view is designed for reporting and integration scenarios where a consumer must traverse the most recent revision of the Internal Controls Manager process hierarchy without writing recursive or self-join logic. Both the parent (PR) and child (CR) sides are sourced from the same process view, AMW_PROCESS_VL, and joined through AMW_LATEST_HIERARCHIES. The metadata notes that this object is not implemented in the current database, meaning it exists as a shipped definition but may not be deployed or populated in every environment.

Underlying Base Objects

The view text references three objects:

• AMW_PROCESS_VL — the process (risk library) view, aliased twice: PR for the parent and CR for the child.
• AMW_LATEST_HIERARCHIES (aliased AH) — supplies the parent/child relationship, the child ordering (CHILD_ORDER_NUMBER) and the organization context.
• AMW_PROCESS_LOCKS — queried within inline subqueries to derive the add/delete "switcher" flags.

The primary join is AH.PARENT_ID = PR.PROCESS_ID and AH.CHILD_ID = CR.PROCESS_ID, restricted to AH.ORGANIZATION_ID = -1 (the global/seed organization). Filtering conditions require PR.END_DATE, CR.END_DATE, PR.DELETION_DATE, and CR.DELETION_DATE to be NULL, so only current, undeleted process revisions are presented. A UNION branch adds orphan/root rows where the child has no parent, projecting NULL parent columns via TO_NUMBER(NULL) and NULL literals.

Key Columns

• PROCESS_ID / REVISION_NUMBER / PROCESS_REV_ID — identity and revision keys for both parent and child.
• NAME / DISPLAY_NAME / PROCESS_CODE / ITEM_TYPE — descriptive attributes exposed for parent and child.
• APPROVAL_STATUS, START_DATE, APPROVAL_DATE, APPROVAL_END_DATE, END_DATE — lifecycle and approval state of the child process.
• CONTROL_COUNT_LATEST, RISK_COUNT_LATEST, ORG_COUNT — aggregate counts of controls, risks, and organizations associated with the child process.
• SIGNIFICANT_PROCESS_FLAG, STANDARD_PROCESS_FLAG, CERTIFICATION_STATUS, PROCESS_CATEGORY, STANDARD_VARIATION — classification and certification attributes.
• PROCESS_OWNER_ID, FINANCE_OWNER_ID, APPLICATION_OWNER_ID — owner references.
• CHILD_ORDER_NUMBER — sequencing of the child within its parent.
• CHILD_ADD_SWITCHER, CHILD_DELETE_SWITCHER — computed flags (0/1) that indicate whether a lock exists on the child process for organization -1. The delete switcher additionally requires a LOCKING_PROCESS_ID different from the LOCKED_PROCESS_ID. These resolve the user's search term locked_process_id, which the view uses as a predicate against AMW_PROCESS_LOCKS.

Common Use Cases and Queries

Typical usage involves reporting the risk library hierarchy with parent context, or determining which processes are locked and therefore cannot have children added or removed.

SELECT parent_display_name,
       child_display_name,
       child_order_number
FROM   amw_latest_hierarchy_rl_v
WHERE  child_process_id IS NOT NULL
ORDER  BY parent_process_id, child_order_number;

To identify locked child processes:

SELECT child_process_id, child_display_name
FROM   amw_latest_hierarchy_rl_v
WHERE  child_add_switcher = 0
   OR  child_delete_switcher = 0;

Because the object is documented as obsolete and not implemented in the reference database, queries should be validated against the target instance before being embedded in production reporting. When available, it aggregates controls, risks, and certification data across the Internal Controls Manager process tree.