Integration of OTM (Oracle Transportation Management) with Baikal-Service Transport Company

Project Goals

• Automate data exchange between the corporate OTM (Oracle Transportation Management) system and the API of the Baikal-Service transport company.
• Reduce manual work for logistics staff by automating the creation, updating, and control of orders, shipments, invoices, and related documents.
• Identify unplanned shipments by automating the reconciliation of service provider data and OTM data.
• Increase transparency and control over transportation processes, synchronizing statuses and data between internal systems and the external API.

Implemented Integration Features

1. Automation of order creation and submission
- Generation and submission of orders to the Baikal-Service API based on data from the corporate TMS (Oracle Transportation Management).
- Cargo data validation, automatic calculation of pickup dates, insurance value checks.
- Logging and user notifications about order creation status.

2. Synchronization of shipment statuses and data
- Automatic updating of order and shipment statuses based on data received from the Baikal-Service API.
- Receiving and processing information about shipment statuses, tracking, payments, and delivery dates.

3. Integration with document flow and invoice reconciliation
- Matching invoice data and act lines with services received via the API.
- Automatic creation of child invoices and their lines in OTM based on reconciled data.
- Verification and reconciliation of service costs between Diadoc, the API, and internal systems.

4. Error handling and notifications
- Centralized logging of all operations, errors, and exceptions.
- Automatic email notifications to responsible users in case of errors, blocks, duplicate orders, and other events.

5. Synchronization with Baikal Personal Account
- Import of orders and shipments created manually in the Baikal personal account, with attempts to automatically match them to internal shipments.
- Processing of "unplanned" orders and automatic addition to the integration log.

6. Automation of calculations and estimations
- Requesting transportation cost calculations for shipments via the Baikal calculator API.
- Parsing and saving the structure of services and their costs for subsequent document matching.

7. Task scheduling and automation
- Use of DBMS_SCHEDULER for regular execution of data update procedures, document reconciliation, and invoice creation.

8. Auxiliary functions
- Automatic determination and validation of permissible cargo pickup dates.
- Retrieval of contact details for the logistics specialist who created the order.
- Support for manual shipment reassignment in case of changes on the OTM side.

---

Technologies and Approaches:
- PL/SQL, Oracle UTL_HTTP, JSON parsing, integration with external REST APIs, automation via job scheduler, centralized logging, email notifications, Diadoc document parsing and processing.

Technologies: Oracle PL/SQL