Showing posts with label jda. Show all posts
Showing posts with label jda. Show all posts

Wednesday, September 5, 2018

RedPrairie/JDA RF Solution for the 21st Century

Overview

All Redprairie WMS implementations need an RF Solution - a solution for physical devices that are used on the floor to perform most of the warehouse functions.  Man Hours spent on these devices far exceed those on GUI screens.  But the solution for these devices looks the same as it did a decade ago.  Internally the infrastructure evolved to a Java based telnet emulation but from the point of view of an end user it has not improved much.

At Oracular we have been working with RedPrarie systems for a long time and we understand the pains and opportunities in such implementations.  Improving the RF Solution is part of that.

We have identified following areas as opportunities that we have addressed in our new solution.
  1. Connection Setup
  2. Touch Support
  3. Screen Layout
  4. Additional Information
  5. Voice Support

Solution Overview

Our solution is an Android app that is an evolution of our earlier solution.  While that solution was from the point of view of ad-hoc use - here our vision is to provide a solution for the warehouse floor.  As several customers are now choosing Android platform - the solution is a natural fit.

Whereas the standard RP solution is a thin telnet emulation - our solution builds on top of that.  All of the delivered and custom RF screens would work without any tweaks - but at the same time the solution can be enhanced by providing additional contextual information to the user.

In order to make it happen, our solution makes a direct connection to the MOCA server so that it can access it and also keeps a telnet window open.  It also utilizes advanced screen scrapping and keyboard manipulation to streamline the data management.

Salient Features

Following is a list of some key features that we have incorporated in our solution.

Connection Setup

A typical warehouse may have hundreds of these devices and the typical users are not technically savvy.  So maintaining these becomes a chore.  Each device needs to have some setup done for following reasons:
  • Define environments that it can connect to.
  • Identify it individually to the application so that DEVCOD variable is set properly
Environment management can become a major headache for system administrators.  Updating the information on several devices is time consuming.  Using the same device against multiple environments or re-purposing a device is not straight forward either.

Our solution solves this problem by defining a single URL for all connection details which will remain the same for all devices.  All of the changes are then done on that single location.

Profile
First we have a concept of profiles.  These are maintained by a simple XML file and these point to the various MOCA environments that the devices may connect to:
<ossi_rf_emul_profiles>
<profile name="env_1">
<app_url>http://10.10.10.10:8110/service</app_url>
<prompt_user_id_first>1</prompt_user_id_first>
<short_name>AC</short_name>
</profile>
<profile name="env_2">
<app_url>http://10.10.10.20:8110/service</app_url>
<prompt_user_id_first>1</prompt_user_id_first>
<short_name>AD</short_name>
</profile>
</ossi_rf_emul_profiles>
This defines following:
XML TagValueComments
nameArbitrary ValueProvide a name of this environment
app_urlMOCA URLURL that connects to this environment
short_nameArbitrary ValueAbbreviated name of the environment to show on the emulator
prompt_user_id_first0 or 1Ask for username and password after connection

Profile_dtl
In the same location, we will have profile detail XML files for each of the profiles defined above.  The XML file is named profile_dtl_name.xml.  For example:
<ossi_rf_emul_profile>
<setup name="env1">
<mtf_url>10.10.10.10:8112</mtf_url>
<short_name>AC</short_name>
<wh_id>WMD1</wh_id>
</setup>
</ossi_rf_emul_profile>
This defines following:
XML TagValueComments
nameArbitrary ValueProvide a name of this environment
mtf_urlMTF URLHost and port of the MTF telnet connection
short_nameArbitrary ValueAbbreviated name of the environment to show on the emulator
wh_idWarehouseAs defined in wh table


When our emulator starts, user selects the appropriate profile and then profile detail.  With this input we know the application URL, MTF URL, and  warehouse Id.  The application will then prompt for username and password for the application URL.  This will allow the emulator to directly connect to the MOCA server and at the same time open a telnet connection to the MTF server.

Device Identification
In order to identify each physical device to the application server RedPrairie has used various techniques over time including:
  • Requiring fixed IP addresses and using last three digits
  • Utilizing "answer back" and then responding with a value from the device.
