BOM Bill of Material Listing (1)

select item1.segment1 assembly_flex_values,
 item1.description assy_desc,
 decode(b.assembly_type, 1, 'No', 'Yes') eng_bill_flag,
 mcat.segment1 || '
' || mcat.segment2 catg_flex_values,
 b.alternate_bom_designator alternate,
 b.organization_id organization_id,
 item2.segment1 common_flex_values,
 item2.description common_desc,
 item2.organization_id common_org_id,
 item1.inventory_item_id item_id,
 b.assembly_item_id assy_id,
 nvl(b.common_assembly_item_id, b.assembly_item_id) common_assy_id,
 b.common_organization_id
from bom_bill_of_materials b,
 mtl_item_categories micat,
 mtl_system_items item1,
 mtl_system_items item2,
 mtl_categories mcat,
 bom_lists blist
where blist.sequence_id = :p_sequence_id and
blist.organization_id = b.organization_id and
b.assembly_item_id = item1.inventory_item_id and
b.organization_id = item1.organization_id and
item1.bom_enabled_flag = 'Y' and
nvl(b.common_assembly_item_id, b.assembly_item_id) = item2.inventory_item_id and
nvl(b.common_organization_id, b.organization_id) = item2.organization_id and
((b.assembly_type = 1 and
  :p_bom_or_eng = 'BOM') or
 (:p_bom_or_eng = 'ENG')) and
1 = 1 and
:p_catg_set_id = micat.category_set_id and
b.assembly_item_id = micat.inventory_item_id and
b.organization_id = micat.organization_id and
mcat.category_id = micat.category_id and
1 = 1 and
((:p_alt_option = 1) or
 (:p_alt_option = 2 and
  b.alternate_bom_designator is null) or
 (:p_alt_option = 3 and
  b.alternate_bom_designator = :p_alternate)) and
(
 (:p_common_org is null) or
 (:p_common_item_id is null and
  :p_common_org = b.common_organization_id) or
 (:p_common_org = b.common_organization_id and
  :p_common_item_id = b.common_assembly_item_id)
)
order by 1 asc,
 13 asc,
 item1.segment1,
 nvl(b.alternate_bom_designator, ' '),
 :p_org_code