SQL script to copy user profile in PeopleSoft application

User profile creation is mostly automated in stable PeopleSoft applications.

Sometimes we may get lots of request to create user profiles in PeopleSoft Applications manually, it may not be feasible to create all profiles from PIA.
Sometimes PIA is down due to issues or slowness in PIA, we may not be able to login in PIA using our user profile.

Simplest way to copy user profile : use navigation PeopleTools -> security -> user profile -> Copy User profile.

Here is a way to skip PIA login to copy user profile and do it from SQL plus manually.

Below SQL script runs fine in Peopletools 8.53 to copy user profile. For lower PeopleTools version ( PT version < 8.53), we need to edit script ( remove "OPERPSWDSALT," text from script  ) and script can be executed successfully in lower versions.

The script create a new user profile KETAN (ID: KETAN, Description : Ketan Pitroda, Email ID : ketan.pitroda@abc.com) from profile (ID : DHARA)

Once profile is created, we can login with new profile KETAN using password of user profile "DHARA" in PIA. We will discuss about a way to change password of user profile using SQL plus in new Post.


set echo on
whenever sqlerror exit rollback

SET DEFINE OFF;
Insert into PSOPRDEFN
   (OPRID, VERSION, OPRDEFNDESC, OPERPSWDSALT, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD, ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER)
 (select 'KETAN', VERSION, 'Ketan Pitroda', OPERPSWDSALT, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD,  ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER
from psoprdefn where oprid = 'DHARA');

Insert into PSOPRALIAS
   (OPRID, OPRALIASTYPE, OPRALIASVALUE, SETID, EMPLID, CUST_ID, VENDOR_ID, APPLID, CONTACT_ID, PERSON_ID, EXT_ORG_ID, BIDDER_ID, EOTP_PARTNERID)
( select
   'KETAN',OPRALIASTYPE, OPRALIASVALUE, SETID, EMPLID, CUST_ID, VENDOR_ID, APPLID, CONTACT_ID, PERSON_ID, EXT_ORG_ID, BIDDER_ID, EOTP_PARTNERID
from psopralias where oprid = 'DHARA');

Insert into PSROLEUSER (select 'KETAN', rolename, dynamic_sw from psroleuser where roleuser = 'DHARA');

Insert into PSUSERATTR
   (OPRID, HINT_QUESTION, HINT_RESPONSE, NO_SYMBID_WARN, LASTUPDOPRID, MPDEFAULMP)
 (select
   'KETAN', HINT_QUESTION, HINT_RESPONSE, NO_SYMBID_WARN, LASTUPDOPRID, MPDEFAULMP from psuserattr where oprid = 'DHARA');



Insert into PSUSEREMAIL
   (OPRID, EMAILTYPE, EMAILID, PRIMARY_EMAIL)
( select
   'KETAN', EMAILTYPE, 'ketan.pitroda@abc.com', PRIMARY_EMAIL from psuseremail where oprid = 'DHARA');

Insert into PSUSERPRSNLOPTN
   (OPRID, OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_CNTL, USER_OPTION_VALUE)
( select 'KETAN', OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_CNTL, USER_OPTION_VALUE from PSUSERPRSNLOPTN where oprid = 'DHARA');


Insert into PS_ROLEXLATOPR
   (ROLEUSER, DESCR, OPRID, EMAILID, FORMID, WORKLIST_USER_SW, EMAIL_USER_SW, FORMS_USER_SW, EMPLID, ROLEUSER_ALT, ROLEUSER_SUPR)
 (select
   'KETAN', 'Ketan Pitroda', 'KETAN', 'ketan.pitroda@abc.com', FORMID, WORKLIST_USER_SW, EMAIL_USER_SW, FORMS_USER_SW, EMPLID, ROLEUSER_ALT, ROLEUSER_SUPR from PS_ROLEXLATOPR where roleuser = 'DHARA');
COMMIT;