Search Results mrp_net_resource_avail




Overview
Explore in AI Chat

Overview

MRP_NET_RESOURCE_AVAIL is a Master Scheduling/MRP (MRP) transactional table owned by the MRP schema. It stores the detail-level availability of production resources at the granularity of a specific resource within a department, on a particular shift date and shift number, and within a defined time window. In Oracle EBS 12.1.1 and 12.2.2, this table is populated by the MRP and Capacity planning engine to represent the net available capacity of a resource after accounting for allocations, exceptions, and existing loads. The row-level availability record is fundamental to capacity planning, constraint-based scheduling, and bottleneck analysis within the MRP planning horizon.

The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning no enforced parent relationships were detected within the documented FK model. As a modeling suggestion, this table is best treated as a satellite-like fact capturing resource capacity measurements over time, keyed by the composite business identifiers rather than by a synthetic hub reference.

Key Information Stored

The primary key MRP_NET_RESOURCE_AVAIL_PK is a composite surrogate composed of eight business columns: ORGANIZATION_ID, DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM, SHIFT_DATE, FROM_TIME, TO_TIME, and SIMULATION_SET. These same columns represent the natural business key that uniquely identifies an availability slot.

  • ORGANIZATION_ID – the inventory/operating unit scope of the resource.
  • DEPARTMENT_ID – the owning department of the resource within the organization.
  • RESOURCE_ID – the specific resource whose availability is recorded.
  • SHIFT_NUM, SHIFT_DATE, FROM_TIME, TO_TIME – the shift and time-window coordinates defining the availability interval.
  • CAPACITY_UNITS – the calculated available capacity quantity for the interval, the principal measure of the table.
  • SIMULATION_SET – identifies the planning simulation set; the default simulation set represents live (non-simulated) data.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE – concurrent program context identifying the planning run that generated the row.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – standard EBS audit columns.
  • INSTANCE_ID, SERIAL_NUMBER – runtime identifiers supporting multi-instance planning.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 – the descriptive flexfield (DFF) segment columns for customer-defined extensions.

Common Use Cases and Queries

Typical reporting scenarios include capacity load versus availability analysis, bottleneck resource identification, and verification of planning engine outputs. The most common query pattern joins the table to BOM_DEPARTMENTS and BOM_RESOURCES on the composite key columns and filters by organization and date range.

  • Availability by resource and date: SELECT resource_id, shift_date, SUM(capacity_units) FROM mrp.mrp_net_resource_avail WHERE organization_id = :org AND shift_date BETWEEN :start AND :end AND simulation_set IS NULL GROUP BY resource_id, shift_date;
  • Comparison of simulated versus live plans by varying the SIMULATION_SET predicate.
  • Auditing a specific planning run with WHERE request_id = :req to attribute rows to the concurrent request.
  • Extracting DFF-driven attributes for specialized capacity views.

Because the table is truncated and repopulated per planning run, queries should always constrain by SIMULATION_SET and date to avoid scanning historical or simulated data.

Related Objects

The table is classified as standalone with no documented enforced FKs, but it functions within the MRP capacity planning ecosystem and is typically joined to the following:

  • BOM_DEPARTMENTS – on ORGANIZATION_ID and DEPARTMENT_ID.
  • BOM_RESOURCES – on ORGANIZATION_ID, DEPARTMENT_ID, and RESOURCE_ID.
  • MRP_SIMULATION_SETS – on SIMULATION_SET to resolve simulation context.
  • FND_CONCURRENT_REQUESTS – on REQUEST_ID to identify the generating planning run.
  • MRP_RESOURCE_AVAIL – aggregate/parallel availability structures populated by the same planning programs.
  • MRP_GROSS_REQUIREMENTS and MRP_RECOMMENDATIONS – downstream planning output consumed alongside resource availability.
  • FND_FLEX_VALUES – for DFF segment validation on the ATTRIBUTE columns.