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?
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.
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:
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.
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[tfd] (@tbl varchar(99))
-- Written by Bryant Avey, InterNuntius, Inc.
-- Provided free "As Is" with no warranties or guarantees
-- I just ask that you keep these comments in the function, if you use it.
-- The complete article describing this function can be found at: http://internuntius.com/how-to-access-the-jde-data-dictionary-in-sql
-- Description: Retrieve Table and Field Descriptions for any JD Edwards Table
-- Use: execute this stored procedure by passing in the table name as a parameter:
-- select * from tfd('F47012')
-- you can also use it to find where fields are used:
-- select * from tfd('%') where alias = 'UOM4'
-- Get the table field definition data
sys.schemas.name "JDE Schema",
sysobjects.name "JDE Table", simd "Table Name",
colorder "Field Sequence",
syscolumns.name "JDE Field",
frclas "Data Class",
frdtat "Data Type",
frdtad "Decimals Stored",
frcdec "Decimals Displayed",
frowdi "Item Description",
--frdsca "Dictionary Description",
join syscolumns on sysobjects.id = syscolumns.id
join jde812.dd812.f9210 on substring(syscolumns.name,3,99) = f9210.frdtai
join jde812.ol812.f9860 on sysobjects.name = siobnm
join sys.schemas on sysobjects.uid = sys.schemas.schema_id
where sysobjects.name like @tbl and sysobjects.xtype = 'U'
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
select tabcols.owner “Schema Owner”,
trim(tbls.sisy) “JDE System Code”,
trim(tbls.siobnm) “Physical Table”,
trim(tbls.simd) “JDE Table Name”,
tabcols.column_id “Field Sequence”,
tabcols.column_name “Physical Column”,
trim(cols.frdtai) “JDE Column Alias”,
trim(cols.frowdi) “JDE Column Description”,
trim(cols.frclas) “Data Class”,
trim(cols.frdtat) “Data Type”,
trim(cols.frdtad) “Decimals Stored”,
trim(cols.frcdec) “Decimals Displayed”,
tabcols.data_length “SQL Length”,
tabcols.data_precision “SQL Precision”,
tabcols.nullable “SQL Null”
from jdeol910.f9860 tbls,
where tbls.sifuno = ‘TBLE’
— and tbls.siobnm = ‘F47011’
and tabcols.table_name = trim(tbls.siobnm)
and substr(tabcols.column_name,3,99) = trim(cols.frdtai)
order by tbls.simd, tabcols.column_id