tim laqua dot com Thoughts and Code from Tim Laqua

jderef.com is dead – Create your own using SSRS and JDE metadata tables

Anyone who has used jderef.com is certainly mourning its demise as JDE, like most ERP systems, is a bear to understand without a really big instruction book. Luckily you can chat with your JDE people and, given the correct libraries, modify the following query to get the same information:

SELECT
       RTRIM(TDOBNM)   as Table_Name,
       RTRIM(d.SIMD)   as Table_Description,
       RTRIM(d.SIPFX)  as Table_Prefix,
       RTRIM(TDOBND)   as Data_Dictionary_Object_Name,
       RTRIM(TDSQLC)   as SQL_Column_Name,
       RTRIM(b.FRDSCR) as Column_Description,
       RTRIM(e.DRDL01) as Field_Type,
       RTRIM(c.FRDTAS) as Field_Length,
       RTRIM(c.FRDTAD) as Field_Decimals,
       --RTRIM(c.FROWDI) as DD_Item,
	CASE 
		WHEN RTRIM(c.FROWER) = 'UDC' THEN 'UDC: RTRIM(DRSY)=''' || RTRIM(c.FROER1) || ''' AND RTRIM(DRRT)=''' || RTRIM(c.FROER2) || ''''
		ELSE 'Generic Edit'
	END as Edit_Type,
       --RTRIM(c.FROWER) as Edit_Rule,
       --RTRIM(c.FROER1) as Edit_Rule_Parm1,
       --RTRIM(c.FROER2) as Edit_Rule_Parm2,
       RTRIM(TDPSEQ)   as Program_Sequence_Number
FROM
        COPD910.F98711 a
        LEFT OUTER JOIN DD910.F9202 b
                ON a.TDOBND = b.FRDTAI
                   AND FRLNGP = '  '
                   AND FRSYR = '    '
        LEFT OUTER JOIN DD910.F9210 c
                ON a.TDOBND = c.FRDTAI
        LEFT OUTER JOIN OL910.F9860 d
                ON a.TDOBNM = d.SIOBNM
                   AND d.SIFUNO = 'TBLE'
        LEFT OUTER JOIN PRODCTL.F0005 e
                ON LTRIM(RTRIM(c.FROWTP)) = LTRIM(RTRIM(e.DRKY))
                   AND e.DRSY = 'H98'
                   AND e.DRRT = 'DT'
WHERE
        RTRIM(TDOBNM) Like ?
ORDER BY
        TDOBNM,
        TDPSEQ

Sample RDL can be found here: http://timlaqua.com/jderef.rdl. You will need to change the data source to work in your environment as well as update the following libraries to your environment:

• COPD910.F98711 - F98711 varies per environment, so make sure you get the right library
• DD910.F9202 - all the F92 files are in the same library
• PRODCTL.F0005 - whatever library your UDC table is in
• OL910.F9860 - the Object Librarian library