Search Results mtl_per_close_dtls_u1

Overview

MTL_PER_CLOSE_DTLS is a table owned by the INV schema in Oracle E-Business Suite (validated across 12.1.1 and 12.2.2). It stores period end quantities, costs, and values broken down by subinventory, item, and cost group for an organization operating under Average Costing for Work in Process. The table additionally captures the period end value of intransit inventory for the organization. Records are created by the period close program rather than by direct user entry.

MTL_PER_CLOSE_DTLS functions as a child table of MTL_PERIOD_SUMMARY, allowing a parent period summary record to be decomposed into item, subinventory, and cost group level detail. For intransit inventory, COST_GROUP_ID is populated with the value 1 and SECONDARY_INVENTORY is NULL. The table resides in the APPS_TS_SUMMARY tablespace with a PCT Free of 10, confirming its role as a summary/aggregate store rather than a high-transaction operational table.

From a data modeling perspective, the mined metadata suggests a standalone classification, meaning the table carries its own descriptive measures (quantities and costs) rather than acting purely as a junction. It is best treated as a satellite-style fact holder whose grain is defined by its unique key.

Key Information Stored

The most significant columns fall into two groups: the composite business key and the measured facts.

• ORGANIZATION_ID — identifies the inventory organization whose period close data is recorded.
• ACCT_PERIOD_ID — the accounting period for the closed period.
• COST_GROUP_ID — the cost group identifier; for intransit inventory this is 1.
• INVENTORY_ITEM_ID — the item identifier.
• SECONDARY_INVENTORY — the subinventory code (VARCHAR2(10)); NULL for intransit records.
• PERIOD_END_QUANTITY — item quantity at period close.
• PERIOD_END_UNIT_COST — item cost at period close.
• LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — standard who columns providing audit lineage.
• PROGRAM_ID, PROGRAM_APPLICATION_ID — concurrent who columns identifying the period close concurrent program.

The surrogate-style composite primary key is MTL_PER_CLOSE_DTLS_PK1 over (ORGANIZATION_ID, ACCT_PERIOD_ID, COST_GROUP_ID, INVENTORY_ITEM_ID, SECONDARY_INVENTORY). The unique index MTL_PER_CLOSE_DTLS_U1 covers the identical five columns, making it the definitive business-key candidate and the natural uniqueness constraint for the table.

Common Use Cases and Queries

Because the table holds period end valuations by item and subinventory, it is commonly used for inventory valuation reporting, period-over-period comparisons, and reconciliation of the general ledger inventory balance. A typical pattern filters on organization and period:

• Period valuation extract: SELECT INVENTORY_ITEM_ID, SECONDARY_INVENTORY, PERIOD_END_QUANTITY, PERIOD_END_UNIT_COST FROM MTL_PER_CLOSE_DTLS WHERE ORGANIZATION_ID = :org AND ACCT_PERIOD_ID = :period.
• Intransit balance: filter WHERE COST_GROUP_ID = 1 AND SECONDARY_INVENTORY IS NULL to isolate the organization's intransit period end value.
• Period-over-period movement: self-join two ACCT_PERIOD_ID values on the same item/subinventory key to compute quantity and cost deltas.
• Audit of close runs: group by PROGRAM_ID and CREATION_DATE to confirm which concurrent close program populated each period.

Related Objects

• MTL_PERIOD_SUMMARY — the documented parent table; join on ORGANIZATION_ID and ACCT_PERIOD_ID.
• FND_LOGINS — referenced via the LOGIN_ID foreign key for sign-on context.
• MTL_SYSTEM_ITEMS_B — join on INVENTORY_ITEM_ID to resolve item names and descriptions.
• MTL_SECONDARY_INVENTORIES — join on ORGANIZATION_ID and SECONDARY_INVENTORY for subinventory attributes.
• CST_COST_GROUPS — join on COST_GROUP_ID to resolve cost group definitions.
• GL_PERIODS / ORG_ACCT_PERIODS — join on ACCT_PERIOD_ID to map periods to fiscal calendars.
• MTL_PARAMETERS — join on ORGANIZATION_ID for organization-level costing context.