Archives by date

You are browsing the site archives for 2013.

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 Whos Who (F01151) for each user profile (F0092):

 SELECT A.ULUSER,A.ULAN8, B.EAEMAL  FROM JDE910.SY910.F0092 A, JDE_PRODUCTION.PRODDTA.F01151 B  WHERE ULUGRP'*GROUP' AND B.EAAN8 IN (SELECT ABAN8 FROM TESTDTA.F0101 WHERE ABAT1='E') AND A.ULAN8=B.EAAN8 ORDER BY ULAN8 

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 didnt 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.
    image
  3. Run the following SQL
     INSERT INTO F9671 (SDPKGNAME,SDSUDET,SDSUDS,SDSUDATE, SDSUTIME,SDSUDFUT1,SDSUDFUT2,SDSUDFUT3,SDUSER,SDPID, SDUPMJ,SDUPMT,SDJOBN) SELECT SDPKGNAME,90,15,113284,111111,' ','DV910',0, 'JDE','SQL',113284,111111,'<deployment name server>' FROM F9671 WHERE SDSUDET=90 AND SDSUDFUT2='PD910' 
    • 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.

Upgrade Gotcha: Media Objects

I got a great reminder from a post today on Shannons JDE CNC Technical Blog titled Media Objects Gotcha-upgrade / deployment server name.

During an upgrade, switching deployment servers or changing the location of media objects, after moving all the files to the new location, you will need to change the internal pointers to the new location.  Run the following SQL on the F00165:

 UPDATE PRODDTA.F00165 SET GDGTFILENM = REPLACE(RTRIM(GDGTFILENM),
'','') 
WHERE GDGTFILENM LIKE ''

How To Compare Field Values Between Tables

I used the following SQL to compare the difference between the F9200 (Data Dictionary) used for EnterpriseOne 9.1 and the one used for EnterpriseOne 8.12 that I copied to the SQL Server using R98403:

SELECT a.FRDTAI,a.FRCDEC AS a_FRCDEC, b.FRCDEC AS b_FRCDEC  
FROM JDE910.DD910.F9210 a  
INNER JOIN DD812.PRODDTA.F9210 b ON  	
(a.FRDTAI COLLATE DATABASE_DEFAULT = b.FRDTAI COLLATE DATABASE_DEFAULT)  
WHERE a.FRCDEC COLLATE DATABASE_DEFAULT  b.FRCDEC 
COLLATE DATABASE_DEFAULT

SQL Server Error: Cannot resolve the collation conflict between "Latin1_General_CI_AS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

I received the following error when trying to join an EnterpriseOne 9.1 table with an EnterpriseOne 8.12 table that was copied to the SQL Server from an iSeries using R98403:

Cannot resolve the collation conflict between “Latin1_General_CI_AS_WS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation

The way I resolved the issue was to specify the fields collation by placing COLLATE DATABASE_DEFAULT after the field name:

 
SELECT a.FRDTAI,a.FRCDEC AS a_FRCDEC, b.FRCDEC AS b_FRCDEC 
FROM JDE910.DD910.F9210 a 
INNER JOIN DD812.PRODDTA.F9210 b ON 	
(a.FRDTAI COLLATE DATABASE_DEFAULT = b.FRDTAI COLLATE DATABASE_DEFAULT) 
WHERE a.FRCDEC COLLATE DATABASE_DEFAULT  b.FRCDEC COLLATE DATABASE_DEFAULT