Security SQL Statements

EnterpriseOne UserIDs Of Terminated Empoyees

Stewart Schatz
214Views
Read Time:38 Sec

My current employer is a publicly traded entity. As such, we are subject to SOX regulations. Because of this we run many reports periodically to satisfy the auditing department.

One of those reports determines if there are any terminated employees that still have active UserIDs.

Here is the SQL that is used for the report: 

SELECT
 uluser, -- userid from f0092
 sceuser, -- userid from f98owsec
 yapast, -- employee pay status from f060116
 date(char(yadt+1900000)), -- adjustment date (last edit of record) from f060116
 yaalph -- full name from f060116
FROM
 sy811/f0092 a, sy811/f98owsec b, proddta/f060116 c
WHERE
 a.uluser = b.scuser = userid
 and a.ulan8 = c.yaan8 -- address book #
 and c.yapast <> '0' -- employee pay status not active
 and b.sceuser = '01' -- userid is enabled

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

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post Problems Deploying A Package
Next post ESU Retrofits and Transfering Objects

More Stories