Results for “internal_upgrade_territory_id”

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

Overview

AS_TERRITORIES_ALL is a table owned by the OSM schema within the AS – Sales Foundation product family of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. Its documented purpose is to hold territory definitions, although the ETRM metadata explicitly flags the object as obsolete, indicating that territory functionality has been migrated out to the JTF Territory Core model. Despite this obsolescence, the table remains VALID in the data dictionary and retains a fully documented physical schema of 47 columns, making it relevant for historical reporting, upgrade reconciliation, and legacy integration analysis.

From a Data Vault modeling perspective, the ETRM metadata classifies AS_TERRITORIES_ALL as satellite-leaning. This heuristic reflects the table's role as a descriptive store of territory attributes anchored to a territory identifier, with supporting foreign key links to type, group, and salesforce reference data. Analysts designing a Data Vault representation should treat TERRITORY_ID as the candidate hub business key, and model the descriptive and change-tracking columns as satellite attributes.

Key Information Stored

The primary key is enforced through the AS_TERRITORIES_PK constraint on TERRITORY_ID, and the unique index AS_TERRITORIES_U1 also covers TERRITORY_ID, confirming it as the single business-key candidate. Other significant columns include:

• TERRITORY_ID – surrogate primary key uniquely identifying each territory definition.
• NAME – the descriptive territory name used in reporting and selection.
• ENABLED_FLAG, ACTIVATED_FLAG, DEACTIVATED_FLAG – lifecycle and status indicators.
• START_DATE_ACTIVE and END_DATE_ACTIVE – date-range boundaries controlling when a territory is effective.
• TERRITORY_TYPE_ID – foreign key to AS_TERRITORY_TYPES_ALL, categorizing the territory.
• TERRITORY_GROUP_ID – foreign key to AS_TERRITORY_GROUPS_ALL, grouping related territories.
• SALESFORCE_ID – foreign key to AS_SALESFORCE, tying the territory to a sales organization.
• ROLLUP_FLAG and USE_TO_ASSIGN_FLAG – behavior flags for rollup and assignment logic.
• NUM_QUALIFIERS, RANK, ACCESS_TYPE – qualifier counts, ranking, and access semantics.
• EXPANDED_FROM_TERR_ID and EXPAND_DATE – self-referencing expansion lineage.
• ORG_ID – multi-org operating unit context.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY – the standard EBS descriptive flexfield (DFF) columns.
• INTERNAL_UPGRADE_TERRITORY_ID – control column supporting migration/upgrade.

Common Use Cases and Queries

Because the table is obsolete but still valid, its principal use is retrospective analysis and reconciliation during upgrades from earlier AS territory models to JTF Territory Core. A typical reporting query retrieves active territories with their type and group context:

SELECT t.TERRITORY_ID, t.NAME, t.ENABLED_FLAG, ty.NAME territory_type, g.NAME group_name FROM OSM.AS_TERRITORIES_ALL t JOIN OSM.AS_TERRITORY_TYPES_ALL ty ON t.TERRITORY_TYPE_ID = ty.TERRITORY_TYPE_ID LEFT JOIN OSM.AS_TERRITORY_GROUPS_ALL g ON t.TERRITORY_GROUP_ID = g.TERRITORY_GROUP_ID WHERE t.ORG_ID = :org_id AND (t.END_DATE_ACTIVE IS NULL OR t.END_DATE_ACTIVE >= SYSDATE);

Other scenarios include tracing expansion lineage via the self-referencing EXPANDED_FROM_TERR_ID column, auditing change history through AS_CHANGED_TERRITORIES_ALL, and validating rollup hierarchies against AS_TERRITORY_ROLLUPS.

Related Objects

• AS_TERRITORY_TYPES_ALL – joined on TERRITORY_TYPE_ID, supplies territory classifications.
• AS_TERRITORY_GROUPS_ALL – joined on TERRITORY_GROUP_ID, supplies grouping context.
• AS_SALESFORCE – joined on SALESFORCE_ID, links territories to sales forces.
• AS_CHANGED_TERRITORIES_ALL – references AS_TERRITORIES_ALL.TERRITORY_ID, capturing change history.
• AS_TERRITORY_ROLLUPS – references both TERRITORY_ID and ROLLUP_TERRITORY_ID, defining hierarchy relationships.
• AS_TERRITORIES_ALL (self-reference) – EXPANDED_FROM_TERR_ID links a territory to its expansion source.