database structure - listing

jparikh

Member
is there any documentation which contains a complete table listing with feild names and descriptions and relationships ?
thanks
JDE - 8.12 SQL
 
jparikh

Firstly, welcome to JDEList.

Secondly the information you request is available from the JDE database. Some time ago I created some Oracle SQL queries that extract the information you require. They work on our system which is Xe using an Oracle database. You may need to adjust them to suit your config. I have attached them. I hope they help.
 

Attachments

  • 109002-Tables and Fields.txt
    3.5 KB · Views: 1,700
Whilst it is possible to list the table structures you should bear in mind that a typical environment will use over 2500 tables so you'll need a lot of paper in that printer.

Really you need to get familiar with looking at the structures on screen, either from the underlying database tools or through OMW.
 
Hello, you have the queries, to put together a menu with excel? as it comes in P01rs01?

We would be very grateful

regards
Alejandro.
 
Alejandro,

I do not know what you mean by:

[ QUOTE ]
Hello, you have the queries, to put together a menu with excel? as it comes in P01rs01?


[/ QUOTE ]

The SQL queries I posted in this thread were for table structures, not menus.

I have no idea what "P01rs01" is, though it is possible I may have forgotten.
 
Hello,

I think you can have a look on the document 1361438.1 on Oracle Support.
Since E1 9.0, it is possible to see data model from technical catalog (https://apex.oracle.com/pls/apex/f?p=50180:63:16123242660811::NO:::). You can download PDF that explain per module tables and relationship that are used.
or you can download data model from Oracle Support (JDEdwards Patches and update) that you can open from SQL Developer Data Modeler
 
Hello - Does JDE have mapping of fields from user screen to JDE table? Also, I was wondering if there is a way to extract data dictionary via SQL.
Thank you in advance for your help.
 
1. Q: Does JDE have mapping of fields from user screen to JDE table?
A: Yes - inside the programs / code

2. Q: Is there is a way to extract data dictionary via SQL
A: Yes. Here's one way (9.1 Oracle PL/SQL)
--------------------------------------
ACCEPT cTablename CHAR DEFAULT * PROMPT 'Enter the Table Name to generate a view for (FXXXXX): '
ACCEPT cOwner CHAR DEFAULT PRODDTA PROMPT 'Enter the Production Environment Table Owner: '
select dtc.column_name, replace(NVL(d.frowdi,dtc.column_name),' ','') JDE_NAME,
d.frcdec Decimal_Places, d.frowtp JDE_Data_Type, d2.frdsca Description
from dba_tab_columns dtc,
DV910.f98711 c, DD910.f9210 d, DD910.f9203 d2
where dtc.owner = '&cOwner'
and dtc.table_name = '&cTablename'
and dtc.column_name=replace(RTRIM(UPPER(c.tdsqlc)),'@','_1')
and c.tdobnm = '&cTablename'
and RTRIM(SUBSTR(c.tdsqlc,3)) = RTRIM(d.frdtai)
and d.frdtai = d2.frdtai and d2.frsyr = ' ' and d2.frscrn = ' '
order by dtc.owner, dtc.table_name, column_id;
--------------------------------------
Instead of querying the database for the table structure you can get that from PD910.F98711, then join DD910.F9210 (on OBND to FRDTAI) and DD910.F9203 (on OBND to FRDTAI and FRSCRN = ' ' and FRSYR = ' '). It will execute much faster that way but I already had the above handy.
 
Back
Top