Oracle JDE EnterpriseOne

How to access the JDE Data Dictionary in SQL

In Custom Development, Data Architecture, Enterprise Architecture, Enterprise Systems, JD Edwards by Bryant AveyLeave a Comment

The Issue

The JD Edwards EnterpriseOne database layer has been abstracted, by design, from the business and application layers.  The impact of how JDE has chosen to implement this data abstraction is that the database does not contain any table or field name aliases with meaningful, English descriptions.  When applications developers and software developers are interfacing with the JD Edwards EnterpriseOne ERP system, there is often a need to have access to the Table File Definitions and Field Descriptions contained in the JDE Data Dictionary.  Without access to the table descriptions and the field descriptions, developers are not able to easily navigate the data they need to use for developing applications or reports.

Normally there are only two ways to get this information.  Both native methods of accessing table and field description information are inside of the JD Edwards application.  This requires the developers to be trained in how to use JDE in order access the information they need.  The first native method of access is to log into the JDE Fat Client and search through the Data Dictionary for the specific field aliases and or table names.  The second method is to pull up the Table Browser inside the EnterpriseOne Web Application or Fat Client and browse the data table toggling between the field names and the field descriptions.

Wouldn’t it be nice if, as a developer, you could stay inside of SQL or your chosen Integrated Development Environment (IDE) and get all the information necessary to identify what fields and tables you need to use to access the JDE data?  Wouldn’t it be great if you could not only see the descriptions, but also see field lengths?  Wouldn’t it be great to be able to search for specific field aliases throughout JDE to discover all the tables where specific fields are used throughout the JDE system?  While we’re wishing, wouldn’t it be wonderful if we could perform wildcard searches for field descriptions, aliases, data types, and other information in the Data Dictionary, right from our development environments?

The Solution

I have developed a simple table function, called TFD (Table File Descriptions), which allows developers to search and display table file descriptions and JDE Data Dictionary information with a SQL command inside of a query window of your chosen IDE.  How it works is simple.  You just type a select statement and use the TFD() function in “From Clause”.  The TFD() function will return a table result based on the table name passed in, and any additional “Where Clause” constraints.

Examples

A basic query using the TFD() function will result in a standard Table File Description result set as follows:

The statement: “select * from tfd(‘F4211’)”will produce the following results:

A more complex SQL Statement such as this:

Select distinct [JDE Table], [Table Name], [JDE Field], [Item Description] from TFD(‘%’) where alias = ‘UOM4’ order by [JDE Table]

Will yield the following results:

Notice that I used the wildcard character ‘%’ (percent) to show all tables where the Pricing Unit of Measure (UOM4) is used.

You can use any combination of wild cards and select statements with the TFD() function.  You can also search on any field in the result set.  If, for example you wanted to find all the fields in the JDE EDI tables (‘F47%’) containing the word ‘%Partner%’ in it, you could write a query as follows:

select [JDE Table], [Table Name], [JDE Field], [Item Description] from tfd(‘F47%’) where [Item Description] like ‘%Partner%’

The result would be:

As you can see from these few examples, the TFD() function can save time in development by making JDE object names and descriptions visible to the SQL, VB, C#, or Java integrated development environments.  Developers can save time finding which fields or tables contain the data they need, and begin making sense of the cryptic JDE alias names.


The code

Simply copy the following code and execute it in the IDE of your choice.  I used SQL Management Studio to develop and create the function.  Once the function has been created in the JDE database, it’ll be ready to use.

Note: you’ll have to change the schema names and database names to match your JDE EnterpriseOne environment.

Frank Rodgers – Submitted this on 2015/01/04.
Here's a version that should work with v 9.1 running on Oracle
work with v 9.1 running on Oracle

Let me know if this function proves useful to you. I welcome the feedback.