MNT_WO_EXCEPTIONS_V

Details

• Schema: FUSION

• Object owner: MNT

• Object type: VIEW

Columns

Name
ISSUE_ID SUPPORTED_ENTITY_ID EXCEPTION_COMPONENT_ID EXCEPTION_COMPONENT_NAME EXCEPTION_COMPONENT_ORG_ID RESOURCE_ID RESOURCE_NAME WORK_AREA_ID WORK_AREA_NAME WORK_CENTER_ID WORK_CENTER_NAME RESOURCE_INSTANCE_ID EXCEPTION_ON_OBJECT_NAME ORGANIZATION_ID SUPPORTED_ENTITY_ID_DUMMY

Query

SQL_Statement

SELECT A.Issue_id, B.supported_entity_id, B.exception_component_id, B.exception_component_name, B.exception_component_org_id, B.resource_id, B.resource_name, B.work_area_id, B.work_area_name, B.work_center_id, B.work_center_name, B.resource_instance_id, B.Exception_On_Object_Name, A.organization_id, '1002' as SUPPORTED_ENTITY_ID_Dummy FROM enq_issues_b A LEFT OUTER JOIN (SELECT eaov.object_id AS issue_id, eaov.supported_entity_id, esiv.inventory_item_id AS exception_component_id, esiv.item_number As exception_component_name, esiv.organization_id AS exception_component_org_id, NULL AS resource_id, NULL AS resource_name, NULL AS work_area_id, NULL AS work_area_name, NULL AS work_center_id, NULL AS work_center_name, NULL AS resource_instance_id, esiv.item_number as Exception_On_Object_Name FROM enq_affected_objects_b eaov, egp_system_items_vl esiv WHERE eaov.supported_entity_id = 1004 AND eaov.pk1_value = esiv.inventory_item_id (+) AND eaov.pk2_value = esiv.organization_id (+) UNION ALL SELECT eaov.object_id AS issue_id, eaov.supported_entity_id, NULL AS exception_component_id, NULL AS exception_component_name, NULL AS exception_component_org_id, wrv.resource_id AS resource_id, wrv.resource_name AS resource_name, NULL AS work_area_id, NULL AS work_area_name, NULL AS work_center_id, NULL AS work_center_name, eaov.pk3_value AS resource_instance_id, wrv.resource_name as Exception_On_Object_Name FROM enq_affected_objects_b eaov, wis_resources_vl wrv WHERE eaov.supported_entity_id = 1003 AND eaov.pk1_value = wrv.resource_id (+) UNION ALL SELECT eaov.object_id AS issue_id, eaov.supported_entity_id, NULL AS exception_component_id, NULL AS exception_component_name, NULL AS exception_component_org_id, NULL AS resource_id, NULL AS resource_name, wwav.work_area_id AS work_area_id, wwav.work_area_name AS work_area_name, NULL AS work_center_id, NULL AS work_center_name, NULL AS resource_instance_id, wwav.work_area_name as Exception_On_Object_Name FROM enq_affected_objects_b eaov, wis_work_areas_vl wwav WHERE eaov.supported_entity_id = 1005 AND eaov.pk1_value = wwav.work_area_id (+) UNION ALL SELECT eaov.object_id AS issue_id, eaov.supported_entity_id, NULL AS exception_component_id, NULL AS exception_component_name, NULL AS exception_component_org_id, NULL AS resource_id, NULL AS resource_name, NULL AS work_area_id, NULL AS work_area_name, wwcv.work_center_id AS work_center_id, wwcv.work_center_name AS work_center_name, NULL AS resource_instance_id, wwcv.work_center_name as Exception_On_Object_Name FROM enq_affected_objects_b eaov, wis_work_centers_vl wwcv WHERE eaov.supported_entity_id = 1006 AND eaov.pk1_value = wwcv.work_center_id (+)) B ON A.issue_id = B.issue_id WHERE A.source='ORA_MAINTENANCE' AND A.quality_type_id IN (SELECT quality_type_id FROM enq_quality_types_b WHERE source = 'ORA_MAINTENANCE')