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
|
