Introduction

One of the marquee features of APEX 26.1 is APEX AI Agents. APEX AI Agents allow you to use an LLM and your own PL/SQL and JS tools to perform actions on your data instead of just chatting with it.

Along with the AI Agents feature came changes to the APEX_AI PL/SQL API that allow you to send attachments to your LLM for analysis.

These two features make it easy to turn business documents, such as Purchase Order PDFs or image files, into JSON that can be used to create transactions in your ERP system.

In this post, I will configure an APEX AI Agent and show you the prompts, JSON schema, and code to turn Purchase Orders into JSON.

The Setup

Pre-Requisites

This post assumes you have already configured and tested a Generative AI Service. I am using OpenAI:

Note: Attachment support depends on the selected AI provider and model. Test your exact document types, sizes, and layouts before designing the workflow around them.

APEX AI Agent Setup

Navigation: Shared Components > Generative AI Agents

• The system prompt provides the agent with operating instructions, rules, and guidelines. For example, in the system prompt, we describe the relationship between a vendor and a customer on a Purchase Order to ensure that the LLM extracts the Customer Information (as it relates to your business) rather than your company information.

• Here is a link to a gist of the complete system prompt.

• There is no need to provide a Welcome Message because we are going to call this agent using the APEX_AI API, ideally from an APEX Automation or Background Page Process, rather than from the APEX Agent Dynamic Action.

• We do not need any agent tools for this example. The agent is being used as a reusable configuration for the system prompt, model settings, attachments, and JSON response format.

• After changing the Response Format - Type to JSON Object, we can provide a JSON Schema.

• The JSON Schema defines the exact structure, data types, required fields, and constraints that an LLM should use when generating or validating JSON output. In the context of an LLM call, it acts as a contract that turns a free-form response into predictable, machine-readable data that downstream code can safely parse and use. Note: The schema improves structural reliability. It does not prove that the extracted business values are correct. You still need downstream validation, ID matching, confidence scoring, and human review for low-confidence cases.

• This significantly improves the chances that we will get a JSON document we can import into our ERP system.

• On Oracle AI Database 26ai, APEX validates the generated JSON object against the agent’s JSON Schema. On earlier database versions, validate the JSON yourself before loading staging tables.

• Here is a link to the gist of the complete JSON schema.

• Take note of the 'Static ID', we will be using this later on.

That is all there is to the Agent Setup!

The Code

Now that the AI Agent is configured, we can call it from PL/SQL using APEX_AI.generate

Table to Store the PO Files

I have a very simple table that stores Purchase Order files:

CREATE TABLE file_blobs (
    id             NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT FILE_BLOBS_PK PRIMARY KEY,
    file_name      VARCHAR2(255) NOT NULL,
    file_mimetype  VARCHAR2(100) NOT NULL,
    file_data      BLOB NOT NULL,
    upload_date    TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT ON NULL CURRENT_TIMESTAMP);

Code to Invoke APEX_AI

DECLARE
  CURSOR cr_file IS
    SELECT file_mimetype, file_data, file_name
    FROM   file_blobs
    WHERE  file_name = 'PO_74810.pdf';
  l_response     CLOB;
  lt_attachments apex_ai.t_attachments;
  lr_attachment  apex_ai.t_attachment;
BEGIN

  -- Get the file mimetype, content and name to process.
  OPEN  cr_file;
  FETCH cr_file 
  INTO  lr_attachment.mime_type,
        lr_attachment.content_blob,
        lr_attachment.file_name;
  CLOSE cr_file;

  -- Set the detail level for the attachment
  lr_attachment.detail_level := apex_ai.c_detail_level_high;
  
  -- Add the attachment to the list of attachments
  lt_attachments := apex_ai.t_attachments();
  lt_attachments.extend;
  lt_attachments(lt_attachments.last) := lr_attachment;

  -- Call the API to return the JSON to l_response
  l_response := apex_ai.generate 
                 (p_prompt          => 'Generate the JSON for this document',
                  p_attachments     => lt_attachments,
                  p_agent_static_id => 'parse-sales-order');
END;

That is just 32 lines of code!

• You can find more information about the PL/SQL record types and attributes in the APEX_AI Constants Section.

• lr_attachment.detail_level

  • For image attachments, detail_level tells the AI provider how much visual detail to use when analyzing the image: low for faster, lower-cost general understanding, high for fine-grained analysis such as screenshots, forms, charts, or small text, and auto to let the provider decide. In OpenAI terms, this maps to the image input detail setting.

Production Considerations

The code above shows the extraction step only. In a production process, you should also consider:

• Validating the returned JSON before loading staging tables.

• Storing the original file, extracted JSON, model name, prompt version, and schema version.

• Handling missing files, unsupported MIME types, provider errors, refusals, and invalid JSON.

• Tracking confidence scores at both the field level and document level.

• Testing against real customer POs, not just clean sample documents.

Sample Output

Sample PO Document

Sample JSON Output

Here is a link to a sample JSON output for the above Purchase Order.

Putting it All Together

Of course, extracting JSON from a Purchase Order is just part of the story. There are several other things we need to do in order to get our Purchase Orders into our ERP. These steps can be orchestrated using APEX Workflow.

1. Ingest the PO Document - For example, we could poll an Office 365 mailbox (e.g., orders@example.com) to fetch new POs. You will need to capture the entire email body as part of the process, as it will be useful during the Human-in-the-Loop review step later.

2. Classify the Email - Depending on the nature of the mailbox, you may also need to classify the inbound email to verify that it is a PO.

3. Parse the Document (this post) - This extracts a clean, consistent JSON document from the PO document.

4. Stage the Document - Once you have a clean JSON file, you should parse it and populate a staging table with the header and lines. Attach the email body, original PDF/image, and the extracted JSON.

5. Identify the key ERP IDs - Before we can import the document, we must determine the customer ID, customer site ID, Item IDs, etc. One approach to identifying customers and addresses is to use a mapping table that maps, for example, the from email address to a customer in our ERP. The approach to finding the IDs is layered. We may want to use the from email address as a limiter, and on top of that, search for the customer's name. For this search, we need to start with an exact, case-insensitive search and then use the fuzzy search algorithm UTL_MATCH for scored fuzzy matching. This is the most complex part of the whole process and the one that requires the most thought.

6. Default Key Attributes - Instead of trying to extract (and match) every minor attribute (e.g., payment terms), you should consider defaulting these values from the customer and/or item. This will reduce code and make your results more consistent.

7. Score the Result - Use the confidence score from the JSON extract, along with the results from ID matching, to generate a final confidence score. Use this to determine if the document requires a human review.

8. Human Review - Provide customer service with a report of ingested Purchase Orders for review. When they select a document, provide a side-by-side view of the PDF/image and an entry form based on the staging table so they can review and complete any missing fields. Also, provide a link to the original email in case it contains helpful information. Once the document is complete, perform a final set of validations before importing it into your system.

Conclusion

APEX 26.1 makes document extraction much easier than before. By combining an AI Agent, attachments, a strong system prompt, and a JSON Schema, we can turn a Purchase Order PDF or image into structured JSON with very little PL/SQL.

That does not mean the full ERP process is automatic. The extraction step still needs to be surrounded by staging tables, ID matching, validation, confidence scoring, and human review. But the hard part has shifted. Instead of writing brittle PDF-parsing code, we can focus on building the business controls that determine when the extracted result is good enough to import.

For APEX developers, this is a very practical AI pattern: use the LLM to convert unstructured documents into structured data, then use APEX, PL/SQL, and Workflow to govern the rest of the process.