Results for “mtl_period_summary”

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

Overview

MTL_PERIOD_SUMMARY is an Oracle Inventory (INV) table that stores period summary inventory values for subinventories. It resides in the INV schema and holds the summarized on-hand valuation of a subinventory for a given accounting period, organization, inventory type, and cost group. Rather than capturing every individual transaction, the table aggregates the inventory value at the close of each accounting period, providing a periodic snapshot of inventory worth. This makes it a foundational object for period-end inventory valuation reporting, reconciliation between inventory and general ledger, and historical trend analysis of stock value.

The table is classified, heuristically from its foreign key structure, as a link in Data Vault modeling terms. This classification reflects its role: it resolves relationships between accounting periods, organizations, and subinventories rather than acting as a standalone reference (hub) or pure descriptive record (satellite). Its grain is defined by the combination of accounting period, organization, inventory type, subinventory, and cost group, so each row captures a distinct intersection of those dimensions.

Key Information Stored

The table contains 15 documented columns. The most significant include:

• ACCT_PERIOD_ID — The accounting period identifier; part of the primary key and a foreign key to ORG_ACCT_PERIODS.
• ORGANIZATION_ID — The inventory organization; part of the primary key.
• SECONDARY_INVENTORY — The subinventory name; part of the primary key and a foreign key to MTL_SECONDARY_INVENTORIES.
• INVENTORY_TYPE — Classifies the inventory category represented in the row.
• COST_GROUP_ID — The cost group; a foreign key to CST_COST_GROUPS and part of the unique business key.
• INVENTORY_VALUE — The summarized monetary value of inventory for the specified dimensions and period.

The surrogate primary key is MTL_PERIOD_SUMMARY_PK over (ACCT_PERIOD_ID, ORGANIZATION_ID, SECONDARY_INVENTORY). The unique index MTL_PERIOD_SUMMARY_U1 over (ACCT_PERIOD_ID, ORGANIZATION_ID, INVENTORY_TYPE, SECONDARY_INVENTORY, COST_GROUP_ID) represents the fuller business-key candidate. Standard audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — track row maintenance, while REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the concurrent program that populated the row.

Common Use Cases and Queries

Typical uses include period-end inventory valuation reports, subinventory value comparisons across periods, and reconciliation of inventory balances against the general ledger. A representative query retrieves the summarized value for an organization across periods:

SELECT mps.acct_period_id, mps.organization_id,
       mps.secondary_inventory, mps.cost_group_id,
       SUM(mps.inventory_value) inventory_value
FROM   mtl_period_summary mps
WHERE  mps.organization_id = :org_id
AND    mps.acct_period_id = :period_id
GROUP BY mps.acct_period_id, mps.organization_id,
         mps.secondary_inventory, mps.cost_group_id;

Another frequent pattern joins to ORG_ACCT_PERIODS to translate the numeric period into a readable period name, and to MTL_SECONDARY_INVENTORIES to obtain the subinventory description for reporting.

Related Objects

The most significant related objects, based on the documented foreign key relationships, are:

• ORG_ACCT_PERIODS — joined on ACCT_PERIOD_ID and ORGANIZATION_ID to resolve period definitions.
• MTL_SECONDARY_INVENTORIES — joined on SECONDARY_INVENTORY and ORGANIZATION_ID to resolve subinventory details.
• CST_COST_GROUPS — joined on COST_GROUP_ID to classify cost groupings within the summarized value.

These relationships establish MTL_PERIOD_SUMMARY as a link connecting period, organization, subinventory, and cost group dimensions, supporting the periodic valuation reporting central to Oracle Inventory and Cost Management operations.