F3002

-- ============================================================
-- Table  : F3002 One row per component line on a parent item's bill of material at a branch/plant, effective-dated, holding the quantity-per and usage rules that drive BOM explosion and product costing.
-- Purpose: Column-selected read of F3002 — auto-generated from field metadata
-- Grain  : One row per IXTBM + IXKIT + IXMMCU + IXSBNT + IXBQTY + IXCOBY + IXCPNB
-- 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.IXTBM AS "Bill type — M is the standard manufacturing bill; other UDC values cover rework, spare parts, and planning bills.",
  f.IXKIT AS "Short item number of the parent being built — the top of this BOM line.",
  TRIM(f.IXMMCU) AS "Branch/plant that owns this bill — BOMs are branch-specific in JDE.",
  f.IXSBNT / POWER(10, 2) AS "Sequence that orders substitute items for a component; 0 for the primary component line.",  -- implied decimals: 2 (verify in F9210)
  f.IXBQTY AS "Batch quantity of the parent that the quantity-per is stated against; part of the key, so one item can carry separate bills per batch size — implied decimals vary by setup.",
  f.IXCOBY AS "Classifies the line as a component, co-product, by-product, or intermediate.",
  f.IXCPNB / POWER(10, 2) AS "System-assigned BOM component line number that uniquifies lines within a parent/branch/type/batch key.",  -- implied decimals: 2 (verify in F9210)
  f.IXKITL AS "Parent item's 25-character second item number (the human-readable part number).",
  f.IXITM AS "Short item number of the component consumed on this line.",
  f.IXLITM AS "Component's 25-character second item number.",
  TRIM(f.IXCMCU) AS "Branch/plant the component is sourced from when it differs from the parent's branch.",
  f.IXCPNT / POWER(10, 1) AS "Component line number as displayed on the bill.",  -- implied decimals: 1 (verify in F9210)
  f.IXQNTY AS "Quantity of the component required per batch quantity of the parent (quantity-per); implied decimals vary by setup.",
  f.IXUM AS "Unit of measure for the component quantity.",
  f.IXUOM AS "Unit of measure the batch quantity was entered in.",
  f.IXFVBT AS "Flag marking the batch size as fixed or variable.",
  CASE WHEN f.IXEFFF IS NULL OR f.IXEFFF = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IXEFFF AS INT) DIV 1000, 1, 1), CAST(f.IXEFFF AS INT) % 1000 - 1) END AS "Date this BOM line becomes effective, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  CASE WHEN f.IXEFFT IS NULL OR f.IXEFFT = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.IXEFFT AS INT) DIV 1000, 1, 1), CAST(f.IXEFFT AS INT) % 1000 - 1) END AS "Date this BOM line expires, stored as Julian CYYDDD.",  -- CYYDDD Julian → DATE
  f.IXITC AS "Issue type code controlling how the component is issued to work orders (manual, backflush, floor stock, etc.).",
  f.IXFORQ AS "Flag marking the component quantity as fixed (per order) or variable (scales with order quantity).",
  f.IXCOTY AS "Component type classifier used mainly in process manufacturing.",
  f.IXOPSQ / POWER(10, 2) AS "Routing operation sequence where this component is consumed — the link between the BOM line and the F3003 routing step.",  -- implied decimals: 2 (verify in F9210)
  f.IXSCRP / POWER(10, 2) AS "Expected scrap percent for the component, inflating planned requirements.",  -- implied decimals: 2 (verify in F9210)
  f.IXCPYP / POWER(10, 2) AS "Cumulative planned yield percent from this operation to the end of the routing.",  -- implied decimals: 2 (verify in F9210)
  f.IXSTPP / POWER(10, 2) AS "Scrap percent applied at the operation where the component is consumed.",  -- implied decimals: 2 (verify in F9210)
  f.IXLOVD AS "Leadtime offset in days that shifts when the component is required relative to the work order start.",
  f.IXPRIC / POWER(10, 4) AS "Unit price per primary unit, used for kit/configured pricing.",  -- implied decimals: 4 (verify in F9210)
  f.IXUNCS / POWER(10, 4) AS "Unit cost of the component captured on the bill line.",  -- implied decimals: 4 (verify in F9210)
  f.IXBREV AS "Revision level of the bill this line belongs to."
FROM <catalog>.<schema_data>.f3002 f
ORDER BY f.IXTBM;