Search Results msc_net_resource_avail

Overview

The MSC_NET_RESOURCE_AVAIL table is a core data repository within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. Its primary function is to store the detailed availability profile for manufacturing and production resources. This table is fundamental to the planning engine's capacity calculations, as it holds the net available time for resources after accounting for predefined shifts, calendars, and downtime. The data within this table directly influences the generation of feasible production plans, enabling planners to understand capacity constraints and optimize scheduling across the supply chain in both Oracle EBS 12.1.1 and 12.2.2 environments.

Key Information Stored

The table's structure is designed to uniquely identify and quantify resource availability within specific planning contexts. Key columns include a composite primary key consisting of TRANSACTION_ID, PLAN_ID, and SR_INSTANCE_ID, which ensures uniqueness for each availability record. The PLAN_ID links the data to a specific planning run or simulation, while SR_INSTANCE_ID identifies the source system instance. Critical resource identification is provided by ORGANIZATION_ID, DEPARTMENT_ID, and RESOURCE_ID. The availability data itself is stored in columns representing different time buckets or periods (e.g., daily, weekly), though the specific period column names are implied by the table's purpose. The PARENT_ID column suggests a hierarchical structure, potentially allowing for aggregated resource availability records.

Common Use Cases and Queries

This table is primarily accessed for capacity analysis and bottleneck identification. A common reporting use case involves querying net available hours for critical resources to compare against planned or required load. For example, planners may execute a query to summarize capacity by resource for a given plan. A typical SQL pattern would join this table to resource and department master tables to retrieve meaningful descriptions.

• Sample Query for Resource Capacity Summary:
  SELECT mra.organization_id, mra.resource_id, mra.department_id, SUM(mra.available_hours) AS total_available_hours FROM msc_net_resource_avail mra WHERE mra.plan_id = :p_plan_id GROUP BY mra.organization_id, mra.resource_id, mra.department_id ORDER BY 1,3,2;
• Integration with Planning Engine: The ASCP engine populates this table during a plan run, and downstream processes or custom reports read from it to display capacity utilization and availability metrics.

Related Objects

The table maintains defined foreign key relationships with several other core MSC tables, as documented in the ETRM metadata. These relationships are crucial for data integrity and join conditions in queries.

• MSC_SIMULATION_SETS: Joined via SR_INSTANCE_ID and SIMULATION_SET columns. This links resource availability data to specific simulation scenarios.
• MSC_DEPARTMENT_RESOURCES: Joined via a composite key (PLAN_ID, ORGANIZATION_ID, RESOURCE_ID, SR_INSTANCE_ID, DEPARTMENT_ID). This is the primary relationship to the master definition of resources within departments.
• Self-Referential Relationship: The table references itself via the PARENT_ID and PLAN_ID columns, indicating a parent-child hierarchy within the availability data, likely for rolled-up summaries.