MTL_SERIAL_NUMBERS

-- ============================================================
-- Table  : MTL_SERIAL_NUMBERS — The serial number master — definition and current position (status, org, subinventory, locator, lot) of every serialized unit; a serial is unique per item, not per org
-- Purpose: Column-selected read of MTL_SERIAL_NUMBERS — auto-generated from field metadata
-- Grain  : One row per INVENTORY_ITEM_ID + SERIAL_NUMBER
-- 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 — a serial is unique per item, across all orgs",
  t.SERIAL_NUMBER AS "The serial number",
  t.CURRENT_ORGANIZATION_ID AS "Where the unit sits now — an attribute, not part of the key; filter it when you mean one org's serials",
  t.CURRENT_STATUS AS "Lifecycle state — defined-not-used, in stores, issued, in transit",
  t.CURRENT_SUBINVENTORY_CODE AS "The subinventory the unit currently sits in",
  t.CURRENT_LOCATOR_ID AS "The locator the unit currently sits in",
  t.LOT_NUMBER AS "The lot the unit belongs to, for lot-and-serial items",
  t.INITIALIZATION_DATE AS "When the serial was first used",
  t.SHIP_DATE AS "When the unit shipped",
  t.ORIGINAL_WIP_ENTITY_ID AS "The job that built the unit, when WIP-sourced"
FROM <catalog>.<schema>.MTL_SERIAL_NUMBERS t
WHERE
  1 = 1  -- no partition column on this table; the filters below are optional
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.SERIAL_NUMBER = '<SERIAL_NUMBER>'
  -- 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;