Search Results as_terr_resources_tmp_u1

Overview

OSM.AS_TERR_RESOURCES_TMP is a temporary working table in the Oracle E-Business Suite Territory Management module, owned by the OSM schema and registered in FND Design Data as AS.AS_TERR_RESOURCES_TMP. Its documented purpose is to hold the intermediate result set produced during territory assignment processing. When the territory assignment engine evaluates the matching rules that associate parties, party sites, leads, resources, and resource groups with territories, it stages the qualifying combinations in this table before the results are consumed or promoted to their final destinations. The table is therefore transient by design: rows are meaningful only within the lifecycle of a concurrent territory assignment run, and the storage attributes reflect that. It resides in the APPS_TS_NOLOGGING tablespace, which suppresses redo generation for bulk interim writes, and it carries standard concurrent program audit columns so that each staged row can be traced back to the request that produced it.

Under a heuristic Data Vault classification mined from its foreign key structure, this object is best described as a link table. Its grain is an association among a party or party site, a lead, a territory, and a resource or resource group, which is precisely the many-to-many connectivity that link entities model. This classification is a modeling suggestion derived from the FK topology rather than a documented Oracle designation.

Key Information Stored

The table contains thirteen documented columns. The business-key candidates are captured by the unique index AS_TERR_RESOURCES_TMP_U1, which spans six columns and defines the grain of a single staged assignment:

• TERR_ID — territory identifier; joins to JTF_TERR_ALL.
• RESOURCE_ID — resource identifier; joins to JTF_RS_RESOURCE_EXTNS.
• RESOURCE_GROUP_ID — group identifier; joins to JTF_RS_GROUPS_B.
• PARTY_ID — party identifier; joins to HZ_PARTIES.
• PARTY_SITE_ID — party site identifier; joins to HZ_PARTY_SITES.
• LEAD_ID — opportunity or lead identifier; joins to AS_LEADS_ALL.

Supporting descriptive and control columns include RESOURCE_TYPE, a VARCHAR2 (60) that qualifies the nature of the assigned resource, and FULL_ACCESS_FLAG, which indicates whether the resource receives unrestricted access to the matched territory. The concurrent audit columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — identify the last concurrent program to touch the row and when it ran. SECURITY_GROUP_ID carries the subscriber identifier used by CRM Online Services for multi-tenant isolation. No separate surrogate primary key column is documented; the uniqueness of a staged row is expressed entirely through the composite unique index, and DBAs should note that the OSM schema does not enforce a single-column system-generated key on this table.

Common Use Cases and Queries

Typical usage centers on diagnosing or validating the output of a territory assignment run. Because the table is named _TMP, the most common production query is a count-by-territory check to confirm that the assignment program produced expected volumes:

• Reconciling assignments by territory: SELECT TERR_ID, COUNT(*) FROM OSM.AS_TERR_RESOURCES_TMP GROUP BY TERR_ID;
• Confirming resource coverage: joining RESOURCE_ID to JTF_RS_RESOURCE_EXTNS to display resource names alongside the territory and party matched.
• Auditing a specific run: filtering on REQUEST_ID and PROGRAM_UPDATE_DATE to isolate the rows written by a single concurrent request.
• Identifying over-broad grants: querying rows where FULL_ACCESS_FLAG is set for a given territory to review resources holding unrestricted access.
• Detecting duplicates or rule overlap: grouping by the full unique index column list to find combinations appearing more than once across runs.

Queries should always constrain on TERR_ID, REQUEST_ID, or PARTY_ID, since the table can hold large volumes of interim rows and is intended to be truncated or reused between executions.

Related Objects

Oracle documents that OSM.AS_TERR_RESOURCES_TMP references no database objects directly and is referenced only by the APPS synonym AS_TERR_RESOURCES_TMP, through which application code accesses it. The significant relationships are the foreign keys from its business-key columns, each of which identifies a master entity in the territory model:

• JTF_TERR_ALL via TERR_ID — the territory definition matched.
• JTF_RS_RESOURCE_EXTNS via RESOURCE_ID — the assigned resource.
• JTF_RS_GROUPS_B via RESOURCE_GROUP_ID — the assigned resource group.
• HZ_PARTIES via PARTY_ID — the party being assigned.
• HZ_PARTY_SITES via PARTY_SITE_ID — the specific party site in scope.
• AS_LEADS_ALL via LEAD_ID — the lead or opportunity record.
• FND_SECURITY_GROUPS via SECURITY_GROUP_ID — the subscriber context, relevant only in CRM Online Services deployments.