Query to Find the Immediate Parent in a BOM
SELECT DISTINCT SUBSTR (SYS_CONNECT_BY_PATH (msib.inventory_item_id, '/') || '/'
, 2
, INSTR ((SYS_CONNECT_BY_PATH (msib.inventory_item_id, '/') || '/')
, '/'
, 2
) - 2
) current_parent_item
, SUBSTR (SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/'
, 2
, INSTR ((SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/')
, '/'
, 2
) - 2
) current_parent_item
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 = :p_organization_id
AND bom.assembly_item_id = :p_assembly_item_id
START WITH bic.component_item_id = :p_component_item_id
CONNECT BY bic.component_item_id = PRIOR msib.inventory_item_id
, 2
, INSTR ((SYS_CONNECT_BY_PATH (msib.inventory_item_id, '/') || '/')
, '/'
, 2
) - 2
) current_parent_item
, SUBSTR (SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/'
, 2
, INSTR ((SYS_CONNECT_BY_PATH (msib.segment1, '/') || '/')
, '/'
, 2
) - 2
) current_parent_item
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 = :p_organization_id
AND bom.assembly_item_id = :p_assembly_item_id
START WITH bic.component_item_id = :p_component_item_id
CONNECT BY bic.component_item_id = PRIOR msib.inventory_item_id
No comments:
Post a Comment