Developer ToolsFree generatorPDF, Excel, Word exportJD Edwards EnterpriseOneAll JD Edwards EnterpriseOne tools

JD Edwards User Defined Codes Table SQL (F0004, F0005)

This generator writes four SQL queries against the JD Edwards EnterpriseOne user defined code tables: F0004 for UDC types, F0005 for values, a decode join from a business table, and F0005D for alternate language descriptions. It handles the right-justified DRKY key that breaks most first attempts.

Fill in

Control tables schema

UDCs live in the control tables data source (for example PRODCTL, CRPCTL, TESTCTL).

Business data schema

Product code (DRSY)

UDC type (DRRT)

00/DT is document type.

Language preference code

From UDC 01/LP.

Generated code

Download:

1. UDC types for a product code (F0004)

SELECT t.DTSY  AS product_code,
       t.DTRT  AS udc_type,
       t.DTDL01 AS description,
       t.DTCDL AS code_length,
       t.DTCNUM AS numeric_flag,
       t.DTLN2 AS two_line_flag
FROM   CRPCTL.F0004 t
WHERE  t.DTSY = '00'
ORDER  BY t.DTRT;

2. Values of one UDC (F0005)

SELECT u.DRSY,
       u.DRRT,
       LTRIM(RTRIM(u.DRKY)) AS code,
       u.DRDL01 AS description_1,
       u.DRDL02 AS description_2,
       u.DRSPHD AS special_handling,
       u.DRHRDC AS hard_coded
FROM   CRPCTL.F0005 u
WHERE  u.DRSY = '00'
  AND  u.DRRT = 'DT'
ORDER  BY LTRIM(RTRIM(u.DRKY));

3. Decode a code in a business table (F0101 search type, UDC 01/ST)

-- DRKY is CHAR(10) and short codes are stored right-justified with leading blanks,
-- while business columns such as ABAT1 are left-justified. Trim both sides.
SELECT a.ABAN8,
       a.ABALPH,
       a.ABAT1,
       u.DRDL01 AS search_type_description
FROM   CRPDTA.F0101 a
LEFT JOIN CRPCTL.F0005 u
       ON u.DRSY = '01'
      AND u.DRRT = 'ST'
      AND LTRIM(RTRIM(u.DRKY)) = LTRIM(RTRIM(a.ABAT1))
ORDER  BY a.ABAN8;

4. Translated description with fallback (F0005D)

SELECT LTRIM(RTRIM(u.DRKY)) AS code,
       COALESCE(d.DRDL01, u.DRDL01) AS description
FROM   CRPCTL.F0005 u
LEFT JOIN CRPCTL.F0005D d
       ON d.DRSY = u.DRSY
      AND d.DRRT = u.DRRT
      AND d.DRKY = u.DRKY
      AND d.DRLNGP = 'F'
WHERE  u.DRSY = '00'
  AND  u.DRRT = 'DT'
ORDER  BY LTRIM(RTRIM(u.DRKY));

Works on Oracle and SQL Server (LTRIM/RTRIM and COALESCE exist on both). On IBM i use your library names in place of the schemas. Check the control tables data source name for your path code in OCM before running. Wrapping DRKY in TRIM stops index use; for large decode reports, materialise the trimmed UDC list first.

Get a decoded reporting layer

Tell us the tables you report on and we will build a set of views that decode every UDC column correctly for your path codes.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

Where UDCs live in EnterpriseOne

User defined codes are maintained in P0004A (Work With User Defined Codes) and stored in the control tables data source, not with business data. On a typical install that means schemas such as PRODCTL, CRPCTL and TESTCTL, one per path code, while F0101 or F4211 sit in PRODDTA or CRPDTA. F0004 holds one row per UDC type (product code DTSY plus type DTRT) with its code length DTCDL. F0005 holds the values: DRSY, DRRT, the code DRKY, descriptions DRDL01 and DRDL02, special handling DRSPHD and the hard-coded flag DRHRDC. F0005D holds translated descriptions keyed by language DRLNGP.

How to use it

Set the control schema for the environment you are reporting on, the business data schema, and the product code and type you want (for example 00/DT for document types, 01/ST for address book search types). Query 1 lists every type under a product code with its length, useful when you are hunting for the right UDC. Query 2 lists values. Query 3 is the pattern for any decode join; replace F0101 and ABAT1 with your table and column and the 01/ST literals with that column's UDC. Query 4 returns the translated description when one exists and falls back to the base text.

Find a column's UDC in the data dictionary (P92001), on the item's edit rule
Hard-coded UDCs (DRHRDC = Y) are used by program logic; do not change their values
Special handling codes drive logic in many programs, so report them too

The DRKY trap

DRKY is a 10 character field. When a UDC's code length is shorter than 10, JD Edwards stores the value right-justified, so a document type OP sits in DRKY as eight blanks followed by OP. The same value in a business column such as F4211 SDDCTO is left-justified. A join of DRKY = SDDCTO returns nothing, which is why the generated SQL trims both sides. The cost is that the database cannot use the F0005 index on DRKY, which is fine for UDCs of a few hundred rows but worth a materialised lookup in a heavy report.

Common mistakes

Querying the business data schema for F0005 often works by accident on a demo database and then fails in production where control tables are separate, so always point at the control tables data source. Second, people forget that each path code can have its own control tables, so a UDC added in CRP may not exist in production yet. Third, product codes are stored as four characters; comparing to the two character literal works on Oracle and SQL Server because trailing blanks are ignored in CHAR comparisons, but keep that in mind on other engines.

Frequently Asked Questions

Which table holds JD Edwards user defined codes?

F0005 holds the UDC values and F0004 holds the UDC type definitions. Alternate language descriptions are in F0005D and alternate type descriptions in F0004D. All of them sit in the control tables data source for the path code, not in the business data schema.

Why does my join to F0005 return no rows?

Almost always because DRKY is stored right-justified with leading blanks for codes shorter than ten characters, while the business column is left-justified. Trim both sides of the comparison, as the generated query does, and make sure you query the control tables schema rather than the business data schema.

How do I see user defined codes in JDE 9.2 without SQL?

Use P0004A, Work With User Defined Codes, entering the product code and UDC type. From there you can also see the type definition, including code length and whether the code is numeric. Changing a UDC there writes to F0005 in the control tables data source for your environment.

What does the hard-coded flag DRHRDC mean?

When DRHRDC is Y, programs depend on the code values, so they must not be changed or deleted. You can usually change the description, but adding or removing values in a hard-coded UDC can break processing. Treat hard-coded UDCs as read-only configuration owned by Oracle's application logic.

If you want people to ask JD Edwards questions in plain English and get answers with UDC codes already decoded, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools