Originally Published: April 17, 2013 – Updated: June 2020
An organization brings in a trainer to walk their IT and Super Users through configuration screens in setup workshop training. They are delighted and happy because, for the first time outside of sales pitches, they have hands-on demos in setting up samples of areas, locations, footprints, etc. They now realize how robust and east it can be to configure JDA's (now BlueYonder) WMS solution.
However, that happiness is short-lived as soon as the IT and Super Users transition into the construction phase of a project. Their faces become pale and severely long. They soon realize they have to deal with hundreds of configurations. The exercise of setting up areas policies for replenishment, which they well recall took ten minutes in the workshop, is now taking days to complete due to the scale of the entries.
Supply Chain Consultants Stepping In
I'm sure this scenario sounds all too familiar. In my experience, this is when experienced consultants with tens of implementations under their belts come into play. They bring in tools, processes, and tips that can significantly increase an organization's setup productivity and reduce their costs. I'll share with you a straightforward method that we have seen work time and time again.
Suppose you're working on the task of configuring the Replenishments "Release Rules" of your areas, which involves going through the Area Maintenance, clicking on Modify Policies, and going through the wizard. For organizations with a large facility, this can be a cumbersome task as there can be hundreds of combinations involving Source Area, Destination Area, Work Type, and picking the unit of measure.
I'll demonstrate how Excel can help you. At a high level, we'll be entering the data in a spreadsheet that will automatically provide us with commands we can later copy, paste and execute in one shot inside JDA (now BlueYonder), and that will complete our task.
In RedPrairie DLlx client, enable tracing and follow the standard procedure of setting Release Rule for a single area. Limit this to one entry. For example, define a directed work for Emergency Replenishment going from a Source Area called BULK to a destination pickface area called SCPM to move Pallets.
Disable and view the trace file.
Look for the high level command. If you’re not a trace reader, don’t sweat it. Look for “ Server got” and keep going through the matches until you find your command (it will be obvious). In our case, it is “create area release rule policy ….”
You’ll need to copy the entire command with its arguments.
Congratulations! You just completed the hardest part. The rest will be in Excel.
(Download the excel template here)
Fire up Excel and create columns for your dynamic data that will change from one entry to another. You can cheat by looking at the command you just got from the trace. In our scenario, this will be Source Area, Destination Area, and Load Level. The rest of arguments such action, priority code, offset, etc. are the same from one area to another in our facility.
Add another column for your script. This will hold the command in which its dynamic parameters will be fed from the other columns. This is an example of what you type in:
="create area release rule policy where wh_id = 'WMD1' and wrktyp = 'E' and srcare = '"&A66&"' and dstare = '"&B66&"' and lodlvl = '"&C66&"'and action = 'CREATE WORK' and arguments = 'dstloc_wrkzon,srcloc_stoloc' and oprcod = 'EMRPL' and baspri = '10' and offset = '10';"
Notice the ampersand referencing the excel cell position that holds the data (ex., srcare = '"&A66&"'). This essentially means the srcare will be whatever is stored in column A, row 66. Also notice the semicolon at the end (I’ll explain it later).
You may copy the Excel formula above to all subsequent rows by dragging the selection.
Work with your Operations team to fill in the spreadsheet.
That's it! When it’s done, you basically have all the commands you need generated dynamically based on filling in the source, destination and load level. You’ll select ALL the rows under the Script column, copy it, paste it into Server Command Operations in Dlx (or use LextEdit or winmsql) and execute it. The semicolon between each row enables executing all these commands in one shot.
The screenshots below illustrate what my spreadsheet looked like at the end and how I loaded it into the system.
In conclusion, the cost of initial setup time, which involves tracing and setting up the Excel sheet, is independent of the data scale. It enables your operation teams to fill in the spreadsheet without any technical assistance in a swift and efficient manner. Your technical resource can then load it into the system in a single shot, also independent of the data scale.
This method can be used in any application where mass data needs to be loaded into the system. For those familiar with Mload utility, this is based on the same principle. It provides an alternative quick and dirty method and an extra level of flexibility, such as having full control of the selection of which data (rows) are to be loaded or loaded first.