E1 Tips & Tricks

EnterpriseOne (E1) ESU Investigation

The title sounds like something out of CSI or Law & Order SVU…

Today our Development Manager was inquiring about what ESUs have been applied and to what environments.

Here is the SQL that I came up with:

Note: I restricted this query to only ESUs that were applied after our "Fix Current" project completed on 106041 (Feb. 10, 2006).  Otherwise, there would be more listings than I could handle.  Also, you’ll find the little trick to convert the JDE 6 digit date to something more readable.

SELECT SDPKGNAME as ESU,
   date(char(Min(Case when SDSUDFUT2='PS811' then SDSUDATE end)+1900000)) as PS811,
   date(char(Min(Case when SDSUDFUT2 = 'DV811' then SDSUDATE end)+1900000)) as DV811,
   date(char(Min(Case when SDSUDFUT2 = 'PY811' then SDSUDATE end)+1900000)) as PY811,
   date(char(Min(Case when SDSUDFUT2 = 'PD811' then SDSUDATE end)+1900000)) as PD811
FROM SY811/F9671, SY811/F9670
WHERE (SDSUDET = '90')
   and (SUSUDATE > 106041)
   and (SDPKGNAME=SUPKGNAME)
GROUP BY SDPKGNAME
ORDER BY SDPKGNAME DESC 

Tags: e1, enterpriseone, EnterpriseOne - General, EnterpriseOne - Install/Upgrade, EnterpriseOne - SQLs, esu, IBM i/System i/iSeries/AS400, oracle, peoplesoft, sql

Related posts

• EnterpriseOne (E1) Package Build Count (0)
• EnterpriseOne (E1) OMW Cleanup (0)
• You’re Fired! …But You’re Still Here? - Cleaning EnterpriseOne (E1) Orphaned Records After Deleting a User Profile (0)
• Who Has Access To That In EnterpriseOne (E1)? (0)
• Promoted EnterpriseOne (E1) Projects (0)

January 4, 2008 | EnterpriseOne - General, EnterpriseOne - Install/Upgrade, EnterpriseOne - SQLs, IBM i/System i/iSeries/AS400