CODEWITHSUNDEEP
peoplesoft
Cynthia White
Cynthia White

Reputation: 21

Find User IDs not on OPRDEFN in PeopleSoft FIN

Our former security admin team off-boarded terminated users by deleting their user profiles from the system. We have changed that policy but there is potential for duplicates. I am attempting to find user ids on the various module tables that are not on the OPRDEFN but having no luck.

I would like to query the major tables to return a list of all user ids to compare to the current OPRDEFN. From there, I can either have them added or create a reference list the admins to use prior to creating a new user id.

Does anyone have any tips or already written SQL? I am not the best SQL writer, I've tried several different things but nothings works.

Any help would be greatly appreciated.

Upvotes: 2

Views: 230

Answers (1)

Walucas
Walucas

Reputation: 2568

Here you have a list of tables using the OPRID field. I have already formatted it in a way you can just run the result to get the Oprids. Also, consider other columns like OPERATOR

SELECT 'SELECT DISTINCT OPRID FROM PS_'||RECNAME||';' FROM PSRECDEFN WHERE RECTYPE=0
AND RECNAME IN 
(SELECT RECNAME FROM PSRECFIELD WHERE FIELDNAME IN ('OPRID','OPERATOR'))
AND RECNAME NOT LIKE '%AET'
AND RECNAME NOT LIKE '%TMP'

Also, you may just look at PSACCESSLOG, it will show you when someone access, so you may save time by querying it only

Upvotes: 3

Related Questions