Business Process Automation Data Structure

The Business Process Automation (BPA) module handles the technical configuration layer of OTM — workflows, notifications, outbound integrations, reports, and transmission logging. The tables below are useful when troubleshooting agent behaviour, auditing integration traffic, or diagnosing report failures.

Contacts:

A Contact in OTM defines a communication endpoint — an email address, fax number, or integration URL — that OTM can reach when sending notifications or outbound data. Contacts can be associated with:

Users — to notify OTM users by email on business events.

External Systems — to route outbound XML transmissions to middleware or third-party systems.

Locations, Service Providers, Involved Parties — for carrier or partner notifications.

Agent Notify Contact actions — triggered automatically by OTM Agents on business events.

Contact tables:

CONTACT — Contact record with name and type.

CONTACT_COM_METHOD — Communication method linked to the contact — stores the actual email address, URL, or fax number and the communication type (EMAIL, HTTP, FAX, etc.).

External System:

An External System defines an outbound destination — typically a middleware platform (Oracle SOA, MuleSoft, webMethods) or any system that receives GlogXML transmissions from OTM. An Out XML Profile is associated to control which fields are included in the outbound XML.

EXTERNAL_SYSTEM — External System record with name, URL, credentials, and transport method.

EXTERNAL_SYSTEM_OUT_XML — Links the External System to its Out XML Profile, controlling the shape of the outbound XML payload.

Agents:

Agents are OTM’s workflow engine — event-driven rules that trigger actions automatically when business events occur (e.g. ORDER CREATED, SHIPMENT TENDERED, STATUS UPDATED). Agents can use standard OTM events or custom events defined by developers. They are covered in detail in the Agents topic.

Agent tables:

AGENT — Agent header with name, description, active flag, and triggering event.

AGENT_ACTION — Actions to execute when the agent fires (e.g. Send Interface Transmission, Notify Contact, Raise Event).

AGENT_ACTION_DETAILS — Parameters for each action — this is where the actual agent code and configuration lives.

AGENT_EVENT — Events that trigger the agent.

AGENT_EVENT_DETAILS — Conditions and filters on the triggering event.

Sample query — search for specific text within agent action parameters:

SELECT aad.*
FROM   agent_action_details aad,
       agent ag
WHERE  UPPER(aad.action_parameters) LIKE UPPER('%TEXT TO SEARCH%')
AND    aad.agent_gid = ag.agent_gid
AND    ag.is_active = 'Y'

Sample query — find all active agents that raise a specific custom event:

SELECT a.agent_gid,
       a.description,
       aad.action_sequence
FROM   agent a,
       agent_action_details aad
WHERE  a.agent_gid = aad.agent_gid
AND    a.is_active = 'Y'
AND    aad.agent_action_gid IN ('RAISE EVENT', 'FOR EACH')
AND    aad.action_parameters LIKE '%custom event text%'

Reports:

OTM reports are built using SQL and BI Publisher. Report development is covered in detail in the Report Development topic.

Note: All report tables exist in the REPORTOWNER schema. They are also accessible from the GLOGOWNER schema under the same names because OTM creates public synonyms for them.

Report tables:

REPORT — Report definition record with name, query, and output format.

REPORT_PARAMETER — Input parameters defined for the report (e.g. shipment ID, date range).

REPORT_SET — Groups related reports together. Used in Agent actions like PRINT DOCUMENT.

REPORT_SET_DETAIL — Individual report entries within a Report Set.

REPORT_LOG — Execution log for each report run — stores output file name, run status, and timestamp.

REPORT_LOG_PARAMETER — Input parameter values used for a specific report run.

Sample query — find the Shipment ID from a report output file name:

SELECT rlp.parameter_value
FROM   report_log_parameter rlp
WHERE  rlp.file_name      = '<output file name>'
AND    rlp.parameter_name = 'P_SHIPMENT_ID'

Integration:

Every inbound and outbound transmission in OTM is logged in the integration tables — including the raw XML, processing status, and any error details. These tables are the first place to look when troubleshooting a failed transmission.

I_TRANSMISSION — Transmission header. Stores status, direction (inbound/outbound), external system name, and timestamps.

I_TRANSACTION — Transaction detail at the object level. Holds the raw XML payload and the GlogXML element name (e.g. TransOrder, OrderRelease, ShipmentStatus).

I_LOG — Error log for failed transactions. Contains the error message text for each failed I_TRANSACTION row.

Sample query — fetch error details for a failed transmission:

SELECT it.i_transmission_no,
       il.i_transaction_no,
       DBMS_LOB.SUBSTR(il.i_message_text, 200) AS error_message
FROM   i_transaction it,
       i_log il
WHERE  it.insert_date      > SYSDATE - 1
AND    it.i_transmission_no = <Enter Transmission No>
AND    it.element_name      = 'ShipmentStatus'
AND    it.transaction_code  = 'I'
AND    it.status            = 'ERROR'
AND    it.i_transaction_no  = il.i_transaction_no

What’s Next:

The next topic covers Basic OTM Configurations — starting with Domain Items, Locations, and Equipment Groups — the foundational setup every OTM implementation requires before planning and tendering can begin.

Next Topic: Basic OTM Configurations — Domain Items, Locations and Equipment

Business Process Automation Data Structure

Questions & Discussion