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.EAM_WORK_ORDERS_V

Source

View Text - Preformatted


SELECT WDJ.ROWID ROW_ID, ( SELECT WE.WIP_ENTITY_NAME FROM EAM_WO_RELATIONSHIPS EWR, WIP_ENTITIES WE WHERE EWR.CHILD_OBJECT_ID=WDJ.WIP_ENTITY_ID AND EWR.PARENT_RELATIONSHIP_TYPE=1 AND EWR.PARENT_OBJECT_ID=WE.WIP_ENTITY_ID ) AS SCHED_PARENT_WIP_ENTITY_NAME , ( SELECT ACTUAL_START_DATE FROM EAM_JOB_COMPLETION_TXNS WHERE TRANSACTION_TYPE=1 AND TRANSACTION_ID= ( SELECT MAX(TRANSACTION_ID) FROM EAM_JOB_COMPLETION_TXNS EJT WHERE EJT.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID AND EJT.ORGANIZATION_ID=WDJ.ORGANIZATION_ID ) ) AS ACTUAL_START_DATE, WDJ.WIP_ENTITY_ID AS wip_entity_id, WE.WIP_ENTITY_NAME WIP_ENTITY_NAME, WE.ROWID AS WE_ROW_ID, WE.ENTITY_TYPE ENTITY_TYPE, WDJ.ORGANIZATION_ID ORGANIZATION_ID, WDJ.LAST_UPDATE_DATE LAST_UPDATE_DATE, WDJ.LAST_UPDATED_BY LAST_UPDATED_BY, WDJ.CREATION_DATE CREATION_DATE, WDJ.CREATED_BY CREATED_BY, WDJ.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, WDJ.DESCRIPTION DESCRIPTION, WDJ.PRIMARY_ITEM_ID PRIMARY_ITEM_ID, NVL (wdj.asset_group_id, NVL (wdj1.rebuild_item_id, wdj1.asset_group_id) ) AS asset_group_id, NVL (wdj.asset_number, NVL (wdj1.asset_number, wdj1.rebuild_serial_number) ) AS asset_number, decode(WDJ.maintenance_object_type, 3,cii.instance_description,2, ( SELECT description FROM MTL_SYSTEM_ITEMS WHERE inventory_item_id = wdj.rebuild_item_id AND rownum = 1 ) ) ASSET_DESCRIPTION, WDJ.CLASS_CODE CLASS_CODE, WDJ.STATUS_TYPE STATUS_TYPE, WDJ.PM_SCHEDULE_ID PM_SCHEDULE_ID, WDJ.MANUAL_REBUILD_FLAG MANUAL_REBUILD_FLAG, WDJ.WORK_ORDER_TYPE WORK_ORDER_TYPE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.WORK_ORDER_TYPE AND lookup_type='WIP_EAM_WORK_ORDER_TYPE' ) WORK_ORDER_TYPE_DISP, WDJ.MATERIAL_ACCOUNT, WDJ.MATERIAL_OVERHEAD_ACCOUNT, WDJ.RESOURCE_ACCOUNT, WDJ.OUTSIDE_PROCESSING_ACCOUNT, WDJ.MATERIAL_VARIANCE_ACCOUNT, WDJ.RESOURCE_VARIANCE_ACCOUNT, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT, WDJ.STD_COST_ADJUSTMENT_ACCOUNT, WDJ.OVERHEAD_ACCOUNT, WDJ.OVERHEAD_VARIANCE_ACCOUNT, WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE, WDJ.DATE_RELEASED, WDJ.DATE_COMPLETED, WDJ.DATE_CLOSED, WDJ.OWNING_DEPARTMENT, ( SELECT DEPARTMENT_CODE FROM BOM_DEPARTMENTS WHERE ORGANIZATION_ID = wdj.organization_id AND department_id = wdj.owning_department ) OWNING_DEPARTMENT_CODE, WDJ.ACTIVITY_TYPE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.ACTIVITY_TYPE AND lookup_type='MTL_EAM_ACTIVITY_TYPE' ) ACTIVITY_TYPE_DISP, WDJ.ACTIVITY_CAUSE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.ACTIVITY_CAUSE AND lookup_type='MTL_EAM_ACTIVITY_CAUSE' ) ACTIVITY_CAUSE_DISP, WDJ.PRIORITY, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.PRIORITY AND lookup_type='WIP_EAM_ACTIVITY_PRIORITY' ) PRIORITY_DISP, WDJ.REQUESTED_START_DATE, WDJ.DUE_DATE REQUESTED_DUE_DATE, WDJ.ESTIMATION_STATUS, WDJ.NOTIFICATION_REQUIRED, WDJ.SHUTDOWN_TYPE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.SHUTDOWN_TYPE AND lookup_type='BOM_EAM_SHUTDOWN_TYPE' ) SHUTDOWN_TYPE_DISP, WDJ.TAGOUT_REQUIRED, WDJ.PLAN_MAINTENANCE, WDJ.FIRM_PLANNED_FLAG, WDJ.SCHEDULE_GROUP_ID, ( SELECT SCHEDULE_GROUP_NAME FROM WIP_SCHEDULE_GROUPS WHERE SCHEDULE_GROUP_ID = WDJ.SCHEDULE_GROUP_ID AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID ) SCHEDULE_GROUP_NAME, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME, WDJ.PROJECT_ID, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME, WDJ.TASK_ID, WDJ.PARENT_WIP_ENTITY_ID, ( SELECT WIP_ENTITY_NAME FROM WIP_ENTITIES WHERE WIP_ENTITY_ID=WDJ.PARENT_WIP_ENTITY_ID AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID ) PARENT_WIP_ENTITY_NAME, WDJ.REBUILD_ITEM_ID, decode(wdj.rebuild_item_id, null, null, cii.serial_number), WDJ.BOM_REFERENCE_ID, WDJ.ROUTING_REFERENCE_ID, WDJ.COMMON_BOM_SEQUENCE_ID, WDJ.COMMON_ROUTING_SEQUENCE_ID, WDJ.ALTERNATE_BOM_DESIGNATOR, WDJ.BOM_REVISION, WDJ.BOM_REVISION_DATE, WDJ.ALTERNATE_ROUTING_DESIGNATOR, WDJ.ROUTING_REVISION, WDJ.ROUTING_REVISION_DATE, WDJ.COMPLETION_SUBINVENTORY, WDJ.COMPLETION_LOCATOR_ID, WDJ.LOT_NUMBER, WDJ.DEMAND_CLASS, WDJ.ATTRIBUTE_CATEGORY, WDJ.ATTRIBUTE1, WDJ.ATTRIBUTE2, WDJ.ATTRIBUTE3, WDJ.ATTRIBUTE4, WDJ.ATTRIBUTE5, WDJ.ATTRIBUTE6, WDJ.ATTRIBUTE7, WDJ.ATTRIBUTE8, WDJ.ATTRIBUTE9, WDJ.ATTRIBUTE10, WDJ.ATTRIBUTE11, WDJ.ATTRIBUTE12, WDJ.ATTRIBUTE13, WDJ.ATTRIBUTE14, WDJ.ATTRIBUTE15, nvl(cii.instance_id, -1) AS INSTANCE_ID, nvl(cii.instance_number, '') AS INSTANCE_NUMBER, WDJ.MAINTENANCE_OBJECT_SOURCE, WDJ.MAINTENANCE_OBJECT_TYPE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.MAINTENANCE_OBJECT_SOURCE AND lookup_type='WIP_MAINTENANCE_OBJECT_SOURCE' ) AS SOURCE, WDJ.MAINTENANCE_OBJECT_ID, ( SELECT SERVICE_REQUEST_ID FROM EAM_WO_SERVICE_ASSOCIATION EWSA WHERE EWSA.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND ( EWSA.ENABLE_FLAG is null OR EWSA.ENABLE_FLAG='Y' ) ) SERVICE_REQUEST_ID, ( SELECT CIAB.INCIDENT_NUMBER FROM EAM_WO_SERVICE_ASSOCIATION EWSA, CS_INCIDENTS_B_SEC CIAB WHERE EWSA.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID AND CIAB.INCIDENT_ID = EWSA.SERVICE_REQUEST_ID AND ( EWSA.ENABLE_FLAG is null OR EWSA.ENABLE_FLAG='Y' ) ) AS SERVICE_REQUEST, WDJ.MATERIAL_ISSUE_BY_MO, WDJ.ACTIVITY_SOURCE, ( SELECT meaning FROM MFG_LOOKUPS WHERE lookup_code=WDJ.ACTIVITY_SOURCE AND lookup_type='MTL_EAM_ACTIVITY_SOURCE' ) AS ACTIVITY_SOURCE_MEANING, WDJ.EAM_LINEAR_LOCATION_ID, ewodv.PENDING_FLAG, ewodv.USER_DEFINED_STATUS_ID, ewodv.WORK_ORDER_STATUS, ewodv.WORK_ORDER_STATUS_PENDING, ewodv.MATERIAL_SHORTAGE_CHECK_DATE, ewodv.MATERIAL_SHORTAGE_FLAG, ewodv.MATERIAL_SHORTAGE_DISP, ewodv.WORKFLOW_TYPE, ewodv.WARRANTY_CLAIM_STATUS, ewodv.CYCLE_ID, ewodv.SEQ_ID, ewodv.DS_SCHEDULED_FLAG, cii.serial_number AS asset_serial_number, mp.maint_organization_id AS Maint_organization_id, ( SELECT msik.concatenated_segments FROM mtl_system_items_kfv msik WHERE msik.inventory_item_id = NVL(wdj.rebuild_item_id,wdj.asset_group_id) AND rownum=1 ) AS ASSET_REBUILD_GROUP , ewodv.PM_SUGGESTED_START_DATE, ewodv.PM_SUGGESTED_END_DATE, ( select eaf.failure_id from eam_asset_failures eaf where eaf.source_id = wdj.wip_entity_id) as failure_id, ( select eafc.failure_entry_id from eam_asset_failures eaf, eam_asset_failure_codes eafc where eaf.source_id = wdj.wip_entity_id and eafc.failure_id = eaf.failure_id) as failure_entry_id, ( select eafc.failure_code from eam_asset_failures eaf, eam_asset_failure_codes eafc where eaf.source_id = wdj.wip_entity_id and eafc.failure_id = eaf.failure_id) as failure_code, ( select eafc.cause_code from eam_asset_failures eaf, eam_asset_failure_codes eafc where eaf.source_id = wdj.wip_entity_id and eafc.failure_id = eaf.failure_id) as cause_code, ( select eafc.resolution_code from eam_asset_failures eaf, eam_asset_failure_codes eafc where eaf.source_id = wdj.wip_entity_id and eafc.failure_id = eaf.failure_id) as resolution_code, nvl(ewodv.failure_code_required,'N') , ( select eaf.failure_date from eam_asset_failures eaf where eaf.source_id = wdj.wip_entity_id) as failure_date, ( select eafc.comments from eam_asset_failures eaf, eam_asset_failure_codes eafc where eaf.source_id = wdj.wip_entity_id and eafc.failure_id = eaf.failure_id) as comments, (select instance_number from csi_item_instances where instance_id = wdj1.maintenance_object_id AND wdj1.maintenance_object_type = 3 ) as parent_instance_number FROM wip_entities we, wip_discrete_jobs wdj1, wip_discrete_jobs wdj, eam_work_order_details_v ewodv, csi_item_instances cii, mtl_parameters mp WHERE wdj.wip_entity_id = we.wip_entity_id AND wdj.organization_id = we.organization_id AND we.entity_type IN (6, 7) AND wdj.parent_wip_entity_id = wdj1.wip_entity_id(+) AND wdj.organization_id = wdj1.organization_id(+) AND ewodv.wip_entity_id = wdj.wip_entity_id AND ewodv.organization_id = wdj.organization_id AND DECODE(wdj.maintenance_object_type,3,wdj.maintenance_object_id,NULL) = cii.instance_id(+) AND mp.organization_id(+) = cii.last_vld_organization_id

