In my development environment, I had a request to create 30 new OPRID’s cloning an existing developer id template. I was not interested in logging into PeopleSoft, navigating to PeopleTools > Security > User Profiles > Copy User Profiles, and creating the clone 30 times. Hmm…
So here is what I did.
1.Create a dummy table
create table hx_clone_ids (oprid varchar2(30));
2.Insert all desired OPRID values. For example,
INSERT INTO HX_CLONE_IDS SELECT ‘HX_NPAI_’||ROWNUM FROM PSOPRDEFN WHERE ROWNUM < 31;
3.Execute below SQL’s (OPRID to be cloned is NPAI).
INSERT INTO PSOPRALIAS
SELECT
HX.OPRID,
OPRALIASTYPE,
OPRALIASVALUE,
SETID,
EMPLID,
CUST_ID,
VENDOR_ID,
APPLID,
CONTACT_ID,
PERSON_ID,
EXT_ORG_ID,
BIDDER_ID,
EOTP_PARTNERID
FROM PSOPRALIAS A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;
INSERT INTO PSOPRDEFN
SELECT
HX.OPRID,
VERSION,
OPRDEFNDESC,
EMPLID,
EMAILID,
OPRCLASS,
ROWSECCLASS,
OPERPSWD,
ENCRYPTED,
SYMBOLICID,
LANGUAGE_CD,
MULTILANG,
CURRENCY_CD,
SYSDATE,
ACCTLOCK,
PRCSPRFLCLS,
DEFAULTNAVHP,
FAILEDLOGINS,
EXPENT,
OPRTYPE,
USERIDALIAS,
LASTSIGNONDTTM,
LASTUPDDTTM,
LASTUPDOPRID,
PTALLOWSWITCHUSER
FROM PSOPRDEFN A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;
INSERT INTO PS_ROLEXLATOPR
SELECT
HX.OPRID,
DESCR,
HX.OPRID,
EMAILID,
FORMID,
WORKLIST_USER_SW,
EMAIL_USER_SW,
FORMS_USER_SW,
EMPLID,
ROLEUSER_ALT,
ROLEUSER_SUPR,
EFFDT_FROM,
EFFDT_TO
FROM PS_ROLEXLATOPR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;
INSERT INTO PSUSERATTR
SELECT
HX.OPRID,
HINT_QUESTION,
HINT_RESPONSE,
NO_SYMBID_WARN,
LASTUPDDTTM,
LASTUPDOPRID,
MPDEFAULMP
FROM PSUSERATTR A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;
INSERT INTO PSUSEREMAIL
SELECT
HX.OPRID,
EMAILTYPE,
EMAILID,
PRIMARY_EMAIL
FROM PSUSEREMAIL A, HX_CLONE_IDS HX WHERE A.OPRID = ‘NPAI’;
INSERT INTO PSROLEUSER
SELECT
HX.OPRID,
ROLENAME,
DYNAMIC_SW
FROM PSROLEUSER A, HX_CLONE_IDS HX WHERE ROLEUSER = ‘NPAI’;
