How to handle 2-letter vs 3-letter country code mismatches during Oracle Transportation Management migrations

🩺 Symptoms

The source OTM instance used 2-letter country codes (e.g., IN, US), while the destination was configured to use 3-letter codes (e.g., IND, USA)
Planning and validation processes in OTM began to fail
Country-based lookups and address mappings were not working as expected

🧪 Steps to Reproduce

Set up a source OTM system with country codes in 2-letter ISO format
Migrate objects or integrate records into a destination OTM using 3-letter ISO format
Attempt planning or execution using those records
Observe failures or mismatches due to code incompatibility

🔍 Investigation

OTM uses a global property to define the country code format: either 2-letter or 3-letter (not both)
The destination OTM was already using the 3-letter format
External systems (like SAP and AIS) were sending country data in 2-letter format
The client requested a solution within OTM, as changes to upstream systems were out of scope

⚙️ Root Cause

A mismatch between the incoming 2-letter country codes and OTM's 3-letter format configuration caused data mismatches and planning failures. Since the country code setting in OTM is global, it wasn't possible to support both formats at once.

💡 Solution

We implemented a three-part approach to fix the issue without impacting external systems:

✅ 1. OTM Automation Agent

A custom Automation Agent was built in OTM
Triggered on integration and update events where country code is involved
The agent automatically converts any 2-letter country code into the correct 3-letter code before storing it in the database
This was implemented using the Before Persist agent action in the Agent Header

Benefits:

Seamless compatibility with OTM's 3-letter format
External systems continued sending 2-letter codes
No upstream changes required

✅ 2. Python Script for Existing Data Cleanup

Many records had already been migrated using 2-letter codes. A Python script was created to:

Accept a CSV file
Replace 2-letter codes with 3-letter ISO codes using a reliable mapping
Output a clean CSV ready for OTM re-upload

✅ 3. Special Handling for HNAME_SET & HNAME_SET_MEMBER

After previous fixes, another issue was found in HNAME_SET and HNAME_SET_MEMBER tables:

These stored entries in the format: <DOMAIN>.COUNTRY\BR\, where BR (a 2-letter code) caused inconsistencies
A separate Python script was created to:
- Parse and identify embedded 2-letter country codes inside the string
- Replace them with corresponding 3-letter ISO codes
- Output corrected files for re-import

✅ Results Achieved

OTM now consistently uses 3-letter country codes across all relevant tables
Automation Agent ensures all future data is aligned
Existing records were cleaned using targeted Python scripts
No changes were needed in upstream systems

📘 Lessons Learned

Small mismatches in configuration like country codes can cause major functional issues in OTM
Aligning on global data standards early is key in migration projects
With a combination of OTM automation and external scripting, issues can be resolved cleanly

🧠 Why This Matters

During complex OTM migrations, full control over external systems isn't always feasible. OTM's extensibility allows building adaptive, automated solutions that maintain system integrity.

🔍 Pro Tip

Use ISO 3166-1 for converting 2-letter to 3-letter country codes
Always validate changes in lower environments before production rollout
Keep automation lean and focused for better performance

📌 Key Takeaway

With a combination of OTM Automation Agent (using Before Persist logic) and Python-based cleanup scripts (for general data and HNAME_SET tables), we resolved a critical formatting issue without impacting upstream systems — keeping OTM clean, stable, and integration-friendly.

💻 Access the Solution Code

📂 Python Scripts → OTM Automation Kit – GitHub

Complexity Level: ⭐⭐⭐⭐ (High)

Required:

Deep understanding of OTM configuration and automation
Built multiple scripts for accurate data cleansing at scale

Tags: #OTM #OracleTransportationManagement #CountryCodeFix #OTMAgent #PythonScript #DataStandardization #IntegrationFix #OTMCloudMigration

OTM Migration: Solving Country Code Format Conflicts