Results for “location_structure_id”

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

Overview

AR_LOC_THREE_LEVEL_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Receivables (AR) module. It exposes a flattened, three-tier hierarchy of location segment values by joining the AR_LOCATION_VALUES view to itself twice — once as the child, once as the parent, and once as the grandparent — through self-referential parent-child relationships. This design allows consumers to retrieve a complete lineage for any location segment value without recursive SQL or hierarchical query clauses at the point of use.

The object is documented in ETRM as a legacy artifact associated with Release 10SC. Although it remains present and VALID in the APPS schema across 12.1.1 and 12.2.2, it is not considered a strategic integration interface. It is most frequently encountered in custom reports, legacy Discoverer workbooks, and historical inquiry screens built in earlier releases that were never refactored for the multi-level location model introduced later. Because the view is a pure projection over existing data with no business logic of its own, upgrades between 12.1.1 and 12.2.2 do not alter its behavior.

Underlying Base Objects

The view text is defined exclusively over AR_LOCATION_VALUES, referenced three times with the aliases CHILD, PARENT, and GRAND_PARENT. AR_LOCATION_VALUES is itself a view, so AR_LOC_THREE_LEVEL_V sits one level of indirection above the physical location tables. The join predicates are:

• CHILD.PARENT_SEGMENT_ID = PARENT.LOCATION_SEGMENT_ID
• PARENT.PARENT_SEGMENT_ID = GRAND_PARENT.LOCATION_SEGMENT_ID
• CHILD.LOCATION_STRUCTURE_ID = PARENT.LOCATION_STRUCTURE_ID
• CHILD.LOCATION_STRUCTURE_ID = GRAND_PARENT.LOCATION_STRUCTURE_ID

The final predicate is significant: it constrains all three levels to the same location structure (key flexfield structure), preventing cross-structure contamination when multiple location structures are defined. Because the joins are inner joins, a record appears only when all three levels exist. Consequently, the view cannot return top-level or second-level segments, and incomplete branches are silently excluded.

Key Columns

The view projects eleven columns. The following are the most important for reporting:

• LOCATION_STRUCTURE_ID — Identifier of the location key flexfield structure to which all three segments belong. Use this to filter to a single structure.
• CHILD_ID, CHILD_VALUE, CHILD_USER_VALUE, CHILD_QUALIFIER — Identifier, stored (internal) value, user-visible value, and qualifier of the lowest-level segment.
• PARENT_ID, PARENT_VALUE, PARENT_USER_VALUE — Identifier and values of the immediate parent segment.
• GRAND_PARENT_VALUE, GRAND_PARENT_USER_VALUE — Values of the grandparent segment. Note that GRAND_PARENT_ID is not projected.
• COUNTRY_CODE — Taken from the child record; typically populated only for the leaf-level segment.

Common Use Cases and Queries

Typical scenarios include producing three-column hierarchy reports, validating parent-child integrity across location segments, and denormalizing location data for downstream extracts. A representative query selecting all children under a specific grandparent looks like:

• SELECT child_id, child_value, parent_value, grand_parent_value
  FROM apps.ar_loc_three_level_v
  WHERE country_code = 'US'
  ORDER BY grand_parent_value, parent_value, child_value;

To pivot the hierarchy for a given structure:

• SELECT location_structure_id, grand_parent_user_value, parent_user_value, child_user_value
  FROM apps.ar_loc_three_level_v
  WHERE location_structure_id = :structure_id;

Because the view is not indexed independently, performance depends on the underlying AR_LOCATION_VALUES access paths. Queries should filter on LOCATION_STRUCTURE_ID and COUNTRY_CODE where possible, and users requiring deeper or variable-level hierarchies should prefer CONNECT BY queries against the base view rather than this fixed three-level object.