Recursive SQL for F3002 BOM(s)

DBohner-(db)

Legendary Poster
Howdy,

Anyone out there have an SQL statement that will recursively explode the F3002 BOM table for a given item?

I've seen a couple on the web - but would prefer to snag one that was directly related to E1 / JDE.

Can you share?

(db)
 
Daniel,

the below works for Oracle DB:

SELECT ixkitl, ixcpnb/100 Seq,ixitm, ixlitm, ixqnty/10000 as Qty
FROM proddta.f3002
WHERE ixtbm='M'
START WITH ixkit = 13828
CONNECT BY PRIOR ixitm = ixkit

If you need a indent level ... well ... I heven't tried to do that yet
tongue.gif
 
The Indented Level turned out to be a built-in feature - just use keyword LEVEL as follows:

SELECT Level,ixkitl, ixcpnb/100 Seq,ixitm, ixlitm, ixqnty/10000 as Qty
FROM proddta.f3002
WHERE ixtbm='M'
START WITH ixkit = 13828
CONNECT BY PRIOR ixitm = ixkit
 
Guys,

That's very close to what I'm trying to get to - THANKS

(db)
 
Can I Just Growl??

The following query returns 87 unique Sales Order Item Numbers, nearly instantly:

SELECT DISTINCT SDITM FROM (SELECT SDITM FROM TESTDTA.F4211, TESTDTA.F3002
WHERE SDITM=IXKIT AND SDMCU=IXMMCU AND SDMCU=' 2620001' AND SDNXTR<'560' AND SDPDDJ<115108 AND SDITM != 0 AND ROWNUM<=10000);

If I cut/paste the results into the following, the results return in less than 60 seconds:

SELECT CONNECT_BY_ROOT(IXKITL) SO_ITEM, LEVEL, IXKITL, IXKIT, IXMMCU, IXCPNB/100 SEQ, IXITM, IXLITM, IXQNTY/1000 AS QNTY
FROM TESTDTA.F3002
WHERE IXTBM='M' AND IXMMCU=' 2620001'
START WITH IXKIT IN (59,163281,268267,307497,307512,494557,618101,1635295,1745226,
1838945,1838946,1838947,1838948,1838949,1838950,1869979,1879401,1885481,1886991,
1886992,1891497,1909647,1970791,1970792,1970797,1970798,1977001,1977002,1977003,
1977004,1977005,1977006,1977007,1977008,1977009,1977010,1989970,1989971,1989972,
1989973,2003743,2012919,2012923,2065511,2065513,2065515,2065517,2065519,2065521,
2065523,2065525,2065527,2065529,2065531,2093584,2109586,2109587,2109588,2109589,
2109590,2109591,2109592,2109593,2109594,2161645,2161646,2161647,2161648,2161649,
2161650,2161651,2161652,2161653,2161654,2161655,2163329,2167705,2167706,2167707,
2167708,2170245,2170246,2170247,2170248,2181266,2181267,2181268)
CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;


Now, if I combine the Original Query and the recursive query - the performance is so bad that I kill the query after 20 minutes:

SELECT CONNECT_BY_ROOT(IXKITL), LEVEL, IXKITL, IXKIT, IXMMCU, IXCPNB/100 SEQ, IXITM, IXLITM, IXQNTY/1000 AS QNTY
FROM TESTDTA.F3002
WHERE IXTBM='M' AND IXMMCU=' 2620001'
START WITH IXKIT IN (SELECT DISTINCT SDITM FROM (SELECT SDITM FROM TESTDTA.F4211, TESTDTA.F3002
WHERE SDITM=IXKIT AND SDMCU=IXMMCU AND SDMCU=' 2620001' AND SDNXTR<'560' AND SDPDDJ<115108 AND SDITM != 0 AND ROWNUM<=10000) )
CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;

Is there another way I should be writing this thing?

If I limit the ROWNUM to 140, the subquery returns three Item Numbers - and the query completes in seconds. If I change the limit to 150, the subquery returns 10 records and the query completes in fifteen minutes. If I cut/paste the ten item numbers into the IN statement, the query returns instantly....

The performance issue makes no sense to me - so I gotta find a better way to write this.

Goal - explode the BOM into a temp file for every open sales order (QUICKLY)... Using ER - the logic takes about an hour for one business unit. The thought was that SQL would do it quicker?

(db)
 
Dan,

looks like you're using Oracle DB.

Here's some concept PLSQL that should be close to what you need and also performs in a timely manner. Add additional fields/code as needed.
---------------------------------------
drop table temp_SO_BOM;
create table temp_SO_BOM
(XXDOCO NUMBER, XXLNID NUMBER, XXKIT NUMBER, XXITM NUMBER, XXCPNB NUMBER, XXQNTY NUMBER);

DECLARE
CURSOR SO_Cursor IS
SELECT s.sddoco, s.sdlnid, s.sditm
FROM PRODDTA.F4211 s
WHERE s.sddoco in (62025,62053) and s.sditm > 0
ORDER BY s.sddoco, s.sdlnid;

