UDC Integrity

Sofie Hönen

Member
Hi,

I just found out that we have 'UDC Code Orphans' in our Item Master / Item Branch Plant tables - the UDC value being stored in the F4101/F4102 does not exist (anymore) in the F0005 file (for the UDC that has been linked to the data item in the IM/IB tables).
Is there any way I can check if there are more such invalid codes in the database? Some kind of UDC Integrity Check?

Thanks for your help,

Sofie

OS Version Win2000 Service Pack 2 (VER_PLATFORM_WIN32_NT 5.1)
Installer Version 091004:0200_OneWorld_EO8.93_I1
 
Hi Sofie,

We wrote a small tool to validate all the fields for every record in a table. The output is written to a CSV file with the table name.

The program is attached.

From a fat client run the program like this:

jdeutv F4101 (table is case sensitive)

will produce f4101.csv in the directory where the program is run.

Craig
 

Attachments

  • 126801-jdeutv.zip
    7.5 KB · Views: 213
Hello Craig,

Thank you very much - this is exactly what I was looking for!
You made my day
smile.gif


Sofie
 
Craig,

I came across this tool, but I had strange results: your tool reports exceptions in fewer columns that it should. I.e.: using Demo data in E91, F0101 table, it reports exceptions for only 2 columns, while there are exceptions in 5.

Consequently I released a tool of our own - UDC Integrity Inspector. It's available for download from our site now. Here's the result I'm getting from our tool for F0101:

########## Processing [F0101] with 56 UDC columns #==========>
==> ABATE [H01|AR]
[ ]
==> ABAC01 [01|01]
[910]
[940]
[930]
[920]
[950]
==> ABSYNCS [90CA|SY]
[0]
==> ABPERRS [90CA|SS]
[0]
==> ABCAAD [90CA|SE]
[0]
<========# finished [F0101] #########################]

PS: Looks like we will have 3 new tools published this month ;-)
 
Thanks Alex.

Haven't looked at this in about 7 years LOL. A little investigation provides some interesting info ...

The tool uses the API jdeDDValidation to evaluate the table column values. In the case of ATE, when blank, the default value from the DD is retrieved and evaluated which is why it's not returning an error. I may be able to "turn off" the default value usage. The SYNCS and CAAD values pass because they are numeric datatypes and converted to strings based on the UDC definition.

While their value is a 0 MATH_NUMERIC, the resulting lookup from the API is

SELECT * FROM F0005 WHERE ( DRSY = '90CA' AND DRRT = 'SY' AND DRKY = ' 00' )

since it's a 2 position numeric UDC. Actually, pretty smart ;)

Craig
 
Cool, thanks! So yes, JDE is smart enough to make the right assumptions and interpret some of the bad values ;-)
 
Back
Top