Back to portfolio
Oracle EBS · ERP Migration · European Distribution
⚡ Project BRIDGE
Business Redesign & Integration for Distribution Growth in Europe
Full BA engagement simulation · Toys & Hobby sector · 12 countries · Oracle EBS ERP
Oracle EBS
Order-to-Cash
Procure-to-Pay
EDI X12
SQL / PL-SQL
BPMN 2.0
3PL Integration
Change Management
| Author | Zahra · Business Analyst |
| Sector | Toys & Hobby / EU Distribution |
| Target ERP | Oracle E-Business Suite |
| Scope | 12 countries · 5 EBS modules |
| Status | Portfolio · Fictional data |
12gaps identified
8P1 critical
5Oracle EBS modules
3.5d→0shipment → invoice lag
95%→20%manual data entry
0%→85%automated AP reconciliation
01
Context & Diagnosis
Our client is a European distributor of toys and hobby products, operating in 12 countries, with revenue of €320M. The company runs on a legacy ERP disconnected from its 3PL warehouse. The entire order-to-cash and procure-to-pay flow relies on manual data entry, Excel exports, and email-based reconciliation between teams.
Issues Identified
| Issue | Operational Impact | Estimated Cost |
|---|---|---|
| ERP and 3PL WMS fully disconnected | 100% manual double entry on every shipment | ~18 FTE/year |
| No real-time stock visibility | Undetected stockouts, frequent overselling | ~€2.4M/year |
| Invoicing delayed by 3.5 days on average | Degraded cash flow, customer penalties | ~€340K/year |
| AP accounting reconciliation done in Excel | Month-end close delayed by 5 days, high error risk | ~6 FTE/year |
| 95% of orders entered manually | 48-hour processing lag, 12% error rate | ~€900K/year |
| No supplier portal | POs sent by email, no tracking possible | ~4 FTE/year |
Oracle EBS Objectives — Post-migration Targets
- Shipment → invoice lag: 0 days (automated)
- ERP/WMS stock discrepancy: < 1%
- Orders via EDI/portal: > 80%
- Automated AP reconciliation: ≥ 85%
- Month-end close: Day+2 (vs Day+7)
Oracle EBS Modules Covered
OM · Order Management
INV · Inventory
AR · Accounts Receivable
AP · Accounts Payable
PO · Purchasing
Stakeholders
| Stakeholder | Role | Involvement |
|---|---|---|
| Europe CIO | Project Sponsor | Decision-making, budget |
| Supply Chain Director | Lead Business Owner | Process validation |
| Head of Accounting | Finance Business Owner | AP/AR specs |
| 3PL Provider | Technical Party | EDI / WMS API |
| Oracle Partner | Implementation Partner | EBS configuration |
| Zahra ★ | Business Analyst | End-to-end |
Deliverable 01
AS-IS Mapping — Order-to-Cash & Procure-to-Pay
Elicitation conducted through 14 workshops (key stakeholder interviews + on-site observation). Mapped to the BPMN 2.0 standard. Steps in red represent identified pain points.
Order-to-Cash (OTC) Flow — Current State
AS-IS Order-to-Cash Process · Manual Flow
Customer
Places order (email / fax)
→
Receives acknowledgment
→
Awaits delivery
→
Receives invoice
Sales / Order Desk
Re-keys order into ERP (+48h)
→
Checks stock manually
→
Confirms to customer
→
Sends pick slip by email to 3PL
3PL (Warehouse)
Picks order
→
Ships
→
Sends shipment notice by email
→
No real-time stock update
Billing / AR
Receives 3PL email, re-keys into ERP
→
Creates invoice manually (+3.5d lag)
→
Sends invoice to customer
→
Manual dunning (Excel)
Accounting
Manual AR reconciliation (Excel)
→
Close delayed to Day+7
Procure-to-Pay (P2P) Flow — Current State
AS-IS Procure-to-Pay Process · Manual Flow
Buyer
Identifies replenishment need (Excel)
→
Creates PO manually in ERP
→
Sends PO to supplier by email
Supplier
Acknowledges by email
→
Ships goods
→
Sends invoice (PDF by email)
3PL (Receiving)
Receives goods
→
Sends ASN by email (no EDI)
→
Manual stock update (+24h)
AP Accounting
3-way matching (PO/GRN/Invoice) in Excel
→
Manual validation (high error risk)
→
Supplier payment
Deliverable 02
Gap Analysis Matrix
12 gaps identified through AS-IS/TO-BE analysis. Each gap is tracked with a unique identifier and backed by SQL evidence demonstrating the discrepancy in the data.
| ID | Process | Gap Description | AS-IS Impact | TO-BE Oracle EBS Solution | Priority | Module |
|---|---|---|---|---|---|---|
| OTC-01 | OTC | Manual invoicing trigger after 3PL shipment | +3.5 days lag, ~€340K cash flow impact | Auto-invoice via ASN EDI 856 → Oracle AR AutoInvoice | P1 CRITICAL | AR / OM |
| OTC-02 | OTC | No ERP/WMS integration — stock not synchronized | 20% discrepancy, overselling, undetected stockouts | WMS REST API ↔ Oracle INV (updated every 15 min) | P1 CRITICAL | INV |
| OTC-03 | OTC | Orders received by email/fax — no EDI | 95% manual entry, 12% error rate, +48h lag | EDI X12 850 order portal + Oracle OM mapping | P1 CRITICAL | OM |
| OTC-04 | OTC | No automated credit check at order entry | Shipments to customers over their credit limit, unpaid invoices | Oracle Credit Management — automatic hold on limit breach | P1 CRITICAL | AR / OM |
| OTC-05 | OTC | 100% manual AR dunning (phone + email) | Degraded collection rate, ~6 FTE/year on dunning | Oracle Collections — automated Day+30/60/90 dunning workflow | P2 MAJOR | AR |
| OTC-06 | OTC | No customer visibility into order status | Support call volume +40%, degraded customer satisfaction | Oracle iStore / customer portal with real-time tracking | P2 MAJOR | OM |
| P2P-01 | P2P | 3-way matching (PO/GRN/Invoice) done manually in Excel | 0% automated, 8 FTE/year, high fraud and error risk | Oracle AP AutoMatch — configured 3-way matching + tolerance | P1 CRITICAL | AP / PO |
| P2P-02 | P2P | POs sent by email — no supplier EDI | No traceability, 72h confirmation lag, frequent errors | EDI X12 850/855 + Oracle iSupplier portal | P1 CRITICAL | PO |
| P2P-03 | P2P | Replenishment triggered manually (Excel analysis) | Frequent stockouts or overstock, no MRP | Oracle Min/Max Planning + standard INV MRP | P1 CRITICAL | INV / PO |
| P2P-04 | P2P | 3PL ASN sent by email — +24h stock update lag | Outdated inventory data, DRP not feasible | EDI X12 856 (ASN) → Oracle INV MSCA auto receiving | P1 CRITICAL | INV |
| CC-01 | FINANCE | Month-end close at Day+7 — manual reconciliations | Delayed financial reporting, no real-time visibility for management | Oracle GL with AutoPost + automated intercompany reconciliation | P2 MAJOR | AR / AP / GL |
| CC-02 | FINANCE | Multi-country VAT not configured — manual calculations | Tax compliance risk across 12 European VAT regimes | Oracle Tax Engine — VAT matrices by country/category | P2 MAJOR | AR / AP |
SQL Evidence — Gap OTC-01: invoicing lag
-- Measure the average shipment → invoicing lag (AS-IS)
SELECT
wsh.name AS shipment_number,
wnd.actual_departure_date AS ship_date,
ra.trx_date AS invoice_date,
ROUND(ra.trx_date - wnd.actual_departure_date, 1)
AS delay_days
FROM wsh_delivery_details wsh
JOIN wsh_new_deliveries wnd ON wnd.delivery_id = wsh.delivery_id
JOIN oe_order_lines_all oel ON oel.line_id = wsh.source_line_id
JOIN ra_customer_trx_all ra ON ra.attribute1 = wnd.name
WHERE wnd.actual_departure_date IS NOT NULL
AND ra.trx_date IS NOT NULL
AND wnd.actual_departure_date >= TRUNC(SYSDATE-90)
ORDER BY delay_days DESC;
-- Aggregate result: average lag = 3.52 days · Max = 8 days
SELECT
wsh.name AS shipment_number,
wnd.actual_departure_date AS ship_date,
ra.trx_date AS invoice_date,
ROUND(ra.trx_date - wnd.actual_departure_date, 1)
AS delay_days
FROM wsh_delivery_details wsh
JOIN wsh_new_deliveries wnd ON wnd.delivery_id = wsh.delivery_id
JOIN oe_order_lines_all oel ON oel.line_id = wsh.source_line_id
JOIN ra_customer_trx_all ra ON ra.attribute1 = wnd.name
WHERE wnd.actual_departure_date IS NOT NULL
AND ra.trx_date IS NOT NULL
AND wnd.actual_departure_date >= TRUNC(SYSDATE-90)
ORDER BY delay_days DESC;
-- Aggregate result: average lag = 3.52 days · Max = 8 days
SQL Evidence — Gap P2P-01: AP matching not automated
-- Identify invoices without 3-way matching (AS-IS)
SELECT
aia.invoice_num AS invoice_number,
aia.invoice_date AS invoice_date,
aia.invoice_amount AS amount,
poh.segment1 AS po_number,
rcv.quantity_received AS received_qty,
aila.quantity_invoiced AS invoiced_qty,
CASE WHEN aila.match_status_flag IS NULL
THEN 'UNMATCHED - Manual Review'
ELSE 'AUTO-MATCHED' END AS match_status
FROM ap_invoices_all aia
JOIN ap_invoice_lines_all alia ON aila.invoice_id = aia.invoice_id
LEFT JOIN po_headers_all poh ON poh.po_header_id = aila.po_header_id
LEFT JOIN rcv_transactions rcv ON rcv.po_line_id = aila.po_line_id
WHERE aila.match_status_flag IS NULL
AND aia.invoice_date >= ADD_MONTHS(SYSDATE, -3);
-- Result: 100% of invoices unmatched → handled in Excel
SELECT
aia.invoice_num AS invoice_number,
aia.invoice_date AS invoice_date,
aia.invoice_amount AS amount,
poh.segment1 AS po_number,
rcv.quantity_received AS received_qty,
aila.quantity_invoiced AS invoiced_qty,
CASE WHEN aila.match_status_flag IS NULL
THEN 'UNMATCHED - Manual Review'
ELSE 'AUTO-MATCHED' END AS match_status
FROM ap_invoices_all aia
JOIN ap_invoice_lines_all alia ON aila.invoice_id = aia.invoice_id
LEFT JOIN po_headers_all poh ON poh.po_header_id = aila.po_header_id
LEFT JOIN rcv_transactions rcv ON rcv.po_line_id = aila.po_line_id
WHERE aila.match_status_flag IS NULL
AND aia.invoice_date >= ADD_MONTHS(SYSDATE, -3);
-- Result: 100% of invoices unmatched → handled in Excel
Deliverable 03
Functional Specifications — Oracle INV & AP
Oracle INV Configuration — Inventory Management
| EBS Parameter | Configured Value | Justification |
|---|---|---|
| Inventory organization | 1 org / 3PL warehouse per country | Multi-country legal segregation |
| Lot control | Enabled (Lot mandatory) | Traceability for CE toy products |
| Unit of measure | PCS / CARTON / PALLET | 3PL logistics management |
| Standard cost | Standard Costing enabled | Consolidated financial reporting |
| Min/Max Planning | Enabled per SKU | Replaces manual Excel MRP |
| MSCA transactions | Auto EDI 856 receiving | Eliminates manual 3PL data entry |
| Cycle Count | Automated monthly ABC | Improves stock accuracy (target <1%) |
Oracle AP Configuration — Accounts Payable
| EBS Parameter | Configured Value | Justification |
|---|---|---|
| 3-Way Matching | Mandatory for PO > €500 | Internal control / audit |
| Matching tolerance | ±2% quantity / ±€50 amount | Reduce false positives |
| Payment Terms | 30d / 60d / 90d by supplier | Aligned with purchasing contracts |
| AutoApproval | Enabled if 3-way OK + tolerance OK | 85% auto-approval target |
| Hold Rules | Price variance > 5%, Qty variance > 2% | Automatic escalation to purchasing |
| Tax Engine | Oracle Tax — 12 EU regimes | Multi-country VAT compliance |
| Electronic Invoicing | EDI X12 810 + PDF OCR fallback | Reduced manual processing |
ERP Business Rules
| ID | Business Rule | Process | Priority |
|---|---|---|---|
| RG-OTC-01 | Any shipment confirmed by EDI 856 automatically triggers Oracle AR invoicing within 30 minutes | OTC | P1 CRITICAL |
| RG-OTC-02 | Customer credit check mandatory at order entry — automatic hold on limit breach | OTC | P1 CRITICAL |
| RG-OTC-03 | ERP/WMS stock discrepancy > 1% → automatic alert to Supply Chain Manager within 2 hours | OTC | P1 CRITICAL |
| RG-OTC-04 | EDI 850 orders received without an existing PO → automatic rejection + buyer notification within 15 min | OTC | P2 MAJOR |
| RG-P2P-01 | Supplier invoice exceeding ±2% quantity or ±5% price vs. PO → automatic hold + buyer escalation | P2P | P1 CRITICAL |
| RG-P2P-02 | Stock level < Min reorder point → automatic PO creation via Oracle MRP within 4 business hours | P2P | P1 CRITICAL |
| RG-P2P-03 | PO auto-created by MRP requires manager approval if amount > €10,000 | P2P | P2 MAJOR |
| RG-CC-01 | Automated Oracle GL month-end close on the 2nd business day following period end | Finance | P2 MAJOR |
Deliverable 04
Technical Design — SQL, EDI X12 & REST API
TO-BE Integration Architecture
🏭
Oracle EBS
OM / INV / AR / AP / PO — central ERP📦
WMS 3PL
Warehouse Management — REST API synchronization🔗
EDI X12
850/855/856/810 — suppliers and 3PL🌐
iSupplier Portal
Oracle iSupplier — supplier web portal📊
Oracle BI
Real-time supply chain & finance reporting⚙️
Oracle SOA / MFT
Middleware — EDI/API flow orchestrationTO-BE Integration Flow — Order-to-Cash
1
Customer → EDI X12 850 (Purchase Order)
Customer order received via EDI, validated by Oracle SOA and automatically loaded into Oracle OM
2
Oracle OM → Automatic Credit Check <30s
AR balance + customer credit limit check — automatic hold on breach (RG-OTC-02)
3
Oracle INV → Real-Time Stock Check <15s
ATP (Available-to-Promise) reservation with WMS data synchronized every 15 min
4
Oracle OM → 3PL WMS via REST API <1 min
Pick order transmitted automatically — no more manual email
5
3PL → EDI X12 856 (ASN) → Oracle AR <30 min
Shipment notice automatically triggers Oracle AutoInvoice — immediate invoicing
6
Oracle AR → Automatic Reconciliation + BI real-time
Customer payment automatically reconciled — Oracle Collections dunning if overdue
REST API Design — WMS ↔ Oracle INV
| Method | Endpoint | Trigger | Oracle Target |
|---|---|---|---|
| POST | /inventory/receipt | 3PL receiving | INV MSCA Receipt |
| POST | /inventory/shipment | 3PL shipment | INV Misc Issue + AR AutoInvoice |
| GET | /inventory/stock/{item_id} | OM ATP check | INV On-Hand Quantity |
| PUT | /inventory/cycle-count | Physical count | INV Cycle Count |
| POST | /orders/pick-release | Oracle pick order | WMS Pick List |
| GET | /orders/{order_id}/status | Customer portal | OM Order Status |
EDI X12 Messages — Mapping Matrix
| EDI X12 | Description | Direction | Oracle Target |
|---|---|---|---|
| 850 | Purchase Order (customer order) | Customer → ERP | Oracle OM Order Import |
| 855 | PO Acknowledgment | ERP → Customer | Oracle OM Workflow |
| 856 | Ship Notice / ASN | 3PL → ERP | Oracle INV + AR AutoInvoice |
| 810 | Invoice (supplier invoice) | Supplier → ERP | Oracle AP Invoice Interface |
| 820 | Payment Order / Remittance | ERP → Supplier | Oracle AP Payment |
| 940 | Warehouse Shipping Order | ERP → 3PL | Oracle OM → WMS |
Deliverable 05
UAT Test Plan & L3 Incident Log
UAT Scenarios — Functional Coverage
| ID | Scenario | Gap Covered | Status |
|---|---|---|---|
| UAT-OTC-01 | EDI 850 receipt → Oracle OM order creation | OTC-03 | PASSED |
| UAT-OTC-02 | Automatic credit check → hold on limit breach | OTC-04 | PASSED |
| UAT-OTC-03 | ASN EDI 856 → AR AutoInvoice < 30 min | OTC-01 | PASSED |
| UAT-OTC-04 | WMS ↔ Oracle INV stock sync every 15 min | OTC-02 | FAILED → L3 |
| UAT-OTC-05 | Oracle Collections dunning — Day+30 reminder | OTC-05 | PASSED |
| UAT-OTC-06 | Customer portal — real-time order tracking | OTC-06 | IN PROGRESS |
| UAT-P2P-01 | Automatic AP 3-way matching < €500 tolerance | P2P-01 | PASSED |
| UAT-P2P-02 | Automatic MRP PO when stock < reorder point | P2P-03 | PASSED |
| UAT-P2P-03 | Supplier EDI 850 → Oracle PO confirmation | P2P-02 | FAILED → L3 |
| UAT-P2P-04 | 3PL ASN EDI 856 → auto Oracle INV receiving | P2P-04 | IN PROGRESS |
| UAT-CC-01 | Automatic Oracle GL close at Day+2 | CC-01 | PASSED |
| UAT-CC-02 | Tax Engine — automatic VAT calculation for 12 countries | CC-02 | IN PROGRESS |
L3 Incident Log — UAT
| INC-ID | Description | Severity | Root Cause | Resolution |
|---|---|---|---|---|
| INC-001 | INV/WMS sync — +3% stock delta after nightly batch | HIGH | UTC vs CET timezone mismatch in REST API — 11pm transactions not included | SOA middleware patch — UTC normalization + batch replay · RESOLVED |
| INC-002 | Supplier EDI 850 — German special characters (ü,ö,ä) rejected | HIGH | ISO-8859-1 encoding on DE supplier side vs UTF-8 expected by Oracle | Encoding conversion in SOA XSLT transformer · RESOLVED |
| INC-003 | AR AutoInvoice — 2% of invoices missing transaction number | MEDIUM | Oracle RA_CUSTOMER_TRX_S sequence not initialized on 3 orgs | SQL script to initialize sequences + reprocessing · RESOLVED |
| INC-004 | 3-way matching — price tolerance ignored on multi-line invoices | HIGH | Oracle AP configuration bug — tolerance applied at total level, not line level | Oracle Support SR opened · Patch 34821066 pending · IN PROGRESS |
| INC-005 | Tax Engine — 23% Polish VAT calculated at 8% on toy products | MEDIUM | Oracle tax category incorrectly mapped to PL product family | Oracle Tax matrix correction + recalculation of affected batches · RESOLVED |
UAT Summary:
7Scenarios PASSED
2Failures → L3
3In progress
83%Pass rate
Deliverable 06
Change Management & Communication Plan
Deployment Timeline — 6 Phases
Phase 1 · Weeks 1–4
Discovery & Elicitation
14 elicitation workshops · BPMN AS-IS mapping · key user interviews · pain point diagnosis · project kick-off
Phase 2 · Weeks 5–8
Gap Analysis & TO-BE Design
12 gaps documented · Oracle EBS functional specifications · SQL/EDI/API technical design · business owner sign-off
Phase 3 · Weeks 9–14
Configuration & Integrations
Oracle INV/AP/AR/OM/PO configuration · EDI X12 interface development · WMS API · unit & integration testing
Phase 4 · Weeks 15–18
UAT & Pilot Training
12 UAT scenarios · L3 incident log · key user training across 12 countries · pilot go-live support (France + Germany)
Phase 5 · Weeks 19–24
Phased Rollout (Remaining 10 Countries)
Rollout in waves of 2–3 countries · 4-week hypercare per wave · post-deployment KPI measurement
Phase 6 · Weeks 25–28
Stabilization & Ongoing Support
Support model handover · configuration tuning · Oracle BI dashboard · project retrospective · lessons learned
Communication Plan by Audience
| Audience | Key Message | Channel | Frequency | Owner |
|---|---|---|---|---|
| Executive Committee | Progress, risks, budget, ROI | Monthly steering committee | Monthly | BA + PMO |
| Supply Chain Managers | Process impact, training schedule | Workshop + email | Bi-monthly | BA |
| Sales / Purchasing Teams | New Oracle EBS procedures | Training + guides | By phase | BA + Change Manager |
| Accounting (12 countries) | New AP/AR flows, close dates | Webinar + manual | Before go-live | BA + Finance |
| 3PL (warehouse) | New EDI flows, REST API | Technical workshop | Phase 3 | BA + Tech Lead |
| Suppliers (top 30) | iSupplier portal, EDI X12 | Email + training | Phase 3–4 | BA + Purchasing |
BA Competencies Demonstrated
Elicitation & process mapping (BPMN)Advanced
Functional gap analysisAdvanced
Oracle EBS ERP specifications (INV, AP, AR, OM, PO)Advanced
SQL & EDI X12 / REST API integrationsAdvanced
Testing & L3 support (UAT, incidents)Advanced
Change management & multi-country communicationAdvanced
Project management (milestones, risks, RACI)Advanced
08
Results Summary — AS-IS vs Oracle EBS Targets
| Metric | AS-IS Value | Oracle EBS Target | Delta |
|---|---|---|---|
| Shipment → invoice lag | 3.5 days | 0 days (auto) | −3.5 days |
| ERP/WMS stock discrepancy rate | ~20% | < 1% | −19 pts |
| Orders entered manually | ~95% | < 20% (EDI + portal) | −75 pts |
| Automated AP reconciliation | 0% | ≥ 85% | +85 pts |
| Month-end close | Day+7 | Day+2 | −5 days |
| FTEs allocated to manual tasks | ~28 FTE/year | < 8 FTE/year | −20 FTE |
| Estimated ROI (year 1 post-deployment) | — | ~€4.2M | Savings + gains |
The 6 Deliverables Produced
Deliverable 01
🗺 AS-IS Mapping — OTC & P2P
- 14 elicitation workshops · BPMN 2.0 notation
- Pain points identified and quantified
Deliverable 02
📊 Gap Analysis Matrix — 12 gaps, scoring, SQL proof
- 8 P1 Critical gaps · 4 P2 Major gaps
- SQL evidence for each data discrepancy
Deliverable 03
📋 Functional Specifications — Oracle INV & AP
- 8 business rules · detailed configuration across 5 EBS modules
Deliverable 04
🔧 Technical Design — SQL, EDI X12, REST API
- 6 EDI flows · 6 API endpoints · 2 advanced SQL queries
Deliverable 05
🧪 UAT Test Plan + L3 Incident Log
- 12 UAT scenarios · 5 incidents documented and resolved
Deliverable 06
📣 Change Management & Communication Plan
- 6 phases · 6 audiences · training plan across 12 countries
Author's note: Project BRIDGE is a portfolio deliverable simulating an end-to-end BA engagement in the European toy distribution sector. All names, data, figures, and scenarios are fictional and created for professional demonstration purposes. This project demonstrates mastery of the full BA lifecycle across a multi-module, multi-country Oracle EBS ERP scope, from functional discovery through change management.
Want to know more?
Check out my other projects or get in touch directly.