MTL_LOT_NUMBERS

-- ============================================================
-- Table  : MTL_LOT_NUMBERS — The lot master — definition, genealogy (parent lot), status, grade, and the origination/expiration/retest dates for every lot, keyed by item + organization + lot number
-- Purpose: Column-selected read of MTL_LOT_NUMBERS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_ID + LOT_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 — lots are defined per item",
  t.ORGANIZATION_ID AS "Inventory organization — lots are defined per org too",
  t.LOT_NUMBER AS "The lot number — only meaningful together with item and org",
  t.PARENT_LOT_NUMBER AS "Parent lot for split/merge genealogy",
  t.ORIGINATION_DATE AS "When the lot came into existence",
  t.EXPIRATION_DATE AS "When the lot expires — the shelf-life analysis date",
  t.RETEST_DATE AS "When the lot is due for retest",
  t.STATUS_ID AS "Material status of the lot",
  t.GRADE_CODE AS "Quality grade of the lot",
  t.DISABLE_FLAG AS "Whether the lot is disabled — a NUMBER, not a Y/N character flag",
  t.SUPPLIER_LOT_NUMBER AS "The supplier's own lot number, kept for traceability"
FROM <catalog>.<schema>.MTL_LOT_NUMBERS 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.LOT_NUMBER = '<LOT_NUMBER>'
  -- AND t.EXPIRATION_DATE >= DATE '<DATE_FROM>'
  -- AND t.EXPIRATION_DATE <= DATE '<DATE_TO>'
  -- 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;