BOM Routing Extract Query

  SELECT   DISTINCT ood.organization_code,
                    --ood.organization_id,
                    --bom.assembly_item_id,
                    msi.segment1,
                    msi.description,
                    bic.operation_seq_num,
                    bic.item_num,
                    --bic.component_item_id,
                    msi1.segment1 COMPONENT,
                    msi1.description COMPONENT_DESCRIPTION,
                    msi1.primary_uom_code,
                    bic.COMPONENT_QUANTITY,
                    bic.COMPONENT_YIELD_FACTOR,
                    --bos.OPERATION_SEQ_NUM,
                    bos.DEPARTMENT_CODE,
                    bos.EFFECTIVITY_DATE FROM_DATE,
                    bic.disable_date TO_DATE,
                    mlr.MEANING BASIS,
                    bos.OPERATION_DESCRIPTION
    --ml.meaning,
    --bic.wip_supply_type,
    -- bic.effectivity_date,
    -- bic.disable_date
    -- bic.*
    FROM   mtl_system_items_b msi,
           org_organization_definitions ood,
           bom_bill_of_materials bom,
           bom_inventory_components bic,
           mtl_system_items_b msi1,
           mfg_lookups ml,
           BOM_OPERATIONAL_ROUTINGS_V bor,
           BOM_OPERATION_SEQUENCES_V bos,
           BOM_OPERATION_RESOURCES_V borv,
           mfg_lookups mlr
   WHERE       bom.assembly_item_id = msi.inventory_item_id
           AND bom.bill_sequence_id = bic.bill_sequence_id
           AND msi.organization_id = ood.organization_id
           AND bom.organization_id = ood.organization_id
           AND msi1.inventory_item_status_code = 'Active'
           AND msi.inventory_item_status_code = 'Active'
           AND bic.component_item_id = msi1.inventory_item_id
           AND msi1.organization_id = ood.organization_id
           AND msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
           AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
           AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
           AND bic.operation_seq_num = bos.OPERATION_SEQ_NUM
           AND bos.operation_sequence_id = borv.operation_sequence_id
           AND borv.BASIS_TYPE = mlr.LOOKUP_CODE(+)
           AND mlr.lookup_type(+) = 'BOM_BASIS_TYPE'
           AND ml.lookup_code(+) = bic.wip_supply_type
           -- and bic.wip_supply_type =4
           AND NVL (bic.disable_date, SYSDATE) >= SYSDATE
           AND ml.lookup_type(+) = 'WIP_SUPPLY'
--and ood.organization_id = 143
--and  msi.segment1 in ('02010981')
--and  msi1.segment1 in ('0QP131155')
ORDER BY   ood.organization_code, msi.segment1, msi1.segment1