Querying JDA/BY WMS Data for Adhoc Analysis with RedPrairie Consultants

Overview

JDA/BY 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 SQL Server. 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 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.

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:

Typical Queries

JDA/BY 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)

Shipping (Outbound)

Inventory

History

Complex Example

Originally published on saadwmsblog.blogspot.com