INV_LOT_NUMBERS

-- ============================================================
-- Table  : INV_LOT_NUMBERS — The lot master — definition, genealogy (parent lot), grade, and the origination/expiration/retest dates for every lot, keyed by item + organization + lot number
-- Purpose: Column-selected read of INV_LOT_NUMBERS — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + LOT_NUMBER + INVENTORY_ITEM_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.LOT_NUMBER AS "Lot identifier — unique only together with item and org",
  t.INVENTORY_ITEM_ID AS "Item the lot belongs to",
  t.ORGANIZATION_ID AS "Org the lot record belongs to",
  t.PARENT_LOT_NUMBER AS "Parent lot for split lots — the genealogy pointer",
  t.STATUS_ID AS "Material status id of the lot",
  t.GRADE_CODE AS "Quality grade of the lot",
  t.DISABLE_FLAG AS "Whether the lot is disabled — a VARCHAR2 code here (it was a NUMBER in EBS)",
  t.ORIGINATION_DATE AS "Lot creation date",
  t.ORIGINATION_TYPE AS "How the lot originated",
  t.EXPIRATION_DATE AS "When the lot expires",
  t.RETEST_DATE AS "Retest due date",
  t.MATURITY_DATE AS "Maturity date",
  t.EXPIRATION_ACTION_CODE AS "Action to take at expiry",
  t.EXPIRATION_ACTION_DATE AS "When the expiry action is due",
  t.HOLD_DATE AS "Hold-until date",
  t.SUPPLIER_LOT_NUMBER AS "The vendor's lot number from receipt"
FROM <catalog>.<schema>.INV_LOT_NUMBERS t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  -- AND t.LOT_NUMBER = '<LOT_NUMBER>'
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.EXPIRATION_DATE >= DATE '<DATE_FROM>'
  -- AND t.EXPIRATION_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.LOT_NUMBER;