View: EAM_WORK_ORDERS_V

Product:	EAM - Enterprise Asset Management
Description:	View shows all maintenance work orders along with the work requests/service requests associated to them.
Implementation/DBA Data:	APPS.EAM_WORK_ORDERS_V

View Text

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

Columns