OTM Templates Design

Templates design is a common solution in many Cloud applications. After all, why do you need to reinvent the wheel? This approach is also commonly used in OTM (Oracle Transportation Management). While there are advantages to the template, this approach also have certain drawbacks.

Template Quick Guides

The type of templates will determine how duration of your customer onboarding. You can follow these quick guides in your templates approach.

  • Keep your templates to less than 8.
  • Look to data auto assignment rules.
  • Collect enough masterdata but not too much of it.
  • Prefill templates data as much as possible.
  • Use API to automate data collection in templates.
  • Provide auto validation checks.
Things to Avoid

Templates have some drawbacks which we need to take note and mitigate.

  • Do not over invest in your templates. It is a means to an end.
  • Templates cover common scenarios. Remember to provide options to templates design.
  • If there are a constant increase of your templates, look to refactoring and harmonise your templates.

Using templates in OTM greatly speed up your customer onboarding time. However, templates are just a tool and should not complicate your OTM configuration.


Oracle VPD (Virtual Private Database) is a quick security policy design for the database. This is also one of the features in OTM (Oracle Transportation Management) application. The setup of VPD can be quite painful if you are new to Oracle and OTM. In OTM, this can be a great struggle as you seek to maintain the VPD configured by your predecessor.


OTM is notoriously famous for many VPD errors when things goes wrong. Many log errors will flag as policy violations. In some cases, your OTM UI screen (User Interface) will just freeze or give a weird error if you try to edit the object. There is another instance where you find your changes cannot be committed to database.


These are some common VPD tips to speed up your understanding in OTM.

  • Test out your issue with or without VPD.
  • Always consider VPD as one of the possible root cause of reported issue.
  • VPD may not work well if your SQL contain too many relational tables.
  • Keep your SQL in VPD short and simple.
  • If your VPD is complex, consider ways to simplify it by using a column field on the object.
  • Do not implement VPD for the sake of VPD.

VPD in OTM is quite a pretty neat extension of Oracle database. You will usually need some advanced knowledge of SQL to maintain and decipher the VPD. As a rule of thumb, it is good practice to keep VPD simple to spare your future headaches.!

OTM Screen Issue

The slowness of OTM screen loading is a known issue. This is worst in Asia with some offices having lower internet speed and network. As a result, OTM is not suitable for deployment for such areas. Have you ever wonder how to solve it? A common approach is to increase the server CPU or network bandwidth. You may even try to upgrade the user laptop or proposed faster browser.

Root Cause

The root cause is due to the SQL used to generate the screen. You can turn on your logs and include the SQL option for the log file. Get the SQL used in the slow screen and run explain plan for it. From the results, you will notice the root cause is caused by the usage of sub tables like status and refnum.

Simple Ways to Fix

Before you go and upgrade your server and network, you can try the following simple ways to improve the screen loading.

  1. Reduce usage of status and refnums in screenset.
  2. Use VPD. This will constraint the query data.
  3. Add domain name in Screenset criterion.

If you have any other good ideas to speed up the screen loading, drop me a DM.

Lesson from OTM LTL Error

Last week, we hit a weird default OTM (Oracle Transportation Management) error whereby LTL (Less Than Truckload) transport mode shipment does not tender out with the standard secure resources status. Instead, secure resources status was changed to SECURE RESOURCE_PICKUP NOTIFICATION. As a result, the service provider cannot accept the tender for this status. Thankfully, OTMFAQ forum give us the clue to resolve this. I will explain the rationale here instead.

OTM Default Workflow

The “issue” is actually due to a default business scenario in United States (US). In layman terms, LTL means loose cargo. Thus, service provider is expected to pickup the loose cargo rather than the need to accept the tender request. This is why the status is called SECURE RESOURCE_PICKUP NOTIFICATION. In a way, the request is auto accepted. The way to fix it is to amend the workflow configuration by adding LTL into the TENDER%MODES values.

OTM and its Culture

Debugging in OTM is a challenging task and can consume lots of unproductive hours. Historically, this product was tested widely in US and Europe and very less for Asia. There are many default values that are catered to the American and European audiences. The key tip is to understand and know which are defaulted. If you manage to find this configuration, we usually cross our fingers and hope that the default values can be amended.

All product are designed by humans. Unknowingly, cultures are factored into its design. A good product should always configuration of these default cultural values. One way to do so is to test its localisation features.

OTM Status Inital Value

Today I have an shocking discovery on a configuration of an initial value setting for OTM (Oracle Transportation Management) status. The initial value is set as Error! It is really amazing to think how this could be an initial value. Of course, valuable hours are wasted to debug an initial value which is actually correct by its initial“error state”.

Happy Path Design Approach

The state diagram is fundamental of engineering and software lifecycle if you are familiar. There should never be an initial value as error or exception. One must alway think of happy path and start with a perfect flow. Starting with an erroneous state gives confusion to users and SME (Subject Matter Expert), as we do not know what is wrong when nothing have started. Be Optimistic and always go for Happy Path Design Approach.

Design by Norms, Think like a Traffic Light

The best solution design is design by Norms and thinking like a Traffic Light. Green is a Go, Red is Stop and Yellow is proceed with caution. Thinking like traffic light greatly reduce learning curve from users and have high system adoption rate. It is one of the best design where users can easily learn with no user manual or training. Do not break the norms and do fanciful solution. You will end up confusing and these type of solution always get discarded due to its complexity.

OTM CSV and UTF8 Encoding

I am dabbling in more Chinese character (中文) for OTM (Oracle Transportation Management). I hit an unusual issue when I try to upload Chinese Characters with csv file. The csv is generated from Excel macro VBA. Thanks to the various forums (OTMFAQ and stackoverflow), I managed to find the root cause on UTF encoding with BOM (Byte Order Mark).

