Results for “ar_location_values_old”

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

Overview

AR_LOCATION_VALUES_OLD is a Receivables (AR) schema table that defines the location and description for each of the value sets used in the Sales Tax Location flexfield. It serves as the master repository for geographical and jurisdictional location segments — such as state, county, city, and postal code components — that Receivables and its tax engines use to determine where a transaction occurred and, consequently, how it should be taxed. The table is owned by the AR schema, is marked VALID in the EBS 12.1.1 and 12.2.2 data dictionary, and contains 45 documented columns. The "_OLD" suffix reflects its legacy lineage within the Sales Tax Location flexfield architecture, though it remains actively referenced and populated. From a dimensional-modeling perspective, the ETRM relationship metadata suggests a hub classification heuristically: the table holds the unique business identity of location segments (location segment identifier and value), and multiple downstream tables reference it by foreign key rather than embedding location attributes themselves.

Key Information Stored

The surrogate identifier is LOCATION_SEGMENT_ID, which uniquely identifies each location segment instance. Business-key candidates are exposed through several unique indexes: AR_LOCATION_VALUES_U1 (LOCATION_SEGMENT_QUALIFIER, LOCATION_SEGMENT_VALUE, PARENT_SEGMENT_ID, LOCATION_STRUCTURE_ID), AR_LOCATION_VALUES_U2 (LOCATION_SEGMENT_QUALIFIER, LOCATION_SEGMENT_VALUE, LOCATION_SEGMENT_ID), and AR_LOCATION_VALUES_U4 (LOCATION_SEGMENT_ID alone). The declared primary key in documentation is AR_LOCATION_VALUES_UK1 (LOCATION_SEGMENT_QUALIFIER, LOCATION_SEGMENT_VALUE, PARENT_SEGMENT_ID).

• LOCATION_SEGMENT_ID — surrogate key referenced by transactional and tax tables.
• LOCATION_SEGMENT_QUALIFIER — identifies which segment of the flexfield the value belongs to (e.g., state, county, city).
• LOCATION_SEGMENT_VALUE — the actual segment value (jurisdiction name or code).
• LOCATION_SEGMENT_DESCRIPTION — descriptive text for the segment value.
• LOCATION_SEGMENT_USER_VALUE — the user-friendly value entered during setup.
• PARENT_SEGMENT_ID — self-referencing parent, establishing geographic hierarchy (e.g., city within county).
• LOCATION_STRUCTURE_ID — the Sales Tax Location flexfield structure this value belongs to.
• TAX_ACCOUNT_CCID — accounting code combination for tax posting.
• INTERIM_TAX_CCID, ADJ_CCID, EDISC_CCID, UNEDISC_CCID, FINCHRG_CCID — CCIDs for interim tax, adjustments, earned/unearned discounts, and finance charges.
• GEOGRAPHY_ID, COUNTRY_GEOGRAPHY_ID — references to geography master data for geospatial tax determination.
• Standard who-columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) and 15 ATTRIBUTE flex columns.

Common Use Cases and Queries

The table is queried when resolving transaction ship-to and location identifiers into jurisdictional detail for tax calculation and reporting, and when validating setup of the Sales Tax Location flexfield.

Resolving a transaction line's location segment:

SELECT l.LOCATION_SEGMENT_VALUE, l.LOCATION_SEGMENT_DESCRIPTION
FROM   AR.AR_LOCATION_VALUES_OLD l, RA_CUSTOMER_TRX_LINES_ALL t
WHERE  t.LOCATION_SEGMENT_ID = l.LOCATION_SEGMENT_ID
AND    t.CUSTOMER_TRX_LINE_ID = :line_id;

Traversing the geographic hierarchy via the self-referencing parent:

SELECT child.LOCATION_SEGMENT_VALUE, parent.LOCATION_SEGMENT_VALUE AS parent_value
FROM   AR.AR_LOCATION_VALUES_OLD child, AR.AR_LOCATION_VALUES_OLD parent
WHERE  child.PARENT_SEGMENT_ID = parent.LOCATION_SEGMENT_ID;

Brazilian tax reporting queries — the use case most closely associated with the search term jl_br_ar_tx_locn_all — join JL_BR_AR_TX_LOCN_ALL to this table on SHIP_TO_SEGMENT_ID to produce fiscal location reporting.

Related Objects

The most significant dependent and referenced objects include:

• JL_BR_AR_TX_LOCN_ALL — references AR_LOCATION_VALUES_OLD via SHIP_TO_SEGMENT_ID; the Brazilian localization ship-to location table.
• JL_BR_AR_TX_EXC_ITM_ALL and JL_BR_AR_TX_EXC_FSC_ALL — Brazilian tax exception tables referencing SHIP_TO_SEGMENT_ID.
• JL_ZZ_AR_TX_LOCN_ALL — localization ship-to location table joined on SHIP_TO_SEGMENT_ID.
• RA_CUSTOMER_TRX_LINES_ALL — transaction lines joined on LOCATION_SEGMENT_ID.
• GL_CODE_COMBINATIONS — referenced by TAX_ACCOUNT_CCID and the various discount and adjustment CCID columns.
• AR_LOCATION_VALUES_OLD (self-reference) — via PARENT_SEGMENT_ID for hierarchy.