Hi! I'm trying to get a BOM exploded (indented list). I modified a little bit a common query found online. The condition is that all items with disabled date should be avoided:
select distinct
LEVEL "level"
,sys_connect_by_path(msib.segment1 , '|' ) as "PATH"
,msib2.segment1 as "CHILD ITEM"
,msib2.description
,bic.component_quantity
,TO_DATE(bic.IMPLEMENTATION_DATE) IMPLEMENTATION
,TO_DATE(bic.DISABLE_DATE)DISABLED
,(SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = BIC.PK2_VALUE) ORGANIZATION
,bom.organization_id
,BIC.ITEM_NUM SEQUENCE
,bic.component_item_id CHILD_INVENTORY_ID
, msib2.primary_uom_code UOM
from bom.bom_components_b bic
,bom.bom_structures_b bom
,inv.mtl_system_items_b msib
,inv.mtl_system_items_b msib2
where 1=1
and bic.bill_sequence_id = bom.bill_sequence_id
and bom.assembly_item_id = msib.inventory_item_id
and bom.organization_id = msib.organization_id
and bic.component_item_id = msib2.inventory_item_id
and bom.organization_id = msib2.organization_id
and bom.organization_id IN (1269)
and bom.alternate_bom_designator is NULL
START WITH msib.segment1 = 'GRANDPA' AND bic.DISABLE_DATE IS NULL
connect by nocycle prior bic.component_item_id = msib.inventory_item_id AND bic.DISABLE_DATE IS NULL
ORDER BY LEVEL,PATH ASC
But the reality is, if a parent is disabled but has at least child that is NOT not disabled, the parent still shows:
GRANDPA
FATHER (D)
CHILD0
CHILD1
CHILD2
Should return:
GRANDPA
Returns:
GRANDPA|FATHER|CHILD0
GRANDPA|FATHER|CHILD1
GRANDPA|FATHER|CHILD2
How can I prevent this? Thanks!
EDIT: just to be clear, the output shows the disabled date field in all elements as NULL but If you check the actual BOM, FATHER does have a disabled date.