Source: OTMFAQ

BOM is a 3 bytes in the beginning of the file when you save UTF-8 on Windows. If you generate the UTF-8, you can view the csv output encoding with TextPad++. OTM does not allow the upload of UTF-8 BOM file. It will just throw out an error, which is not friendly at all.

The solution to solve this is to convert the csv to UTF-8 without BOM. This will remove the first 3 Bytes from the beginning of the file. This can also be automated in macro with the following codes. Thanks to the solution codes from stackoverflow.

Public Sub PutTextFileUtf8(ByVal PathFileName As String, ByVal FileBody As String)

‘ Outputs FileBody as a text file (UTF-8 encoding without leading BOM)
‘ named PathFileName

‘ Needs reference to “Microsoft ActiveX Data Objects n.n Library”
‘ Addition to original code says version 2.5. Tested with version 6.1.

‘ 1Nov16 Copied from http://stackoverflow.com/a/4461250/973283
‘ but replaced literals with parameters.
‘ 15Aug17 Discovered routine was adding an LF to the end of the file.
‘ Added code to discard that LF.

‘ References: http://stackoverflow.com/a/4461250/973283

Dim BinaryStream As Object
Dim UTFStream As Object

Set UTFStream = CreateObject(“adodb.stream”)

UTFStream.Type = adTypeText
UTFStream.Mode = adModeReadWrite
UTFStream.Charset = “UTF-8”
‘ The LineSeparator will be added to the end of FileBody. It is possible
‘ to select a different value for LineSeparator but I can find nothing to
‘ suggest it is possible to not add anything to the end of FileBody
UTFStream.LineSeparator = adLF
UTFStream.WriteText FileBody, adWriteLine

UTFStream.Position = 3 ‘skip BOM

Set BinaryStream = CreateObject(“adodb.stream”)
BinaryStream.Type = adTypeBinary
BinaryStream.Mode = adModeReadWrite

UTFStream.CopyTo BinaryStream

‘ Oriinally I planned to use “CopyTo Dest, NumChars” to not copy the last
‘ byte. However, NumChars is described as an integer whereas Position is
‘ described as Long. I was concerned by “integer” they mean 16 bits.
‘Debug.Print BinaryStream.Position
BinaryStream.Position = BinaryStream.Position – 1
‘Debug.Print BinaryStream.Position

Set UTFStream = Nothing

BinaryStream.SaveToFile PathFileName, adSaveCreateOverWrite
Set BinaryStream = Nothing

End Sub

PS: Remember to include “Microsoft ActiveX Data Objects 2.5 Object Library” under Tools->References.

OTM Debugging

Today, I had spend 4 hours troubleshooting OTM (Oracle Transport Management) only to realise that the mistake was due to a change of Service Provider ID. This change was not updated in the Rate Offering. As such, the shipment planning was not able to pick up the rates. It was a very silly mistake and also shows the challenge of debugging in OTM. Anyone doing OTM will know the power of OTM debugging. Here are some debugging tips which could help anyone still doing OTM!

OTM Debugging Tips
  • Test often with each change of configuration. Use Agile to configure and test each change within a day.
  • Prepare a standard checklist for checking. This is important for Shipment Planning and rates setup.
  • Turn on your diagnostic log. It is more friendly than the log file.
  • Ask your OTM buddy for second opinion in your debugging approach. You may find new perspective.
  • Turn on your log files as last resort. The log file can either help you or discourage you. Go to the last page for the clues.

If you still unable to find the issue, rollback the change and configure your change again. This time, configure and test more aggressively. The key to debugging OTM is patience and practice. The only way is to get your hands dirty.

Agile TMS with DevOps

Unknowingly, I have spend more than 5 years with TMS (Transportation Management System) using OTM (Oracle Transportation Management) system. Overall, I enjoy the ease of configuration to deploy Agile solution using technique of DevOps. The control of global TMS solution also create high degree of adaptability for the team.

How to Agile your TMS
  • TMS have short transit window. It is favorable to adopt Agile.
  • Do design for change rather than design on sign-off requirements.
  • Configuration solution with custom plugins can cater for all TMS scenarios.
  • Determine your Agile components.
When to DevOps
  • DevOps complement Agile approach. This suits TMS because TMS contains many exceptions in the real world.
  • Use DevOps to close your loop in Agile implementation.
  • Consider to include monitoring tools or reports and analyse your data to determine your solution effectiveness.
  • TMS is usually 20% localisation. You may choose to DevOps on these localisation.

In summary, TMS needs a good product platform. You will also need to train your your TMS team to be Agile and DevOps ready.

OTM Object Locks

OTM (Oracle Transportation Management) Object locks have been my nemesis lately. You can see my past battles with refactoring and code quality. In migration or upgrades, direct copy of old packages from old to new domain often do not work as expected. Of course, you will only realise it when you hit object locks.

Signs of Object Locks
  • If you see very long PLSQL packages, it will mean the codes will take a while to travel from end to end. Any delay will lock object or tables giving the user a non responsive feel in the UI.
  • Many UPDATE SQL are seen in the agent or PLSQL.
  • Locks in Agent are used in Modified agent.
  • Copying of data from different objects like Shipment to OR (Order Release).
  • Removal of PLSQL packages in Agent and DSU (Direct SQL Update) to bare minimal, especially Modified Agents.
  • Reduction of codes in PLSQL. Delete or comment out old codes.
  • Reduce your UPDATE to a single point.
  • Add exception handling in your PLSQL to allow graceful exit of your PLSQL.
  • Conduct unit testing to each Agent or procedures before enabling them. If in doubt, remove them.

It is a painful process and huge amount of mandays are spend. Of course, lots of practice and hair tearing are done before you realise these tips. Hopefully, they are of help to those doing migration or upgrades of OTM.