VIEW: APPS.EAM_OUTSTANDING_WORK_ORDERS_V

Source

View Text - Preformatted

SELECT fw.group_id, decode(fw.action_type, 1, 3, decode(wdj.pm_schedule_id, null, 1, 2)) work_order_pm_type, ml1.meaning, fw.action_type action_type, ml6.meaning, wdj.wip_entity_id, we.wip_entity_name, meaa.organization_id, fw.pm_forecast_id, meaa.inventory_item_id, meaa.instance_number , meaa.asset_activity_id, fw.last_update_date, fw.last_updated_by, fw.creation_date, fw.created_by, fw.last_update_login, wdj.status_type, decode(wdj.wip_entity_id, null, msi.description, wdj.description) description, wdj.firm_planned_flag, fw.scheduled_start_date, fw.scheduled_completion_date, wdj.scheduled_start_date, wdj.scheduled_completion_date, to_number(meaa.priority_code), ml5.meaning, fw.pm_schedule_id, wdj.work_order_type, ml8.meaning, meaa.activity_type_code, ml2.meaning, meaa.activity_cause_code, ml3.meaning, meaa.shutdown_type_code, ml4.meaning, meaa.owning_department_id, bd.department_code, wdj.notification_required, wdj.tagout_required, nvl(wdj.plan_maintenance, 'N'), fw.attribute_category, fw.attribute1, fw.attribute2, fw.attribute3, fw.attribute4, fw.attribute5, fw.attribute6, fw.attribute7, fw.attribute8, fw.attribute9, fw.attribute10, fw.attribute11, fw.attribute12, fw.attribute13, fw.attribute14, fw.attribute15, fw.cycle_id, fw.seq_id, fw.wo_status, ewsv.work_order_status, eps.name, meaa.eam_item_type , decode(wdj.wip_entity_id,null,ewsv.work_order_status, ewodv.work_order_status_pending) wo_status_disp, meaa.maintenance_object_id, meaa.maintenance_object_type FROM mfg_lookups ml1, mfg_lookups ml2, mfg_lookups ml3, mfg_lookups ml4, mfg_lookups ml5, mfg_lookups ml6, mfg_lookups ml8, bom_departments bd, mtl_eam_asset_activities_v meaa, eam_forecasted_work_orders fw, wip_entities we, wip_discrete_jobs wdj, eam_pm_schedulings eps, eam_wo_statuses_v ewsv, mtl_system_items_vl msi, eam_work_order_details_v ewodv WHERE fw.wip_entity_id = wdj.wip_entity_id(+) and wdj.wip_entity_id = we.wip_entity_id(+) and we.entity_type(+) = 6 and fw.process_flag <> 'Y' and fw.activity_association_id = meaa.activity_association_id(+) and bd.department_id (+) = meaa.owning_department_id and bd.organization_id (+) = meaa.organization_id and msi.inventory_item_id = meaa.asset_activity_id and msi.organization_id = meaa.organization_id and ml1.lookup_type (+) = 'EAM_WORK_ORDER_PM_TYPE' and ml1.lookup_code = decode(fw.action_type, 1, 3, decode(wdj.pm_schedule_id, null, 1, 2)) and ml2.lookup_type (+) = 'MTL_EAM_ACTIVITY_TYPE' and ml2.lookup_code (+) = meaa.activity_type_code and ml3.lookup_type (+) = 'MTL_EAM_ACTIVITY_CAUSE' and ml3.lookup_code (+) = meaa.activity_cause_code and ml4.lookup_type (+) = 'BOM_EAM_SHUTDOWN_TYPE' and ml4.lookup_code (+) = meaa.shutdown_type_code and ml5.lookup_type (+) = 'WIP_EAM_ACTIVITY_PRIORITY' and ml5.lookup_code (+) = meaa.priority_code and ml6.lookup_type (+) = 'EAM_WORK_ORDER_PM_ACTION' and ml6.lookup_code (+) = fw.action_type and ml8.lookup_type (+) = 'WIP_EAM_WORK_ORDER_TYPE' and ml8.lookup_code (+) = wdj.work_order_type and ewsv.status_id=fw.wo_status and fw.pm_schedule_id=eps.pm_schedule_id and fw.wip_entity_id = ewodv.wip_entity_id(+)

View Text - HTML Formatted

