F4108

-- ============================================================
-- Table  : F4108 Lot master: one row per lot or serial number by item and branch, holding lot status, grade, potency, supplier lot linkage, and the full set of lot control dates.
-- Purpose: Column-selected read of F4108 — auto-generated from field metadata
-- Grain  : One row per IOLOTN + IOITM + IOMCU
-- 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
  f.IOLOTN AS "Lot or serial number identifying the specific lot of stock.",
  f.IOITM AS "Short internal item number (8-digit numeric key).",
  TRIM(f.IOMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f.IOLDSC AS "Free-text description of the lot.",
  f.IOLOTS AS "Lot status code; blank means approved, non-blank values are hold states.",
  f.IOLITM AS "Second (long) item number, the customer-facing alphanumeric item identifier.",
  f.IOAITM AS "Third item number, an alternate catalog-style identifier.",
  f.IOVEND AS "Supplier address number the lot was last received from.",
  f.IOKCOO AS "Company of the order that created the lot.",
  f.IODOCO AS "Order number that created the lot.",
  f.IODCTO AS "Order type of the document that created the lot.",
  f.IORLOT AS "The supplier's own lot number for traceability.",
  f.IOLOTP / POWER(10, 3) AS "Potency percentage of the lot for potency-controlled items.",  -- implied decimals: 3 (verify in F9210)
  f.IOLOTG AS "Lot grade code used for grade-controlled items.",
  CASE WHEN f.IOMMEJ IS NULL OR f.IOMMEJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IOMMEJ AS INT) DIV 1000, 1, 1), CAST(f.IOMMEJ AS INT) % 1000 - 1) END AS "Lot expiration date.",  -- CYYDDD Julian → DATE
  f.IOSERN AS "Serial number tied to the lot.",
  f.IOLRP1 AS "Lot category code 1 (site-defined lot classification).",
  f.IOLRP2 AS "Lot category code 2 (site-defined lot classification).",
  f.IOLRP3 AS "Lot category code 3 (site-defined lot classification).",
  f.IOLRP4 AS "Lot category code 4 (site-defined lot classification).",
  f.IOLRP5 AS "Lot category code 5 (site-defined lot classification).",
  f.IOLRP6 AS "Lot category code 6 (site-defined lot classification).",
  f.IOLRP7 AS "Lot category code 7 (site-defined lot classification).",
  f.IOLRP8 AS "Lot category code 8 (site-defined lot classification).",
  f.IOLRP9 AS "Lot category code 9 (site-defined lot classification).",
  f.IOLRP0 AS "Lot category code 10 (site-defined lot classification).",
  CASE WHEN f.IOBBDJ IS NULL OR f.IOBBDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IOBBDJ AS INT) DIV 1000, 1, 1), CAST(f.IOBBDJ AS INT) % 1000 - 1) END AS "Best-before date of the lot.",  -- CYYDDD Julian → DATE
  CASE WHEN f.IOBODJ IS NULL OR f.IOBODJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IOBODJ AS INT) DIV 1000, 1, 1), CAST(f.IOBODJ AS INT) % 1000 - 1) END AS "Based-on date from which other lot dates are calculated.",  -- CYYDDD Julian → DATE
  CASE WHEN f.IODLEJ IS NULL OR f.IODLEJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IODLEJ AS INT) DIV 1000, 1, 1), CAST(f.IODLEJ AS INT) % 1000 - 1) END AS "Date the lot becomes effective (available).",  -- CYYDDD Julian → DATE
  CASE WHEN f.IOOHDJ IS NULL OR f.IOOHDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IOOHDJ AS INT) DIV 1000, 1, 1), CAST(f.IOOHDJ AS INT) % 1000 - 1) END AS "Date the lot first went on hand.",  -- CYYDDD Julian → DATE
  CASE WHEN f.IOSBDJ IS NULL OR f.IOSBDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IOSBDJ AS INT) DIV 1000, 1, 1), CAST(f.IOSBDJ AS INT) % 1000 - 1) END AS "Sell-by date of the lot.",  -- CYYDDD Julian → DATE
  f.IOCHDF AS "Flag: lot is under a critical hold.",
  f.IOLOTR AS "Flag: lot number may be reused.",
  f.IOLOTC AS "Expanded (3-character) lot status code.",
  f.IOORIG AS "Country of origin of the lot."
FROM <catalog>.<schema_data>.f4108 f
WHERE
  f.IOITM = <ITM>
  -- AND f.IOMCU = '<MCU>'
ORDER BY f.IOLOTN;