Results for “ar_loc_four_level_v”

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

Overview

AR_LOC_FOUR_LEVEL_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Receivables (AR) module. It is documented in the E-Business Suite Technical Reference Manual (ETRM) as a legacy object, carrying the annotation "(Release 10SC Only)." That designation indicates the view originated in an early Receivables release and was retained in the data model for backward compatibility rather than being actively extended in Release 12.1.1 or 12.2.2.

Functionally, the view presents a flattened, four-tier hierarchical representation of a location structure. The Oracle EBS location model stores address and location segments as nodes in a hierarchy, where each segment can reference a parent segment. A standard hierarchy — such as Country > State/Province > City > Postal Code — contains four levels. AR_LOC_FOUR_LEVEL_V exploits that depth by self-joining the underlying location values view four times, producing one row per leaf (child) segment with all of its ancestors pivoted into the same row.

Because it pre-joins the hierarchy, the view serves reporting and integration scenarios where consumers require a single flat record describing a complete four-level location path without writing recursive or multi-pass self-joins themselves. It is not a transactional object; it exposes descriptive segment metadata only.

Underlying Base Objects

The ETRM metadata records a single referenced base object: AR_LOCATION_VALUES, itself a view. The defining query joins AR_LOCATION_VALUES four times using the aliases CHILD, PARENT, GRAND_PARENT, and GREAT_GRAND_PARENT. The join predicates establish both vertical and horizontal integrity:

• CHILD.PARENT_SEGMENT_ID = PARENT.LOCATION_SEGMENT_ID — links each node to its immediate parent.
• PARENT.PARENT_SEGMENT_ID = GRAND_PARENT.LOCATION_SEGMENT_ID — links the parent to the grandparent.
• GRAND_PARENT.LOCATION_SEGMENT_ID = GREAT_GRAND_PARENT.LOCATION_SEGMENT_ID — constrains the top of the four-level chain.
• Equal LOCATION_STRUCTURE_ID across all four aliases — ensures every level belongs to the same location structure (flexfield context), preventing cross-structure contamination.

The dependency chain therefore runs AR_LOC_FOUR_LEVEL_V → AR_LOCATION_VALUES → location segment/flexfield base tables. Any change to the structure or column set of AR_LOCATION_VALUES directly affects this view, and invalidating the base view cascades to AR_LOC_FOUR_LEVEL_V.

Key Columns

The view exposes sixteen columns, organized around the four hierarchy levels plus the structure and country context:

• LOCATION_STRUCTURE_ID — identifies the location structure (key flexfield context) to which the row belongs.
• CHILD_ID / CHILD_VALUE / CHILD_USER_VALUE / CHILD_QUALIFIER — the leaf segment's surrogate ID, stored value, user-entered display value, and its qualifier (e.g., postal code, city).
• PARENT_ID / PARENT_VALUE / PARENT_USER_VALUE — the immediately superior hierarchy level.
• GRAND_PARENT_ID / GRAND_PARENT_VALUE / GRAND_PARENT_USER_VALUE — the second level above the leaf.
• GREAT_GRAND_PARENT_ID / GREAT_GRAND_PARENT_VALUE / GREAT_GRAND_PARENT_USER_VALUE — the topmost of the four levels.
• COUNTRY_CODE — sourced from the child record, indicating the country associated with the leaf segment.

Note the asymmetry: only the CHILD level carries a QUALIFIER and the COUNTRY_CODE in the projection, while ancestor levels expose ID, stored value, and user value.

Common Use Cases and Queries

Typical usage includes address hierarchy reporting, territory and tax-zone derivation, and extracting fully qualified location strings for downstream integrations or data warehouses. A representative query retrieves the complete four-level path and country for a given structure:

• Reporting: SELECT CHILD_VALUE, PARENT_VALUE, GRAND_PARENT_VALUE, GREAT_GRAND_PARENT_VALUE, COUNTRY_CODE FROM AR_LOC_FOUR_LEVEL_V WHERE LOCATION_STRUCTURE_ID = :structure_id;
• Filtering by leaf: append WHERE CHILD_ID = :segment_id to retrieve a single child with all ancestors flattened.
• Concatenation for display: use GREAT_GRAND_PARENT_VALUE || ', ' || GRAND_PARENT_VALUE || ', ' || PARENT_VALUE || ' ' || CHILD_VALUE to build a mailing-style string.

Because only four levels are projected, the view does not return hierarchies deeper than four segments; structures with more levels require alternative traversal logic. Given the "(Release 10SC Only)" designation, implementations on 12.1.1 and 12.2.2 should treat the view as legacy — confirm that AR_LOCATION_VALUES remains valid and consider whether a supported, current construct better serves new development before relying on this object.