Search Results lob_valueset

Overview

APPS.AMW_MANAG_HIER_V is a reporting view in the Oracle E-Business Suite (EBS) environment, associated with the Application Management (AMW) module — the internal ETRM (Enterprise Transaction and Risk Management) schema used by Oracle's Application Management Suite / Application Change Management Pack for E-Business Suite. The view presents a flattened, hierarchical representation of the Line of Business (LOB) dimension used to scope audit units, controls, and risk configuration. In Oracle EBS 12.1.1 and 12.2.2, it serves as a denormalized read layer that exposes LOB flexfield values and their parent-child relationships in a single, consistent structure suitable for reporting, list-of-values (LOV) population, and integration into audit management dashboards.

The view returns a uniform column set across two logical segments, identified by the LINE_TYPE column (set to 'LOB'). It supports both root-level LOB values (where parent columns are null) and child LOB values that participate in a parent-child hierarchy. This makes it the natural source for any consumer requiring a complete LOB tree rather than a flat value list.

Underlying Base Objects

No base tables are documented as referenced objects for this view in the ETRM metadata. The view text, however, is defined over two principal source objects:

• FND_FLEX_VALUES_VL — the seeded key flexfield values view, supplying FLEX_VALUE_SET_ID, FLEX_VALUE, FLEX_VALUE_ID, and DESCRIPTION. Joined twice (aliases fvlp for parents and fvlc for children) in the hierarchical segment.
• FND_FLEX_VALUE_CHILDREN_V — the seeded flexfield hierarchy view, providing CH.FLEX_VALUE_SET_ID, CH.PARENT_FLEX_VALUE, and CH.FLEX_VALUE. This is the hierarchy edge source driving parent-child rows.
• AMW_AUDIT_UNITS_V — used in subqueries to identify the set of distinct LOB_VALUESET values in scope, filtering both segments to relevant LOB value sets only.

The first UNION branch selects top-level LOB flex values that are not themselves present as a child in FND_FLEX_VALUE_CHILDREN_V (i.e., root nodes), and the second branch selects LOB flex values that do have a defined parent, producing the full set of hierarchy rows.

Key Columns

• FLEX_VALUE_SET_ID — identifies the value set that the LOB value belongs to; scoped to the LOB value sets discovered through AMW_AUDIT_UNITS_V.
• PARENT_VALUE / PARENT_ID / PARENT_DISPLAY_NAME — the parent LOB flex value, its flex value ID, and its description. Null for root nodes.
• CHILD_VALUE / CHILD_ID / CHILD_DISPLAY_NAME — the child (or standalone) LOB flex value and its description. CHILD_DISPLAY_NAME is populated from the flexfield DESCRIPTION and is the user-facing label for the LOB.
• LINE_TYPE — constant 'LOB' in all rows, distinguishing this view's content from other AMW hierarchy views.
• Remaining columns (CHILD_DESCRIPTION, CHILD_PROCESS_ID, CHILD_ORGANIZATION_ID, CHILD_PROCESS_CODE, CHILD_REVISION_NUMBER, CHILD_APPROVAL_STATUS, CHILD_APPROVAL_DATE, CHILD_CONTROL_COUNT, CHILD_RISK_COUNT, CHILD_SIGNIFICANT_PROCESS_FLAG, CHILD_STANDARD_PROCESS_FLAG, CHILD_CERTIFICATION_STATUS, CHILD_PROCESS_CATEGORY, CHILD_PROCESS_TYPE, CHILD_CONTROL_ACTIVITY_TYPE, CHILD_ORDER_NUMBER, ACTIVE_AUDIT_PROJECT_COUNT) are null in this view, providing a common shape compatible with sibling AMW hierarchy views.

Common Use Cases and Queries

Typical usage involves resolving LOB hierarchy for audit scoping, generating LOVs, and producing hierarchical reports. A query to list all LOB children with their parents follows:

SELECT flex_value_set_id,
       parent_value,
       parent_display_name,
       child_value,
       child_display_name
  FROM apps.amw_manag_hier_v
 WHERE line_type = 'LOB'
 ORDER BY parent_display_name, child_display_name;

To retrieve only root LOB values:

SELECT child_value, child_display_name
  FROM apps.amw_manag_hier_v
 WHERE parent_id IS NULL;

Because CHILD_DISPLAY_NAME is sourced from the flexfield description, the view is well suited for populating pick lists, BI Publisher reports, and OBIEE/XML Publisher data models where a readable LOB label is required alongside the underlying value.