Search Results defect_tracking_on_flag




Overview
Explore in AI Chat

Overview

CSS_DEF_PRODUCTS_VL is a view in the Oracle E-Business Suite CSS (Support) product family, historically associated with defect tracking and quality-related inventory reporting. In the ETRM metadata, the object is flagged as part of CSS - Support (obsolete), and it is explicitly noted as "Not implemented in this database" in the source documentation. This means the view may exist as a definition within the ETRM reference library but is not deployed in every environment. Its role is to present a filtered, denormalized list of inventory items that have defect tracking enabled, exposing product-oriented attributes for reporting and integration. The view is named with the "_VL" suffix, indicating that it is a value-list style view typically used to support List of Values (LOV) poplists and lookup-driven user interfaces, though it can also be queried directly for reporting. Because it filters on DEFECT_TRACKING_ON_FLAG = 'Y', it is scoped to items relevant to defect tracking workflows rather than the full item master.

Underlying Base Objects

According to the view text, the sole documented base object is MTL_SYSTEM_ITEMS_VL, the value-list view over the item master that joins item-level attributes from MTL_SYSTEM_ITEMS_B and translations from MTL_SYSTEM_ITEMS_TL. The ETRM "referenced base objects" entry lists none explicitly, but the SQL is unambiguous. The view joins the item master to an organization filter obtained from CSS_DEF_MTL_API.GET_INV_ORGANIZATION_ID, a PL/SQL API function that returns the inventory organization identifier relevant to the CSS defect-tracking context. Consequently, the view is effectively a single-organization slice of the item master, restricted to rows where defect tracking is active. Because it draws on the _VL layer of the item master, it inherits translated description fields and concatenated key flexfield segments.

Key Columns

The view exposes four columns in its SELECT list, though the documented column list uses alternate names:

  • PRODUCT_INV_ITEM_ID — Maps to INVENTORY_ITEM_ID, the unique identifier for the inventory item. This is the primary join key to other item-related tables.
  • PRODUCT_NUMBER — Maps to CONCATENATED_SEGMENTS, the concatenated key flexfield segments that form the user-visible item number.
  • PRODUCT_DESCRIPTION — Maps to DESCRIPTION, the translated item description.
  • PRODUCT_REVISION_CONTROL_CODE — Maps to REVISION_QTY_CONTROL_CODE, the code controlling whether revision and/or quantity control applies to the item. This is the column most relevant to the user's search term, "revision_qty_control_code." Valid values correspond to the item master's revision/quantity control scheme (for example, revision control, quantity control, or both).

The alias mapping indicates that consumers of this view should expect product-prefixed column names rather than the raw item master column names.

Common Use Cases and Queries

The primary use case is populating defect-tracking product lists and validating that a selected item is eligible for defect tracking within the correct inventory organization. A typical query is:

SELECT PRODUCT_INV_ITEM_ID,
       PRODUCT_NUMBER,
       PRODUCT_DESCRIPTION,
       PRODUCT_REVISION_CONTROL_CODE
FROM   CSS_DEF_PRODUCTS_VL
WHERE  PRODUCT_NUMBER LIKE :search || '%';

Because the view has no parameters, callers rely on its built-in organization and defect-tracking filters. A reporting query to identify items under revision control could add:

SELECT PRODUCT_NUMBER, PRODUCT_REVISION_CONTROL_CODE
FROM   CSS_DEF_PRODUCTS_VL
WHERE  PRODUCT_REVISION_CONTROL_CODE IN ('R', 'B');

Given the "obsolete" classification and the "not implemented" note, administrators should verify deployment status in their specific instance before relying on this view. Where it is absent, equivalent results can be produced by querying MTL_SYSTEM_ITEMS_VL directly with the same organization and defect-tracking predicates.