Results for “insertion_flag”

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

Overview

MTL_PAC_ACT_CST_DTL_TEMP is a transient working table in the INV (Inventory) schema of Oracle E-Business Suite, appearing as a VALID object in both 12.1.1 and 12.2.2. The ETRM documentation describes its purpose concisely: it is "used to temporarily hold data during the interorg iteration process." This places the table squarely within the Periodic Average Cost (PAC) costing engine, which recalculates item costs across organizations during the cost processor run and the inter-organization transfer iteration. Rather than serving as a permanent transaction repository, the table acts as a staging buffer where the cost processor deposits intermediate cost calculations — prior versus new cost components, buy versus make breakdowns, and transfer-related cost amounts — before those results are validated and merged into the permanent PAC item cost and cost layer tables.

Because the table has no foreign keys directed at it and is populated and consumed exclusively by concurrent cost processing programs, the Data Vault classification mined from the FK structure is "standalone." As a modeling suggestion, this is best described as a satellite-style staging structure: it carries descriptive, non-key cost attributes attached to a composite business key, but exists only for the duration of one processing cycle. Rows are not intended to persist between cost runs, and DBAs should treat the object as a high-churn, ephemeral table when assessing segment growth, tablespace sizing, and statistics staleness.

Key Information Stored

The documented physical schema contains 28 columns. The unique index MTL_PAC_ACT_CST_DTL_TEMP_U1 covers TRANSACTION_ID, PAC_PERIOD_ID, COST_GROUP_ID, COST_ELEMENT_ID, and LEVEL_TYPE, identifying this five-column tuple as the business-key candidate for a staging row. The most operationally significant columns include:

• TRANSACTION_ID — the inventory transaction being costed; a core member of the unique key and the primary join path back to transaction data.
• PAC_PERIOD_ID, COST_GROUP_ID, COST_ELEMENT_ID, LEVEL_TYPE — the remaining unique-key members that scope the row to a period, cost group, cost element (material, overhead, etc.), and cost level.
• COST_TYPE_ID, COST_LAYER_ID, INVENTORY_ITEM_ID — foreign-key references identifying the cost type, the PAC item cost layer, and the item under evaluation.
• PRIOR_COST, ACTUAL_COST, NEW_COST — the comparison triplet representing the cost before recalculation, the actual computed cost, and the resulting new cost.
• PRIOR_BUY_COST, PRIOR_MAKE_COST, NEW_BUY_COST, NEW_MAKE_COST — the buy/make breakdown of prior and new costs, essential to organizations using both purchased and manufactured items.
• TRANSFER_TRANSACTION_ID, SHIPMENT_NUMBER, TRANSFER_COST, TRANSPORTATION_COST — inter-organization transfer context and the freight or transfer charges to be absorbed into the receiving organization's cost.
• NEW_ONHAND_QUANTITY, NEW_BUY_QUANTITY, NEW_MAKE_QUANTITY — quantity bases used to apportion cost across the affected inventory.
• MOH_ABSORPTION_COST, VARIANCE_AMOUNT, WIP_VARIANCE — manufacturing overhead absorption and variance amounts carried for cost update.
• USER_ENTERED, INSERTION_FLAG, TRANSACTION_COSTED_DATE — processing flags and the effective costing date.

Common Use Cases and Queries

The dominant use case is diagnostic: when inter-organization transfer costs appear incorrect or the PAC cost processor errors out, support and development teams inspect this table to see the intermediate values the processor computed before the final cost update. A typical query scans a single cost run by item and period:

• SELECT transaction_id, inventory_item_id, cost_element_id, prior_cost, actual_cost, new_cost FROM mtl_pac_act_cst_dtl_temp WHERE pac_period_id = :period_id AND inventory_item_id = :item_id;
• Buy/make analysis for a cost group: SELECT transaction_id, prior_buy_cost, prior_make_cost, new_buy_cost, new_make_cost FROM mtl_pac_act_cst_dtl_temp WHERE cost_group_id = :group_id ORDER BY transaction_id;
• Transfer cost reconciliation: SELECT transfer_transaction_id, shipment_number, transfer_cost, transportation_cost FROM mtl_pac_act_cst_dtl_temp WHERE transfer_transaction_id IS NOT NULL;

Because the table is periodically purged by the cost processor, it should never be used as a reporting source of record. Any permanent reporting of actual cost changes must be taken from CST_PAC_ITEM_COSTS and the PAC cost layer tables after the run completes.

Related Objects

The documented foreign keys define the table's dependencies on the core PAC costing schema:

• CST_COST_GROUPS — joined on COST_GROUP_ID; defines the cost group owning the transaction.
• CST_PAC_PERIODS — joined on PAC_PERIOD_ID; identifies the PAC costing period being processed.
• CST_COST_TYPES — joined on COST_TYPE_ID; identifies the cost type (e.g., frozen, average) in context.
• CST_PAC_ITEM_COSTS — joined on COST_LAYER_ID; the permanent PAC item cost layer receiving the new cost values.
• MTL_MATERIAL_TRANSACTIONS — the natural join target for TRANSACTION_ID and TRANSFER_TRANSACTION_ID, providing transaction type, quantity, and organization context.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID to resolve item number and description in diagnostic queries.
• CST_COST_ELEMENTS — the lookup backing COST_ELEMENT_ID, used to decode which cost element (material, material overhead, resource, overhead) a row affects.