EnterpriseOne – SQLs

ESU Cleanup

Since our installations of EnterpriseOne were installed and the data upgraded we have applied and installed thousands of ESUs. On 4 of the 7, we neglected to apply the ESUs to the Pristine pathcode (PS900/PS910). This made researching code changes a bit of a challenge.

So, the other day, I decided to rectify that and apply the many, many ESUs to Pristine. Because there were some very old ESUs and not all of them were registered with Change Assistant, it was a very time consuming. After I had finished, I noticed that there were several ESUs that had only been applied to PS & PD and others that had only been applied to PS, DV & PY. Rather than look through every ESU and risk missing some, I connected to E1LOCAL and wrote a SQL statement to get every ESU that had been applied to the system that was not in every pathcode.

Please see “Use Oracle SQL Developer To Modify The JDE LOCAL Database” for connection instructions.

Quick SQL To List E1 Roles With Description

Here’s a quick SQL to get a list of E1 Roles and their descriptions:

Unable To Check In OMW Object Or Promote OMW Project

Last week we had a developer that kept getting the following error when trying to check-in an object in OMW.  We also found that this error can happen when trying to promote a project:

According to the Oracle support doc this error is caused by:

Corruption exists in the spec record(s) for the object related to the spec key shown in the jde.log.This could be related to an install, uninstall and then attempted reinstall of an ESU resulting in the “Insert to table F98741 failed” errors on the R98700 PDF.

There are 2 solutions given for this issue:

  1. To resolve this issue, back up the F98741 table first.  Then use SQL to remove the 198 record for EVSK:5ef15a1b-e064-11d1-9ac9-00600832fe19 from the F98741 table. Use Update Uninstall to remove all references to the ESU if it has not been applied to other environments.  See Note Document 654262.1 E1: ESU: Remove Software Updates (Update Uninstall vs. Update Clean-up) – for more details on removing ESUs.

    Delete the executable for the ESU.  Then download the ESU again.  Run the executable from the new download and apply the ESU through Software Updates application. This resolved the issue.

  2. To resolve this issue, check for the related records (F98740, F98741) for the GBRSPEC related to the spec key shown in the jde.log. Back up the table(s) where the spec records exist and delete the records to that spec key (in this case, a69a4b2e-1435-40e1-9df1-deeb8e88a605).  Reinstall the ESU to reapply the related object.

Our issue had nothing really to do with an ESU, so we opted to try solution #2.

It didn’t work… so it seemed.

We had to go through the following exercise 8+ times in order to get a successful check-in:

  1. Delete the record

  2. Try the check-in again
  3. Get a new GUID from the JDE.LOG

It’s not the most elegant solution, but hey, this is CNC!

How To Find The Physical Size Of SQL Server Tables And Indexes

Inspired by Shannon’s post (give me the size of data and indexes for a file in an oracle database please), I found a SQL statement that finds the size of each table and index in a SQL Server database. I also found that there are a ton of different ways to get this information. Below is the method that I is most beneficial to me:

How To Redeploy A Package To A Development Client (Fat Client)

I needed to redeploy a full package and all the update packages associated with the pathcode.  So, I used the SQL below to do the job:

Cool Data Selection When Purging Work Submitted Jobs

Normally, we purge out all records from the Work Submitted Jobs screen that are at least 45 days old.

However, one of our clients requested to have all versions of R43510 purged after only one day.

While setting up the R9861101 data selection, I found that the Literal –> Range of Values method worked in a interesting way.  I was able to enter the following range on the F986110|RNDFUF2 (Foundation – Future Use 2):

That is about the most robust string/integer range I’ve seen.

Then, I thought, “How the heck is this happening?  Is it some kind of special JDE E1 processing that is parsing through the characters and integers?” 

Well, as some of you already know, SQL has a “BETWEEN” SQL operator

The SQL equivilant looks something like this:

SQL To Get The Email Address From The Address Book Who’s Who For Each User Profile

Below is the SQL used to get the emails address from the Address Book Who’s Who (F01151) for each user profile (F0092):

Use Oracle SQL Developer To Modify The JDE LOCAL Database

During our upgrade process, we apply all of the ESUs to become “code current” before mirroring the upgraded code to the other pathcodes.

The process of doing this does not automatically update the Electronic Software Updates application to reflect that all of the ESUs are in all the pathcodes.

There a couple of different way to update the grid:

  1. Apply the ESUs to the other environments.  This can be a very long and tedious endeavor because there could be thousands of ESUs to apply.
  2. Update the JDE LOCAL database.

I have done option 1 in the past but found it kind of a pain and since I knew that I didn’t really want the process to really do anything except update the table, I chose to come up with a process to do option 2.

Here is what I did:

  1. Install and configure Oracle SQL Developer.  This is really the hardest part, but really not much of an issue.
  2. Create a connection to the JDESY910 database.  Remember, not only is JDESY910 the name of the database, but by default it is also the UserID and Password.
  3. Run the following SQL
    • In this SQL, we upgraded PD910 and then moved the code to DV & PY.
    • We only need the “status 90” records because those are the ones that the grid uses.

Hope this helps.