tim laqua dot com Thoughts and Code from Tim Laqua

8Apr/140

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

ssrs-jderef-1

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

ssrs-jderef-2

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
Tagged as: , , Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.