Results for “sql_statement”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CS_QM_FILTERS table resides in the CS (Service) product schema of Oracle E-Business Suite and belongs to the Quality Management (QM) subsystem used by Oracle TeleService and related field service modules. It stores the pre-seeded definitions of filters that drive list-of-values (LOV) behavior, attribute-based searching, and dynamic data restriction mechanisms within the Service responsibility. Each row represents a single filter definition, capturing its identifier, code, display name, the SQL statement used to resolve its LOV, and various control flags governing whether the filter is seeded, active, or identifier-based.

Because the combination of FILTER_CODE and FILTER_NAME is documented as unique, the table functions as a controlled reference list rather than a high-volume transactional entity. Most rows are shipped with the application as seed data and are rarely modified by customers, though administrative users may extend the set with custom filters.

From a Data Vault modeling perspective, the metadata's heuristic classification is hub-leaning. In practical terms this suggests CS_QM_FILTERS is best treated as a hub-like reference entity keyed on its true business identity, with descriptive and SQL-definition attributes potentially modeled as a satellite that tracks changes over time through the audit columns.

Key Information Stored

The table is documented with eighteen physical columns. The most significant include:

• FILTER_ID — The surrogate primary key enforced by the CS_QM_FILTERS_PK constraint. It is the stable internal identifier used by all dependent objects and joins.
• FILTER_CODE — The short business code for the filter. Along with FILTER_NAME it participates in the unique constraint CS_QM_FILTERS_UK1, making it a strong business-key candidate.
• FILTER_NAME — The user-facing name of the filter. The documentation explicitly notes that FILTER_CODE combined with FILTER_NAME is unique, which is directly relevant to searches referencing "filer_name" (a common misspelling of FILTER_NAME).
• DESCRIPTION — Free-text explanation of the filter's purpose, useful for metadata reporting.
• ACTIVE_FLAG — Indicates whether the filter is currently enabled and available for use at runtime.
• SEEDED_FLAG — Distinguishes Oracle-provided seed rows from customer-created entries.
• ID_BASED_FLAG — Signals whether the filter operates on internal identifiers rather than values, affecting how its LOV resolves.
• SQL_STATEMENT_FOR_LOV and SQL_STATEMENT — The SQL bodies that drive the filter's LOV and its underlying selection logic.
• DEFAULT_COLUMN_NAME and DEFAULT_COLUMN_DATATYPE — The column and datatype the filter defaults to when applied.
• OBJECT_VERSION_NUMBER — The optimistic locking token used by the OAF framework for concurrent update protection.
• ZD_EDITION_NAME — The editioning column introduced for EBS 12.2 online patching, part of the unique index CS_QM_FILTERS_U1 referenced with FILTER_ID.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN provide standard WHO information for change tracking.

Common Use Cases and Queries

Typical uses include identifying which filters are active, locating a filter by its code or display name, and auditing the SQL definitions that drive runtime LOVs. A frequent query pattern resolves a filter by name or code:

• SELECT filter_id, filter_code, filter_name, active_flag FROM cs.cs_qm_filters WHERE filter_name = :name;
• SELECT filter_id, filter_code FROM cs.cs_qm_filters WHERE filter_code = :code AND active_flag = 'Y';
• SELECT f.filter_name, g.function_id FROM cs.cs_qm_filters f, cs.cs_qm_function_filters g WHERE f.filter_id = g.filter_id;

Reporting scenarios include enumerating seeded versus custom filters, reviewing SQL_STATEMENT bodies during upgrade impact analysis, and joining to CS_QM_FUNCTION_FILTERS to determine which functions consume a given filter.

Related Objects

The principal dependent object is CS_QM_FUNCTION_FILTERS, whose FILTER_ID column references CS_QM_FILTERS, forming the association between filters and the Service functions they serve. The primary key CS_QM_FILTERS_PK on FILTER_ID and the unique constraints CS_QM_FILTERS_UK1 (FILTER_CODE, FILTER_NAME) and CS_QM_FILTERS_U1 (FILTER_ID, ZD_EDITION_NAME) govern integrity and editioning. Related Service QM reference tables and the seeded filter definitions themselves depend on this table for their operation.