Results for “cst_pac_quantity_layers_pk”

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

Overview

CST_PAC_QUANTITY_LAYERS is an Oracle EBS table owned by the BOM schema (Bills of Material) that stores the layer quantity of an item by period for periodic costing. It is a central component of the Periodic Average Cost (PAC) architecture within Oracle Cost Management, which allows organizations to cost inventory using a periodic average rather than a perpetual basis. Each row captures the quantity attributable to a specific cost layer within a defined PAC period and cost group, providing the volumetric counterpart to the cost values maintained elsewhere in the PAC schema.

Heuristic Data Vault modeling suggests classifying CST_PAC_QUANTITY_LAYERS as a link table. This classification reflects its role as an associative structure that binds together independent business concepts — item cost layers, PAC periods, and cost groups — rather than acting as a standalone hub of unique business entities or a satellite of descriptive attributes.

Key Information Stored

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

• QUANTITY_LAYER_ID — Part of the composite primary key (CST_PAC_QUANTITY_LAYERS_PK); a surrogate identifier for the quantity layer record.
• COST_LAYER_ID — Also part of the composite primary key; a foreign key to CST_PAC_ITEM_COSTS linking the quantity record to its corresponding cost layer.
• PAC_PERIOD_ID — Foreign key to CST_PAC_PERIODS, identifying the periodic costing period to which the layer belongs.
• COST_GROUP_ID — Foreign key to CST_COST_GROUPS, partitioning quantities by cost group (e.g., material, material overhead, resource).
• INVENTORY_ITEM_ID — The inventory item to which the layer quantity applies.
• LAYER_QUANTITY — The quantity held in the layer for the period.
• BEGIN_LAYER_QUANTITY — The beginning quantity of the layer, supporting period-over-period rollforward reporting.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, plus concurrent program context (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE).

The unique index CST_PAC_QUANTITY_LAYERS_U1 on (QUANTITY_LAYER_ID, COST_LAYER_ID) serves as the business-key candidate, mirroring the primary key definition.

Common Use Cases and Queries

Typical scenarios include periodic cost rollforward reporting, layer-level quantity reconciliation, and variance analysis comparing beginning and ending quantities within a PAC period.

• Retrieving layer quantities for an item in a given period:

  SELECT q.quantity_layer_id, q.cost_layer_id, q.layer_quantity,
         q.begin_layer_quantity, q.cost_group_id
  FROM   cst_pac_quantity_layers q, cst_pac_periods p
  WHERE  q.pac_period_id = p.pac_period_id
  AND    q.inventory_item_id = :item_id
  AND    p.period_name = :period_name;

• Joining to cost layers to derive unit costs:

  SELECT q.inventory_item_id, q.layer_quantity, c.layer_cost
  FROM   cst_pac_quantity_layers q, cst_pac_item_costs c
  WHERE  q.cost_layer_id = c.cost_layer_id;

• Aggregating quantity by cost group for a period to support average cost computation.
• Reconciling periodic layer quantities against perpetual inventory balances during period close.

Related Objects

• CST_PAC_ITEM_COSTS — joined via COST_LAYER_ID; holds the cost value for each layer.
• CST_PAC_PERIODS — joined via PAC_PERIOD_ID; defines periodic costing periods.
• CST_COST_GROUPS — joined via COST_GROUP_ID; defines cost group partitioning.
• CST_ITEM_COSTS and CST_COST_LAYERS — related cost layer infrastructure sharing the layer identifiers.
• MTL_SYSTEM_ITEMS_B — source of item validation via INVENTORY_ITEM_ID.
• Periodic costing concurrent programs (e.g., Period Close and average cost calculation) that populate and consume this table.