Sunday, June 10, 2018

Querying RedPrairie WMS Data for Adhoc Analysis

Overview

RedPrairie/JDA instances can have over 1000 tables and over 5000 distinct field names.  Exact counts can vary based on the options that are installed and if there are customizations. 

Typical RedPrairie installation is in a single schema for oracle or a single database in SQLServer.  The data is very easy to query and since the software is implemented with sound use of the concept of database transactions, querying from another tool is just fine!

In this blog I will provide some techniques about getting the information about the data dictionary so that you may use other tools to create reports or views.

RedPrairie Data Dictionary

Oracle and SQLServer had constructs and data stores for storing detailed information about tables and columns for some time but most software packages do not utilize these.  RedPrairie had been putting such information into the database for some time.
Information Data Dictionary Table Example
Table Information (Legacy SQLServer) ::fn_listextendedproperty select value cmnt from ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', @table_name, NULL,NULL)
Table Information (Legacy Oracle) user_tab_comments select comments cmnt from user_tab_comments where table_name = @table_name
Table Information (New) les_mls_cat tbl_table select * from les_mls_cat where mls_id = 'tbl_' || @table
Table Information (New) les_mls_cat tbl_table select * from les_mls_cat where mls_id = 'tbl_' || @table
Column - Short Name les_mls_cat select * from les_mls_cat where mls_id = @column
Column - Comment (SQLServer) les_mls_cat select value cmnt from ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', @table_name,'column',@column)
Column - Comment (Oracle) user_col_comments select comments cmnt from user_col_comments where column_name = @column_name and table_name = @table_name

RedPrairie Data Dictionary via MOCA

This information can be retrieved by executing MOCA Commands as well.  If you have access to the instance via MOCA - this can be simpler.  You can use a MOCA client or use Server Command Operations.

Run "Server Command Operations".  It shows an application like


Some commands you can run here to get information are:
Command Data Returned Example
list user tables Table list along with details list user tables
list table columns Columns in a table, short name, and comment list table columns where table_name = 'rcvinv'


Typical Queries

RedPrairie database is well normalized and is easy to query.  You can refer to this blog entry to see the tables that correspond to some functional areas.  Once you know a table, you can refer to techniques specified above to see columns in the table.  In order to join tables you can refer to table primary key.  Most of the times related tables have exactly same column names or very similar column names.

Receiving (Inbound)

Query Tables Example
Supplier List supmst, adrmst select * from supmst join admst on adrmst.adr_id = supmst.adr_id
Master Receipt / Receive Truck rcvtrk, trlr, locmst Every rcvtrk is a location. rcvtrk has trlr_id to join to trlr
List of receipts rcvtrk, rcvinv, rcvlin from rcvtrk join rcvinv on rcvinv.trknum = rcvtrk.trknum and rcvinv.wh_id = rcvtrk.wh_id join rcvlin on rcvlin.wh_id = rcvinv.wh_id and rcvlin.client_id = rcvinv.client_id and rcvlin.invnum = rcvinv.invnum and rcvlin.supnum = rcvinv.supnum
Inventory for receipt rcvlin, invdtl from rcvlin join invdtl on invdtl.rcvkey = rcvlin.rcvkey

Shipping (Outbound)

Query Tables Example
Customer List cstmst, adrmst select * from cstmst join admst on adrmst.adr_id = cstmst.adr_id
Shipping Structure shipment, shipment line, order line One shipment has several lines. Each line points to an order line
Inventory for shipment shipment_line, invdtl shipment line join invdtl on invdtl.ship_line_id= shipment_line.ship_line_id

Inventory

Query Tables Example
Inventory List locmst, invlod, invsub, invdtl Actual item details are in invdtl. invsub is a group of details like a case. invlod is like a pallet. locmst is actual storage location or for ASN it is the truck

History

Query Tables Example
History Data dlytrn Any activity will log something in dlytrn. For example receiving, inventory movements, picking, shipping. While transaction tables will have current data these can be queried to get historical data

Complex Examples

Query Approach
Historical Report to get supplier performance, i.e. errors in receipts
  1. If we change status while receiving, rcvlin will have rows with bad status
  2. If we received in expected status and then changed status, then use dlytrn to query an entry for the same load after creating inventory with changed status