View Text - HTML Formatted

SELECT WDJ.ROWID ROW_ID
, ( SELECT WE.WIP_ENTITY_NAME
FROM EAM_WO_RELATIONSHIPS EWR
, WIP_ENTITIES WE
WHERE EWR.CHILD_OBJECT_ID=WDJ.WIP_ENTITY_ID
AND EWR.PARENT_RELATIONSHIP_TYPE=1
AND EWR.PARENT_OBJECT_ID=WE.WIP_ENTITY_ID ) AS SCHED_PARENT_WIP_ENTITY_NAME
, ( SELECT ACTUAL_START_DATE
FROM EAM_JOB_COMPLETION_TXNS
WHERE TRANSACTION_TYPE=1
AND TRANSACTION_ID= ( SELECT MAX(TRANSACTION_ID)
FROM EAM_JOB_COMPLETION_TXNS EJT
WHERE EJT.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND EJT.ORGANIZATION_ID=WDJ.ORGANIZATION_ID ) ) AS ACTUAL_START_DATE
, WDJ.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME WIP_ENTITY_NAME
, WE.ROWID AS WE_ROW_ID
, WE.ENTITY_TYPE ENTITY_TYPE
, WDJ.ORGANIZATION_ID ORGANIZATION_ID
, WDJ.LAST_UPDATE_DATE LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY LAST_UPDATED_BY
, WDJ.CREATION_DATE CREATION_DATE
, WDJ.CREATED_BY CREATED_BY
, WDJ.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, WDJ.DESCRIPTION DESCRIPTION
, WDJ.PRIMARY_ITEM_ID PRIMARY_ITEM_ID
, NVL (WDJ.ASSET_GROUP_ID
, NVL (WDJ1.REBUILD_ITEM_ID
, WDJ1.ASSET_GROUP_ID) ) AS ASSET_GROUP_ID
, NVL (WDJ.ASSET_NUMBER
, NVL (WDJ1.ASSET_NUMBER
, WDJ1.REBUILD_SERIAL_NUMBER) ) AS ASSET_NUMBER
, DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, CII.INSTANCE_DESCRIPTION
, 2
, ( SELECT DESCRIPTION
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = WDJ.REBUILD_ITEM_ID
AND ROWNUM = 1 ) ) ASSET_DESCRIPTION
, WDJ.CLASS_CODE CLASS_CODE
, WDJ.STATUS_TYPE STATUS_TYPE
, WDJ.PM_SCHEDULE_ID PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG MANUAL_REBUILD_FLAG
, WDJ.WORK_ORDER_TYPE WORK_ORDER_TYPE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.WORK_ORDER_TYPE
AND LOOKUP_TYPE='WIP_EAM_WORK_ORDER_TYPE' ) WORK_ORDER_TYPE_DISP
, WDJ.MATERIAL_ACCOUNT
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WDJ.RESOURCE_ACCOUNT
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WDJ.MATERIAL_VARIANCE_ACCOUNT
, WDJ.RESOURCE_VARIANCE_ACCOUNT
, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT
, WDJ.STD_COST_ADJUSTMENT_ACCOUNT
, WDJ.OVERHEAD_ACCOUNT
, WDJ.OVERHEAD_VARIANCE_ACCOUNT
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, WDJ.OWNING_DEPARTMENT
, ( SELECT DEPARTMENT_CODE
FROM BOM_DEPARTMENTS
WHERE ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND DEPARTMENT_ID = WDJ.OWNING_DEPARTMENT ) OWNING_DEPARTMENT_CODE
, WDJ.ACTIVITY_TYPE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.ACTIVITY_TYPE
AND LOOKUP_TYPE='MTL_EAM_ACTIVITY_TYPE' ) ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.ACTIVITY_CAUSE
AND LOOKUP_TYPE='MTL_EAM_ACTIVITY_CAUSE' ) ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.PRIORITY
AND LOOKUP_TYPE='WIP_EAM_ACTIVITY_PRIORITY' ) PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE REQUESTED_DUE_DATE
, WDJ.ESTIMATION_STATUS
, WDJ.NOTIFICATION_REQUIRED
, WDJ.SHUTDOWN_TYPE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.SHUTDOWN_TYPE
AND LOOKUP_TYPE='BOM_EAM_SHUTDOWN_TYPE' ) SHUTDOWN_TYPE_DISP
, WDJ.TAGOUT_REQUIRED
, WDJ.PLAN_MAINTENANCE
, WDJ.FIRM_PLANNED_FLAG
, WDJ.SCHEDULE_GROUP_ID
, ( SELECT SCHEDULE_GROUP_NAME
FROM WIP_SCHEDULE_GROUPS
WHERE SCHEDULE_GROUP_ID = WDJ.SCHEDULE_GROUP_ID
AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID ) SCHEDULE_GROUP_NAME
, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME
, WDJ.PROJECT_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME
, WDJ.TASK_ID
, WDJ.PARENT_WIP_ENTITY_ID
, ( SELECT WIP_ENTITY_NAME
FROM WIP_ENTITIES
WHERE WIP_ENTITY_ID=WDJ.PARENT_WIP_ENTITY_ID
AND ORGANIZATION_ID = WDJ.ORGANIZATION_ID ) PARENT_WIP_ENTITY_NAME
, WDJ.REBUILD_ITEM_ID
, DECODE(WDJ.REBUILD_ITEM_ID
, NULL
, NULL
, CII.SERIAL_NUMBER)
, WDJ.BOM_REFERENCE_ID
, WDJ.ROUTING_REFERENCE_ID
, WDJ.COMMON_BOM_SEQUENCE_ID
, WDJ.COMMON_ROUTING_SEQUENCE_ID
, WDJ.ALTERNATE_BOM_DESIGNATOR
, WDJ.BOM_REVISION
, WDJ.BOM_REVISION_DATE
, WDJ.ALTERNATE_ROUTING_DESIGNATOR
, WDJ.ROUTING_REVISION
, WDJ.ROUTING_REVISION_DATE
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, WDJ.LOT_NUMBER
, WDJ.DEMAND_CLASS
, WDJ.ATTRIBUTE_CATEGORY
, WDJ.ATTRIBUTE1
, WDJ.ATTRIBUTE2
, WDJ.ATTRIBUTE3
, WDJ.ATTRIBUTE4
, WDJ.ATTRIBUTE5
, WDJ.ATTRIBUTE6
, WDJ.ATTRIBUTE7
, WDJ.ATTRIBUTE8
, WDJ.ATTRIBUTE9
, WDJ.ATTRIBUTE10
, WDJ.ATTRIBUTE11
, WDJ.ATTRIBUTE12
, WDJ.ATTRIBUTE13
, WDJ.ATTRIBUTE14
, WDJ.ATTRIBUTE15
, NVL(CII.INSTANCE_ID
, -1) AS INSTANCE_ID
, NVL(CII.INSTANCE_NUMBER
, '') AS INSTANCE_NUMBER
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.MAINTENANCE_OBJECT_SOURCE
AND LOOKUP_TYPE='WIP_MAINTENANCE_OBJECT_SOURCE' ) AS SOURCE
, WDJ.MAINTENANCE_OBJECT_ID
, ( SELECT SERVICE_REQUEST_ID
FROM EAM_WO_SERVICE_ASSOCIATION EWSA
WHERE EWSA.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND ( EWSA.ENABLE_FLAG IS NULL OR EWSA.ENABLE_FLAG='Y' ) ) SERVICE_REQUEST_ID
, ( SELECT CIAB.INCIDENT_NUMBER
FROM EAM_WO_SERVICE_ASSOCIATION EWSA
, CS_INCIDENTS_B_SEC CIAB
WHERE EWSA.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND CIAB.INCIDENT_ID = EWSA.SERVICE_REQUEST_ID
AND ( EWSA.ENABLE_FLAG IS NULL OR EWSA.ENABLE_FLAG='Y' ) ) AS SERVICE_REQUEST
, WDJ.MATERIAL_ISSUE_BY_MO
, WDJ.ACTIVITY_SOURCE
, ( SELECT MEANING
FROM MFG_LOOKUPS
WHERE LOOKUP_CODE=WDJ.ACTIVITY_SOURCE
AND LOOKUP_TYPE='MTL_EAM_ACTIVITY_SOURCE' ) AS ACTIVITY_SOURCE_MEANING
, WDJ.EAM_LINEAR_LOCATION_ID
, EWODV.PENDING_FLAG
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS
, EWODV.WORK_ORDER_STATUS_PENDING
, EWODV.MATERIAL_SHORTAGE_CHECK_DATE
, EWODV.MATERIAL_SHORTAGE_FLAG
, EWODV.MATERIAL_SHORTAGE_DISP
, EWODV.WORKFLOW_TYPE
, EWODV.WARRANTY_CLAIM_STATUS
, EWODV.CYCLE_ID
, EWODV.SEQ_ID
, EWODV.DS_SCHEDULED_FLAG
, CII.SERIAL_NUMBER AS ASSET_SERIAL_NUMBER
, MP.MAINT_ORGANIZATION_ID AS MAINT_ORGANIZATION_ID
, ( SELECT MSIK.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.INVENTORY_ITEM_ID = NVL(WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID)
AND ROWNUM=1 ) AS ASSET_REBUILD_GROUP
, EWODV.PM_SUGGESTED_START_DATE
, EWODV.PM_SUGGESTED_END_DATE
, ( SELECT EAF.FAILURE_ID
FROM EAM_ASSET_FAILURES EAF
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID) AS FAILURE_ID
, ( SELECT EAFC.FAILURE_ENTRY_ID
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID
AND EAFC.FAILURE_ID = EAF.FAILURE_ID) AS FAILURE_ENTRY_ID
, ( SELECT EAFC.FAILURE_CODE
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID
AND EAFC.FAILURE_ID = EAF.FAILURE_ID) AS FAILURE_CODE
, ( SELECT EAFC.CAUSE_CODE
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID
AND EAFC.FAILURE_ID = EAF.FAILURE_ID) AS CAUSE_CODE
, ( SELECT EAFC.RESOLUTION_CODE
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID
AND EAFC.FAILURE_ID = EAF.FAILURE_ID) AS RESOLUTION_CODE
, NVL(EWODV.FAILURE_CODE_REQUIRED
, 'N')
, ( SELECT EAF.FAILURE_DATE
FROM EAM_ASSET_FAILURES EAF
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID) AS FAILURE_DATE
, ( SELECT EAFC.COMMENTS
FROM EAM_ASSET_FAILURES EAF
, EAM_ASSET_FAILURE_CODES EAFC
WHERE EAF.SOURCE_ID = WDJ.WIP_ENTITY_ID
AND EAFC.FAILURE_ID = EAF.FAILURE_ID) AS COMMENTS
, (SELECT INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = WDJ1.MAINTENANCE_OBJECT_ID
AND WDJ1.MAINTENANCE_OBJECT_TYPE = 3 ) AS PARENT_INSTANCE_NUMBER
FROM WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ1
, WIP_DISCRETE_JOBS WDJ
, EAM_WORK_ORDER_DETAILS_V EWODV
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.PARENT_WIP_ENTITY_ID = WDJ1.WIP_ENTITY_ID(+)
AND WDJ.ORGANIZATION_ID = WDJ1.ORGANIZATION_ID(+)
AND EWODV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND EWODV.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, WDJ.MAINTENANCE_OBJECT_ID
, NULL) = CII.INSTANCE_ID(+)
AND MP.ORGANIZATION_ID(+) = CII.LAST_VLD_ORGANIZATION_ID