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_RESOURCE_ITEMS_TREE_V

Source

View Text - Preformatted


SELECT req.plan_id, req.sr_instance_id, req.organization_id, dept_res.resource_group_name, dept_res.department_class, req.department_id, dept_res.department_code, dept_res.department_description, req.resource_id, dept_res.resource_code, dept_res.resource_description, dept_res.line_flag, sup.inventory_item_id, item.item_name, item.description, item.buyer_id, item.planner_code, item.abc_class, dept_res.batchable_flag FROM msc_system_items item, msc_department_resources dept_res, msc_supplies sup, msc_resource_requirements req WHERE item.inventory_item_id = sup.inventory_item_id AND item.organization_id = sup.organization_id AND item.sr_instance_id = sup.sr_instance_id AND item.plan_id = sup.plan_id AND dept_res.sr_instance_id = req.sr_instance_id AND dept_res.plan_id = req.plan_id AND dept_res.organization_id = req.organization_id AND dept_res.department_id = req.department_id AND dept_res.resource_id = req.resource_id AND sup.sr_instance_id = req.sr_instance_id AND sup.plan_id = req.plan_id AND sup.transaction_id = req.supply_id AND NVL(req.parent_id,2) = 2 UNION ALL SELECT DISTINCT rtg.plan_id, rtg.sr_instance_id, rtg.organization_id, dept_res.resource_group_name, dept_res.department_class, rtg_op.department_id, dept_res.department_code, dept_res.department_description, op_res.resource_id, dept_res.resource_code, dept_res.resource_description, dept_res.line_flag, rtg.assembly_item_id, item.item_name, item.description, item.buyer_id, item.planner_code, item.abc_class, dept_res.batchable_flag FROM msc_system_items item, msc_department_resources dept_res, msc_routings rtg, msc_routing_operations rtg_op, msc_operation_resource_seqs op_res_s, msc_operation_resources op_res WHERE item.inventory_item_id = rtg.assembly_item_id AND item.sr_instance_id = rtg.sr_instance_id AND item.organization_id = rtg.organization_id AND item.plan_id = rtg.plan_id AND dept_res.sr_instance_id = rtg_op.sr_instance_id AND dept_res.plan_id = rtg_op.plan_id AND dept_res.organization_id = rtg.organization_id AND dept_res.department_id = rtg_op.department_id AND rtg_op.plan_id = op_res_s.plan_id AND rtg_op.sr_instance_id = op_res_s.sr_instance_id AND rtg_op.routing_sequence_id = op_res_s.routing_sequence_id AND rtg_op.operation_sequence_id = op_res_s.operation_sequence_id AND op_res_s.plan_id = op_res.plan_id AND op_res_s.sr_instance_id = op_res.sr_instance_id AND op_res_s.routing_sequence_id = op_res.routing_sequence_id AND op_res_s.operation_sequence_id = op_res.operation_sequence_id AND op_res_s.resource_seq_num = op_res.resource_seq_num AND op_res.sr_instance_id = dept_res.sr_instance_id AND op_res.plan_id = dept_res.plan_id AND rtg.organization_id = dept_res.organization_id AND op_res_s.department_id = dept_res.department_id AND op_res.resource_id = dept_res.resource_id AND rtg.routing_sequence_id = rtg_op.routing_sequence_id AND rtg.sr_instance_id = rtg_op.sr_instance_id AND rtg.plan_id = rtg_op.plan_id UNION ALL SELECT sup.plan_id, sup.sr_instance_id, sup.organization_id, NULL, NULL, sup.line_id, dept_res.department_code, dept_res.department_description, -1, NULL, NULL, 1, sup.inventory_item_id, item.item_name, item.description, item.buyer_id, item.planner_code, item.abc_class, dept_res.batchable_flag FROM msc_system_items item, msc_department_resources dept_res, msc_supplies sup WHERE item.inventory_item_id = sup.inventory_item_id AND item.sr_instance_id = sup.sr_instance_id AND item.organization_id = sup.organization_id AND item.plan_id = sup.plan_id AND dept_res.sr_instance_id = sup.sr_instance_id AND dept_res.plan_id = -1 AND dept_res.organization_id = sup.organization_id AND dept_res.department_id = sup.line_id AND dept_res.resource_id = -1

View Text - HTML Formatted

