Search Results demand




Deep Dive into MRP_ATP_DETAILS_TEMP Table in Oracle EBS 12.1.1/12.2.2

The MRP_ATP_DETAILS_TEMP table in Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2 is a critical temporary storage structure used within the Material Requirements Planning (MRP) and Available-to-Promise (ATP) modules. This table plays a pivotal role in processing ATP inquiries, allocations, and scheduling by temporarily holding intermediate calculation results before final commitment to transactional tables. Below is a detailed breakdown of its purpose, structure, and functional significance.

1. Purpose and Context

The MRP_ATP_DETAILS_TEMP table acts as a staging area during ATP calculations, which determine product availability based on supply (inventory, purchase orders, production schedules) and demand (sales orders, forecasts). It stores transient data generated during ATP rule evaluations, including projected availability dates, quantities, and sourcing details. This temporary storage ensures performance efficiency by avoiding direct writes to permanent tables during complex ATP computations.

2. Key Columns and Data Structure

The table's schema typically includes the following columns (specifics may vary by EBS version):

  • SESSION_ID: Unique identifier linking temporary data to a user session.
  • INVENTORY_ITEM_ID: References the item being analyzed (foreign key to MTL_SYSTEM_ITEMS_B).
  • ORGANIZATION_ID: Identifies the inventory organization (foreign key to HR_ORGANIZATION_UNITS).
  • REQUESTED_DATE: The date for which ATP is calculated.
  • QUANTITY: Available or allocated quantity.
  • ATP_RULE_ID: References the ATP rule applied (from MRP_ATP_RULES).
  • SOURCE_TYPE: Indicates supply source (e.g., 'ONHAND', 'PURCHASE_ORDER').
  • STATUS_FLAG: Marks data as processed or pending.

3. Integration with ATP Workflow

During ATP execution, Oracle EBS populates MRP_ATP_DETAILS_TEMP in these stages:

  1. Initialization: Session-specific data is inserted for each ATP inquiry.
  2. Rule Processing: ATP rules evaluate supply/demand, updating quantities and dates in the temp table.
  3. Allocation: Temporary records are used to reserve quantities (if allocation is enabled).
  4. Cleanup: Data is purged post-transaction or via scheduled jobs (e.g., MRP_ATP_TEMP_DATA_CLEANUP).

4. Performance and Maintenance Considerations

Since this table handles high-volume transient data, improper maintenance can lead to performance degradation:

  • Indexing: Oracle typically indexes SESSION_ID and STATUS_FLAG for faster queries.
  • Purging: Implement regular cleanup via concurrent programs to avoid table bloating.
  • Partitioning (12.2.2+): Enhances scalability by partitioning data by session or date ranges.

5. Customization and Extensions

In custom ATP implementations, developers may extend this table by:

  • Adding columns for custom sourcing logic (e.g., subcontractor lead times).
  • Integrating with PL/SQL APIs like MRP_ATP_PUB.CALL_ATP to modify temp data pre-commit.

6. Troubleshooting

Common issues include orphaned records (due to session crashes) or locking conflicts. Diagnostic queries often join this table with MRP_ATP_SESSIONS to identify stale data.

In summary, MRP_ATP_DETAILS_TEMP is a foundational component of Oracle EBS's ATP engine, balancing computational complexity with transactional integrity. Its efficient design ensures real-time availability checks while maintaining system performance through transient data management.