Introduction
A product recall is one of the most time-sensitive crises a manufacturing company can face. The ramifications ripple across every corner of the enterprise - procurement, inventory, production, sales, shipping, and your customer install base, all at once.
The challenge isn't just what to do. It's being able to see the full picture fast enough so you can act.
In this post, I'll walk through how we built a single-page dashboard that gives decision-makers an instant, enterprise-wide view of a recalled raw material's impact, so they can act in minutes, not days.
Problem Statement
When a raw material (RM) is flagged for recall or a quality issue surfaces, you need to quickly answer questions like:
Which Bills of Materials (BOMs) reference this RM?
Which Work Orders (WOs), Purchase Orders (POs), and Sales Orders (SOs) need to go on hold?
What Finished Goods (FGs) are at risk. And do any have a substitute RM?
What's already shipped and sitting at customer sites?
What's in-transit or pending receipt?
The problem is that this data lives across every corner of your ERP, and it's tangled like a spider web. Tracing a single RM through open and closed orders, multiple BOM versions, serial numbers, and shipment records is overwhelming.
The Current Reality
Without a dedicated solution, here's the typical workflow in a recall situation:
Work with software developers to write SQL queries and export inventory data, PO status, sales history, shipment details, and customer reach.
Receive the exports, massage them in Excel
Only then can they extract the critical decision criteria and begin to act.
This process takes days. It involves sifting through multiple BOM versions and alternates, open and closed orders across modules, and a massive collection of interrelated data. And because of the time and resource drain on primary decisions (halt operations? notify customers?), there are no resources left for secondary analysis, e.g., understanding the full RM-to-FG impact, identifying all downstream ramifications, or exploring how to prevent this in the future.
Solution Overview
We delivered a one-pane-of-glass dashboard, a single-page APEX app that summarizes the full impact of a recalled raw material across the enterprise.
At the top of the page, key metrics are displayed as clickable badges - On-Hand Quantity, On-Hand in FGs, Shipments, Open Jobs, All Jobs, Open POs and All POs. Each badge is drillable. Click on any one and it takes you directly to the detail - the Work Orders, Purchase Orders, Sales Orders, Shipments or Inventory transactions behind that number. No more hunting across multiple ERP screens or waiting on a developer to pull the data for you.
With this one page summary, a decision-maker can quickly assess the lay of the land and execute: reach out to Sales to communicate customer impact and put WOs, SOs and POs on hold.
Screenshot of on-hand inventory drill down by subinventory for a RM, along with serial number lineage.
Screenshot of on-hand inventory drill down by subinventory for a Finished Good
Screenshot of Recent Shipment Lines Drill-Down
Finished Goods Impact Analysis
Scrolling down the page, the dashboard lists every Finished Good impacted by the recalled RM. For each FG, it highlights whether a substitute (alternate) raw material exists.
- Cells highlighted in red indicate the FG has less than three backup substitutes with enough on-hand inventory to replace the recalled component. This enables a quick decision: prioritize finding an alternate RM or put future orders for that FG on hold.
FG Workbench
From the one-page summary, clicking the FG Workbench button opens a detailed view of a specific Finished Good. Here you can see its real-time status at a glance - open Sales Orders, active Work Orders and on-hand inventory by subinventory.
But the real power is in the BOM drill-down. Click the RM BOM icon next to any FG and a pop-up displays all active BOMs for that Finished Good, along with the real-time on-hand quantities of each component per BOM such as how many units of the RM are on open Jobs and POs, as well as how many are in stock. You can also filter BOMs by status to narrow your view.
From here, if you want to deep-dive into a specific raw material, click the RM Workbench button.
Raw Materials Workbench
The RM Workbench provides a comprehensive, real-time view of the RM:
Captures all transactions for the RM
Identifies every FG made with the RM
Flags any at-risk FGs if the RM were recalled
Displays related and alternate RMs along with their current on-hand inventory and projected availability based on open demand
How do we identify substitute RMs? By leveraging the fact that multiple BOM versions often exist for the same FG, each referencing a different RM. By comparing BOM definitions, we can surface the substitute items without having to perform a manual search.
How We Built It
Building this solution came down to two things:
Mapping the business logic to the ERP data model
Reusing views and functions and leveraging APEX Collections for performance and maintainability
1. Mapped Business Logic to the ERP
Before writing a single line of SQL, we mapped out the full business algorithm. For a potential recalled RM, the business logic flows like this:
Lookback Period is a user-selected parameter (1, 2, or 5 years) that controls how far back the queries reach across WOs, SOs, PO receipts, and inventory transactions.
đź’ˇ
Note: these queries reference Oracle ERP base tables across OM, INV, PO, and WSH (Shipping). If you don't have deep familiarity with these tables, budget extra time for testing. Even with 30+ years of Oracle techno-functional experience, we encountered tables with fields that appeared to contain the data we needed but didn't , e.g., serial numbers being a prime example. You have to test exhaustively against real scenarios to confirm the right table is returning the right data.
2. We Built for Reuse and Performance
We utilized three patterns to improve reusability and performance:
Database Views - complex queries were written once and referenced across multiple pages and reports. When a business rule changes, you only update it in one place. (For a deeper dive, check out Jon Dixon's post on the importance of using DB views in APEX.)
PL/SQL Functions - Instead of duplicating the same calculations and logic across multiple queries and views, we wrote the SQL once as a function and called it wherever needed.
APEX Collections - used to stage and share data across pages within a session, reducing need to re-run expensive queries on every page load.
Could AI Have Built This?
Honestly, not yet. And here's why.
The hard part of this solution wasn't writing the SQL. It was knowing which tables and columns to pull from in the first place. An Oracle ERP schema can have thousands of tables. Even seasoned developers spend significant time hunting for the right table - the one that actually populates the field you need, not just the one that has the field.
In one test case, we were tracking a serial number through the shipment tables. The table we initially queried had a serial number column, but it never populated. We could see the serial number in the ERP UI, so we knew it was captured somewhere. It just took several iterations to find the right table.
AI faces the same challenge. Without knowing which column from which table to reference, a generated query may look correct but return inaccurate results. The validation burden doesn't go away, it just shifts. You still need someone who understands the business scenarios, knows what "correct" looks like, and can test exhaustively to prove the solution right.
One more thought worth noting: the reason this solution works is because the underlying data exists to query in the first place. Years of consistently capturing transactions in the ERP (POs, WOs, SOs, shipments, serial numbers, etc.), built the data foundation that makes real-time decision-making possible.
As Accenture CEO Julie Sweet noted recently:
“over 90% of data clean-up that companies have, when you look across the globe, is still to come.” Julie Sweet, Accenture CEO, Jan 20, 2026.
The companies investing in their data foundation today are the ones who will be able to act fast when it matters most. Do not be part of this 90%.
Conclusion
The queries are straightforward. What's hard, and what I hope you took away from this post, is knowing which tables in the ERP database hold the data you need, and that knowledge only comes from experience and exhaustive, iterative testing. Even our developer, with 30+ years of Oracle ERP expertise, had to try multiple tables before finding ones that actually returned the serial numbers and transaction details the business needed.
What used to take days of manual SQL exports, Excel manipulation and cross-referencing now takes minutes on a single APEX dashboard. Decision-makers can assess the full impact of a recalled raw material across Procurement, Inventory, Production, Sales, Shipping and their Customer Install Base.
If your enterprise handles raw materials, components or any product with a supply chain, I hope I have inspired you to build a similar impact analysis application.
As always, reach out and let me know how it goes. I'd love to hear about your use case and help.
