SQL Statements

The EnterpriseOne SQL Statements category will include all posts and pages pertaining to SQL statements that can be used in the administration and configuration of EnterpriseOne.

EnterpriseOne (E1) Package Build Count

Recently we were asked the following question:

"How many packages of each type per environment did you build last year?"

Here is the SQL that I used to gather the information required:

select hhpkgname, hhpathcd, year(date(char(hhblddte+1900000))) as hhblddte
from sy811/f96215
where hhpkgname not like 'JJ%' --remove the ESUs 
and hhdatp='CLIENT' --type of build 
and hhbldsts=50 --build completed successfully

I then used this information to create an ASP.Net page to organize the data more elegantly. Below are package statistics for the 2 EnterpriseOne (E1) installations that I support:

PkgStats1

PkgStats2

Ok, so now you know what I’ve been doing… How many packages do you build in a year?

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Technorati Tags: e1,enterpriseone,erp,peoplesoft,oracle,jde,jd edwards

EnterpriseOne (E1) Version Promotion With Security

The other day our development manager came to me and stated that she could not promote a version to production.

In order to take any security out of the picture, I tried to promote it with my admin id. I received the same error: "General error promoting object". As with most E1 error messages, this was not real helpful.

I then looked at the object in the Batch Versions application and happened to notice that the version that was causing the issue had version security set to 2. According to the the documentation a "2" in the version security options:

2 (medium to full security) This level allows all users to install or copy the version, but lets only the "Last Modified By" user complete all the tasks.

NOTE: The broken-English is the documentation’s not mine.

Because of this setting I was unable to make any changes to this security through the application. Instead, I used SQL:

update codv811/f983051
set vrexcl='1' 
where vrpid = 'xxxxxx' 
and vrvers = 'xxxxxx'

Hope this helps!

Hey, have you ever had trouble with this or something similar? If so, how did you get things to work again?

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Technorati Tags: e1,enterpriseone,erp,peoplesoft,oracle,jde,jd edwards

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

Promoted EnterpriseOne (E1) Projects

So, to continue with the SQL theme from our E1 Development Manager, here is one that retrieves info about the projects that have been promoted since a given date. It gives the Project Name, Project Description, Object Name, Object Description, Object Type and the person’s userid that is on the object in PY.

SELECT PMOMWPRJID PROJ_NAME, PMOMWDESC PROJ_DESC, 
 POOMWOBJID OBJECT_NAME,IFNULL(C.SIMD,VRJD) OBJECT_DESC, 
 POOMWOT OBJECT_TYPE, 
 (SELECT E.SIUSER FROM OL811/F9861 E 
 WHERE E.SIOBNM = C.SIOBNM AND E.SIPATHCD = 'PY811') DEVELOPER 
FROM SY811/F98220, 
 ((SY811/F98222 
 LEFT OUTER JOIN OL811/F9860 C ON POOMWOBJID = C.SIOBNM) 
 LEFT OUTER JOIN COPD811/F983051 ON POOMWOBJID = 
 CONCAT(CONCAT(RTRIM(VRPID),'|'),VRVERS) ) 
WHERE PMOMWPS in ('38') AND PMOMWPRJID = POOMWPRJID AND 
 POOMWOT NOT IN ('UO', 'SE_TASK', 'SE_REL') AND 
 PMUPMJ > 107250 
ORDER BY PMOMWPRJID, POOMWOBJID 

Technorati Tags: e1,enterpriseone,erp,peoplesoft,oracle,jde,jd edwards

Who Has Access To That In EnterpriseOne (E1)?

There are many things that I find frustrating about the GUI tools for EnterpriseOne (E1), especially when doing Systems Administration functions.

Apparently, I’m not the only one. We give read-only access to most of the SysAdmin applications to our developers. You may have your own opinions about that, but it’s what we chose to do.

Anyway, our development manager gets a lot of CNC-type questions from our users and doesn’t like to always “bother” us with them. One of the questions she gets is “Who has access to the application or UBE?”. She got tired of wading through the multiple GUI applications required to get that information and came up with a very slick SQL query that returns the information quickly:

Note: iSeries DB2 syntax

SELECT ABALPH, FSOBNM, RLFRROLE, RLTOROLE 
FROM SY811/F00950, SY811/F95921, SY811/F0092, PRODDTA/F0101 
WHERE FSOBNM = 'PXXXX' AND 
 FSUSER = RLFRROLE AND
 RLTOROLE = ULUSER AND 
 ULAN8 = ABAN8 

Thanks Deb!

Technorati Tags: e1,enterpriseone,erp,peoplesoft,oracle,jde,jd edwards