CURSOR BOM_Cursor (bKIT NUMBER) IS
SELECT b.ixkit, b.ixcpnb, b.ixitm, b.ixqnty
FROM proddta.f3002 b
WHERE ixtbm='M'
START WITH ixkit = bKIT
CONNECT BY PRIOR ixitm = ixkit;

v_doco number := 0;
v_lnid number := 0;
v_kit number := 0;
v_bkit number := 0;
v_itm number := 0;
v_cpnb number := 0;
v_qnty number := 0;

PROCEDURE Insert_Workfile (pDOCO NUMBER, pLNID NUMBER, pKIT NUMBER, pITM NUMBER, pCPNB NUMBER, pQNTY NUMBER) IS
BEGIN
INSERT INTO temp_SO_BOM (XXDOCO, XXLNID, XXKIT, XXITM, XXCPNB, XXQNTY )
VALUES (pDOCO, pLNID, pKIT, pITM, pCPNB, pQNTY);
END;

BEGIN

OPEN SO_Cursor;
LOOP
FETCH SO_Cursor INTO v_doco, v_lnid, v_kit;
EXIT WHEN SO_Cursor%NOTFOUND;
OPEN BOM_Cursor(v_kit);
LOOP
FETCH BOM_Cursor INTO v_bkit, v_cpnb, v_itm, v_qnty;
EXIT WHEN BOM_Cursor%NOTFOUND;
Insert_Workfile(v_doco,v_lnid,v_bkit,v_itm,v_cpnb,v_qnty);
END LOOP;
CLOSE BOM_Cursor;
END LOOP;
CLOSE SO_Cursor;
END;
/

select * from temp_SO_BOM;
 
Hey Dan,

after re-reading your goal I was wondering - can you be a bit more specific on that? Is this supposed to be a nightly batch for all open SOs or interactive on-demand for a current SO / SO Line?

The reason I ask is that for purchase orders we have a on-demand BOM Explosion for a selected PO Line that a) attaches a Exploded BOM as a Text Attachment; and b) updates a table containing a single level BOM (including any phantom assemblies). User launches via row exit. Even though it's done via UBE (invisible to user) it typically completes in less than 2 seconds. Just a thought for you to consider.
 
Here is an ugly sql I used recently for Oracle DB

<font class="small">Code:</font><hr /><pre>
SELECT t1.ixmmcu AS "Branch",
t1.ixtbm AS "Typ BOM",
t1.ixkitl AS "Pack - 2nd Item Number",
(SELECT ib.ibprp6
FROM F4102 ib
WHERE ib.iblitm=t1.ixkitl
AND ib.ibmcu =t1.ixmmcu
) "Pack - Item Group",
(SELECT trim(udc.drdl01
||udc.drdl02)
FROM PRODCTL.F0005 udc
WHERE udc.drsy ='41'
AND udc.drrt ='01'
AND lpad(
(SELECT ib.ibprp6
FROM F4102 ib
WHERE ib.iblitm=t1.ixkitl
AND ib.ibmcu =t1.ixmmcu
), 10, ' ' ) =udc.drky
) AS "Description",
t1.ixcpnb/100 AS "Line No.",
t1.ixlitm AS "Component - 2nd Item Number",
t1.ixum AS "UM",
t1.ixqnty/1000 AS "Quantity ",
t2.ibprp6 AS "Component - Item Group",
(SELECT trim(udc.drdl01
||udc.drdl02)
FROM PRODCTL.F0005 udc
WHERE udc.drsy ='41'
AND udc.drrt ='01'
AND lpad(t2.ibprp6, 10, ' ' )=udc.drky
) AS "Description",
bom2.ixlitm "Component (lowest level)",
bom2.ixcpnb/100 "Line No. (lowest level)",
bom2.ixum AS "UM",
bom2.ixqnty/1000 AS "Qty per Pack - Quantity "
FROM F3002 t1
LEFT JOIN F3002 bom2
ON t1.ixlitm=bom2.ixkitl
LEFT JOIN f4102 t2
ON t1.ixmmcu =t2.ibmcu
AND t1.ixlitm =t2.iblitm
--WHERE
--t1.ixkitl='490489'
--t1.ixkitl='100200'
--AND
-- t1.ixmmcu LIKE '%500%'
--OR t1.ixmmcu LIKE '%520%'
--AND t2.ibprp6 NOT IN ('FOR')
CONNECT BY NOCYCLE prior bom2.ixkitl=t1.ixlitm
ORDER BY t1.ixmmcu,
t1.ixkitl,
t1.ixcpnb ;
</pre><hr />
 
The Goal / Process....

Nightly Job - to Explode the BOM of all Open Sales Orders for Delivery within 18 Months. The target file will be sumarized several different ways - allowing summaries by all BOM Items by Sales Order, Total Quantity of Required Parts needed for next 18 months and any other summary you can imagine....

Does that make sense?

Currently - we have a working process, using Native BOM Functions. The process is been designed to run in parallel and the whole process runs in less than two hours per business unit. We will be having larger business units come online that may double/triple the time - and are looking for a quicker solution.

