8Apr/140
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
Leave a comment