View: EAM_OPERATIONS_DETAILS_V

Product:	EAM - Enterprise Asset Management
Description:	View provides details about the operations of a work order
Implementation/DBA Data:	APPS.EAM_OPERATIONS_DETAILS_V

View Text

SELECT WO.ROWID ROW_ID
, WDJ.ROWID WO_ROW_ID
, WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WDJ.ORGANIZATION_ID
, WDJ.DESCRIPTION WORK_DESCRIPTION
, WO.OPERATION_SEQ_NUM
, WO.DESCRIPTION OP_DESCRIPTION
, WO.FIRST_UNIT_START_DATE
, WOR.COMPLETION_DATE FIRST_UNIT_COMPLETION_DATE
, WOR.START_DATE AS LAST_UNIT_START_DATE
, WO.LAST_UNIT_COMPLETION_DATE DUE_DATE
, CII.INSTANCE_NUMBER ASSET_NUMBER
, CII.INSTANCE_ID
, ( SELECT DISTINCT MSIBKFV.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_B_KFV MSIBKFV
WHERE MSIBKFV.INVENTORY_ITEM_ID = NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)) AS ASSET_GROUP
, NVL(WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID) ASSET_GROUP_ID
, MLU.MEANING PRIORITY
, WO.LONG_DESCRIPTION OP_LONG_DESCPRIPTION
, WDJ.PRIMARY_ITEM_ID ACTIVITY_ID
, MSIBKFV2.CONCATENATED_SEGMENTS ACTIVITY
, WO.LAST_UPDATED_BY
, WO.LAST_UPDATE_DATE
, WO.CREATION_DATE
, WO.CREATED_BY
, WO.LAST_UPDATE_LOGIN
, WO.REQUEST_ID
, WO.PROGRAM_APPLICATION_ID
, WO.PROGRAM_ID
, WO.PROGRAM_UPDATE_DATE
, WORI.RESOURCE_SEQ_NUM
, BRE.RESOURCE_ID
, BRE.INSTANCE_ID
, BRE.PERSON_ID PERSON_ID
, WO.DEPARTMENT_ID
, NVL(WO.OPERATION_COMPLETED
, 'N') OPERATION_COMPLETED
, 'ASSIGNED' AS STATUS_ASSIGN
, NULL AS STATUS_DEPARTMENT
, MLU1.MEANING
, ( SELECT DISTINCT MLU2.MEANING
FROM MTL_SYSTEM_ITEMS_B MSI
, MFG_LOOKUPS MLU2
WHERE MSI.INVENTORY_ITEM_ID = NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)
AND MSI.EAM_ITEM_TYPE = MLU2.LOOKUP_CODE (+)
AND MLU2.LOOKUP_TYPE (+) ='MTL_EAM_ASSET_TYPE'
AND MLU2.ENABLED_FLAG(+)='Y' ) AS ASSETTYPE
, BD.DEPARTMENT_CODE
, WDJ.MAINTENANCE_OBJECT_TYPE
, WDJ.MAINTENANCE_OBJECT_ID
, CII.INSTANCE_NUMBER
FROM WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV2
, MFG_LOOKUPS MLU
, WIP_OPERATION_RESOURCES WOR
, WIP_OP_RESOURCE_INSTANCES WORI
, BOM_RESOURCE_EMPLOYEES BRE
, MFG_LOOKUPS MLU1
, BOM_DEPARTMENTS BD
WHERE WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
AND WE.ENTITY_TYPE=6
AND WDJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND WDJ.STATUS_TYPE = 3
AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WO.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND WORI.INSTANCE_ID = BRE.INSTANCE_ID
AND SYSDATE >= BRE.EFFECTIVE_START_DATE
AND SYSDATE <= BRE.EFFECTIVE_END_DATE
AND WOR.ORGANIZATION_ID = WORI.ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = WORI.RESOURCE_SEQ_NUM
AND WOR.WIP_ENTITY_ID = WORI.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WORI.OPERATION_SEQ_NUM
AND NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) = CII.INVENTORY_ITEM_ID (+)
AND DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, WDJ.MAINTENANCE_OBJECT_ID
, NULL) = CII.INSTANCE_ID(+)
AND WDJ.ORGANIZATION_ID=MSIBKFV2.ORGANIZATION_ID(+)
AND WDJ.PRIMARY_ITEM_ID=MSIBKFV2.INVENTORY_ITEM_ID(+)
AND MLU.LOOKUP_CODE (+)=WDJ.PRIORITY
AND MLU.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND MLU.ENABLED_FLAG(+)='Y'
AND WDJ.STATUS_TYPE = MLU1.LOOKUP_CODE (+)
AND MLU1.LOOKUP_TYPE (+) ='WIP_JOB_STATUS'
AND MLU1.ENABLED_FLAG(+)='Y'
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND WO.ORGANIZATION_ID = BD.ORGANIZATION_ID UNION SELECT WO.ROWID ROW_ID
, WDJ.ROWID WO_ROW_ID
, WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WDJ.ORGANIZATION_ID
, WDJ.DESCRIPTION WORK_DESCRIPTION
, WO.OPERATION_SEQ_NUM
, WO.DESCRIPTION OP_DESCRIPTION
, WO.FIRST_UNIT_START_DATE
, WOR.COMPLETION_DATE FIRST_UNIT_COMPLETION_DATE
, WOR.START_DATE AS LAST_UNIT_START_DATE
, WO.LAST_UNIT_COMPLETION_DATE DUE_DATE
, CII.INSTANCE_NUMBER ASSET_NUMBER
, CII.INSTANCE_ID
, ( SELECT DISTINCT MSIBKFV.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_B_KFV MSIBKFV
WHERE MSIBKFV.INVENTORY_ITEM_ID = NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)) AS ASSET_GROUP
, NVL(WDJ.REBUILD_ITEM_ID
, WDJ.ASSET_GROUP_ID) ASSET_GROUP_ID
, MLU.MEANING PRIORITY
, WO.LONG_DESCRIPTION OP_LONG_DESCPRIPTION
, WDJ.PRIMARY_ITEM_ID ACTIVITY_ID
, MSIBKFV2.CONCATENATED_SEGMENTS ACTIVITY
, WO.LAST_UPDATED_BY
, WO.LAST_UPDATE_DATE
, WO.CREATION_DATE
, WO.CREATED_BY
, WO.LAST_UPDATE_LOGIN
, WO.REQUEST_ID
, WO.PROGRAM_APPLICATION_ID
, WO.PROGRAM_ID
, WO.PROGRAM_UPDATE_DATE
, WOR.RESOURCE_SEQ_NUM
, WOR.RESOURCE_ID
, TO_NUMBER(NULL) AS INSTANCE_ID
, TO_NUMBER(NULL) AS PERSON_ID
, WO.DEPARTMENT_ID
, NVL(WO.OPERATION_COMPLETED
, 'N') OPERATION_COMPLETED
, NULL AS STATUS_ASSIGN
, 'DEPARTMENT' AS STATUS_DEPARTMENT
, MLU1.MEANING
, ( SELECT DISTINCT MLU2.MEANING
FROM MTL_SYSTEM_ITEMS_B MSI
, MFG_LOOKUPS MLU2
WHERE MSI.INVENTORY_ITEM_ID = NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)
AND MSI.EAM_ITEM_TYPE = MLU2.LOOKUP_CODE (+)
AND MLU2.LOOKUP_TYPE (+) ='MTL_EAM_ASSET_TYPE'
AND MLU2.ENABLED_FLAG(+)='Y' ) AS ASSETTYPE
, BD.DEPARTMENT_CODE
, WDJ.MAINTENANCE_OBJECT_TYPE
, WDJ.MAINTENANCE_OBJECT_ID
, CII.SERIAL_NUMBER
FROM WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, WIP_OPERATIONS WO
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV2
, MFG_LOOKUPS MLU
, WIP_OPERATION_RESOURCES WOR
, MFG_LOOKUPS MLU1
, BOM_DEPARTMENTS BD
WHERE WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
AND WE.ENTITY_TYPE=6
AND WDJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND WDJ.STATUS_TYPE = 3
AND WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WO.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND WOR.RESOURCE_ID = WOR.RESOURCE_ID
AND NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) = CII.INVENTORY_ITEM_ID (+)
AND DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, WDJ.MAINTENANCE_OBJECT_ID
, NULL) = CII.INSTANCE_ID(+)
AND WDJ.ORGANIZATION_ID=MSIBKFV2.ORGANIZATION_ID(+)
AND WDJ.PRIMARY_ITEM_ID=MSIBKFV2.INVENTORY_ITEM_ID(+)
AND MLU.LOOKUP_CODE (+)=WDJ.PRIORITY
AND MLU.LOOKUP_TYPE(+) ='WIP_EAM_ACTIVITY_PRIORITY'
AND MLU.ENABLED_FLAG(+)='Y'
AND WDJ.STATUS_TYPE = MLU1.LOOKUP_CODE (+)
AND MLU1.LOOKUP_TYPE (+) = 'WIP_JOB_STATUS'
AND MLU1.ENABLED_FLAG(+)='Y'
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND WO.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM WIP_OP_RESOURCE_INSTANCES WORI
WHERE WORI.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM
AND WORI.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WORI.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND WORI.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID)

Columns