Upgrade 101

Please read the article posted on Medium using the link below:

Upgrade 101

Customization in JDA (RedPrairie) – A fearful proposition for many?

Generally speaking, in my project experience in the JDA (RedPrairie) domain for over 20 plus years, one commonality exists: the fear of customizing the software to meet the operational efficiency that one may desire. Often these customizations are pushed to JDA with the hopes of getting incorporated into standard product, yet most of the time it is a struggle to get past this hurdle and it is accepted as a necessary evil. The fear is generally emanating from previous failed attempts in such customizations or the misconception that any customization is taking the client away from the upgrade path.

In this blog entry, my goal is to address this concern.  As long as user's customizations are done in a bolt-on manner allowing the base JDA code to be preserved., the client's upgrade path is not put in jeopardy. The customizations  can include changes to RDT screens, or adding new RDT screens, or changes to standard behaviors to allocation. picking or putaway and still remain on the upgrade path.

Again, the purpose of this entry is not to advocate rampant customizations.  The only objective is that an operational efficiency not be sacrificed, simply for the unfounded fear of customization.

The analogy I can think of is that I went and got a nice car. It suits me well, has all the gadgets I need etc. except for one limitation, that it has high performance tires. I happen to live in Wisconsin so I am left with two options:

• Not drive it for the 4 winter months
• Or, get a different car for winter

Many customers will treat their JDA purchase in this manner and be willing to adopt a manual approach or a different system to handle a shortcoming rather to change the tires. To add insult to injury they would somehow think that getting all weather tires from the dealership at twice the price will somehow insure the warranty of the car.

Understanding the JDA Application Architecture

JDA Application Architecture is layered and has been envisioned to support customization at all levels.  Understanding this architecture, allows to tailor the changes in a manner that ensures the upgrade path.

JDA Changes are configuration based, database changes, or code changes.  At all of these levels, following the correct methodology allows the client to preserve their changes during an upgrade migration.

• Configuration Changes - Almost all configurable changes (like field names, messages, validations, lookups etc.) have a customization level.  Having this customization level to be non zero (generally 10 or above), ensures that JDA hot-fix will not replace this change.  
• Database Changes - Many times, a change requires adding a custom column to a standard table, or adding a custom table.  As long as the column names are prefixed with UC and table names, with USR (the convention provided by JDA), any new version will preserve these during an upgrade.  Furthermore, if the user followed the convention of preserving the changes in a file in the $LES/db/ddl folders then these scripts can be run again after the new data dictionary is loaded.  For example, if a client has added custom columns to PCKWRK in 2009 version and is now moving to a higher version where PCKWRK is split into two tables, the upgrade scripts cannot determine which tables the columns should be moved to.  But the user can easily edit their scripts in the $LES/db/ddl folder to reflect the PCKWRK_HDR or PCKWRK_DTL and run them again (and make corresponding changes to the code).
• Command Based Changes - Changes done at command level, when done correctly are the easiest to preserve because the $LES/src/cmdsrc/usrint directory is preserved during an upgrade process.  The key is how the user (or partner) made the changes:
• A change can be complete custom solution, like a new DDA, a new report etc.  This type of change has no dependency on an upgrade except to ensure that it is needed and the data model is consistent
• A change that is an override to a standard command, like adding some custom business logic to allocation etc.  This is where the correct methodology comes into play.  The client should be mindful of the following red flags:
• A change to standard command being done at the C/ Java code level - If the partner is providing new C/ Java code and pointing your command to a new var based component, beware.  You are now not on the upgrade path.  The client should challenge this type of change and ask if there is another option
• A change to a standard RDT screen is being done by copying the standard code to VAR and modifying it.  Again, as a client, you are not on the upgrade path.  Challenge this and find out why the given change cannot be done as an override?
• Re-purpose standard database columns for some other use.  A client often chooses to, or is advised to utilize a standard column (like REVLVL) for a change that the client is implementing, and not add a custom column for this purpose.  Beware, that the next upgrade may not have that column available or has a different use for this, making your change not compatible anymore.
• Triggers on standard commands - Changes may be made by adding business rules on standard commands utilizing custom triggers.  As long as the custom triggers are at a higher firing sequence and have a custom name, the upgrade path is preserved.  Many times, a change is needed to a standard trigger.  This should always be done by disabling the standard trigger and then adding a custom trigger.  Clients, beware, that if the change is not being done in this manner, you are going away from the standard methodology.
• Integration Changes - These changes are more common than others, as clients usually have little choice in this.  However, I see the most non-standard practices here, causing the clients undue effort during an upgrade.  So client, beware, that if you end up with any custom events where a standard is available (like a custom INV-ADJ, or a custom SHIP_LOAD), as why?  Generally speaking the customizations are only needed in Retrieve methods, or having some algorithms etc.  

Need of Customization

As stated above, one can have a varying perspective as to what is a customization in JDA.  The author’s perspective is that any change that takes the application away from a possible upgrade in the future needs to be evaluated heavily for the merit of the given change.  Beyond this, as long as base JDA code remains in play, then extending of the code, database, or configuration should only be merited against the operational cost of the work-around that is requiring the change.  For example:

