Search Results wip_eam_direct_items_v




Overview
Explore in AI Chat

Overview

WIP_EAM_DIRECT_ITEMS_V is an APPS-owned database view in the Work in Process (WIP) product that exposes the direct items charged against Enterprise Asset Management (EAM) work orders. Direct items are purchased materials or services that are associated with a specific EAM work order and operation rather than being issued from inventory. The view consolidates requisition-sourced and purchase-order-sourced detail so that a single record set describes what was requested, what was ordered, and how much has been delivered.

The view is a UNION of two query branches. The first branch reads requisition lines and requisition headers joined to line types, units of measure, vendors, and WIP_EAM_DIRECT_ITEMS. The second branch aggregates purchase order distributions to derive ordered and delivered quantities, using PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL, and PO_REQ_DISTRIBUTIONS_ALL. Because the view is a reporting and inquiry object, it supports EAM maintenance screens, Work Order costing, and ad-hoc reconciliation of direct procurement activity to work orders. Column AMOUNT_DELIVERED, which was the search term, is exposed via a TO_NUMBER(NULL) placeholder in the requisition branch and is populated in the purchase-order branch from the aggregated SUM(PD.QUANTITY_DELIVERED) expression.

Underlying Base Objects

The documented base objects are MTL_UNITS_OF_MEASURE, PO_DISTRIBUTIONS_ALL, PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_LINE_TYPES, PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL, PO_REQ_DISTRIBUTIONS_ALL, PO_VENDORS (itself a view), and WIP_EAM_DIRECT_ITEMS. All Purchasing and MTL objects are referenced through APPS synonyms, and PO_VENDORS is a view.

The first UNION branch joins PO_REQUISITION_LINES_ALL to PO_REQUISITION_HEADERS_ALL on REQUISITION_HEADER_ID, to PO_LINE_TYPES on LINE_TYPE_ID, to MTL_UNITS_OF_MEASURE on UNIT_MEAS_LOOKUP_CODE, and outer-joins PO_VENDORS on VENDOR_ID. It is filtered to line-location-less requisition lines whose destination type is SHOP FLOOR and whose WIP_ENTITY_ID is not null, excluding cancelled, rejected, or system-saved requisitions and outside-operation line types. WIP_EAM_DIRECT_ITEMS is outer-joined on ITEM_DESCRIPTION, WIP_ENTITY_ID, WIP_OPERATION_SEQ_NUM, and DESTINATION_ORGANIZATION_ID, contributing DIRECT_ITEM_SEQUENCE_ID. The second branch brings in PO_DISTRIBUTIONS_ALL, PO_HEADERS_ALL, PO_LINES_ALL and PO_LINE_LOCATIONS_ALL to aggregate quantity ordered and quantity delivered per distribution.

Key Columns

  • WIP_ENTITY_ID, WIP_OPERATION_SEQ_NUM - identify the EAM work order and operation that consumed the direct item.
  • DESTINATION_ORGANIZATION_ID - the inventory organization receiving the item.
  • SEGMENT1 - carries the requisition number in the first branch and the purchase order number in the second.
  • ITEM_DESCRIPTION, ITEM_ID, CATEGORY_ID - describe the purchased item.
  • UOM_CODE, UNIT_PRICE, CURRENCY_CODE, QUANTITY - pricing and quantity in the requisition/PO line.
  • QUANTITY_DELIVERED (aggregated) - the received quantity from PO distributions; this drives the AMOUNT_DELIVERED value.
  • AMOUNT and AMOUNT_DELIVERED - the requisition line amount and the delivered amount; AMOUNT_DELIVERED is null in the requisition branch and derived in the PO branch.
  • VENDOR_NAME - supplier name from PO_VENDORS.
  • REQUISITION_HEADER_ID, REQUISITION_LINE_ID, PO_HEADER_ID - document cross-references.
  • AUTHORIZATION_STATUS, CLOSED_CODE, ORDER_TYPE_LOOKUP_CODE - workflow and lifecycle status indicators.

Common Use Cases and Queries

Typical uses include reporting direct-item spend on EAM work orders, verifying that a requisition was converted to a purchase order, and reconciling delivered quantities against ordered quantities.

Query direct items for a work order:

SELECT wip_entity_id, wip_operation_seq_num, item_description,
       quantity, amount, amount_delivered, vendor_name
FROM   apps.wip_eam_direct_items_v
WHERE  wip_entity_id = :p_wip_entity_id;

Query delivered amounts for a specific item:

SELECT segment1, item_description, quantity, amount_delivered
FROM   apps.wip_eam_direct_items_v
WHERE  item_id = :p_item_id
AND    amount_delivered IS NOT NULL;

Summarize direct-item spend by organization:

SELECT destination_organization_id, SUM(amount) total_amount,
       SUM(amount_delivered) total_delivered
FROM   apps.wip_eam_direct_items_v
GROUP  BY destination_organization_id;

Because AMOUNT_DELIVERED is null for requisition-only rows, queries should filter on it when only delivered purchase activity is required, or use NVL where the complete picture including outstanding requisitions is needed.