Search Results sort_seq

Overview

CS_CSZ_SRCH_SORTS is a Service (CS) module table in the Oracle E-Business Suite that stores the sorting specifications applied to saved searches defined in the CS_CSZ_SEARCHES table. Within the EBS Service foundation layer, searches allow users to define reusable query criteria against service-related entities; CS_CSZ_SRCH_SORTS captures the companion ordering rules that determine how the result set of each search is presented. Every row is scoped to a parent search, so the table functions as a dependent configuration detail rather than a standalone master entity.

The table resides in the CS schema and holds twelve documented columns in the 12.2.2 physical schema. Its primary key, CS_CSZ_SRCH_SORTS_PK, is composed of SEARCH_ID and SORT_SEQ, and a unique index, CS_CSZ_SRCH_SORTS_U1, covers the same column pair. Because the entire key of a row is inherited from its parent search record plus an ordinal position, the table is best modeled as a satellite-leaning object in Data Vault terms: it describes attributes of a parent business key (the search) rather than representing an independent hub or a many-to-many link between distinct business entities.

Key Information Stored

The documented columns describe both the identity of each sort rule and the attributes that drive query generation:

• SEARCH_ID — Foreign key to CS_CSZ_SEARCHES, identifying the parent search to which the sort specification belongs. Also the leading column of the primary key and unique index.
• SORT_SEQ — Ordinal position of the sort rule within the search, allowing multiple sort levels (for example, primary, secondary, tertiary ordering). Completes the composite primary key.
• ATTR_ID — Identifier of the attribute being sorted on.
• ATTR_GROUP_ID — Identifier of the attribute group to which the sort attribute belongs, used to disambiguate attributes that share a name across groups.
• ATTR_TYPE — Classification of the sort attribute, indicating the attribute category or data type context used when building the ORDER BY clause.
• SORT_DIRECTION — Direction of ordering (typically ascending or descending) applied to the attribute in this sequence position.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the EBS framework to detect concurrent updates.
• CREATION_DATE, CREATED_BY — Standard WHO columns recording when and by whom the sort row was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO columns recording the most recent modification and the login context of the updater.

The surrogate primary key is a composite natural key (SEARCH_ID, SORT_SEQ); there is no single-column system-generated identifier, and the unique index CS_CSZ_SRCH_SORTS_U1 is therefore the authoritative business-key candidate. The audit columns are managed by the framework and should not be treated as business keys.

Common Use Cases and Queries

Typical usage centers on retrieving the complete ordered sort specification for a search so that it can be rendered in the UI or translated into a SQL ORDER BY clause. A common retrieval pattern is:

• SELECT SEARCH_ID, SORT_SEQ, ATTR_ID, ATTR_GROUP_ID, ATTR_TYPE, SORT_DIRECTION FROM CS.CS_CSZ_SRCH_SORTS WHERE SEARCH_ID = :p_search_id ORDER BY SORT_SEQ;

Reporting and diagnostics scenarios include auditing which attributes are most frequently used for sorting across all saved searches, verifying that no search carries duplicate SORT_SEQ values (guarded by CS_CSZ_SRCH_SORTS_U1), and confirming that SORT_DIRECTION values conform to expected conventions (for example, ASC/DESC). Administrators also use this data when troubleshooting search result ordering, since a missing or malformed sort row causes the application to fall back to default ordering. Because the table is small, configuration-oriented, and rarely updated, it is safe to query directly rather than through the application tier.

Related Objects

The most significant related objects, grounded in the documented constraints, are:

• CS_CSZ_SEARCHES — Parent table referenced by CS_CSZ_SRCH_SORTS.SEARCH_ID; the search definition that owns one or more sort rows.
• CS_CSZ_SRCH_SORTS_PK — Primary key constraint on (SEARCH_ID, SORT_SEQ).
• CS_CSZ_SRCH_SORTS_U1 — Unique index on (SEARCH_ID, SORT_SEQ), the business-key candidate.
• CS_CSZ_SRCH_SORTS — Base table from which any CS Service search-definition views are constructed.
• CS_CSZ_SRCH_FILTERS / companion search-detail tables — Sibling configuration tables that store filter criteria for the same parent search, commonly joined on SEARCH_ID to present a complete search definition.

No secondary foreign keys beyond SEARCH_ID are documented in the ETRM metadata, so any associations to attribute-definition tables should be validated against the specific EBS release before being relied upon in scripts.