eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_ROUTING_OPERATIONS_MTQ_V

Source

View Text - Preformatted


SELECT distinct mb.plan_id ,mb.sr_instance_id ,mb.organization_id ,mro.routing_sequence_id ,mro.operation_sequence_id ,mro.operation_seq_num ,mb.assembly_item_id ,mbc.inventory_item_id ,msc_get_name.item_name(mbc.inventory_item_id, mbc.organization_id ,mbc.plan_id, mbc.sr_instance_id) ,mr.alternate_routing_designator ,mbc.bill_sequence_id ,mb.alternate_bom_designator ,msc_get_name.get_mtq_coprod_details(mro.plan_id ,mro.sr_instance_id ,mro.routing_sequence_id ,mro.operation_sequence_id ,mbc.inventory_item_id ,'MTQTY') ,msc_get_name.get_mtq_coprod_details(mro.plan_id ,mro.sr_instance_id ,mro.routing_sequence_id ,mro.operation_sequence_id ,mbc.inventory_item_id ,'MINTT') ,msc_get_name.get_mtq_coprod_details(mro.plan_id ,mro.sr_instance_id ,mro.routing_sequence_id ,mro.operation_sequence_id ,mbc.inventory_item_id ,'MAXTT') ,decode(mtp.organization_type , 2 , null,(mbc.usage_quantity)*-100 ) ,decode(mtp.organization_type ,2 ,decode(mbc.scaling_type,null, null, msc_get_name.lookup_meaning('MSC_SCALING_TYPE',decode(mbc.scaling_type ,0,0 ,2,0 ,1,1 ,3,1 ,4,2 ,5,2 ) ) ) ,null) ,abs(mbc.usage_quantity) ,mbc.uom_code from msc_boms mb, msc_bom_components mbc, msc_routings mr, msc_operation_components moc , msc_routing_operations mro , msc_trading_partners mtp WHERE mb.bill_sequence_id = mbc.bill_sequence_id and mb.plan_id = mbc.plan_id and mb.organization_id = mbc.organization_id and mb.sr_instance_id = mbc.sr_instance_id and mbc.component_type = 10 and moc.bill_sequence_id(+) = mbc.bill_sequence_id and moc.plan_id (+) = mbc.plan_id and moc.organization_id (+) = mbc.organization_id and moc.sr_instance_id (+) = mbc.sr_instance_id and moc.component_sequence_id(+) = mbc.component_sequence_id and mro.plan_id(+) = moc.plan_id and mro.sr_instance_id (+) = moc.sr_instance_id and mro.routing_sequence_id(+) = moc.routing_sequence_id and mro.operation_sequence_id(+) = moc.operation_sequence_id and mr.plan_id(+) = mro.plan_id and mr.sr_instance_id (+) = mro.sr_instance_id and mr.routing_sequence_id(+) = mro.routing_sequence_id and mr.organization_id(+) = mro.organization_id and mtp.sr_tp_id = mbc.organization_id and mtp.sr_instance_id = mbc.sr_instance_id and mtp.partner_type = 3 UNION ALL SELECT distinct mb.plan_id ,mb.sr_instance_id ,mb.organization_id ,mr.routing_sequence_id ,msc_get_name.get_op_seq_id(mr.plan_id, mr.sr_instance_id, mr.routing_sequence_id, mb.yielding_op_seq_num) ,mb.yielding_op_seq_num ,mb.assembly_item_id ,mb.assembly_item_id ,msc_get_name.item_name(mb.assembly_item_id, mb.organization_id ,mb.plan_id, mb.sr_instance_id) ,mr.alternate_routing_designator ,mb.bill_sequence_id ,mb.alternate_bom_designator ,msc_get_name.get_mtq_coprod_details(mr.plan_id ,mr.sr_instance_id ,mr.routing_sequence_id ,msc_get_name.get_op_seq_id(mr.plan_id, mr.sr_instance_id, mr.routing_sequence_id, mb.yielding_op_seq_num) ,mb.assembly_item_id ,'MTQTY') ,msc_get_name.get_mtq_coprod_details(mr.plan_id ,mr.sr_instance_id ,mr.routing_sequence_id ,msc_get_name.get_op_seq_id(mr.plan_id, mr.sr_instance_id, mr.routing_sequence_id, mb.yielding_op_seq_num) ,mb.assembly_item_id ,'MINTT') ,msc_get_name.get_mtq_coprod_details(mr.plan_id ,mr.sr_instance_id ,mr.routing_sequence_id ,msc_get_name.get_op_seq_id(mr.plan_id, mr.sr_instance_id, mr.routing_sequence_id, mb.yielding_op_seq_num) ,mb.assembly_item_id ,'MAXTT') ,decode(mtp.organization_type , 2 , null,(mb.assembly_quantity)*-100 ) ,decode(mtp.organization_type ,2 ,decode(mb.scaling_type,null, null, msc_get_name.lookup_meaning('MSC_SCALING_TYPE',decode(mb.scaling_type ,0,0 ,2,0 ,1,1 ,3,1 ,4,2 ,5,2 ) ) ) ,null) ,(mb.assembly_quantity*-1) ,mb.uom FROM MSC_BOMS MB, MSC_ROUTINGS MR, MSC_TRADING_PARTNERS MTP, msc_process_effectivity mpe WHERE MR.PLAN_ID = MB.PLAN_ID AND MR.SR_INSTANCE_ID = MB.SR_INSTANCE_ID AND MR.ASSEMBLY_ITEM_ID = MB.ASSEMBLY_ITEM_ID AND MR.ORGANIZATION_ID = MB.ORGANIZATION_ID AND MTP.SR_TP_ID = MB.ORGANIZATION_ID AND MTP.SR_INSTANCE_ID = MB.SR_INSTANCE_ID AND MTP.PARTNER_TYPE = 3 AND MTP.ORGANIZATION_TYPE = 2 and mpe.plan_id = mb.plan_id and mpe.sr_instance_id = mb.sr_instance_id and mpe.organization_id = mb.organization_id and mpe.bill_sequence_id = mb.bill_sequence_id and mpe.plan_id = mr.plan_id and mpe.sr_instance_id = mr.sr_instance_id and mpe.organization_id = mr.organization_id and mpe.routing_sequence_id = mr.routing_sequence_id

View Text - HTML Formatted

SELECT DISTINCT MB.PLAN_ID
, MB.SR_INSTANCE_ID
, MB.ORGANIZATION_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MRO.OPERATION_SEQ_NUM
, MB.ASSEMBLY_ITEM_ID
, MBC.INVENTORY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MBC.INVENTORY_ITEM_ID
, MBC.ORGANIZATION_ID
, MBC.PLAN_ID
, MBC.SR_INSTANCE_ID)
, MR.ALTERNATE_ROUTING_DESIGNATOR
, MBC.BILL_SEQUENCE_ID
, MB.ALTERNATE_BOM_DESIGNATOR
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, 'MTQTY')
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, 'MINTT')
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MRO.PLAN_ID
, MRO.SR_INSTANCE_ID
, MRO.ROUTING_SEQUENCE_ID
, MRO.OPERATION_SEQUENCE_ID
, MBC.INVENTORY_ITEM_ID
, 'MAXTT')
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, NULL
, (MBC.USAGE_QUANTITY)*-100 )
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(MBC.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCALING_TYPE'
, DECODE(MBC.SCALING_TYPE
, 0
, 0
, 2
, 0
, 1
, 1
, 3
, 1
, 4
, 2
, 5
, 2 ) ) )
, NULL)
, ABS(MBC.USAGE_QUANTITY)
, MBC.UOM_CODE
FROM MSC_BOMS MB
, MSC_BOM_COMPONENTS MBC
, MSC_ROUTINGS MR
, MSC_OPERATION_COMPONENTS MOC
, MSC_ROUTING_OPERATIONS MRO
, MSC_TRADING_PARTNERS MTP
WHERE MB.BILL_SEQUENCE_ID = MBC.BILL_SEQUENCE_ID
AND MB.PLAN_ID = MBC.PLAN_ID
AND MB.ORGANIZATION_ID = MBC.ORGANIZATION_ID
AND MB.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND MBC.COMPONENT_TYPE = 10
AND MOC.BILL_SEQUENCE_ID(+) = MBC.BILL_SEQUENCE_ID
AND MOC.PLAN_ID (+) = MBC.PLAN_ID
AND MOC.ORGANIZATION_ID (+) = MBC.ORGANIZATION_ID
AND MOC.SR_INSTANCE_ID (+) = MBC.SR_INSTANCE_ID
AND MOC.COMPONENT_SEQUENCE_ID(+) = MBC.COMPONENT_SEQUENCE_ID
AND MRO.PLAN_ID(+) = MOC.PLAN_ID
AND MRO.SR_INSTANCE_ID (+) = MOC.SR_INSTANCE_ID
AND MRO.ROUTING_SEQUENCE_ID(+) = MOC.ROUTING_SEQUENCE_ID
AND MRO.OPERATION_SEQUENCE_ID(+) = MOC.OPERATION_SEQUENCE_ID
AND MR.PLAN_ID(+) = MRO.PLAN_ID
AND MR.SR_INSTANCE_ID (+) = MRO.SR_INSTANCE_ID
AND MR.ROUTING_SEQUENCE_ID(+) = MRO.ROUTING_SEQUENCE_ID
AND MR.ORGANIZATION_ID(+) = MRO.ORGANIZATION_ID
AND MTP.SR_TP_ID = MBC.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = MBC.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3 UNION ALL SELECT DISTINCT MB.PLAN_ID
, MB.SR_INSTANCE_ID
, MB.ORGANIZATION_ID
, MR.ROUTING_SEQUENCE_ID
, MSC_GET_NAME.GET_OP_SEQ_ID(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MB.YIELDING_OP_SEQ_NUM)
, MB.YIELDING_OP_SEQ_NUM
, MB.ASSEMBLY_ITEM_ID
, MB.ASSEMBLY_ITEM_ID
, MSC_GET_NAME.ITEM_NAME(MB.ASSEMBLY_ITEM_ID
, MB.ORGANIZATION_ID
, MB.PLAN_ID
, MB.SR_INSTANCE_ID)
, MR.ALTERNATE_ROUTING_DESIGNATOR
, MB.BILL_SEQUENCE_ID
, MB.ALTERNATE_BOM_DESIGNATOR
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MSC_GET_NAME.GET_OP_SEQ_ID(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MB.YIELDING_OP_SEQ_NUM)
, MB.ASSEMBLY_ITEM_ID
, 'MTQTY')
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MSC_GET_NAME.GET_OP_SEQ_ID(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MB.YIELDING_OP_SEQ_NUM)
, MB.ASSEMBLY_ITEM_ID
, 'MINTT')
, MSC_GET_NAME.GET_MTQ_COPROD_DETAILS(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MSC_GET_NAME.GET_OP_SEQ_ID(MR.PLAN_ID
, MR.SR_INSTANCE_ID
, MR.ROUTING_SEQUENCE_ID
, MB.YIELDING_OP_SEQ_NUM)
, MB.ASSEMBLY_ITEM_ID
, 'MAXTT')
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, NULL
, (MB.ASSEMBLY_QUANTITY)*-100 )
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(MB.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCALING_TYPE'
, DECODE(MB.SCALING_TYPE
, 0
, 0
, 2
, 0
, 1
, 1
, 3
, 1
, 4
, 2
, 5
, 2 ) ) )
, NULL)
, (MB.ASSEMBLY_QUANTITY*-1)
, MB.UOM
FROM MSC_BOMS MB
, MSC_ROUTINGS MR
, MSC_TRADING_PARTNERS MTP
, MSC_PROCESS_EFFECTIVITY MPE
WHERE MR.PLAN_ID = MB.PLAN_ID
AND MR.SR_INSTANCE_ID = MB.SR_INSTANCE_ID
AND MR.ASSEMBLY_ITEM_ID = MB.ASSEMBLY_ITEM_ID
AND MR.ORGANIZATION_ID = MB.ORGANIZATION_ID
AND MTP.SR_TP_ID = MB.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = MB.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND MTP.ORGANIZATION_TYPE = 2
AND MPE.PLAN_ID = MB.PLAN_ID
AND MPE.SR_INSTANCE_ID = MB.SR_INSTANCE_ID
AND MPE.ORGANIZATION_ID = MB.ORGANIZATION_ID
AND MPE.BILL_SEQUENCE_ID = MB.BILL_SEQUENCE_ID
AND MPE.PLAN_ID = MR.PLAN_ID
AND MPE.SR_INSTANCE_ID = MR.SR_INSTANCE_ID
AND MPE.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MPE.ROUTING_SEQUENCE_ID = MR.ROUTING_SEQUENCE_ID