VIEW: APPS.EAM_OPERATIONS_DETAILS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)