Search Results cst_quantity_layers




Overview
Explore in AI Chat

Overview

The CST_QUANTITY_LAYERS table is a core transactional data store within the Oracle E-Business Suite (EBS) Bills of Material (BOM) product module. It serves as the fundamental repository for implementing the perpetual inventory costing method, specifically the "layered costing" approach. This table's primary role is to maintain a historical record of distinct inventory layers, where each layer represents a batch of items added to stock at a specific unit cost. When inventory is issued, the system references these layers—typically using methods like First-In, First-Out (FIFO) or Last-In, First-Out (LIFO)—to determine the cost of goods sold and the remaining inventory valuation. Its integrity is critical for accurate financial reporting, cost management, and inventory accounting in releases 12.1.1 and 12.2.2.

Key Information Stored

Each row in CST_QUANTITY_LAYERS corresponds to a unique cost layer for an item in a specific organization. The primary key, LAYER_ID, uniquely identifies each layer. Essential columns include INVENTORY_ITEM_ID and ORGANIZATION_ID, which link the layer to the master item definition in MTL_SYSTEM_ITEMS_B. The table stores the core transactional quantities and costs: primary and secondary transaction quantities, the unit cost for the layer, and the remaining quantity on hand. Additional columns track the transaction that created the layer (e.g., transaction date, transaction ID, transaction action ID) and system control information such as creation date and last update date. This structure allows the system to preserve the cost history of every inventory addition.

Common Use Cases and Queries

The primary use case is inventory valuation and cost flow calculation during material transactions. When a shipment or issue occurs, costing engines query this table to identify the appropriate layer(s) to relieve based on the costing method. Common reporting queries involve analyzing inventory value by summarizing remaining quantities multiplied by layer cost. A fundamental sample SQL pattern retrieves the current on-hand layers for an item:

  • SELECT layer_id, inventory_item_id, organization_id, primary_quantity, unit_cost
  • FROM bom.cst_quantity_layers
  • WHERE inventory_item_id = :item_id
  • AND organization_id = :org_id
  • AND primary_quantity > 0
  • ORDER BY transaction_date; -- For FIFO analysis

This data is also crucial for period-end closing procedures, cost variance analysis, and generating detailed inventory valuation reports.

Related Objects

CST_QUANTITY_LAYERS sits at the center of a key data model. It has documented foreign key relationships with the following objects:

  • MTL_SYSTEM_ITEMS_B: The layer is linked to the item master via the composite foreign key on (INVENTORY_ITEM_ID, ORGANIZATION_ID). This ensures the layer references a valid item-organization combination.
  • CST_LAYER_COST_DETAILS: This table holds the detailed cost elements (material, overhead, etc.) that make up the total unit cost in the quantity layer. It references CST_QUANTITY_LAYERS through the LAYER_ID column, creating a one-to-many relationship where a single cost layer can have multiple cost detail components.

Furthermore, the table is heavily referenced by internal costing APIs and engine programs (e.g., CSTPACCI, CSTPACIN) which create, update, and relieve layers during transaction processing.