VIEW: APPS.MSC_ROUTING_OPERATIONS_V

Source

View Text - Preformatted

SELECT mro.plan_id, mbc.organization_id, mro.sr_instance_id, msc_get_name.org_code(mbc.organization_id, mbc.sr_instance_id) , mro.operation_sequence_id, mro.routing_sequence_id, mr.alternate_routing_designator, moc.bill_sequence_id, msc_get_name.alternate_bom(mro.plan_id, mro.sr_instance_id, moc.bill_sequence_id), mbc.inventory_item_id, msc_get_name.item_name(mbc.inventory_item_id, mbc.organization_id, mbc.plan_id, mbc.sr_instance_id), mro.operation_seq_num, mro.operation_description, mro.effectivity_date, mro.disable_date, mro.from_unit_number, mro.to_unit_number, mro.option_dependent_flag, mro.operation_type, msc_get_name.lookup_meaning('BOM_OPERATION_TYPE',mro.operation_type), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mbc.inventory_item_id, mr.cfm_routing_flag, 'MTQTY'), mro.yield, mro.department_id, mro.department_code, mro.operation_lead_time_percent, mro.cumulative_yield, mro.reverse_cumulative_yield, mro.net_planning_percent, mro.setup_duration, mro.tear_down_duration, mro.uom_code, mro.standard_operation_code, mbc.using_assembly_id, msc_get_name.item_name(mbc.using_assembly_id, mbc.organization_id, mbc.plan_id, mbc.sr_instance_id) , mr.line_id, msc_get_name.department_code(1,mr.line_id,mr.organization_id, mr.plan_id, mr.sr_instance_id), DECODE(mro.use_in_plan, NULL,NULL,mfg.meaning), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mbc.inventory_item_id, mr.cfm_routing_flag, 'MINTT'), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mbc.inventory_item_id, mr.cfm_routing_flag, 'MAXTT') FROM msc_bom_components mbc, msc_routings mr, msc_operation_components moc, msc_routing_operations mro, mfg_lookups mfg WHERE mbc.bill_sequence_id = moc.bill_sequence_id AND mbc.plan_id = moc.plan_id AND mbc.sr_instance_id = moc.sr_instance_id AND mbc.organization_id = moc.organization_id AND mbc.component_sequence_id = moc.component_sequence_id AND mro.routing_sequence_id = moc.routing_sequence_id AND mro.sr_instance_id = moc.sr_instance_id AND mro.plan_id = moc.plan_id AND mro.operation_sequence_id = moc.operation_sequence_id AND mr.plan_id = mro.plan_id AND mr.routing_sequence_id = mro.routing_sequence_id AND mr.sr_instance_id = mro.sr_instance_id AND NVL(mro.use_in_plan, 1) = mfg.lookup_code AND mfg.lookup_type = 'SYS_YES_NO' AND nvl(mbc.component_type,0) <> 10 UNION ALL SELECT mro.plan_id, mr.organization_id, mro.sr_instance_id, msc_get_name.org_code(mr.organization_id, mro.sr_instance_id), mro.operation_sequence_id, mro.routing_sequence_id, mr.alternate_routing_designator, to_number(NULL), NULL, to_number(NULL), NULL, mro.operation_seq_num, mro.operation_description, mro.effectivity_date, mro.disable_date, mro.from_unit_number, mro.to_unit_number, mro.option_dependent_flag, mro.operation_type, msc_get_name.lookup_meaning('BOM_OPERATION_TYPE',mro.operation_type), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mr.assembly_item_id, mr.cfm_routing_flag, 'MTQTY'), mro.yield, mro.department_id, mro.department_code, mro.operation_lead_time_percent, mro.cumulative_yield, mro.reverse_cumulative_yield, mro.net_planning_percent, mro.setup_duration, mro.tear_down_duration, mro.uom_code, mro.standard_operation_code, mr.assembly_item_id, msc_get_name.item_name(mr.assembly_item_id, mr.organization_id, mr.plan_id, mr.sr_instance_id), mr.line_id, msc_get_name.department_code(1,mr.line_id,mr.organization_id, mr.plan_id, mr.sr_instance_id), DECODE(mro.use_in_plan, NULL, NULL, mfg.meaning), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mr.assembly_item_id, mr.cfm_routing_flag, 'MINTT'), msc_get_name.get_mtq_details(mro.plan_id, mro.sr_instance_id, mro.routing_sequence_id, mro.operation_sequence_id, mr.assembly_item_id, mr.cfm_routing_flag, 'MAXTT') FROM msc_routings mr, msc_routing_operations mro, mfg_lookups mfg WHERE mr.plan_id = mro.plan_id AND mr.routing_sequence_id = mro.routing_sequence_id AND mr.sr_instance_id = mro.sr_instance_id AND NOT EXISTS (SELECT 1 FROM msc_operation_components moc WHERE moc.plan_id = mro.plan_id AND moc.operation_sequence_id = mro.operation_sequence_id AND moc.sr_instance_id = mro.sr_instance_id AND moc.routing_sequence_id = mro.routing_sequence_id) AND NVL(mro.use_in_plan, 1) = mfg.lookup_code AND mfg.lookup_type = 'SYS_YES_NO'

