Results 1 to 8 of 8

Thread: database structure - listing

  1. #1

    database structure - listing

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


  2. #2
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,147

    Re: database structure - listing

    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.
    Attached Files Attached Files
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  3. #3
    Senior Member wainwrr's Avatar
    Join Date
    Oct 2001
    Location
    London, UK
    Posts
    212

    Re: database structure - listing

    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.
    OneWorld Xe B7333 Win2008 SP24.1.1 Oracle 11g 64 bit Websphere 6.1.

    The views, opinions, and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by EMCOR Group, Inc.

  4. #4

    Re: database structure - listing

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

    We would be very grateful

    regards
    Alejandro.

  5. #5
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,147

    Re: database structure - listing

    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.
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  6. #6

    Re: database structure - listing

    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...2660811::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
    Franck BLETTNER
    http://www.nomana-it.fr


  7. #7
    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.

  8. #8
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,240
    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.
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.