CST_ITEM_COSTS

-- ============================================================
-- Table  : CST_ITEM_COSTS — The item cost header — one row per item, organization, and cost type, carrying the total unit cost and its five cost-element buckets plus this-level/previous-level splits
-- Purpose: Column-selected read of CST_ITEM_COSTS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_ID + COST_TYPE_ID
-- Caution: One row per item per org PER COST TYPE — filter one cost type or unit costs multiply; which type is current depends on the org's costing method (see MTL_PARAMETERS)
-- 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 — the cost is per item, org, and cost type",
  t.ORGANIZATION_ID AS "Inventory organization",
  t.COST_TYPE_ID AS "The cost type — filter to one or unit costs multiply",
  t.ITEM_COST AS "Total unit cost — the sum of the element buckets",
  t.MATERIAL_COST AS "Material element of the unit cost",
  t.MATERIAL_OVERHEAD_COST AS "Material overhead element",
  t.RESOURCE_COST AS "Resource element",
  t.OUTSIDE_PROCESSING_COST AS "Outside processing element",
  t.OVERHEAD_COST AS "Overhead element",
  t.TL_MATERIAL AS "This-level material — the split between the item's own cost and its components'",
  t.TL_RESOURCE AS "This-level resource",
  t.PL_MATERIAL AS "Previous-level material — rolled up from components",
  t.INVENTORY_ASSET_FLAG AS "Whether the item is an inventory asset under this cost type",
  t.BASED_ON_ROLLUP_FLAG AS "Whether the cost came from a rollup vs manual entry",
  t.LOT_SIZE AS "Costing lot size lot-basis charges spread over",
  t.SHRINKAGE_RATE AS "Shrinkage rate applied in the rollup"
FROM <catalog>.<schema>.CST_ITEM_COSTS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.COST_TYPE_ID = <COST_TYPE_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.INVENTORY_ITEM_ID;