Search Results as_territory_accesses_u1

Overview

OSM.AS_TERRITORY_ACCESSES is an intersection (association) table within the Oracle E-Business Suite Territory Management and Access Management schema. As documented in the ETRM repository, it serves as the intersection table between JTF_TERR_ALL (territory definitions) and AS_ACCESSES_ALL (access definitions), enabling the assignment of territory authority to specific access records. This table is the physical mechanism by which territory-based access control and security models are instantiated in Oracle EBS Releases 12.1.1 and 12.2.2.

From a heuristic Data Vault modeling perspective, the mined FK structure classifies this object as a link table. It resolves a many-to-many relationship between territories and accesses, carrying no descriptive attributes of its own beyond standard audit columns. The design is typical of EBS intersection tables: a composite unique key, foreign keys to both parent entities, and the Standard Who columns.

Key Information Stored

The table contains fourteen documented columns, of which the following are the most significant:

• ACCESS_ID (NUMBER) — Identifier of the access record; foreign key to AS_ACCESSES_ALL_ALL. Part of the primary key.
• TERRITORY_ID (NUMBER) — Identifier of the territory; foreign key to JTF_TERR_ALL. Part of the primary key.
• USER_TERRITORY_ID (NUMBER) — Retained for backward compatibility; documented as "No longer used."
• SECURITY_GROUP_ID (NUMBER) — Subscriber identifier used for CRM Online Services; foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER (NUMBER) — Optimistic locking counter used by the OAF/ADF framework during updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Who audit columns tracking row provenance.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit context identifying the last batch process to modify the row.

The surrogate primary key is composite, defined by AS_TERRITORY_ACCESSES_PK (ACCESS_ID, TERRITORY_ID). The unique index AS_TERRITORY_ACCESSES_U1 — the object the user searched for — is defined on the same two columns and therefore represents the business-key candidate enforcing one territory-access pairing per row. Two nonunique indexes support lookup by USER_TERRITORY_ID (AS_TERRITORY_ACCESSES_N1) and by TERRITORY_ID alone (AS_TERRITORY_ACCESSES_N2). All indexes reside in APPS_TS_TX_IDX; the table itself resides in APPS_TS_TX_DATA with PCTFREE 10.

Common Use Cases and Queries

This table is queried when determining which accesses are granted to a territory, or conversely which territories apply to a given access. Typical reporting includes territory assignment auditing, security model reviews, and user-access matrices.

List all accesses for a given territory:

SELECT a.access_id, a.territory_id, a.last_update_date
FROM   osm.as_territory_accesses a
WHERE  a.territory_id = :p_territory_id;

Join to parent entities to produce a meaningful report:

SELECT t.territory_id, t.name, x.access_id, ac.name
FROM   osm.as_territory_accesses x,
       jtf_terr_all            t,
       as_accesses_all         ac
WHERE  x.territory_id = t.territory_id
AND    x.access_id    = ac.access_id;

Detect duplicate business keys (a validation query enabled by the U1 index):

SELECT access_id, territory_id, COUNT(*)
FROM   osm.as_territory_accesses
GROUP  BY access_id, territory_id
HAVING COUNT(*) > 1;

Related Objects

• JTF_TERR_ALL — Territory master; joined on TERRITORY_ID.
• AS_ACCESSES_ALL / AS_ACCESSES_ALL_ALL — Access master; joined on ACCESS_ID.
• FND_SECURITY_GROUPS — Security group registry; joined on SECURITY_GROUP_ID.
• APPS.AS_TERRITORY_ACCESSES — The APPS-layer synonym/view exposing this table to application code.
• AS_TERRITORY_ACCESSES_PK — Composite primary key constraint on (ACCESS_ID, TERRITORY_ID).
• AS_TERRITORY_ACCESSES_U1 — Unique index on the same columns, serving as the business-key candidate.