Search Results default_code




Overview
Explore in AI Chat

Overview

The CSP_INV_LOC_ASSIGNMENTS_V view is a reporting and integration object owned by the APPS schema within Oracle E-Business Suite. It belongs to the CSP product family, commonly identified as Spares Management, and its documented purpose is to show inventory locations assigned to resources. In functional terms, the view answers the question of which inventory organization, subinventory, and locator combinations are associated with a given resource, such as a field service engineer, maintenance technician, or service organization.

In EBS 12.1.1 and 12.2.2, CSP uses these assignments to drive spares sourcing, material issue defaults, and replenishment logic for depot and field inventory. Because the view exposes effective dating, a resource's inventory location assignment can change over time without losing historical context. The view carries a VALID status and is exposed as a standard APPS object, which means it can be queried directly from SQL*Plus, used in BI Publisher data templates, referenced in Oracle Reports or OAF pages, and included in custom integrations through the applications database user.

Underlying Base Objects

The view is defined over a single base object: the table CSP_INV_LOC_ASSIGNMENTS, referenced through a synonym. The column list of the view is a one-to-one projection of the base table, including all audit columns, the business columns, the DEFAULT_CODE flag, and the fifteen descriptive flexfield ATTRIBUTE columns plus ATTRIBUTE_CATEGORY. Because no joins, aggregations, or filters are applied, the view exists primarily as an access layer rather than as a transformation.

This has two practical consequences. First, row counts and cardinality match the base table exactly, and no duplicate elimination occurs. Second, any insert, update, or delete performed through the view is a direct operation on CSP_INV_LOC_ASSIGNMENTS subject to the base table's constraints and security. The view text is documented in the ETRM metadata, confirming that CSP_INV_LOC_ASSIGNMENT_ID is exposed from the same-named column in the base table.

Key Columns

  • CSP_INV_LOC_ASSIGNMENT_ID — primary identifier for each inventory location assignment record.
  • RESOURCE_ID and RESOURCE_TYPE — identify the resource and the category of resource to which the location is assigned.
  • ORGANIZATION_ID — the inventory organization in which the spares are held.
  • SUBINVENTORY_CODE — the subinventory within the organization.
  • LOCATOR_ID — the specific locator (bin) reference, where locator control applies.
  • DEFAULT_CODE — indicates whether this assignment is the default source for the resource. This is the column most often involved when users search for "default_code," because it determines which location is proposed when a material transaction is initiated for the resource.
  • EFFECTIVE_DATE_START and EFFECTIVE_DATE_END — define the active window of the assignment; consumers should always filter on these to avoid selecting expired or not-yet-effective rows.
  • Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — standard Who columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — descriptive flexfield storage.

Common Use Cases and Queries

Typical scenarios include determining the default issue location for a technician, validating that a resource has any active inventory assignment before material issue, and reporting on assignments that have expired. The following queries reflect those needs.

To find the current default location for a resource:

SELECT resource_id, organization_id, subinventory_code, locator_id
FROM   csp_inv_loc_assignments_v
WHERE  resource_id = :p_resource_id
AND    default_code = 'Y'
AND    TRUNC(SYSDATE) BETWEEN effective_date_start
                          AND NVL(effective_date_end, TRUNC(SYSDATE));

To list all active assignments for a resource with organization and subinventory detail:

SELECT a.resource_id, a.organization_id, a.subinventory_code,
       a.locator_id, a.default_code,
       a.effective_date_start, a.effective_date_end
FROM   csp_inv_loc_assignments_v a
WHERE  a.resource_id = :p_resource_id
AND    TRUNC(SYSDATE) BETWEEN a.effective_date_start
                          AND NVL(a.effective_date_end, TRUNC(SYSDATE))
ORDER  BY a.default_code DESC, a.organization_id;

To detect resources whose assignments have lapsed (useful for data cleanup):

SELECT v.resource_id, v.organization_id, v.subinventory_code
FROM   csp_inv_loc_assignments_v v
WHERE  v.effective_date_end < TRUNC(SYSDATE);

When using this view, always constrain on RESOURCE_ID or ORGANIZATION_ID where possible, since the base table can contain multiple historical and future-dated rows per resource. Filtering on DEFAULT_CODE = 'Y' alone is insufficient without the effective date predicate, because superseded rows may also carry a default flag from a prior period.