ENG Engineering Change Orders – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Engineering · all Engineering reports
Overview
Engineering change orders (ECOs) with one row per revised item: the ECO header with its type, status, approval status, priority, reason, requestor and dates, and the revised item with its new revision, effectivity, implementation and cancellation dates, use-up item and material disposition. An ECO without revised items is listed once with blank revised item columns.
Change types are those based on the Change Order change category, as on the Engineering Change Orders form.
Show Component Changes adds one row per component the revised item adds, changes or disables. Old Quantity and Old Op Seq come from the component the change replaces.
Open Jobs is the current number of unreleased, released and on hold discrete jobs building the revised item in the ECO’s organization, whatever the ECO’s status. Open Job List shows the first 15 of these jobs by scheduled start date.
Estimated Eng Cost and Estimated Mfg Cost are shown on the first row of an ECO and Open Jobs on the first row of a revised item only, so that they can be summed.
Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.
Report Parameters
Organization Code, ECO, Change Type, ECO Status, Approval Status, Open Only, Revised Item, Effectivity Date From, Effectivity Date To, Show Component Changes, Show DFF Attributes
Oracle EBS Tables Used
hz_parties, hr_all_organization_units_vl, bom_structures_b, mtl_system_items_vl, mtl_parameters, eng_engineering_changes, eng_change_order_types_vl, eng_revised_items, wip_discrete_jobs, wip_entities, org_access_view
Report Categories
Related Reports
INV Item Upload, BOM Bill of Material Structure, CAC Manufacturing Variance, INV Items, INV Item Upload 11i, FND Attached Documents, BOM Routing Upload
Running This SQL Without Blitz Report
The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | ENG Engineering Change Orders 15-Oct-2026 022515.xlsx |
| Blitz Report™ XML Import | ENG_Engineering_Change_Orders.xml |
| SQL Source | ENG Engineering Change Orders.sql |
| Full Report on Enginatics | www.enginatics.com/reports/eng-engineering-change-orders/ |
Case Study & Technical Analysis: ENG Engineering Change Orders
Executive Summary
This report lists engineering change orders (ECOs) with one row per revised item, showing the change header, its type, status and approval state, and for each revised item its new revision, effectivity dates, use-up item and material disposition. Change coordinators, item engineers and planners use it to see which changes are pending, approved or implemented, and their effect on items. It runs in Excel through Blitz Report™, and optionally shows component changes and the open discrete jobs that build each revised item.
Business Challenge
- Change visibility: Engineering changes move through approval and implementation across many items, and the status of each revised item must be visible in one list.
- Effectivity planning: Manufacturing needs the early and scheduled effectivity dates of each revised item to plan the cut-in of new revisions.
- Impact on open work: An ECO that changes an item affects the unreleased, released and on-hold jobs building that item, which planners must identify before the change takes effect.
- Cost tracking: Estimated costs should be reported once per change, not once per revised item.
The Solution
- Lists each ECO with its change type, status, approval status, priority, reason, requestor, assignee and responsible organization.
- Shows each revised item with its new item revision, routing revision, early and scheduled effectivity dates, implementation and cancellation dates, use-up item and disposition.
- Adds one row per component when Show Component Changes is requested, with the old quantity and old operation sequence of the component the change replaces.
- Counts the open jobs for each revised item in the ECO’s organization, and lists the first 15 of them by scheduled start date.
Technical Architecture (High Level)
Primary Tables
ENG_ENGINEERING_CHANGES: the change order header, including change notice, status, approval dates, cost estimates and requestor.ENG_CHANGE_ORDER_TYPES_VL: the change order types, restricted to those based on the Change Order change category.ENG_REVISED_ITEMS: the items revised by each change, with new revisions, effectivity, implementation and disposition.MTL_SYSTEM_ITEMS_VL: the revised item and the use-up item, with their item numbers and descriptions.BOM_STRUCTURES_B: the alternate bill of material designator of a revised item.WIP_DISCRETE_JOBSandWIP_ENTITIES: the discrete jobs building each revised item, with their job names and status types.HZ_PARTIESandHR_ALL_ORGANIZATION_UNITS_VL: requestor, assignee and responsible organization names.MTL_PARAMETERSandORG_ACCESS_VIEW: the organization code and the organizations the responsibility can access.
Logical Relationships
- Each ECO is joined to its change order type, and only types based on the CHANGE_ORDER change category are kept.
- ECOs are outer-joined to their revised items on change notice and organization, so an ECO with no revised items is listed once with blank item columns.
- Each revised item is joined to its item master record in
MTL_SYSTEM_ITEMS_VL, and the open job summary is joined on organization and item. - The open job summary is built from discrete jobs with status types 1, 3 and 6, which are the unreleased, released and on-hold statuses.
- Component changes, when requested, add extra joins and rows within each revised item.
- Results are ordered by organization code, change notice, revised item and revised item sequence.
Parameters & Filtering
- Organization Code, ECO and Change Type: narrow the changes to one organization, one change notice or one change order type.
- ECO Status, Approval Status and Open Only: filter on the state of the change.
- Revised Item: limits the output to ECOs that revise a given item.
- Effectivity Date From and Effectivity Date To: select revised items by effectivity date.
- Show Component Changes and Show DFF Attributes: add the component rows and the descriptive flexfield columns to the output.
Performance & Optimization
- The open job summary is an inline view grouped by organization and item, restricted to the revised items in scope.
- The open job list is built with an aggregate string function that keeps only the first 15 jobs by scheduled start date, which bounds the length of the column.
- Estimated costs use a row-numbering window function, so they appear once per change.
- The organization access view limits the organizations to those of the current responsibility.
FAQ
Q: Why are the estimated costs shown on only one row for each ECO? A: The report numbers each change’s rows and returns the estimated engineering and manufacturing cost only on the first row, so the costs can be summed without counting them once per revised item.
Q: How are open jobs counted for a revised item? A: Open Jobs counts the discrete jobs in the ECO’s organization that build the revised item and are unreleased, released or on hold, whatever the status of the ECO.
Q: Which change types does the report include? A: It includes only change order types based on the Change Order change category, as shown on the Engineering Change Orders form.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH