View: MSC_RESOURCE_ITEMS_TREE_V

Product:	MSC - Advanced Supply Chain Planning
Description:
Implementation/DBA Data:	APPS.MSC_RESOURCE_ITEMS_TREE_V

View Text

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

Columns