CST_ITEM_COST_DETAILS

-- ============================================================
-- Table  : CST_ITEM_COST_DETAILS — The cost-element breakdown under each item cost — one row per element, level, and department/operation/resource contribution; where a rolled-up cost's ingredients live
-- Purpose: Column-selected read of CST_ITEM_COST_DETAILS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) — see the Fields section for the full key
-- Caution: No unique key — this is a multi-row breakdown; SUM ITEM_COST at your grain and never join it to the cost header expecting one row
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. 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.INVENTORY_ITEM_ID AS "Item id — part of the working key with org and cost type; no unique key exists on this table",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.COST_TYPE_ID AS "The cost type the breakdown belongs to",
  t.COST_ELEMENT_ID AS "Which of the five cost elements the row contributes to",
  t.LEVEL_TYPE AS "This level vs previous level (rolled up from components)",
  t.OPERATION_SEQUENCE_ID AS "The routing operation behind resource rows",
  t.OPERATION_SEQ_NUM AS "The operation number",
  t.DEPARTMENT_ID AS "The department behind resource rows",
  t.RESOURCE_ID AS "The resource or sub-element charged",
  t.RESOURCE_RATE AS "The resource rate used",
  t.USAGE_RATE_OR_AMOUNT AS "Usage rate or amount behind the contribution",
  t.BASIS_TYPE AS "Item vs lot basis",
  t.BASIS_FACTOR AS "Basis factor applied",
  t.ITEM_COST AS "This row's contribution to the unit cost — SUM at your grain",
  t.ROLLUP_SOURCE_TYPE AS "User-defined, defaulted, or rolled-up row"
FROM <catalog>.<schema>.CST_ITEM_COST_DETAILS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
;