Search Results as_changed_territories_pk

Overview

The AS_CHANGED_TERRITORIES_ALL table is a critical data object within the Oracle E-Business Suite (EBS) Sales Foundation module (AS). It functions as a logging and tracking mechanism for territory modifications within the application's sales territory management framework. Its primary role is to maintain a historical record of territory changes, which is essential for auditing, data synchronization, and triggering downstream processes such as territory reassignments for opportunities or accounts. The table is owned by the OSM schema and is designed to support multi-organization access through its "_ALL" suffix, indicating it can store data for multiple operating units.

Key Information Stored

The table's structure is centered around identifying the territory that was altered and linking it to the relevant salesforce entity. Based on the provided metadata, the core columns include the primary and foreign keys. The TERRITORY_ID column is the primary identifier for the changed territory and serves as the table's primary key (AS_CHANGED_TERRITORIES_PK). The TRANSFER_TO_SALESFORCE_ID column is a foreign key that links the changed territory record to a specific salesforce within the AS_SALESFORCE table. While the explicit ETRM excerpt does not list descriptive columns like a timestamp or change type, such tables typically include audit columns (e.g., LAST_UPDATE_DATE, CREATED_BY) to track when the change was logged and by whom.

Common Use Cases and Queries

A primary use case is generating audit reports for territory management changes over a specific period. Administrators can query this table to understand the frequency and scope of territory modifications. Another key scenario involves integration with territory realignment workflows; processes that reassign sales records based on new territory definitions likely consume data from this table to identify which territories require record updates. A common query pattern involves joining to the AS_TERRITORIES_ALL table to get descriptive territory information.

SELECT cta.territory_id,
       ata.name territory_name,
       cta.transfer_to_salesforce_id,
       cta.last_update_date
FROM   osm.as_changed_territories_all cta,
       osm.as_territories_all ata
WHERE  cta.territory_id = ata.territory_id
AND    cta.last_update_date > SYSDATE - 30;

Related Objects

The table has documented foreign key relationships with two core Sales Foundation tables, as per the provided metadata:

• AS_TERRITORIES_ALL: The TERRITORY_ID column in AS_CHANGED_TERRITORIES_ALL references the TERRITORY_ID in AS_TERRITORIES_ALL. This join retrieves the master definition of the territory that was changed.
• AS_SALESFORCE: The TRANSFER_TO_SALESFORCE_ID column in AS_CHANGED_TERRITORIES_ALL references a column (typically SALESFORCE_ID) in the AS_SALESFORCE table. This relationship links the territory change to the specific salesforce entity responsible for or affected by the change.

These relationships are fundamental for any query that requires descriptive data beyond the territory identifier, ensuring referential integrity within the territory management data model.