Search Results css_def_defects_vl

Overview

CSS_DEF_DEFECTS_VL is a translated, view-only ("_VL") database object belonging to the Oracle E-Business Suite product family designated CSS – Support, which is documented in ETRM as obsolete. The view exposes defect records maintained by the legacy Support/Defect Tracking schema, presenting them through a combination of a base table and its translation table so that descriptive text is returned in the session language. Because the "_VL" suffix follows the standard EBS convention, the object is a read-only projection rather than a table that can be written to directly, and it is intended for query, reporting, and integration access instead of transactional data entry.

The ETRM metadata explicitly records the implementation status as "Not implemented in this database." In Oracle EBS 12.1.1 and 12.2.2 environments running current product families, this view is therefore typically absent, and any dependency on it should be treated as an indicator of a non-standard or historical customization.

Underlying Base Objects

The view definition is a two-table join over CSS_DEF_DEFECTS_B (the base table holding the defect attributes) and CSS_DEF_DEFECTS_TL (the translation table holding language-specific descriptive text). The join predicate is B.DEFECT_ID = T.DEFECT_ID AND T.LANGUAGE = USERENV('LANG'), which means only the row matching the caller's session language is returned. All columns are drawn from the base table except SUMMARY, the translated defect summary, which is taken from the TL table.

Although ETRM lists no documented referenced base objects for this view in the 12.2.2 metadata extract, the view text itself establishes the dependency on the two underlying tables above. The absence of an entry in the referenced-objects list reflects the obsolete status of the CSS schema rather than a true lack of dependencies.

Key Columns

• ROW_ID, DEFECT_ID, OBJECT_VERSION_NUMBER – Row identity, primary key, and optimistic locking version.
• DEFECT_NUMBER, CATEGORY_TYPE – User-facing defect identifier and classification.
• FILED_DATE, UPDATED_DATE, RESOLVED_DATE, CLOSED_DATE – Lifecycle timestamps used for aging and turnaround reporting.
• RESOLVED_FLAG, OPEN_FLAG, DELETED_FLAG, PUBLISH_EXT_FLAG, COMMIT_TO_FIX_FLAG – State and disposition indicators.
• PROBLEM_TYPE_ID, PHASE_ID, STATUS_ID, SEVERITY_ID, PRIORITY_ID, FILER_ID – Foreign-key references to defect classification and ownership structures.
• PRODUCT_INV_ITEM_ID, COMPONENT_INV_ITEM_ID, SUB_COMPONENT_INV_ITEM_ID and their revision columns – Product and component context, including first-found and fix-by/fixed-in revision detail.
• ORIGINATING_CUSTOMER_ID, EXT_CUSTOMER_FLAG, TERRITORY_ID – Customer and support-territory attribution.
• ACTUAL_COST, ESTIMATED_COST, ACTUAL_COST_UOM, ESTIMATED_COST_UOM – Cost tracking for defect resolution.
• ESTIMATED_RESOLUTION_DATE, SYSTEM_RESOLUTION_DATE, ESTIMATED_CLOSURE_DATE, SYSTEM_CLOSURE_DATE – Planned versus system-calculated milestone dates.
• ATTRIBUTE1–ATTRIBUTE15, CONTEXT – Descriptive flexfield columns.
• SUMMARY – The translated defect summary, sourced from CSS_DEF_DEFECTS_TL.

Common Use Cases and Queries

The principal use case is reporting on open and historical defects with language-aware summaries. A representative query retrieves currently open defects ordered by severity:

SELECT defect_number, summary, filed_date, severity_id
FROM   css_def_defects_vl
WHERE  open_flag = 'Y'
AND    deleted_flag = 'N'
ORDER BY severity_id, filed_date;

A second common pattern measures resolution turnaround by comparing lifecycle dates:

SELECT defect_number,
       TRUNC(resolved_date - filed_date) AS days_to_resolve
FROM   css_def_defects_vl
WHERE  resolved_date IS NOT NULL;

Because the view filters on USERENV('LANG'), integrators should confirm that the session language is set correctly before extracting data, and should verify the object's existence at runtime given its obsolete, non-implemented status in ETRM.