Transport 7.4 Migration – Line Item Detail Issue

Background


I’m currently working with a client upgrading from TM 7.0 to 7.4. In the 7.2 timeframe, JDA (Manu at the time) introduced ‘Line Item Detail’ functionality to the Order transaction. This is a brief description of an issue encountered (and verified by a technical colleague) and some of the logic behind it. To be clear, I have not tested this issue with other version migrations, only the 7.4.0 migrations. Other pre-Item versions may have the same issue when upgrading.

Problem Description


The problem occurs when running the 7.4 packaged migration scripts on a version of Transport that does not have the ord_item table. Migrating data from say, the ord_qty table into the ord_item table requires the script to ‘dummy’ an Item to keep the relationship between the Order & Item intact – where previously none existed.

To compensate, it appears that the migration will take any Order from the origin schema, and perform the following actions:
1. Determine the appropriate Item ID, likely based off the Commodity of the current Order
2. Insert the Item ID into the item table
3. Apply a suffix of ‘_ILD’ to the item record
4. Use that item_ILD record as the new ord_item.item_id

Now, this causes a few problems when migrating a production environment into another production environment. First, we have all the new item_ILD records (these are replicas…not replacements). And, they are attached to orders through the migration, making them difficult to delete without dropping the order. Because of integrity constraints, you cannot rename the ord_item.item_id or truncate the ‘_ILD’ portion of the ID because it’s already on an order. So, the logical next step would be to delete the Orders, clean and reload.

Adding this on top of all the normal tasks on cutover weekend would be challenging, and possibly risky. Those orders could already be on loads. Those loads could already be in ACCEPTED or ENROUTE status, meaning you shouldn’t really touch them at all. Plus, for those of us using Freight Pay – the Item record is usually the link to the proper GL Account for freight cost allocation purposes.

Needless to say, we’re still working through this. A colleague of mine at another client had the same issue, but they were able to manage a clean cutover (they didn’t have to migrate orders, they could start fresh in a new 7.4 environment). How this will be handled is still being debated, but the default position is that we will be doing extra cleanup during cutover weekend to remedy this migration issue.

More to come & if anyone has any suggestions on overcoming the issue cleanly – I’m all ears!

About Brad Forester