SELECT FW.GROUP_ID
, DECODE(FW.ACTION_TYPE
, 1
, 3
, DECODE(WDJ.PM_SCHEDULE_ID
, NULL
, 1
, 2)) WORK_ORDER_PM_TYPE
, ML1.MEANING
, FW.ACTION_TYPE ACTION_TYPE
, ML6.MEANING
, WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, MEAA.ORGANIZATION_ID
, FW.PM_FORECAST_ID
, MEAA.INVENTORY_ITEM_ID
, MEAA.INSTANCE_NUMBER
, MEAA.ASSET_ACTIVITY_ID
, FW.LAST_UPDATE_DATE
, FW.LAST_UPDATED_BY
, FW.CREATION_DATE
, FW.CREATED_BY
, FW.LAST_UPDATE_LOGIN
, WDJ.STATUS_TYPE
, DECODE(WDJ.WIP_ENTITY_ID
, NULL
, MSI.DESCRIPTION
, WDJ.DESCRIPTION) DESCRIPTION
, WDJ.FIRM_PLANNED_FLAG
, FW.SCHEDULED_START_DATE
, FW.SCHEDULED_COMPLETION_DATE
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, TO_NUMBER(MEAA.PRIORITY_CODE)
, ML5.MEANING
, FW.PM_SCHEDULE_ID
, WDJ.WORK_ORDER_TYPE
, ML8.MEANING
, MEAA.ACTIVITY_TYPE_CODE
, ML2.MEANING
, MEAA.ACTIVITY_CAUSE_CODE
, ML3.MEANING
, MEAA.SHUTDOWN_TYPE_CODE
, ML4.MEANING
, MEAA.OWNING_DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, WDJ.NOTIFICATION_REQUIRED
, WDJ.TAGOUT_REQUIRED
, NVL(WDJ.PLAN_MAINTENANCE
, 'N')
, FW.ATTRIBUTE_CATEGORY
, FW.ATTRIBUTE1
, FW.ATTRIBUTE2
, FW.ATTRIBUTE3
, FW.ATTRIBUTE4
, FW.ATTRIBUTE5
, FW.ATTRIBUTE6
, FW.ATTRIBUTE7
, FW.ATTRIBUTE8
, FW.ATTRIBUTE9
, FW.ATTRIBUTE10
, FW.ATTRIBUTE11
, FW.ATTRIBUTE12
, FW.ATTRIBUTE13
, FW.ATTRIBUTE14
, FW.ATTRIBUTE15
, FW.CYCLE_ID
, FW.SEQ_ID
, FW.WO_STATUS
, EWSV.WORK_ORDER_STATUS
, EPS.NAME
, MEAA.EAM_ITEM_TYPE
, DECODE(WDJ.WIP_ENTITY_ID
, NULL
, EWSV.WORK_ORDER_STATUS
, EWODV.WORK_ORDER_STATUS_PENDING) WO_STATUS_DISP
, MEAA.MAINTENANCE_OBJECT_ID
, MEAA.MAINTENANCE_OBJECT_TYPE
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, MFG_LOOKUPS ML5
, MFG_LOOKUPS ML6
, MFG_LOOKUPS ML8
, BOM_DEPARTMENTS BD
, MTL_EAM_ASSET_ACTIVITIES_V MEAA
, EAM_FORECASTED_WORK_ORDERS FW
, WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, EAM_PM_SCHEDULINGS EPS
, EAM_WO_STATUSES_V EWSV
, MTL_SYSTEM_ITEMS_VL MSI
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE FW.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID(+)
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND WE.ENTITY_TYPE(+) = 6
AND FW.PROCESS_FLAG <> 'Y'
AND FW.ACTIVITY_ASSOCIATION_ID = MEAA.ACTIVITY_ASSOCIATION_ID(+)
AND BD.DEPARTMENT_ID (+) = MEAA.OWNING_DEPARTMENT_ID
AND BD.ORGANIZATION_ID (+) = MEAA.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MEAA.ASSET_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = MEAA.ORGANIZATION_ID
AND ML1.LOOKUP_TYPE (+) = 'EAM_WORK_ORDER_PM_TYPE'
AND ML1.LOOKUP_CODE = DECODE(FW.ACTION_TYPE
, 1
, 3
, DECODE(WDJ.PM_SCHEDULE_ID
, NULL
, 1
, 2))
AND ML2.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ML2.LOOKUP_CODE (+) = MEAA.ACTIVITY_TYPE_CODE
AND ML3.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ML3.LOOKUP_CODE (+) = MEAA.ACTIVITY_CAUSE_CODE
AND ML4.LOOKUP_TYPE (+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND ML4.LOOKUP_CODE (+) = MEAA.SHUTDOWN_TYPE_CODE
AND ML5.LOOKUP_TYPE (+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND ML5.LOOKUP_CODE (+) = MEAA.PRIORITY_CODE
AND ML6.LOOKUP_TYPE (+) = 'EAM_WORK_ORDER_PM_ACTION'
AND ML6.LOOKUP_CODE (+) = FW.ACTION_TYPE
AND ML8.LOOKUP_TYPE (+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND ML8.LOOKUP_CODE (+) = WDJ.WORK_ORDER_TYPE
AND EWSV.STATUS_ID=FW.WO_STATUS
AND FW.PM_SCHEDULE_ID=EPS.PM_SCHEDULE_ID
AND FW.WIP_ENTITY_ID = EWODV.WIP_ENTITY_ID(+)