Search Results oe_cust_item_settings_u

Overview

ONT.OE_CUST_ITEM_SETTINGS is a transactional configuration table in the Oracle Order Management (ONT) schema that stores shipment and return tolerances for customer-item, item-site, and item-level combinations. In Oracle EBS 12.1.1 and 12.2.2, this table allows order management operations to enforce customer-specific and item-specific acceptance thresholds during the shipping and return authorization processes. When goods are shipped against an order line, Oracle Shipping Execution evaluates the over-shipment and under-shipment tolerances defined here to determine whether a delivery quantity falls within an acceptable range. Similarly, Return Material Authorization (RMA) processing consults the return tolerances to validate return quantities.

From a Data Vault modeling perspective, this table is classified as satellite-leaning. That heuristic classification suggests it functions primarily as a dependent descriptive record attached to a core business entity (the customer-item relationship) rather than as an independent hub or a resolution link. Its grain is defined by the combination of customer, site use, and internal item, with a system-generated surrogate identifier serving as the primary key.

Key Information Stored

The central business columns in this table capture configuration and tolerance thresholds:

• CUST_ITEM_SETTING_ID — The system-generated numeric primary key (OE_CUST_ITEM_SETTINGS_PK) and the single column of the unique index OE_CUST_ITEM_SETTINGS_U. It is the surrogate identifier; the unique index does not by itself define a composite business key in the documented metadata.
• CUSTOMER_ID — Identifies the customer to which the tolerance settings apply. This is the only documented foreign key, referencing RA_CUSTOMERS.
• SITE_USE_ID — Associates the settings with a specific customer site use, enabling site-level tolerance configuration.
• INTERNAL_ITEM_ID — Identifies the inventory item for which tolerances are defined.
• INVENTORY_ORG_ID — Restricts the configuration to a particular inventory organization.
• OVER_SHIPMENT_TOLERANCE / UNDER_SHIPMENT_TOLERANCE — Define the acceptable percentage range above and below ordered quantity for shipment fulfillment.
• OVER_RETURN_TOLERANCE / UNDER_RETURN_TOLERANCE — Define the acceptable percentage range for authorized return quantities.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — Descriptive Flexfield (DFF) segments supporting customer-specific extensions.
• Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — Audit trail fields maintained automatically by the application.

Two non-unique indexes support access: OE_CUST_ITEM_SETTINGS_N1 on (CUSTOMER_ID, INTERNAL_ITEM_ID) and OE_CUST_ITEM_SETTINGS_N2 on (INTERNAL_ITEM_ID, SITE_USE_ID).

Common Use Cases and Queries

A frequent reporting requirement is identifying which tolerances govern a given customer and item, for example to explain why a shipment was flagged as over-tolerance:

• Query by customer and item using the N1 index path: SELECT * FROM ont.oe_cust_item_settings WHERE customer_id = :p_customer AND internal_item_id = :p_item;
• Query by item and site use using the N2 index path to support returns processing: SELECT * FROM ont.oe_cust_item_settings WHERE internal_item_id = :p_item AND site_use_id = :p_site;
• Reporting tolerance exceptions: join to RA_CUSTOMERS to display customer names alongside tolerance values.
• Data migration and configuration auditing: extract all rows with non-null DFF attributes to verify customer-specific extensions in a 12.2.2 upgrade.

Related Objects

• RA_CUSTOMERS — Referenced via CUSTOMER_ID; the primary documented FK relationship.
• RA_CUSTOMER_PROFILES / RA_SITE_USES_ALL — Provide customer and site-use context joined on CUSTOMER_ID and SITE_USE_ID.
• MTL_SYSTEM_ITEMS_B — Supplies item details via INTERNAL_ITEM_ID.
• OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL — Order data that consumes tolerance values during fulfillment.
• WSH_DELIVERY_DETAILS — Shipping execution records validated against shipment tolerances.
• OE_RMA_LINES / OE_RETURN_REQUESTS — Return processing that consumes return tolerance settings.