BOM Item Where Used Report GUI

select  
           MSI.concatenated_segments     l_item,
            MSI.description                                         l_description,
            bv.display_plan_level                               p_level,
            bv.component_op_seq_num                   op,
            bv.parent,
            bv.parent_alternate_designator             alternate,
            bv.parent_description                             description, 
			lu2.meaning	item_type,
			msi.inventory_item_status_code   item_status,
            to_char(bv.component_effective_date,'DD-MON-RR HH24:MI')                 effective_date,
            to_char(bv.component_disable_date,'DD-MON-RR HH24:MI')                  disable_date,
            bv.basis_type		         basis_type,
            bv.component_quantity                          quantity,
            bv.parent_uom                                         uom,
            lu.meaning                                                 type,
            bv.change_notice                                     notice, 
            bv.implemented_flag                           implemented_flag,
            bv.lowest_item_id,
            bv.sort_code,
            bv.organization_id,
            bv.revised_item_sequence_id, 
	BOM_BOMRWUIT_XMLP_PKG.get_orgcode(bv.organization_id) C_orgcode, 
	BOM_BOMRWUIT_XMLP_PKG.get_status(bv.revised_item_sequence_id, bv.implemented_flag, bv.change_notice) C_status
from    
           mtl_system_items_vl MSI,
		   mtl_item_flexfields F,
            mfg_lookups lu,
            BOM_IMPLOSION_VIEW bv,
			fnd_lookup_values lu2
where 
   bv.lowest_item_id = MSI.inventory_item_id
   and  MSI.organization_id = bv.organization_id
   and bv.parent_item_id = F.item_id
    and bv.organization_id = F.organization_id 
   and bv.parent is not NULL
   and bv.sequence_id = :P_SEQUENCE_ID
   and bv.current_level != 0
   and lu.lookup_code = bv.parent_engineering_bill and
           lu.lookup_type = 'BOM_NO_YES'
   and nvl(:P_TOP_ASSLY_FLG, 2) = 2
   and F.item_type = lu2.lookup_code(+)
   and lu2.lookup_type(+) = 'ITEM_TYPE'
   and lu2.language(+) = USERENV('LANG')
   and lu2.VIEW_APPLICATION_ID(+) = 3
   and (lu2.LOOKUP_CODE  IS NULL
     OR lu2.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(lu2.LOOKUP_TYPE,lu2.VIEW_APPLICATION_ID))
union all
select  
           MSI.concatenated_segments     l_item,
            MSI.description                                         l_description,
            bv.display_plan_level                               p_level,
            bv.component_op_seq_num                   op,
            bv.parent,
            bv.parent_alternate_designator             alternate,
            bv.parent_description                             description, 
			lu2.meaning	item_type,
			msi.inventory_item_status_code   item_status,
            to_char(bv.component_effective_date,'DD-MON-RR HH24:MI')                 effective_date,
            to_char(bv.component_disable_date,'DD-MON-RR HH24:MI')                  disable_date,
            bv.basis_type		         basis_type,
            bv.component_quantity                          quantity,
            bv.parent_uom                                         uom,
            lu.meaning                                                 type,
            bv.change_notice                                     notice, 
            bv.implemented_flag                           implemented_flag,
            bv.lowest_item_id,
            bv.sort_code,
            bv.organization_id,
            bv.revised_item_sequence_id, 
	BOM_BOMRWUIT_XMLP_PKG.get_orgcode(bv.organization_id) C_orgcode, 
	BOM_BOMRWUIT_XMLP_PKG.get_status(bv.revised_item_sequence_id, bv.implemented_flag, bv.change_notice) C_status
from    
           mtl_system_items_vl MSI,
		   mtl_item_flexfields F,
            mfg_lookups lu,
            BOM_IMPLOSION_VIEW bv,
			fnd_lookup_values lu2
where 
   bv.lowest_item_id = MSI.inventory_item_id
   and  MSI.organization_id = bv.organization_id
   and bv.parent_item_id = F.item_id
    and bv.organization_id = F.organization_id 
   and bv.parent is not NULL
   and bv.sequence_id = :P_SEQUENCE_ID
   and bv.current_level != 0
   and lu.lookup_code = bv.parent_engineering_bill and
           lu.lookup_type = 'BOM_NO_YES'
   and nvl(:P_TOP_ASSLY_FLG, 2) = 1
   and bv.parent_item_id NOT IN 
	  (SELECT
		/*+ push_subq no_unnest */
		bv1.current_item_id
	  FROM bom_implosion_view bv1
	  WHERE bv1.sequence_id  = :P_SEQUENCE_ID
	  AND bv1.lowest_item_id = bv.lowest_item_id
	  )
   and F.item_type = lu2.lookup_code(+)
   and lu2.lookup_type(+) = 'ITEM_TYPE'
   and lu2.language(+) = USERENV('LANG')
   and lu2.VIEW_APPLICATION_ID(+) = 3
   and (lu2.LOOKUP_CODE  IS NULL
     OR lu2.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(lu2.LOOKUP_TYPE,lu2.VIEW_APPLICATION_ID))
order by lowest_item_id, sort_code,organization_id