F4104

-- ============================================================
-- Table  : F4104 Item cross-reference: links internal item numbers to external identifiers such as customer or supplier part numbers, substitutes, and replacements, keyed by cross-reference type.
-- Purpose: Column-selected read of F4104 — auto-generated from field metadata
-- Grain  : One row per IVITM + IVXRT + IVAN8 + IVCITM + IVEXDJ + IVCIRV
-- 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.IVITM AS "Short internal item number (8-digit numeric key).",
  f.IVXRT AS "Cross-reference type code that says what kind of link this is (customer item, vendor item, substitute, replacement, bar code, etc.).",
  f.IVAN8 AS "Address book number the external item number belongs to (customer or supplier); zero for generic references.",
  f.IVCITM AS "The external (customer's or supplier's) item number being cross-referenced.",
  CASE WHEN f.IVEXDJ IS NULL OR f.IVEXDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IVEXDJ AS INT) DIV 1000, 1, 1), CAST(f.IVEXDJ AS INT) % 1000 - 1) END AS "Expiration date; the record stops applying after this date.",  -- CYYDDD Julian → DATE
  f.IVCIRV AS "Revision level of the customer/supplier item.",
  CASE WHEN f.IVEFTJ IS NULL OR f.IVEFTJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IVEFTJ AS INT) DIV 1000, 1, 1), CAST(f.IVEFTJ AS INT) % 1000 - 1) END AS "Effective-from date; the record applies on or after this date.",  -- CYYDDD Julian → DATE
  TRIM(f.IVMCU) AS "Branch/plant (business unit); stored right-justified with leading blanks.",
  f.IVDSC1 AS "Description of the cross-referenced item.",
  f.IVDSC2 AS "Second description line of the cross-referenced item.",
  f.IVALN AS "Compressed description used for word search.",
  f.IVLITM AS "Second (long) item number, the customer-facing alphanumeric item identifier.",
  f.IVAITM AS "Third item number, an alternate catalog-style identifier.",
  f.IVURCD AS "User-reserved code (open field for site-specific use).",
  CASE WHEN f.IVURDT IS NULL OR f.IVURDT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IVURDT AS INT) DIV 1000, 1, 1), CAST(f.IVURDT AS INT) % 1000 - 1) END AS "User-reserved date (open field for site-specific use).",  -- CYYDDD Julian → DATE
  f.IVURAT / POWER(10, 2) AS "User-reserved amount (open field for site-specific use).",  -- implied decimals: 2 (verify in F9210)
  f.IVURAB AS "User-reserved number (open field for site-specific use).",
  f.IVURRF AS "User-reserved reference (open field for site-specific use).",
  f.IVRATO / POWER(10, 4) AS "Quantity ratio applied when substituting this item.",  -- implied decimals: 4 (verify in F9210)
  f.IVAPSP / POWER(10, 4) AS "Priority order among substitute items.",  -- implied decimals: 4 (verify in F9210)
  f.IVIDEM AS "Flag: transfer demand to the substitute item in planning.",
  f.IVAPSS AS "Flag: substitute usable by APS planning.",
  f.IVADIND AS "Flag: price adjustments apply to the cross-referenced item.",
  f.IVBPIND AS "Flag: base price is retrieved for the cross-referenced item.",
  f.IVCARDNO AS "Card number used for country-of-origin style references."
FROM <catalog>.<schema_data>.f4104 f
WHERE
  f.IVITM = <ITM>
  -- AND f.IVAN8 = <AN8>
ORDER BY f.IVITM;