Search Results up_down_ind

Overview

AMW.AMW_PROC_HIERARCHY_DENORM is a denormalized process hierarchy table owned by the AMW schema in Oracle E-Business Suite (validated on 12.1.1 and 12.2.2). It stores the flattened representation of process-to-process relationships, enabling efficient ancestor/descendant traversal without recursive self-joins against the underlying normalized hierarchy structures. This denormalization supports application modules that must resolve parent-child chains, roll up costs or quantities across process levels, or validate hierarchical integrity during transaction processing.

From a Data Vault modeling perspective, the metadata classifies this object as standalone, with no outgoing foreign keys except the standard SECURITY_GROUP_ID reference to FND_SECURITY_GROUPS. In Data Vault terms, this table is best treated as a link/satellite hybrid suggestion: the core business keys (PROCESS_ID, PARENT_CHILD_ID, UP_DOWN_IND, HIERARCHY_TYPE) form the relationship link, while the WHO columns and OBJECT_VERSION_NUMBER act as satellite descriptive attributes. This is a modeling heuristic, not a physical constraint.

Key Information Stored

The table contains 11 documented columns. The most operationally significant are:

• PROCESS_ID (NUMBER) — the primary process identifier participating in the hierarchy.
• PARENT_CHILD_ID (NUMBER) — the related process identifier, representing either the parent or the child depending on UP_DOWN_IND.
• UP_DOWN_IND (VARCHAR2) — directional indicator distinguishing parent-to-child from child-to-parent relations.
• HIERARCHY_TYPE (VARCHAR2) — classifies the hierarchy (for example, planning or costing variants).
• OBJECT_VERSION_NUMBER (NUMBER) — optimistic locking control for concurrent updates.
• SECURITY_GROUP_ID (NUMBER) — supports hosted/multi-tenant environments; the only documented FK to FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns tracking the creator, last modifier, and login context.

The surrogate/business key is enforced by unique index AMW_PROC_HIERARCHY_DENORM_U1 over (PROCESS_ID, PARENT_CHILD_ID, UP_DOWN_IND, HIERARCHY_TYPE). A nonunique supporting index, AMW_PROC_HIERARCHY_DENORM_N1 on (PROCESS_ID, HIERARCHY_TYPE), accelerates filtered lookups.

Common Use Cases and Queries

Typical usage involves resolving full process trees, validating parent-child consistency, or generating hierarchy reports. Common query patterns include retrieving all descendants of a given process by filtering on PROCESS_ID and UP_DOWN_IND, and joining to master process tables to obtain descriptions.

• Traverse one level of an upward hierarchy: SELECT PARENT_CHILD_ID FROM AMW_PROC_HIERARCHY_DENORM WHERE PROCESS_ID = :p AND UP_DOWN_IND = 'P' AND HIERARCHY_TYPE = :t;
• Fetch full stored hierarchy for a process: SELECT * FROM AMW_PROC_HIERARCHY_DENORM WHERE PROCESS_ID = :p AND HIERARCHY_TYPE = :t ORDER BY UP_DOWN_IND;
• Reporting rollups join PROCESS_ID to the base process definition to aggregate across levels.

Because the hierarchy is pre-flattened, these queries avoid costly CONNECT BY operations and are suitable for high-volume reporting in AMW planning and costing flows.

Related Objects

The documented dependency data shows this table does not reference other database objects, but it is referenced by the APPS synonym and the underlying AMW_PROC_HIERARCHY_DENORM base object. The relevant join relationships are:

• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.
• APPS.AMW_PROC_HIERARCHY_DENORM — the APPS-layer synonym through which application code and concurrent programs query the AMW base table.
• Base process definition objects within the AMW schema — joined on PROCESS_ID and PARENT_CHILD_ID to resolve descriptions and attributes for hierarchy nodes.

Because the object is standalone, integration effort centers on the unique-key combination and the security group rather than on cascading foreign-key constraints.