Search Results csp_part_categories_v




Overview
Explore in AI Chat

Overview

In Oracle E-Business Suite 12.1.1 and 12.2.2, the view CSP_PART_CATEGORIES_V is owned by the APPS schema and resides within the CSP (Spares Management) product module. As its documented description states, the view "shows the spare part categories" used throughout the Spares Management application. It is a reporting and validation object rather than a transactional table, presenting a curated, enabled-only listing of the part category lookup values that drive spare-parts classification, depot repair logic, and downstream reporting.

Because the view is defined over the FND_LOOKUPS dictionary rather than over a bespoke CSP table, it acts as a semantic abstraction layer: consumers query category names and numeric bounds without needing to understand the underlying lookup-code encoding or the date-effective flag logic. This makes it, in effect, the canonical read interface for spare-part category enumeration within the CSP module.

Underlying Base Objects

The documented base objects referenced by this view are FND_LOOKUPS, CSP_PART_PRIORITIES_V, and the FND_GLOBAL package. Its actual logic is driven overwhelmingly by FND_LOOKUPS, filtered on LOOKUP_TYPE = 'CSP_PARTS_CATEGORY'.

The view's text reveals a UNION of two branches:

  • The first branch performs a self-join of FND_LOOKUPS (aliased FL1 and FL2) to compute a LOW value (the row's own lookup code) and a HIGH value — the next-higher lookup code in the same lookup type, capped using LEAST(..., 100).
  • The second branch emits all lookup codes greater than 100, where the LOW and HIGH values are identical (a boundary/sentinel row behaviour).

Both branches restrict to ENABLED_FLAG = 'Y' and honour the lookup's effective date window via SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE) AND NVL(END_DATE_ACTIVE, SYSDATE). This means only currently active, enabled categories are exposed. The reference to CSP_PART_PRIORITIES_V and FND_GLOBAL in the object metadata indicates dependency relationships within the CSP reporting stack, though the primary data source remains the application lookup dictionary.

Key Columns

  • CATEGORY — The human-readable meaning (FL.MEANING) of the spare part category, sourced directly from the FND_LOOKUPS meaning column. This is the descriptive label surfaced to users.
  • LOW — The numeric lower bound of the category band, derived via TO_NUMBER(LOOKUP_CODE). It establishes the inclusive floor of a category's numeric range.
  • HIGH — The numeric upper bound, computed as the least of the next lookup code or 100. Together with LOW, it defines the numeric interval (band) that the category represents.

Collectively these three columns allow applications and reports to translate a numeric spare-parts value into a named category and to understand the range boundaries separating adjacent categories.

Common Use Cases and Queries

Typical use cases include validating a part's category assignment, building category-band lookups in Spares Management reports, and driving pickers or list-of-values components that must show only currently enabled categories.

A simple enumeration of all spare part categories:

SELECT category, low, high
FROM   csp_part_categories_v
ORDER BY low;

To find the category covering a specific numeric value:

SELECT category
FROM   csp_part_categories_v
WHERE  :p_value BETWEEN low AND high;

Because the view already filters by ENABLED_FLAG and effective dates, consumers need not repeat that logic, ensuring consistent, date-aware results across CSP reports and integrations in both 12.1.1 and 12.2.2.