eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_EAM_ASSET_ACTIVITIES_WB_V

Source

View Text - Preformatted


SELECT eaa.rowid row_id, eaa.activity_association_id, mp.maint_organization_id organization_id, eaa.asset_activity_id, msi.concatenated_segments activity, msi.description activity_description, ag.eam_item_type, ag.concatenated_segments asset_rebuild_group, cii.inventory_item_id, cii.instance_number, cii.serial_number, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, fl2.meaning activity_cause, eomd.activity_cause_code, fl3.meaning activity_type, eomd.activity_type_code, bd.department_code owning_department, eomd.owning_department_id, eomd.tagging_required_flag, fl4.meaning shutdown_type, eomd.shutdown_type_code, eomd.accounting_class_code, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, fl5.meaning activity_source, eomd.activity_source_code, eam_activityutilities_pvt.get_next_service_start_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_start_date, eam_activityutilities_pvt.get_next_service_end_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_end_date, msi.organization_id activity_organization, fl6.meaning work_order_type_disp, eomd.work_order_type, fl7.meaning planner_maintenance_disp, eomd.planner_maintenance, eomd.firm_planned_flag, eomd.plan_maintenance, eomd.notification_required FROM mtl_system_items_vl msi, mtl_eam_asset_activities eaa, eam_org_maint_defaults eomd, bom_departments bd, mfg_lookups fl1, mfg_lookups fl2, mfg_lookups fl3, mfg_lookups fl4, mfg_lookups fl5, mfg_lookups fl6, mfg_lookups fl7, csi_item_instances cii, mtl_parameters mp, mtl_system_items_kfv ag WHERE msi.inventory_item_id = eaa.asset_activity_id AND msi.organization_id = eomd.organization_id AND eaa.activity_association_id = eomd.object_id AND eomd.object_type = 60 AND eaa.maintenance_object_type = 3 AND eaa.maintenance_object_id = cii.instance_id AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = msi.organization_id AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND eomd.activity_cause_code = fl2.lookup_code(+) AND fl2.lookup_type(+) = 'MTL_EAM_ACTIVITY_CAUSE' AND eomd.activity_type_code = fl3.lookup_code(+) AND fl3.lookup_type(+) = 'MTL_EAM_ACTIVITY_TYPE' AND eomd.shutdown_type_code = fl4.lookup_code(+) AND fl4.lookup_type(+) = 'BOM_EAM_SHUTDOWN_TYPE' AND eomd.activity_source_code = fl5.lookup_code(+) AND fl5.lookup_type(+) = 'MTL_EAM_ACTIVITY_SOURCE' AND eomd.work_order_type = fl6.lookup_code(+) AND fl6.lookup_type(+) = 'WIP_EAM_WORK_ORDER_TYPE' AND eomd.PLANNER_MAINTENANCE = fl7.lookup_code(+) AND fl7.lookup_type(+) = 'EAM_PLANNER' AND eomd.owning_department_id = bd.department_id(+) AND cii.inventory_item_id = ag.inventory_item_id AND cii.last_vld_organization_id = ag.organization_id UNION ALL SELECT eaa.rowid row_id, eaa.activity_association_id, msi.organization_id, eaa.asset_activity_id, msi.concatenated_segments activity, msi.description activity_description, (SELECT eam_item_type FROM mtl_system_items_b WHERE inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) eam_item_type , (SELECT ag.concatenated_segments FROM mtl_system_items_kfv ag WHERE ag.inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) asset_rebuild_group, eaa.maintenance_object_id inventory_item_id, NULL, NULL, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, fl2.meaning activity_cause, eomd.activity_cause_code, fl3.meaning activity_type, eomd.activity_type_code, bd.department_code owning_department, eomd.owning_department_id, eomd.tagging_required_flag, fl4.meaning shutdown_type, eomd.shutdown_type_code, eomd.accounting_class_code, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, fl5.meaning activity_source, eomd.activity_source_code, NULL, NULL, msi.organization_id actvity_organization, fl6.meaning work_order_type_disp, eomd.work_order_type, fl7.meaning planner_maintenance_disp, eomd.planner_maintenance, eomd.firm_planned_flag, eomd.plan_maintenance, eomd.notification_required FROM mtl_system_items_vl msi, mtl_eam_asset_activities eaa, eam_org_maint_defaults eomd, bom_departments bd, mfg_lookups fl1, mfg_lookups fl2, mfg_lookups fl3, mfg_lookups fl4, mfg_lookups fl5, mfg_lookups fl6, mfg_lookups fl7 WHERE msi.inventory_item_id = eaa.asset_activity_id AND msi.organization_id = eomd.organization_id AND eaa.activity_association_id = eomd.object_id AND eomd.object_type = 40 AND eaa.maintenance_object_type = 2 AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND eomd.activity_cause_code = fl2.lookup_code(+) AND fl2.lookup_type(+) = 'MTL_EAM_ACTIVITY_CAUSE' AND eomd.activity_type_code = fl3.lookup_code(+) AND fl3.lookup_type(+) = 'MTL_EAM_ACTIVITY_TYPE' AND eomd.shutdown_type_code = fl4.lookup_code(+) AND fl4.lookup_type(+) = 'BOM_EAM_SHUTDOWN_TYPE' AND eomd.activity_source_code = fl5.lookup_code(+) AND fl5.lookup_type(+) = 'MTL_EAM_ACTIVITY_SOURCE' AND eomd.work_order_type = fl6.lookup_code(+) AND fl6.lookup_type(+) = 'WIP_EAM_WORK_ORDER_TYPE' AND eomd.PLANNER_MAINTENANCE = fl7.lookup_code(+) AND fl7.lookup_type(+) = 'EAM_PLANNER' AND eomd.owning_department_id = bd.department_id(+) UNION ALL SELECT eaa.rowid row_id, eaa.activity_association_id, mp.maint_organization_id organization_id, eaa.asset_activity_id, msi.concatenated_segments activity, msi.description activity_description, ag.eam_item_type, ag.concatenated_segments asset_rebuild_group, cii.inventory_item_id, cii.instance_number, cii.serial_number, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, NULL activity_cause, NULL, NULL activity_type, NULL, NULL owning_department, NULL, NULL, NULL, NULL, NULL, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, NULL activity_source, NULL, eam_activityutilities_pvt.get_next_service_start_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_start_date, eam_activityutilities_pvt.get_next_service_end_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_end_date, msi.organization_id activity_organization, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM mtl_system_items_vl msi, mtl_eam_asset_activities eaa, mfg_lookups fl1, csi_item_instances cii, mtl_parameters mp, mtl_system_items_kfv ag WHERE msi.inventory_item_id = eaa.asset_activity_id AND eaa.maintenance_object_type = 3 AND eaa.maintenance_object_id = cii.instance_id AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = msi.organization_id AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND cii.inventory_item_id = ag.inventory_item_id AND cii.last_vld_organization_id = ag.organization_id AND NOT EXISTS (SELECT 1 FROM eam_org_maint_defaults eomd WHERE eomd.organization_id = msi.organization_id AND eomd.object_id = eaa.activity_association_id AND eomd.object_type = 60 ) UNION ALL SELECT eaa.rowid row_id, eaa.activity_association_id, msi.organization_id, eaa.asset_activity_id, msi.concatenated_segments activity, msi.description activity_description, (SELECT eam_item_type FROM mtl_system_items_b WHERE inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) eam_item_type , (SELECT ag.concatenated_segments FROM mtl_system_items_kfv ag WHERE ag.inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) asset_rebuild_group, eaa.maintenance_object_id inventory_item_id, NULL, NULL, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, NULL activity_cause, NULL, NULL activity_type, NULL, NULL owning_department, NULL, NULL, NULL shutdown_type, NULL, NULL, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, NULL activity_source, NULL, NULL, NULL, msi.organization_id actvity_organization, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM mtl_system_items_vl msi, mtl_eam_asset_activities eaa, mfg_lookups fl1 WHERE msi.inventory_item_id = eaa.asset_activity_id AND eaa.maintenance_object_type = 2 AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND NOT EXISTS (SELECT 1 FROM eam_org_maint_defaults eomd WHERE eomd.organization_id = msi.organization_id AND eomd.object_id = eaa.activity_association_id AND eomd.object_type = 40 ) UNION ALL SELECT eaa.rowid row_id, eaa.activity_association_id, mp.maint_organization_id organization_id, eaa.asset_activity_id, (SELECT msik.concatenated_segments FROM mtl_system_items_kfv msik WHERE msik.inventory_item_id = eaa.asset_activity_id AND rownum = 1 ) activity, (SELECT msi.description FROM mtl_system_items_vl msi WHERE msi.inventory_item_id = eaa.asset_activity_id AND rownum = 1 ) activity_description, ag.eam_item_type, ag.concatenated_segments asset_rebuild_group, cii.inventory_item_id, cii.instance_number, cii.serial_number, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, NULL, NULL, NULL activity_type, NULL, NULL owning_department, NULL, NULL, NULL shutdown_type, NULL, NULL, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, NULL, NULL, eam_activityutilities_pvt.get_next_service_start_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_start_date, eam_activityutilities_pvt.get_next_service_end_date (eaa.activity_association_id, eaa.maintenance_object_id) next_service_end_date, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM mtl_eam_asset_activities eaa, mfg_lookups fl1, csi_item_instances cii, mtl_parameters mp, mtl_system_items_kfv ag WHERE eaa.maintenance_object_type = 3 AND eaa.maintenance_object_id = cii.instance_id AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id NOT IN (SELECT msi.organization_id FROM mtl_system_items_b msi WHERE msi.inventory_item_id = eaa.asset_activity_id ) AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND cii.inventory_item_id = ag.inventory_item_id AND cii.last_vld_organization_id = ag.organization_id UNION ALL SELECT eaa.rowid row_id, eaa.activity_association_id, mp.maint_organization_id, eaa.asset_activity_id, (SELECT msik.concatenated_segments FROM mtl_system_items_kfv msik WHERE msik.inventory_item_id = eaa.asset_activity_id AND rownum = 1 ) activity, (SELECT msi.description FROM mtl_system_items_vl msi WHERE msi.inventory_item_id = eaa.asset_activity_id AND rownum = 1 ) activity_description, (SELECT eam_item_type FROM mtl_system_items_b WHERE inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) eam_item_type, (SELECT ag.concatenated_segments FROM mtl_system_items_kfv ag WHERE ag.inventory_item_id = eaa.maintenance_object_id AND rownum = 1 ) asset_rebuild_group, eaa.inventory_item_id, NULL, eaa.serial_number, eaa.start_date_active, eaa.end_date_active, fl1.meaning priority, eaa.priority_code, NULL activity_cause, NULL, NULL activity_type, NULL, NULL owning_department, NULL, NULL, NULL shutdown_type, NULL, NULL, eaa.last_update_date, eaa.last_updated_by, eaa.creation_date, eaa.created_by, eaa.last_update_login, eaa.attribute_category, eaa.attribute1, eaa.attribute2, eaa.attribute3, eaa.attribute4, eaa.attribute5, eaa.attribute6, eaa.attribute7, eaa.attribute8, eaa.attribute9, eaa.attribute10, eaa.attribute11, eaa.attribute12, eaa.attribute13, eaa.attribute14, eaa.attribute15, eaa.request_id, eaa.program_application_id, eaa.program_id, eaa.program_update_date, eaa.maintenance_object_id, eaa.maintenance_object_type, eaa.creation_organization_id, eaa.tmpl_flag template_flag, NULL activity_source, NULL, NULL, NULL, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM mtl_eam_asset_activities eaa, mfg_lookups fl1, mtl_parameters mp, mtl_system_items_b msib WHERE eaa.inventory_item_id = msib.inventory_item_id AND msib.organization_id = mp.organization_id AND mp.maint_organization_id NOT IN (SELECT msi.organization_id from mtl_system_items_b msi where msi.inventory_item_id = eaa.asset_activity_id) AND eaa.maintenance_object_type = 2 AND eaa.priority_code = fl1.lookup_code(+) AND fl1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY'

View Text - HTML Formatted

SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, AG.EAM_ITEM_TYPE
, AG.CONCATENATED_SEGMENTS ASSET_REBUILD_GROUP
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_NUMBER
, CII.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, FL2.MEANING ACTIVITY_CAUSE
, EOMD.ACTIVITY_CAUSE_CODE
, FL3.MEANING ACTIVITY_TYPE
, EOMD.ACTIVITY_TYPE_CODE
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, EOMD.OWNING_DEPARTMENT_ID
, EOMD.TAGGING_REQUIRED_FLAG
, FL4.MEANING SHUTDOWN_TYPE
, EOMD.SHUTDOWN_TYPE_CODE
, EOMD.ACCOUNTING_CLASS_CODE
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, FL5.MEANING ACTIVITY_SOURCE
, EOMD.ACTIVITY_SOURCE_CODE
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_START_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_START_DATE
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_END_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_END_DATE
, MSI.ORGANIZATION_ID ACTIVITY_ORGANIZATION
, FL6.MEANING WORK_ORDER_TYPE_DISP
, EOMD.WORK_ORDER_TYPE
, FL7.MEANING PLANNER_MAINTENANCE_DISP
, EOMD.PLANNER_MAINTENANCE
, EOMD.FIRM_PLANNED_FLAG
, EOMD.PLAN_MAINTENANCE
, EOMD.NOTIFICATION_REQUIRED
FROM MTL_SYSTEM_ITEMS_VL MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, EAM_ORG_MAINT_DEFAULTS EOMD
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS FL1
, MFG_LOOKUPS FL2
, MFG_LOOKUPS FL3
, MFG_LOOKUPS FL4
, MFG_LOOKUPS FL5
, MFG_LOOKUPS FL6
, MFG_LOOKUPS FL7
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV AG
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = EOMD.ORGANIZATION_ID
AND EAA.ACTIVITY_ASSOCIATION_ID = EOMD.OBJECT_ID
AND EOMD.OBJECT_TYPE = 60
AND EAA.MAINTENANCE_OBJECT_TYPE = 3
AND EAA.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND EOMD.ACTIVITY_CAUSE_CODE = FL2.LOOKUP_CODE(+)
AND FL2.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND EOMD.ACTIVITY_TYPE_CODE = FL3.LOOKUP_CODE(+)
AND FL3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND EOMD.SHUTDOWN_TYPE_CODE = FL4.LOOKUP_CODE(+)
AND FL4.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND EOMD.ACTIVITY_SOURCE_CODE = FL5.LOOKUP_CODE(+)
AND FL5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND EOMD.WORK_ORDER_TYPE = FL6.LOOKUP_CODE(+)
AND FL6.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND EOMD.PLANNER_MAINTENANCE = FL7.LOOKUP_CODE(+)
AND FL7.LOOKUP_TYPE(+) = 'EAM_PLANNER'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+)
AND CII.INVENTORY_ITEM_ID = AG.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = AG.ORGANIZATION_ID UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, (SELECT EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) EAM_ITEM_TYPE
, (SELECT AG.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV AG
WHERE AG.INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) ASSET_REBUILD_GROUP
, EAA.MAINTENANCE_OBJECT_ID INVENTORY_ITEM_ID
, NULL
, NULL
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, FL2.MEANING ACTIVITY_CAUSE
, EOMD.ACTIVITY_CAUSE_CODE
, FL3.MEANING ACTIVITY_TYPE
, EOMD.ACTIVITY_TYPE_CODE
, BD.DEPARTMENT_CODE OWNING_DEPARTMENT
, EOMD.OWNING_DEPARTMENT_ID
, EOMD.TAGGING_REQUIRED_FLAG
, FL4.MEANING SHUTDOWN_TYPE
, EOMD.SHUTDOWN_TYPE_CODE
, EOMD.ACCOUNTING_CLASS_CODE
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, FL5.MEANING ACTIVITY_SOURCE
, EOMD.ACTIVITY_SOURCE_CODE
, NULL
, NULL
, MSI.ORGANIZATION_ID ACTVITY_ORGANIZATION
, FL6.MEANING WORK_ORDER_TYPE_DISP
, EOMD.WORK_ORDER_TYPE
, FL7.MEANING PLANNER_MAINTENANCE_DISP
, EOMD.PLANNER_MAINTENANCE
, EOMD.FIRM_PLANNED_FLAG
, EOMD.PLAN_MAINTENANCE
, EOMD.NOTIFICATION_REQUIRED
FROM MTL_SYSTEM_ITEMS_VL MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, EAM_ORG_MAINT_DEFAULTS EOMD
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS FL1
, MFG_LOOKUPS FL2
, MFG_LOOKUPS FL3
, MFG_LOOKUPS FL4
, MFG_LOOKUPS FL5
, MFG_LOOKUPS FL6
, MFG_LOOKUPS FL7
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND MSI.ORGANIZATION_ID = EOMD.ORGANIZATION_ID
AND EAA.ACTIVITY_ASSOCIATION_ID = EOMD.OBJECT_ID
AND EOMD.OBJECT_TYPE = 40
AND EAA.MAINTENANCE_OBJECT_TYPE = 2
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND EOMD.ACTIVITY_CAUSE_CODE = FL2.LOOKUP_CODE(+)
AND FL2.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND EOMD.ACTIVITY_TYPE_CODE = FL3.LOOKUP_CODE(+)
AND FL3.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND EOMD.SHUTDOWN_TYPE_CODE = FL4.LOOKUP_CODE(+)
AND FL4.LOOKUP_TYPE(+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND EOMD.ACTIVITY_SOURCE_CODE = FL5.LOOKUP_CODE(+)
AND FL5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND EOMD.WORK_ORDER_TYPE = FL6.LOOKUP_CODE(+)
AND FL6.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND EOMD.PLANNER_MAINTENANCE = FL7.LOOKUP_CODE(+)
AND FL7.LOOKUP_TYPE(+) = 'EAM_PLANNER'
AND EOMD.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+) UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, AG.EAM_ITEM_TYPE
, AG.CONCATENATED_SEGMENTS ASSET_REBUILD_GROUP
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_NUMBER
, CII.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL ACTIVITY_CAUSE
, NULL
, NULL ACTIVITY_TYPE
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL ACTIVITY_SOURCE
, NULL
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_START_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_START_DATE
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_END_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_END_DATE
, MSI.ORGANIZATION_ID ACTIVITY_ORGANIZATION
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_SYSTEM_ITEMS_VL MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV AG
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND EAA.MAINTENANCE_OBJECT_TYPE = 3
AND EAA.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND CII.INVENTORY_ITEM_ID = AG.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = AG.ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EOMD.OBJECT_ID = EAA.ACTIVITY_ASSOCIATION_ID
AND EOMD.OBJECT_TYPE = 60 ) UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MSI.ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, MSI.CONCATENATED_SEGMENTS ACTIVITY
, MSI.DESCRIPTION ACTIVITY_DESCRIPTION
, (SELECT EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) EAM_ITEM_TYPE
, (SELECT AG.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV AG
WHERE AG.INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) ASSET_REBUILD_GROUP
, EAA.MAINTENANCE_OBJECT_ID INVENTORY_ITEM_ID
, NULL
, NULL
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL ACTIVITY_CAUSE
, NULL
, NULL ACTIVITY_TYPE
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL SHUTDOWN_TYPE
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL ACTIVITY_SOURCE
, NULL
, NULL
, NULL
, MSI.ORGANIZATION_ID ACTVITY_ORGANIZATION
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_SYSTEM_ITEMS_VL MSI
, MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND EAA.MAINTENANCE_OBJECT_TYPE = 2
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND NOT EXISTS (SELECT 1
FROM EAM_ORG_MAINT_DEFAULTS EOMD
WHERE EOMD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND EOMD.OBJECT_ID = EAA.ACTIVITY_ASSOCIATION_ID
AND EOMD.OBJECT_TYPE = 40 ) UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, (SELECT MSIK.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND ROWNUM = 1 ) ACTIVITY
, (SELECT MSI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_VL MSI
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND ROWNUM = 1 ) ACTIVITY_DESCRIPTION
, AG.EAM_ITEM_TYPE
, AG.CONCATENATED_SEGMENTS ASSET_REBUILD_GROUP
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_NUMBER
, CII.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL
, NULL
, NULL ACTIVITY_TYPE
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL SHUTDOWN_TYPE
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL
, NULL
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_START_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_START_DATE
, EAM_ACTIVITYUTILITIES_PVT.GET_NEXT_SERVICE_END_DATE (EAA.ACTIVITY_ASSOCIATION_ID
, EAA.MAINTENANCE_OBJECT_ID) NEXT_SERVICE_END_DATE
, -1
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV AG
WHERE EAA.MAINTENANCE_OBJECT_TYPE = 3
AND EAA.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID NOT IN (SELECT MSI.ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID )
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND CII.INVENTORY_ITEM_ID = AG.INVENTORY_ITEM_ID
AND CII.LAST_VLD_ORGANIZATION_ID = AG.ORGANIZATION_ID UNION ALL SELECT EAA.ROWID ROW_ID
, EAA.ACTIVITY_ASSOCIATION_ID
, MP.MAINT_ORGANIZATION_ID
, EAA.ASSET_ACTIVITY_ID
, (SELECT MSIK.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND ROWNUM = 1 ) ACTIVITY
, (SELECT MSI.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_VL MSI
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID
AND ROWNUM = 1 ) ACTIVITY_DESCRIPTION
, (SELECT EAM_ITEM_TYPE
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) EAM_ITEM_TYPE
, (SELECT AG.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV AG
WHERE AG.INVENTORY_ITEM_ID = EAA.MAINTENANCE_OBJECT_ID
AND ROWNUM = 1 ) ASSET_REBUILD_GROUP
, EAA.INVENTORY_ITEM_ID
, NULL
, EAA.SERIAL_NUMBER
, EAA.START_DATE_ACTIVE
, EAA.END_DATE_ACTIVE
, FL1.MEANING PRIORITY
, EAA.PRIORITY_CODE
, NULL ACTIVITY_CAUSE
, NULL
, NULL ACTIVITY_TYPE
, NULL
, NULL OWNING_DEPARTMENT
, NULL
, NULL
, NULL SHUTDOWN_TYPE
, NULL
, NULL
, EAA.LAST_UPDATE_DATE
, EAA.LAST_UPDATED_BY
, EAA.CREATION_DATE
, EAA.CREATED_BY
, EAA.LAST_UPDATE_LOGIN
, EAA.ATTRIBUTE_CATEGORY
, EAA.ATTRIBUTE1
, EAA.ATTRIBUTE2
, EAA.ATTRIBUTE3
, EAA.ATTRIBUTE4
, EAA.ATTRIBUTE5
, EAA.ATTRIBUTE6
, EAA.ATTRIBUTE7
, EAA.ATTRIBUTE8
, EAA.ATTRIBUTE9
, EAA.ATTRIBUTE10
, EAA.ATTRIBUTE11
, EAA.ATTRIBUTE12
, EAA.ATTRIBUTE13
, EAA.ATTRIBUTE14
, EAA.ATTRIBUTE15
, EAA.REQUEST_ID
, EAA.PROGRAM_APPLICATION_ID
, EAA.PROGRAM_ID
, EAA.PROGRAM_UPDATE_DATE
, EAA.MAINTENANCE_OBJECT_ID
, EAA.MAINTENANCE_OBJECT_TYPE
, EAA.CREATION_ORGANIZATION_ID
, EAA.TMPL_FLAG TEMPLATE_FLAG
, NULL ACTIVITY_SOURCE
, NULL
, NULL
, NULL
, -1
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_EAM_ASSET_ACTIVITIES EAA
, MFG_LOOKUPS FL1
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_B MSIB
WHERE EAA.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID NOT IN (SELECT MSI.ORGANIZATION_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = EAA.ASSET_ACTIVITY_ID)
AND EAA.MAINTENANCE_OBJECT_TYPE = 2
AND EAA.PRIORITY_CODE = FL1.LOOKUP_CODE(+)
AND FL1.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'