Re: getting list of complete BOM

I want to thanks all for the help provided. Having used that and looking further I've managed to make a list from all finals with their components.
Very basic list but can be expanded and changed easily.

Here the script in case it's useful for someone else. Please note that it only goes 8 levels deep, can be changed for further levels if you want.
I run it in SQL navigator.

Best regards,

Ronald.

select  a1.a1_ass_id


,       a1.a1_org_id

,       a1.a1_item

,       a1.a1_bom_flg

,       a1.a1_bom_seqid

,       c1.c1_cmp_id

,       c1.c1_item

--


,       a2.a2_bom_seqid

,       c2.c2_cmp_id

,       c2.c2_item

--


,       a3.a3_bom_seqid

,       c3.c3_cmp_id

,       c3.c3_item

--


,       a4.a4_bom_seqid

,       c4.c4_cmp_id

,       c4.c4_item

--


,       a5.a5_bom_seqid

,       c5.c5_cmp_id

,       c5.c5_item

--


,       a6.a6_bom_seqid

,       c6.c6_cmp_id

,       c6.c6_item

--


,       a7.a7_bom_seqid

,       c7.c7_cmp_id

,       c7.c7_item

--


,       a8.a8_bom_seqid

,       c8.c8_cmp_id

,       c8.c8_item

--
from
(
select  m.segment1                  a1_item


,       b.ASSEMBLY_ITEM_ID          a1_ass_id

,       b.organization_id           a1_org_id

,       b.Bill_sequence_id          a1_bom_seqid

,       m.BOM_ENABLED_FLAG          a1_bom_flg

-- ,       m.BOM_ITEM_TYPE             bom_typ
from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.organization_id           = 2
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.PLANNING_MAKE_BUY_CODE    = 1
and     m.ENABLED_FLAG              = 'Y'
and     m.organization_id           = 2
and     alternate_bom_designator    is null
-- and     b.bill_sequence_id  = 3514
-- and     b.ASSEMBLY_ITEM_ID  = 8427
) a1


,

(
select  m.segment1                  c1_item


,       c.COMPONENT_ITEM_ID         c1_cmp_id

,       c.Bill_sequence_id          c1_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c1


,

(
select  m.segment1                  a2_item


,       b.ASSEMBLY_ITEM_ID          a2_ass_id

,       b.organization_id           a2_org_id

,       b.Bill_sequence_id          a2_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a2,
(
select  m.segment1                  c2_item


,       c.COMPONENT_ITEM_ID         c2_cmp_id

,       c.Bill_sequence_id          c2_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c2


,(

select  m.segment1                  a3_item


,       b.ASSEMBLY_ITEM_ID          a3_ass_id

,       b.organization_id           a3_org_id

,       b.Bill_sequence_id          a3_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a3,
(
select  m.segment1                  c3_item


,       c.COMPONENT_ITEM_ID         c3_cmp_id

,       c.Bill_sequence_id          c3_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c3
--


,(

select  m.segment1                  a4_item


,       b.ASSEMBLY_ITEM_ID          a4_ass_id

,       b.organization_id           a4_org_id

,       b.Bill_sequence_id          a4_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a4,
(
select  m.segment1                  c4_item


,       c.COMPONENT_ITEM_ID         c4_cmp_id

,       c.Bill_sequence_id          c4_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c4
--


,(

select  m.segment1                  a5_item


,       b.ASSEMBLY_ITEM_ID          a5_ass_id

,       b.organization_id           a5_org_id

,       b.Bill_sequence_id          a5_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a5,
(
select  m.segment1                  c5_item


,       c.COMPONENT_ITEM_ID         c5_cmp_id

,       c.Bill_sequence_id          c5_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c5
--


,(

select  m.segment1                  a6_item


,       b.ASSEMBLY_ITEM_ID          a6_ass_id

,       b.organization_id           a6_org_id

,       b.Bill_sequence_id          a6_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a6,
(
select  m.segment1                  c6_item


,       c.COMPONENT_ITEM_ID         c6_cmp_id

,       c.Bill_sequence_id          c6_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c6
--


,(

select  m.segment1                  a7_item


,       b.ASSEMBLY_ITEM_ID          a7_ass_id

,       b.organization_id           a7_org_id

,       b.Bill_sequence_id          a7_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a7,
(
select  m.segment1                  c7_item


,       c.COMPONENT_ITEM_ID         c7_cmp_id

,       c.Bill_sequence_id          c7_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c7
--


,(

select  m.segment1                  a8_item


,       b.ASSEMBLY_ITEM_ID          a8_ass_id

,       b.organization_id           a8_org_id

,       b.Bill_sequence_id          a8_bom_seqid

from    bom_bill_of_materials  b


,       mtl_system_items       m

where   1=1
and     b.ASSEMBLY_ITEM_ID          = m.inventory_item_id
and     m.organization_id           = 2
and     b.organization_id           = 2
and     alternate_bom_designator    is null
) a8,
(
select  m.segment1                  c8_item


,       c.COMPONENT_ITEM_ID         c8_cmp_id

,       c.Bill_sequence_id          c8_bom_seqid

from    bom_inventory_components    c


,       mtl_system_items            m

where   1=1
and     c.COMPONENT_ITEM_ID         = m.inventory_item_id
and     m.organization_id           = 2
and     nvl(c.disable_date,sysdate+1)   > sysdate
) c8

--
where   1=1
-- and     a1.ass_id    = 8427
and     a1.a1_bom_seqid = c1.c1_bom_seqid
--
and     c1.c1_cmp_id    = a2.a2_ass_id(+)
and     a2.a2_bom_seqid = c2.c2_bom_seqid(+)
--
and     c2.c2_cmp_id    = a3.a3_ass_id(+)
and     a3.a3_bom_seqid = c3.c3_bom_seqid(+)
--
and     c3.c3_cmp_id    = a4.a4_ass_id(+)
and     a4.a4_bom_seqid = c4.c4_bom_seqid(+)
--
and     c4.c4_cmp_id    = a5.a5_ass_id(+)
and     a5.a5_bom_seqid = c5.c5_bom_seqid(+)
--
and     c5.c5_cmp_id    = a6.a6_ass_id(+)
and     a6.a6_bom_seqid = c6.c6_bom_seqid(+)
--
and     c6.c6_cmp_id    = a7.a7_ass_id(+)
and     a7.a7_bom_seqid = c7.c7_bom_seqid(+)
--
and     c7.c7_cmp_id    = a8.a8_ass_id(+)
and     a8.a8_bom_seqid = c8.c8_bom_seqid(+)