Building an Automated Invoice Processing Application in Oracle APEX Using OCI Document Understanding

 

Introduction

Manual invoice processing is time-consuming and error-prone. With the power of Oracle Cloud Infrastructure (OCI) AI Services, developers can automate document processing and extract key information such as:

  • Invoice Number

  • Vendor Name

  • Invoice Date

  • Total Amount

In this article, we will build a solution that integrates Oracle APEX with OCI Document Understanding to automatically extract invoice data from uploaded PDF files.

The workflow will:

  1. Upload invoice from APEX

  2. Store the document in OCI Object Storage

  3. Send the document to OCI Document Understanding API

  4. Extract invoice fields using AI Key-Value extraction

  5. Store the extracted results in database tables

Architecture Overview

The solution architecture consists of the following components:

1️⃣ Oracle APEX Application
2️⃣ OCI Object Storage (to store uploaded invoices)
3️⃣ OCI Document Understanding AI Service
4️⃣ Oracle Database tables for storing results

Flow:

User Uploads Invoice (APEX)
        │
        ▼
Upload File → OCI Object Storage
        │
        ▼
OCI Document Understanding API
        │
        ▼
AI extracts key-value fields
        │
        ▼
Store results in Oracle tables

Step by Step Approaches:
Step 1: Create Database Tables
First, create a table to store uploaded invoices and extracted values.
Response Table
CREATE TABLE DOCAI_RESPONSE (
    ID               NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    DOCUMENT_ID      NUMBER,
    FIELD_TYPE_CODE  VARCHAR2(50),
    FIELD_LABEL      VARCHAR2(100),
    LABEL_SCORE      NUMBER,
    FIELD_VALUE      VARCHAR2(1000),
    CREATED_DATE     DATE DEFAULT SYSDATE
);
This table will store the key-value fields extracted from the invoice.

Step 2: Create OCI Object Storage Bucket
In OCI Console:
Navigate to:
Storage → Object Storage & Archive storage → Buckets


Create a new bucket:
Example:
Bucket Name: invoice-bucket
Namespace: <your_namespace>
Region : us-chicago-1

Step 3: Configure Web Credentials in APEX
Navigate to:
Workspace Utilities

→ Web Credentials
→ Create Credential
Configuration:
Authentication Type:
OCI Native Authentication
Provide:
  • OCI User OCID
  • Tenancy OCID
  • Private Key
  • Fingerprint
This credential will allow APEX to securely call OCI APIs.

Step 4: Configure REST Data Source Credentials in Oracle APEX
Navigate to:
Shared Components
→ REST Data Sources
→ Create REST Data Source
Base URL:
https://document.aiservice.us-chicago-1.oci.oraclecloud.com/20221109
URL Pattern:
/processorActions/analyzeDocument
HTTP Method:
POST
REST Data Source 
Provide:
  • Name
  • REST Data Source Type
  • Remote Server
  • URL Path Prefix
  • Credentials
Authentication: We use the web credentials created above.


{
"compartmentId" : "#COMPARTMENT_ID#",
"document" :
    {
        "namespaceName" : "#NAMESPACE_NAME#",
        "bucketName" : "#BUCKET_NAME#",
        "objectName" : "#OBJECT_NAME#",
        "source" : "OBJECT_STORAGE"
    },
"features" :
    [
        {
            "featureType" : "#FEATURE_TYPE#"
        }
    ]
}




Step 5: Upload File to OCI Object Storage
Create a PL/SQL procedure to upload the invoice file to OCI Object Storage.
Under the Processing tab, create a Process to invoke the PL/SQL Procedure using the Invoke API Process Type.
  1. Process Type: Invoke API
  2. Settings > Type: PL/SQL Procedure or Function 
  3. Procedure or Function: Select UPLOAD_FILE
  4. The UPLOAD_FILE Procedure possesses a single input parameter and three output parameters. Please select the parameter and assign suitable Page Items from your form. For more details, refer to the PL/SQL Procedure code below


 CREATE OR REPLACE PROCEDURE UPLOAD_FILE (
    P_FILE_CONTENT IN VARCHAR2,
    P_STATIC_ID IN VARCHAR2,
    P_NAMESPACE IN VARCHAR2,
    P_BUCKET IN VARCHAR2,
    P_REGION IN VARCHAR2,
    P_OBJECT_STORAGE_URL OUT VARCHAR2,
    P_FILE_NAME OUT VARCHAR2,
    P_MIME_TYPE OUT VARCHAR2
) IS
    L_OBJECT BLOB;
    L_RESPONSE CLOB;
BEGIN

SELECT
    BLOB_CONTENT,
    FILENAME,
    MIME_TYPE
