Search Results wip_preference_levels




Overview
Explore in AI Chat

Overview

WIP.WIP_PREFERENCE_LEVELS is a Work in Process (WIP) configuration table that stores preference level definitions used by Oracle EBS manufacturing and shop floor execution. Preference levels provide the granularity at which WIP preferences (such as default values, execution controls, and flexibility settings) are resolved and applied. Each row defines a level at which preferences can be scoped, typically tied to a responsible organization, department, or module context within the manufacturing environment. In Oracle EBS 12.1.1 and 12.2.2, this table operates as a reference/definition object supporting the preference resolution framework used by WIP transactions and concurrent processes.

From a heuristic Data Vault modeling perspective, the metadata classifies this object as standalone. This suggests it is best modeled as a hub (or a standalone reference entity) rather than as a link or satellite, since no downstream tables were mined as children referencing it via foreign keys. It functions as an independent definition table whose rows are referenced conceptually by preference-resolution logic rather than by hard foreign-key constraints from other documented tables.

Key Information Stored

The table contains 13 documented columns. The most significant are:

  • LEVEL_ID — Surrogate primary key identifying each preference level record. It is a foreign key to MSD_LEVELS, linking the WIP preference level to the shared level definition.
  • LEVEL_CODE — Business-facing code used to identify the preference level in application logic and lookups.
  • RESP_KEY — Responsible key used to associate the preference level with a responsibility or application context for preference resolution.
  • ORGANIZATION_ID — Identifies the inventory organization to which the preference level applies, enforcing multi-org scoping.
  • DEPARTMENT_ID — Foreign key to BOM_DEPARTMENTS, tying the preference level to a specific department.
  • MODULE_ID — Identifies the functional module (such as WIP) for which the preference level is defined.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns recording who created and last modified the row and when.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/ADF framework to detect concurrent updates.
  • ZD_EDITION_NAME — Editioning column supporting the Online Patching (ADOP) architecture introduced in 12.2.x; it forms part of the unique business key.

The documented unique index WIP_PREFERENCE_LEVELS_U1 (LEVEL_ID, ZD_EDITION_NAME) serves as the business-key candidate, confirming that LEVEL_ID combined with the edition name uniquely identifies each record across editions. LEVEL_ID is therefore both the surrogate primary key and the primary business-key candidate.

Common Use Cases and Queries

Typical scenarios include confirming which preference levels are defined for an organization, resolving preference scope during shop floor transactions, and validating department-level configurations after an upgrade or clone.

  • Listing preference levels for an organization: 
    SELECT level_id, level_code, organization_id, department_id, module_id
FROM   wip.wip_preference_levels
WHERE  organization_id = :org_id;
  • Joining to the shared level definition and department: 
    SELECT wpl.level_code, ml.level_name, bd.department_code
FROM   wip.wip_preference_levels wpl,
       msd_levels            ml,
       bom_departments       bd
WHERE  wpl.level_id      = ml.level_id
AND    wpl.department_id = bd.department_id;
  • Auditing recent changes to preference level definitions: 
    SELECT level_code, last_updated_by, last_update_date
FROM   wip.wip_preference_levels
WHERE  last_update_date > SYSDATE - 30;

These queries support configuration audits, multi-org setup validation, and troubleshooting of preference resolution issues in WIP execution.

Related Objects

The most significant objects related to WIP_PREFERENCE_LEVELS, based on documented foreign keys and standard WIP dependencies, are:

  • MSD_LEVELS — joined via LEVEL_ID; provides the shared level definition.
  • BOM_DEPARTMENTS — joined via DEPARTMENT_ID; identifies the department context.
  • WIP_PREFERENCES — the companion table holding actual preference values resolved against these levels.
  • WIP_PARAMETERS — WIP parameter configuration sharing organizational and module context.
  • WIP_ENTITIES — the core WIP entity table whose transactions rely on preference resolution.
  • MTL_PARAMETERS — organization-level defaults that interact with preference level scoping.

Together these objects form the configuration backbone enabling scoped, organization- and department-aware WIP preference behavior.