Web Only Version Pain

We seem to be having an issue with old “web only” versions.  There are times when the “web only” version causes the other version to not egen correctly.  So, below is the SQL that can be used to select the “web only” versions.

SELECT count(*) FROM COPD811/F983051
WHERE VRVCC2='1'             

If you want to be more precise, you can grab the “web only” versions that have been executed prior to a certain date by using the VRVED field and the six digit date which can be found on the e1 dates page.

How To Find EnterpriseOne Web Only Versions

Dealing with EnterpriseOne “Web Only” versions can be a real pain.

They are deleted with each full package deployment and can cause a lot of confusion for the users.

In order to find these troublesome quasi-objects you can run the sql below:

Add Record To OCM – F986101

The other day, I had a need to insert many records into the OCM (F986101).  So I set out to create a quick SQL statement that would serve that purpose.  Below is what I came up with:

INSERT INTO SY811/F986101                         

VALUES('DV811',0,'OBJECTNAME','LOCAL','','*PUBLIC',  

'*ALL','P','','H95','AV','SQL','BSFN',            

'USERNAME','','','',0,'',0,'','','','')            

Remember � you may have to make entries in both the SY811 and SVM811 F986101 files.

Access To Environments

To find out what users or groups have access to a certain environment run the following SQL:

select * from sy811/f0093
where llll='<enviornment>'    

To find out what environments a user or group has access to run the following SQL:

select * from sy811/f0093
where lluser='<userid>'    

Clean Up Your Web-Only Versions

We have been having some issues lately where users create web-only versions and expect to use them after a full package has been built and deployed.

We have processes in place for them to submit new versions to our development teams, but sometimes things don�t happen the way they are designed.

Anyway, I was doing some checking and noticed that there were quite a few versions in the F983051 that were web-only versions and were older than the last full build.

So, I created an SQL that we are going to run immediately after the deployment of a new full package.

DELETE FROM <central objects>/F983051
WHERE VRVCC2='1'  

***NOTE: the syntax used in the SQL statement is for the IBM System i (as400, iSeries)

Quick EnterpriseOne Version Security Solution

Whether you are trying to change the processing options of an interactive version or a batch version.  Sometimes you can run into issues where the application indicates that you do not have authority to change the version like the example to the left.

To quickly get around this error, we can use SQL to change the security setting in the F983051.

UPDATE CODV811/F983051
SET VREXCL = 0
WHERE vRPID = 'P03B2002'
AND VRVERS = 'TEST0001' 

Time To Change Your Password

We have our passwords set to expire every 90 days.

Sometimes there are user IDs that you don’t want to change but don’t really want them to show up on the audit report as not having an expiration period.

Here is a SQL statement that can help you:

UPDATE SY811/F98OWSEC
SET SCSECLST=<numeric date>
WHERE SCUSER='<UserID>' 

Cleaning Up The Effects Of Deleting User Overrides In EnterpriseOne

Over a year ago, I wrote about how to delete User Overrides in EnterpriseOne.

Well, I had to use that process the other day and felt I should probably publish a fix to an unfortunate effects of using the User Overrides application.

What are these effects?  Well, when you delete a User Override, it is first placed in your default project and then deleted.  Unfortunately, after it is deleted, the object is not removed from your project.

So, to clean up what could be hundreds of objects from your default project, run the following SQL:

DELETE FROM SY811/F98222
WHERE POOMWUSER ='***userid***'
AND POOMWOBJID LIKE '%GF%'

Can you think of anything that would make this easier?

EnterpriseOne 8.12 Functionality with Version 8.11 SP1

Background:

In developing the Employee Self-Service user interface without using the portal, the development team had a need to be able to print out an employee’s paystub and have it emailed to them.  The email portion of the process is handled by Optio but before it gets to the Optio server, it needs to be sent to an outqueue on the Enterprise Server.  Because of the flow of the UBE submission application, upon requesting the paystub (UBE) the user is presented with a printer selection verification screen.  It was decided that this screen would be pretty confusing to those employees that do not use EnterpriseOne regularly.  So, the developer found a way around that.  She submitted the UBE using a Named Event Rule (NER). 

Issue:

Because of using the NER to submit the UBE, the Print Immediate parameter in the JAS.INI file doesn’t get read.  This is because it is being submitted by the Enterprise Server instead of the JAS server.  If the job isn’t printed, or sent to an outqueue, then the PDF never gets to the Optio server to be processed and emailed to the user.

Resolution:

After consulting with Oracle Support, we changed the VRVCC3 field of the F983051 to contain a ’1′ in the record of the UBE Version that we wanted to Print Immediate.  Apparently, this version Print Immediate parameter is used in the 8.12 applications but is also read in the 8.11 SP1 version.

Note: We are using 8.11 SP1 and this solution may or may not work with earlier versions.

Escape From SQL

The other day, one of my coworkers was presented a question from our development team:

How can I use SQL to look for a "%" in a column?

Well, the first guess is to use something like:

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%%%'

or

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%\%%'

Well, unfortunately, neither of those statements would work. However, DB2/OS400 provides a very cool way of escaping a special character. It is the ESCAPE keyword. This keyword allows you to specify the character that you would like to use as an escape character. So, this would be the correct syntax:

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%+%%' ESCAPE '+'

I think that this is a very cool feature. I just wish that the ANSI/ISO standards used it. Then, it could be used in other databases.

Next Page »