INTO
    L_OBJECT,
    P_FILE_NAME,
    P_MIME_TYPE
FROM
    APEX_APPLICATION_TEMP_FILES
WHERE
    NAME = P_FILE_CONTENT;

P_OBJECT_STORAGE_URL :=
'https://objectstorage.'||P_REGION||
'.oraclecloud.com/n/'||P_NAMESPACE||
'/b/'||P_BUCKET||
'/o/'||APEX_UTIL.URL_ENCODE(P_FILE_NAME);

APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := P_MIME_TYPE;

L_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    P_URL => P_OBJECT_STORAGE_URL,
    P_HTTP_METHOD => 'PUT',
    P_BODY_BLOB => L_OBJECT,
    P_CREDENTIAL_STATIC_ID => P_STATIC_ID
);

END;
    P_FILE_CONTENT IN VARCHAR2,
    P_STATIC_ID IN VARCHAR2,
    P_NAMESPACE IN VARCHAR2,
    P_BUCKET IN VARCHAR2,
    P_REGION IN VARCHAR2,
    P_OBJECT_STORAGE_URL OUT VARCHAR2,
    P_FILE_NAME OUT VARCHAR2,
    P_MIME_TYPE OUT VARCHAR2
) IS
    L_OBJECT BLOB;
    L_RESPONSE CLOB;
BEGIN
SELECT
    BLOB_CONTENT,
    FILENAME,
    MIME_TYPE
INTO
    L_OBJECT,
    P_FILE_NAME,
    P_MIME_TYPE
FROM
    APEX_APPLICATION_TEMP_FILES
WHERE
    NAME = P_FILE_CONTENT;
P_OBJECT_STORAGE_URL :=
'https://objectstorage.'||P_REGION||
'.oraclecloud.com/n/'||P_NAMESPACE||
'/b/'||P_BUCKET||
'/o/'||APEX_UTIL.URL_ENCODE(P_FILE_NAME);
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := P_MIME_TYPE;
L_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    P_URL => P_OBJECT_STORAGE_URL,
    P_HTTP_METHOD => 'PUT',
    P_BODY_BLOB => L_OBJECT,
    P_CREDENTIAL_STATIC_ID => P_STATIC_ID
);
END;
    P_FILE_CONTENT IN VARCHAR2,
    P_STATIC_ID IN VARCHAR2,
    P_NAMESPACE IN VARCHAR2,
    P_BUCKET IN VARCHAR2,
    P_REGION IN VARCHAR2,
    P_OBJECT_STORAGE_URL OUT VARCHAR2,
    P_FILE_NAME OUT VARCHAR2,
    P_MIME_TYPE OUT VARCHAR2
) IS
    L_OBJECT BLOB;
    L_RESPONSE CLOB;
BEGIN
SELECT
    BLOB_CONTENT,
    FILENAME,
    MIME_TYPE
INTO
    L_OBJECT,
    P_FILE_NAME,
    P_MIME_TYPE
FROM
    APEX_APPLICATION_TEMP_FILES
WHERE
    NAME = P_FILE_CONTENT;
P_OBJECT_STORAGE_URL :=
'https://objectstorage.'||P_REGION||
'.oraclecloud.com/n/'||P_NAMESPACE||
'/b/'||P_BUCKET||
'/o/'||APEX_UTIL.URL_ENCODE(P_FILE_NAME);
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := P_MIME_TYPE;
L_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    P_URL => P_OBJECT_STORAGE_URL,
    P_HTTP_METHOD => 'PUT',
    P_BODY_BLOB => L_OBJECT,
    P_CREDENTIAL_STATIC_ID => P_STATIC_ID
);
END;

This procedure uploads the invoice file from APEX to OCI Object Storage.

Step 6: Configuring the Automatic Row Processing to Insert a New Record into the Table
Once the file has been uploaded to the object storage and the relevant values such as filename, mime_type, and object_storage_url have been obtained, the next step is to set up our page with a page process to handle the form
  1. Under the Processing tab, create a new Page Process below the Invoke API Process we created earlier.
    1. Process Type: Form – Automatic Row Processing (DML).
    2. Form Region: Invoice Upload
  2. Make sure that you have selected Database Action as SQL INSERT action while creating the Process Document button.

Step 7: Invoke the OCI Document Understanding REST Data Source through a Page Process
  1. Create the second Process called “Invoke REST Data Source” with the following attributes:
    • Process Type: Invoke API
    • Settings > Type: REST Source
    • Rest Source: Document Understanding API (Select the REST Data Source created above).

