BOM Routing

select rtg.common_routing_sequence_id   common_sequence_id1,
       rtg.routing_sequence_id          routing_sequence_id2, 
       rtg.alternate_routing_designator alternate_designator1,
       rtg.assembly_item_id             assembly_item_id1,
       INV_MEANING_SEL.C_ITEM_DESCRIPTION(item1.inventory_item_id,item1.organization_id)                assembly_desc1,
       item1.primary_uom_code           routing_uom1,
       INV_MEANING_SEL.C_ITEM_DESCRIPTION(item2.inventory_item_id,item2.organization_id)         common_desc1,
       alt.description                  alt_desc1,
       rtg.common_assembly_item_id      common_assembly_item_id1,
       rtg.routing_comment              routing_comment1,
       rtg.completion_subinventory      completion_subinventory1,
       rtg.completion_locator_id        completion_locator_id1,
       null                      assembly_flex_values1,
       null                        common_flex_values1,
       null                       locator_flex_values1,
       rev.process_revision process_revision1,
       ROUND(item1.full_lead_time,:P_qty_precision)     full_lead_time1,
       ROUND(item1.fixed_lead_time,:P_qty_precision)    fixed_lead_time1,
       ROUND(item1.variable_lead_time,:P_qty_precision) variable_lead_time1,
       lu1.meaning                      eng_routing1,
       rtg.line_id    line_id1,
       rtg.total_product_cycle_time    total_cycle_time1,
	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_format1, 
	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_format1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('locator_flex_format', 'INV', 'MTLL', 101, locator.ORGANIZATION_ID, locator.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE') locator_flex_format1
from bom_operational_routings rtg,
     mtl_system_items item1,
     mtl_system_items item2,
     mtl_item_locations locator,
     mtl_rtg_item_revisions rev,
     mtl_item_categories icat,
     mtl_categories cat,
     bom_alternate_designators alt,
     mfg_lookups lu1
where rtg.organization_id = :P_ORG_ID
  and ((rtg.routing_type = 1 and :P_BOM_OR_ENG = 'BOM') or
       (:P_BOM_OR_ENG = 'ENG'))
  and rtg.assembly_item_id = item1.inventory_item_id
  and rtg.organization_id = item1.organization_id
  and item1.bom_enabled_flag = 'Y' 
  and rtg.cfm_routing_flag=1
  and rtg.common_assembly_item_id = item2.inventory_item_id (+)
  and rtg.organization_id = item2.organization_id (+)
  and rtg.completion_locator_id = locator.inventory_location_id (+)
  and rtg.organization_id = locator.organization_id (+)
  and nvl(rtg.alternate_routing_designator,'NONE') = nvl(alt.alternate_designator_code,'NONE')
  and (rtg.organization_id = alt.organization_id
       or
       alt.organization_id = -1)       
  and nvl(rtg.common_assembly_item_id,rtg.assembly_item_id) = rev.inventory_item_id
  and rtg.organization_id = rev.organization_id
  and rev.effectivity_date = 
      (select max(rev2.effectivity_date)
       from mtl_rtg_item_revisions rev2
       where rev2.effectivity_date <= :P_EFF_DATE1
         and rev2.implementation_date is not null
         and rev2.inventory_item_id = nvl(rtg.common_assembly_item_id,rtg.assembly_item_id)
         and rev2.organization_id = rtg.organization_id)
  and rev.process_revision = (select max(rev3.process_revision)
                      from mtl_rtg_item_revisions rev3
                      where rev3.implementation_date is not null  
                        and rev3.inventory_item_id = nvl(rtg.common_assembly_item_id,rtg.assembly_item_id) 
                        and rev3.organization_id = rtg.organization_id
                        and rev3.effectivity_date = rev.effectivity_date)
  and ((:P_ALT_OPTION = 1) or
       (:P_ALT_OPTION = 2 and rtg.alternate_routing_designator is null) or
       (:P_ALT_OPTION = 3 and rtg.alternate_routing_designator =
                             :P_ALTERNATE))
  and ((:P_RPT_SELECTION = 1 and rtg.assembly_item_id = :P_ITEM) or
       (:P_RPT_SELECTION = 2 and &P_ASSY_BETWEEN))
  and :P_CAT_SET = icat.category_set_id
  and rtg.assembly_item_id = icat.inventory_item_id
  and rtg.organization_id = icat.organization_id
  and cat.category_id = icat.category_id
  and &P_CAT_BETWEEN   
  and lu1.lookup_code = rtg.routing_type 
  and lu1.lookup_type = 'BOM_NO_YES'
ORDER by &P_ASSY_ORDER, 
         nvl(rtg.alternate_routing_designator,' ')