UPDATED
I have the need to get a BOM with all components , currently using Tc. CK86 , but this doesn't give enough information, so I want to do this by SQL, I come from oracle background and dont know how could I get this done in DB2 R/3, I do not have access to query builder or quickview , but I do have read access via SQL, I'm currently trying to figure out a way of getting this info using tables :
Does any of you have a solution for this?
In Oracle I did something like the following:
SELECT DISTINCT LEVEL
,sys_connect_by_path(msil.segment1, ' @ ') AS "BOM TREE"
,msi.segment1
,lpad(' ', LEVEL, '') || msil.segment1 Cod_Component
,msil.item_type
,msil.description Desc_Component
,BIC.component_quantity
,msiL.primary_unit_of_measure
FROM mtl_system_items msi
,bom_bill_of_materials bom
,BOM_INVENTORY_COMPONENTS BIC
,MTL_SYSTEM_ITEMS MSIL
WHERE msi.organization_id = 332
AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND nvl(bic.disable_date, sysdate) >= SYSDATE
AND BIC.component_ITEM_ID = MSIL.INVENTORY_ITEM_ID
AND Bom.ORGANIZATION_ID = MSIL.ORGANIZATION_ID
AND msil.inventory_item_status_code = 'Active'
AND msi.inventory_item_status_code = 'Active'
connect BY prior bic.component_item_id = bom.assembly_item_id
START WITH msi.segment1 = trim(:parte)
ORDER BY 2
I am trying the following, trying to keep it simple but it is giving me an error at line 18 no matter what I try , apparently in DB2 I need the "connect by" to be after the "START" , in my oracle working example it has "connect" first, don't know if it makes a difference, but no matter how i write it, it gives me an error :"ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "PRIOR" was found following "ASQ19130' CONNECT BY". Expected tokens may include: "PRIOR"."
Here is what I got so far:
SELECT DISTINCT level
,sys_connect_by_path(msil.stlnr, ' @ ') AS "BOM TREE"
,msi.stlnr as parent
--,lpad(' ', LEVEL, '') || MSIL.MATNR Cod_Component
--,lpad(' ', LEVEL, '') || MSIL.MATNR as Cod_Component
,CAST(SPACE((LEVEL - 1) * 4) || '/' || MSIL.MATNR AS VARCHAR(40)) as Cod_Component
,BIC.menge as qty
,bic.stlnr as compnumb
,msiL.mein as uom
FROM
MAST msi
,STKO bom
,STPO BIC
,MAST MSIL
WHERE
BOM.STLNR = MSI.STLNR
AND BIC.STLNR = MSIL.STLNR
START WITH msi.MATNR = 'ASQ19130'
CONNECT BY PRIOR BIC.stlnr = bom.stlnr
order by 2
This is what ended up with and it works for me.
(Edited for bugfix)
in case anyones needs this:
WITH myquery (
root,
matnr,matd,
bom_tree,
lvl,
parent_stlkn,
stlkn,
idnrk,
meins, menge
) AS (
SELECT
m.matnr root,
m.matnr,MAKT.MAKTX Matd,
p.idnrk bom_tree,
1 lvl,
p.stlkn,
p.stlkn,
p.idnrk,
p.meins, p.menge
FROM
mast m
JOIN stko k ON k.stlnr = m.stlnr AND K.STLAL=M.STLAL
JOIN stpo p ON p.stlnr = k.stlnr,makt
where m.stlal='01' /*and k.stlal='01'*/
and m.matnr=MAKT.matnr
),x (
root,
matnr,matd,
bom_tree,
lvl,
parent_stlkn,
stlkn,
idnrk,
meins, menge
) AS (
SELECT
root,
matnr,matd,
bom_tree,
lvl,
parent_stlkn,
stlkn,
idnrk,
meins, menge
FROM
myquery
UNION ALL
SELECT
x1.root,
x2.matnr,x2.matd,
x1.bom_tree
|| ' @ '
|| x2.idnrk bom_tree,
x1.lvl + 1 lvl,
x1.parent_stlkn,
x2.stlkn,
x2.idnrk,
x2.meins,x2.menge
FROM
myquery x1,
myquery x2
WHERE
x2.matnr = x1.bom_tree
) SELECT
x.matnr,matd Description,
marc.herkl country,
eina.urzla country2,
mbew.stprs comp_price,
bom_tree,
lvl,
stlkn,
idnrk comp,
x.meins UOM,
menge qpa
FROM
x, marc, mbew, eina
WHERE
root = :p
and mbew.matnr=marc.matnr
and mbew.matnr=eina.matnr
and marc.werks=1850
and idnrk=mbew.matnr
--and x.matnr=mbew.matnr
and mbew.bwkey=1850
ORDER BY
root,
parent_stlkn,
lvl,
stlkn