#1151 - OIC Logistics Adapter (OTM) & OTM Data Export Agent

Introduction

I looked at this adapter some time ago, but like everything, if you don't use it, you forget it.

OTM is very function rich and I don't pretend to be an SME. So, as a neophyte, how do I integrate with this app? Answer - we use the OIC Logistics adapter.

But, before we start, let's check out the OTM Integration documentation - 

Here's a screenshot that succinctly describes the integration touchpoints -

You will need an OTM user with the INTEGRATION role, this will be used in the OIC connection definition. We will do this later and then create an integration that leverages the connection. The integration will publish a new location to OTM.

As you can see from the diagram above, OTM can also invoke OIC - OTM connection as OIC Trigger -

From the OTM docs - Sending data from Transportation and Global Trade Management Cloud to another system via OIC can be done by calling the OIC Web Service for the desired integration in OIC. 

More about that later.

Finally, there are pre-built recipes available in OIC for OTM; check them out here - 

Create the OTM Connection in OIC

As you can see, one needs a wsdl url and username / password.

The WSDLs can be retrieved from within OTM - 

The Transmission Service is used to publish data to OTM - 

I copy the Transmission Service URL and use it in my connection definition.

I create an app driven integration that will publish a new location to OTM - 

I use a REST trigger with the following request payload - 

I now add the OTM connection - 

I map as follows - 

I test in the mapper and see the following -

looks good!

Now I run the integration - 

Note the TransactionCode needs to be one of the following - 

I check the integration log - 

Note the Transmission number - 

I can check this in OTM - 

I can find the exact error in the OTM Log - 

I run the integration again - 

this time with a valid domain. I check the Transmission - 

Create Contact Example

Here's the Payload I use - 

{

  "fname": "Renate",

  "lname": "Hasselbacher",

  "email": "r.h@oracle.com"

}

I also created a lookup for common values, this will be leveraged by the mapper in this integration - define once, use many times - change easily, without having to touch the integrations.

I run the integration and get the following transmission number returned - 1964767

I check in OTM - 

I search for my new contact - and there she is - 

Bulk Export from OTM

Check out the OTM REST apis for bulk export -

I begin, as usual, by getting this to work in Postman - 

You can export Business Objects or the underlying DB tables. The latter is the best choice for bulk export.

The OTM Data Dictionary is available at MOS - 

Here is my URL for creating the export request - 

https://myOTMInstance:443/logisticsRestApi/data-int/v1/exportRequests 

 I will be exporting 2 tables to OCI Object Storage; here is the target bucket - 

I will use PAR (Pre-Authenticated Request) 

I copy the URL and paste it into the request - 

{

  "schema": "PRIMARY",

  "excludePublic": true,

  "contentType": "text/plain",

  "externalRef": "niallC-export-001",

  "targetSystem": {"targetURL": "https://myPARURL/n/.../b/niallc-demoBucket/o/locationTableExport",

       "appendName": true,

       "httpMethod": "PUT"

    },

  "tables" : {

    "items" : [

        {

            "tableName": "SHIPMENT",

            "selectList": "SHIPMENT_GID,SHIPMENT_NAME,PERSPECTIVE,INSERT_DATE,UPDATE_DATE"

        },

        {

            "tableName": "LOCATION"

        }

    ]

    }

}

Note the ability to specify which tables to export as well as the option to select only certain columns.

The target url is - 

https://myPAR-URL//n/nnn/b/niallc-demoBucket/o/locationTableExport

I set Auth to Basic and then execute the request in postman -

The response contains a requestID, which I can use to query export progress. 

Here I used - 

I check in Object Storage - 

Let's replicate this in OIC - 

Data Export via OIC

I begin by creating a REST based connection for OTM.


I then create the following integration - 

This is a synchronous integration that returns the requestId. The Invoke is configured, based on what I did in postman.

Many of the invoke request fields are standard vales, so I externalise these to a Lookup - 

The lookup values are used in the Map action before the Invoke of OTM.

I run the integration with the following request payload - 

