Saved Queries/Conditions

Saved Queries in OTM help to query specific records in a screen or add conditions to custom logic while creating an Agent or similar configuration. They can be reused across multiple OTM features including recurring processes and business monitors.

Start here — this is foundational: Saved Queries and Conditions are arguably the most important configuration concept in OTM for both UI customisation and automation. Almost every advanced OTM feature — Agents, Business Monitors, Recurring Processes, Action Checks, and custom Workbenches — requires a Saved Query or Condition to define which records the logic should act on. Without a solid grasp of how to build and reuse queries, you cannot effectively configure any of these features.

Beyond automation, Saved Queries power the everyday user experience: they let you build pre-filtered finder screens that surface only the records relevant to a specific role or workflow — for example, a shipment coordinator who should only see shipments in their region, or an approver who only sees invoices within their approval threshold. A well-designed set of Saved Queries is often the difference between an OTM implementation that users actually adopt and one they work around.

Invest time here before moving to Agents, Business Monitors, or any other automation topic. Every hour spent mastering Saved Queries will save you many hours of troubleshooting downstream.

Type 1: User in Finder Query

Configuring Multi-Stop Shipments in OTM

In OTM, a multi-stop shipment is a single shipment that picks up or delivers freight at more than one location. For example, a truck may pick up goods from one warehouse and deliver them to multiple customer locations in sequence — all on the same shipment.

OTM’s Bulk Plan algorithm can automatically consolidate multiple orders into a single multi-stop shipment to optimize transportation costs. To enable this, the following areas need to be configured.

Domains, Standard Schema and Data Dictionary

OTM organises all of its data using a domain model. Understanding domains, the database schema, and how business objects are identified is foundational for anyone working with OTM data — whether you are building integrations, writing reports, or troubleshooting transactions.

Domains:

What is a domain? A domain is a logical grouping of OTM data for a business unit. A corporation with multiple shipping divisions — say, Merchandise and Food & Beverage — would typically have one domain per division. Users and roles are tied to a domain, so a user in one domain cannot see or modify data in another.
Domain hierarchy: Domains can be nested — a parent domain at the corporate level with child domains under each business unit. Configuration defined at the parent level (e.g. Item Numbers, Agents, Rate Offerings) is shared across all child domains, while each child domain can also maintain its own configuration.
DOMAIN_NAME column: Every table in OTM includes a DOMAIN_NAME column that ties each row to its domain. This is how OTM isolates data between business units within a single database instance.

GID and XID — How OTM Identifies Business Objects:

Inbound Integrations (XML)

OTM supports several methods for receiving data from external systems. This topic covers XML-based integrations, which are the most common approach in OTM implementations.

Integration Methods Overview:

GlogXML via Middleware: XML files following the GlogXML schema, posted to OTM from middleware platforms such as Oracle SOA/BPEL, webMethods, or MuleSoft. This is the most common approach for production integrations.
Manual XML Upload: OTM users manually upload a GlogXML-formatted XML file through the OTM Integration Manager screen. Useful for testing, one-time loads, or small data corrections.
CSV Upload: Uploading data using comma-separated value files for supported OTM entities. See the CSV Data Uploads topic for details.
DB.XML Upload: A method for loading data directly using database-level XML files, typically used in implementations or data migrations.
REST API: Newer versions of OTM support REST API-based integrations for querying and updating OTM data without using GlogXML format.
HTTP Post from Code: Custom programs written in PL/SQL, Java, or other languages can post GlogXML directly to the OTM WMServlet endpoint. See the Post Data via HTTP Request topic for details.

GlogXML Schema:

What is OTM ?

OTM stands for Oracle Transportation Management. It is a software product designed to automate various logistics business processes — receiving orders from source ERP systems for freight consolidation, optimizing routes and shipping costs, sending tender requests to carriers, receiving shipment tracking events, processing invoices, allocating freight costs to orders, and more.

Some of the Core Capabilities of OTM:

Order Management: Receive Purchase Orders (Order Base) from source ERP or legacy systems and create Order Releases (Bookings) for full or partial quantities, either manually or through automated release rules.
Bulk Planning: Consolidate Order Releases (Bookings) with common source and destination locations, or build multi-stop shipments. The Bulk Plan algorithm identifies the optimal route (Itinerary), selects the least-cost carrier based on pre-configured rates, optimizes loading of items into equipment, and calculates in-transit times.
Third-Party Rate and Distance Engines: OTM can dynamically fetch LTL rates from third-party engines such as SMC³ RateWare XL, and calculate distances between locations using mileage engines such as PC*MILER or MileMaker.
Tendering: Send Tender Offer requests to the carriers identified during planning. If a carrier cannot accept XML tender files directly, middleware tools such as MuleSoft, Oracle BPEL, or similar integration platforms can be used to translate OTM outbound XML to carrier-readable formats such as EDI.
Tender Response: Receive and process carrier tender responses and update the shipment accordingly.
Shipment Tracking: Receive shipment tracking updates from carriers and update shipment status in OTM.
Invoicing: Receive and process invoices from carriers, match invoice costs against planned costs for approval, create vouchers for approved invoices, and allocate freight costs back to customer orders.

Ease of Implementation:

Yard Management and Appointment Scheduling

Oracle OTM’s Yard Management and Appointment Scheduling features help warehouse teams track containers in the yard and coordinate dock door assignments for loading and unloading.

Yard Management:

A warehouse typically has a Parking Yard and Dock Doors. The yard has Rows and each row has a defined number of slots. A container is first placed in a yard slot. Warehouse staff then pull the container from the yard slot to a dock door for unloading. The carrier is typically responsible for bringing the container from port to yard.

Order Management Data Structure

OTM models order management across three levels: the Purchase Order (what was ordered), the Order Release (a confirmed booking for movement), and Order Movements (the planned legs of that movement). Understanding this hierarchy and the underlying tables is essential for integration work and troubleshooting.

Purchase Order (Order Base):

A Purchase Order represents the commercial transaction — what items are being moved, from where, to where, and under what terms. POs are typically created in an ERP system (e.g. Oracle E-Business Suite) and sent to OTM via inbound integration. Note that POs often lack exact weight and volume details, which are required for shipment planning — those are defined on the Order Release.

Product Architecture

OTM follows a standard three-tier architecture — a widely used design in enterprise software where the application is split into three distinct layers: Web Tier, Application Tier, and Database Tier. Each tier has a specific responsibility, and together they allow OTM to scale, be maintained, and support many users simultaneously.

Depending on the deployment model — on-premise or SaaS — the underlying infrastructure differs, but the three-tier concept remains the same. In on-premise deployments, your organization manages each tier on its own servers. In SaaS (cloud) deployments, Oracle manages the infrastructure and these tiers are hosted and maintained by Oracle.

Outbound Integrations

OTM can generate outbound XML data for any business object — Order Release, Shipment, Invoice, and more — and send it to an external system such as a carrier platform, ERP, or middleware. This can be triggered manually using the Send Interface Transmission action, or automatically from an OTM Agent (workflow). OTM Agents are covered in a later topic.

There are three things you need to set up before OTM can send outbound data:

REST API

OTM exposes a REST API that lets external systems read and write OTM data without using XML transmissions. All four standard HTTP methods are supported — GET, POST, PATCH, and DELETE — and the same API can run OTM Saved Queries, which lets you pass parameters into complex SQL-backed searches.

Setup — Create an Integration User

Before making any API call, create a dedicated user in OTM and attach a role that includes the REST ACLs for the objects you need.