Building Resilient Oracle PDH Integrations Using Oracle Integration Cloud (OIC): A Real-World Success Story

Building Resilient Oracle PDH Integrations Using Oracle Integration Cloud (OIC): A Real-World Success Story

When customers demand complex, highly customized processes to manage their item master and structures, standard out-of-the-box forms often fall short. In one of our recent implementations, we successfully delivered a smart, scalable, and error-resilient integration between a custom Oracle APEX UI and Oracle Product Hub Cloud (PDH) using Oracle Integration Cloud (OIC).

The Business Need

The customer had an advanced, multi-stage approval process for item and item structure creation. Instead of relying on native Oracle forms, they opted to build a custom front-end UI using Oracle APEX—designed to capture only key data and streamline user interactions.

Behind the scenes, however, the integration logic was far more demanding:

1.      Create Items across Master and Inventory Orgs

2.      Create Item Structures with all their components

3.      Ensure all or none of the structure is created (transactional integrity)

4.      Handle failures and retries gracefully

5.      Automatically clean up partial records from Oracle SaaS

 

Integration Flow – Powered by OIC

Here's how we designed the end-to-end integration leveraging the power of Oracle Integration Cloud:

Step 1: Trigger from APEX UI

·         Once item or structure is created in APEX, the UI triggers a REST endpoint hosted in OIC, passing the Item Number or Structure ID.

·         This ensures a decoupled, event-driven approach from the UI to the backend integration.

Step 2: Retrieve Data from Oracle ATP

·         The OIC integration fetches full item/structure details from ATP (the backend DB for APEX).

·         Additional business and derived values are computed inside OIC, such as:

o   Default templates

o   Descriptive flexfields

o   Org assignments

 

Step 3: Ensure Approval Status

·         The integration validates that the item or structure has passed **internal approvals** before proceeding.

·         If not, OIC logs the issue and exits cleanly.

Step 4: Create Item in Oracle PDH

·         OIC uses REST APIs to create:

o   Items in the **Master Org**

o   Items in associated **Inventory Orgs**

·         Logic includes:

o   Org-based logic branching

o   Error handling for each step

Step 5: Create Item Structure with Transactional Integrity

This was the most critical and complex requirement:

 

Challenge	OIC Solution
Some components could fail while others succeed	Ensure “all-or-none” creation
If any component fails, the structure must be reversed	OIC creates a DELETE_GROUP using Oracle PDH REST APIs
Successfully created components added to DELETE_GROUP	OIC invokes delete execution for that group
ATP must log full failure reasons	OIC updates ATP tables with fault messages for traceability

Step 6: APEX Users Fix & Retry

·         APEX users receive feedback and failure reasons from the ATP table UI.

·         After correcting data, the user can resubmit the request, re-triggering the OIC endpoint.

Highlights of Oracle Integration Cloud Capabilities

 

Capability	How It Was Used
REST Adapter	Exposed REST endpoint to be called from APEX
ATP DB Adapter	Query item/structure data and update logs
Conditional Logic & Orchestration	Validate approval, derive data, control flow based on failures
Error Handling & Recovery	Capture granular errors and update ATP for user visibility
SaaS REST APIs Integration	Create items, item structures, and invoke DELETE_GROUP cleanup
Retry Mechanism	Enable seamless resubmission via APEX for partial failures
Scalability	Decouple frontend (APEX) and backend (PDH/OIC) logic for maintainability

Outcome

• 100% automated flow from APEX UI to PDH and back
• Zero partial failures thanks to structured delete and retry mechanism
• Error visibility for business users through ATP tables and UI messaging
• Significant time saving in item/structure creation vs manual entry