Step 8: Parse the AI Response
Create a third process, parse the response, which can now parse the response stored on page Item P1_RESPONSE, and update the database table with the tags returned from the OCI AI Document Understanding Service. 
INSERT INTO DOCAI_RESPONSE
        (
            document_id,
            field_type_code,
            field_label,
            label_score,
            field_value
        )
SELECT :P1_ID,
field_type_code,
field_label,
label_score,
field_value
FROM   JSON_TABLE(:P1_RESPONSE, '$.pages[*]'
        COLUMNS (page_number       NUMBER        PATH '$.pageNumber',
                NESTED PATH '$.documentFields[*]' COLUMNS
                (field_type_code VARCHAR2(50)   PATH '$.fieldType',
                    field_label     VARCHAR2(100)  PATH '$.fieldLabel.name',
                    label_score     NUMBER         PATH '$.fieldLabel.confidence',
                    field_value     VARCHAR2(1000) PATH '$.fieldValue.value'
                    ))) jt
WHERE  jt.field_type_code = 'KEY_VALUE';


Step 9: Display Extracted Invoice Details
Once processed, the application displays:
  • Invoice Number
  • Vendor Name
  • Invoice Date
  • Total Amount
These values can then be used for:
  • Accounts payable automation
  • Invoice validation
  • ERP integrations

The following screenshots display the final results in the Oracle APEX UI after processing the invoice.






Final Result
Users can now upload invoices through Oracle APEX, and the system will automatically extract important invoice data using OCI Document Understanding AI.
This eliminates manual data entry and significantly improves processing efficiency.
Conclusion
Integrating Oracle APEX with OCI AI Services opens powerful possibilities for document automation.
By leveraging OCI Document Understanding, developers can build intelligent applications capable of:
  • Invoice processing
  • Document classification
  • Form data extraction
  • Automated workflows
This integration demonstrates how Oracle's cloud ecosystem enables developers to quickly build AI-powered enterprise applications with minimal effort.

Comments

Post a Comment

Popular posts from this blog

Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL

Image
📘 Introduction Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS). In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing. In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication. 🎯 Objective The goal of this implementation is to: • Expose database data through REST APIs • Create structured API endpoints • Secure APIs using OAuth • Enable controlled external access 🧱 Prerequisites Before starting, ensure: • Oracle APEX 24.2 is installed • ORDS 20.x or later is configured • RESTful Services are enabled • A working table exists (Example: HR.EMPLOYEES) 🌐 REST API URL Structure The generated API follows this format: https://host:port/ords/schema/module/template ⚙️ Implementat...
Read more

Building a Portfolio Generator in Oracle APEX: A Step-by-Step Guide to Generate Downloadable Portfolio Documents

Image
  Introduction This tutorial demonstrates how to build a Portfolio Generator application in Oracle APEX where users can enter resume details and generate a downloadable portfolio HTML/ PDF with one click . The application collects user details, work experience, skills, and education, then dynamically generates a styled HTML portfolio that can be printed as PDF . Step- by- Step  Implementation  Guide Step 1 -  Create Database Tables First create the tables required to store resume information. We will create 5 tables . 1️⃣ Resume Master Table Stores the main profile information.   CREATE TABLE "RESUME_MASTER"     ( "RESUME_ID" NUMBER GENERATED ALWAYS AS IDENTITY, "FULL_NAME" VARCHAR2(200),  "EMAIL" VARCHAR2(200),  "PHONE" VARCHAR2(50),  "ADDRESS" VARCHAR2(500),  "DOB" DATE,  "OBJECTIVE" CLOB,  "CURRENT_COMPANY" VARCHAR2(200),  "CURRENT_ROLE" VARCHAR2(200),  "C...
Read more

Implementing WhatsApp OTP Verification in Oracle APEX Using UltraMsg API

Image
  🚀 Introduction In today’s digital applications, user verification plays a crucial role in ensuring security and trust. Traditionally, OTPs are sent via SMS — but with the widespread use of WhatsApp, sending OTPs through WhatsApp offers: Faster delivery Better user reach Improved engagement In this blog, we explore how to implement OTP verification in Oracle APEX by integrating WhatsApp messaging using an external API service. 🎯 Objective The goal of this implementation was to: ✔️ Generate OTP dynamically ✔️ Send OTP via WhatsApp ✔️ Integrate external messaging API ✔️ Enhance login/verification security ✔️ Enable real-time communication 🧩 Solution Overview This solution integrates: PL/SQL OTP generation REST API call from Oracle APEX WhatsApp messaging service This allows Oracle APEX applications to send OTP directly to users via WhatsApp instead of SMS.  🧱 Step-by-Step Approach  Step 1: Configure WhatsApp API To send OTP via WhatsApp, ...
Read more