Both can work but are not easy to setup.  Fixed IP addresses are not suitable at all these days.  Answer back works but requires per-device configuration.

Our solution in this case is to support these legacy concepts and extend it further to make things simpler.  Each physical device provides several interesting identifiers that can be utilized for example:
  • IP Address (if fixed IP address)
  • Bluetooth Id
  • MAC Address
  • Serial Number
MAC Addresses and serial numbers are especially interesting since they are often known from the product packaging and are already set.  In RedPrairie we already have a concept of defining each device as "RF Terminal" using "RF Terminal Maintenance"

As you can see here rather that utilizing "answer back" we define one of the following attributes for each device - and while defining we can use wild cards:
  • IP Address
  • Bluetooth Id
  • Mac Address
  • Serial Number
So in our solution when the "RF Terminal Id" prompt shows up on the emulator, we automatically key in the corresponding terminal id.


Touch Support

Some hardware devices are quite compact where keys are quite small and typing can be tricky.  Our emulator provides touch support.  This comes in handy for situations like:
  • Selecting menu options
  • "Pressing" Function keys

Screen Layout Enhancements

15 years ago defining an RF screen in terms of lines and columns of characters made sense but today these devices have high resolution screens - so in reality a lot of usable real estate is wasted.  Our solution maximizes the real-estate in several ways:
  • Function Key pallet is displayed on left side - making it easier to manipulate them.  Refer to the images displayed above to see the function key pallet.
  • Soft Keyboard support.  Refer to the image above to see how the soft keyboard appears and disappears
  • Automatically Zoom In and Zoom Out to maximize the use of real-estate.  Refer to the image above to see how screen automatically zooms in and out based on the soft keyboard
  • Top of the screen provides connection details like environment, user, etc.

  • Bottom section of the screen can provide additional contextually sensitive information (described below)

Additional Information

RedPrairie standard RF screens often need to be complimented with additional information.  They do not provide simple ways of providing that so sometimes users would switch to alternate front end with GUI, or have reports.  Our approach in this case is to utilize the full capabilities of the devices and allow users to extend the view with additional information based on the context.  For instance if a pick directs you to a location we could show the inventory in the location along with its attributes like lot, and origin.  We can show multiple pages and the information provided can be different for different users.  The information could be from external sources as well, e.g. ERP or external websites.

This is defined by users themselves and is not a development task.

LES Command Maintenance
We use the les_cmd table to store the commands that are used for this purpose.  This allows us to make changes without needing "mbuild" and thus avoid rollout requirements.  When we execute these commands we always execute the highest level commands - thus providing a mechanism for overriding them as well.

If we want to show a URL then the command returns a special variable called "url":


We have two types of commands - the ones that show data (as shown above) and others that detect the context.  These commands always return one row and one column with a value of 1 or 0:

Additional Information Definition
Additional RF Form Information is defined as a collection.  Several pages may be defined in one collection.  The collection also describes how it fetches the context from the RF form that is running.  The screenshot below indicates that "wrkref" is scrapped from the screen to establish context.  Then three pages will be displayed below the form for additional information.  Each "page" has associated command which would return the data.


Setups
We have defined the various page groupings above.  To use them in a specific context we use the concept of "setups".  Several setups can be defined for an RF form.  Each setup has an associated "condition command" and "Additional Information Id".  The first setup where the conditional command returns true will be used.  This concept can be used to display different types of additional information on same RF form.  For example we could show different data based on type of user or other context information.  A supervisor could, for example, see the inventory levels in the system while doing counting but other users will not.


How it all comes together
When a user goes into an RF form, we check if this form has a setup which is active based on the pre-condition defined.  If there is one we then find the various pages and the associated commands.  We utilize the bottom section of the RF form to show this data where various pages are displayed as tabs and data is displayed in a grid.

Voice Support

If devices support voice, the RF emulator can utilize it as well.  It can speak the form name and the field name.


Conclusion

We have been working with RedPrairie WMS for a long time and understand the intricacies of the architecture.  We also understand the deployment framework and use cases.  This allows us to appreciate the gaps and this solution is based on years of experience in deploying these solutions in various industries and environments.   Once deployed users will appreciate the new capabilities which will result in ease of use and improvement in quality and throughput.  The system support will also become easier.

