View: EAM_CFR_WORK_ORDER_V

Product:	EAM - Enterprise Asset Management
Description:	This view displays details of all maintenance work orders.This view is used for ERES at the time of work order completion in the XGM.
Implementation/DBA Data:	APPS.EAM_CFR_WORK_ORDER_V

View Text

SELECT DISTINCT WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.DESCRIPTION
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS ASSET_GROUP_ID
, MSIK1.CONCATENATED_SEGMENTS AS ASSET_GROUP
, MSIK1.DESCRIPTION AS ASSET_GROUP_DESCRIPTION
, CII.INSTANCE_ID AS INSTANCE_ID
, CII.INSTANCE_NUMBER AS ASSET_NUMBER
, CII.SERIAL_NUMBER AS ASSET_SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION ASSET_NUMBER_DESCRIPTION
, MSIK1.EAM_ITEM_TYPE
, ML16.MEANING AS ASSET_TYPE_DISP
, WDJ.PRIMARY_ITEM_ID ASSET_ACTIVITY_ID
, MSIK2.CONCATENATED_SEGMENTS AS ASSET_ACTIVITY
, MSIK2.DESCRIPTION AS ASSET_ACTIVITY_DESCRIPTION
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, ML1.MEANING STATUS_TYPE_DISP
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, ML2.MEANING AS MANUAL_REBUILD_FLAG_DISP
, WDJ.WORK_ORDER_TYPE
, ML3.MEANING 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
, BD.DEPARTMENT_CODE
, WDJ.ACTIVITY_TYPE
, ML4.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, ML5.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, ML6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, ML20.MEANING AS ESTIMATION_STATUS_DISP
, WDJ.NOTIFICATION_REQUIRED
, ML7.MEANING AS NOTIFICATION_REQUIRED_DISP
, WDJ.SHUTDOWN_TYPE
, ML8.MEANING SHUTDOWN_TYPE_DISP
, EASH.START_DATE
, EASH.END_DATE
, WDJ.TAGOUT_REQUIRED
, ML9.MEANING AS TAGOUT_REQUIRED_DISP
, WDJ.PLAN_MAINTENANCE
, ML10.MEANING AS PLAN_MAINTENANCE_DISP
, WDJ.FIRM_PLANNED_FLAG
, ML11.MEANING AS FIRM_PLANNED_FLAG_DISP
, WDJ.SCHEDULE_GROUP_ID
, WSG.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
, 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.ISSUE_ZERO_COST_FLAG
, ML12.MEANING AS ISSUE_ZERO_COST_FLAG_DISP
, 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
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, WDJ.MAINTENANCE_OBJECT_ID
, WDJ.MATERIAL_ISSUE_BY_MO
, ML13.MEANING AS MATERIAL_ISSUE_BY_MO_DISP
, WDJ.ACTIVITY_SOURCE
, ML15.MEANING AS ACTIVITY_SOURCE_DISP
, WDJ.EAM_LINEAR_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE AS LOCATION_NAME
, PL.LOCATION_CODE AS LOCATION_CODE
, CII.PN_LOCATION_ID
, EAM_COMMON_UTILITIES_PVT.GET_ASSET_AREA(CII.INSTANCE_ID
, WDJ.ORGANIZATION_ID) AS AREA
, EWOD.PENDING_FLAG
, EWOD.MATERIAL_SHORTAGE_CHECK_DATE
, EWOD.MATERIAL_SHORTAGE_FLAG
, ML14.MEANING AS MATERIAL_SHORTAGE_DISP
, EWOD.WORKFLOW_TYPE
, ML18.MEANING AS WORKFLOW_TYPE_DISP
, EWOD.WARRANTY_CLAIM_STATUS
, ML19.MEANING AS WARRANTY_CLAIM_STATUS_DISP
, EWOD.ASSIGNMENT_COMPLETE
, ML17.MEANING AS ASSIGNMENT_COMPLETE_DISP
, EWOD.CYCLE_ID
, EWOD.SEQ_ID
, EWOD.DS_SCHEDULED_FLAG
, EWOD.USER_DEFINED_STATUS_ID
, DECODE(EWOD.PENDING_FLAG
, 'Y'
, EWSV.WORK_ORDER_STATUS || ' - ' || FND_MESSAGE.GET_STRING('EAM'
, 'EAM_PENDING_TEXT')
, EWSV.WORK_ORDER_STATUS)
, EWOD.PM_SUGGESTED_START_DATE
, EWOD.PM_SUGGESTED_END_DATE
, EWOD.PM_BASE_METER_READING
, EWOD.PM_BASE_METER
, CCT.NAME
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV MSIK1
, MTL_SYSTEM_ITEMS_KFV MSIK2
, CSI_ITEM_INSTANCES CII
, WIP_SCHEDULE_GROUPS WSG
, BOM_DEPARTMENTS BD
, PN_LOCATIONS_ALL PL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, MFG_LOOKUPS ML5
, MFG_LOOKUPS ML6
, MFG_LOOKUPS ML7
, MFG_LOOKUPS ML8
, MFG_LOOKUPS ML9
, MFG_LOOKUPS ML10
, MFG_LOOKUPS ML11
, MFG_LOOKUPS ML12
, MFG_LOOKUPS ML13
, MFG_LOOKUPS ML14
, MFG_LOOKUPS ML15
, MFG_LOOKUPS ML16
, MFG_LOOKUPS ML17
, MFG_LOOKUPS ML18
, MFG_LOOKUPS ML19
, MFG_LOOKUPS ML20
, EAM_WORK_ORDER_DETAILS EWOD
, EAM_WO_STATUSES_V EWSV
, EAM_ASSET_STATUS_HISTORY EASH
, CSI_COUNTERS_TL CCT
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.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND WDJ.MAINTENANCE_OBJECT_TYPE IN (2
, 3)
AND DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, WDJ.MAINTENANCE_OBJECT_ID
, -999) = CII.INSTANCE_ID (+)
AND MSIK1.INVENTORY_ITEM_ID = NVL( WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)
AND MSIK1.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIK2.INVENTORY_ITEM_ID (+) = WDJ.PRIMARY_ITEM_ID
AND MSIK2.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND SYSDATE >= NVL(PL.ACTIVE_START_DATE
, SYSDATE)
AND SYSDATE <= NVL(PL.ACTIVE_END_DATE
, SYSDATE)
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML2.LOOKUP_CODE = DECODE(NVL(WDJ.MANUAL_REBUILD_FLAG
, 'N')
, 'Y'
, 1
, 2)
AND ML3.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND ML3.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND ML4.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND ML4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ML5.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND ML5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ML6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND ML6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND ML7.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML7.LOOKUP_CODE = DECODE(NVL(WDJ.NOTIFICATION_REQUIRED
, 'N')
, 'Y'
, 1
, 2)
AND ML8.LOOKUP_CODE (+) = WDJ.SHUTDOWN_TYPE
AND ML8.LOOKUP_TYPE (+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND ML9.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML9.LOOKUP_CODE = DECODE(NVL(WDJ.TAGOUT_REQUIRED
, 'N')
, 'Y'
, 1
, 2)
AND ML10.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML10.LOOKUP_CODE = DECODE(NVL(WDJ.PLAN_MAINTENANCE
, 'N')
, 'Y'
, 1
, 2)
AND ML11.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML11.LOOKUP_CODE = WDJ.FIRM_PLANNED_FLAG
AND ML12.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML12.LOOKUP_CODE = DECODE(NVL(WDJ.ISSUE_ZERO_COST_FLAG
, 'N')
, 'Y'
, 1
, 2)
AND ML13.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML13.LOOKUP_CODE = DECODE(NVL(WDJ.MATERIAL_ISSUE_BY_MO
, 'N')
, 'Y'
, 1
, 2)
AND ML14.ENABLED_FLAG (+) = 'Y'
AND ML14.LOOKUP_TYPE (+) ='EAM_MATERIAL_SHORTAGE'
AND ML14.LOOKUP_CODE (+) = EWOD.MATERIAL_SHORTAGE_FLAG
AND ML15.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND ML15.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND ML16.LOOKUP_TYPE = 'MTL_EAM_ASSET_TYPE'
AND ML16.LOOKUP_CODE = MSIK1.EAM_ITEM_TYPE
AND ML17.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML17.LOOKUP_CODE = DECODE(NVL(EWOD.ASSIGNMENT_COMPLETE
, 'N')
, 'Y'
, 1
, 2)
AND ML18.LOOKUP_TYPE(+) = 'EAM_WORKFLOW_TYPE'
AND ML18.LOOKUP_CODE(+) = EWOD.WORKFLOW_TYPE
AND ML19.LOOKUP_TYPE(+) = 'EAM_WARRANTY_STATUS'
AND ML19.LOOKUP_CODE(+) = EWOD.WARRANTY_CLAIM_STATUS
AND ML20.LOOKUP_TYPE = 'CST_EAM_ESTIMATION_STATUS'
AND ML20.LOOKUP_CODE = NVL (WDJ.ESTIMATION_STATUS
, 1 )
AND WDJ.WIP_ENTITY_ID = EWOD.WIP_ENTITY_ID
AND EWOD.USER_DEFINED_STATUS_ID = EWSV.STATUS_ID
AND WDJ.WIP_ENTITY_ID = EASH.WIP_ENTITY_ID (+)
AND WDJ.ORGANIZATION_ID = EASH.ORGANIZATION_ID (+)
AND EASH.OPERATION_SEQ_NUM IS NULL
AND EASH.ENABLE_FLAG (+) = 'Y'
AND EWOD.PM_BASE_METER = CCT.COUNTER_ID(+)
AND CCT.LANGUAGE(+) = USERENV('LANG')

Columns