View: MSC_ACTION_ORDERS_TREE_V

Product:	MSC - Advanced Supply Chain Planning
Description:	Ths view shows the recommendations in the tree
Implementation/DBA Data:	APPS.MSC_ACTION_ORDERS_TREE_V

View Text

SELECT MED.EXCEPTION_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MED.EXCEPTION_TYPE
, MED.ORGANIZATION_CODE
, NULL
, MED.ITEM_SEGMENTS
, NULL
, MED.PROJECT_ID
, MED.TASK_ID
, MED.PROJECT_NUMBER
, MED.TASK_NUMBER
, MED.PLANNING_GROUP
, MED.DEPARTMENT_ID
, MED.RESOURCE_ID
, MED.DEPARTMENT_LINE_CODE
, NULL
, DECODE(MED.RESOURCE_ID
, -1
, 1
, 2)
, MED.RESOURCE_CODE
, NULL
, MED.BATCHABLE_FLAG
FROM MSC_EXCEPTION_DETAILS_V MED UNION ALL SELECT S.TRANSACTION_ID
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.INVENTORY_ITEM_ID
, 101
, I.ORGANIZATION_CODE
, NULL
, I.ITEM_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
FROM MSC_WORKBENCH_DISPLAY_OPTIONS W
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND W.USER_ID = FND_GLOBAL.USER_ID
AND S.PLAN_ID = P.PLAN_ID
AND S.NEW_ORDER_PLACEMENT_DATE <= (P.PLAN_START_DATE + NVL(W.RECOMMENDATION_DAYS
, 0))
AND (((S.SOURCE_ORGANIZATION_ID <> S.ORGANIZATION_ID OR S.SOURCE_SR_INSTANCE_ID <> S.SR_INSTANCE_ID OR S.SOURCE_SUPPLIER_ID IS NOT NULL)
AND I.PURCHASING_ENABLED_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 2
AND I.PURCHASING_ENABLED_FLAG = 1))
AND S.ORDER_TYPE = 5 UNION ALL SELECT S.TRANSACTION_ID
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.INVENTORY_ITEM_ID
, 102
, I.ORGANIZATION_CODE
, NULL
, I.ITEM_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
FROM MSC_WORKBENCH_DISPLAY_OPTIONS W
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND W.USER_ID = FND_GLOBAL.USER_ID
AND S.PLAN_ID = P.PLAN_ID
AND S.NEW_ORDER_PLACEMENT_DATE <= (P.PLAN_START_DATE + NVL(W.RECOMMENDATION_DAYS
, 0))
AND ((S.SOURCE_ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.SOURCE_SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.BUILD_IN_WIP_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 1
AND I.BUILD_IN_WIP_FLAG = 1))
AND S.ORDER_TYPE = 5
AND NOT EXISTS (SELECT 1
FROM MSC_ROUTINGS R
WHERE R.ASSEMBLY_ITEM_ID = S.INVENTORY_ITEM_ID
AND R.ORGANIZATION_ID = S.ORGANIZATION_ID
AND R.PLAN_ID = S.PLAN_ID
AND R.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND NVL(R.ALTERNATE_ROUTING_DESIGNATOR
, '@@@') = NVL(S.ALTERNATE_ROUTING_DESIGNATOR
, '@@@')
AND R.CFM_ROUTING_FLAG = 1) UNION ALL SELECT S.TRANSACTION_ID
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.INVENTORY_ITEM_ID
, 103
, I.ORGANIZATION_CODE
, NULL
, I.ITEM_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
FROM MSC_WORKBENCH_DISPLAY_OPTIONS W
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND W.USER_ID = FND_GLOBAL.USER_ID
AND S.PLAN_ID = P.PLAN_ID
AND S.NEW_ORDER_PLACEMENT_DATE <= (P.PLAN_START_DATE + NVL(W.RECOMMENDATION_DAYS
, 0))
AND ((S.SOURCE_ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.SOURCE_SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.BUILD_IN_WIP_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 1
AND I.BUILD_IN_WIP_FLAG = 1))
AND S.ORDER_TYPE = 5
AND EXISTS (SELECT 1
FROM MSC_ROUTINGS R
WHERE R.ASSEMBLY_ITEM_ID = S.INVENTORY_ITEM_ID
AND R.ORGANIZATION_ID = S.ORGANIZATION_ID
AND R.PLAN_ID = S.PLAN_ID
AND R.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND NVL(R.ALTERNATE_ROUTING_DESIGNATOR
, '@@@') = NVL(S.ALTERNATE_ROUTING_DESIGNATOR
, '@@@')
AND R.CFM_ROUTING_FLAG = 1) UNION ALL SELECT S.TRANSACTION_ID
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.INVENTORY_ITEM_ID
, 104
, I.ORGANIZATION_CODE
, NULL
, I.ITEM_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
FROM MSC_WORKBENCH_DISPLAY_OPTIONS W
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND W.USER_ID = FND_GLOBAL.USER_ID
AND S.PLAN_ID = P.PLAN_ID
AND S.NEW_ORDER_PLACEMENT_DATE <= (P.PLAN_START_DATE + NVL(W.RECOMMENDATION_DAYS
, 0))
AND I.PLANNING_MAKE_BUY_CODE = 1
AND S.ORDER_TYPE = 13

Columns