Results for “csf_dc_queries_vl”

Because the view follows Oracle's "_VL" (view, language) naming convention, it blends the transactional or definitional attributes held in the base table with the translated name and description columns contributed by the translation table. This makes it suitable for both display and programmatic use, and it is the object that Dispatch Center forms, LOVs, and dependent reports query when rendering or filtering the list of saved queries available to a dispatcher.

Underlying Base Objects

The view is defined over two documented objects, both exposed as synonyms in the APPS schema:

• CSF_DC_QUERIES_B — the base (non-translated) table, aliased as CDQB in the view text. It supplies the query identifier, who-columns, object version, the WHERE_CLAUSE text, owning user, seeded flag, and effective date range.
• CSF_DC_QUERIES_TL — the translation table, aliased as CDQT. It supplies NAME and DESCRIPTION for the applicable language.

The two are joined on QUERY_ID, with the translation side additionally constrained by CDQT.LANGUAGE = USERENV('LANG'). This ensures exactly one translation row is returned per query, matching the language of the active session, which is the standard pattern for MLS views in EBS. The view also exposes the base table's ROWID as ROW_ID, a convention used by many EBS views to support forms-based update and lock semantics.

Key Columns

• ROW_ID — the ROWID of the underlying CSF_DC_QUERIES_B row, used for direct row identification.
• QUERY_ID — the unique identifier of the saved dispatch query; the join key between the base and translation tables.
• NAME / DESCRIPTION — the language-resolved display name and description of the query.
• WHERE_CLAUSE — the stored SQL predicate fragment that defines which dispatch records the query selects.
• USER_ID — the user who owns the query, supporting per-user query visibility.
• SEEDED_FLAG — indicates whether the query is a seeded (predefined) system query or a user-created one.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range controlling when the query is active.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS who-columns for auditing.
• OBJECT_VERSION_NUMBER — used for optimistic locking in the Dispatch Center forms.

Common Use Cases and Queries

Typical uses include listing available queries for a dispatcher's LOV, reporting on seeded versus user-defined queries, and auditing WHERE_CLAUSE definitions. A simple listing for the current user or all active seeded queries can be written as follows:

• SELECT query_id, name, description, where_clause, seeded_flag FROM csf_dc_queries_vl WHERE seeded_flag = 'Y'; — returns all predefined dispatch queries.
• SELECT query_id, name FROM csf_dc_queries_vl WHERE user_id = :p_user_id AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE) ORDER BY name; — returns a user's active queries.
• SELECT name, where_clause FROM csf_dc_queries_vl WHERE UPPER(name) LIKE '%DISPATCH%'; — search queries by name for administrative review.

Because NAME and DESCRIPTION are resolved via USERENV('LANG'), join or filter behavior is language-aware without requiring the caller to reference the translation table directly. This view should be treated as read-only; maintenance of dispatch queries is performed against the underlying _B and _TL tables through the Dispatch Center application forms.