Search Results work_request_owning_dept

Overview

The APPS.WIP_EAM_WORK_REQUESTS_V view is a denormalized reporting construct within the Oracle E-Business Suite Work in Process (WIP) module, specifically supporting Enterprise Asset Management (EAM) maintenance work request processing. It consolidates work request header information that is otherwise distributed across transactional and reference tables, joining each work request to its associated maintenance asset instance, organization maintenance defaults, location data, and lookup-decoded status, priority, and type descriptions. Because the base WIP_EAM_WORK_REQUESTS table stores several foreign keys as numeric identifiers, this view resolves those identifiers into meaningful values by joining to MFG_LOOKUPS three times (aliases LV1, LV2, LV3) and to BOM_DEPARTMENTS. The view is defined and owned by the APPS schema and is marked VALID in the ETRM metadata for Release 12.2.2, with equivalent behavior in 12.1.1. Its principal role is to support reporting, inquiry forms, and integration extracts that require human-readable work request context, including the EXPECTED_RESOLUTION_DATE column frequently searched by implementers.

Underlying Base Objects

The view is defined over eight documented base objects. WIP_EAM_WORK_REQUESTS is the driving table, holding the work request record itself. It is joined to CSI_ITEM_INSTANCES on MAINTENANCE_OBJECT_ID = INSTANCE_ID, constrained by MAINTENANCE_OBJECT_TYPE = 3, so that each row maps to a specific maintained asset instance. WIP_ENTITIES is outer-joined on both WIP_ENTITY_ID and ORGANIZATION_ID, linking the request to its discrete work order where one exists. EAM_ORG_MAINT_DEFAULTS and MTL_EAM_LOCATIONS are outer-joined to supply the asset's location codes and area identifiers; the defaults join is scoped by organization. BOM_DEPARTMENTS is outer-joined on DEPARTMENT_ID to resolve the owning department. Finally, MFG_LOOKUPS is joined three times, each outer-joined, to translate WORK_REQUEST_STATUS_ID (lookup type WIP_EAM_WORK_REQ_STATUS), WORK_REQUEST_PRIORITY_ID (WIP_EAM_ACTIVITY_PRIORITY), and WORK_REQUEST_TYPE_ID (WIP_EAM_WORK_REQ_TYPE) into their meanings. Nearly all joins are outer joins, ensuring work requests are not silently dropped when optional related data is absent.

Key Columns

• WORK_REQUEST_ID, WORK_REQUEST_NUMBER, DESCRIPTION — primary identifier and descriptive attributes of the work request.
• EXPECTED_RESOLUTION_DATE — the target date by which the maintenance work request is expected to be resolved; central to scheduling and overdue reporting.
• WORK_REQUEST_STATUS, WORK_REQUEST_PRIORITY, WORK_REQUEST_TYPE — decoded meanings from MFG_LOOKUPS.
• INSTANCE_NUMBER, INSTANCE_DESCRIPTION, INVENTORY_ITEM_ID, CATEGORY_ID — identifying the maintained asset.
• ORGANIZATION_ID, WIP_ENTITY_ID, WIP_ENTITY_NAME — organization and associated discrete job.
• WORK_REQUEST_OWNING_DEPT, DEPARTMENT_CODE — owning department and its code.
• AREA_ID, LOCATION_CODES — maintenance location from the EAM defaults.
• WORK_REQUEST_AUTO_APPROVE, WORK_REQUEST_CREATED_BY — control and provenance attributes.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — descriptive flexfield columns.

Common Use Cases and Queries

Typical usage includes overdue work request reporting, asset maintenance dashboards, and integration extracts. A common query filters on status and resolution date:

• SELECT WORK_REQUEST_NUMBER, INSTANCE_NUMBER, WORK_REQUEST_STATUS, EXPECTED_RESOLUTION_DATE FROM APPS.WIP_EAM_WORK_REQUESTS_V WHERE EXPECTED_RESOLUTION_DATE < SYSDATE AND WORK_REQUEST_STATUS NOT IN ('CLOSED','CANCELLED');
• Priority-based workload analysis: SELECT WORK_REQUEST_PRIORITY, COUNT(*) FROM APPS.WIP_EAM_WORK_REQUESTS_V GROUP BY WORK_REQUEST_PRIORITY;
• Asset history: SELECT WORK_REQUEST_NUMBER, WIP_ENTITY_NAME, EXPECTED_RESOLUTION_DATE FROM APPS.WIP_EAM_WORK_REQUESTS_V WHERE INSTANCE_NUMBER = :instance;

Because the view uses ROWID and outer joins, it is generally read-only and intended for querying rather than DML. Reporting should join against it rather than the base table when decoded meanings and asset context are required.