Results for “inst_transaction_id”

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

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:

• INST_TRANSACTION_ID, PLAN_ID, SR_INSTANCE_ID — the composite business-key triple underpinning the unique index MSC_NET_RES_INST_AVAIL_U1. INST_TRANSACTION_ID identifies the individual instance availability record; PLAN_ID ties the row to a specific ASCP plan; SR_INSTANCE_ID identifies the source application instance (e.g., the source ERP).
• ORGANIZATION_ID — the inventory organization owning the resource.
• DEPARTMENT_ID — the department where the resource resides, the sole documented foreign key (to BOM_DEPARTMENTS).
• RESOURCE_ID, RES_INSTANCE_ID, EQUIPMENT_ITEM_ID — the resource definition, the specific resource instance, and the associated equipment item.
• SERIAL_NUMBER, PARENT_ID — instance identity and parent hierarchy linkage.
• SHIFT_NUM, SHIFT_DATE, FROM_TIME, TO_TIME — the shift and time window for which availability is declared.
• STATUS, APPLIED — the processing state and whether the availability has been applied during the plan run.
• CAPACITY_UNITS — the quantity of resource capacity available in the window.
• SIMULATION_SET, REFRESH_NUMBER — planning simulation and refresh-cycle identifiers.
• LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, PROGRAM_ID, REQUEST_ID — standard audit and concurrent-program tracking columns.

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.