INV_SERIAL_NUMBERS

-- ============================================================
-- Table  : INV_SERIAL_NUMBERS — The serial number master — definition and current position (status, org, subinventory, locator, lot) of every serialized unit, with manufacturing and shipping lifecycle dates
-- Purpose: Column-selected read of INV_SERIAL_NUMBERS — auto-generated from field metadata
-- Grain  : One row per SERIAL_NUMBER + INVENTORY_ITEM_ID + CURRENT_ORGANIZATION_ID
-- 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.SERIAL_NUMBER AS "The serialized unit's number",
  t.INVENTORY_ITEM_ID AS "Item the serial belongs to",
  t.CURRENT_ORGANIZATION_ID AS "Org where the unit currently resides — part of the key, and NOT named ORGANIZATION_ID",
  t.CURRENT_SUBINVENTORY_CODE AS "Current subinventory of the unit",
  t.CURRENT_LOCATOR_ID AS "Current locator of the unit",
  t.CURRENT_STATUS AS "Lifecycle state (defined, in stores, issued…) — a VARCHAR2 code here",
  t.STATUS_ID AS "Material status id (distinct from the lifecycle state)",
  t.LOT_NUMBER AS "Lot the unit belongs to, if lot-controlled",
  t.REVISION AS "Item revision of the unit",
  t.INITIALIZATION_DATE AS "When the serial was first initialized",
  t.COMPLETION_DATE AS "Manufacturing completion date",
  t.SHIP_DATE AS "When the unit shipped",
  t.WIP_ENTITY_ID AS "Work order reference",
  t.PARENT_ITEM_ID AS "Parent item for component genealogy",
  t.PARENT_SERIAL_NUMBER AS "Parent unit's serial in the genealogy"
FROM <catalog>.<schema>.INV_SERIAL_NUMBERS t
WHERE
  1 = 1  -- no partition column on this table; the filters below are optional
  -- AND t.SERIAL_NUMBER = '<SERIAL_NUMBER>'
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.CURRENT_ORGANIZATION_ID = <CURRENT_ORGANIZATION_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.SERIAL_NUMBER;