If you want additional information about this solution or any other Oracular solutions or initiatives contact me at saad.ahmad@oracular.com.  Visit us at http://www.oracular.com


Tuesday, March 13, 2018

Is Archiving the way to deal with old data in 2018?

Overview

JDA/RedPrairie WMS requires customers to have a data archival strategy. The approach that they push and most customers end up adopting is akin to using a sledge hammer to push a nail.

The JDA approach is quite interesting and probably an appropriate mechanism if the year was 1995. Their approach is to say that the data will be archived to an identical environment from the point of view of software but that other environment will have older data.  This is a very interesting paradigm because all of the reports, queries, use cases, etc. that we may have will work in exactly the same way in the archive environment - all we have to to validate is that the data from the current system is properly moved to the archive instance.

Issues

So what is the issue with above approach?

  • We are adding another environment to support.  So lets say we have 5 production environments (for sets of warehouses) - we really need 5 additional environments for archive.  Then to support testing and development we will have 10s of additional environments.  If we have some related environments like EMS, parcel, report writer, etc - those may be needed as well.
  • These additional environments need to be part of the software development life cycle.  All rollouts need to go to these environments as well.
  • We create "cut off" points, i.e. some queries would need to go to multiple environments.
  • Some advanced use cases like "recalling" and "returns" may need overly complex solutions where we may need to query archive and online instances.
  • An additional point of failure for daily maintenance.

What are my options?

So do we have any options?  First we really need to check the calendar.  What we considered as "too much data" in 1995 will be considered a "below average database size" today.  Secondly we need to understand that number of rows in a table does not cause an issues - it is the access path.  Thirdly, today's databases have advanced features like partitioning that render such complex strategies obsolete.  In this article, I will focus on solutions available in Oracle - but please note that SQLServer has similar concepts and features.

First let us firmly establish the problem we are trying to solve.  The size of the table is not a measure of performance - it is always the access path - so what we are really trying to address is data management not performance.  Another "problem" that archiving solution supposedly addresses is that queries against the archive instance will not adversely impact the online system's performance.

Data Management - Partitioning

The solution that is most promising for data management problem is to utilize advanced database feature for "partitioning" and let the data stay in the online system.  This implies that your single online instance will have all of the data all the time.  Dispatched shipments will simply be in a different partition.

Ideal partitioning solution will be to create the main partition by range (or in new versions interval partitions).  Then have a sub-partition by warehouse.  That will result in manageable data in each partition.  This partitioning strategy provides good information to the database to parallelize queries.

Data Isolation - Reporting Database

The other problem of providing an isolated environment for queries is easily addressed by utilizing the concept of stand-by database or "data guard" or "reporting databases".  Please refer to the database documentation - but what it provides is a completely isolated database instance that is "almost" up to date and can be used for reporting.  The setup is low level DBA activity and requires little application support.

But this may not be needed for majority of the scenarios.  Such a database should be made available for true research activities where we will not be using the RedPrairie/JDA client to access the tables.  All use cases where we need to access the data through application should be handled by connecting to the online instance and letting database handle the large amounts of data.

How should I partition the data?

To effectively partition we need to have a good date column on the table and for warehouse sub-partition warehouse id should be in the table.  Unfortunately some tables in JDA WMS do not have these columns so small modifications are needed to make it all come together.  

In the following section I have listed the tables and suitable partitioning keys.  Following enhancements should be done to make it all work:
  1. When "shipping trailer" dispatches, update following tables with uc_trndte set to dispatch date/time.  And if table does not have a warehouse id, then we set uc_wh_id column.
    • trlr
    • car_move
    • stop
    • shipment
    • shipment_line
    • ord
    • ord_note
    • ord_line
    • ord_line_note
    • oub_serv_ord
    • oub_serv_ord_line
    • pckwrk_hdr
    • pckwrk_dtl
    • pckmov
    • invlod
    • invsub
    • invdtl
    • locmst
    • manfst
    • shp_dst_loc
    • sp_hand_ref
  2. When "receiving trailer" dispatches, update following tables with uc_trndte set to dispatch date/time.  And if table does not have a warehouse id, then we set uc_wh_id column.
    • trlr
    • rcvtrk
    • rcvinv
    • rcvlin
    • locmst
    • inb_serv_rcvinv
    • inb_serv_rcvlin
  3. As a general rule - use base 36 sequences
