Results for “picking_rule_id”

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

Overview

MTL_PICKING_RULES is an Inventory (INV) base table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores picking rule definitions. A picking rule is the configurable strategy that determines how the system selects material to satisfy an outbound demand during the pick-release and transaction-move process. Each row defines a unique rule that governs the ordering and filtering of lots, serial numbers, subinventories, and locators at the time of picking, and can also carry a revision rule. The table therefore acts as a reusable master of allocation strategy, referenced both as a system-level default and as an item-level attribute.

The object is described in the ETRM metadata as "Picking Rule Definitions" under the INV product. Based on the documented foreign-key topology, a heuristic Data Vault classification of hub-leaning is appropriate: MTL_PICKING_RULES holds a stable business key (PICKING_RULE_ID) that is referenced by multiple downstream tables, which is the defining characteristic of a hub entity. This classification is offered as a modeling suggestion rather than a stated Oracle designation.

Key Information Stored

The table contains 18 documented columns. The most operationally significant are:

• PICKING_RULE_ID — the surrogate primary key, defined by the unique index MTL_PICKING_RULES_PK and also by the unique index MTL_PICKING_RULES_U1. It is the sole documented business-key candidate and the column through which all referencing tables join.
• PICKING_RULE_NAME — the user-facing name displayed in Inventory setup forms and reported alongside transactions.
• REVISION_RULE — controls whether revisions are ordered or filtered during picking.
• LOT_RULE — governs lot selection, for example FIFO or expiration-date ordering, for lot-controlled items.
• SERIAL_NUMBER_RULE — determines how serial numbers are sequenced or selected.
• SUBINVENTORY_RULE — defines the order in which subinventories are considered.
• LOCATOR_RULE — defines the order in which locators within a subinventory are searched.
• DESCRIPTION — free-text explanation of the rule's intent.
• MIGRATED_IND — flag indicating whether the row was migrated, relevant to upgrade and data-conversion analysis.
• Audit and concurrent-program columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE. These support WHO-column auditing and trace rule creation or modification back to the originating concurrent request.

Common Use Cases and Queries

Typical reporting scenarios include identifying which picking rule is set as the organization default, which items override that default, and reconciling rule definitions across environments.

Listing all rules for an organization's default:

SELECT mpr.picking_rule_id,
       mpr.picking_rule_name,
       mpr.lot_rule,
       mpr.subinventory_rule,
       mpr.locator_rule
FROM   mtl_picking_rules mpr
JOIN   mtl_parameters   mp
  ON   mp.default_picking_rule_id = mpr.picking_rule_id
WHERE  mp.organization_id = :org_id;

Finding items that override the default picking rule:

SELECT msi.segment1, msi.description, mpr.picking_rule_name
FROM   mtl_system_items_b msi
JOIN   mtl_picking_rules  mpr
  ON   mpr.picking_rule_id = msi.picking_rule_id
WHERE  msi.organization_id = :org_id
  AND  msi.picking_rule_id IS NOT NULL;

A third common pattern is a gap analysis listing rules defined but never referenced by either parameter defaults or item assignments, which supports cleanup of obsolete setups. The audit columns are useful for tracking when a rule was last modified.

Related Objects

• MTL_PARAMETERS — MTL_PARAMETERS.DEFAULT_PICKING_RULE_ID references MTL_PICKING_RULES.PICKING_RULE_ID, establishing the organization-level default rule.
• MTL_SYSTEM_ITEMS_B — MTL_SYSTEM_ITEMS_B.PICKING_RULE_ID references the same primary key, allowing item-level overrides.
• MTL_PICKING_RULE_HEADERS / picking rule detail tables — the criteria assignments (subinventory, locator, lot, serial sort criteria) that refine each header record.
• MTL_MATERIAL_TRANSACTIONS — picking activity and move-order transactions reference the rule applied during allocation.
• WMS / pick-release interfaces and Inventory public APIs — the release and picking logic consumes the rule at runtime to sequence allocation.
• MTL_ITEM_LOCATIONS and MTL_SECONDARY_LOCATORS — supply the locator and subinventory candidates that the rule orders.

Because the documented relationship data identifies this object as hub-leaning, any analytic model should treat MTL_PICKING_RULES as the durable dimension and the referencing tables as its dependent links or satellites.