Script for the BOM Query in Oracle Apps EBS in R12 ( Bom Explosion Query)

---- Script for the BOM Query in Oracle Apps EBS in R12 ( Bom Explosion Query) ---


SELECT   (SELECT hou.NAME
            FROM hr_organization_units hou
           WHERE hou.organization_id = bom.organization_id) "Organization",
         (SELECT msib.segment1
            FROM mtl_system_items_b msib
           WHERE msib.inventory_item_id = bom.assembly_item_id
             AND msib.organization_id = 85) assembly_item,
         (SELECT msib.description
            FROM mtl_system_items_b msib
           WHERE msib.inventory_item_id = bom.assembly_item_id
             AND msib.organization_id = 85) assembly_item_description,
         bom.item_num item_seq, bom.operation_seq_num operation_seq,
         (SELECT msib1.segment1
            FROM mtl_system_items_b msib1
           WHERE msib1.inventory_item_id = bom.component_item_id
             AND msib1.organization_id = 85) component_item,
         (SELECT msib1.description
            FROM mtl_system_items_b msib1
           WHERE msib1.inventory_item_id =bom.component_item_id
             AND msib1.organization_id = 85) component_item_description,
         DECODE (bom.basis_type, NULL, 1, 2) basis  ,         --,bom.BASIS_TYPE   ,
         bom.component_quantity quantity,
         (SELECT msib.primary_uom_code
            FROM mtl_system_items_b msib
           WHERE msib.inventory_item_id = bom.assembly_item_id
             AND msib.organization_id = 85) uom,
         bom.wip_supply_type supply_type,
         bom.supply_subinventory subinventory, bom.locator1,
         bom.include_in_cost_rollup,
         (SELECT meaning
            FROM fnd_common_lookups fcl, mtl_system_items_b msib
           WHERE msib.inventory_item_id = bom.assembly_item_id
             AND msib.organization_id = 85
             AND fcl.lookup_type = 'ITEM_TYPE'
             AND fcl.lookup_code = msib.item_type) item_type,
         (SELECT msib.inventory_item_status_code
            FROM mtl_system_items_b msib
           WHERE msib.inventory_item_id = bom.assembly_item_id
             AND msib.organization_id = 85) item_status,
         (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 = bom.assembly_item_id
             AND ROWNUM <= 1) product_category,
         bom.disable_date effictive_end_date
    FROM (SELECT bbm.assembly_item_id, bic.component_item_id,
                 bic.component_quantity, bic.supply_subinventory,
                 bic.basis_type, bic.wip_supply_type, bbm.organization_id,
                 bic.item_num, bic.operation_seq_num, bic.disable_date,
                 bic.supply_locator_id locator1, bic.include_in_cost_rollup
            FROM bom_inventory_components bic, bom_bill_of_materials bbm
           WHERE bbm.bill_sequence_id = bic.bill_sequence_id
             AND bbm.organization_id = 85) bom
ORDER BY product_category