Search Results csf_res_inv_assignments_v




Overview
Explore in AI Chat

Overview

CSF_RES_INV_ASSIGNMENTS_V is a VALID APPS-owned view within the CSF (Field Service) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as the base query for a form block of the CSFCMRSL form, the Field Service responsibility screen used to maintain inventory location assignments for service resources. The view presents a denormalized, security-aware projection of inventory location assignment data by joining the underlying assignment entity to the global resource directory, thereby exposing the human-readable resource name alongside the assignment's operational attributes. Its role in reporting and integration is to provide a single, consumable source for queries that must resolve which subinventory a field service resource is assigned to, whether that assignment is the default, and over what effective date range it applies. Because the join already resolves RESOURCE_NAME, consumers avoid re-implementing the resource-directory lookup themselves. The view also participates in Oracle's Multi-Org and security model indirectly through its reference to HR_GENERAL and HR_SECURITY packages, which are used by the resource views it depends upon.

Underlying Base Objects

The documented base objects are:

  • CSP_INV_LOC_ASSIGNMENTS_V (VIEW) — the primary driving entity supplying the CSP_INV_LOC_ASSIGNMENT_ID key and all assignment attributes.
  • JTF_RS_ALL_RESOURCES_VL (VIEW) — the resource directory view supplying RESOURCE_NAME; joined on RESOURCE_ID and RESOURCE_TYPE.
  • HR_GENERAL (PACKAGE) — invoked by the underlying resource views to resolve user and business-group context.
  • HR_SECURITY (PACKAGE) — invoked by the underlying resource views to enforce row-level security on resource records.

The view text is a simple two-table equijoin: FROM CSP_INV_LOC_ASSIGNMENTS_V ILA, JTF_RS_ALL_RESOURCES_VL RAR WHERE ILA.RESOURCE_ID = RAR.RESOURCE_ID AND ILA.RESOURCE_TYPE = RAR.RESOURCE_TYPE. Note that the join is on both RESOURCE_ID and RESOURCE_TYPE, which is significant because resource identifiers in the JTF resource model are only unique within a given resource type. The HR_SECURITY dependency means that the rows visible for a given query are filtered by the resource security profile of the session or the runtime user, so the same SQL issued under different responsibilities or users may return different result sets.

Key Columns

  • CSP_INV_LOC_ASSIGNMENT_ID — the primary key of the inventory location assignment. This is the column most frequently referenced in searches and is the identifier used by the base entity and any dependent foreign keys.
  • RESOURCE_ID / RESOURCE_TYPE — the composite logical key identifying the service resource to which the assignment belongs.
  • RESOURCE_NAME — the descriptive name of the resource, sourced from JTF_RS_ALL_RESOURCES_VL.
  • SUBINVENTORY_CODE — the subinventory to which the resource is assigned.
  • DEFAULT_CODE — indicates whether the assignment is the resource's default location assignment.
  • EFFECTIVE_DATE_START / EFFECTIVE_DATE_END — the date range during which the assignment is active, enabling point-in-time or as-of queries.
  • ORGANIZATION_ID — the inventory organization context for the subinventory assignment.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO columns.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns.

Common Use Cases and Queries

Typical scenarios include validating which subinventory a field service resource should default to, auditing assignment effective dates, and extracting assignments for downstream integration into scheduling or inventory replenishment logic. Standalone SQL is often used for reconciliation because the form exposes only a single resource's assignments at a time.

To resolve assignments for resources by name:

SELECT resource_id, resource_type, resource_name, subinventory_code, default_code, effective_date_start, effective_date_end, organization_id FROM csf_res_inv_assignments_v WHERE resource_name LIKE 'JOHN%';

To find the active default assignment for a resource on a given date:

SELECT csp_inv_loc_assignment_id, resource_name, subinventory_code FROM csf_res_inv_assignments_v WHERE resource_id = :p_resource_id AND resource_type = :p_resource_type AND default_code = 'Y' AND :p_as_of_date BETWEEN effective_date_start AND NVL(effective_date_end, :p_as_of_date);

Because the view inherits HR_SECURITY filtering from its resource dependency, queries intended for administrative extraction should be executed under a responsibility whose security profile permits visibility of all target resources; otherwise rows may be silently omitted.