Item- BOM with Routing Query

 ****************************************
Item BOM and Routing Query
***********************************************
SELECT ROWNUM "Sl NO", assembly_item_id,
       (SELECT hou.NAME
          FROM hr_organization_units hou
         WHERE hou.organization_id = bor.organization_id) "Organization",
       (SELECT msib.segment1
          FROM apps.mtl_system_items_b msib
         WHERE msib.inventory_item_id =
                                      bor.assembly_item_id
           AND msib.organization_id = 85) assembly_item_code,
       (SELECT msib.description
          FROM apps.mtl_system_items_b msib
         WHERE msib.inventory_item_id =
                               bor.assembly_item_id
           AND msib.organization_id = 85) assembly_item_description,
       bor.completion_subinventory, bor.completion_locator_id,
       bos.operation_seq_num, bso.operation_code, bd.department_code,
       bores.resource_seq_num, br.resource_code, br.unit_of_measure uom,
       bores.basis_type basis, bores.schedule_flag scheduling,
       (SELECT micv.category_concat_segs product_category
          FROM apps.mtl_item_categories_v micv
         WHERE micv.category_set_id = '1100000042'
           AND micv.organization_id = 85
           AND micv.inventory_item_id = bor.assembly_item_id
           AND ROWNUM <= 1) product_category,
       bos.disable_date
  FROM apps.bom_operational_routings bor,
       apps.bom_operation_sequences bos,
       apps.bom_standard_operations bso,
       apps.bom_departments bd,
       apps.bom_operation_resources bores,
       apps.bom_resources br
 WHERE bor.routing_sequence_id = bos.routing_sequence_id
   AND bos.standard_operation_id = bso.standard_operation_id(+)
   AND bos.department_id = bd.department_id
   AND bores.operation_sequence_id = bos.operation_sequence_id
   AND br.resource_id = bores.resource_id
   AND bor.assembly_item_id = 2110381;