View Text - HTML Formatted

SELECT MRO.PLAN_ID
, MBC.ORGANIZATION_ID
, MRO.SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MBC.ORGANIZATION_ID
, MBC.SR_INSTANCE_ID)
, MRO.OPERATION_SEQUENCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MR.ALTERNATE_ROUTING_DESIGNATOR
, MOC.BILL_SEQUENCE_ID
, MSC_GET_NAME.ALTERNATE_BOM(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MOC.BILL_SEQUENCE_ID)
, MBC.INVENTORY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MBC.INVENTORY_ITEM_ID
, MBC.ORGANIZATION_ID
, MBC.PLAN_ID
, MBC.SR_INSTANCE_ID)
, MRO.OPERATION_SEQ_NUM
, MRO.OPERATION_DESCRIPTION
, MRO.EFFECTIVITY_DATE
, MRO.DISABLE_DATE
, MRO.FROM_UNIT_NUMBER
, MRO.TO_UNIT_NUMBER
, MRO.OPTION_DEPENDENT_FLAG
, MRO.OPERATION_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_OPERATION_TYPE'
, MRO.OPERATION_TYPE)
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MTQTY')
, MRO.YIELD
, MRO.DEPARTMENT_ID
, MRO.DEPARTMENT_CODE
, MRO.OPERATION_LEAD_TIME_PERCENT
, MRO.CUMULATIVE_YIELD
, MRO.REVERSE_CUMULATIVE_YIELD
, MRO.NET_PLANNING_PERCENT
, MRO.SETUP_DURATION
, MRO.TEAR_DOWN_DURATION
, MRO.UOM_CODE
, MRO.STANDARD_OPERATION_CODE
, MBC.USING_ASSEMBLY_ID
, MSC_GET_NAME.ITEM_NAME(MBC.USING_ASSEMBLY_ID
, MBC.ORGANIZATION_ID
, MBC.PLAN_ID
, MBC.SR_INSTANCE_ID)
, MR.LINE_ID
, MSC_GET_NAME.DEPARTMENT_CODE(1
, MR.LINE_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, DECODE(MRO.USE_IN_PLAN
, NULL
, NULL
, MFG.MEANING)
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MINTT')
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MAXTT')
FROM MSC_BOM_COMPONENTS MBC
, MSC_ROUTINGS MR
, MSC_OPERATION_COMPONENTS MOC
, MSC_ROUTING_OPERATIONS MRO
, MFG_LOOKUPS MFG
WHERE MBC.BILL_SEQUENCE_ID = MOC.BILL_SEQUENCE_ID
AND MBC.PLAN_ID = MOC.PLAN_ID
AND MBC.SR_INSTANCE_ID = MOC.SR_INSTANCE_ID
AND MBC.ORGANIZATION_ID = MOC.ORGANIZATION_ID
AND MBC.COMPONENT_SEQUENCE_ID = MOC.COMPONENT_SEQUENCE_ID
AND MRO.ROUTING_SEQUENCE_ID = MOC.ROUTING_SEQUENCE_ID
AND MRO.SR_INSTANCE_ID = MOC.SR_INSTANCE_ID
AND MRO.PLAN_ID = MOC.PLAN_ID
AND MRO.OPERATION_SEQUENCE_ID = MOC.OPERATION_SEQUENCE_ID
AND MR.PLAN_ID = MRO.PLAN_ID
AND MR.ROUTING_SEQUENCE_ID = MRO.ROUTING_SEQUENCE_ID
AND MR.SR_INSTANCE_ID = MRO.SR_INSTANCE_ID
AND NVL(MRO.USE_IN_PLAN
, 1) = MFG.LOOKUP_CODE
AND MFG.LOOKUP_TYPE = 'SYS_YES_NO'
AND NVL(MBC.COMPONENT_TYPE
, 0) <> 10 UNION ALL SELECT MRO.PLAN_ID
, MR.ORGANIZATION_ID
, MRO.SR_INSTANCE_ID
, MSC_GET_NAME.ORG_CODE(MR.ORGANIZATION_ID
, MRO.SR_INSTANCE_ID)
, MRO.OPERATION_SEQUENCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MR.ALTERNATE_ROUTING_DESIGNATOR
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, MRO.OPERATION_SEQ_NUM
, MRO.OPERATION_DESCRIPTION
, MRO.EFFECTIVITY_DATE
, MRO.DISABLE_DATE
, MRO.FROM_UNIT_NUMBER
, MRO.TO_UNIT_NUMBER
, MRO.OPTION_DEPENDENT_FLAG
, MRO.OPERATION_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_OPERATION_TYPE'
, MRO.OPERATION_TYPE)
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MR.ASSEMBLY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MTQTY')
, MRO.YIELD
, MRO.DEPARTMENT_ID
, MRO.DEPARTMENT_CODE
, MRO.OPERATION_LEAD_TIME_PERCENT
, MRO.CUMULATIVE_YIELD
, MRO.REVERSE_CUMULATIVE_YIELD
, MRO.NET_PLANNING_PERCENT
, MRO.SETUP_DURATION
, MRO.TEAR_DOWN_DURATION
, MRO.UOM_CODE
, MRO.STANDARD_OPERATION_CODE
, MR.ASSEMBLY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MR.ASSEMBLY_ITEM_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MR.LINE_ID
, MSC_GET_NAME.DEPARTMENT_CODE(1
, MR.LINE_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, DECODE(MRO.USE_IN_PLAN
, NULL
, NULL
, MFG.MEANING)
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MR.ASSEMBLY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MINTT')
, MSC_GET_NAME.GET_MTQ_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MR.ASSEMBLY_ITEM_ID
, MR.CFM_ROUTING_FLAG
, 'MAXTT')
FROM MSC_ROUTINGS MR
, MSC_ROUTING_OPERATIONS MRO
, MFG_LOOKUPS MFG
WHERE MR.PLAN_ID = MRO.PLAN_ID
AND MR.ROUTING_SEQUENCE_ID = MRO.ROUTING_SEQUENCE_ID
AND MR.SR_INSTANCE_ID = MRO.SR_INSTANCE_ID
AND NOT EXISTS (SELECT 1
FROM MSC_OPERATION_COMPONENTS MOC
WHERE MOC.PLAN_ID = MRO.PLAN_ID
AND MOC.OPERATION_SEQUENCE_ID = MRO.OPERATION_SEQUENCE_ID
AND MOC.SR_INSTANCE_ID = MRO.SR_INSTANCE_ID
AND MOC.ROUTING_SEQUENCE_ID = MRO.ROUTING_SEQUENCE_ID)
AND NVL(MRO.USE_IN_PLAN
, 1) = MFG.LOOKUP_CODE
AND MFG.LOOKUP_TYPE = 'SYS_YES_NO'