Search Results gmp_routing_offsets

Overview

GMP_ROUTING_OFFSETS is a table owned by the GMP schema within the Oracle EBS Process Manufacturing Process Planning module. It stores routing offset values used in lead time calculations, enabling the planning engine to determine when a routing step should start and end relative to the production of a given recipe or formula. In process manufacturing, lead times are not simply fixed durations; they are derived from the sequencing of routing steps and the offsets applied between them. This table captures that offset information so that planning, scheduling, and capacity calculations can accurately reflect real-world timing constraints on the shop floor.

The object resides in the GMP - Process Manufacturing Process Planning product area, one of the core modules in the Oracle Process Manufacturing (OPM) suite. It is classified as VALID and is documented in ETRM for both 12.1.1 and 12.2.2. The heuristic Data Vault classification mined from the foreign key structure is standalone. In Data Vault modeling terms, this suggests the table may be treated as a satellite-like entity attached to recipe and routing hubs, rather than a true hub or link in its own right, though the standalone classification indicates the FK structure is relatively simple.

Key Information Stored

The table contains 14 documented columns. The most significant include:

• RECIPE_ID — Foreign key to GMD_RECIPES_B. Identifies the recipe to which the offset applies. This is a primary business-key candidate linking offsets to a specific production recipe.
• ROUTINGSTEP_ID — Foreign key to FM_ROUT_DTL. Identifies the routing step within the routing for which the offset is defined. Together with RECIPE_ID, this forms the natural business key of the record.
• START_OFFSET — The offset value (typically a duration or quantity) applied to the start of the routing step in lead time calculations.
• END_OFFSET — The offset value applied to the end of the routing step.
• PLANT_CODE — The plant or facility identifier, enabling offsets to vary by manufacturing site.
• FORMULA_ID — Identifies the formula associated with the recipe, relevant when offsets are formula-dependent.
• ROUTING_ID — Identifies the routing header, providing linkage to the routing definition.
• FMEFF_ID — Foreign key to the formula effectivity, tying the offset to a specific formula version.
• ORGANIZATION_ID — The operating unit / inventory organization context, supporting multi-org reporting.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns tracking record creation and modification.

No surrogate primary key column (such as a sequence-generated ID) is explicitly named in the documented metadata; the effective identifier is the composite of RECIPE_ID and ROUTINGSTEP_ID (and, where present, ORGANIZATION_ID).

Common Use Cases and Queries

Planners and implementers query GMP_ROUTING_OFFSETS primarily to audit and troubleshoot lead time calculations. Common scenarios include:

• Verifying that start and end offsets are configured for each routing step of a recipe.
• Comparing offset values across plants for the same recipe to ensure consistent lead times.
• Extracting offset data to feed external scheduling or APS systems.
• Investigating why a planned order's start date differs from the expected date.

A representative query to list offsets for a given recipe and organization:

SELECT r.recipe_id, r.routingstep_id, r.start_offset, r.end_offset, r.plant_code FROM gmp.gmp_routing_offsets r WHERE r.recipe_id = :recipe_id AND r.organization_id = :org_id;

To join to the recipe header:

SELECT b.recipe_no, o.start_offset, o.end_offset FROM gmp.gmp_routing_offsets o, gmd.gmd_recipes_b b WHERE o.recipe_id = b.recipe_id;

Related Objects

The most significant related objects, based on documented foreign key relationships and module context, are:

• GMD_RECIPES_B — Recipe master table; joined via GMP_ROUTING_OFFSETS.RECIPE_ID = GMD_RECIPES_B.RECIPE_ID. Provides recipe number and description.
• FM_ROUT_DTL — Routing step detail table; joined via GMP_ROUTING_OFFSETS.ROUTINGSTEP_ID = FM_ROUT_DTL.ROUTINGSTEP_ID. Provides step-level attributes such as resource and operation.
• FM_ROUT_HDR — Routing header, referenced through ROUTING_ID, providing the routing definition context.
• GMD_FORMULAS_B — Formula master, linked via FORMULA_ID and FMEFF_ID, giving formula version context for offset applicability.
• GMD_RECIPE_VERSIONS — Records recipe versioning, which affects effectivity of offsets.
• GMP_PLANNING_LEAD_TIMES (or equivalent planning tables) — Consumes offset values during lead time calculation.
• MTL_SYSTEM_ITEMS_B — Related indirectly through inventory item identification for planned items.
• HR_ALL_ORGANIZATION_UNITS / ORG_ORGANIZATION_DEFINITIONS — Provide the organization context for ORGANIZATION_ID.

Together, these objects allow the routing offset data to be fully resolved into actionable lead time information within Oracle Process Manufacturing.