•  JDA does not offer any column in the “Pre-Plan Summary” for Wave Operations to select orders by carrier move.  If the customer is doing transportation management, this can be a valuable field to display on the pre-plan summary screen
• The change will simply be to override the list command
• The override should call the original command and then add a new select to get the carrier move and re-publish the result set
• The screen will start to display the additional column because it is in the result set
• The code is upgrade proof because the standard command is still what returns the main data set
• JDA offers the capability to write triggers on existing commands.  The business requires an additional log entry for inventory movement under specified conditions
• JDA Standard Inventory Deposit Screen needs to show an additional set of information. In the post 2011 version of JDA the RDT screens are Java based, hence can be extended without having to rewrite the entire RDT screen.  Thus the base RDT screen still remains pristine and additional information, or method is called as custom
• A given SQL statement is not optimized.  A Query hook can be added, to rewrite the query before it gets to the middleware and hence remain upgrade proof (please see the Blog entry on this topic)

The above discussion was only focused on business reasons to extend standard functionality.  More often we need entirely custom solutions, like a new DDA, a new RDT screen etc.   Generally speaking any modification that is needed in JDA is being built to optimize a warehouse operation.  Thus the existing set of standard commands may still remain in play, except the business case is to optimize the multiple set of commands.  For example:

• JDA does not offer a standard transaction to change the Item on existing inventory.  The standard option is to adjust out the inventory of the first item and then adjust in the inventory for the second item.  This is the preferred mechanism as the process will fire the requisite host transactions to keep the host in balance.  The customization in this case, be it a DDA or RDT screen would simply be to take the input from the user (like the LPN and the new item) and then do the two transactions in succession.  The end result, even though considered a customization based on being a new screen is calling standard functions underneath
• JDA does not offer a standard transaction for automatically loading and shipping a trailer.  However, often for customers that do inter-company transactions, this becomes a requirement.  The goal is to load and dispatch a trailer so an inbound ASN can be generated to receive the inventory in the other plant.  The inventory physically does not need to be loaded.  In this case again, having a DDA that can do the load inventory, close trailer, dispatch trailer transactions in succession allows to achieve the end result and convenience for the users

The business cases discussed above should allow the reader to appreciate the concept that given needs for extending the business logic in the JDA application are often simplistic in nature with high return value in operational efficiency.  Furthermore, any of the business cases discussed above, though “Custom”, will remain upgrade proof as they are calling standard commands underneath.

Types of Customizations

This leads into what types of customization terminology is applicable in the JDA domain.  Please consider that the following is a list based on author’s perspective and how the custom work is being defined here.  A given enterprise may still differ on specifics of the definition of custom work but may agree on the scope of the list below:

• Extending Business logic
• Creating a wrapper command that calls on or multiple standard commands in succession
• Creating a custom command for custom DDAs, Reports, and Labels etc.
• Overriding Existing Commands
• Override to change the inputs to a standard command to effect the needed outcome
• Override the command to change the result set of the command
• Override to change entirely the business logic (not recommended), but allow the calling screens etc. to still function the same way
• Adding Custom Triggers
• Allow for additional logging
• Allow for custom 
• Adding custom Tables/columns
• To track custom attributes
• To track custom 
• Extending standard 
• Adding custom 
• Adding custom Data Driven Applications (
• Adding custom .NET screens for more complex screen 
• Extending standard RDT screens
• Adding new methods, or extending existing ones like a new behavior on Form Entry
• Adding new 
• Adding new RDT 
• Adding new Reports and 
• Adding new 
• Adding new Valid Lists

All of the above mentioned changes to the standard delivered JDA application may be intimidating to a customer and fears start to set in around cost and possible future upgrades.  Yet, when done correctly, the cost is justified by the ROI on the operational efficiency and the fear of an upgrade mitigated by employing the correct methodology.

Conclusion

The JDA application architecture with its flexibility of screen architecture, Java attributes in the RDT screens, and the entire MOCA architecture flexibility with overriding levels of command structure allows for extending the business application.  The notion that no two warehouses are alike bears strong resonance with this application architecture.  While controls are needed, and needless customizations avoided, never should a path be taken that compromises on operational efficiency in lieu of a “Customization”.  Simply the notion that a given change requires some code to be written vs. going to a screen and turning a flag on/ off should not be the differentiator between a customization and configuration.

“In a tailor-made service, you fit the cloth according to the size and taste of the customer; not just the taste and strength of the designer.”

JDA 2017 UI - The Good, The Bad and The Ugly

JDA'2 2017 version and its GUI rendering is leaps and bounds ahead of previous versions.  Both in its presentation and its capabilities.  However, this dramatic transformation comes at a huge learning curve, especially for those converting from previous UI experience.

For me, even the conversion to version 8.2 UI was a challenge (not able to find the Exit button anymore).  However, the 2017's Web Based UI is a complete transformation in experience and usability.

In this Blog entry, I will share my experience (not to say it is the final word), and hope that it can be a conversation point to invite comments and feedback to further enrich this entry.  Also note that the goal of this entry is not to challenge the vision behind 2017.  I am of strong belief that this change is for the better and user adoption will attest to this.

The Good:

I must say that JDA has invested quite a bit into the 2017 product.  To go from old thought process and to rethink into this new version, one must be humbled at the sheer magnitude of the endeavor.  So what did JDA get right in this release?  Again a disclaimer that I have not fully gone through this version so I will continue to edit this article as I dig deeper.

• WEB Availability - The Portal version is slick, crisp and fast.  There is no need for any components to be downloaded and the user experience is rich with many available widgets, especially the embedded charts in almost every screen
• UI Transformation - I personally like the way this version's UI is put together.  Someone moving from version 8.2, would find it less of a change.  From previous versions, this may be a tough learning curve.  The User's 360 view approach as Dashboards, to WMS functions is revolutionary
• Integrated Dashboards - This version has been envisioned with the end user in mind.  Thus the UI is rich with dashboards, charts and hyperlinks to allow the user to continue to drill down as needed
• Data Approach - The UI is envisioned with Data first.  The user is presented with the data based on the function the user initiated.  The user then refines the presented data as needed
• Switch between Warehouses - For Multi-Warehouse installations, the ability to quickly switch between warehouses is nice and efficient
• Switch between Workstations - This has been a limiting factor in previous versions where a Workstation was tied to a printer and if the Workstation needs to act as a different role, it was quite difficult to switch it.  In this version, you can simply switch to a new workstation and adopt all the features of that
• Complete Knowledge Base in Help - The Help Icon provides rich information that is very relevant and allows to bridge the learning gap quickly
• Page Templates Easy to Develop - The Page Templates (substitute for DDAs), are quick to develop and provide a quick and rich experience
• Re-Envisioning Trace - The new Tracing paradigm is a better approach and allows to trace the needed functions only by keeping the Tracing Window on top and allowing to interact with the application in the background
• Double Pipe (||) is still supported - The Double pipe to provide a list of limiting values in search is still support.  Yeah!
• Dashboards with Auto Refresh Option - Most Dashboards and Inquiry screens have the Refresh Option where the user can choose the number of seconds for Auto-Refresh
• Relevant Data in Menu Options - This is quite a nice feature.  For applications that will bring up data (like Inventory Issues as an example) the menu will show the user what record count to expect when the screen is launched.  Neat!
• RF in General Stays the Same - This is a saving grace because of the learning curve involved with GUI, that most of the user population in a warehouse, especially in case of an upgrade, will remain largely unaffected
• RF Receiving without Receipt Option - I know, that the Blog is about UI, but wanted to say Kudos for this new RF screen.  Great for bringing in inventory as receiving (and not to do through Inventory Adjust)
• Data Model is very Similar - Data model, other than the changes needed around Locations/ Area is largely unaffected.  This allows for creation of conversion scripts that can effectively convert an existing setup into 2017 model if needed (See Oracular's approach to an accelerated technical 2017 upgrade - Coming Soon!)

The Bad:

Disclaimer again: My enumeration of the following is based on my experience with the application and again as a Functional User.  This in no way is meant to take away from a User's experience as mentioned above in the Good Features of this version.

The features I am counting in "The Bad" list are features that make the application setup quite cumbersome.  Furthermore, even as an advanced user, some of the features will start to be a nag, especially if the user was comfortable in the previous versions with using the advanced features of the Grids etc.

• Nested Setup Screens - The setup requires a Functional User to go through nested hyperlinks for various setups.  This makes the setup part quite laborious.  In contrast if we can look back at the "Area Maintenance" with various tabs, it would feel like a luxury.  
• No Criteria Fields on Inquiry Screens - The new UI Paradigm is to use the search box for limiting your data.  A new user will find the absence of a "Find" button quite unsettling.  Utilizing the search box, seems to be a tedious way of doing searches, especially when a search is with multiple fields.  Furthermore, a user can easily make a mistake to pick a wrong field and has to start again
• Criteria Fields and the Ability for AND vs OR - A user can specify multiple criteria by using the search box.  If within these, the user needs to provide the condition of OR (instead of AND), the user needs to modify the condition manually.  However, this is limiting factor as the logical OR cannot be enclosed in parenthesis (and the expression is evaluated mainly from left to right)
• The Dashboards with Hyperlinks Do a Query First - Any Dashboards that are setup with Hyperlinks will go to a detailed screen and query the data right away.  The user needs to use the search box afterwards to limit the data.  This can become a problem in a live environment where users continue to click on (as an example open orders) and keep bringing up large amounts of data (A little bit of saving grace does exist that the grids are designed to only bring back 10 records initially)
• Dashboards with Auto Refresh Option - Most Dashboards and Inquiry screens have the Refresh Option where the user can choose the number of seconds for Auto-Refresh, which can be an issue in a live environment if a user sets up a refresh on a badly formulated query (or search criteria)
• Server Trace File Name - I have not found a way to name the server trace files to my specifications (If someone has found away, please feel free to comment and I will remove it from this list)
• Lack of Multiple-Screens - Any screen a user navigates to, replaces the existing one.  The user needs to make a conscious decision to invoke a screen in a new tab to achieve the multi screen/tab effect.  This can cause issues when trying to compare data between multiple screens
• New Naming Conventions - JDA has opted to change most of the naming conventions in this release.  This can be a huge learning curve for Upgrade Users.  The following is a list of commonly used ones where it will be challenging to relate to these terms.  There are others that are changed simply because of the new approach to things (like Pick Zones, Storage Zones, Location Types etc.) that need to be adapted to because it is a new version:
• LPN for Load/ Pallet
• Sub-LPN for Case
• HU for Asset (SAP users, watch out for confusion)
• Equipment for Trailer
• Inbound Shipments for Receipts
• Loads for Carrier Mobe
• Lack of Grid Features as compared to Previous Versions - Some of the Grid Features like saving your own views, Summary/ Grouping, and Grid Colors are not available.  Sorting is also a two step process, ability to see the native value of a code is not available etc.  Furthermore, the grid can show maximum of 150 records at a time.  Depending upon the screen, this may end up to be a limiting factor

The Ugly:

Mainly, I had to use this for reference to the old Western with the same name.  However, my classification for this list is where I am worried, the application's new approach to some data management can lead to issues in a live environment if it is left unchecked.  So mainly a word of caution for the reader that this list is comprised of items that I see as a potential to do harm, and not necessarily bad by themselves:

• Location Mass Update (and other Mass Updates) - This is a necessary screen for doing a lot of setup, especially because many setup screens are nested in nature, so you would need to come back to Locations to do updates again.  However, I am concerned that the way one enters a screen from the menu options (Like Storage Locations), can lead to potential update of more locations than needed.  A user must first further filter out the unneeded locations before applying the change, rather than starting with the correct set of locations
• Switch between Fat Client and Web Client - Since all screens are not converted to Web, JDA has taken the approach to not provide the Web screens on Fat Client.  This means a user needs to jump between two UIs for doing their job. So not a huge concern, but can be for a specific role like Pack Station User
• Data not refreshed quickly - I have noticed on some screens (especially nested ones), that the data is not refreshed quickly.  In some cases I had to reconnect to see data changes.  Could be a quirk in the Web Layer, but can be troublesome if the user cannot identify quickly what the issue is
• Search Box as the Only Search Tool - This one I listed above in the Bad list, but I am concerned mainly base don my experience with previous versions and users ability to interact with the application.  While I can save my criteria and use them, the interaction is tedious based on entering a value and then searching from the list to apply this value to.  While experienced users will take advantage by continuing to type their search, a novice user will be spending more time with this interaction.  This one, I am hoping I can move from this list to the Bad or even the Good features list, its just too soon to tell (Please leave feedback for your own experiences)

Conclusion

It is too soon to draw any conclusions on this product and its UI.  As stated in the beginning, when I moved to version 8.2, I was missing my Exit button and hated the idea of all tabs to go from left to right.  I remember when RedPrairie first introduced the Multi-Warehouse in its dictionary and how many bugs were introduced in the product as a result.  So I must say that this version, given the amount of changes there are, in UI, in Dictionary, and in Commands, that this is a very stable product.  The UI features, I am chalking off to learning curve and my own prejudices of previous versions.

All in all, I am hoping to get some feedback on this to see how others feel about this and hoping to alter my list based on all your feedback.

MERGE/ USING - A beneficial SQL Statement

Often times in our JDA commands, we are faced with doing an INSERT, UPDATE or DELETE based on data in a table as it relates to another.  For example, I am loading Inventory Data into a transaction table to be posted for polling.  I possibly have the following scenarios:

• If my Inventory record exists in the destination table, then update it
• If the record does not exist, then insert it
• Possibly, if the record exists, but my inventory is in a different status (depends on specific use case), then delete it from the destination

To implement this logic, a MOCA Command would be something like this (pseudo code):

[select data from destination table where pk=@lodnum] catch(-1403)

|

if (@?=0)

  if (@data has wrong status)

    perform my delete of destination table

  else

    perform my update of destination table

else

  perform my insert of the destination table

This in itself is not difficult to follow from coding perspective.  However, we do have three different sets of code branches to manage and maintain.  The MERGE/USING statement (both SQL and Oracle support it with slight differences in usage and syntax) allows for all three possibilities to be encompassed in one statement.

The pseudo syntax on MERGE/USING is as follows.  The statement was first introduced in SQL 2008 and Oracle 9i. For specific syntax and usage as applicable to your needs and database, please consult the relevant Database Documentation.

MERGE <destination table> 

USING <source table> /* Can be a select */

ON ( key columns )

WHEN MATCHED <and extra clauses>

  THEN UPDATE....

WHEN NOT MATCHED <BY TARGET or SOURCE>

  THEN INSERT....

You only have options of MATCHED and NOT MATCHED with extra qualifiers to implement the possibility of a DELETE statement.  Also note that UPDATE and DELETE statements can be provided at the same time under the MATCHED clause.

The benefit is that using one SQL statement, all three DML operations can be performed.  From performance standpoint, we have one cursor open that is traversing the source table and doing operations in the destination table in one go.  Rather than doing multiple open/ close cursors inside a loop.

Please note that MERGE statement is optimized for merging a SET of data and not for Single Row operations.  In a multi-row SET, the performance gain of a MERGE/USING statement can be almost 15 times, that of doing the same operations as INSERT/UPDATE.  So it is best to use it when you have a need of a larger set of data to be merged with another table.  Also note that the entire MERGE operation is one DML statement and hence one COMMIT context.

Hope you can find a use case appropriate for this statement.

Utilizing MOCA Query Hooks

RedPrairie/ JDA introduced Hooks in MOCA Toolset.  The Hook being described here is referred as the “QueryHook” (See MOCA Developer Guide for details).  This feature allows to look for a given SQL before it is sent to the database engine and manipulate the SQL itself or possibly its result set.

Caution: As mentioned in the MOCA Developer’s guide also, “This hook should be very heavily evaluated before its usage is implemented”.  As certainly a wrong implementation can have unpredictable results.

Oracular has introduced a solution that simplifies this process and allows a client’s development team to change an SQL at run-time to their advantage.  This solution takes the complexity out of writing a new Java method for the QueryHook and instead provides a simple Text File where Query Patterns can be stored.

Why do we need to change SQL this way?

JDA MOCA platform allows great flexibility in providing mechanism for overriding standard functionality and still keeping the upgrade path open.  However, many times the SQL performance of a given query requires it to be changed.  Introducing an index may not yield the performance plan that one is looking for and the only viable option is to be able to rewrite the query.

If the Query happens to be in a Local Syntax command, the user can easily copy the command to an LES folder and change the SQL to their liking.  In doing so, they have closed the door to this command being fixed by JDA (as this is a complete override), but the solution and performance goals have been achieved.

Many times however, the SQL that needs to be tuned is inside some C/ Java program or part of RDT code.  These SQL commands can only be manipulated using the QueryHook option.

Oracular's Solution

MOCA QueryHook can be implemented by anyone with knowledge of Java and they can write their own Moca Hook to take advantage of this.

Oracular provided a simple usage for this for our developers as well as our clients where a file sitting in $LES\data folder contains the Query Pattern to look for and its replacement.  This allows for a simple process where a new SQL pattern can be introduced with much ease.

The following section will explain how to use the Oracular Solution.

Basic Components

Anyone needing to take advantage of this feature from Oracular will need the Mocahooks.jar file to exist in their $LES\lib folder and query_replacements.txt file to exist in their $LES\data folder.

Structure of Query Replacement File

The Query Replacement File has three components for each SQL that needs to be manipulated:

• Leading edge of the SQL to search for – This must be an exact match.  This string does not allow for any regular expressions for white space.  So, number of spaces, new line characters etc. are all important to be specified exactly
• Regular Expression String to match the rest of the SQL – This Regular expression allows to match and parse the rest of the SQL.  This expression serves two purposes:

o   Fully qualify the SQL

o   Find Groups of data inside the SQL to be used during the replacement (important concept that will be explained in the example illustration)

• SQL to replace with – This is the new SQL that is executed instead.  The Groups identified in the search can be used in the replacement SQL

The use and need of replacements is generally performance related.  However, it can be used to manipulate result set if needed.  However, if result set is being manipulated, be sure to verify that downstream processing can handle any changes to the result set.

Regular Expressions

The search string uses Regular Expressions (Regex) for searching through the SQL string.  Please note that Regular Expressions are used in many variations and have unique structures and implementations based on the usage.  For our purpose, only the Java Regular Expressions can be used (not Posix, PHP etc.)

The following are the commonly utilized expressions for our purpose:

• (?i)  - Starting the expression with this, specifies to ignore the case during the search
• (?s) – This modifier allows to ignore New-Line when searching for any character
• If both of the above modifiers are needed then only one ? in the beginning is needed (?is)
•  ^ - Asserts position as the start of the string
• \s+ - Look for white space.  But at least one.  Commonly this will be used before SQL key words as there needs to be at least one space before them (like from or where)
• \s* - Look for white space.  None is also OK
•  \( or \) – Parenthesis are part of Regex.  So if you need to specify a parenthesis in your expression, always precede that with a \
• (.*?) – Look for a group of characters.  Any character can match in this and whatever matches becomes a group (see below on using groups).  Groups are numbered from left to right.  So the first instance of (.*?) is group one ($1), next is Group two ($2) and so one
• .* - Look for any character to match
• Anything else mentioned in the expression is taken as is.  So, keywords like select, from, or operators like = etc. are to be specified as is

Please note that this is just a brief list of commonly needed search operators.  Based on your specific need, you may need to use more.

If you need to validate your expression, you can use the site below:

https://regex101.com/

In this site you can provide your regular expression string in the first field and the select statement you are searching for in the next field and it will verify for you if the match is going to occur or not.  Furthermore, it provides all the groups for you also.  See illustration below.

This screen also shows the explanation of your expressions:

Notice Group 1 is what the expressions was looking for first as (.*) and it is also color coded for your convenience.

Generally speaking the groups are used for all variable data that may possibly come in the SQL string.  As this data cannot be forecasted ahead of time, so the search string must be able to ignore it during the search, but still use it later when rebuilding the new string.

So, in this illustration the group data involves part of the SELECT statement and the variable data in the WHERE clause.

SQL Statement from the Trace File

It is very important to find the proper statement in the Trace file for utilization in the QueryHook.  JDA trace files show an SQL statement in three different forms:

• The original form based on the MOCA command sending it
•  The XLATE modification where Bind Variables are introduced
• The modified statement set to the SQL engine

For QueryHook purposes, we must utilize the XLATE version of the SQL.  Any other version may not properly yield a good search pattern and match.  The following is an illustration of how SQL takes different forms in the trace file:

Original SQL:

Executing SQL: select distinct          lodnum,          decode('lodnum', 'subnum', subnum, 'dtlnum',                 subnum, 'x') subnum,          decode('lodnum', 'dtlnum',                 dtlnum, 'x') dtlnum     from inventory_view     where (('lodnum' = 'lodnum' and lodnum = :var_lodnum)              or ('subnum' = 'lodnum' and subnum = :var_lodnum)              or dtlnum = :var_lodnum )

XLATE Version:

XLATE: select distinct          lodnum, (case when :q0 = :q1 then  subnum when :q0 = :q2 then                  subnum else  :q3 end) subnum, (case when :q4 = :q5 then                  dtlnum else  :q6 end) dtlnum     from inventory_view     where ((:q7 = :q8 and lodnum = :var_lodnum)              or (:q9 = :q10 and subnum = :var_lodnum)              or dtlnum = :var_lodnum )

UNBIND Version Sent to SQL Engine:

UNBIND: select distinct          lodnum, (case when 'lodnum' = 'subnum' then  subnum when 'lodnum' = 'dtlnum' then                  subnum else  'x' end) subnum, (case when 'lodnum' = 'dtlnum' then                  dtlnum else  'x' end) dtlnum     from inventory_view     where (('lodnum' = 'lodnum' and lodnum = '00100538930017771875')              or ('subnum' = 'lodnum' and subnum = '00100538930017771875')              or dtlnum = '00100538930017771875' )

Notice that the original version of the statement has a DECODE in it.  Then XLATE converted that to CASE as this was being passed to SQL Server, and then the UNBIND version added all the values for bind variables.

Illustration Example

For sake of illustration I will share two examples.  In Microsoft SQL, there is a common issue where any statement with OR does not yield a good query plan.   This statement if converted to a UNION provides a far better execution plan and performance.  Hence any such instances of SQL statements will be good candidates for the QueryHook.

Converting an "OR" to a "UNION"

The SQL mentioned above is the illustration for converting the OR statement to a UNION.  However, to keep the SQL conversion database independent, the search string did not want to assume that a DECODE will be converted to a CASE statement.  So, the search pattern is as follows:

select distinct          lodnum

$$$$$

(?is)select\s+distinct\s+lodnum\s*,(.*)from\s+inventory_view\s+where\s+\(\(\s*(.*?)\s*=\s*(.*?)\s+and\s+lodnum\s*=\s*(.*?)\s*\)\s+OR\s+\(\s*(.*?)\s*=\s*(.*?)\s+and\s+subnum\s+=\s+(.*?)\s*\)\s+OR\s+dtlnum\s+=\s+(.*?)\s*\)\s*$

$$$$$

select distinct lodnum,

$1

   from inventory_view

  where ($2 = $3

    and lodnum = $4)

  union

 select distinct lodnum,

 $1

   from inventory_view

  where ($5 = $6

    and subnum = $4)

  union

 select distinct lodnum,

 $1

   from inventory_view

  where dtlnum = $4

Explanation

• The first line is the pattern that needs to match exactly

o   Notice the number of spaces between distinct and lodnum

o   This needed to be specified as this because the XLATE version illustrated above shows in the same manner

o   Hint: Copy the text directly from the Trace file and place it here

• After the $$$$$ comes the second portion of the QueryHook where the SQL is parsed based on the Regular Expression provided

o   It starts with (?is) to specify to ignore case during the pattern matching and ignore new line

o   After looking for “select”, it says \s+ meaning there should be at least one white space but more is OK

o   After “distinct” it does the same to say \s+ until “lodnum”.  Now that we are using regular expressions, we don’t need to worry about all the spaces between “distinct” and “lodnum”

o   After that we have \s* till comma.  So, it can be any number of white space, including none

o   Then comes the first group (.*).  Note that any search criteria when enclosed in () becomes a group.  As mentioned before, this QueryHook did not need to worry about DECODE vs. CASE, so it is saying that after the “lodnum,” till “from”, any text is fine.  However, I want that text to be in my group ($1 in this case), as I want to use it in my Query Rewrite

o   After this, it looks for “From”, then “inventory_view”, then “where”

o   After that it starts to look for more Groups of data, as the XLATE was showing that these are possible bind variables.  We need to make sure that the query will match, regardless of how the parameters are passed to it.

o   The Search Pattern terminates with $

• After the next $$$$$ comes the new SQL

o   In this section, you are allowed to write any valid SQL statement

o   It may or may not have relevance to the original SQL (as illustrated in the next example)

o   However, in most cases you will be rewriting the query to yield similar result set, except in a faster manner

o   So, in this illustration the OR was turned into a three-way UNION

o   The first part of the UNION utilizes the first part of the OR, and so on for the next two portions

o   Important part is the utilization of the groups:

§  Note that $1 was all the text matched after lodnum, till the “from”.  So, in our rewrite we use it as is

§  The rest of the groups need to be utilized properly when the UNION is being expanded so the proper group is utilized where needed

§  Since the original statement only provides the value for the Load Number first in $4, it gets used throughout.  However, the same statement could have been written as:

·       Subnum = $7

·       Dtlnum = $8

After this QueryHook is introduced, the Trace Shows the Following:

Converting a statement to a Completely Different One:

Another possibility is that a problem statement due to performance, needs to be rewritten, but the functionality is not being used in your respective implementation.  So even though you could go through the exercise of a rewrite, it will still take precious seconds to execute, yet to produce no benefit.

So, in this case, simply returning a result set from DUAL yields a faster execution time.

Caution: This use should be utilized with extreme care, as turning the functionality on in the future will not work as we have changed the statement completely using the QueryHook

select distinct trnsp_mode.palletctl_flg

$$$$$

^select\s+distinct\s+trnsp_mode.palletctl_flg\s+from\s+pckwrk\s*,\s*shipment\s*,\s*stop\s*,\s*car_move\s*,\s*cardtl\s*,\s*trnsp_mode\s*,\s*wrkque\s+where\s+shipment.ship_id\s*=\s*pckwrk.ship_id\s+and\s+shipment.stop_id\s*=\s*stop.stop_id\s+and\s+stop.car_move_id\s*=\s*car_move.car_move_id\s+and\s+car_move.carcod\s*=\s*cardtl.carcod\s+and\s+\(wrkque.wrkref\s*=\s*pckwrk.wrkref\s+or\s+wrkque.list_id\s* =\s*pckwrk.list_id\)\s+and\s+wrkque.wh_id\s*=\s*pckwrk.wh_id\s+and\s+wrkque.wh_id\s*=\s*(.*?)\s+and\s+wrkque.reqnum\s*=\s*(.*?)\s*and\s*trnsp_mode.trnsp_mode\s*=\s*nvl\s*\(car_move.trans_mode\s*,\s*cardtl.cartyp\)$

$$$$$

select  0 as palletctl_flg from dual

Explanation

• The first line shows the predicate to match
• The line after the $$$$$ has the regular expression to match.  This is complex SQL so the regular expression is quite long.  However, with the knowledge of each search patter it can be understood
• After the next $$$$$ comes the replacement SQL.  Notice that this is simply being replaced with a select from DUAL.  We did make sure that the correct column is returned so the result set is not compromised for the downstream logic.  But how the 0 was returned did not matter and since Pallet Control logic is not being used, this rewrite yielded the best performance in this case.

Conclusion

The purpose of this blog entry was to illustrate to the reader the important and useful feature of MOCA and the available QueryHook.  Even though that caution must be exercised, this provides a valuable way to fine tune standard SQLs which JDA may not even be able to provide a fix for since the given SQL may only be working poorly in your specific case only.  This was the purpose of the two illustrations also, to emphasize that these SQLs would not be changed by JDA as they are optimized from their perspective. 

New SQL Functions to aid in Reporting

SQL Language (as well as MOCA) has steadily improved adding a host of new functions that can make many of the reporting requirements quite easy to handle.  In this blog, I will showcase the use of the following functions.  Please note that my reference point in this blog is Oracle Database.  However, where appropriate I will provide information appropriate for SQL Server.

• LISTAGG (STRING_AGG in SQL)
• PIVOT (Same in SQL)
• NTH_VALUE (no corresponding function in SQL)
• ROLLUP (Same in SQL)
• CUBE (Same in SQL)
• In Memory (Internal) Table in MOCA

I will leave the reader to lookup the exact syntax for these functions.  The purpose of this blog is to simply provide possible use cases for these functions and point the reader to the value they provide.

While MOCA language may provide some possibilities into calculating these (like the LISTAGG using column results to string command), there is real value and benefit when the task of producing the correct result set is passed to the SQL engine and thus reduce the complexity of code and improve performance.

LISTAGG:

This function can return a list of values concatenated together within a GROUP BY.  Often times, we are challenged to provide all the distinct values within a group, but not list them in multiple rows.  For example a use case can be showing all locations from where a shipment is getting picked from.  Traditionally I would have a select statement to list all the shipments from PCKWRK. Then have another select to get the SRCLOC, move it to RES and then use the "column results to string" MOCA function to get a result string and publish the entire result set.  But using the LISTAGG, I can do all that within one SQL statement.  An example is given below for reference:

[select ship_id, 

  listagg(srcloc, ',') within group (order by srcloc) loclist

 from pckwrk_view

 group by ship_id]

PIVOT:

A common need in reporting is to show data across in multiple columns, based on a group of data.   Again taking PCKWRK as a data set, our need is to show number of picks coming from each area for all shipments.  This requires all SRCARE to show as columns in the report.  Using the PIVOT function, this becomes quite easy.  The following is an example illustrating this:

[select '''' || arecod || ''' as ' || arecod arelist from aremst a 

  where pckcod != 'N' 

  and exists (select 1 from locmst b where b.arecod=a.arecod and b.pckflg=1)]

   >> res

|

convert column results to string 

 where res = @res and colnam = 'arelist' and separator = ','

|

[select * from (

select ordnum, srcare, pckqty

from pckwrk_view

where pcksts='R'

and appqty=0

and adddte > sysdate-30)

 PIVOT ( count(*) cnt, sum(pckqty) pckqty for srcare in ( @result_string:raw ) )

]

NTH_VALUE:

This is an interesting function which I am struggling to find a good use case for illustration.  However, I am sure that someone would have come across a need, so please provide feedback if you find this useful.  This function will return the NTH value (first, second...) within a group.  So if the need is to show the second value based on the sortation criteria of the group, then this function will be useful.  For my illustration I came up with a requirement where I need to show my PCKQTY as a relation to top 2 PCKQTYs in my wave.  While the TOP 1, I can easily get as a MAX, the second one is not possible (at least easily) without the NTH_VALUE function.  Note the I am purposely using different ORDER BY values to illustrate the benefit:

[select schbat, ship_id cnt, pckqty, pckqty/nv1*100 pct1, pckqty/nv2*100 pct2

from (

select schbat, ship_id, count(*) cnt, sum(pckqty) pckqty, 

   nth_value( sum(pckqty), 1) over (partition by schbat order by sum(pckqty) desc

      rows between unbounded preceding and unbounded following ) nv1,

   nth_value( sum(pckqty), 2) over (partition by schbat order by sum(pckqty) desc

      rows between unbounded preceding and unbounded following ) nv2

from pckwrk_view

group by schbat, ship_id

having count(*) > 10

) 

order by 1,2,4]

ROLLUP:

A common need in reporting is doing Sub-Totals at a secondary group field.  While we can make needed design setup in a proper reporting tool, it can be tedious in simple SQL and when doing prototyping.  Furthermore, having access to ROLLUP as part of SQL language makes many of the reporting tasks (especially a DDA) quite easy.  Staying with the PCKWRK data as example, the following query illustrates getting a partial sum by each SCHBAT and then getting a grand total in the end:

[select schbat, ship_id, count(*) cnt, sum(pckqty) pckqty

from pckwrk_view

where wrktyp='P'

group by rollup(schbat, ship_id)

order by 1,2,4]

CUBE:

Similar to ROLLUP, except it will do all possible combinations of the group fields (2 to n, where n is the number of columns) and provide their totals.  The benefit of CUBE comes into play when you have more than 2 columns in the GROUP BY to see different combinations play out.  For example in the example below, we are grouping data from PCKWRK for SCHBAT, SHIP_ID and SRCARE.  So one set of summation is at SCHBAT, SHIP_ID and SRCARE level,  but then CUBE will also show a combination of SCHBAT and SRCARE, and SCHBAT and SHIP_ID as the extra set (ROLLUP was only going to provide SCHBAT and SHIP_ID):

[select schbat, ship_id, srcare, count(*) cnt, sum(pckqty) pckqty

from pckwrk_view

where wrktyp='P'

group by cube(schbat, ship_id, srcare)

order by 1,2,4]

In Memory (Internal) Tables in MOCA:
The use of this data structure for Integrator use is explained quite extensivley in Saad Ahmad's blog (http://saadwmsblog.blogspot.com/2015/12/integrator-in-memory-database-makes.html).  However, I wanted to share their use for reporting.  The challenge we often face in reporting is that the data being put together does not match in keys and dimensions.  Trying to accomplish this feat by joining brings the performance.  Furthermore, there is a challenge where the grouping of data across different tables does not match.  These are the scenerios where loading data into an Internal Table and then doing Group BY/ Order BY on that data can help solve the problem and achieve the solution with better performance.

For illustratration purposes I will use an example where I need to get the data from Daily Transaction (DLYTRN) table.  I am trying to find all movements of inventory for a pallet and device ID, but only show the first from location and the last to location in the report (by Pallet/Device).  Also the last location needs to be based on Transaction Date.  You can see that simple MIN/MAX on FRSTOL or TOSTOL cannot work here.  So the Query had to be broken into multiple Select statements.  However, the challenge was then to still show the data organized and grouped in its own dimensions.  Using the Internal Tables with GROUP  BY and ORDER BY allowed to solve this issue (please note that the following code is for illustration purposes to explain the problem with a working solution):

[select min(trndte) trndte, usr_id, frstol, prtmst_view.dsp_prtnum,
   prtmst_view.typcod, dlytrn.lotnum, dlytrn.lodnum, sum(trnqty) trnqty, devcod
from dlytrn 
 join prtmst_view 
on prtmst_view.prtnum = dlytrn.prtnum 
and prtmst_view.prt_client_id = dlytrn.prt_client_id 
and prtmst_view.wh_id = dlytrn.wh_id
where fr_arecod=@frarecod 
and dlytrn.wh_id=@wh_id
and (dlytrn.trndte  >= @st_dte:date or @st_dte is NULL)
and (dlytrn.trndte  <= @ed_dte:date  or @ed_dte is NULL)
and @+dlytrn.usr_id
group by usr_id, frstol, prtmst_view.dsp_prtnum, 
  prtmst_view.typcod, dlytrn.lotnum, devcod, dlytrn.lodnum]
|
[select tostol tostol
from ( select tolstol from dlytrn 
       where trndte > @trndte:date
       and dlytrn.wh_id=@wh_id
       and devcod=@devcod and lodnum=@lodnum
       and rownum < 9999
       order by dlytrn_id desc )
where rownum < 2
]
|
if (@tostol)
  filter data where moca_filter_level = '2' and @+tostol
} >> my_res
|
{
  release internal tables
  ;
  create internal table
   where table_name = 'uc_my_putaway_detail'
   and res = @my_res
  |
  select from internal table
   where select_stmt ="select min(trndte) trndte, usr_id, frstol,
     dsp_prtnum, typcod, lotnum,lodnum, sum(trnqty) trnqty, tostol"
   and table_name="uc_my_putaway_detail"
   and group_by_clause="group by usr_id, frstol, dsp_prtnum, 
                                 typcod,lodnum, lotnum, tostol"
   and order_by_clause="order by usr_id,trndte"
}