Here is the response from OTM - 

Here is the response from the integration - 

I check progress using request id - 

I check my bucket in OCI Object Storage - 

Let's agentify this.

OIC AI Agent for Data Extract

First step is to expose the following 2 integrations as Agentic Tools - 

Create the Agent Pattern - 

Create a Prompt Template - 

Please note, I've hard-coded the tables, LOCATION and SHIPMENT, however, I could have defined these as variables, just as I did with {{currentDate}}. 

Export the following Tables from OTM - LOCATION and SHIPMENT. Add the following columns to the SHIPMENT select list - SHIPMENT_GID, SHIPMENT_NAME, PERSPECTIVE, INSERT_DATE, UPDATE_DATE. Use the following external reference - OIC-OTM-Export-Agent. Set the target Object Storage Folder Name to Location-Shipment-Export. Use the processing date value{{currentDate}}.

Here is the Agent configuration -

you will receive a data export request in the following format - Export the following Tables from OTM - LOCATION and SHIPMENT. Add the following columns to the SHIPMENT select list - SHIPMENT_GID, SHIPMENT_NAME, PERSPECTIVE, INSERT_DATE, UPDATE_DATE. Use the following external reference - OIC-OTM-Export-Agent. Use the processing date value dd-mm-yy.

Use the create OTM export request tool to create the request, setting the target Object Storage Folder Name to Location-Shipment-Export, adding the processing date provided as a suffix. e.g. Location-Shipment-Export-11-07-26. 

You will receive a response with the OTM request id.

Output this response to the user.

Then use the tool get OTM export request status to retrieve the status of the request. Keep executing this tool until you receive a success of failed response. 

Output this response to the user.

Finish with a quote from Marcus Aurelius.

I run the agent - 

I validate in OCI Object Storage - 

Looks good!

Data export, especially to a data lake,  is usually incremental, so an ask will be - how can I only export the shipments, etc. from the last week?

The OTM REST API support this as follows - 

Note the rangeBefore and rangeAfter fields. Let's try this out - 

I begin by creating a new Contact, Cathal Brugha,  in OTM - 

I execute the following request in postman - 

{

  "schema": "PRIMARY",

  "excludePublic": true,

  "contentType": "text/plain",

  "rangeAfter" : {"value": "2026-07-10T00:00:00Z"},

  "externalRef": "niallC-newContactExport-0710",

   "targetSystem": {

       "targetURL": ".../b/niallc-demoBucket/o/newContactExport",

       "appendName": true,

       "httpMethod": "PUT"

    },

  "tables" : {

        "items" : [

       

            {

                "tableName": "CONTACT"

            }

        ]

    }

}

As you can see, the raw contact data is returned immediately; no file is written to Object Storage.

I add rangeBefore to the request payload - 

I execute the request and see all my new contacts since the start of July - 

Again, no file is written to Object Storage. This I can force by adding the following HTTP Header to the REST request -

• The "Prefer" HTTP header identifies that the request should be processed asynchronously. If not present, and there is only one table in the request message, the request will be processed synchronously, the HTTP status will be "200 OK" and the exported content will be the response data. In all other cases it will be processed asynchronously, the HTTP status will be "202 Accepted" and the response to the request will be an export status message.

Let's try this out - setting the Prefer header to respond-async -

File is exported to Object Storage - 

Now let's get this working in OIC - 

Essentially the same as the integration, create OTM export request, except I've added the 2 data range fields to the request. 

I also add the Prefer header to the OTM REST api Invoke - 

I augment the Lookup with the default Prefer value - 

I run the integration - 

Note the folder name - OTMExport-0710.

I check in Object Storage - 

The final step is to schedule such exports. Here I will create a scheduled integration, which will invoke the integration I just created.

I add some global variables - 

I will not use dateRangeBefore in this example. The requestId is an output field for the latest request id returned from OTM. The tables4Export parameter allows me to easily change the list of tables to be exported.

Here is the integration - 

