Results for “child_name”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view AMW_ALL_HIER_CHILDREN_V belongs to the AMW - Internal Controls Manager product family within Oracle E-Business Suite. It is documented as an obsolete component in ETRM 12.2.2 metadata, and the test database in which the object would normally reside reports "Not implemented in this database." Despite its obsolete status, the definition remains relevant to installations upgrading from 12.1.1 or auditing legacy Internal Controls Manager configurations, because the view encapsulates the historical process hierarchy model that underpinned control and risk reporting.

Functionally, the view exposes the complete set of process hierarchy descendants for both approved and latest hierarchies. Every row represents a parent-child relationship between two process revisions, annotated with the hierarchy from which the relationship was derived. This unified projection allowed downstream reports, extract programs, and integration interfaces to traverse an entire process tree — for example, to enumerate all sub-processes beneath a top-level business cycle — without writing separate queries against the approved and unapproved hierarchy tables.

Underlying Base Objects

The view text is a UNION of two structurally identical SELECT statements, each anchored on a different hierarchy table:

• AMW_APPROVED_HIERARCHIES — drives the approved hierarchy branch. This branch is filtered to AH.END_DATE IS NULL so that only the current effective hierarchy version is returned, and the parent and child process revisions must be approval-valid as of the hierarchy start date.
• AMW_LATEST_HIERARCHIES — drives the latest (working) hierarchy branch. It requires PR.END_DATE IS NULL and CH.END_DATE IS NULL for both endpoint processes.
• AMW_PROCESS_VL — joined twice, once aliased PR for the parent and once aliased CH for the child, supplying process codes, IDs, revision numbers, and display names.
• AMW_PROCESS — aliased ACH and outer-joined on CH.STANDARD_VARIATION = ACH.PROCESS_REV_ID to resolve the standard variation process revision where one exists.

Both branches apply an organization filter of ORGANIZATION_ID IS NULL OR ORGANIZATION_ID = -1, indicating the view surfaces global, non-organization-specific hierarchies.

Key Columns

• PARENT_ID / CHILD_ID — process identifiers for the parent and child nodes of the relationship.
• PARENT_REV_NUM / CHILD_REV_NUM — revision numbers corresponding to those processes.
• CHILD_PROCESS_CODE and CHILD_NAME — the code and display name of the descendant process, taken from AMW_PROCESS_VL.
• CHILD_STANDARD_PROCESS_FLAG — indicates whether the child is a standard process.
• CHILD_STD_VAR_PROCESS_ID — the process ID of the child's standard variation, if any.
• HIERARCHY_TYPE — the discriminator column populated with the literal 'A' for approved hierarchies and 'L' for latest hierarchies. This is the column most commonly filtered by consumers.

Common Use Cases and Queries

Typical usage involves enumerating descendants of a specific parent, or separating approved versus working hierarchies for reconciliation. A representative query restricted to the approved hierarchy is shown below; substituting 'L' returns the latest hierarchy instead.

SELECT parent_id, parent_rev_num, child_id, child_rev_num, child_process_code, child_name, child_standard_process_flag, child_std_var_process_id FROM amw_all_hier_children_v WHERE hierarchy_type = 'A' AND parent_id = :p_process_id ORDER BY child_process_code;

Because the view returns full descendant sets, it also supports recursive reporting when joined back to itself on child_id = parent_id, and it can be used to audit discrepancies between the approved and latest process structures by comparing the two HIERARCHY_TYPE populations.