Search Results wms_sort_criteria




Overview
Explore in AI Chat

Overview

The WMS_SORT_CRITERIA table is a core data object within the Warehouse Management System (WMS) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It functions as a child table that defines the specific, ordered criteria used to sort tasks, such as pick slips or putaway assignments, generated by WMS rules. The table stores the sequence and parameters that determine the priority and logical order in which tasks are presented to warehouse operators, directly impacting operational efficiency and workflow optimization. Its existence is integral to the rule-based engine that drives automated warehouse processes.

Key Information Stored

The table's structure centers on linking a rule to its sorting parameters in a defined order. The primary key is a composite of RULE_ID and SEQUENCE_NUMBER, ensuring unique sorting steps within a rule. Key columns include RULE_ID, which foreign-key references the parent WMS_RULES_B table to identify the governing rule. SEQUENCE_NUMBER dictates the order of application for multiple sort criteria. PARAMETER_ID foreign-key references WMS_PARAMETERS_B, specifying the actual attribute used for sorting, such as item number, destination locator, task priority, or creation date. Additional columns, while not explicitly detailed in the provided metadata, typically include attributes like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY for auditing, and potentially a SORT_ORDER (ASC/DESC) column.

Common Use Cases and Queries

This table is primarily accessed for configuring, analyzing, and troubleshooting warehouse task sequencing. A common use case is reviewing the sort logic for a specific rule to validate or debug task lists. For example, a query to retrieve all sort criteria for a rule, joined to the parameters table for clarity, is essential for support and development. Sample SQL to achieve this would be:

  • SELECT wsc.sequence_number, wp.parameter_code, wp.description FROM wms_sort_criteria wsc, wms_parameters_b wp WHERE wsc.parameter_id = wp.parameter_id AND wsc.rule_id = <RULE_ID> ORDER BY wsc.sequence_number;

This table is also critical for custom reports analyzing rule performance and for data fixes when sort sequences require adjustment. Integration or extension points, such as custom task release programs, may query this table to understand the intended sort order.

Related Objects

The WMS_SORT_CRITERIA table maintains defined foreign key relationships with two primary master tables in the WMS schema, as documented in the provided metadata:

  • WMS_RULES_B: The parent rule definition. The join is WMS_SORT_CRITERIA.RULE_ID = WMS_RULES_B.RULE_ID. This relationship ties the sorting steps to the broader business rule.
  • WMS_PARAMETERS_B: The source for the sortable attribute. The join is WMS_SORT_CRITERIA.PARAMETER_ID = WMS_PARAMETERS_B.PARAMETER_ID. This resolves the PARAMETER_ID to a human-readable code and description (e.g., 'INVENTORY_ITEM_ID', 'PRIORITY').

As a child of WMS_RULES_B, this table is often accessed in conjunction with other rule-related tables like WMS_RULE_ACTIONS. It is a foundational component for views or APIs that materialize complete, executable rule definitions for the WMS engine.