The Stitch action is used to hard code the tables to be exported. In this demo, I hard-code CONTACT and LOCATION. As I mentioned earlier, we could make this a variable, or have it defined in the Lookup.

The Assign Action at the end updates the dateRangeAfter parameter, setting it to the current dateTime.

  
I add some "batch" specific entries to the Lookup - 

I do an ad hoc run the scheduled integration and check in Object Storage - 

Applying a schedule to the scheduled export integration is easy - 

OTM Invoking OIC

Here I will configure OTM to invoke an OIC integration, when a new Contact is created.

We need a couple of artifacts - 

External System

We need to create an external service in OTM. This will be configured to invoke OIC. 

Next step is to create an Automation Agent - 

The Agent has actions, one of which is to invoke the external system -

Note the use of SEND INTEGRATION - to invoke the external system.

I then create a new contact in OTM - Sean South - 

Here I used the standard OIC REST adapter for the trigger. I could have also used the Logistics Adapter based connection - 

This has the value-add of adding the XML schema definition of Contact.

Otherwise, I need to add this myself - 

Summa Summarum

OIC makes integrating with OTM easy. Whether it's transactional - e.g. create contact, or bulk data export, with OIC you can do such easily and very quickly. You also have the value add of OIC AI Agents and tools, allowing you to "agentify" the process very quickly.

   

#1150 - OIC AI Agent for Error Management

Introduction

Manual resubmission or replay of failed flows is a pain, both time consuming and prone to error. How about letting an Agent take care of this? Here's a simple POC from me that illustrates exactly this. 

The use case is very simple -  I have integrations that error out, some may be eligible for recovery, others may be replayable. So how about automating the processing of such?

Recoverable Integrations

Async integrations that error out are recoverable. I have a couple of demo integrations, which will generate errors; one of these is - async process order - 

this invokes child create order and child update order. Errors will be thrown, based on the values in a Lookup.

This makes it easy for me to generate errors and also recover from such.

Replayable Integrations

Integrations with the following box checked are replayable - 

This checkbox has been activated for integration sync update order.

Other Errors for this POC

The integration - save order to file - writes to a local file system, via the connectivity agent; stopping the agent will generate an error.

Agent Tools

I will use the following tools to manage my OIC errors - 

Replay Orders Tool

As the name suggests, this tool will replay those replayable orders. Unfortunately, a bit of a misnomer, as it will replay all "replayable" flows. It uses the factory api - 

human approval Tool

This is a HITL based tool to get admin approval, before replaying failed flows.

notify admin Tool

This tool sends email notifications to the OIC admin(s), when certain things happen, recovery, relay etc.

Resubmit Errors tool

This tool uses the factory api to recover async errors - 

Get Errors Expanded tool

This tool uses the factory api to retrieve errors for a certain time window - 

https://design.integration.us-phoenix-1.ocp.oraclecloud.com/ic/api/integration/v1/monitoring/errors?offset=0&limit=50&q={timewindow: '1h'}&includePurged=no&orderBy=lastupdateddate&expand=integration,connection&integrationInstance=yourOICInstance

Note the use of the expand parameter; this will include integration and connection details in the response.

The Agent

Here is the configuration - 

You will receive a request to retrieve errors for a specific time period, e.g. Give me the errors for the last 1 hour. Other valid time periods are 6 hours, 1 day, 2 days, 3 days. Use the time period you receive when invoking the GetErrorsExpanded Tool. 

For each errored instance returned, output the following values from the GetErrorsExpanded response - 

instanceIid, from integration - name, id, projectCode, recoverable, replayable, errorCode, primaryName, primaryValue, from errorItems - errorLocation, errorMessage, detailErrorMessage.

Aggregate the errors output by name and errorMessage.

Recoverable Errors Processing Steps:

1. Output the following text ' ----------- Resubmission of Recoverable Errors Processing -----------'

2. Output the instanceId, name, id, projectCode of the recoverable errors 

3. Ask user if they want to resubmit these instances. 

4. If they respond with No, go to the Replayable Errors Processing Steps. 

