Integrating the MedScope Acquisition: Order Bridge & Data Migration
Kept sales running during an acquisition by bridging two companies' order systems, then migrated the acquired company onto Medical Guardian's platform.
Medical Guardian (personal emergency response devices and monitoring) acquired MedScope. MedScope ran Microsoft Dynamics CRM on SQL Server; Medical Guardian ran an Oracle database with a PHP (CodeIgniter) website and order jobs, Salesforce, and Oracle APEX for device-related monitoring.
THE CHALLENGE
Inventory and fulfilment moved to Medical Guardian immediately after the acquisition, but MedScope's sales team had to keep selling in their own system. Every MedScope order had to reach Medical Guardian's warehouse without delays, losses or duplicates, and be visible to Medical Guardian teams in Salesforce.
WHAT I BUILT
Scheduled sync jobs that picked up new orders from MedScope's Dynamics CRM database
A PL/SQL ETL / staging layer in Medical Guardian's Oracle database that validated and mapped each order
Order creation in Medical Guardian, flowing to the warehouse for dispatch
Order visibility in Salesforce for the Medical Guardian team
Data migration of all MedScope records into Medical Guardian: customers, orders and device records
RELIABILITY
Duplicate protection
Each order keyed on its MedScope order ID, so re-runs never create duplicate orders or shipments.
Product-code mapping
A mapping table translating MedScope SKUs to Medical Guardian SKUs.
Validation and error list
Orders that could not be processed went to an error list for manual fix and reprocessing.
Reconciliation
Checks comparing order counts between the two systems.
ARCHITECTURE · AS BUILT
As built: scheduled jobs and a PL/SQL staging layer in Medical Guardian’s Oracle database.
OIC RE-ARCHITECTURE · DESIGNED AFTERWARDS
Proposed design · not deployed at Medical Guardian
After the project, I designed how this integration would look on Oracle Integration Cloud as a long-term, centrally monitored replacement for the scheduled jobs.
Proposed design: not built or deployed at Medical Guardian.
Order intake integration: app-driven trigger from Dynamics (scheduled polling as a fallback), validation, lookups for SKU and status codes, upsert into Medical Guardian staging via the Database adapter and Connectivity Agent, then a call to the existing order-creation PL/SQL.
Fulfilment integration: Medical Guardian order to the warehouse (REST or file).
Status-back integration: shipped status to Salesforce and Dynamics (upsert on external ID).
Customer sync: matching and upsert to avoid duplicate customers.
Fault handling: scope and global fault handlers; retry for technical errors; no retry for business errors, which go to an error queue with notification; resubmission from OIC monitoring; a daily reconciliation integration.
Rollout: parallel run with the existing jobs, compare counts, cut over, then decommission.
TRADE-OFF
OIC adds central monitoring, pre-built adapters and resubmission, but has licensing and setup cost. For a temporary acquisition bridge, well-built scheduled jobs can be the pragmatic choice.
SKILLS SHOWN
Acquisition integrationPL/SQL ETLData migrationCross-system order flowsSalesforce and Dynamics CRM dataIntegration reliability designOracle Integration Cloud architecture