BOM Routing

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Routing Report
Application: Bills of Material
Source: Routing Report (XML)
Short Name: BOMRDRTG_XML
DB package: BOM_BOMRDRTG_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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,' ')
Parameter Name SQL text Validation
Item Selection
 
LOV Oracle
BOM_SRS_SPECIFIC_ROUTING
 
Char
Alternate Selection
 
LOV Oracle
Alternate
 
LOV Oracle
Revision
 
LOV Oracle
Date
 
DateTime
Display Option
 
LOV Oracle
Operation Detail
 
LOV Oracle
Items From
 
Char
To
 
Char
Category Set
 
LOV Oracle
Categories From
 
Char
To 2
 
Char