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	If we change status while receiving, rcvlin will have rows with bad status 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