SELECT REQ.PLAN_ID
, REQ.SR_INSTANCE_ID
, REQ.ORGANIZATION_ID
, DEPT_RES.RESOURCE_GROUP_NAME
, DEPT_RES.DEPARTMENT_CLASS
, REQ.DEPARTMENT_ID
, DEPT_RES.DEPARTMENT_CODE
, DEPT_RES.DEPARTMENT_DESCRIPTION
, REQ.RESOURCE_ID
, DEPT_RES.RESOURCE_CODE
, DEPT_RES.RESOURCE_DESCRIPTION
, DEPT_RES.LINE_FLAG
, SUP.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, ITEM.DESCRIPTION
, ITEM.BUYER_ID
, ITEM.PLANNER_CODE
, ITEM.ABC_CLASS
, DEPT_RES.BATCHABLE_FLAG
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_DEPARTMENT_RESOURCES DEPT_RES
, MSC_SUPPLIES SUP
, MSC_RESOURCE_REQUIREMENTS REQ
WHERE ITEM.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND ITEM.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND ITEM.PLAN_ID = SUP.PLAN_ID
AND DEPT_RES.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND DEPT_RES.PLAN_ID = REQ.PLAN_ID
AND DEPT_RES.ORGANIZATION_ID = REQ.ORGANIZATION_ID
AND DEPT_RES.DEPARTMENT_ID = REQ.DEPARTMENT_ID
AND DEPT_RES.RESOURCE_ID = REQ.RESOURCE_ID
AND SUP.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND SUP.PLAN_ID = REQ.PLAN_ID
AND SUP.TRANSACTION_ID = REQ.SUPPLY_ID
AND NVL(REQ.PARENT_ID
, 2) = 2 UNION ALL SELECT DISTINCT RTG.PLAN_ID
, RTG.SR_INSTANCE_ID
, RTG.ORGANIZATION_ID
, DEPT_RES.RESOURCE_GROUP_NAME
, DEPT_RES.DEPARTMENT_CLASS
, RTG_OP.DEPARTMENT_ID
, DEPT_RES.DEPARTMENT_CODE
, DEPT_RES.DEPARTMENT_DESCRIPTION
, OP_RES.RESOURCE_ID
, DEPT_RES.RESOURCE_CODE
, DEPT_RES.RESOURCE_DESCRIPTION
, DEPT_RES.LINE_FLAG
, RTG.ASSEMBLY_ITEM_ID
, ITEM.ITEM_NAME
, ITEM.DESCRIPTION
, ITEM.BUYER_ID
, ITEM.PLANNER_CODE
, ITEM.ABC_CLASS
, DEPT_RES.BATCHABLE_FLAG
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_DEPARTMENT_RESOURCES DEPT_RES
, MSC_ROUTINGS RTG
, MSC_ROUTING_OPERATIONS RTG_OP
, MSC_OPERATION_RESOURCE_SEQS OP_RES_S
, MSC_OPERATION_RESOURCES OP_RES
WHERE ITEM.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND ITEM.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND ITEM.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND ITEM.PLAN_ID = RTG.PLAN_ID
AND DEPT_RES.SR_INSTANCE_ID = RTG_OP.SR_INSTANCE_ID
AND DEPT_RES.PLAN_ID = RTG_OP.PLAN_ID
AND DEPT_RES.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND DEPT_RES.DEPARTMENT_ID = RTG_OP.DEPARTMENT_ID
AND RTG_OP.PLAN_ID = OP_RES_S.PLAN_ID
AND RTG_OP.SR_INSTANCE_ID = OP_RES_S.SR_INSTANCE_ID
AND RTG_OP.ROUTING_SEQUENCE_ID = OP_RES_S.ROUTING_SEQUENCE_ID
AND RTG_OP.OPERATION_SEQUENCE_ID = OP_RES_S.OPERATION_SEQUENCE_ID
AND OP_RES_S.PLAN_ID = OP_RES.PLAN_ID
AND OP_RES_S.SR_INSTANCE_ID = OP_RES.SR_INSTANCE_ID
AND OP_RES_S.ROUTING_SEQUENCE_ID = OP_RES.ROUTING_SEQUENCE_ID
AND OP_RES_S.OPERATION_SEQUENCE_ID = OP_RES.OPERATION_SEQUENCE_ID
AND OP_RES_S.RESOURCE_SEQ_NUM = OP_RES.RESOURCE_SEQ_NUM
AND OP_RES.SR_INSTANCE_ID = DEPT_RES.SR_INSTANCE_ID
AND OP_RES.PLAN_ID = DEPT_RES.PLAN_ID
AND RTG.ORGANIZATION_ID = DEPT_RES.ORGANIZATION_ID
AND OP_RES_S.DEPARTMENT_ID = DEPT_RES.DEPARTMENT_ID
AND OP_RES.RESOURCE_ID = DEPT_RES.RESOURCE_ID
AND RTG.ROUTING_SEQUENCE_ID = RTG_OP.ROUTING_SEQUENCE_ID
AND RTG.SR_INSTANCE_ID = RTG_OP.SR_INSTANCE_ID
AND RTG.PLAN_ID = RTG_OP.PLAN_ID UNION ALL SELECT SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, NULL
, NULL
, SUP.LINE_ID
, DEPT_RES.DEPARTMENT_CODE
, DEPT_RES.DEPARTMENT_DESCRIPTION
, -1
, NULL
, NULL
, 1
, SUP.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, ITEM.DESCRIPTION
, ITEM.BUYER_ID
, ITEM.PLANNER_CODE
, ITEM.ABC_CLASS
, DEPT_RES.BATCHABLE_FLAG
FROM MSC_SYSTEM_ITEMS ITEM
, MSC_DEPARTMENT_RESOURCES DEPT_RES
, MSC_SUPPLIES SUP
WHERE ITEM.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND ITEM.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND ITEM.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND ITEM.PLAN_ID = SUP.PLAN_ID
AND DEPT_RES.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND DEPT_RES.PLAN_ID = -1
AND DEPT_RES.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND DEPT_RES.DEPARTMENT_ID = SUP.LINE_ID
AND DEPT_RES.RESOURCE_ID = -1