P41202

-- ============================================================
-- Program: P41202 Item Availability — inquire on on-hand, committed, and available quantities by item, branch, location, and lot
-- Purpose: Item Availability — inquire on on-hand, committed, and available quantities by item, branch, location, and lot — auto-generated boilerplate from program-table-map
-- Grain  : F41021 × F4102, F4101, F41001 — 1:N joins yield one row per detail line
-- Tables : F41021, F4102, F4101, F41001
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  loc.LIITM AS "Short item number - the internal numeric item key",
  TRIM(loc.LIMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  loc.LILOCN AS "Storage location within the branch plant",
  loc.LILOTN AS "Lot or serial number",
  f.CISY AS "System/product code the constants row belongs to (typically 41)."
FROM <catalog>.<schema_data>.f41021 loc
LEFT JOIN <catalog>.<schema_data>.f4102 ib
  ON ib.IBITM = loc.LIITM
 AND ib.IBMCU = loc.LIMCU
LEFT JOIN <catalog>.<schema_data>.f4101 im
  ON im.IMITM = loc.LIITM
LEFT JOIN <catalog>.<schema_data>.f41001 f
  ON f.CIMCU = loc.LIMCU
WHERE
  loc.LIITM = <ITM>
  AND loc.LIMCU = '<MCU>'
ORDER BY loc.LIITM;