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
Description: Routing Report
Application: Bills of Material
Source: Routing Report (XML)
Short Name: BOMRDRTG_XML
DB package: BOM_BOMRDRTG_XMLP_PKG
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 |