Streamlining Item & Structure Management: Oracle APEX + OIC + Oracle PDH Integration

Introduction

In many organizations, Product Data Hub (PDH) serves as the single source of truth for product master data. However, business users often need simplified, custom UIs to create or maintain items and their structures — without navigating the complexity of PDH’s native UI.

In this implementation, we combined Oracle APEX, Oracle Integration Cloud (OIC), and Oracle PDH REST APIs to create a seamless, reliable workflow for item and structure creation/updates.

Business Requirement

• Custom UI in Oracle APEX for creating/updating:

  • Items

  • Item Structures

• Real-time integration with Oracle PDH

• Robust error handling with feedback stored in APEX

• Support for both create and update requests

• Granular record processing (one record at a time to isolate failures)

High-Level Architecture

Actors:

• APEX Application → Front-end data entry

• Oracle ATP (Autonomous Transaction Processing) → Backend DB for APEX

• Oracle Integration Cloud (OIC) → Orchestration and API integration layer

• Oracle PDH (Product Data Hub) → Final system of record

Architecture Flow Diagram

1. User Action in APEX

   • User creates or updates an Item or Structure

   • Record is inserted into an APEX backend table with a status_flag = 'N' (New)

2. OIC Trigger

   • OIC integration is invoked (either on-demand or scheduled)

   • Reads one unprocessed record at a time from the APEX table

3. Business Logic in OIC

   • Determine request type:

     • Create New Item

     • Update Existing Item

     • Create New Structure

     • Update Existing Structure

   • Route flow accordingly using OIC orchestration

4. Calling PDH REST APIs

   • Use PDH REST endpoints to:

     • Create/Update Item

     • Create/Update Structure

   • Map APEX table fields to PDH payload structure

5. Response Handling

• If success → Update status_flag = 'S' in APEX table

• If error → Update status_flag = 'E' and store error message for user review

OIC Orchestration Details

Integration Type: Orchestration (Application-driven or Scheduled)

Key Steps:

1. Read Next Record from APEX

   • OIC uses ATP Database Adapter to query 1 row with status_flag = 'N'

2. Identify Action

   • Conditional branches:

     • IF Request Type = ITEM_CREATE → Call PDH Item Create API

     • IF Request Type = ITEM_UPDATE → Call PDH Item Update API

     • IF Request Type = STRUCTURE_CREATE → Call PDH Structure Create API to Process Items/Components  as well Structure item

     • IF Request Type = STRUCTURE_UPDATE → Call PDH Structure Update API to Process Items/Components as well as structure item

3. Invoke PDH REST API

   • Use REST Adapter with proper authentication

4. Update Status

   • Success → Set status_flag = 'S'in ATP database ( APEX or CUSTOM Application)

   • Failure → Set status_flag = 'E' and store error_message (APEX or CUSTOM application)

5. Loop Until No Records Left

   • Repeat until all new records are processed

Error Handling Approach

Benefits of the Solution

• User-Friendly → APEX UI is much easier for business users than native PDH forms.
• Decoupled Architecture → OIC handles all PDH API logic; APEX only stores and forwards requests.
• Resilient → One-record-at-a-time processing ensures no mass failure due to one bad record.
• Traceable → status_flag and error messages give full transparency to users and support team.
• Extensible → New request types (e.g., Item Attribute Updates) can be added easily.