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 |
