PeopleSoft : Application Tables By Module.

Have you ever wondered what we could do with Owner ID property.

We can Owner ID in the following SQL to Get list of PeopleSoft Application tables by Module Name.

select B.XLATLONGNAME  AS "PeopleSoft Module", A.recname AS "PeopleSoft Table" from psrecdefn A,PSXLATITEM B 

where B.fieldname='OBJECTOWNERID' 

and A.OBJECTOWNERID=B.Fieldvalue

and B.effdt = (select max(B1.effdt) from PSXLATITEM B1 where B.fieldname=B1.fieldname and B.fieldvalue=B1.fieldvalue and B1.effdt<=sysdate)

and B.eff_status='A'

order by 1,2;

This would be helpful when starting implementation project/ Collecting tables for creating Data warehouse or data lake.

Use the following SQL to know the List of module names by ObjectownerID property.

SELECT b.Fieldvalue AS "PeopleSoft ObjectOwnerID",b.xlatlongname AS "PeopleSoft Module" FROM PSXLATITEM B

WHERE B.FIELDNAME = 'OBJECTOWNERID'

and B.effdt = (select max(B1.effdt) from PSXLATITEM B1 where B.fieldname=B1.fieldname and B.fieldvalue=B1.fieldvalue and B1.effdt<=sysdate)

and B.eff_status='A'

order by 1;

PeopleSoft: Update user password - Helpful during installation

This is a very simple Data mover script to update a user's password in PeopleSoft
and re-encrypt it.
Useful if you can't access the PIA (e.g. during installation)
but need to update user passwords.

Steps to update the user password.

1) Login into Data mover using Bootstrap mode. (use database access ID and password)
2) Run below script.(in the example, VP2 user password updated and re encrypted).
UPDATE PSOPRDEFN SET PTOPERPSWDV2 = 'VP2', ENCRYPTED = 0, ACCTLOCK = 0
WHERE OPRID = 'VP2';
ENCRYPT_PASSWORD VP2;

PS: This should be done with utmost precautions. Updating userID password
which is part of any configuration can lead to issues in booting application server.