Search Results oe_item_orderability_rules




Overview
Explore in AI Chat

Overview

The OE_ITEM_ORDERABILITY_RULES table resides in the ONT schema (Order Management) and stores the Availability Rules Set definitions that determine whether a given item may be ordered by a specific customer, in a specific context, or under a specific order type. Within Oracle EBS 12.1.1 and 12.2.2, this table is the persistence layer behind the Item Orderability feature, which allows organizations to restrict or permit sales of items based on combinations of customer attributes, geography, channel, and order characteristics. Each row represents a single rule definition scoped to one or more qualifier columns, with an enable flag controlling whether the rule is enforced.

The ETRM metadata classifies this object heuristically as a standalone structure within the Data Vault model — effectively a satellite or hub-like reference table rather than a transactional link. It carries a surrogate primary key (ORDERABILITY_ID) and a foreign key to SO_ORDER_TYPES_115_ALL, indicating that rules may be tied to specific order types. The table holds 39 columns as documented in the 12.2.2 physical schema, including twenty generic ATTRIBUTE columns and a CONTEXT column supporting DFF extension.

Key Information Stored

Business-key candidates typically combine RULE_LEVEL with the applicable qualifier columns (for example, CUSTOMER_ID, ORDER_TYPE_ID, and SHIP_TO_LOCATION_ID), though the table uses the surrogate ORDERABILITY_ID as its definitive unique identifier.

Common Use Cases and Queries

Typical usage includes verifying which rules are active for a given item/customer combination, auditing rule definitions during order-entry troubleshooting, and generating reports of orderability restrictions by channel or region. A representative query retrieves enabled rules for a specific order type:

  • SELECT ORDERABILITY_ID, RULE_LEVEL, CUSTOMER_ID, ORDER_TYPE_ID, ENABLE_FLAG FROM ONT.OE_ITEM_ORDERABILITY_RULES WHERE ORDER_TYPE_ID = :order_type AND ENABLE_FLAG = 'Y';
  • Join to SO_ORDER_TYPES_115_ALL to resolve order type names for reporting: SELECT r.ORDERABILITY_ID, t.NAME FROM OE_ITEM_ORDERABILITY_RULES r, SO_ORDER_TYPES_115_ALL t WHERE r.ORDER_TYPE_ID = t.ORDER_TYPE_ID;
  • Audit recent changes: filter on LAST_UPDATE_DATE within a date range to identify modified rule sets.
  • Profile usage by rule level or channel to assess rule proliferation before cleanup.

Related Objects

  • SO_ORDER_TYPES_115_ALL — Joined via ORDER_TYPE_ID; supplies order type definitions referenced by the rule.
  • OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — Order-entry transactions validated against orderability rules.
  • HZ_CUST_ACCOUNTS / HZ_CUST_ACCT_SITES_ALL — Sources for customer and location qualifiers (CUSTOMER_ID, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID).
  • MTL_SYSTEM_ITEMS_B — Item master used in conjunction with rule evaluation.
  • OE_ORDER_PUB / Order Management APIs — Programmatic validation entry points that consult orderability rules during order creation.
  • JTF_RS_SALESREPS — Source for SALES_PERSON_ID qualifier values.