This solution implements a bidirectional integration pattern with intelligent error handling, custom validation logic, incremental data extraction, and comprehensive reprocessing capabilities—ensuring data integrity while minimizing manual intervention.
The integration challenge is rarely “just move data.” The real requirement is:
Pull only approved invoices
Validate with enterprise-specific rules and cross-references
Prevent duplicates (idempotency)
Create or update invoices in Fusion
Capture detailed errors centrally
Mark invoices as “exported” in Coupa only after Fusion success. [exported flag handling is optional and depends on whether Coupa is used as the system of record for export state.
]Target Design Overview
Schedule: OIC Scheduled Integration runs every 30 minutes
Coupa selection: only invoices with status = approved and not exported
Validation: stage into ADW → execute PL/SQL validations
Outcome:
- Validation failures → outright rejection (status = E) + error logged + notification sent
- Success → Create/Update invoice in Fusion (status = S)
- After Fusion success → update Coupa invoice to mark exported
Architecture Overview
Key Components
Oracle Integration Cloud (OIC): Orchestration layer managing API calls, data transformations, and error handling
Autonomous Data Warehouse (ADW): Central repository for staging data, cross-reference lookups, custom validations, and audit trails
Coupa Source System: Procurement platform providing approved invoice data via REST APIs
Oracle Fusion Payables: Target ERP system for invoice processing
Notification Layer: Email/Microsoft Teams alerts for validation failures
Integration Flow Diagram
Detailed Component Design
NOTE: The table below summarizes the core components and APIs involved in the integration. The goal is not to document every endpoint, but to illustrate how responsibilities are clearly separated across systems.
OIC Integration Flow Design
Step 1: Scheduled Extraction from Coupa
An OIC Scheduled Integration runs every 30 minutes. Only invoices that meet all three conditions are extracted:
status = approved
exported = false
updated_at > last_successful_run_timestamp
This ensures we pick up:
Newly approved invoices
Corrections to previously approved invoices
No duplicates
Incremental Strategy (Safe & Idempotent)
The integration uses a bookmark-based incremental strategy driven by Coupa’s updated-at timestamp, ensuring reliable, overlap-safe invoice extraction every 30 minutes without missing or duplicating records.
A single control table is maintained in ADW:
INT_CONTROL_BOOKMARK
SOURCE_SYSTEM
BOOKMARK_NAME
- BOOKMARK_VALUE_TS (last_successful_timestamp)
This table stores the point up to which Coupa invoices have already been processed.
On Each Scheduled Run
Read the bookmark
OIC reads the last_successful_timestamp from ADW.Query Coupa incrementally
OIC calls the Coupa Invoice API and retrieves only:Invoices with status = approved
Invoices updated after the last bookmark
updated-at > last_successful_timestamp
Stage invoices in ADW
All returned invoices are inserted into ADW staging tables.Advance the bookmark (safely)
After successful staging, the bookmark is updated using:MAX(updated-at) from staged invoices and Not the current system time
The bookmark moves forward only after data is successfully staged—not when the job starts.
Step 2 — Extract from Coupa (Approved + not exported + incremental)
API/api/invoices?
Operation:GET
Handle pagination (loop until no results).
Use response filters/fields to keep payload lean
Filters applied:
status=approved
exported=false
updated-at[gt]=<bookmark>
Step 3 — Stage into ADW (Header + Lines)
MAP Output of COUPA with ADW Staging tables and Insert into:ADW_INVOICE_HEADER_STG
ADW_INVOICE_LINES_STG
Add control columns:
RUN_ID, BATCH_ID
COUPA_INVOICE_ID (unique key)
STATUS (N=new, E=error, S=validated-success)
ERROR_MESSAGE (short)
Step 4 — Cross-reference mapping (IT maintained)
ADW lookup tables store Coupa → Fusion mappings:
Supplier & Supplier Site
Business Unit
Payment Terms
Tax Codes
Accounts / CCIDs
As per our implementation these cross reference tables don’t have any UI and IT Technical team maintains these mapping.
Step 5 — PL/SQL validation (custom rules)
Call a PL/SQL procedure (example):
COUPA_AP_VAL_PKG.VALIDATE_BATCH(p_run_id => :RUN_ID)
Rules typically include:
Supplier/site mapping exists
Required fields
Distribution/account validation
Duplicate policy rules
Amount math consistency
Outcomes:
Fail → STATUS = 'E' + rejection reason
Pass → STATUS = 'S'
Step 6 — OIC Create vs Update in Fusion
For STATUS='S' invoices:
Search Fusion invoice (idempotency) using your chosen key strategy (invoice number + supplier + BU, or a stored source reference).
If not found → Create Invoice
If found → Update Invoice
Updates are supported as long as Fusion APIs allow them.
CREATE or UPDATE Operation
Call Fusion Invoice API
Build payload from ADW data
Execute POST (create) or PATCH (update)
Handle response
Update ADW with Fusion Response
If successful:
Update FUSION_INVOICE_ID
Update FUSION_INVOICE_NUM
Set PROCESSING_STATUS = 'P' (Pending Coupa Update)
If failed:
Set PROCESSING_STATUS = 'F' (Fusion Error)
Insert into GLOBAL_ERROR table
Step 7 — Global error logging (custom + Fusion)
All errors—both validation and Fusion API errors—are captured in ADW:
GLOBAL_ERROR table includes:
Invoice reference
Integration step
Step 8 — Generate Processing Summary
Create 2 Reports
Summary Report to show count of Success and failure from ADW tables for current Run
For all Invoice with Status = E Print
Coupa Invoice #
Supplier
Error Message
Error Step ( Custom Validation or REST API errors)
Step 9: Notifications for Failures
Whenever validation failures occur:
OIC sends Email / Teams notification
Includes:
Invoice number
Supplier
Error summary
🔔 This enables faster resolution without digging into logs.
Step 10: Mark Invoice as Exported in Coupa
exported flag handling is optional and depends on whether Coupa is used as the system of record for export state.
OIC updates Coupa invoice:
REST API - /api/invoices/:id
Operation : Patch
exported = true
last-exported-at = current timestamp
🚫 No invoice is ever marked exported unless it exists successfully in Fusion.
Why This Integration Design Works
Idempotent by design – safe for retries and restarts
Validation before posting – protects financial integrity
Decoupled architecture – Coupa, Fusion, and OIC evolve independently
Operationally transparent – errors are visible, actionable, and auditable
Bizinsight Consulting has extensive experience designing and implementing Oracle Integration Cloud solutions across Oracle Fusion, Coupa, and legacy ERP platforms. If you are planning a similar integration or struggling with an existing one, we’d be happy to help.
