Creating OPRID clones at the speed of light!!!

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’;

 

Read More About OPRID Clones

How to unleash the best of Oracle Features… Part I

In this series, I will cover some of the Oracle features that can be easily implemented to optimize our PeopleSoft environment.

Resumable Space

In todayworld, we will hardly find a database that is not monitored for space usage. But there is still a possibility for our process failing because it ran out of space in the UNDO tablespace or user defined tablespace or the DBA was too late to allocate the required space. Majority of PeopleSoft processes have restart capability. However, there might be instances where you just cannot afford to let the process fail and restart from the last commit/rollback step, example – data conversion during cutover.

The Resumable Space feature provides the facility to suspend transactions when they hit the space errors. The transaction will resume when the error is corrected.

Below are the steps to implement this feature for a SQR process.
1. Modify the SQR to include the following procedure which will be called at the start of the process.

begin-procedure SetResumable          ! Set RESUMABLE in current session

begin-SQL
ALTER SESSION ENABLE RESUMABLE;
end-SQL

end-procedure

2. Create an AFTER SUSPEND trigger to monitor any transactions which have been suspended

CREATE OR REPLACE TRIGGER resumable_alert_notifier
AFTER SUSPEND
ON DATABASE

BEGIN

– LOG ERROR
INSERT INTO RESUMABLE_ERR_LOG (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM USER_RESUMABLE
WHERE SESSION_ID = (SELECT DISTINCT(SID) FROM V$MYSTAT));
– Send Email notification using UTL_SMTP
– Code not shown

END;

You can determine the best strategy (notification or timeout or abort or log error) when transaction is suspended by coding in the AFTER SUSPEND trigger.

Summary
Resumable Space is a cool feature and I recommend it be considered for implementation in your production environment to avoid business critical processes from failing when it encounters space issues. As with any implementation the mantra is “Understand > Plan > Implement in non-production db > Test > Test > Test > implement in production > relax”.
Read More About  Oracle Features

PSADMIN and Remote Desktop

Ever had problems connecting to your server hosting the NT process scheduler or App Server using Remote Desktop and not being able to use“psadmin” to perform administrative tasks?

Here is what happens when you connect to the server using remote desktop and invoke psadmin.


You will receive the above message when you try to check the status of process scheduler.
So, how do you determine if you NT process scheduler is running? There are multiple ways to determine if your process scheduler is running.
1.Look at the task manager
2.Look at the log files
3.Check status using psadmin
In this post, I will cover the last method since the other two are straighforward.
Here is how you can use psadmin when connected using remote desktop.

1.Ensure that the “Telnet” service is started on the server

2. Go to Start > Run > Telnet <servername>

3.You will get a command prompt window

4.From this prompt, go to your PS_HOME/appserv

5.Invoke psadmin

Now, you should be able to use the “psadmin” utility for monitoring, configuring, start, stop, etc.

Read More: PsAdmin

PeopleCode Properties???

Sometime back, I had this question from my team mentioning – How do we determine when and who had customized this particular Record PeopleCode? The compare report showed that the PeopleCode was customized but there were no comments (bad practice!) in the code to show who touched it last.
In Application Designer the properties button is grayed out when you open the peoplecode. Also, the properties at the record level will not answer this question. Here is the SQL you can use to determine who last updated the code and when.

SELECT A.OBJECTVALUE1 RECORD, A.OBJECTVALUE2 FIELD, A.OBJECTVALUE3 EVENT, TO_CHAR(A.LASTUPDDTTM,’YYYY-MM-DD-

HH24.MI.SS.”000000″‘),

A.LASTUPDOPRID

FROM PSPCMPROG A

WHERE A.OBJECTVALUE1 = ‘&RECNAME’

AND OBJECTVALUE2 = ‘&FIELDNAME’

AND UPPER(OBJECTVALUE3) = UPPER(‘&EVENTNAME’)

/

RECORD FIELD EVENT TO_CHAR(A.LASTUPDDTTM,’YYY LASTUPDOPRID

——— ————— ——— ————————– ————

VOUCHER BUSINESS_UNIT RowInit 2006-11-13-18.25.23.000000 PPLSOFT

VOUCHER BUSINESS_UNIT RowInit 2006-11-13-18.25.23.000000 PPLSOFT

Read More about PeopleCode Properties