5. If they respond with Yes, then use the Resubmit Error tool to resubmit. It expects an array of instanceIds. Output the response from Resubmit Errors. Then use the notify admin tool to send an email to the OIC admin. This tool has 2 parameters emailSubject and emailBody. Set subject to 'Error Monitoring Agent Report' + current dateTime. The emailBody should contain a table listing the details of the instances that have been resubmitted. The emailBody should look professional and HTML based. 

Replayable Errors Processing Steps:

1. Output the following text ' ----------- Replay of Replayable Errors Processing -----------'

2. Output the instanceId, code, version, projectCode, instanceReportingLevel of the replayable errors, do not include any errors that you have just re-submitted for recovery. Check the value of replayable in the integration section of the GetErrorsExpanded response. If replayable is set to true, then the instance is replayable.

3. Ask user if they want to replay these instances. If they respond with Yes, then use the human approval tool to initiate the approval workflow. Inform them the replays will require human approval and that you have initiated the approval workflow.

4. output the response of human approval, telling user whether the Replays have been approved or not.

5. If approved, then use the Replay Orders Tool to initiate the replays.

6. Output a message telling the user the replay(s) have been initiated.

Finish with a quote from Marcus Aurelius.

Test

• sync update order will fail on product = iSpy
• child create order will fail on iBike
• child update order will fail on iCar

I set the Lookup values as follows - 

I run the integrations a couple of times - 

Here are the errored flows - 

Now I run the agent - 

I begin by setting the conversation id, and then check for errors in the last hour - 

I will respond with a Yes - I do want the instances recovered. But before I do so, I change the values in the lookup -

I will need to leverage the conversation id in my response - 

I check my email - 

Now to the Replay processing - 

Remember my logic here? Replays need human approval. I answer with Yes. 

The task is assigned to me, so I check out my Task list - 

As you can see, I'm not great with designing compelling UIs; the data is there though, ready for approval.

I approve - 

I return to the agent log - 

Validate in OIC Observability

The replays were successful - 

I check out one of the resubmitted/recovered flows - 

Summa Summarum

Granted, this is a very simple POC. One would need to implement pagination logic, when retrieving the errored flows, to cope with large numbers of errors. But, as the Germans would say, that is simply Fleißarbeit.

Finally, I repeat the caveat - this is just a basic POC. Would I want Agents automatically resubmitting integrations? Probably not. I could envisage the following -

• Agent retrieves the errors, crunches the data
• Agent presents the result to the user 
• display errors based on integration / error e.g.
• create order has 3 errored flows due to Connectivity Agent issues
• create contact has 5 errored flows due to ATP error - Primary Key violation
• create invoice has 3 errored flows due to Netsuite concurrency throttling

Now does it make sense to auto-resubmit all of these? Mais non! The admin would need to validate the state of the connectivity agent. We could add an extra tool, based on the api - 

and, based on a positive result, try a resubmit.

For the 2nd example, primary key violation, resubmitting would probably just add to my woes. If the contact already exists, trying to add it again won't change things.

Now the 3rd example, this is a good candidate for an auto-resubmit. But, as I hope I've graphically illustrated, a blanket auto-resubmit probably doesn't make sense.

Continuing the bullets - 

• Agent gives advice of suitability for auto-resubmission
• Agent allows user to resubmit based on integration/error OR
• Agent invokes HITL to visualise the aggregated error data and allow admin to decide on which groups to resubmit.
• Agent log data stored for compliance purposes.

You get the idea.

Generally, using AI agents to do things with your enterprise data, is fine; but you must also consider  security, possibility of hallucinations and, of course, compliance. Keep this in mind when creating your agents. In this use case, using the agent to 

• retrieve the error data
• summarize the the error data
• give recommendations in respect of suitability for resubmission

is fine. You also need to decide how to invoke the agent; here you can consider -

• ad hoc agent runs from the OIC UI
• scheduled integration invoking agent, via the Agent native action
• client app invoking the agent via it's REST API

You may also consider applying RBAC to control who can run the agent.

So, to conclude - happy error hunting!