Results for “mtl_atp_rules”

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

Overview

MTL_ATP_RULES is an Inventory (INV) module table that stores ATP (Available-to-Promise) computation rules within Oracle E-Business Suite 12.1.1 and 12.2.2. ATP rules govern how Oracle's scheduling and order promising engines evaluate supply and demand when calculating the quantity of a product that can be committed to a customer by a requested date. Each row in the table defines a named rule that determines which demand and supply sources are considered, how time fences are applied, and how availability is accumulated across the planning horizon.

The table resides in the INV schema and carries a documented status of VALID with 64 columns. Its heuristic Data Vault classification is hub-leaning, meaning the table primarily functions as a durable reference entity keyed by a stable business identifier. From a modeling perspective, RULE_ID and RULE_NAME both behave as business keys; the unique indexes MTL_ATP_RULES_U1 (RULE_ID) and MTL_ATP_RULES_U2 (RULE_NAME) confirm these as the two most reliable natural identifiers, while the surrounding descriptive columns behave as satellite-style attributes.

Key Information Stored

The surrogate primary key of the table is RULE_ID, enforced by MTL_ATP_RULES_PK, with RULE_NAME serving as the primary business-key candidate through unique index MTL_ATP_RULES_U2. Together with DESCRIPTION, these form the identification layer of each rule.

The most significant columns from a functional standpoint are those governing ATP computation behavior:

• ACCUMULATE_AVAILABLE_FLAG and ACCUMULATION_WINDOW — control whether availability is accumulated across periods and the window over which accumulation occurs.
• BACKWARD_CONSUMPTION_FLAG and FORWARD_CONSUMPTION_FLAG — determine whether demand consumes supply before or after the requested date.
• INFINITE_SUPPLY_TIME_FENCE and INFINITE_SUPPLY_FENCE_CODE — define the horizon over which supply is treated as infinite.
• ACCEPTABLE_EARLY_FENCE and ACCEPTABLE_LATE_FENCE — bound the acceptable promise window around the requested date.
• PAST_DUE_DEMAND_CUTOFF_FENCE and PAST_DUE_SUPPLY_CUTOFF_FENCE — exclude stale demand and supply beyond the cutoff.
• AGGREGATE_TIME_FENCE_CODE and AGGREGATE_TIME_FENCE — control aggregation of transactions across time.
• DEFAULT_ATP_SOURCES — indicates the default set of supply sources used by the rule.
• INCLUDE_SALES_ORDERS, INCLUDE_PURCHASE_ORDERS, INCLUDE_ONHAND_AVAILABLE, INCLUDE_INTERORG_TRANSFERS — flag inclusion of major supply and demand streams.
• INCLUDE_DISCRETE_WIP_DEMAND, INCLUDE_DISCRETE_WIP_RECEIPTS, INCLUDE_REP_WIP_DEMAND, INCLUDE_REP_WIP_RECEIPTS — govern work-in-process demand and supply.
• DEMAND_CLASS_ATP_FLAG and MPS_DESIGNATOR — tie the rule to demand class processing and master production schedule designation.

Standard EBS audit columns are present, including LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and the ATTRIBUTE1 through ATTRIBUTE15 flexfield set.

Common Use Cases and Queries

ATP rules are configured once and then referenced throughout the order management and planning lifecycle. Common reporting and diagnostic patterns include:

• Identifying which ATP rule is assigned to a specific item by joining MTL_SYSTEM_ITEMS_B on ATP_RULE_ID.
• Finding the system default rule via MTL_PARAMETERS.DEFAULT_ATP_RULE_ID.
• Auditing rule inclusion flags to troubleshoot differences between expected and calculated available-to-promise quantities.
• Listing rules by name using the MTL_ATP_RULES_U2 business key for selection lists and validation queries.

A representative query joining items to their ATP rule:

• SELECT i.segment1, i.organization_id, r.rule_name, r.accumulate_available_flag FROM mtl_system_items_b i JOIN mtl_atp_rules r ON i.atp_rule_id = r.rule_id WHERE i.atp_rule_id IS NOT NULL;

For parameter-level defaults:

• SELECT p.organization_id, r.rule_name FROM mtl_parameters p JOIN mtl_atp_rules r ON p.default_atp_rule_id = r.rule_id;

Related Objects

The FK and PK relationships documented in the ETRM metadata identify the most significant dependent objects:

• MTL_SYSTEM_ITEMS_B — references MTL_ATP_RULES via ATP_RULE_ID, assigning a default ATP rule to each item.
• MTL_PARAMETERS — references MTL_ATP_RULES via DEFAULT_ATP_RULE_ID, defining the organization-level default rule.
• MTL_DEMAND_INTERFACE — references MTL_ATP_RULES via ATP_RULE_ID, applying a rule to demand interface records.
• BOM_DEPARTMENT_RESOURCES — references MTL_ATP_RULES via ATP_RULE_ID, linking ATP rules to department resources.

Beyond these foreign keys, ATP rules are consumed indirectly by Oracle Order Management's scheduling and promising processes, which read the rule definition when committing order lines against availability. Any change to a rule's inclusion flags or time fences therefore propagates through these dependent objects, making controlled maintenance of MTL_ATP_RULES important for consistent order promising behavior across the EBS environment.