So the overall partitioning strategy will end up as follows (not including work order tables)
Table Partition 1 Sub Partition Online Data Mod Needed
Transaction History Tables
dlytrn trndte - month wh_id - list No separate concept
trlract trndte - month wh_id - list No separate concept 
ordact trndte - month wh_id - list No separate concept
invact trndte - month wh_id - list No separate concept 
cnthst adddte - month wh_id - list No separate concept 
wrkhst issdte - month wh_id - list No separate concept
canpck candte - month wh_id - list No separate concept
pko_act ins_dt - month wh_id - list No separate concept
pko_act_dtl ins_dt - month None No separate concept
Workflow History Tables
cnfrm_bck_serv moddte - month wh_id - list No separate concept
cnfrm_inv_serv adddte - month wh_id - list No separate concept
cnfrm_noninv_serv adddte - month wh_id - list No separate concept 
Order Tables
trlr uc_trndte-month stoloc_wh_id uc_trndte is null #1
car_move uc_trndte-month wh_id uc_trndte is null #1
stop uc_trndte-month uc_wh_id uc_trndte is null #1
shipment uc_trndte-month wh_id uc_trndte is null #1
shipment_line uc_trndte-month wh_id uc_trndte is null #1
ord uc_trndte-month wh_id uc_trndte is null #1
ord_note uc_trndte-month wh_id uc_trndte is null #1
ord_line uc_trndte-month wh_id uc_trndte is null #1
ord_line_note uc_trndte-month wh_id uc_trndte is null #1
oub_serv_ord uc_trndte-month wh_id uc_trndte is null #1
oub_serv_ord_line uc_trndte-month wh_id uc_trndte is null #1
pckwrk_hdr uc_trndte-month wh_id uc_trndte is null #1
pckwrk_dtl uc_trndte-month wh_id uc_trndte is null #1
pckmov uc_trndte-month wh_id uc_trndte is null #1
invlod uc_trndte-month wh_id uc_trndte is null #1
invsub uc_trndte-month uc_wh_id uc_trndte is null #1
invdtl uc_trndte-month uc_wh_id uc_trndte is null #1
locmst uc_trndte-month uc_wh_id uc_trndte is null #1
manfst uc_trndte-month uc_wh_id uc_trndte is null #1
shp_dst_loc uc_trndte-month uc_wh_id uc_trndte is null #1
sp_hand_ref uc_trndte-month uc_wh_id uc_trndte is null #1
Receiving Tables
trlr uc_trndte-month stoloc_wh_id uc_trndte is null #2
rcvtrk uc_trndte-month wh_id uc_trndte is null #2
rcvinv uc_trndte-month wh_id uc_trndte is null #2
rcvlin uc_trndte-month stoloc_wh_id uc_trndte is null #2
locmst uc_trndte-month wh_id uc_trndte is null #2
inb_serv_rcvinv uc_trndte-month wh_id uc_trndte is null #2
inb_serv_rcvlin uc_trndte-month wh_id uc_trndte is null #2
Job/Task History Tables
job_definition_exec start_dte-month
task_definition_exec start_dte-month

Conclusion

If you have been live for some time - chances are you have experienced your share of Archiving Blues. Because of the overly complex archive solution - people often resent even going to archive instance. Volumes of priceless data in dlytrn is completely ignored and rather than valuing it we consider it a burden. Using the simple strategies described here we can enter the 21st century for our JDA WMS systems. We can consider dlytrn as a valuable data source which we choose to not purge and are not worried about it growing to 100s of millions of rows. We can utilize simple techniques like "stand-by databases" and mine through that data to see what gems may be there - or if we do not have time or budget right now at least we can leave data there rather than losing it. Also by keeping it all in one system where historical data is easily accessible we do not have to worry about untimely exceptions. If we have to print a packing slip from 5 years ago - no problem; we simply go to "Report Operations" and print it.