VIEW: APPS.MSC_ACTION_ORDERS_TREE_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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