Contact us

    Talk to our Experts

    +1 920 303 0470 1302 S Main ST, Oshkosh, WI 54902-6518

    Querying JDA/BY WMS Data for Adhoc Analysis

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

    By Saad Ahmad, Executive Vice President of Smart IS International


    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)



    Complex Example

    Originally published on

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Recent Stories

    Copyright © 2024 Smart-IS International. All Rights Reserved.

    Microsoft is a registered trademark of Microsoft Corporation Oracle, JD Edwards and Peoplesoft are registered trademarks of Oracle Corporation. Blue Yonder is a registered trademark of Blue Yonder Group Incorporated. All trademarks are property of their respective owners.