CST_PERPAVG_COST

-- ============================================================
-- Table  : CST_PERPAVG_COST — The perpetual average cost history — recomputed transaction by transaction per cost org, book, item, valuation unit, and cost element; the latest row is the current average
-- Purpose: Column-selected read of CST_PERPAVG_COST — auto-generated from field metadata
-- Grain  : One row per TRANSACTION_ID + REC_TRXN_ID + DEP_TRXN_ID + COST_ELEMENT_ID + EFF_DATE
-- Caution: One row per recomputation per cost element — take the latest EFF_DATE per item/element for the current average; UNIT_COST_AVERAGE is the resulting number
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.TRANSACTION_ID AS "The costing transaction that triggered the recomputation",
  t.REC_TRXN_ID AS "Receipt transaction id — part of the composite key",
  t.DEP_TRXN_ID AS "Depleting transaction id — part of the composite key",
  t.COST_ELEMENT_ID AS "Cost element of this average slice",
  t.EFF_DATE AS "When this average became effective — latest row is current",
  t.PERPAVG_COST_ID AS "Surrogate id of the row",
  t.COST_ORG_ID AS "Cost organization",
  t.COST_BOOK_ID AS "Cost book",
  t.INVENTORY_ITEM_ID AS "The item",
  t.VAL_UNIT_ID AS "Valuation unit",
  t.QUANTITY_ONHAND AS "On-hand quantity at recomputation",
  t.UNIT_COST_AVERAGE AS "The resulting perpetual average — the number analytics wants",
  t.CURRENCY_CODE AS "Costing currency",
  t.UOM_CODE AS "Costing UOM"
FROM <catalog>.<schema>.CST_PERPAVG_COST t
WHERE
  1 = 1  -- no partition column on this table; the filters below are optional
  -- AND t.TRANSACTION_ID = <TRANSACTION_ID>
  -- AND t.REC_TRXN_ID = <REC_TRXN_ID>
  -- AND t.DEP_TRXN_ID = <DEP_TRXN_ID>
  -- AND t.COST_ELEMENT_ID = <COST_ELEMENT_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.TRANSACTION_ID;