MILOMA

-- ============================================================
-- Table  : MILOMA — The lot master — one row per item and lot (batch) number, carrying the lot's identity and life-cycle dates; location balances and allocations reference it by BANO
-- Purpose: Column-selected read of MILOMA — auto-generated from field metadata
-- Grain  : One row per company (CONO) + LMITNO + LMBANO
-- Notes  : Auto-generated skeleton for a prefixed physical M3 schema or a landing schema normalized to the prefixed names in this catalog. Raw Data Lake property names vary with the published object: map them through Data Catalog before running this SQL. Dates are numeric YYYYMMDD (0 = none, mapped to NULL); all curated status values are decoded inline. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  m.LMCONO AS "Company",
  m.LMITNO AS "Item number",
  m.LMBANO AS "Lot (batch) number — unique per item, not global",
  CASE WHEN m.LMEXPI = 0 THEN NULL ELSE TO_DATE(CAST(CAST(m.LMEXPI AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Expiration date of the lot",  -- YYYYMMDD, 0 → NULL
  CASE WHEN m.LMMFDT = 0 THEN NULL ELSE TO_DATE(CAST(CAST(m.LMMFDT AS BIGINT) AS STRING), 'yyyyMMdd') END AS "Manufacturing date of the lot"  -- YYYYMMDD, 0 → NULL
FROM <catalog>.<schema>.MILOMA m
WHERE
  m.LMCONO = <company>
  -- AND m.LMITNO = '<ITNO>'
  -- AND m.LMBANO = '<BANO>'
ORDER BY m.LMITNO;