# JD Edwards F3002 Multi-Level BOM Explosion SQL

> Source: https://netray.co/tools/jde-f3002-bom-explosion-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate an Oracle recursive SQL query that explodes a JD Edwards bill of material from F3002 level by level, plus a check of a work order parts list in F3111.

## Category

developer

## Erp

JD Edwards EnterpriseOne

## Audience

- JDE developer
- Manufacturing engineer
- Planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Business data schema

**Key:** schema

**Type:** text

**Default:** CRPDTA

#### Parent item (2nd item number IMLITM)

**Key:** parent

**Type:** text

**Default:** 1001

#### Branch/plant (IXMMCU)

**Key:** mcu

**Type:** text

**Default:** 30

#### Bill type (IXTBM)

**Key:** tbm

**Type:** text

**Default:** M

#### Effective date (Julian CYYDDD)

**Key:** eff

**Type:** text

**Default:** 126295

#### Parent quantity to explode

**Key:** qty

**Type:** text

**Default:** 1

#### Divisor for IXQNTY implied decimals

**Key:** qdiv

**Type:** text

**Default:** 1

**Help:** Check the display decimals of QNTY in P92001: 1, 100 or 10000.

#### Maximum levels

**Key:** maxlvl

**Type:** text

**Default:** 10

#### Work order for parts list check

**Key:** doco

**Type:** text

**Default:** 100245

**Blocks:**

#### Multi-level explosion (Oracle 11gR2+ recursive WITH)

**Body:** WITH parent_item AS (
  SELECT IMITM FROM {{schema}}.F4101 WHERE IMLITM = '{{parent}}'
),
bom (lvl, parent_itm, comp_itm, cpnt, qty_per, ext_qty, stkt) AS (
  SELECT 1,
         x.IXKIT,
         x.IXITM,
         x.IXCPNT,
         x.IXQNTY / {{qdiv}},
         CASE WHEN x.IXFORQ = 'F' THEN x.IXQNTY / {{qdiv}}
              ELSE {{qty}} * x.IXQNTY / {{qdiv}} END,
         b.IBSTKT
  FROM   {{schema}}.F3002 x
  JOIN   parent_item p ON p.IMITM = x.IXKIT
  LEFT JOIN {{schema}}.F4102 b ON b.IBITM = x.IXITM AND b.IBMCU = x.IXMMCU
  WHERE  LTRIM(RTRIM(x.IXMMCU)) = '{{mcu}}'
    AND  x.IXTBM = '{{tbm}}'
    AND  x.IXBQTY = 0
    AND  x.IXEFFF <= {{eff}} AND x.IXEFFT >= {{eff}}
  UNION ALL
  SELECT c.lvl + 1,
         x.IXKIT,
         x.IXITM,
         x.IXCPNT,
         x.IXQNTY / {{qdiv}},
         CASE WHEN x.IXFORQ = 'F' THEN x.IXQNTY / {{qdiv}}
              ELSE c.ext_qty * x.IXQNTY / {{qdiv}} END,
         b.IBSTKT
  FROM   bom c
  JOIN   {{schema}}.F3002 x ON x.IXKIT = c.comp_itm
  LEFT JOIN {{schema}}.F4102 b ON b.IBITM = x.IXITM AND b.IBMCU = x.IXMMCU
  WHERE  LTRIM(RTRIM(x.IXMMCU)) = '{{mcu}}'
    AND  x.IXTBM = '{{tbm}}'
    AND  x.IXBQTY = 0
    AND  x.IXEFFF <= {{eff}} AND x.IXEFFT >= {{eff}}
    AND  NVL(c.stkt, ' ') <> 'P'
    AND  c.lvl < {{maxlvl}}
)
SEARCH DEPTH FIRST BY cpnt SET seq
CYCLE comp_itm SET is_cycle TO 'Y' DEFAULT 'N'
SELECT c.lvl,
       LPAD(' ', 2 * (c.lvl - 1)) || i.IMLITM AS component,
       i.IMDSC1   AS description,
       c.qty_per,
       c.ext_qty  AS extended_qty,
       c.stkt     AS stocking_type,
       c.is_cycle
FROM   bom c
LEFT JOIN {{schema}}.F4101 i ON i.IMITM = c.comp_itm
ORDER  BY c.seq;

#### Compare with a work order parts list (F3111)

**Body:** SELECT m.WMDOCO,
       m.WMCPNT,
       i.IMLITM,
       i.IMDSC1,
       m.WMUORG                AS qty_required,
       m.WMTRQT                AS qty_issued,
       m.WMUORG - m.WMTRQT     AS qty_still_to_issue
FROM   {{schema}}.F3111 m
LEFT JOIN {{schema}}.F4101 i ON i.IMITM = m.WMCPIT
WHERE  m.WMDOCO = {{doco}}
ORDER  BY m.WMCPNT;

