Search Results cst_pac_explosion_temp




Overview
Explore in AI Chat

Overview

CST_PAC_EXPLOSION_TEMP is a transient working table owned by the BOM schema within Oracle E-Business Suite, positioned at the intersection of the Bills of Material and Cost Management modules. Its documented purpose is to store distinct parent-child combinations of items for a given period and organization cost group. In practice, this table supports the periodic average cost (PAC) rollup and cost explosion process, which traverses the bill of material structure to accumulate material, resource, and overhead costs for each assembly in a cost group. Because the explosion routine can reach the same parent-child pairing through multiple bill paths in a multi-level structure, CST_PAC_EXPLOSION_TEMP acts as a de-duplication and staging area, holding one row per distinct relationship before downstream cost computation proceeds.

From a Data Vault modeling perspective, the mined foreign-key structure suggests this object is best classified as a link. It resolves two independent business concepts — the cost period (referenced through PAC_PERIOD_ID) and the cost group (referenced through COST_GROUP_ID) — against the parent-child item relationship that forms the core of the explosion. It therefore behaves as a relationship or association table rather than a descriptive satellite or a standalone hub.

Key Information Stored

ETRM documents six physical columns for this table, and they map cleanly to the explosion grain. The most significant are:

  • PAC_PERIOD_ID — Foreign key to CST_PAC_PERIODS. Identifies the costing period for which the explosion is being performed; this is a primary business-key participant and scopes the entire working set.
  • COST_GROUP_ID — Foreign key to CST_COST_GROUPS. Identifies the organization cost group to which the exploded relationships belong, establishing the organizational context.
  • ASSEMBLY_ITEM_ID — The parent item in the bill relationship. Combined with the component, this defines the direction of the explosion.
  • COMPONENT_ITEM_ID — The child item consumed by the assembly. Together with ASSEMBLY_ITEM_ID it forms the distinct parent-child pair the table is designed to preserve.
  • DELETED — A status/flag column indicating whether the row has been logically removed from the active working set, allowing the explosion process to mark stale combinations without physical deletion.
  • LOOP_COUNT — A counter used by the explosion algorithm, typically to detect circular bill references or to bound iterative traversal depth.

No standalone surrogate primary key column is documented among the six columns; the natural business key is the composite of PAC_PERIOD_ID, COST_GROUP_ID, ASSEMBLY_ITEM_ID, and COMPONENT_ITEM_ID, which is what enforces the "distinct parent-child combination" guarantee described in the object metadata.

Common Use Cases and Queries

This table is primarily consumed by the cost explosion and PAC rollup concurrent programs rather than by end users. Typical diagnostic and reporting patterns include:

  • Verifying explosion scope — counting distinct parent-child pairs for a period and cost group:
    SELECT COUNT(*) FROM bom.cst_pac_explosion_temp WHERE pac_period_id = :p AND cost_group_id = :g AND deleted = 'N';
  • Identifying top-level parents — finding assemblies that never appear as components:
    SELECT DISTINCT assembly_item_id FROM bom.cst_pac_explosion_temp a WHERE NOT EXISTS (SELECT 1 FROM bom.cst_pac_explosion_temp b WHERE b.component_item_id = a.assembly_item_id AND b.pac_period_id = a.pac_period_id);
  • Loop investigation — flagging rows where LOOP_COUNT exceeds expected depth, indicating a potential circular bill.
  • Cleanup validation — confirming that fully DELETED period/group combinations have been purged before the next costing cycle.

Because it is a temporary staging table, queries should always be constrained by PAC_PERIOD_ID and COST_GROUP_ID to avoid full scans.

Related Objects

The documented foreign-key relationships anchor this table to the cost management schema:

  • CST_PAC_PERIODS — joined on CST_PAC_EXPLOSION_TEMP.PAC_PERIOD_ID; supplies period definition and status.
  • CST_COST_GROUPS — joined on CST_PAC_EXPLOSION_TEMP.COST_GROUP_ID; supplies cost group definition and organization linkage.
  • BOM_BILL_OF_MATERIALS / BOM_COMPONENTS — the structural source from which parent-child item combinations are derived.
  • CST_ITEM_COSTS — the downstream cost repository populated once the explosion is resolved.
  • MTL_SYSTEM_ITEMS_B — resolved via ASSEMBLY_ITEM_ID and COMPONENT_ITEM_ID for item descriptions and attributes.
  • CST_PAC_ROLLUP / PAC cost programs — the concurrent processes that populate and consume this temporary table during periodic average costing.