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.

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

Leggo My EnterpriseOne (E1) Object!

While trying to delete an object the other day, I received the ever-helpful “Object exists in an active project” error. Since our developers have created a few hundred OMW projects, I didn’t want to go searching through every project to see where this object was. I realize that I should have been able to look at the News/Status tab and get the info, but it didn’t seem to be working.

Anyway, I used the following SQL to get the info (iSeries syntax):

select * from sy811/f98222 where POOMWOBJID like '[object_name]%'

How To Get A Date… Out Of E1 Part 2

Don’t worry, this still is not a dating service!

My last post dealt with dates in E1. Well, there is another way that dates are stored…

The number of seconds since January 1, 1970.

This isn’t too uncommon in programming languages such as C++, but it sure is a pain in the rear-end when looking in a database.

I have included a conversion tool on my E1 Dates page.

***NOTE: the dates calculation page has been updated***

Updated:

Here is a very simple way to convert the date using SQL:

date(char(DATEFIELD+1900000))

Example:

select date(char(SCSECLST+1900000)) from sy811/f98owsec

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

EnterpriseOne (E1) OMW Cleanup

When installing ESUs, an OMW project is created. If you have installed a lot of ESUs those projects can make it very annoying when trying to find other legitimate projects that you have access to. Yes, you can filter the OMW Project display, but I kinda find that annoying too. Anyway, I created a very simple SQL that I run once in a while to clean up my OMW:

DELETE FROM sy811/f98221
WHERE PUOMWUSER = 'userid'
AND PUOMWPRJID LIKE 'JJ%'

Of course the syntax will need to be adjusted if using a database other than an IBM iSeries.

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