#### SQL Server variant

**Body:** -- SQL Server: drop the SEARCH and CYCLE clauses (add your own cycle guard),
-- replace NVL with ISNULL and || with +, cast IMLITM as needed,
-- carry a sort path column (CAST(... AS VARCHAR(4000))) to order depth first,
-- and add OPTION (MAXRECURSION 100) after the final ORDER BY.

**Notes:** Recursion stops at purchased items (stocking type P) and at the level limit. Components are exploded at the same branch/plant; if subassemblies are built in another branch, change the recursive join. IXBQTY = 0 selects the standard bill rather than batch bills. Check the decimals of IXQNTY, WMUORG and WMTRQT in your data dictionary before trusting the quantities.

## Content

**Intro:** This generator writes an Oracle recursive SQL query that explodes a JD Edwards EnterpriseOne bill of material from F3002 level by level, honouring effectivity dates, fixed quantity components and purchased items. A second query compares a work order parts list in F3111 so you can see what is still to issue.

**Sections:**

#### Where this lives in EnterpriseOne

**Body:** Bills are maintained in P3002 (Bill of Material Revisions) and stored in F3002, one row per parent and component line. The parent is IXKIT (short item number) at branch IXMMCU, with bill type IXTBM (M for manufacturing) and batch quantity IXBQTY. Each row has the component IXITM, component line IXCPNT, quantity per IXQNTY, fixed or variable flag IXFORQ and effectivity IXEFFF to IXEFFT. Item master F4101 gives the second item number IMLITM and description, and item branch F4102 gives the stocking type IBSTKT. When a work order is attached, the parts list is copied to F3111.

#### How to use it

**Body:** Enter the parent item's second item number, the branch/plant, bill type, an effective date in Julian, the quantity to explode and a level limit. Check how many decimals the quantity data item uses and set the divisor so 1.5 does not show as 150. The query returns one row per component in depth first order, indented by level, with quantity per parent and extended quantity for your parent quantity. Purchased items end their branch of the tree; manufactured subassemblies continue to explode.

**Bullets:**

- Fixed quantity components (IXFORQ = F) are not multiplied by the parent quantity
- Phantom and other stocking types continue to explode; only P stops
- The CYCLE clause flags a bill that contains its own parent

#### Worked example

**Body:** Exploding item 1001 at branch 30 for a quantity of 10 on 22 October 2026: a level 1 subassembly with quantity per 2 shows an extended quantity of 20, and a level 2 bracket used 4 per subassembly shows 80. A fixed quantity setup kit with quantity 1 shows 1 regardless of the order size. Compare this with the multi-level where-used and indented bill inquiries in the system to confirm the query matches. Then run the F3111 query for an open work order to see required, issued and still to issue per component line.

#### Version differences and gotchas

**Body:** Branch/plant is a right-justified 12 character field, hence the trim. Batch bills (IXBQTY greater than 0) are separate bills chosen by order quantity, so this query excludes them by default. Components can carry their own branch for supply, and configured items (stocking type C) build their parts list from rules in the configurator, not only from F3002, so an explosion of a configured parent shows the base bill rather than what a specific order will use. Scrap percentages and yield are not applied here; add them if your planners need gross requirements.

**Cta:** If you want planners to ask JD Edwards for an exploded bill or a shortage list in plain English on your own servers, we can set that up over F3002 and F3111.

**Faq:**

#### Which table holds the bill of material in JD Edwards?

**Answer:** F3002, Bill of Material Master. Each row links a parent item at a branch/plant to one component with quantity, effectivity and component line number. Routings are in F3003, the item master in F4101 and item branch data, including stocking type, in F4102. Work order parts lists are copied into F3111.

#### How do I create a BOM in JDE 9.2?

**Answer:** Set up the parent and component items in the item master and item branch, then use P3002 Bill of Material Revisions: enter the parent, branch and bill type, and add component lines with quantity, unit of measure, issue type and effectivity. Validate with the bill inquiry before attaching it to work orders.

#### How do configured items differ in JDE?

**Answer:** Configured items (stocking type C) use the configurator: rules choose components and quantities from the answers on each sales order, and the result is written as a configured parts list for that order. The F3002 bill of a configured parent therefore does not show the full structure of any particular order.

#### Why are my extended quantities wrong?

**Answer:** Usually because of implied decimals in the quantity column, fixed quantity components multiplied by the parent quantity, or batch bills mixed in with the standard bill. Check the display decimals of the quantity data item, honour IXFORQ, and filter IXBQTY as the generated query does.

## Search terms

jde f3002 table, jde bom explosion sql, how to create a bom in jde 9.2, jde manufacturing implementation guide, jd edwards data model, jde f3111 parts list, configured items in jde

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json