How to query Oracle JDE E1 database

In this article I would like to tell you about my experience with using Oracle JD Edwards E1.

JDE E1 is an ERP developed by Oracle. I’m not going to tell you how to customize it, but how to perform selections to extract data from its database.

JDE E1 is very complex, so I am speaking to you as a developer who over time has had to exchange data with it, without going into too much detail of operation.

I’ll do a roundup of queries that have come in handy.
I will briefly describe the database tables that are used.
First I make a short intro on how JDE E1 tables are generally structured.

The first time you see a jde tables what you think is: oh shit, what the hell is this! Now that I’ve scared you, let’s get to practical things.

All tables in JDE E1 have the prefix “F”.
For example, the table relating to work orders is F4801.
the names of the fields in the tables consist of a prefix with the associated data type.
Example for the F4801 table:
WADOCO: workorder number
WADL01: description
WACO: company
…
Each table with dozens of fields.
A note on the date field. the date in JDE E1 is Julian date.

Now, I will make a list of generic and useful queries for those who need to query a generic jde database:

Select items (if you want to select the items branch use the table F4102):

SELECT * FROM F4101

Select sales Order Detail:

SELECT * FROM F4211

Select Purchase Order Detail:

SELECT * FROM F4311

Select Work Order:

SELECT * FROM F4801

Select Item Ledger:

SELECT * FROM F4111

Select Lot:

SELECT * FROM F4108

Now two examples more complex queries:

Select Work Order, Item and Work Order Routing:

SELECT WLDOCO, WLDSC1, WLKIT, WLMCU, WLMACA, WLLABA, WLSETA, WLSTRX, IMITM, IMDSC1, IMDSC2, WLOPSQ, WLMMCU, WLBFPF, WASRST
FROM F3112 
INNER JOIN F4101 ON WLKIT = IMITM 
INNER JOIN F4801 ON WLDOCO = WADOCO 
ORDER BY WLSTRX, WLDOCO

Select Orders for a certain item:

SELECT * FROM f4211 WHERE SDLITM LIKE ‘279710%’

A very useful reference site to navigate the hundreds of tables of oracle E1 is https://jde.erpref.com/