Search Results csp_inv_loc_assignments




Overview
Explore in AI Chat

Overview

CSP_INV_LOC_ASSIGNMENTS is a table in the CSP (Spares Management) product schema of Oracle E-Business Suite. Its documented purpose is to record the assignment of inventory locations to resources. In the Spares Management model, a "resource" typically denotes a field service technician, engineer, or service vehicle, and this table governs which subinventories and stock locators a given resource is authorized to draw material from — for example, when a mobile field technician replenishes spares from a designated depot or van stock location.

The table sits at the intersection of the inventory model (MTL_SECONDARY_INVENTORIES, MTL_ITEM_LOCATIONS) and the resource model (CSP_RESOURCES), functioning as an association or junction object. Based on its foreign key structure and the presence of effective-dating columns and a SECURITY_GROUP_ID, a Data Vault classification heuristic suggests treating this object as a link table — it connects resources to inventory locations and carries relationship attributes such as effective dates and default flags rather than serving as a standalone hub or satellite.

Key Information Stored

The table contains 31 columns. The most significant are:

The documented unique index is on the surrogate primary key. The natural business key is conceptually the tuple of resource, organization, subinventory, and locator, bounded by effective dates.

Common Use Cases and Queries

Typical scenarios include validating which subinventories a technician may pick from, driving replenishment of van stock, and reporting location-to-resource coverage. A representative query joins the assignment to the inventory tables:

SELECT a.resource_id, a.organization_id, a.subinventory_code, a.locator_id, a.default_code
FROM csp_inv_loc_assignments a
WHERE a.resource_id = :resource_id
AND TRUNC(SYSDATE) BETWEEN a.effective_date_start AND NVL(a.effective_date_end, TRUNC(SYSDATE));

Because the table is effective-dated, reported logic should always filter by the active date window. Reporting use cases include auditing default location assignments, identifying resources with no assigned inventory location, and reconciling assignments against discontinued subinventories or locators.

Related Objects

  • MTL_SECONDARY_INVENTORIES — joined on SUBINVENTORY_CODE and ORGANIZATION_ID.
  • MTL_ITEM_LOCATIONS — joined on LOCATOR_ID and ORGANIZATION_ID.
  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID.
  • CSP_RESOURCES — referenced via RESOURCE_ID and RESOURCE_TYPE.
  • MTL_PARAMETERS — provides inventory organization context for ORGANIZATION_ID.

These relationships establish CSP_INV_LOC_ASSIGNMENTS as a security and sourcing control point between the Spares Management resource model and Oracle Inventory.