FND Design Data [Home] [Help]

View: WIP_EAM_WORK_ORDER_DTLS_V

Product: WIP - Work in Process
Description: Detailed information for maintenance work orders
Implementation/DBA Data: ViewAPPS.WIP_EAM_WORK_ORDER_DTLS_V
View Text

SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, CII.INSTANCE_NUMBER
, NULL)
, CII.INSTANCE_DESCRIPTION
, JOB.ASSET_GROUP_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PRIMARY_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, CII.CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS
, EOMD.AREA_ID
, LOC.LOCATION_CODES
, EOMD.OWNING_DEPARTMENT_ID
, BD2.DEPARTMENT_CODE
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, CII.INSTANCE_NUMBER
, NULL)
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, MSI.CONCATENATED_SEGMENTS)
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, CII.SERIAL_NUMBER
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_EAM_LOCATIONS LOC
, MTL_SYSTEM_ITEMS_KFV MSI2
, MTL_CATEGORIES_KFV MCK
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENTS BD2
, EAM_ORG_MAINT_DEFAULTS EOMD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50
AND LOC.LOCATION_ID (+) = EOMD.AREA_ID
AND MCK.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD2.DEPARTMENT_ID (+) = EOMD.OWNING_DEPARTMENT_ID UNION ALL SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, CII.INSTANCE_NUMBER
, NULL)
, CII.INSTANCE_DESCRIPTION
, JOB.ASSET_GROUP_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PRIMARY_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 1
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, CII.CATEGORY_ID
, MCK.CONCATENATED_SEGMENTS
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, CII.INSTANCE_NUMBER
, NULL)
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, MSI.CONCATENATED_SEGMENTS)
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, DECODE(JOB.ASSET_GROUP_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI2.CONCATENATED_SEGMENTS
, NULL)
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, DECODE(MSI.EAM_ITEM_TYPE
, 3
, MSI.CONCATENATED_SEGMENTS
, NULL)
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, CII.SERIAL_NUMBER
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_KFV MSI2
, MTL_CATEGORIES_KFV MCK
, BOM_DEPARTMENTS BD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MCK.CATEGORY_ID (+) = CII.CATEGORY_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.OBJECT_ID = CII.INSTANCE_ID
AND EOMD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND EOMD.OBJECT_TYPE = 50) UNION ALL SELECT JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, NULL
, ( SELECT DISTINCT MSI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = JOB.MAINTENANCE_OBJECT_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID )
, NULL
, NULL
, JOB.PRIMARY_ITEM_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, JOB.STATUS_TYPE
, STATUSLOOKUP.MEANING
, JOB.WORK_ORDER_TYPE
, JOB.DESCRIPTION
, JOB.PRIORITY
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, JOB.ACTIVITY_TYPE
, JOB.ACTIVITY_CAUSE
, JOB.ACTIVITY_SOURCE
, JOB.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.REQUESTED_START_DATE
, JOB.NOTIFICATION_REQUIRED
, JOB.SHUTDOWN_TYPE
, JOB.TAGOUT_REQUIRED
, JOB.PLAN_MAINTENANCE
, JOB.PARENT_WIP_ENTITY_ID
, ENTITY2.WIP_ENTITY_NAME
, JOB.REBUILD_ITEM_ID
, NULL
, DECODE (JOB.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, ( SELECT DISTINCT MSI3.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI3
, MTL_PARAMETERS MP
WHERE MSI3.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI3.INVENTORY_ITEM_ID = NVL(PARENT.ASSET_GROUP_ID
, PARENT.REBUILD_ITEM_ID)
AND MP.MAINT_ORGANIZATION_ID = PARENT.ORGANIZATION_ID ) )
, DECODE(JOB.PARENT_WIP_ENTITY_ID
, NULL
, NULL
, EAM_COMMON_UTILITIES_PVT.GET_PARENT_ASSET(JOB.PARENT_WIP_ENTITY_ID
, JOB.ORGANIZATION_ID))
, MSI2.CONCATENATED_SEGMENTS
, JOB.MANUAL_REBUILD_FLAG
, JOB.LAST_UPDATE_DATE
, ( SELECT DISTINCT MSI.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = JOB.MAINTENANCE_OBJECT_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID )
, JOB.PROJECT_ID
, JOB.TASK_ID
, JOB.MATERIAL_ISSUE_BY_MO
, JOB.EAM_LINEAR_LOCATION_ID
, JOB.MAINTENANCE_OBJECT_TYPE
, JOB.MAINTENANCE_OBJECT_ID
, NULL
FROM WIP_DISCRETE_JOBS JOB
, WIP_ENTITIES ENTITY
, WIP_ENTITIES ENTITY2
, MFG_LOOKUPS STATUSLOOKUP
, MTL_SYSTEM_ITEMS_KFV MSI2
, BOM_DEPARTMENTS BD
, WIP_DISCRETE_JOBS PARENT
WHERE JOB.WIP_ENTITY_ID = ENTITY.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND STATUSLOOKUP.LOOKUP_CODE = JOB.STATUS_TYPE
AND STATUSLOOKUP.LOOKUP_TYPE = UPPER('WIP_JOB_STATUS')
AND ENTITY2.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND JOB.MAINTENANCE_OBJECT_TYPE = 2
AND PARENT.WIP_ENTITY_ID (+) = JOB.PARENT_WIP_ENTITY_ID
AND PARENT.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID

Columns

Name
WIP_ENTITY_ID
WIP_ENTITY_NAME
ORGANIZATION_ID
ASSET_NUMBER
ASSET_DESCRIPTION
ASSET_GROUP_ID
ASSET_GROUP
ASSET_ACTIVITY_ID
ASSET_ACTIVITY
ASSET_CATEGORY_ID
ASSET_CATEGORY
ASSET_LOCATION_ID
ASSET_LOCATION
ASSET_OWNING_DEPARTMENT_ID
ASSET_OWNING_DEPARTMENT
STATUS_TYPE
STATUS
WORK_ORDER_TYPE
DESCRIPTION
PRIORITY
START_DATE
COMPLETION_DATE
ACTIVITY_TYPE
ACTIVITY_CAUSE
ACTIVITY_SOURCE
OWNING_DEPARTMENT_ID
OWNING_DEPARTMENT
CLASS_CODE
REQUESTED_START_DATE
NOTIFICATION_REQUIRED
SHUTDOWN_TYPE
TAGOUT_REQUIRED
PLAN_MAINTENANCE
PARENT_WIP_ENTITY_ID
PARENT_WIP_ENTITY_NAME
REBUILD_ITEM_ID
REBUILD_SERIAL_NUMBER
CHARGE_ASSET_GROUP
CHARGE_ASSET_NUMBER
REBUILD_ACTIVITY
MANUAL_REBUILD_FLAG
LAST_UPDATE_DATE
REBUILD_ITEM
PROJECT_ID
TASK_ID
MATERIAL_ISSUE_BY_MO
EAM_LINEAR_LOCATION_ID
MAINTENANCE_OBJECT_TYPE
MAINTENANCE_OBJECT_ID
ASSET_SERIAL_NUMBER