You’re Fired! …But You’re Still Here? – Cleaning EnterpriseOne (E1) Orphaned Records After Deleting a User Profile

Have you ever had the need to delete a user’s profile, then find that there were tables where the deleted profile is still hanging around?

Well, to tell you the truth, I had never really thought of it before. Then, while trying to catch up on my reading, I ran across an article written by Mike Wright for JDETips. It was in the July/August 2007 issue and is titled: "Deleting EnterpriseOne® User Profiles".

In the article Mike explains that when deleting the user profile using the P0092 (Work With User/Role Profiles) records are deleting from a number of tables that tie back to the User/Role profile, including:

• F0092 – User/Role Profiles
• F00921 – User Display Preferences
• F00925 – User Access Definitions
• F00924 – User Install Packages

However, he also explains that there are some tables that require a more manual cleaning method:

• F986167 – Default Printers
• F98950 – User Overrides
• F00950 – Security Workbench
• F400951 – Default Branch Plant (I’m not sure what version of E1 Mike is working with but I was unable to find this file in 8.11 SP1)

He provides a method of keeping these tables clean by modifying the P0092 application.

That got me thinking about how to clean up those files because modifying the application would only maintain them. We have been "Live" on E1 for almost a year in both of our instances. So, I’m sure that we already have "orphaned" records that should be cleaned up. I put together the following SQL to check:

select * from sy811/f00950
where fsuser not in (select uluser from sy811/f0092)
and fsuser  '*PUBLIC' --exclude the *PUBLIC records
and fsuser  'EXCLUSIVE' --exclude the exclusive security record

As you can see, I started with the Security Workbench table. You might as well check the most important one first. I also didn’t really care to see all of the *PUBLIC records so I got rid of those and the "Exclusive Security" record.

For the other tables, all you have to do is replace the table and field names:

• F986167 – dpuser
• F98950 – uouser

Also, please take note where the tables are located. The F00950 & F986167 are in the System library, so there will usually be only one of those tables. Where as, the F98950 table is located in Central Objects which will usually be located in at least 3 different libraries, one for each pathcode: COPD811, COPY811 & CODV811.

Now, where else would we maybe have orphaned records? How about the F0024 (Batch Approval / Post Security Table) or the F07209 (PayCycle Workbench – Execution Control Parameters)?

The F0024 looks like this:

select * from proddta/f0024
where ugusr1 not in (select uluser from sy811/f0092)

There are only 2 fields in this table and they are both full of user profile names. However, we only use the ugusr1 field because those are the people that have authority to post batches on behalf of the user listed in ugusr2. Field ugusr2 will probably be full of users that do not exist because someone has to have authority to post things entered by people who no longer work for us.

Now, F07209 is a little more complicated. This is the file that controls who can run the PayCycle Workbench processes. The problem with this SQL is that you have to search through 40 fields of user profile names to determine if there are any that are not valid. Hopefully, you don’t have too many payroll batches in your F07209 or this could take a while. If you have a better way of doing this, please let me know.

select * from proddta/f07209
where y@pui1 not in (select uluser from sy811/f0092) and y@pui1 ''
or y@pui2 not in (select uluser from sy811/f0092) and y@pui2 ''
or y@pui3 not in (select uluser from sy811/f0092) and y@pui3 ''
or y@pui4 not in (select uluser from sy811/f0092) and y@pui4 ''
or y@pui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@cui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@cui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@cui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@cui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@cui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@jui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@jui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@jui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@jui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@jui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@rui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@rui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@rui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@rui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@rui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@uui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@uui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@uui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@uui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@uui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@eui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@eui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@eui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@eui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@eui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@vui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@vui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@vui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@vui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@vui5 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@nui1 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@nui2 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@nui3 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@nui4 not in (select uluser from sy811/f0092) and y@pui5 ''
or y@nui5 not in (select uluser from sy811/f0092) and y@pui5 ''

Do you have other tables that you clean up after deleting a user profile that should be included?

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

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com