Search Results material_shortage_disp




Overview
Explore in AI Chat

Overview

EAM_WORK_ORDER_DETAILS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, delivered with the Enterprise Asset Management (EAM) product. It is defined over the EAM_WORK_ORDER_DETAILS table and enriches that base data with user-defined work order status descriptions, human-readable material shortage descriptions, and construction estimate references. The view exists because the underlying work order detail record stores only coded identifiers; the view resolves those codes into operational meaning for display in forms, reports, and downstream integrations.

Two derived behaviors distinguish the view. First, it derives a user-defined work order status through the EAM_WO_STATUSES_V view, and where the PENDING_FLAG is set to "Y" it appends the localized pending suffix obtained from FND_MESSAGE.GET_STRING for the EAM/EAM_PENDING_TEXT message. Second, it translates the MATERIAL_SHORTAGE_FLAG into a display value via the EAM_MATERIAL_SHORTAGE lookup, producing the MATERIAL_SHORTAGE_DISP column. The view is therefore the standard access point for any process that requires work order status and material shortage information in a presentation-ready form.

Underlying Base Objects

The view is constructed from four referenced objects joined in a single SELECT. The driving object is EAM_WORK_ORDER_DETAILS (aliased EWOD), a synonym resolving to the work order details table in the APPS schema. Status resolution is performed against EAM_WO_STATUSES_V (EWSV), a synonym for the EAM work order statuses view, joined on EWSV.STATUS_ID = EWOD.USER_DEFINED_STATUS_ID. Material shortage descriptions are resolved through MFG_LOOKUPS (ML), an Oracle Manufacturing lookups view, using an outer join on LOOKUP_TYPE = 'EAM_MATERIAL_SHORTAGE' where the lookup code equals the material shortage flag and ENABLED_FLAG is "Y". Construction estimate data is supplied by an outer join to EAM_CONSTRUCTION_ESTIMATES (ECE) on ECE.ESTIMATE_ID = EWOD.ESTIMATE_ID. The view text also invokes the FND_MESSAGE package function GET_STRING to retrieve the pending status literal at query time.

Key Columns

Common Use Cases and Queries

Typical usage includes work order status dashboards, material shortage exception reports, maintenance scheduling extracts, and integrations that must present statuses and shortage descriptions as text rather than codes. Because the view already performs status, lookup, and estimate resolution, queries against it require no additional joins to lookups.

Retrieve active status and shortage information for a work order:

SELECT wip_entity_id, organization_id, work_order_status_pending, material_shortage_disp, material_shortage_check_date FROM eam_work_order_details_v WHERE wip_entity_id = :wip_entity_id;

Identify work orders with material shortages:

SELECT wip_entity_id, organization_id, work_order_status, material_shortage_disp FROM eam_work_order_details_v WHERE material_shortage_flag = 'Y' ORDER BY organization_id, wip_entity_id;

Report pending work orders by status:

SELECT work_order_status, COUNT(*) FROM eam_work_order_details_v WHERE pending_flag = 'Y' GROUP BY work_order_status;

Join to construction estimates for planning extracts:

SELECT wip_entity_id, estimate_number, pm_suggested_start_date, pm_suggested_end_date FROM eam_work_order_details_v WHERE estimate_id IS NOT NULL;

Because several joins in the view are outer joins, records are returned even when material shortage lookup values or construction estimates are absent, which suits exception reporting. The dependency on EAM_WO_STATUSES_V and FND_MESSAGE.GET_STRING means the returned status text reflects the currently defined statuses and the installed language at execution time.