Search Results is_fetched

Overview

MSC.MSC_GANTT_QUERY is a global temporary table in the MSC (Advanced Supply Chain Planning) schema of Oracle E-Business Suite, documented and valid in both release 12.1.1 and 12.2.2. It serves as a session-scoped work area used by the Advanced Supply Chain Planning Gantt visualization infrastructure to materialize the hierarchical rows that make up a scheduling Gantt chart. Rather than persisting analytical results, the table holds transient rendering data — plan node hierarchies, operation sequences, resource assignments, and dependency relationships — that a planning session builds and consumes while displaying a Gantt view. Its data duration is SYS$SESSION, meaning rows are private to the session that inserted them and are automatically purged when that session ends; no other session can see the rows, and no manual cleanup job is required.

The documented schema exposes 31 columns. The ETRM dependency analysis also shows that DEPARTMENT_ID carries a foreign key to BOM_DEPARTMENTS, establishing a direct link between Gantt rows and the departmental model used in manufacturing routing.

Applying the supplied heuristic Data Vault classification, this object is standalone; in Data Vault terms it behaves as a transient satellite-like staging artifact rather than a durable hub or link. Because it is a temporary table with no persistent business key ledger, it should be treated as volatile planning scratch space, not as a system-of-record entity.

Key Information Stored

• QUERY_ID — Identifier for the logical Gantt query (a planning session's query instance); the leading column of the nonunique index MSC_GANTT_QUERY_U1.
• ROW_INDEX — Positional sequence of the row within the query; the second indexed column. QUERY_ID plus ROW_INDEX form the effective access path.
• CHILD_QUERY_ID — Self-referencing pointer to a subordinate query, supporting nested or expanded Gantt segments.
• SORT_INDEX — Ordering key controlling display sequence of sibling rows.
• NODE_PATH — The hierarchical path string (VARCHAR2(250)) identifying a node's position in the Gantt tree; the column most likely targeted by user searches, since it encodes ancestor lineage.
• NODE_TYPE, NODE_LEVEL — Classify the node (e.g., plan, operation, resource) and its depth in the hierarchy.
• PARENT_LINK — A wide (VARCHAR2(4000)) composite link describing the parent relationship, complementing NODE_PATH for traversal.
• PLAN_ID — The supply chain plan context to which the row belongs.
• ORGANIZATION_ID — The inventory organization context.
• TRANSACTION_ID, OP_SEQ_ID, OP_SEQ_NUM, OP_DESC — Work-order/operation identity and description for the scheduled activity shown on the bar.
• DEPENDENCY_TYPE, CRITICAL_FLAG — Diagram edge semantics (precedence relationship type) and critical-path indicator.
• INVENTORY_ITEM_ID, SUPPLIER_ID, SUPPLIER_SITE_ID, DEPARTMENT_ID, RESOURCE_ID, RES_INSTANCE_ID, SERIAL_NUMBER — The dimensional attributes (item, supplier, department, resource, serial) that populate and filter the Gantt view.
• IS_FETCHED, ROW_FLAG — Processing state flags indicating whether the row has been retrieved and its disposition/filtering status.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN provide standard EBS who-columns.

No primary key constraint is documented. The only index, MSC_GANTT_QUERY_U1, is nonunique on (QUERY_ID, ROW_INDEX), so uniqueness is logical rather than enforced; QUERY_ID/ROW_INDEX should be regarded as the surrogate access key, while PLAN_ID, ORGANIZATION_ID, and TRANSACTION_ID are the business-key candidates for joins.

Common Use Cases and Queries

Typical usage is diagnostic and reporting-oriented: extracting the rendered Gantt hierarchy for a plan, auditing node paths, or tracing how the planner assembled operation and resource rows during a session.

• Retrieve a full Gantt tree for a query instance, ordered for display:

  SELECT QUERY_ID, ROW_INDEX, NODE_PATH, NODE_TYPE, NODE_LEVEL
  FROM   MSC.MSC_GANTT_QUERY
  WHERE  QUERY_ID = :query_id
  ORDER  BY SORT_INDEX;

• Locate nodes by hierarchy path:

  SELECT * FROM MSC.MSC_GANTT_QUERY
  WHERE QUERY_ID = :query_id
  AND   NODE_PATH LIKE :root_path || '%';

• List critical-path operations for a plan:

  SELECT TRANSACTION_ID, OP_SEQ_NUM, OP_DESC, RESOURCE_ID
  FROM   MSC.MSC_GANTT_QUERY
  WHERE  PLAN_ID = :plan_id
  AND    CRITICAL_FLAG = 1;

• Join departments for organization-level reporting on BOM_DEPARTMENTS.DEPARTMENT_ID.

Because the table is session-temporary, queries must run in the same session that populated it; cross-session reporting against persistent data must instead target the underlying MSC planning tables. Purge timing is automatic at session end, so no DELETE maintenance is appropriate.

Related Objects

• BOM_DEPARTMENTS — referenced via DEPARTMENT_ID, providing department descriptions and routing context.
• MSC_GANTT_QUERY self-reference — CHILD_QUERY_ID and PARENT_LINK create internal hierarchical relationships within the same temporary result set.
• MSC plans and plan data tables — joined through PLAN_ID to relate Gantt rows to the parent supply chain plan definition.
• Inventory / item master (INVENTORY_ITEM_ID) — resolves the item dimension displayed on Gantt bars.
• Suppliers and supplier sites (SUPPLIER_ID, SUPPLIER_SITE_ID) — sourcing attributes for procurement-oriented Gantt views.
• Resources and resource instances (RESOURCE_ID, RES_INSTANCE_ID) — capacity/resource context for scheduled operations.
• Departments / routing objects — the departmental model referenced by the documented foreign key.
• Gantt visualization APIs and concurrent programs in the MSC schema — the session-bound consumers that populate and read this table at runtime.

The ETRM dependency metadata states that this table does not reference any database object except through the documented DEPARTMENT_ID foreign key, reinforcing its role as a self-contained, session-local rendering structure.