View: WIP_EAM_WORK_ORDERS_V

Product:	WIP - Work in Process
Description:	Maintenance Work Orders
Implementation/DBA Data:	APPS.WIP_EAM_WORK_ORDERS_V

View Text

SELECT WDJ.ROWID
, WDJ.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ROWID
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY
, WDJ.CREATION_DATE
, WDJ.CREATED_BY
, WDJ.LAST_UPDATE_LOGIN
, WDJ.DESCRIPTION
, WDJ.PRIMARY_ITEM_ID
, NVL(WDJ.ASSET_GROUP_ID
, NVL(WDJ1.ASSET_GROUP_ID
, WDJ1.REBUILD_ITEM_ID)) AS ASSET_GROUP_ID
, DECODE(WDJ.ASSET_GROUP_ID
, NULL
, DECODE(WDJ.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(WDJ.PARENT_WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID))
, CII.INSTANCE_NUMBER) AS ASSET_NUMBER
, CII.INSTANCE_DESCRIPTION
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, WDJ.WORK_ORDER_TYPE
, LU2.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
, LU3.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, LU4.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, LU6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, WDJ.NOTIFICATION_REQUIRED
, WDJ.SHUTDOWN_TYPE
, LU5.MEANING
, WDJ.TAGOUT_REQUIRED
, WDJ.PLAN_MAINTENANCE
, WDJ.FIRM_PLANNED_FLAG
, 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
, WE1.WIP_ENTITY_NAME
, WDJ.REBUILD_ITEM_ID
, DECODE(WDJ.REBUILD_ITEM_ID
, NULL
, NULL
, CII.INSTANCE_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
, LU7.MEANING AS SOURCE
, WDJ.MAINTENANCE_OBJECT_ID
, NULL AS SERVICE_REQUEST_ID
, NULL AS SERVICE_REQUEST
, WDJ.MATERIAL_ISSUE_BY_MO
, WDJ.ACTIVITY_SOURCE
, LU8.MEANING AS ACTIVITY_SOURCE_MEANING
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID
, 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
, MP.MAINT_ORGANIZATION_ID
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_ENTITIES WE1
, CSI_ITEM_INSTANCES CII
, WIP_SCHEDULE_GROUPS WSG
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
, MFG_LOOKUPS LU6
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU7
, WIP_DISCRETE_JOBS WDJ1
, MFG_LOOKUPS LU8
, EAM_WORK_ORDER_DETAILS_V EWODV
, MTL_PARAMETERS MP
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.MAINTENANCE_OBJECT_TYPE = 3
AND WDJ.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE'
AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE
AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE
AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE'
AND WDJ.PARENT_WIP_ENTITY_ID = WDJ1.WIP_ENTITY_ID(+)
AND WDJ1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = EWODV.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = EWODV.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID UNION ALL SELECT WDJ.ROWID
, WDJ.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ROWID
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.LAST_UPDATE_DATE
, WDJ.LAST_UPDATED_BY
, WDJ.CREATION_DATE
, WDJ.CREATED_BY
, WDJ.LAST_UPDATE_LOGIN
, WDJ.DESCRIPTION
, WDJ.PRIMARY_ITEM_ID
, NVL(WDJ1.ASSET_GROUP_ID
, WDJ1.REBUILD_ITEM_ID) AS ASSET_GROUP_ID
, DECODE(WDJ.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(WDJ.PARENT_WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID)) AS ASSET_NUMBER
, NULL
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, WDJ.WORK_ORDER_TYPE
, LU2.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
, LU3.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, LU4.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, LU6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, WDJ.NOTIFICATION_REQUIRED
, WDJ.SHUTDOWN_TYPE
, LU5.MEANING
, WDJ.TAGOUT_REQUIRED
, WDJ.PLAN_MAINTENANCE
, WDJ.FIRM_PLANNED_FLAG
, 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
, WE1.WIP_ENTITY_NAME
, WDJ.REBUILD_ITEM_ID
, NULL
, 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
, NULL AS INSTANCE_ID
, NULL AS INSTANCE_NUMBER
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, LU7.MEANING AS SOURCE
, WDJ.MAINTENANCE_OBJECT_ID
, NULL AS SERVICE_REQUEST_ID
, NULL AS SERVICE_REQUEST
, WDJ.MATERIAL_ISSUE_BY_MO
, WDJ.ACTIVITY_SOURCE
, LU8.MEANING AS ACTIVITY_SOURCE_MEANING
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS MAINTAINED_ITEM_ID
, 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
, NULL
, WDJ.ORGANIZATION_ID
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, WIP_ENTITIES WE1
, WIP_SCHEDULE_GROUPS WSG
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU4
, MFG_LOOKUPS LU5
, MFG_LOOKUPS LU6
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU7
, WIP_DISCRETE_JOBS WDJ1
, MFG_LOOKUPS LU8
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.MAINTENANCE_OBJECT_TYPE = 2
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND LU2.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND LU2.LOOKUP_TYPE(+)= 'WIP_EAM_WORK_ORDER_TYPE'
AND LU3.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND LU3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND LU4.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND LU4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND LU5.LOOKUP_CODE(+) = WDJ.SHUTDOWN_TYPE
AND LU5.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND LU6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND LU6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND LU8.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND LU8.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND WE1.WIP_ENTITY_ID(+) = WDJ.PARENT_WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND LU7.LOOKUP_CODE(+) = WDJ.MAINTENANCE_OBJECT_SOURCE
AND LU7.LOOKUP_TYPE(+) = 'WIP_MAINTENANCE_OBJECT_SOURCE'
AND WDJ.PARENT_WIP_ENTITY_ID = WDJ1.WIP_ENTITY_ID(+)
AND WDJ1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = EWODV.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = EWODV.ORGANIZATION_ID

Columns