Jan 25 2012

Update a Table’s Primary Keys from JDE

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)

Does that title sound funny? Can you update your Primary Keys of a record in a table too? Ofcourse you can, SQL does allow it. But does that work in an ERP?

Yes ERP’s are supposed to keep their data integrity well, and secure it too.

But in JD Edwards you have an option to update the Primary Keys. Actually – Change the primary key values. You can achieve this feat by using Headerless Detail form.
The secondary option is to use the Table IO update operation, by selecting on the secondary keys, to update the primary key values.

This article focuses actually on a tip which in turn tells you that you need to be careful when you the Headerless Detail form for your application development.

Following is a POC, I created for the purpose.

  1. Created a simple Table with 3 columns UKID, ALPH and EMAL. where UKID is the Primary Key.
  2. Created an Application with a Find/Browse form on top of the table.
  3. The Find/Browse form links to a Headerless Detail form for Add and Select Buttons.
  4. Following are the records available in the Table, which you can see in the application too.
  5. Now, we select the First record – UKID=1. Following is the screen we see.
    I’m passing the Form Interconnect value to the QBE, and doing a AutoFind on Entry
  6. Change the UKID value to something say UKID=10 and then Click OK.
  7. Viola….! the UKID has been changed to 10. Check the Find/Browse screens!!!

    Also check the UTB to see the revised values 😀

Ain’t it amazing?
This is not a Bug of JD Edwards. The functionality exists in UDC form too (Headerless Detail form used there), where you can change the KY value, though its a part of the primary key.

What happens behind the scenes is as follows. The Headerless detail form issues an Update SQL to do it.

UPDATE JDEData900.F55TEST1 SET TTUKID= 10,TTALPH= 'Jitender Verma',TTEML= [email protected]' WHERE ((TTUKID = 1 ))

So… this is a word of caution too, make sure, that you validate or disable the Primary key columns for Headerless Detail Form’s UPDATE MODE.

Published under the license.

VN:F [1.9.22_1171]
Rating: 5.0/5 (2 votes cast)
Update a Table's Primary Keys from JDE, 5.0 out of 5 based on 2 ratings
(Visited 517 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

Leave a Reply