BOM Bill of Material Listing

select 
            &P_ASSEMBLY                                             assembly_flex_values,
            item1.description                                          assy_desc,
            DECODE(b.assembly_type, 1,'No','Yes')  eng_bill_flag,
            null                                            catg_flex_values,
            b.alternate_bom_designator                       alternate,
            b.organization_id                                         organization_id,
            null                                              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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('assembly_flex_format', 'INV', 'MSTK', 101, item1.ORGANIZATION_ID, item1.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') assembly_flex_format, 
	BOM_BOMRASSY_XMLP_PKG.get_common_org(b.common_organization_id) C_common_org_code, 
	BOM_BOMRASSY_XMLP_PKG.org_codeformula(b.organization_id) ORG_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('common_flex_format', 'INV', 'MSTK', 101, item2.ORGANIZATION_ID, item2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') common_flex_format, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('catg_flex_format', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') catg_flex_format
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 &P_ASSY_WHERE
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 &P_CATG_WHERE
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  &P_ASSY_ORDER, 
NVL(b.alternate_bom_designator, ' '),:P_ORG_CODE