Being that the native BOM Functions have so much validation built into them - I'm thinking that I'll either have to copy/strip functionality from the native functions or see if there is a means to fully explode the BOMs via SQL.

It doesn't appear that I have PL/SQL access (not enought permissions)...

Now... back to our regularly scheduled programming...

(db)
 
Thanks for the update/reply Dan. Sounds like the database is getting POUNDED.
Basically its a kind of MRP you've developed.
 
Finally - it looks like I might have 'tripped' something, and I'm getting back results I expect (like within seconds)...

SELECT CONNECT_BY_ROOT(IXMMCU),CONNECT_BY_ROOT(IXKIT),CONNECT_BY_ROOT(IXKITL), LEVEL,
F3002.IXMMCU,F3002.IXKIT,F3002.IXKITL,F3002.IXKITA, F3002.IXCMCU,F3002.IXITM,
F3002.IXLITM,F3002.IXAITM,F3002.IXUM,F3002.IXDSC1,F3002.IXFORQ, F3002.IXITC,
F3002.IXOPSQ,F3002.IXCPNB/100,F3002.IXLNTY,F3002.IXEFFF,F3002.IXEFFT,F3002.IXBREV,
F3002.IXQNTY/1000,F3002.IXRVNO,F3002.IXCMRV,F3002.IXPRIC,F3002.IXUNCS,F3002.IXSCRP,
F3002.IXECOD,F3002.IXECOD

FROM TESTDTA.F3002
WHERE IXTBM='M' AND IXMMCU=' 2620001' AND IXEFFF<114001 AND IXEFFT>114001

START WITH IXKIT IN (
SELECT DISTINCT SDITM FROM TESTDTA.F4211
WHERE SDMCU=IXMMCU AND SDMCU=' 2620001' AND SDNXTR<'560' AND SDPDDJ<115180 AND SDITM != 0 )

CONNECT BY NOCYCLE PRIOR IXITM=IXKIT AND PRIOR IXCMCU=IXMMCU;

I still have validation, but the statement is now returning within seconds. Amazing what Explain Plan will tell you ... and an index or two will resolve...

Yes, Larry, re-creating an MRP system... UGLY as all get out!

(db)
 
I found some issues with Oracle's Connect By Prior functionality - where, if a component at level 3 is a component of another component at level 4... things go missing. Not sure if it's a bug or 'by design'.

Anyway, below is the "Right Way" to explode a BOM with a Recursive Oracle SQL Statement. Even better, it doesn't use the proprietary Connect / Prior - and I read that other databases allow similar syntax.

----------------------------------------------------------------
WITH PARENT (IXPATH, IXKITP, IXMMCUP, LVL, IXMMCU, IXKIT, IXKITL, IXCMCU, IXITM, IXLITM, IXTBM, IXEFFF, IXEFFT, IXQNTY, IXEXQTY ) AS
(SELECT
(P.IXKIT || ' / ' || P.IXITM) IXPATH,
P.IXKIT,
P.IXMMCU,
1,
P.IXMMCU,
P.IXKIT,
P.IXKITL,
P.IXCMCU,
P.IXITM,
P.IXLITM,
P.IXTBM,
P.IXEFFF,
P.IXEFFT,
P.IXQNTY,
(P.IXQNTY / 10000) IXEXQTY
FROM TESTDTA.F3002 P
WHERE P.IXTBM = 'M'
AND P.IXMMCU IN (' 2620001', ' 2740001')
AND P.IXEFFF < 114021
AND P.IXEFFT > 114021
AND EXISTS (
SELECT NULL FROM TESTDTA.F4211 S
WHERE P.IXKIT=S.SDITM AND P.IXMMCU=S.SDMCU
AND LTRIM(RTRIM(S.SDMCU)) IN ('2620001', '2740001') AND S.SDNXTR<'560' AND S.SDPPDJ <115108 AND S.SDITM>0
AND S.SDITM=263273)
UNION ALL
SELECT
(P.IXPATH || ' / ' || C.IXITM) IXPATH,
P.IXKITP,
P.IXMMCUP,
P.LVL+1,
C.IXMMCU,
C.IXKIT,
C.IXKITL,
C.IXCMCU,
C.IXITM,
C.IXLITM,
C.IXTBM,
C.IXEFFF,
C.IXEFFT,
C.IXQNTY,
DECODE(C.IXFORQ,'F',C.IXQNTY/10000, (P.IXEXQTY * C.IXQNTY/10000)) OSXQNTY
FROM TESTDTA.F3002 C JOIN PARENT P ON C.IXKIT = P.IXITM and C.IXMMCU = P.IXCMCU AND C.IXTBM='M'
WHERE C.IXTBM = 'M' AND C.IXEFFF < 114021 AND C.IXEFFT > 114021
)
CYCLE IXKIT SET is_cycle TO 'Y' DEFAULT 'N'
SELECT *
FROM PARENT
ORDER BY IXPATH
;
 
John,

it will explode everything in the F3002 that fulfills the data selection.

Yes, that includes phantoms.

We validated against the P30200, with options to show everything.

Regards'

(db)
 
Back
Top