F41021

-- ============================================================
-- Table  : F41021 Item location — on-hand, committed, and inbound quantities by item, branch, location, and lot
-- Purpose: Column-selected read of F41021 — auto-generated from field metadata
-- Grain  : One row per LIITM + LIMCU + LILOCN + LILOTN
-- Notes  : Auto-generated skeleton. Julian dates (CYYDDD) are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210); padded business-unit/UDC keys are TRIMmed. 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",
  loc.LIPBIN AS "Flags the primary location for the item at this branch",
  loc.LIGLPT AS "G/L class code for this location (overrides branch/item value)",
  loc.LILOTS AS "Lot status code - blank means approved",
  CASE WHEN loc.LILRCJ IS NULL OR loc.LILRCJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(loc.LILRCJ AS INT) DIV 1000, 1, 1), CAST(loc.LILRCJ AS INT) % 1000 - 1) END AS "Date of last receipt into this location",  -- CYYDDD Julian → DATE
  loc.LIPQOH AS "Quantity on hand in primary UOM",
  loc.LIPBCK AS "Quantity on backorder",
  loc.LIPREQ AS "Quantity on open purchase orders",
  loc.LIQWBO AS "Quantity expected from open work orders",
  loc.LIOT1P AS "Other quantity 1 (primary UOM)",
  loc.LIOT2P AS "Other quantity 2 (primary UOM)",
  loc.LIOT1A AS "Other purchasing quantity 1",
  loc.LIHCOM AS "Quantity hard committed to specific locations/lots",
  loc.LIPCOM AS "Quantity soft committed by sales/work orders",
  loc.LIFCOM AS "Quantity committed to future dated orders",
  loc.LIFUN1 AS "Quantity soft committed to work orders",
  loc.LIQOWO AS "Quantity hard committed to work orders",
  loc.LIQTTR AS "Quantity in transit between branches",
  loc.LIQTIN AS "Quantity in inspection",
  loc.LIQONL AS "Quantity on loan to manufacturing",
  loc.LIQTRI AS "Inbound warehouse-management quantity",
  loc.LIQTRO AS "Outbound warehouse-management quantity",
  CASE WHEN loc.LINCDJ IS NULL OR loc.LINCDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(loc.LINCDJ AS INT) DIV 1000, 1, 1), CAST(loc.LINCDJ AS INT) % 1000 - 1) END AS "Next cycle count date"  -- CYYDDD Julian → DATE
FROM <catalog>.<schema_data>.f41021 loc
WHERE
  loc.LIITM = <ITM>
  -- AND loc.LIMCU = '<MCU>'
ORDER BY loc.LIITM;