Search Results msc_net_res_inst_avail




Overview
Explore in AI Chat

Overview

MSC_NET_RES_INST_AVAIL is a transactional planning table in the MSC (Advanced Supply Chain Planning) schema of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores net resource instance availability — that is, the time-phased capacity that individual resource instances (machines, equipment, or defined resource units) can supply during specific shifts, and how much of that capacity has already been applied or consumed by the planning engine. Rather than holding generic resource definitions, the table captures the availability of a discrete instance of a resource for a given organization, department, shift, and date range, making it a granular building block for capacity planning and constraint analysis in ASCP.

From a Data Vault modeling perspective (a heuristic suggested by the mined foreign key structure), the table is best classified as a satellite attached to a resource-instance hub, since it records descriptive, time-stamped availability attributes tied to a business key rather than serving as a pure relationship link. Its single documented foreign key, DEPARTMENT_ID referencing BOM_DEPARTMENTS, anchors it to the departments dimension.

Key Information Stored

With 45 columns, the table blends planning keys, scheduling attributes, and standard audit columns. The most significant columns are:

Common Use Cases and Queries

Typical reporting focuses on shift-level capacity by organization or department, applied-versus-available comparisons, and plan-level resource loading. A representative query joins availability to the department dimension:

  • SELECT a.ORGANIZATION_ID, a.DEPARTMENT_ID, d.DEPARTMENT_CODE, a.RESOURCE_ID, a.SHIFT_DATE, a.CAPACITY_UNITS, a.APPLIED FROM MSC.MSC_NET_RES_INST_AVAIL a, BOM.BOM_DEPARTMENTS d WHERE a.DEPARTMENT_ID = d.DEPARTMENT_ID AND a.PLAN_ID = :plan_id;
  • Filtering on CAPACITY_UNITS and APPLIED to identify unapplied or over-consumed availability before a plan refresh.
  • Grouping by SHIFT_NUM and SHIFT_DATE to build shift-capacity utilization dashboards.
  • Excluding partial or stale data by constraining SIMULATION_SET and REFRESH_NUMBER to the current planning run.

Related Objects

The table's relationships are defined primarily through its business key and the department FK rather than declarative constraints. Significant related objects include:

  • BOM_DEPARTMENTS — joined on DEPARTMENT_ID; supplies the department definition.
  • MSC_NET_RESOURCES / MSC_RESOURCE_INSTANCES — parent resource and instance definitions referenced via RESOURCE_ID and RES_INSTANCE_ID.
  • MSC_PLANS — the plan master keyed by PLAN_ID.
  • MSC_SYSTEM_ITEMS_B / MSC_ITEM_ORG_ASSIGNMENTS — item context for EQUIPMENT_ITEM_ID.
  • MSC_SR_INSTANCES — source instance metadata for SR_INSTANCE_ID.
  • MSC_NET_RES_INST_AVAIL_U1 — the unique index enforcing the (PLAN_ID, INST_TRANSACTION_ID, SR_INSTANCE_ID) business key.