F4111

-- ============================================================
-- Table  : F4111 Item ledger (Cardex) — every inventory movement with quantity, cost, and links back to source documents
-- Purpose: Column-selected read of F4111 — auto-generated from field metadata
-- Grain  : One row per ILUKID
-- 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
  il.ILUKID AS "Unique key ID - the system-assigned surrogate key",
  il.ILITM AS "Short item number - the internal numeric item key",
  il.ILLITM AS "Second item number - the primary user-facing item code",
  il.ILAITM AS "Third item number - catalog or cross-reference item code",
  TRIM(il.ILMCU) AS "Business unit / branch plant (right-justified, space-padded to 12)",
  il.ILLOCN AS "Storage location within the branch plant",
  il.ILLOTN AS "Lot or serial number",
  il.ILKCO AS "Document key company",
  il.ILDOC AS "Document number (invoice, voucher, JE) tied to the movement",
  il.ILDCT AS "Document type of the related document",
  il.ILICU AS "G/L batch number the movement posted in",
  il.ILJELN AS "Journal entry line number",
  CASE WHEN il.ILDGL IS NULL OR il.ILDGL = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(il.ILDGL AS INT) DIV 1000, 1, 1), CAST(il.ILDGL AS INT) % 1000 - 1) END AS "General ledger date the transaction posts to",  -- CYYDDD Julian → DATE
  il.ILGLPT AS "G/L class code used when the movement posted",
  il.ILDCTO AS "Related order type (SO, OP, WO, ...)",
  il.ILDOCO AS "Related order number that drove the movement",
  il.ILKCOO AS "Related order key company",
  il.ILLNID / POWER(10, 3) AS "Related order line number, stored x1000",  -- implied decimals: 3 (verify in F9210)
  il.ILIPCD AS "As-of posted code - whether the record is in the as-of file",
  CASE WHEN il.ILTRDJ IS NULL OR il.ILTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(il.ILTRDJ AS INT) DIV 1000, 1, 1), CAST(il.ILTRDJ AS INT) % 1000 - 1) END AS "Transaction date of the inventory movement",  -- CYYDDD Julian → DATE
  il.ILTRUM AS "Unit of measure of the transaction quantity",
  il.ILAN8 AS "Address book number tied to the movement (customer/supplier)",
  il.ILSHAN AS "Ship-to address book number",
  il.ILTREX AS "Free-text explanation of the movement",
  il.ILTREF AS "Transaction reference",
  il.ILRCD AS "Reason code for the adjustment/movement",
  il.ILTRQT AS "Transaction quantity (sign shows direction)",
  il.ILUNCS / POWER(10, 4) AS "Unit cost (4 implied decimals)",  -- implied decimals: 4 (verify in F9210)
  il.ILPAID / POWER(10, 2) AS "Extended cost/price of the movement",  -- implied decimals: 2 (verify in F9210)
  CASE WHEN il.ILCRDJ IS NULL OR il.ILCRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(il.ILCRDJ AS INT) DIV 1000, 1, 1), CAST(il.ILCRDJ AS INT) % 1000 - 1) END AS "Date the record was created",  -- CYYDDD Julian → DATE
  il.ILRE AS "Marks reversed or voided transactions",
  TRIM(il.ILMCUZ) AS "Alternate business unit charged by the movement"
FROM <catalog>.<schema_data>.f4111 il
WHERE
  il.ILTRDJ >= <TRDJ_FROM>  -- Julian CYYDDD, e.g. 126001
  -- AND il.ILTRDJ <= <TRDJ_TO>  -- Julian CYYDDD, e.g. 126365
ORDER BY il.ILUKID;