Results for “eam_service_request_details_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EAM_SERVICE_REQUEST_DETAILS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Enterprise Asset Management (EAM) product family and is described in the ETRM documentation simply as "Service request details." The view furnishes a denormalized, read-only projection of maintenance-related service requests — internally stored as incidents in the Oracle Service (CS) schema — together with the status, severity, item, inventory organization, and operating unit context required for operational and analytical reporting. Because it joins transactional incident data to descriptive lookups and organization definitions, it removes much of the complexity that a direct query against the CS incident tables would otherwise require.

Underlying Base Objects

The documented view text defines EAM_SERVICE_REQUEST_DETAILS_V over the following objects: CS_INCIDENTS_ALL_B (aliased CIA), ORG_ORGANIZATION_DEFINITIONS (OOD), CS_INCIDENT_STATUSES_B (CIS), CS_INCIDENT_SEVERITIES_B (CISE), and HR_ORGANIZATION_UNITS (HOU). The 12.2.2 ETRM metadata additionally lists BOM_DEPARTMENTS, CSI_ITEM_INSTANCES, CS_INCIDENTS_VL_SEC, CS_INCIDENT_TYPES_VL_SEC, and MTL_SYSTEM_ITEMS_B_KFV among the referenced base objects.

Joins are established on INCIDENT_STATUS_ID and INCIDENT_SEVERITY_ID to the CS lookup tables, and organization resolution is performed through ORG_ORGANIZATION_DEFINITIONS matched either by ORG_ID to OPERATING_UNIT or by ORGANIZATION_ID to INV_ORGANIZATION_ID, with HR_ORGANIZATION_UNITS supplying the truncated operating unit name. A critical filter is applied: NVL(CIS.CLOSE_FLAG,'N') <> 'Y', so only service requests whose status is not flagged as closed are exposed through this view. The query also uses SELECT DISTINCT to eliminate duplicate rows arising from the organization join.

Key Columns

• INCIDENT_ID / INCIDENT_NUMBER — Primary identifier and user-visible number of the service request.
• INCIDENT_STATUS_ID / STATUS — Status identifier and the corresponding decoded status name (e.g., Open, In Progress). Closed statuses are excluded by the view definition.
• INCIDENT_SEVERITY_ID / SEVERITY — Severity identifier and decoded severity name drawn from CS_INCIDENT_SEVERITIES_B, the lookup the user referenced in their search.
• ORG_ID / OPERATING_UNIT — Operating unit context of the service request.
• INV_ORGANIZATION_ID — Inventory organization linked to the incident, used for item resolution.
• INVENTORY_ITEM_ID / ITEM_NAME — Item identifier and the concatenated item name returned by EAM_COMMON_UTILITIES_PVT.GET_ITEM_NAME, invoked with the incident, org, and inventory organization identifiers.
• OPERATING_UNIT_NAME — The first 30 characters of the HR organization name for the operating unit.

Common Use Cases and Queries

Typical usage includes dashboards of open service requests by severity, item-level reliability reporting for maintained assets, and integration extracts that feed external maintenance systems. A representative query filters for high-severity open requests within an inventory organization:

• SELECT incident_number, status, severity, item_name, operating_unit_name FROM apps.eam_service_request_details_v WHERE inventory_item_id = :item_id ORDER BY severity;
• SELECT severity, COUNT(*) FROM apps.eam_service_request_details_v WHERE org_id = :org_id GROUP BY severity;

Because the view calls EAM_COMMON_UTILITIES_PVT.GET_ITEM_NAME per row, large unfiltered extracts can be costly; restricting by item, organization, or operating unit is advisable. Note also that closure status filtering is baked into the view, so historical closed requests must be sourced from CS_INCIDENTS_ALL_B directly.