MMS850MI

-- ============================================================
-- Program: MMS850MI — Stock movement API — moves stock between locations programmatically, writing the MITLOC balances and their MITTRA transactions
-- Purpose: Read the tables behind API program MMS850MI — auto-generated from program-table-map
-- Grain  : MITLOC × MILOMA — 1:N joins yield one row per line
-- Tables : MITLOC, MILOMA
-- 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; company-partitioned tables are joined on CONO to prevent cross-company fan-out. Audit columns (RGDT/RGTM/LMDT/CHNO/CHID) omitted — see the quirks guide.
-- ============================================================
SELECT
  il.MLCONO AS "Company",
  il.MLWHLO AS "Warehouse",
  il.MLITNO AS "Item number",
  il.MLWHSL AS "Stock location within the warehouse",
  il.MLBANO AS "Lot (batch) number — blank for non-lot-controlled items",
  il.MLREPN AS "Receiving number distinguishing receipts of the same item and lot in the same location",
  il.MLSTQT AS "On-hand balance at this location/lot grain, in the basic unit",
  il.MLALQT AS "Allocated quantity at this location/lot grain",
  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
FROM <catalog>.<schema>.MITLOC il
LEFT JOIN <catalog>.<schema>.MILOMA m
  ON m.LMBANO = il.MLBANO
 AND m.LMITNO = il.MLITNO
 AND m.LMCONO = il.MLCONO
WHERE
  il.MLCONO = <company>
ORDER BY il.MLWHLO;