Results for “as_territory_values_all”

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

Overview

AS_TERRITORY_VALUES_ALL is a table within the Oracle E-Business Suite Sales Foundation module (product code AS), owned by the OSM schema. Per the ETRM documentation metadata, the object is marked OBSOLETE to JTF Terr Core, meaning that its historical function — holding territory values used by the Territory Manager to define the qualification criteria for sales territories — has been superseded by the JTF Territory Core (JTF_TERR_*) schema introduced in later EBS releases. In EBS 12.1.1 and 12.2.2, the table is retained principally for backward compatibility and for querying legacy territory assignments defined in earlier releases.

The table records the individual value rows that populate a territory definition. Each row represents a single qualification criterion — a customer, an address, a state or country, an interest code, an inventory item, or a numeric/character range — that is attached to a specific territory via TERRITORY_ID. The heuristic Data Vault classification mined from the foreign-key structure is a link: the table sits between multiple independent business entities (customers, addresses, items, interest codes) and the territory itself, making it a natural candidate for modeling as a relationship (link) construct between hubs.

Key Information Stored

• TERRITORY_VALUE_ID — surrogate primary key of the table, backed by the unique index AS_TERRITORY_VALUES_U1 (and the AS_TERRITORY_VALUES_ALL_PK constraint).
• TERRITORY_ID — the parent territory this value row qualifies; also the leading column of the business-key unique index AS_TERRITORY_VALUES_UK, which distinguishes duplicate value rows within the same territory.
• CUSTOMER_ID — foreign key to RA_CUSTOMERS; identifies a customer-based territory qualifier.
• ADDRESS_ID — foreign key to RA_ADDRESSES_ALL; qualifies the territory by ship-to or bill-to address.
• STATE_PROVINCE_CODE and COUNTRY — geographic qualifiers used when territories are defined by region.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — foreign key pair to MTL_SYSTEM_ITEMS_B; supports item-based territory qualification.
• INTEREST_TYPE_ID, PRIMARY_INTEREST_CODE_ID, SECONDARY_INTEREST_CODE_ID — foreign keys to AS_INTEREST_TYPES_ALL and AS_INTEREST_CODES_ALL respectively, allowing territories to be qualified by customer interest classification.
• LOW_VALUE_CHAR, HIGH_VALUE_CHAR, LOW_VALUE_NUMBER, HIGH_VALUE_NUMBER — range boundary columns that support between-value territory matching.
• ROLLUP_TERRITORY_ID — references the parent territory in a rollup hierarchy.
• SEEDED_QUALIFIER_ID and MATCH_CODE — identify the seeded qualifier definition and matching rule used to evaluate the value row.
• ORG_ID — multi-org operating unit context; CURRENCY_CODE for currency-qualified values.
• Standard WHO audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The most frequent requirement is to inventory all qualification values belonging to a given territory. A typical query joins the value rows back to the territory header:

• Extract all value rows for a territory: SELECT * FROM as_territory_values_all WHERE territory_id = :territory_id;
• Identify customer-based qualifiers with customer names: join CUSTOMER_ID to RA_CUSTOMERS.
• Audit geographic territories: group by COUNTRY and STATE_PROVINCE_CODE.
• Find item-qualified territories by joining INVENTORY_ITEM_ID and ORGANIZATION_ID to MTL_SYSTEM_ITEMS_B.
• Compare value rows that have both low and high boundaries to detect overlapping numeric ranges.
• Reconcile legacy values with the current JTF Territory Core tables during migration or upgrade validation.

Because the table is obsolete, reporting should generally be restricted to historical or reconciliation purposes; new territory configuration should target the JTF_TERR_* schema.

Related Objects

• AS_CHANGED_TERRITORIES_ALL — references this table via TERRITORY_VALUE_ID; tracks territory value changes for audit and synchronization.
• RA_CUSTOMERS — joined on CUSTOMER_ID for customer-qualified territory values.
• RA_ADDRESSES_ALL — joined on ADDRESS_ID for address-qualified values.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID for item-qualified values.
• AS_INTEREST_TYPES_ALL — joined on INTEREST_TYPE_ID to resolve interest type descriptions.
• AS_INTEREST_CODES_ALL — joined on PRIMARY_INTEREST_CODE_ID and SECONDARY_INTEREST_CODE_ID.
• AS_TERRITORIES_ALL (territory header) — the parent entity referenced by TERRITORY_ID and ROLLUP_TERRITORY_ID; not documented as an explicit FK above but implicit in the TERRITORY_ID column and the AS_TERRITORY_VALUES_UK index.