Script for the BOM Routing Query in Oracle Apps EBS R12

---- Script for the BOM Routing Query in Oracle Apps EBS R12 ----


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 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 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 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 bom_operational_routings bor,
     bom_operation_sequences bos,
     bom_standard_operations bso,
     bom_departments bd,
     bom_operation_resources bores,
     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 =2272 --3188962